5. SQL (II)

Week 6: SQL (II) at the University of Calgary

Learning Objectives

  • Write single- and multiple-table queries using SQL commands.

  • Define three types of join commands and utilize SQL to write these commands.

  • Write non-correlated and correlated subqueries and understand the appropriate context for each.

Processing Multiple Tables (1 of 2)

  • Join: A relational operation combining two or more tables with a common domain into a single table or view.

  • Equi-join: A join based on equality between values in common columns; redundant common columns appear in the result.

  • Inner join: An equi-join that eliminates one of the duplicate columns in the result table.

Natural/Equi/Inner Join Examples

  • Given two tables, the information about salespersons that served at least one order is displayed. Columns include: Salesperson Name, Salesperson ID (Left and Right), Order ID, and Order Date.

SQL Syntax for Inner Join

SELECT SalespersonName, s.SalespersonID, o.SalesPersonID, OrderID, OrderDate 
FROM Salesperson_T s 
INNER JOIN Order_T o ON s.SalespersonID = o.SalesPersonID;

  • Alternative syntax may also utilize a WHERE clause:

SELECT SalespersonName, s.SalespersonID, o.SalesPersonID, OrderID, OrderDate 
FROM Salesperson_T s, Order_T o 
WHERE s.SalespersonID = o.SalesPersonID;

Exercise: SQL Queries

  1. Show all orders from customers in ‘NY’, ordered by the date of the order.

  2. Show all customers who made purchases in the year 2018.

Processing Multiple Tables (2 of 2)

  • Outer Join (LEFT, RIGHT, or FULL): Retrieves rows without matching values in common columns. Unlike inner joins, rows without matches are still included in the result.

Left Join / Right Join Examples

  • Given two tables, display all salespersons and list orders served, marking NULL for any without orders:

  • LEFT JOIN SQL example:

SELECT SalespersonName, s.SalespersonID, o.SalesPersonID, OrderID, OrderDate 
FROM Salesperson_T s 
LEFT JOIN Order_T o ON s.SalespersonID = o.SalesPersonID;

  • RIGHT JOIN SQL example:

SELECT SalespersonName, s.SalespersonID, o.SalesPersonID, OrderID, OrderDate 
FROM Salesperson_T s 
RIGHT JOIN Order_T o ON s.SalespersonID = o.SalesPersonID;

Exercises

  1. Show the list of Salespersons that have not served any order.

  2. Generate a list of Salespersons with the number of orders served.

Full Join Example

  • Lists all salespersons along with any orders served, including every order with no salesperson:

SELECT SalespersonName, s.SalespersonID, o.SalesPersonID, OrderID, OrderDate 
FROM Salesperson_T s 
FULL JOIN Order_T o ON s.SalespersonID = o.SalesPersonID;

Multi-table Join Example

  • A complex query combining multiple tables:

SELECT c.CustomerName, o.OrderDate, p.ProductDescription 
FROM Customer_T c 
INNER JOIN Order_T o ON c.CustomerId = o.CustomerID 
INNER JOIN OrderLine_T ol ON o.OrderID = ol.OrderID 
INNER JOIN Product_T p ON ol.ProductID = p.ProductID 
WHERE p.ProductDescription LIKE '%table%';

  • This query lists customers who bought products with 'table' in the description.

GROUP BY with Aggregation

  • Find summaries of sales grouped by product and year and average product prices. Example:

SELECT ProductLineName, COUNT(ProductID) AS NbrOfProducts, AVG(ProductPrice) AS AvgPrice 
FROM Product_T 
GROUP BY ProductLineName 
HAVING AvgPrice > 200;

Self Join Example

  • Querying relationships within the same table:

SELECT s.EmployeeID AS [Supervisor ID], s.EmployeeName AS [Supervisor Name], e.EmployeeID, e.EmployeeName 
FROM Employee_T s 
INNER JOIN Employee_T e ON e.EmployeeSupervisor = s.EmployeeID;

  • Displays each supervisor with the employees they supervise.

Subqueries

  • Subquery: An inner query placed within an outer query used for filtering or returning specific datasets.

  • Non-correlated Subqueries: Executed once per outer query.

  • Correlated Subqueries: Executed for each row returned by the outer query.

Example of Subquery in WHERE Clause

  • Querying customer details for a specific order:

SELECT CustomerName, CustomerAddress 
FROM Customer_T 
WHERE CustomerID = (SELECT CustomerID FROM Order_T WHERE OrderID = 34);

Exercise Examples for Subqueries

  1. Find all employees sharing the same supervisor as Laura:

SELECT * FROM Employee_T 
WHERE EmployeeSupervisor = (SELECT EmployeeSupervisor FROM Employee_T WHERE EmployeeName LIKE 'laura%');

  1. Show products with prices above average:

SELECT * FROM Product_T WHERE ProductPrice > (SELECT AVG(ProductPrice) FROM Product_T);

Derived Tables

  • Create temporary tables through subqueries for analysis, such as finding orders with specific products. Example:

SELECT t3.OrderID, t3.OrderDate 
FROM (SELECT o.OrderID, o.OrderDate FROM Order_T o, OrderLine_T ol WHERE o.OrderID = ol.OrderID AND ol.ProductID = 3) t3, 
     (SELECT o.OrderID, o.OrderDate FROM Order_T o, OrderLine_T ol WHERE o.OrderID = ol.OrderID AND ol.ProductID = 4) t4 
WHERE t3.OrderID = t4.OrderID;

UNION, INTERSECT, EXCEPT Examples

  • Querying products within orders combining multiple conditions. Examples include finding orders with various product combinations influenced by UNION, INTERSECT, and EXCEPT:

-- Union example
SELECT OrderID FROM OrderLine_T WHERE ProductID IN (3)
UNION
SELECT OrderID FROM OrderLine_T WHERE ProductID IN (4);
-- Intersect example
SELECT OrderID FROM OrderLine_T WHERE ProductID = 3
INTERSECT
SELECT OrderID FROM OrderLine_T WHERE ProductID = 4;
-- Except example
SELECT OrderID FROM OrderLine_T WHERE ProductID = 3
EXCEPT
SELECT OrderID FROM OrderLine_T WHERE ProductID = 4;