Advanced SQL and Relational Theory

Advanced SQL and Relational Theory: Core Concepts Explained

1. Filtering Ranges with BETWEEN

When you need to retrieve rows whose numeric column falls inside a specific interval, the BETWEEN operator is the most readable choice. It is inclusive, meaning the boundary values are part of the result set.

• Syntax: SELECT * FROM TableName WHERE ColumnName BETWEEN low_value AND high_value;
• Equivalent to: ColumnName >= low_value AND ColumnName <= high_value

For example, to list products priced between 50,000 and 100,000 (inclusive) the correct query is:

SELECT * FROM SanPham WHERE GiaGoc BETWEEN 50000 AND 100000;

Using IN or a single <= comparison would either miss the lower bound or return rows outside the intended range.

2. Finding the Highest Score with Subqueries

Retrieving the student(s) who achieved the maximum score in a particular course demonstrates two important techniques:

• Correlated subqueries that compute an aggregate once and compare each row to that value.
• Keeping the query portable across SQL dialects (avoiding vendor‑specific shortcuts like TOP 1 WITH TIES).

The semantically correct statement is:

SELECT K.MaSV, HoSV, TenSV
FROM SinhVien S
JOIN KetQua K ON S.MaSV = K.MaSV
WHERE K.MaMH = 'CSDL'
  AND K.Diem = (SELECT MAX(Diem) FROM KetQua WHERE MaMH = 'CSDL');

Here the inner SELECT MAX(Diem)… returns the highest score for the CSDL course. The outer query then filters rows whose Diem matches that maximum, guaranteeing that every top‑scoring student is returned, even when there are ties.

3. Comparing Values to an Aggregate: Prices Above the Average

To compare each row against an aggregate of the whole table, you embed the aggregate in a scalar subquery. The correct formulation is:

SELECT * FROM SanPham
WHERE GiaGoc > (SELECT AVG(GiaGoc) FROM SanPham);

Notice that the subquery does not contain a GROUP BY clause because we want the average of *all* products, not a per‑group average. Adding GROUP BY MaSP would produce multiple rows, causing a runtime error.

4. Modeling Many‑to‑Many Relationships

Relational databases cannot store a many‑to‑many association directly in two tables. The proper solution is a junction (link) table that holds foreign keys referencing each side of the relationship.

• Students table: HocVien (MaHocVien, …)
• Courses table: MonHoc (MaMonHoc, …)
• Link table: HocVien_MonHoc (MaHocVien, MaMonHoc)

This design allows any student to enroll in many courses and any course to have many students, while preserving referential integrity through foreign‑key constraints.

5. Primary Keys: What They Consist Of

A primary key is a set of one or more attributes (columns) that uniquely identify each tuple (row) in a relational table. The key must be:

• Unique – no two rows share the same key value.
• Not null – every row must contain a value for each attribute of the key.
• Stable – the value should not change frequently.

Understanding that a primary key is defined by its attributes (not by files, tuples, or ER diagrams) is essential for proper schema design.

6. Controlling Access: The REVOKE Statement

SQL provides a set of Data‑Control Language (DCL) commands to manage permissions. While GRANT gives privileges, REVOKE removes them.

REVOKE SELECT, INSERT ON DatabaseName FROM UserName;

Using REVOKE ensures that a user can no longer execute the specified operations, which is crucial for maintaining security and compliance.

7. Eliminating Duplicates with DISTINCT

The DISTINCT keyword removes duplicate rows from the result set. It applies to the entire row, but when you select a single column, it effectively returns the unique values of that column.

SELECT DISTINCT ProductID FROM order_details;

Other syntaxes such as UNIQUE or custom phrases like “ONLY ONCE” are not standard SQL and will cause errors.

8. Functional Dependencies and Derivation

Functional dependencies (FDs) describe how attributes relate to each other in a relation. Given the relation D(H, K, I, Y, Z) with the following FDs:

• HK → I Z
• K → Y
• Y → H

We can derive additional dependencies using Armstrong’s axioms (reflexivity, augmentation, transitivity). For instance:

• From K → Y and Y → H, we obtain K → H (transitivity).
• Combining K → Y with HK → I Z yields K H → I Z Y, etc.

Among the answer choices, the dependency K → H is not directly derivable from the given set because the transitive step requires both K → Y and Y → H. The single‑step inference K → H is therefore invalid without explicitly chaining the two rules.

9. Putting It All Together: Best Practices for Advanced SQL Queries

When designing complex queries, keep the following guidelines in mind:

• Readability: Use BETWEEN for ranges, DISTINCT for duplicate elimination, and explicit JOIN syntax instead of comma‑separated tables.
• Portability: Prefer standard SQL constructs (e.g., subqueries, MAX) over vendor‑specific shortcuts like TOP 1 WITH TIES.
• Performance: Place aggregates in scalar subqueries only when necessary; consider indexing columns used in WHERE clauses.
• Data Integrity: Model many‑to‑many relationships with a junction table and enforce primary‑key/foreign‑key constraints.
• Security: Regularly audit privileges using GRANT and REVOKE to follow the principle of least privilege.

10. Quick Review Quiz

Test your understanding with these short prompts:

• Which clause would you use to return rows where price is exactly 75,000? Answer: WHERE price = 75000
• How do you retrieve the maximum salary from an Employees table? Answer: SELECT MAX(Salary) FROM Employees;
• What is the purpose of a junction table? Answer: To represent a many‑to‑many relationship by storing pairs of foreign keys.
• Which SQL command removes a previously granted privilege? Answer: REVOKE
• Write a query that lists unique customer IDs from the Orders table. Answer: SELECT DISTINCT CustomerID FROM Orders;

By mastering these concepts, you will be equipped to write robust, efficient, and secure SQL statements that reflect solid relational theory.