Exercises & Answer Key

WHERE filters on a single equality condition on the NUOCSX column.

The exercise only asks for a single condition, so an equality comparison is enough - no more complex technique is needed.

IN checks whether a value is present in a given list.

IN is more compact when comparing 1 column to several discrete values - equivalent to but more readable than a chain of ORs.

LIKE matches a string pattern; % stands in for any sequence of characters (including none) between "B" and "01".

The exercise requires a condition on the first/last characters, not an exact equality match, so LIKE must be used instead of =.

BETWEEN a AND b is equivalent to >= a AND <= b, checking whether a value falls within a closed range.

BETWEEN is more concise when filtering a continuous range like GIA from 30,000 to 40,000.

Combines IN (multiple countries of manufacture) and BETWEEN (price range) with AND.

The 2 independent conditions (country of manufacture and price range) must both be true at the same time, so they're joined with AND; each condition is itself shortened with IN/BETWEEN instead of a longer OR/comparison chain.

Compares the NGHD column (date type) against a list of 2 specific date values.

IN replaces OR when comparing the same column against several discrete values - this applies to date columns too, not just strings or numbers.

YEAR()/MONTH() extract the year/month from a date value; ORDER BY can sort by multiple columns, each with its own ascending/descending direction.

Filtering "January 2007" requires splitting out the year and month since NGHD stores a full date. Sorting by 2 criteria in opposite directions (date ascending, total descending) means ASC/DESC must be declared separately for each column within the same ORDER BY.

JOIN combines KHACHHANG with HOADON through MAKH to get the HOTEN of customers who have an invoice on that exact date.

The data needed (HOTEN) lives in KHACHHANG, while the filter condition (NGHD) lives in HOADON - the 2 tables must be JOINed to query both at once.

JOIN HOADON with NHANVIEN through MANV to filter by employee full name.

HOADON only stores MANV (the code), not the employee's name - a JOIN to NHANVIEN is required before filtering by the name "Nguyen Van B".

Chains 4 tables together (SANPHAM ➜ CTHD ➜ HOADON ➜ KHACHHANG) following the exact foreign-key chain to get from "customer name" to "products purchased".

There's no direct foreign key between KHACHHANG and SANPHAM - you have to go through HOADON and CTHD (the intermediate tables) to connect 2 tables that are 2 relationship layers apart.

DISTINCT removes duplicate SOHD values from the returned result.

An invoice could include both BB01 and BB02 (2 CTHD rows) - without DISTINCT, that invoice would appear twice in the result.

Adds the condition AND (SL BETWEEN 10 AND 20), applied to each CTHD row that matches MASP.

The quantity condition applies to the individual line item under consideration (not a summed total), so it only needs an extra AND in the same WHERE - no GROUP BY is needed.

INTERSECT returns the rows that appear in both result sets (the intersection of the 2 SOHD sets).

Questions 11/12 use "or" (IN) because only 1 of the 2 products is needed; this question requires "both at the same time", so the intersection must be taken with INTERSECT - IN/OR cannot express this requirement.

UNION combines 2 independent result sets (products by country of manufacture, products by sale date) and automatically removes duplicates.

The 2 conditions belong to 2 different logical branches (one only needs SANPHAM, the other needs to JOIN in CTHD/HOADON), so they're split into 2 separate SELECT statements and combined with UNION rather than being merged into 1 query.

NOT IN excludes any MASP that has ever appeared in CTHD (i.e., has ever been sold).

"Never sold" means the MASP doesn't exist in CTHD - the negation of IN is NOT IN.

A 2-layer nested subquery: the inner layer filters SOHD by year 2006, the outer layer filters MASP by those SOHD values.

CTHD has no year column - it must be nested through HOADON (which has NGHD) first to determine which CTHD rows belong to 2006.

Combines a NUOCSX condition (on SANPHAM) with the same NOT IN subquery as Question 16.

This is Question 16 narrowed further with 1 more condition (country of manufacture) via AND - reusing the same subquery logic already built instead of writing it from scratch.

GROUP BY groups invoices by each employee; COUNT(*) counts the number of rows (invoices) in each group.

The exercise asks for figures "per employee" (not an overall total), so grouping by MANV is required (along with HOTEN, which is functionally dependent on MANV).

COUNT(DISTINCT ...) counts the number of distinct values, ignoring duplicates.

An employee may have sold multiple invoices to the same customer - a plain COUNT(*) would count that customer multiple times, so DISTINCT is needed to count each customer once.

SUM(SL) adds up the quantity for each product; TOP 1 WITH TIES takes the top row and also keeps any tied rows.

SUM is required because the exercise asks for "total quantity sold", not the number of sales. WITH TIES is used so you don't miss the case where multiple products are tied for the highest total.

SUM(TRIGIA) adds up the total value of the invoices each employee created in October 2006.

"Sales" means the total invoice value an employee generated, which differs from Question 18 (counting the number of invoices) - SUM the money column here, not COUNT.

EXCEPT takes rows present in the first set but not present in the second set.

"Present in 2006 but not in 2007" is exactly a set-subtraction operation - EXCEPT expresses this directly, more so than writing it with NOT IN.

HAVING filters on a result that's already been GROUP BY'd (unlike WHERE, which filters before grouping).

The condition "at least 2 different employees" can only be evaluated after counting DISTINCT MANV per product - so it must be filtered with HAVING, not WHERE.

EXCEPT takes the rows in the first set (every customer) that don't appear in the second set (customers who have purchased at least 1 Thai product, found through the chain JOIN KHACHHANG ➜ HOADON ➜ CTHD ➜ SANPHAM).

"Did not purchase any Thai product" is equivalent to "every customer minus those who purchased at least 1 Thai product" - this is exactly a set-subtraction operation, so EXCEPT is the most natural tool for this "none of..." phrasing.

Sorts the 2006 invoices by TRIGIA descending, then takes TOP 1; WITH TIES also keeps every other invoice that reaches the exact same highest value, not just 1 row.

With plain TOP 1 (no WITH TIES), if 2 invoices tie for the highest value, SQL Server would only return 1 of them at random - the other would be missed. WITH TIES guarantees no data is lost in a tie.