SQL - DML - 3

Mark Fontenot, PhD

Northeastern University

1

Quick Review

2

SELECT <list of columns>

FROM <table name> [JOIN <table name> � ON <join condition> [...]]

[WHERE <condition list>]

[ORDER BY <column [ASC|DESC]>,[...]]

General�Form

Another Join Example - Process

Northwind has been having some problems with Speedy Express shipping company… they don’t seem to be too speedy. Provide a list of all orders shipped via Speedy Express and include the order id, customer name, order date, and shipped date for all orders in 2022.

3

Maybe you think to yourself… “Where do I start?”

Another Join Example - Process

Northwind has been having some problems with Speedy Express shipping company… they don’t seem to be too speedy. Provide a list of all orders shipped via Speedy Express and include the order id, customer name, order date, and shipped date for all orders in 2022.

4

1. Think about what columns are needed in the final result.

Another Join Example - Process

Northwind has been having some problems with Speedy Express shipping company… they don’t seem to be too speedy. Provide a list of all orders shipped via Speedy Express and include the order id, customer name, order date, and shipped date for all orders in 2022.

5

2. What tables are those attributes in?

- Minimally, your FROM clause will need to include those tables.

- Are there other tables needed that link the above?

Tables:�- Shippers

- Orders

- Customers

Another Join Example - Process

6

3. What join conditions do I need for the tables?

- Can I natural join all of them?

- If no, what attributes link the different pairs of tables?

- Since Shippers and Orders do not have a common attribute name, we can’t use a Natural Join.

​

- Must use a JOIN … ON

​

Another Join Example - Process

Northwind has been having some problems with Speedy Express shipping company… they don’t seem to be too speedy. Provide a list of all orders shipped via Speedy Express and include the order id, customer name, order date, and shipped date for all orders in 2022.

7

4. What, if any, filters are needed for the rows?

Putting it all together

8

1. Think about what columns are needed in the final result.

2. What tables are those attributes in?

3. What join conditions do I need for the tables?

4. What, if any, filters are needed for the rows?

SELECT o.orderid, c.CompanyName, o.OrderDate, o.ShippedDate

FROM Shippers s JOIN Orders o ON s.shipperid = o.shipvia

JOIN Customers c ON o.customerid = c.customerid

WHERE s.CompanyName = ‘Speedy Express’

AND o.OrderDate >= ‘2022-01-01’

What’s Wrong With the Following?

Northwind has been having some problems with Speedy Express shipping company… they don’t seem to be too speedy. Provide a list of all orders shipped via Speedy Express and include the order id, customer name, order date, and shipped date for all orders in 2022.

9

SELECT o.orderid,

c.CompanyName,

o.OrderDate,

o.ShippedDate

FROM shippers s

JOIN orders o ON s.shipperid = o.shipvia

NATURAL JOIN customers c

WHERE s.companyName = 'Speedy Express'

AND o.orderdate >= '2022-01-01';

SELECT DISTINCT

SELECT DISTINCT removes duplicate rows from result.

10

SELECT DISTINCT ShipCity, ShipCountry

FROM Orders

WHERE shipregion = 'Western Europe'

ORDER BY shipcity, shipcountry;

SELECT ShipCity, ShipCountry

from Orders

where shipregion = 'Western Europe'

order by shipcity, shipcountry;

versus

Aggregate Functions

- Return some function produced from processing all rows in the result set of a query

- When used alone, resultset will be a single row

- Common aggregate functions:

- MAX

- MIN

- SUM

- AVG

- COUNT

11

SELECT MAX(UnitPrice)

FROM Products;

​

SELECT COUNT(*)

FROM Products;

Aggregate Functions

- Return some function produced from processing all rows in the result set of a query

- When used alone, resultset will be a single row

- Common aggregate functions:

- MAX

- MIN

- SUM

- AVG

- COUNT

12

SELECT MAX(UnitPrice)

FROM Products;

​

SELECT COUNT(*)

FROM Products;

Aggregate Functions

What’s the average price of all products we sell?

​

What’s the average price of all non-discontinued products we sell?

​

What is the date of the first order we ever processed?

​

How many orders has “Alfreds Futterkiste” placed with us?

13

Applying Aggregates to Groups

- The GROUP BY clause allows you to define subgroups for the application of an aggregate function.

- The GROUP BY list should be a subset of the SELECT attributes.

14

SELECT [DISTINCT] <attribute list>

FROM <table list>

[WHERE <condition list>]

[GROUP BY <attribute list>]

[ORDER BY <attribute-order list>];

Group By -

Intuition

Problem: Provide the average GPA for all students in each year of their education.

15

Group By -

Intuition

Problem: Provide the average GPA for all students in each year of their education.

… GROUP BY Year

16

Average these vals

​

​

Average these vals…

Sample Group By

Determine the average price of products from each of our suppliers.

17

You can apply multiple agg functions to a group in 1 stmt.

For each category of products, give the name of the product category, min product unit price, max product unit price, and average product unit price.

​

​

18

SELECT A, MAX(B), MIN(B)

FROM table

GROUP BY A;

SELECT categoryName, min(unitPrice), max(unitPrice), avg(unitPrice)

FROM Categories C JOIN Products P ON C.categoryID = P.categoryID

GROUP BY categoryName;

Group By - Your Turn

For each employee, provide the total number of orders they’ve done, the date of their first order, and the date of their most recent order. Make sure to include the employee’s ID number, first name, and last name.

19

SELECT employeeid, firstname, lastname,

count(orderid) as TotalOrders,

min(orderdate) as FirstOrder,

max(orderdate) as lastorder

FROM Employees NATURAL JOIN Orders

GROUP BY employeeid, firstname, lastname;

HAVING Clause

- used in conjunction with GROUP BY

- a set of boolean expressions that are evaluated for each group

- If true, group is returned. If false, group is discarded.

20

Provide a list of all regions of the world where Northwind has more than 10 customers.

SELECT region,

count(customerid) as numCustomers

FROM Customers

GROUP BY region

HAVING count(customerid) >= 10

HAVING

21

SELECT <list of columns>

FROM <table name> [JOIN <table name> � ON <join condition> [...]]

[WHERE <condition list>]

[GROUP BY <attribute list>]

[HAVING <group conditions>]

[ORDER BY <column [ASC|DESC]>,[...]]

General�Form

Challenge Problem

Provide a list of all customers whose average order total is below the average for all companies in their same region.

22