Chapter 4: reating Relational Database Queries: A Comprehensive Guide

Query Design View
- This is Microsoft's specific implementation of a Query-By-Example (QBE) query interface within Access.
- QBE interfaces are generally not standardized across different database products, meaning skills learned in one QBE interface may not directly transfer to another database system.
SQL View
- Most conventions of the SQL (Structured Query Language) language are standardized across various database products (e.g., MySQL, PostgreSQL, Oracle, SQL Server).
- This standardization is crucial as it allows users to apply their SQL query skills to database products beyond Microsoft Access, making them highly transferable and valuable.

SELECT Clause: Used to select data (columns) from a database.
- Example: SELECT * FROM Customers; This retrieves all columns (*) from the Customers table.
INNER JOIN: Used to combine rows from two or more tables based on a related column between them.
- It selects records that have matching values in both tables, effectively retrieving only the rows where the join condition is met.
WHERE Clause: Used to filter records based on specified conditions.
- It extracts only those records that fulfill a given condition.
- The WHERE clause can be combined with logical operators: AND, OR, NOT.
  - AND Operator: Displays a record only if all the conditions separated by AND are TRUE.
  - OR Operator: Displays a record if any of the conditions separated by OR are TRUE.
  - NOT Operator: Displays a record if the condition is FALSE.
BETWEEN Operator: Used to select values within a given range.
- The values can be numbers, text, or dates.
- It is inclusive, meaning the start and end values of the range are included in the result.

GROUP BY Clause: Groups rows that have the same values in one or more specified columns into summary rows.
- It is often used in conjunction with aggregate functions (e.g., SUM(), COUNT(), MAX(), MIN(), AVG()) to perform calculations on each group.
- Syntax: SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)

Objective: To list the invoice numbers, sale dates (SaleDate), and salespeople (SalesPerson) for all sales made to a specific customer, Lola Doyle.
Underlying Logic: This would involve selecting specific columns from the Sales table and potentially joining with a Customers table if customer name is not directly in Sales, then filtering records where CustomerName (or CustomerID linked to Lola Doyle) matches 'Lola Doyle'.

Objective: To list the sale date (SaleDate), description of the item (Description), and quantity (Quantity) for each transaction where refrigerators were sold in the month of October.
Skills Demonstrated: Use of SELECT for specific columns, FROM for relevant tables (Sales, Sales_Inventory, Inventory), and WHERE conditions combining item description and a date range using BETWEEN.
- Example SQL structure: SELECT Sales.SaleDate, Inventory.Description, Sales_Inventory.Quantity FROM Sales INNER JOIN Sales_Inventory ON Sales.SalesInvoiceID = Sales_Inventory.SalesInvoiceID INNER JOIN Inventory ON Sales_Inventory.ItemID = Inventory.ItemID WHERE Inventory.Description = 'Refrigerator' AND Sales.SaleDate BETWEEN #10/1/2021# AND #10/31/2021#; (Date format may vary by database).

Objective: Builds upon Query 2. The sales manager wants to know the total count (summation of quantity) of refrigerators sold specifically in October.
Skills Demonstrated: This involves applying an aggregate function, specifically SUM(), to the Quantity field, after filtering for refrigerators and the month of October.

Objective: To list the item description (Description) and the sum of quantities (SumOfQuantity) for inventory types that sold $2$ or more units in total during the month of October.
Skills Demonstrated: Use of GROUP BY on the Description column to group sales by item type, SUM() on Quantity, and then filtering these aggregated results (or groups) using a HAVING clause (e.g., HAVING SUM(Quantity) >= 2).

Objective: To list the sale date (SaleDate) and the calculated InvoiceTotal for each invoice generated in October.
Data Model Implied (from provided tables):
- Customers Table: CustomerID, CustomerName, Street, City, State
- Sales Table: SalesInvoiceID, SaleDate, SalesPerson, CustomerID
- Sales_Inventory Table (Junction Table): SalesInvoiceID, ItemID, Quantity, SoldPrice
- Inventory Table: ItemID, Description, Color, VendorID, QuantityOnHand, ListPrice
InvoiceTotal Calculation: The InvoiceTotal is derived from the product of Quantity and SoldPrice for each item within an invoice, and then summing these products for all items in that invoice: $\sum (Sales_Inventory.Quantity \times Sales_Inventory.SoldPrice)$
Skills Demonstrated: This requires selecting SaleDate and the calculated InvoiceTotal. It involves joining Sales and Sales_Inventory tables, applying an aggregate function (SUM) to calculate the total price per invoice, and then grouping by SalesInvoiceID and SaleDate to get an individual total for each invoice. The filtering condition for October dates would be applied to SaleDate.
- Example SQL structure: SELECT S.SaleDate, SUM(SI.Quantity * SI.SoldPrice) AS InvoiceTotal FROM Sales AS S INNER JOIN Sales_Inventory AS SI ON S.SalesInvoiceID = SI.SalesInvoiceID WHERE S.SaleDate BETWEEN #10/1/2021# AND #10/31/2021# GROUP BY S.SalesInvoiceID, S.SaleDate;