Exercises & Answer Key
The exercise statement is open to read freely. The answer key and detailed explanations are reserved for students currently enrolled in the IT004 practicum class and require an access code from the instructor.
EXERCISE
Sales Management - Questions 26 ➜ 53
Following the Week 3 guide, use Microsoft SQL Server to complete Part III - questions 26 through 53. Attempt each one yourself before checking hints/answers. Full question details are in the PDF below.
<MSSV>_<HoVaTen>_BTTH3.sql (MSSV is your student ID, HoVaTen is your full name).- Find the invoice numbers that include all products manufactured in Singapore.
- Find the invoice numbers in 2006 that include all products manufactured in Singapore.
- How many invoices were not purchased by a registered member customer?
- How many distinct products were sold in 2006?
- What are the highest and lowest invoice totals?
- What is the average value of all invoices sold in 2006?
- Calculate the sales revenue for 2006.
- Find the invoice number with the highest total value in 2006.
- Find the full name of the customer who purchased the invoice with the highest total value in 2006.
- Print the top 3 customers (MAKH, HOTEN) sorted by sales in descending order.
- Print the list of products (MASP, TENSP) priced at one of the 3 highest price levels.
- Print the list of products (MASP, TENSP) manufactured in "Thai Lan" priced at one of the 3 highest price levels (among all products).
- Print the list of products (MASP, TENSP) manufactured in "Trung Quoc" priced at one of the 3 highest price levels (among products manufactured in "Trung Quoc").
- Print the list of customers ranked in the top 3 (ranked by sales).
- Calculate the total number of products manufactured in "Trung Quoc".
- Calculate the total number of products for each country of manufacture.
- For each country of manufacture, find the highest, lowest, and average selling price of its products.
- Calculate the daily sales revenue.
- Calculate the total quantity sold for each product in October 2006.
- Calculate the monthly sales revenue for each month of 2006.
- Find invoices that include at least 4 different products.
- Find invoices that include 3 products manufactured in "Viet Nam" (3 distinct products).
- Find the customer (MAKH, HOTEN) with the highest number of purchases.
- Which month of 2006 had the highest sales revenue?
- Find the product (MASP, TENSP) with the lowest total quantity sold in 2006.
- For each country of manufacture, find the product (MASP, TENSP) with the highest selling price.
- Find the country of manufacture that produces at least 3 products with different selling prices.
- Among the top 10 customers by sales, find the customer with the highest number of purchases.
ANSWER
View answer
Protected content
Enter the access code to view the Week 3 answer.
Access code provided by the instructor.
Answers for Question 26 through Question 53. Retype each query in SSMS so it actually sticks - the Copy button here is just for show.
QUESTION 26 · RELATIONAL DIVISION
Exercise: Find the invoice numbers that include all products manufactured in Singapore.
SELECT hd.SOHD
FROM HOADON hd
WHERE NOT EXISTS (
SELECT * FROM SANPHAM sp
WHERE sp.NUOCSX = 'Singapore'
AND NOT EXISTS (
SELECT * FROM CTHD ct
WHERE ct.MASP = sp.MASP AND ct.SOHD = hd.SOHD
)
);Relational division: find invoices where there is no Singapore product that is missing from that invoice's CTHD.
SQL Server has no direct division operator, so you have to reason in reverse using nested NOT EXISTS - this is the standard pattern for "bought all/taught all" type questions.
Alternative approach
SELECT hd.SOHD
FROM HOADON hd
JOIN CTHD ct ON hd.SOHD = ct.SOHD
JOIN SANPHAM sp ON sp.MASP = ct.MASP
WHERE sp.NUOCSX = 'Singapore'
GROUP BY hd.SOHD
HAVING COUNT(DISTINCT sp.MASP) = (
SELECT COUNT(*) FROM SANPHAM WHERE NUOCSX = 'Singapore'
);QUESTION 27 · DIVISION + WHERE
Exercise: Find the invoice numbers in 2006 that include all products manufactured in Singapore.
SELECT hd.SOHD
FROM HOADON hd
WHERE YEAR(hd.NGHD) = 2006
AND NOT EXISTS (
SELECT * FROM SANPHAM sp
WHERE sp.NUOCSX = 'Singapore'
AND NOT EXISTS (
SELECT * FROM CTHD ct
WHERE ct.MASP = sp.MASP AND ct.SOHD = hd.SOHD
)
);Exactly the same as Question 26, just adding YEAR(NGHD) = 2006 on the outer invoice.
The year condition only applies to the invoice being checked (the outer table), and has nothing to do with the division logic inside - so it goes straight into the main query's WHERE.
QUESTION 28 · IS NULL
Exercise: How many invoices were not purchased by a registered member customer?
SELECT COUNT(*) AS SLHoaDon
FROM HOADON
WHERE MAKH IS NULL;MAKH IS NULL exactly matches invoices with no member customer attached.
You can't write MAKH = NULL - in SQL, an equality comparison against NULL always evaluates to unknown, so IS NULL is required.
QUESTION 29 · COUNT DISTINCT
Exercise: How many distinct products were sold in 2006?
SELECT COUNT(DISTINCT ct.MASP) AS SLSanPham
FROM CTHD ct
JOIN HOADON hd ON ct.SOHD = hd.SOHD
WHERE YEAR(hd.NGHD) = 2006;COUNT(DISTINCT ...) counts distinct values, removing duplicates.
A product can appear in multiple CTHD rows (across different invoices) - a plain COUNT(*) would count duplicates, so DISTINCT on MASP is required.
QUESTION 30 · MAX/MIN
Exercise: What are the highest and lowest invoice totals?
SELECT MAX(TRIGIA) AS TriGiaCaoNhat, MIN(TRIGIA) AS TriGiaThapNhat
FROM HOADON;MAX/MIN compute directly over the whole TRIGIA column - no GROUP BY needed since only one result row is expected.
The question only asks for the highest/lowest values (two numbers), not which invoice - so there's no need for a JOIN or any extra filtering.
QUESTION 31 · AVG + WHERE
Exercise: What is the average value of all invoices sold in 2006?
SELECT AVG(TRIGIA) AS TriGiaTrungBinh
FROM HOADON
WHERE YEAR(NGHD) = 2006;AVG computes the average over the rows left after WHERE has filtered by year.
WHERE runs before aggregate functions, so filtering the year here is correct - no GROUP BY/HAVING needed since there's no grouping involved.
QUESTION 32 · SUM
Exercise: Calculate the sales revenue for 2006.
SELECT SUM(TRIGIA) AS DoanhThu
FROM HOADON
WHERE YEAR(NGHD) = 2006;SUM(TRIGIA) adds up the value of every remaining invoice after the year filter.
"Revenue" means the total of invoice values, unlike "average" in Question 31 - just swap AVG for SUM, everything else stays the same.
QUESTION 33 · SUBQUERY MAX SCOPED BY YEAR
Exercise: Find the invoice number with the highest total value in 2006.
SELECT SOHD
FROM HOADON
WHERE YEAR(NGHD) = 2006
AND TRIGIA = (
SELECT MAX(TRIGIA) FROM HOADON WHERE YEAR(NGHD) = 2006
);The subquery computes MAX(TRIGIA) within 2006 only, then the outer query finds the invoice whose value equals that number.
Both the subquery and the outer query must filter by year 2006 - filtering only one of them risks matching an invoice from a different year that happens to tie the 2006 maximum.
Alternative approach
SELECT TOP 1 WITH TIES SOHD
FROM HOADON
WHERE YEAR(NGHD) = 2006
ORDER BY TRIGIA DESC;QUESTION 34 · JOIN + TOP WITH TIES
Exercise: Find the full name of the customer who purchased the invoice with the highest total value in 2006.
SELECT TOP 1 WITH TIES hd.MAKH, kh.HOTEN, hd.SOHD, hd.TRIGIA
FROM KHACHHANG kh
JOIN HOADON hd ON kh.MAKH = hd.MAKH
WHERE YEAR(hd.NGHD) = 2006
ORDER BY hd.TRIGIA DESC;TOP 1 WITH TIES ... ORDER BY TRIGIA DESC fetches the invoice(s) with the highest value, joined with customer info.
WITH TIES guarantees that if multiple invoices are tied for the highest value, all of them are returned - unlike plain TOP 1, which only returns exactly one row.
QUESTION 35 · TOP + ORDER BY
Exercise: Print the top 3 customers (MAKH, HOTEN) sorted by sales in descending order.
SELECT TOP 3 MAKH, HOTEN
FROM KHACHHANG
ORDER BY DOANHSO DESC;TOP 3 takes exactly the first 3 rows after sorting descending by DOANHSO.
The question asks for exactly "the top 3 customers" (not "top 3 ranks" like Question 39), so plain TOP 3 works - no need for WITH TIES.
QUESTION 36 · DISTINCT TOP INSIDE IN
Exercise: Print the list of products (MASP, TENSP) priced at one of the 3 highest price levels.
SELECT MASP, TENSP
FROM SANPHAM
WHERE GIA IN (
SELECT DISTINCT TOP 3 GIA FROM SANPHAM ORDER BY GIA DESC
);SELECT DISTINCT TOP 3 GIA ... ORDER BY GIA DESC gets the 3 highest, distinct price levels (not 3 products).
Many products can share the same price - without DISTINCT, "the 3 highest price levels" might only reflect 1-2 actual price levels due to duplicates inside TOP 3.
QUESTION 37 · Filtering after computing the 3 levels
Exercise: Print the list of products (MASP, TENSP) manufactured in "Thai Lan" priced at one of the 3 highest price levels (among all products).
SELECT MASP, TENSP
FROM SANPHAM
WHERE NUOCSX = 'Thai Lan'
AND GIA IN (
SELECT DISTINCT TOP 3 GIA FROM SANPHAM ORDER BY GIA DESC
);Same as Question 36, adding NUOCSX = 'Thai Lan' in the outer query.
The subquery computes the 3 highest price levels across all products (no country filter) - because the question says "among all products," so the Thailand filter only applies outside.
QUESTION 38 · Subquery also scoped by country
Exercise: Print the list of products (MASP, TENSP) manufactured in "Trung Quoc" priced at one of the 3 highest price levels (among products manufactured in "Trung Quoc").
SELECT MASP, TENSP
FROM SANPHAM
WHERE NUOCSX = 'Trung Quoc'
AND GIA IN (
SELECT DISTINCT TOP 3 GIA FROM SANPHAM
WHERE NUOCSX = 'Trung Quoc'
ORDER BY GIA DESC
);Unlike Question 37, the subquery here also filters NUOCSX = 'Trung Quoc' - the 3 highest price levels are computed only within the China group.
The question explicitly says "among products manufactured in Trung Quoc" (unlike Question 37's "among all products") - read the parenthetical carefully to know whether the subquery needs its own filter.
QUESTION 39 · TOP WITH TIES by sales
Exercise: Print the list of customers ranked in the top 3 (ranked by sales).
SELECT TOP 3 WITH TIES MAKH, HOTEN, DOANHSO
FROM KHACHHANG
ORDER BY DOANHSO DESC;TOP 3 WITH TIES returns every customer within the top 3 sales levels, including all customers tied at the same level.
Unlike Question 35 ("top 3 customers" - exactly 3 rows), this is "top 3 ranks" - if two customers tie for rank 3, both must appear, which requires WITH TIES.
Alternative approach
SELECT MAKH, HOTEN, DOANHSO
FROM KHACHHANG
WHERE DOANHSO IN (
SELECT DISTINCT TOP 3 DOANHSO FROM KHACHHANG ORDER BY DOANHSO DESC
);QUESTION 40 · COUNT + WHERE
Exercise: Calculate the total number of products manufactured in "Trung Quoc".
SELECT COUNT(*) AS SLSanPham
FROM SANPHAM
WHERE NUOCSX = 'Trung Quoc';COUNT(*) counts the rows left after WHERE filters the manufacturing country.
This is a simple single-condition count - no GROUP BY needed since there's no grouping involved.
QUESTION 41 · GROUP BY + COUNT
Exercise: Calculate the total number of products for each country of manufacture.
SELECT NUOCSX, COUNT(*) AS SLSanPham
FROM SANPHAM
GROUP BY NUOCSX;GROUP BY NUOCSX groups products by country, COUNT(*) counts the products in each group.
"For each country of manufacture" is a clear signal for GROUP BY - unlike Question 40, which asks about only one specific country (China).
QUESTION 42 · GROUP BY with multiple aggregates
Exercise: For each country of manufacture, find the highest, lowest, and average selling price of its products.
SELECT NUOCSX, MAX(GIA) AS GiaCaoNhat, MIN(GIA) AS GiaThapNhat, AVG(GIA) AS GiaTrungBinh
FROM SANPHAM
GROUP BY NUOCSX;Multiple aggregate functions (MAX, MIN, AVG) can be computed together in one SELECT, as long as they share the same GROUP BY.
No need to write 3 separate queries - each aggregate function computes independently within each NUOCSX group, in a single pass over the data.
QUESTION 43 · GROUP BY date
Exercise: Calculate the daily sales revenue.
SELECT NGHD, SUM(TRIGIA) AS DoanhThu
FROM HOADON
GROUP BY NGHD;GROUP BY NGHD groups invoices from the same date together; SUM(TRIGIA) totals the value for that date.
"Daily" means splitting the result by each distinct NGHD value - the core meaning of GROUP BY.
QUESTION 44 · GROUP BY + multiple WHERE conditions
Exercise: Calculate the total quantity sold for each product in October 2006.
SELECT ct.MASP, SUM(ct.SL) AS SLSP
FROM CTHD ct
JOIN HOADON hd ON ct.SOHD = hd.SOHD
WHERE YEAR(hd.NGHD) = 2006 AND MONTH(hd.NGHD) = 10
GROUP BY ct.MASP;WHERE filters down to October 2006 first, then GROUP BY MASP totals the quantity per product.
Quantity lives in CTHD but the date lives in HOADON - the two tables must be JOINed before you can filter by the right time range.
QUESTION 45 · GROUP BY month
Exercise: Calculate the monthly sales revenue for each month of 2006.
SELECT MONTH(NGHD) AS Thang, SUM(TRIGIA) AS DoanhThu
FROM HOADON
WHERE YEAR(NGHD) = 2006
GROUP BY MONTH(NGHD);You can GROUP BY directly on the result of a function (MONTH(NGHD)), not just a plain column name.
The result needs to be grouped by month (1-12), not by each individual date like Question 43 - MONTH() "rounds" the date down to its month before grouping.
QUESTION 46 · HAVING COUNT DISTINCT
Exercise: Find invoices that include at least 4 different products.
SELECT hd.SOHD, COUNT(DISTINCT ct.MASP) AS SLSP
FROM HOADON hd
JOIN CTHD ct ON hd.SOHD = ct.SOHD
GROUP BY hd.SOHD
HAVING COUNT(DISTINCT ct.MASP) >= 4;HAVING COUNT(DISTINCT MASP) >= 4 keeps only invoices with 4 or more distinct product codes.
"4 different products" requires DISTINCT - with plain COUNT(*), one product bought in a large quantity could wrongly be counted as "many products."
QUESTION 47 · 3-table JOIN + HAVING
Exercise: Find invoices that include 3 products manufactured in "Viet Nam" (3 distinct products).
SELECT hd.SOHD, COUNT(DISTINCT ct.MASP) AS SLSP
FROM HOADON hd
JOIN CTHD ct ON hd.SOHD = ct.SOHD
JOIN SANPHAM sp ON sp.MASP = ct.MASP
WHERE sp.NUOCSX = 'Viet Nam'
GROUP BY hd.SOHD
HAVING COUNT(DISTINCT ct.MASP) >= 3;Same as Question 46, with an added JOIN SANPHAM to restrict to products manufactured in "Viet Nam" before counting.
The WHERE filter on manufacturing country must run before GROUP BY, so COUNT(DISTINCT ...) only counts Vietnamese products, not products from other countries on the same invoice.
QUESTION 48 · TOP WITH TIES + COUNT
Exercise: Find the customer (MAKH, HOTEN) with the highest number of purchases.
SELECT TOP 1 WITH TIES hd.MAKH, kh.HOTEN, COUNT(DISTINCT hd.SOHD) AS SLMH
FROM HOADON hd
JOIN KHACHHANG kh ON hd.MAKH = kh.MAKH
GROUP BY hd.MAKH, kh.HOTEN
ORDER BY SLMH DESC;"Number of purchases" means the count of distinct invoices for that customer, so group by customer and count SOHD.
TOP 1 WITH TIES guarantees that if multiple customers tie for the most purchases, all of them are listed - not just one chosen arbitrarily.
QUESTION 49 · GROUP BY + TOP WITH TIES
Exercise: Which month of 2006 had the highest sales revenue?
SELECT TOP 1 WITH TIES MONTH(NGHD) AS Thang, SUM(TRIGIA) AS DoanhThu
FROM HOADON
WHERE YEAR(NGHD) = 2006
GROUP BY MONTH(NGHD)
ORDER BY DoanhThu DESC;Compute revenue per month exactly like Question 45, then ORDER BY DoanhThu DESC + TOP 1 WITH TIES to get the highest month.
Revenue must be computed per month first (GROUP BY) before you can compare which month is highest - you can't just take MAX directly since you also need to know which month it was.
QUESTION 50 · ORDER BY ASC
Exercise: Find the product (MASP, TENSP) with the lowest total quantity sold in 2006.
SELECT TOP 1 WITH TIES ct.MASP, sp.TENSP, SUM(ct.SL) AS SLSP
FROM CTHD ct
JOIN HOADON hd ON ct.SOHD = hd.SOHD
JOIN SANPHAM sp ON sp.MASP = ct.MASP
WHERE YEAR(hd.NGHD) = 2006
GROUP BY ct.MASP, sp.TENSP
ORDER BY SLSP ASC;Same structure as Question 44 (total quantity per product), just switching to ORDER BY ... ASC (ascending) to get the lowest.
ASC is ORDER BY's default direction, but it's still worth writing explicitly for readability - especially when questions keep switching between "highest" and "lowest."
QUESTION 51 · Correlated subquery
Exercise: For each country of manufacture, find the product (MASP, TENSP) with the highest selling price.
SELECT sp.NUOCSX, sp.MASP, sp.TENSP
FROM SANPHAM sp
WHERE sp.GIA = (
SELECT MAX(sp1.GIA)
FROM SANPHAM sp1
WHERE sp1.NUOCSX = sp.NUOCSX
);A correlated subquery: for each outer row sp, the inner subquery recomputes MAX(GIA) scoped to that same row's NUOCSX.
Unlike Question 42 (which only needs an aggregate number), this question also needs the product's MASP/TENSP - plain GROUP BY can't return the product name alongside a MAX, so a correlated subquery is needed.
QUESTION 52 · HAVING COUNT DISTINCT
Exercise: Find the country of manufacture that produces at least 3 products with different selling prices.
SELECT NUOCSX, COUNT(DISTINCT GIA) AS GiaBanKhacNhau
FROM SANPHAM
GROUP BY NUOCSX
HAVING COUNT(DISTINCT GIA) >= 3;HAVING COUNT(DISTINCT GIA) >= 3 keeps only countries with 3 or more distinct price levels.
Note that DISTINCT must be inside HAVING too, not just in the displayed column name - if HAVING only said COUNT(GIA) >= 3 (missing DISTINCT), it would be counting "at least 3 products" instead of "3 different prices," which doesn't match the question.
QUESTION 53 · Subquery with TOP WITH TIES + GROUP BY
Exercise: Among the top 10 customers by sales, find the customer with the highest number of purchases.
SELECT TOP 1 WITH TIES hd.MAKH, kh.HOTEN, COUNT(hd.SOHD) AS SLMH
FROM KHACHHANG kh
JOIN HOADON hd ON kh.MAKH = hd.MAKH
WHERE hd.MAKH IN (
SELECT TOP 10 WITH TIES MAKH FROM KHACHHANG ORDER BY DOANHSO DESC
)
GROUP BY hd.MAKH, kh.HOTEN
ORDER BY SLMH DESC;The subquery first narrows down to the top 10 customers by sales; the outer query then counts purchases only within that group of 10.
This is a two-layer problem: "filter the top 10" and "find who purchased the most" are two different criteria (sales vs. purchase count) - they can't be combined into a single ORDER BY, so a separate subquery is required.