SQL Aggregators Activity
In this activity I learned about Header/Detail design. Header/detail design pattern is when some
transaction requires the second table to record the transaction details (e.g. invoice recording
transaction and detail table should record which products were apart of the transaction as well).
Also, you could store order date in sales invoice table instead of listing it repeatedly in the sales
invoice detail table. This way it will make it easier to update, reduce the amount of data we store,
and increase performance of the system. The aggregation functions in the SQL tells query to
summarized data from multiple rows in a single row of result. The different types of aggregation
function include SUM, AVG, COUNT, GROUP BY, and many others. All of these function
were used in this activity including calculating the difference between two tables.
In this project, I’ve collaborated with Alex and he helped me to understand the filter by the date
property of the view function, Othman helped me with the HAVING queries, and I helped
Phelan with the YTD purchase section and showed him the having queries function that I learned
For my extension, I redo this activity at least 2 times to fully understood the materials because
there were too many complex queries such as calculating the differences and having queries.
Head and Body Design
Queries of the head
SELECT dbo.SalesInvoices.SalesInvoiceID, dbo.Customers.Customer_Name,
dbo.SalesInvoices.InvoiceDate, dbo.SalesInvoices.SubTotal, dbo.SalesInvoices.TaxAmt,
FROM dbo.Customers INNER JOIN
dbo.SalesInvoices ON dbo.Customers.CustomerID =