Explore BrainMass
Share

Explore BrainMass

    SQL Statements for Order Processing Database (Moonlight Distributors)

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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?

    © BrainMass Inc. brainmass.com October 10, 2019, 2:09 am ad1c9bdddf
    https://brainmass.com/computer-science/sql/sql-statements-order-processing-database-365353

    Attachments

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

    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.

    $2.19