Exercises & Answer Key

All 8 relations are created with CREATE TABLE; foreign keys are declared right in the column definitions once the referenced table already exists. The 3 new columns GHICHU, DIEMTB, XEPLOAI are added straight into HOCVIEN at creation time.

Table creation order must follow the foreign key dependency chain - GIAOVIEN waits for KHOA, HOCVIEN waits for LOP. KHOA.TRGKHOA ↔ GIAOVIEN.MAKHOA and LOP.TRGLOP ↔ HOCVIEN.MALOP reference each other in a cycle, so both foreign keys can't be declared at once inside CREATE TABLE - One direction has to be created first, then ALTER TABLE ADD CONSTRAINT adds the other direction once the other table exists.

CHECK ... IN restricts a column to exactly the 2 listed values, regardless of which table the column belongs to.

GIOITINH appears in both HOCVIEN and GIAOVIEN in this schema, and the question doesn't specify which relation - the constraint is applied to both so the data stays consistent everywhere the column appears.

BETWEEN 0 AND 10 limits the value to a closed range - both 0 and 10 are valid.

The "2 decimal places" part is already satisfied by the NUMERIC(4,2) type declared for DIEM in Question 1 (4 digits total, 2 after the decimal point) - This question only still needs the 0-10 range constraint.

CHECK constrains the relationship between 2 columns in the same row - here DIEM and KQUA must always agree.

The 2 conditions can't be split into 2 separate CHECK constraints because they depend on each other (whether KQUA is correct depends on DIEM) - Combining both cases in 1 CHECK with OR keeps the data consistent no matter how high or low DIEM is.

Restricts LANTHI to only 1, 2, or 3.

The primary key (MAHV, MAMH, LANTHI) already guarantees no duplicate attempt number for a given (student, subject) pair - combined with the CHECK capping LANTHI at 3, a student can have at most 3 exam result rows for the same subject.

Same technique as Question 5, applied to the HOCKY column of GIANGDAY.

An academic year has only 3 semesters at this training center - BETWEEN 1 AND 3 blocks any invalid semester value right at data entry.

CHECK ... IN with a closed list of 5 valid degrees.

The question lists exactly 5 degrees, with no trailing "…" - this is a closed list, so IN (...) is the right fit.

DATEADD(YEAR, -18, GETDATE()) computes the date exactly 18 years before today; a valid student must be born on or before that date.

SQL Server allows a CHECK to reference GETDATE(), but the constraint is only evaluated at INSERT/UPDATE time - a student who was valid when inserted stays in the table as-is even as time passes; CHECK does not automatically re-scan existing rows.

A direct comparison between 2 date columns in the same row.

A course can't end before it starts - this constraint keeps the teaching period logically valid.

DATEADD(YEAR, 22, NGSINH) computes the date the teacher turns 22; NGVL must be on or after that date.

Unlike Question 8, this constraint compares 2 columns within the same row (NGSINH and NGVL) instead of against GETDATE() - so it stays correct and stable forever, without going stale the way a current-date CHECK would.

ABS(...) takes the absolute value, so the difference is computed correctly whether TCLT is larger or smaller than TCTH.

The question only says the difference "may not exceed 3" without caring which side is larger - writing TCLT - TCTH <= 3 without ABS would let a case where TCTH is much larger than TCLT slip through, which contradicts the intended meaning.

The subquery gets every MAGV that is currently the TRGKHOA of any department; IN filters the UPDATE to exactly those teachers.

TRGKHOA can be NULL (the KTMT department has no department head yet). With IN, filtering out NULL isn't strictly required since NULL never matches but also never causes a wrong result - however it's a good habit to keep the IS NOT NULL filter, because if this were switched to NOT IN in another exercise, a NULL in the list would make the whole statement return nothing - a very easy mistake to make.

The inner subquery uses MAX(LANTHI) to keep only the most recent attempt for each (MAHV, MAMH) pair; the outer subquery runs AVG(DIEM) over exactly that filtered set for each student.

You can't run AVG(DIEM) directly over the whole KETQUATHI table, since a student can have multiple rows for the same subject (retakes) - without filtering to the most recent attempt, the average would be skewed by earlier failed attempts too.

The subquery gets the MAHV of every 3rd-attempt result scoring below 5; IN then flags exactly those students.

Just 1 qualifying subject is enough to trigger "Cam thi" ("any subject") - no GROUP BY or counting is needed, the student just has to appear at least once in the filtered result set.

CASE WHEN checks conditions in order from the highest threshold down - SQL Server stops at the first true condition, so each branch doesn't need to spell out both an upper and a lower bound.

For example the "G" branch only needs DIEMTB >= 8, without an extra AND DIEMTB < 9 - because if DIEMTB were really ≥ 9, the "XS" branch above it would already have matched first and CASE would have stopped there. Adding an upper-bound condition would be redundant.

JOIN HOCVIEN with LOP via TRGLOP to get exactly the students who are currently a class monitor; HO + ' ' + TEN concatenates the full name since HOCVIEN stores it as 2 separate columns.

JOIN is used (not a subquery) because we need 4 full columns of student data - the question asks for both full name and date of birth, so we must join directly into HOCVIEN.

JOIN HOCVIEN with KETQUATHI via MAHV, filtering for the right class and subject; ORDER BY TEN before HO because sorting "by first name, then last name" means the first (given) name takes priority.

No need to JOIN in LOP as well, since MALOP already lives directly on HOCVIEN - there's no need to go through LOP just to filter for class "K12".

Filter KETQUATHI directly by LANTHI = 1 and KQUA = 'Dat', then JOIN to HOCVIEN just to get the full name.

The question also asks to print "the subjects" passed on attempt 1 - so MAMH has to stay in the result, no GROUP BY or aggregation, each row is 1 (student, subject) pair that satisfies the condition.

JOIN then filter on 4 conditions at once with AND: the right class, the right subject, the right attempt, the right result.

Only attempt 1 is in scope, so LANTHI = 1 must be filtered explicitly - without it, a student who retook the exam could still show up in the result even if they passed on a later attempt.

JOIN with KETQUATHI filters to students who actually took the CTRR exam (confirming they took it while also filtering by class); NOT EXISTS confirms none of their CTRR attempts passed - together this means every CTRR attempt by this student failed.

Class "K" here should be read as a class-code prefix - every class in this schema has a code starting with "K" (K11, K12, K13), so class "K" is equivalent to MALOP LIKE 'K%' (every class), not a single specific class - the same prefix-style phrasing as Question 3 in Week 2 ("product code starting with B"). If we only filtered KQUA = 'Khong Dat' like Question 4, a student who retook the exam and passed once would still show up incorrectly - NOT EXISTS is needed to guarantee no attempt ever passed, matching "across all attempts". DISTINCT avoids duplicate rows since JOIN KETQUATHI can match more than 1 CTRR attempt per student.

JOIN 3 tables GIANGDAY - GIAOVIEN - MONHOC to go from teacher name to subject name; DISTINCT because 1 teacher can teach the same subject to multiple classes in the same semester (duplicate rows).

The question only asks for the "subject name" (not the code), and per the sample data teacher GV02 teaches CTRR to both K11 and K12 in semester 1/2006 - DISTINCT avoids printing the subject name twice.

The subquery gets exactly 1 teacher code, the homeroom teacher of class K11, from LOP.MAGVCN, then filters GIANGDAY for that teacher and the right semester/year.

A plain equality subquery (=, not IN) is used because each class has exactly 1 homeroom teacher, so the subquery is guaranteed to return exactly 1 value - no need to join LOP into the outer query.

The subquery finds the class codes where teacher "Nguyen To Lan" teaches "Co So Du Lieu"; the outer query JOINs LOP with HOCVIEN via TRGLOP to get the class monitor's full name for those classes.

1 teacher can teach this subject to more than 1 class, so the subquery uses IN (not =) - DISTINCT guards against a duplicate monitor name if multiple GIANGDAY rows match the same class.

The subquery looks up the subject code for "Co So Du Lieu"; DIEUKIEN.MAMH_TRUOC lists the subjects that must be taken before it, then JOIN MONHOC gets their names.

The question gives a subject name, not a code, so the code must be looked up first via a subquery - hardcoding the subject code directly, even though we know it from the sample data, would break if the code ever changed, while looking it up by name stays correct.

The reverse direction of Question 9 - this time filter by MAMH_TRUOC and pull out MAMH (the subjects that require CTRR as a prerequisite).

DIEUKIEN stores the pair (MAMH, MAMH_TRUOC); Question 9 goes from MAMH to find MAMH_TRUOC, Question 10 goes the other way, from MAMH_TRUOC to find MAMH - same table, filtered on a different column depending on the direction of the question.

2 independent subqueries, each returning the MAGV that taught CTRR to exactly 1 class (K11 or K12) in semester 1/2006; a valid teacher must satisfy both IN conditions at once.

You can't merge this into a single MALOP IN ('K11','K12') condition, since that would match a teacher who taught just 1 of the 2 classes - the 2 subqueries have to stay separate, joined by AND, to force the teacher to cover both classes.

The main condition filters for a failing CSDL attempt 1; NOT EXISTS checks that no row exists at all with LANTHI > 1 for that same student and subject.

"Has not retaken it" means no record exists from attempt 2 onward - LANTHI > 1 (rather than a fixed LANTHI = 2) is used to also safely catch the rare edge case of a student having a LANTHI = 3 record without a LANTHI = 2 one in the data (the schema has no constraint forcing attempt numbers to be contiguous) - NOT EXISTS is the right tool for checking "does not exist", as opposed to counting or filtering directly.

NOT EXISTS checks that the teacher doesn't appear in any GIANGDAY row at all.

This is a total negation query ("not any") - NOT EXISTS is always the safest choice here, avoiding the NULL trap that NOT IN can fall into.

NOT EXISTS checks that there's no GIANGDAY row for that teacher matching a subject that belongs to the teacher's own department (mh.MAKHOA = gv.MAKHOA).

Unlike Question 13 (not teaching anything at all), this only cares about subjects from the teacher's own department - a teacher can still teach subjects from other departments and satisfy this condition, as long as they teach nothing from their own department.

2 EXISTS branches joined by OR inside the same parentheses: branch 1 checks for any subject that reached attempt 3 (the maximum) and still failed; branch 2 checks for a CTRR attempt 2 scoring exactly 5.

"Failed any subject on more than 3 attempts" - given the max LANTHI is 3 (Part I, Question 5) - is read as having reached attempt 3 (the last allowed attempt) and still being "Khong dat", meaning all retake chances are exhausted; this is the closest reading consistent with the schema's actual data constraint. Watch out: Writing this condition literally as LANTHI > 3 would make that branch always empty (never true), since the CHECK from Question 5 (Part I) already caps LANTHI at 3 - "failed on more than 3 attempts" has to be read according to how the schema's constraint actually works (all 3 allowed attempts used up), not translated word-for-word into > 3.

GROUP BY (MAGV, HOCKY, NAM), then count distinct classes with COUNT(DISTINCT MALOP); HAVING >= 2 keeps only groups teaching 2 or more classes.

Both HOCKY and NAM must be in the GROUP BY (not just MAGV), since the question requires "in the same semester of the same academic year" - grouping by MAGV alone would wrongly count a teacher who taught K11 in semester 1 and K12 in semester 2 (2 different semesters) as satisfying the condition. Note: Group by MAGV (the primary key) and only pull HOTEN out afterward - grouping directly by HOTEN instead of MAGV would accidentally merge 2 teachers who happen to share the same name into a single group, skewing the count.

The MAX(LANTHI) subquery finds each student's most recent attempt for the CSDL subject; the outer condition keeps only the row matching that attempt.

A student can retake the CSDL exam multiple times - without filtering to the most recent attempt, scores from earlier attempts would also print, which contradicts what the question asks for.

GROUP BY per student, then MAX(DIEM) - unlike Question 17 (filtering by most recent attempt), this takes the highest score regardless of which attempt it came from.

"Highest score across all attempts" is a different requirement from "score from the most recent attempt" in Question 17 - a student could score their highest on the first try and then score lower on a retake; MAX(DIEM) always picks the highest value regardless of attempt order.

The MIN(NGTLAP) subquery finds the earliest founding date; the outer query keeps the department(s) whose founding date matches it.

ORDER BY with TOP 1 isn't used because if 2 departments share the same earliest date, TOP 1 would drop 1 of them - comparing directly against MIN(...) always keeps every tied department.

COUNT(*) counts the rows matching either of the 2 listed values for HOCHAM.

HOCHAM can be NULL (a teacher with no academic rank yet) - IN (...) automatically skips NULL rows without needing a separate IS NOT NULL condition.

GROUP BY on the pair (MAKHOA, HOCVI), COUNT(*) counts teachers within each group.

Both columns must be in GROUP BY together since the report needs a breakdown down to each (department, degree) combination - grouping by MAKHOA alone would merge every degree together into 1 row.

GROUP BY on the pair (MAMH, KQUA) to count attempts by result for each subject.

This counts by exam attempt (each KETQUATHI row), without deduplicating students who retook the exam - since every row in KETQUATHI represents exactly 1 attempt, counting rows directly is the most reasonable reading; a student who retook a subject could contribute to both the "Khong Dat" group (earlier attempt) and the "Dat" group (later attempt) for the same subject.

JOIN GIAOVIEN with LOP via MAGVCN to find which class each teacher is homeroom teacher of; EXISTS checks whether that teacher teaches any subject to that exact class.

The class must match exactly between GIANGDAY and the LOP row being considered (gd.MALOP = l.MALOP) - just checking "teaches something, somewhere" without tying it to the right class would wrongly count a homeroom teacher who teaches a different class entirely.

The MAX(SISO) subquery finds the largest enrollment; the outer query JOINs LOP with HOCVIEN via TRGLOP to get the monitor's full name for that class.

This approach automatically keeps every class tied for the largest enrollment instead of dropping some, the way TOP 1 would - same reasoning as Question 19.

The subquery counts COUNT(DISTINCT MAMH), where for each subject NOT EXISTS confirms no attempt ever passed (same idea as Question 5) - the result is the number of subjects this student has never passed despite taking them.

hv.MAHV IN (SELECT TRGLOP FROM LOP) filters to exactly the students currently acting as a class monitor (LOPTRG) for any class; the > 3 count condition sits in its own parentheses because it's a scalar subquery compared directly, not a HAVING clause (since the outer query has no GROUP BY).

GROUP BY per student, counting scores of 9 or 10; TOP 1 WITH TIES keeps every student tied for the top count instead of just 1 row.

TOP ... WITH TIES (not a plain TOP 1) is needed because multiple students could tie for the highest count of 9-10 scores - a plain TOP 1 would arbitrarily keep just 1 of them.

The derived table t counts each student's 9-10 scores; the derived table t2 recomputes the same figures and takes MAX for the matching class (t2.MALOP = t.MALOP), comparing within each class separately.

Unlike Question 26 (school-wide comparison), this needs the highest count computed separately per class - a single shared MAX value like Question 19/24 won't work, so MAX must be correlated to the exact MALOP being evaluated.

GROUP BY (MAGV, HOCKY, NAM); COUNT(DISTINCT MAMH) counts distinct subjects, COUNT(DISTINCT MALOP) counts distinct classes within each group.

DISTINCT inside COUNT is required because a teacher can appear in multiple GIANGDAY rows for different classes but the same subject (or vice versa) within the same semester/year - a plain COUNT(*) would just count assignment rows, not the actual number of subjects/classes.

The derived table t counts each teacher's number of assignments (GIANGDAY rows) per (semester, year); it's compared against the MAX for that exact semester/year (correlated via HOCKY, NAM) to keep the top teacher within each group.

Like Question 27, "the most" here means the most within each semester/year individually - not the most across the entire dataset, so MAX must be correlated to the exact (HOCKY, NAM) being evaluated.

GROUP BY per subject, counting attempt-1 failures; TOP 1 WITH TIES keeps every subject tied for the highest count.

Same reasoning as Question 26 - TOP ... WITH TIES avoids dropping a subject if multiple subjects tie for the most attempt-1 failures.

EXISTS confirms the student has at least 1 attempt-1 record; NOT EXISTS confirms none of their attempt-1 records failed - together this means every subject's first attempt was a pass.

Both EXISTS and NOT EXISTS are needed - with only NOT EXISTS, a student who never took any exam at all would vacuously satisfy "never failed", even though they never took a subject in the first place.

This time NOT EXISTS checks that no subject's most recent attempt (MAX(LANTHI) for that subject) failed - unlike Question 31, which only looked at exactly LANTHI = 1.

The "most recent attempt" differs by subject and by student (some subjects taken once, others retaken 2-3 times) - MAX(LANTHI) must be recomputed correlated to each (MAHV, MAMH) pair, the same technique used in Question 17, rather than a fixed LANTHI value like Question 31.

This is the classic relational division pattern using 2 nested NOT EXISTS - "there is no subject the student did not pass on attempt 1" is equivalent to "every subject was passed on attempt 1".

Unlike Question 31 (only considering subjects the student actually took), this requires the student to have taken every single subject that exists in MONHOC - the "not exists X such that not exists Y" structure is the standard SQL pattern for a "for all" condition, since SQL has no direct universal quantifier.

The same division structure as Question 33, but the inner "passed" condition uses each subject's most recent attempt instead of a fixed LANTHI = 1.

This combines the "most recent attempt" technique (Question 17, Question 32) with the division technique (Question 33) - the student must have passed the latest attempt of every subject that exists to qualify.

The derived table t builds the list of scores from the most recent attempt of each (student, subject) pair - the same technique as Question 17; the result is then filtered to rows whose score equals the highest score for that specific subject (t2.MAMH = t.MAMH).

This is a "maximum per group" problem (like Question 27, Question 29) applied on top of data already filtered to the most recent attempt (like Question 17) - both techniques have to be combined: correlating MAX by MAMH, while also excluding any attempt that isn't the most recent one.