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?
For Customer Volume by Month:
Month Number of Customers
SQL for Summary Customers by Month with delivery complete
SELECT TO_CHAR( Date, 'MON') As Month, COUNT(CustomerID) As [Number of
WHERE Manifest.Complete = 1
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 =
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
ORDER BY Date
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 ...
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.