Database Practice Mock Exam

Create the 6 tables in strict foreign-key dependency order: PHONG, DICHVU, KHACHHANG depend on no other table so they are created first; DATPHONG references KHACHHANG and PHONG so it comes next; THANHTOAN and CTDICHVU reference DATPHONG so they come last.

There is no pair of foreign keys referencing each other in a cycle (unlike some other schemas, e.g. KHOA↔GIAOVIEN), so no extra ALTER TABLE is needed afterward - Simply creating the tables in dependency order lets every foreign key be declared directly inside CREATE TABLE. The primary key of CTDICHVU is the triple (MaDatPhong, MaDV, NgaySuDung) because the same booking is allowed to use the same service on multiple different dates (each date is its own row).

LoaiPhong <> 'Deluxe' OR GiaPhong > 500000 is the classic "if...then..." CHECK pattern - If it isn't Deluxe, there's no constraint at all (the first clause is true); if it is Deluxe, the second clause must be true.

The constraint only applies to one specific room type, not the whole table - Writing CHECK (GiaPhong > 500000) directly would force every room (including Standard and Superior) to have a minimum rate of 500,000, which is wrong since the requirement only limits Deluxe rooms. This is the technique of using implication (P ⟹ Q ⟺ ¬P ∨ Q) to express a conditional rule with CHECK.

LIKE '0%' matches any string starting with the character "0"; the % wildcard represents any remaining characters of any length.

A "starts with" constraint is a string-format constraint, not a specific value or numeric range - CHECK ... LIKE is the right tool for this case, similar to Question 8 (Requirement 2 - Part I) of Week 1's Practice Exercise 1.

JOIN inserted with DATPHONG on MaDatPhong to get the correct stay period for the corresponding booking; IF EXISTS checks the entire inserted table at once, so it stays correct whether INSERT/UPDATE affects 1 row or many.

This is an "outside the range" case, so it must be negated with OR: Earlier than NgayCheckIn or later than NgayCheckOut both count as a violation - It cannot be written as a single BETWEEN condition, because this Trigger raises an error when the condition is not satisfied, not when it is. Extension: The official answer only requires a Trigger on CTDICHVU (already worth the full point) - In practice, this constraint can still be broken if an UPDATE changes a DATPHONG row's NgayCheckIn/NgayCheckOut so that it no longer covers NgaySuDung values already recorded - Full protection would require an additional Trigger on DATPHONG AFTER UPDATE, but this is not required by the question.

4 consecutive JOINs are needed to filter on 3 different entities: Service usage date (CTDICHVU), booking status (DATPHONG), room rate (PHONG); DISTINCT removes duplicates since the same service can be used by multiple different bookings.

"The first six months of 2025" filters on NgaySuDung (the service usage date), not NgayDat or NgayCheckIn - Read the question carefully to know exactly which column carries the time constraint, to avoid filtering on the wrong one.

COUNT(DP.MaDatPhong) counts the filtered bookings per room; GROUP BY must list both MaPH and LoaiPhong since both appear in SELECT outside an aggregate function.

Starting the JOIN from PHONG (rather than DATPHONG) is purely a readability convention - An INNER JOIN doesn't care about table order, but here it makes sense because the question asks "for each room," so putting PHONG as the leading table makes the statement easier to read.

2 structurally identical queries, differing only in the service name condition (Spa/Massage); INTERSECT takes the intersection of the 2 MaDatPhong sets, keeping only bookings that satisfy both sides.

CT.NgaySuDung = DP.NgayCheckIn is a required condition - The question asks for the service used exactly "on the check-in date," not at any point during the stay. With the sample data, the result set is DP008 (Spa and Massage both used on 2025-01-18, exactly the check-in date) and DP015 (Spa and Massage both used on 2025-07-08, exactly the check-in date) - DP011 has both Spa and Massage but Massage was used 1 day off from NgayCheckIn, so it is excluded.

2 independent subqueries, each handling exactly one side of the requirement: The first finds the service(s) with the highest total SoLuong in Q3 2025; the second finds the booking(s) with the lowest total ThanhTien in the same quarter - TOP 1 WITH TIES ensures ties are never missed.

This is the hardest question on the exam because it has 2 independent "highest/lowest" criteria that must hold simultaneously - The service must be in the most-used group, and the booking that contains it must be in the lowest-spending group for that service. These cannot be combined into a single subquery because the 2 criteria are computed with 2 different GROUP BY keys (by MaDV and by MaDatPhong), so they must be split into 2 subqueries joined by AND.

The TOP 3 WITH TIES subquery finds the 3 rooms (possibly more if tied) with the highest service spending among "Confirmed" bookings; HAVING COUNT(DISTINCT DP.MaPH) = 3 in the outer query ensures the service appears at all 3 of those rooms, not just 1-2 of them.

"Used by all 3 rooms" is a "for all" requirement over a specific, known subset (the 3 top rooms), unlike "used by at least 1 room" (which only needs IN) - Counting the distinct rooms the service matches and comparing to exactly 3 is what guarantees full coverage. This is also why GROUP BY + HAVING COUNT DISTINCT is the more intuitive way to express "for all over a known finite set," while the double NOT EXISTS division pattern (Alternative approach) is more general when the comparison set's exact size isn't known in advance.