SQL Statements for Order Processing Database (Moonlight Distributors)
Not what you're looking for?
See the attached file.
One of your team members are a little rusty on their SQL skills, but needed to create the SQL Select statements that would produce running summary files for reports on the following:
Customer volume by month
CustomerID, Month, Total shipments, Total weight
Driver performance by month
Driver ID, Month, Number of manifests on time, Number of manifests delivered late.
The team member sent you an e-mail with the following questions:
* From where should he source the information for the design?
* What difficulties would he have in writing the SQL statements for each of these tables?
* Should any of these tables be created on an as-needed basis?
* Do these summary tables violate the concept of table normalization?
Purchase this Solution
Solution Summary
The goal of this solution is to assist the student in describing data relationships using SQL, and deciding on the appropriate steps to take in deciding on what form of SQL syntax is necessary to implement the SQL statements to create summary reports.
Solution Preview
**********Solution Description******************************
For Customer Volume by Month:
Report 1
Month Number of Customers
Jan 1000
Feb 50
SQL for Summary Customers by Month with delivery complete
SELECT TO_CHAR( Date, 'MON') As Month, COUNT(CustomerID) As [Number of
Customers]
FROM Manifest
WHERE Manifest.Complete = 1
Report 2
For Customer ID, Month, Total Shipments, Total Weight:
CustID Month ShipTotal WeightTotal
1 Jan 2000 5000
1 Feb 20 100
1 Mar 0 0
1 Apr 0 0
1 May 0 0
1 June 400 800
1 July ...
2 Jan 50 175
2 Feb 35 205
...
SQL for Summary Customers Volume by Month with delivery complete
SELECT CustomerID , TO_CHAR( Date, 'MON') As Month,
Count(PickupDetails.ManifestID) As [Total Shipments], SUM(
PickupDetails.TotalWeight) As [Total Weight]
FROM Manifest, PickupDetails
WHERE Manifest.Complete = 1 And PickupDetails.ManifestID =
Manifest.ManifestID
OrderBy Date
Report 3
Driver Performance by Month
For All Drivers
Month On time Late
Jan 1000 65
Feb 2000 125
....
SQL for Summary of Delivery Stats for Each Month All Drivers
SELECT TO_DATE( Date, 'MON') AS Month, COUNT( * ) WHERE Date <=
Scheduled-date As [On Time], COUNT(*) WHERE Date > Scheduled_date As
[Late]
FROM Delivery
ORDER BY Date
Report 4
DriverID Month OnTime Late
1234 Jan 40 5
1234 Feb 20 20
1234 Mar 50 0
2345 Jan 100 0
2345 Feb 110 10
...
SQL for Summary of Delivery Stats for Each Month by Driver
CREATE TABLE ...
Purchase this Solution
Free BrainMass Quizzes
Excel Introductory Quiz
This quiz tests your knowledge of basics of MS-Excel.
Javscript Basics
Quiz on basics of javascript programming language.
Basic UNIX commands
Use this quiz to check your knowledge of a few common UNIX commands. The quiz covers some of the most essential UNIX commands and their basic usage. If you can pass this quiz then you are clearly on your way to becoming an effective UNIX command line user.
Inserting and deleting in a linked list
This quiz tests your understanding of how to insert and delete elements in a linked list. Understanding of the use of linked lists, and the related performance aspects, is an important fundamental skill of computer science data structures.
Basic Networking Questions
This quiz consists of some basic networking questions.