Exercises & Answer Key

SELECT lists exactly the 3 columns the question asks for, instead of SELECT *.

The question explicitly names 3 columns in parentheses (MANCC, TENNCC, THANHPHO) - TRANGTHAI isn't pulled even though the table has it.

SELECT * pulls every column that currently exists in PHUTUNG.

The question just says "information" in general, without naming specific columns like Question 1 - so pulling everything is the reasonable reading.

A direct filter with WHERE THANHPHO = 'London'.

The sample data has exactly 2 suppliers in London (S1 Smith, S4 Clark) - an = comparison is enough, no LIKE needed since the city name matches exactly.

Same technique as Question 3, switching the table and the city condition.

The result is P2 (Bolt, Green) and P5 (Cam, Blue) - the only 2 parts with THANHPHO = 'Paris' in the sample data.

A numeric comparison with > on the KHOILUONG column (a FLOAT).

The result is P2 (17), P3 (17), P6 (19) - "greater than 15" excludes a value equal to 15 (no part happens to be exactly 15 here, but it's still worth telling > apart from >= when reading a question).

Adds a MAUSAC <> 'Red' condition joined by AND onto Question 5.

Of the 3 parts satisfying Question 5 (P2, P3, P6), P6 is excluded for being Red - leaving just P2 (Green) and P3 (Blue).

NOT IN ('Red', 'Green') excludes both colors at once, more compact than writing MAUSAC <> 'Red' AND MAUSAC <> 'Green'.

Starting from Question 6's result (P2 Green, P3 Blue), P2 is now excluded too for being Green - leaving only P3 (Blue).

An open range on both ends (> 15 AND < 20, excluding 15 and 20); ORDER BY TENPT sorts alphabetically by part name.

The 3 parts that qualify are P2 (Bolt, 17), P3 (Screw, 17), P6 (Cog, 19) - sorting by name gives the order Bolt, Cog, Screw (not by part code P2/P3/P6).

JOIN PHUTUNG with VANCHUYEN via MAPT, filtered to supplier S1; DISTINCT removes duplicates.

A part could appear multiple times in VANCHUYEN if shipped by several different suppliers, but filtering to exactly 1 MANCC here means each MAPT actually matches just 1 row (since VANCHUYEN's primary key is the pair MANCC, MAPT) - DISTINCT is still kept to match what the question explicitly asks for, and is a safe habit whenever JOINing.

The mirror structure of Question 9 - the JOIN direction flips from NHACUNGCAP to VANCHUYEN, filtering by MAPT instead of MANCC.

The 2 suppliers who ever shipped P1 are S1 and S2 - a good example of an N-N relationship: a single part can be shipped by several different suppliers.

Chains JOIN across all 3 tables to know both where the supplier is located and where the parts they shipped are located; the 2 THANHPHO = 'London' conditions apply to 2 different tables (ncc and pt).

It's easy to mistake the 2 "London" conditions as the same idea, but these are 2 independent constraints on 2 different entities - the supplier is based in London and (not necessarily the same row of data) has shipped at least 1 part that originates from London. Both S1 and S4 qualify (S1 shipped P1/P4/P6 - all located in London; S4 shipped P4 - located in London).

An independent subquery gets the list of MAPT that S1 ever shipped; the outer query filters PHUTUNG against that list with IN.

Gives the same result as Question 9 without needing JOIN or DISTINCT - since IN only checks "is it in the list" instead of multiplying rows the way JOIN can, there's naturally no duplication to remove.

The mirror of Question 12, flipping the filter from MAPT to MANCC.

Same reasoning as Question 12 - IN compactly replaces the JOIN + DISTINCT pair when all that's needed is to check "does a relationship exist", with no extra column pulled from the intermediate VANCHUYEN table.

A correlated subquery - vc.MAPT = pt.MAPT references back to the current PHUTUNG row in the outer query, unlike the independent subquery in Question 12.

All 3 approaches (Question 9's JOIN, Question 12's IN, Question 14's EXISTS) give exactly the same result - the difference is in how SQL Server optimizes execution: EXISTS stops as soon as it finds the first matching row, usually faster than IN when the inner table is large.

The mirror of Question 14, correlated via MANCC instead of MAPT.

Questions 10/13/15 form a complete trio illustrating the same question answered with JOIN, IN, and EXISTS - worth writing out all 3 and comparing results to make sure each technique's behavior is properly understood.

The "supplier is in London" condition stays in the outer query; the "shipped a London part" condition becomes an independent subquery returning a list of MANCC, matched with IN.

The subquery still needs to JOIN VANCHUYEN with PHUTUNG internally, since it has to know which parts are in London before tracing back to the supplier - the JOIN doesn't disappear, it just moves inside the subquery.

Same as Question 16, but the subquery is correlated via vc.MANCC = ncc.MANCC instead of returning a standalone list to compare with IN.

Questions 11/16/17 round out the complete trio of JOIN, IN, and EXISTS for the same 2-condition problem - a step up in complexity from Questions 9-10 since it needs an extra JOIN right inside the subquery.

NOT IN excludes every supplier whose code appears in VANCHUYEN, leaving only suppliers that have never shipped anything.

The result is S5 (Adams) - the only supplier with no row at all in VANCHUYEN. NOT IN is safe here since VANCHUYEN.MANCC is part of the primary key and can never be NULL.

NOT EXISTS checks that no VANCHUYEN row matches the supplier being evaluated.

Gives the same S5 result as Question 18 - NOT EXISTS is always the safer long-term choice over NOT IN (no NULL trap to worry about), even though both are correct in this exercise since there's no NULL in VANCHUYEN.MANCC.

LEFT JOIN keeps every supplier even when no VANCHUYEN row matches (the vc-side columns come back NULL); filtering WHERE vc.MANCC IS NULL is the classic "anti-join" technique.

Questions 18/19/20 are the 3 most classic ways to answer a "doesn't have any" question in SQL - NOT IN, NOT EXISTS, and LEFT JOIN ... IS NULL - worth memorizing all 3, since this pattern shows up constantly on practice exams.

COUNT(*) counts every row in the table.

The sample data has exactly 5 suppliers (S1-S5).

WHERE filters first, COUNT(*) counts second - only rows that pass the filter get counted.

The result is 2 (S1, S4) - the difference from Question 21 is the added WHERE to narrow the scope of the count.

MAX() and MIN() can be computed together in the same SELECT, no need for 2 separate statements.

TRANGTHAI across the 5 suppliers is 20, 10, 30, 20, 30 - highest is 30, lowest is 10.

Adds WHERE THANHPHO = 'London' before computing MAX/MIN, unlike Question 23 which computes over the whole table.

Both suppliers in London (S1, S4) happen to have TRANGTHAI = 20, so both the highest and lowest come out to 20 - not a bug, just a coincidence of the data.

GROUP BY MANCC splits the data by supplier; SUM(SOLUONG) totals the quantity within each group.

"How many parts" here means the total quantity shipped (the SOLUONG column), not a count of distinct part types - the result: S1=1300, S2=700, S3=200, S4=900. S5 doesn't appear since GROUP BY runs on VANCHUYEN, where S5 has no rows at all.

Same idea as Question 25, but now needs a JOIN to NHACUNGCAP to get TENNCC/THANHPHO, so GROUP BY has to list all 3 non-aggregated columns.

SQL Server requires every column in SELECT that isn't wrapped in an aggregate function to also appear in GROUP BY - leaving out TENNCC or THANHPHO from GROUP BY raises a syntax error.

HAVING SUM(SOLUONG) > 500 filters on the aggregated result after GROUP BY, unlike WHERE which can only filter the original individual rows.

WHERE SUM(SOLUONG) > 500 wouldn't work, since at the point WHERE runs the data hasn't been grouped yet, so SUM() has no value - from Question 25's result (S1=1300, S2=700, S3=200, S4=900), only S3 (200) gets excluded.

WHERE pt.MAUSAC = 'Red' filters before grouping (keeping only shipments of red parts), then GROUP BY + HAVING filters on the total quantity.

The red parts are P1, P4, P6. Per supplier: S1 shipped red P1(300)+P4(200)+P6(100)=600; S2 shipped red P1(300)=300; S4 shipped red P4(300)=300 - only S1's total is greater than 300 (S2 and S4 are exactly 300, which doesn't satisfy "more than").

Same logic as Question 28, adding a JOIN to NHACUNGCAP for TENNCC/THANHPHO, the same way Question 26 extended Question 25.

The result is exactly 1 row: S1, Smith, London, 600 - matching the analysis in Question 28.

GROUP BY THANHPHO splits suppliers by city; COUNT(*) counts how many fall into each group.

The result: London=2 (S1, S4), Paris=2 (S2, S3), Athens=1 (S5) - all 5 suppliers accounted for, unlike Question 25 (where S5 was missing since the source table there was VANCHUYEN, not NHACUNGCAP).

Combines JOIN (to get the supplier name), GROUP BY (to total the quantity per supplier), and ORDER BY ... DESC + TOP 1 WITH TIES (to take the supplier with the highest total, keeping any suppliers tied for the top spot).

A typical example of chaining several learned techniques in 1 question - from the total-quantity results in Question 25 (S1=1300, S2=700, S3=200, S4=900), S1 (Smith) leads with 1300, and no other supplier is tied, so the result is exactly 1 row.

INTERSECT takes the overlap between 2 sets of THANHPHO values - keeping only cities that appear in both tables.

Supplier cities: London, Paris, Athens. Part cities: London, Paris, Oslo - the overlap is just London and Paris (Athens only has a supplier, Oslo only has a part).

INSERT INTO ... (column list) VALUES (...) adds exactly 1 new row with values matching the order listed.

Naming the columns explicitly (instead of just VALUES (...)) makes the statement independent of the table's physical column order, and easier to read and less error-prone on a table with many columns.

UPDATE ... SET ... WHERE only modifies the row with MANCC = 'S6' just added in Question 33.

The WHERE clause in UPDATE must filter to the exact MANCC - forgetting WHERE would change every supplier in the table to "Sydney."

SET TRANGTHAI = TRANGTHAI + 10 takes the column's current value and adds 10, applied to every row matching WHERE.

Unlike Question 34 (updating 1 row by primary key), this question intentionally updates multiple rows at once (both S1 and S4 are in London) - WHERE THANHPHO = 'London' is still required, just filtering on a different attribute instead of the primary key.

DELETE FROM ... WHERE deletes exactly the row with MANCC = 'S6'.

S6 doesn't appear in VANCHUYEN (it never shipped anything) so it can be deleted right away - if S6 already had linked rows in VANCHUYEN, the FKShip1 foreign key constraint would block this DELETE until the related VANCHUYEN rows were removed first.