To help you understand the mechanics of writing the SQL queries for the assignment, I have outlined below the “structure” of the query results, and how each part is produced and how the three parts are combined to produce the final result set.
computer science
Description
To help you understand the mechanics of writing the SQL queries for the assignment, I have outlined
below the “structure” of the query results, and how each part is produced and how the three parts
are combined to produce the final result set.
As shown in the diagram above, the output of the query #1 consists of 3 main parts, and they are
constructed in the order listed (HW #1 query #2 works in a very similar fashion):
1. This is the easy part, where you are simply computing MIN_Q (minimum quantity), MAX_Q
(maximum quantity) and AVG_Q (average quantity) based on the grouping attribute, CUSTOMER.
It is a simple “group-by” query (or “aggregation operator” in relational algebra).
2. Next step is to capture the corresponding information for each pair of (CUSTOMER, MIN_Q) –
that is, MIN_PROD, MIN_DATE and ST (that is, the corresponding PRODUCT, DATE and STATE for
the given (CUSTOMER, MIN_Q)). The “challenge” is that the corresponding information is only
available in the SALES table; therefore, you will need to “join” the result of Part #1 with the SALES
table to pull the corresponding information from the SALES table.
