Share
Explore BrainMass

Structured Query Language Skills

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
In order to create the SQL select statements that would produce running summary files for reports of the above; how would you answer the questions below?
• From where should they source the information for the design?
• What difficulties would they 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?
Guidelines
Answer four questions above and provide any examples, tables, or SQL statements that you created to arrive at your solutions.

Solution Preview

Task:

Structured Query Language Skills
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

In order to create the SQL select statements that would produce running summary files for reports of the above; how would you answer the questions below?
• From where should they source the information for the design?
• What difficulties would they 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?
Guidelines
Answer four questions above and provide any examples, tables, or SQL statements that you created to arrive at your solutions.

Answer:

I used the following set of SQL statements and example tables (and table fields) to reach to the solution.

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 ...

Solution Summary

Structured query language skills are discussed.

$2.19