ROYAL UNIVERSITY OF PHNOM PENH
DATABASE II
Chapter 10
How to code subqueries (LAB)
Lecturer: Mr. Chhim Bunchhun,
[Link]@[Link], 093 222 380
A subquery in the WHERE clause
SELECT invoice_number, invoice_date, invoice_total
FROM invoices
WHERE invoice_total >
(SELECT AVG(invoice_total)FROM invoices)
ORDER BY invoice_total
The value returned by the subquery
1879.741316
•The result set
•(21 rows)
Chapter 10: How to code subqueries 2
A query that uses an inner join
SELECT invoice_number, invoice_date, invoice_total
FROM invoices JOIN vendors
ON invoices.vendor_id = vendors.vendor_id
WHERE vendor_state = 'CA'
ORDER BY invoice_date
•(40 rows)
The result set
Chapter 10: How to code subqueries 3
The same query restated with a subquery
SELECT invoice_number, invoice_date, invoice_total
FROM invoices
WHERE vendor_id IN
(SELECT vendor_id
FROM vendors
WHERE vendor_state = 'CA')
ORDER BY invoice_date
The same result set
•(40 rows)
Chapter 10: How to code subqueries 4
The syntax of a WHERE clause that uses an IN phrase
A query that gets vendors without invoices
SELECT vendor_id, vendor_name, vendor_state
FROM vendors
WHERE vendor_id NOT IN
(SELECT DISTINCT vendor_id
FROM invoices)
ORDER BY vendor_id
Chapter 10: How to code subqueries 5
The query restated without a subquery
SELECT v.vendor_id, vendor_name, vendor_state
FROM vendors v LEFT JOIN invoices i
ON v.vendor_id = i.vendor_id
WHERE i.vendor_id IS NULL
ORDER BY v.vendor_id
Chapter 10: How to code subqueries 6
The syntax of a WHERE clause that uses a comparison operator
SELECT invoice_number, invoice_date,
invoice_total - payment_total –
credit_total
AS balance_due
FROM invoices
WHERE invoice_total - payment_total –credit_total > 0
AND invoice_total - payment_total – credit_total <
(
SELECT AVG(invoice_total - payment_total –
credit_total)
FROM invoices
WHERE invoice_total - payment_total – credit_total
> 0
)
ORDER BY invoice_total DESC
Chapter 10: How to code subqueries 7
A query that uses ALL
SELECT vendor_name, invoice_number, invoice_total
FROM invoices i JOIN vendors v ON i.vendor_id = v.vendor_id
WHERE invoice_total > ALL
(SELECT invoice_total
FROM invoices
WHERE vendor_id = 34)
ORDER BY vendor_name
The result of the subquery
The result set
•(25 rows)
Chapter 10: How to code subqueries 8
A query that uses ANY
SELECT vendor_name, invoice_number, invoice_total
FROM vendors JOIN invoices
ON vendors.vendor_id = invoices.vendor_id
WHERE invoice_total < ANY
(SELECT invoice_total
FROM invoices
The result of the subquery with invoice totals for vendor 115
WHERE vendor_id = 115)
The result set for invoices with totals less than any invoice for vendor 115
(17 rows)
Chapter 10: How to code subqueries 9
A subquery in the SELECT clause
SELECT vendor_name,
(SELECT MAX(invoice_date) FROM invoices
WHERE vendor_id = vendors.vendor_id) AS latest_inv
FROM vendors
ORDER BY latest_inv DESC
The result set
•(122 rows)
Chapter 10: How to code subqueries 10
The same query restated using a join
SELECT vendor_name, MAX(invoice_date) AS latest_inv
FROM vendors v
LEFT JOIN invoices i ON v.vendor_id = i.vendor_id
GROUP BY vendor_name
ORDER BY latest_inv DESC
The same result set
•(122 rows)
Chapter 10: How to code subqueries 11
A query that uses an inline view
SELECT vendor_state,
MAX(sum_of_invoices) AS max_sum_of_invoices
FROM
(
SELECT vendor_state, vendor_name,
SUM(invoice_total) AS sum_of_invoices
FROM vendors v JOIN invoices i
ON v.vendor_id = i.vendor_id
GROUP BY vendor_state, vendor_name
) t
GROUP BY vendor_state
ORDER BY vendor_state
Chapter 10: How to code subqueries 12
A complex query that uses three subqueries
SELECT t1.vendor_state, vendor_name, t1.sum_of_invoices
FROM
(
-- invoice totals by vendor
SELECT vendor_state, vendor_name,
SUM(invoice_total) AS sum_of_invoices
FROM vendors v JOIN invoices i
ON v.vendor_id = i.vendor_id
GROUP BY vendor_state, vendor_name
) t1
Chapter 10: How to code subqueries 13
A complex query (continued)
JOIN
(
-- top invoice totals by state
SELECT vendor_state, MAX(sum_of_invoices) AS sum_of_invoices
FROM
(
-- invoice totals by vendor
SELECT vendor_state, vendor_name, SUM(invoice_total) AS sum_of_invoices
FROM vendors v JOIN invoices i
ON v.vendor_id =i.vendor_id
GROUP BY vendor_state, vendor_name
) t2
GROUP BY vendor_state
) t3
ON t1.vendor_state = t3.vendor_state AND
t1.sum_of_invoices = t3.sum_of_invoices
ORDER BY vendor_state
Chapter 10: How to code subqueries 14
Two CTEs and a query that uses them
WITH summary AS
(
SELECT vendor_state, vendor_name,
SUM(invoice_total) AS sum_of_invoices
FROM vendors v JOIN invoices i
ON v.vendor_id = i.vendor_id
GROUP BY vendor_state, vendor_name
),
top_in_state AS
(
SELECT vendor_state,
MAX(sum_of_invoices) AS
sum_of_invoices
FROM summary
GROUP BY vendor_state
)
Chapter 10: How to code subqueries 15
Two CTEs and a query that uses them (continued)
SELECT summary.vendor_state, summary.vendor_name,
top_in_state.sum_of_invoices
FROM summary JOIN top_in_state
ON summary.vendor_state = top_in_state.vendor_state AND
summary.sum_of_invoices = top_in_state.sum_of_invoices
ORDER BY summary.vendor_state
The result set
•(10 rows)
Chapter 10: How to code subqueries 16
