Explore BrainMass

Explore BrainMass

    Structured Query Language Skills

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

    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.

    © BrainMass Inc. brainmass.com June 4, 2020, 4:03 am ad1c9bdddf
    https://brainmass.com/computer-science/data/structured-query-language-skills-547532

    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

    ADVERTISEMENT