Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Holly Ta
2/26/19
MIS 322
SQL Aggregators Activity
Introduction
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.
Collaboration
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
from Othman.
Extension
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,
dbo.SalesInvoices.Freight, dbo.SalesInvoices.TotalDue
FROM dbo.Customers INNER JOIN
dbo.SalesInvoices ON dbo.Customers.CustomerID =
dbo.SalesInvoices.CustomerID
WHERE (dbo.SalesInvoices.SalesInvoiceID
SalesInvoiceID
CustomerName
InvoiceDate
Subtotal
TaxAmt
Freight
TotalDue
2
Glossy Bikes
12:00:00
AM
$
7,489.43
$
374.47
$
7.91
$7,871.81
Figure 1: Shows the InvoiceID number 2 of the customer who are affiliated to it including the
date, subtotal, tax, freight, and amount due.
Queries of the Body
SELECT TOP (100) PERCENT dbo.SalesInvoiceDetails.SalesInvoiceID,
dbo.Products.ProductName, dbo.SalesInvoiceDetails.OrderQty, dbo.Products.ListPrice,
dbo.SalesInvoiceDetails.LineTotal
FROM dbo.SalesInvoiceDetails INNER JOIN
dbo.Products ON dbo.SalesInvoiceDetails.ProductNumber =
dbo.Products.ProductNumber
WHERE (dbo.SalesInvoiceDetails.SalesInvoiceID = 2)
ORDER BY dbo.Products.ProductName
SalesInvoiceID
ProductName
OrderQty
ListPrice
LineTotal
2
LL Road Frame -
Black, 52
1
$
337.22
$
337.22
Touring-1000 Yellow,
