Chapter 8 – Advanced SQL
8.1 Relational Set Operators
SQL provides several set operations to combine and compare results from two or more SELECT
queries. The key operators are UNION, UNION ALL, INTERSECT, and MINUS (EXCEPT in MS
Access).
Common Set Operators:
1 UNION – Combines results of two queries and removes duplicates.
2 UNION ALL – Combines results including duplicates.
3 INTERSECT – Returns common rows between two queries (not supported in MS Access).
4 MINUS / EXCEPT – Returns rows from the first query not found in the second.
Example (MS Access):
SELECT City FROM Students
UNION
SELECT City FROM Teachers;
8.2 SQL Join Operators
Join operations are used to retrieve data from multiple related tables based on a common column.
1 INNER JOIN – Returns only matching rows.
2 LEFT JOIN – Returns all rows from the left table, matched with right table.
3 RIGHT JOIN – Returns all rows from the right table, matched with left table.
4 CROSS JOIN – Returns all combinations of both tables.
Example:
SELECT [Link], [Link]
FROM Students
INNER JOIN Courses ON [Link] = [Link];
8.3 Subqueries and Correlated Queries
A subquery is a query within another query. It can return one or multiple values. A correlated
subquery refers to columns of the outer query and executes once for each row.
Examples:
-- Simple Subquery
SELECT StudentName, Age FROM Students
WHERE Age > (SELECT AVG(Age) FROM Students);
-- Correlated Subquery
SELECT [Link] FROM Students AS S
WHERE [Link] > (SELECT AVG(Age) FROM Students WHERE CourseID =
[Link]);
8.4 SQL Functions
SQL has built-in functions for calculations, text handling, and date operations.
1 Date and Time: NOW(), DATE(), YEAR(), MONTH(), DAY()
2 Numeric: ROUND(), ABS(), SUM(), AVG(), COUNT()
3 String: UCASE(), LCASE(), LEN(), MID(), LEFT(), RIGHT(), CONCAT()
4 Conversion: CINT(), CDATE(), CSTR()
Example (MS Access):
SELECT StudentName, UCASE(City) AS CityName, YEAR(DateOfBirth) AS
BirthYear FROM Students;
SELECT COUNT(StudentID), AVG(Age) FROM Students;
8.5 Sequences and AutoNumber Fields
MS Access uses the AutoNumber data type to automatically generate unique values for each
record.
Example:
CREATE TABLE Enrollments (EnrollID AUTOINCREMENT PRIMARY KEY, StudentID
INTEGER, CourseID INTEGER);
8.6 Views and Updatable Queries
A view is a virtual table created using a SELECT query. In MS Access, it is similar to a saved query.
Example:
CREATE VIEW CourseSummary AS
SELECT [Link], COUNT([Link]) AS TotalStudents
FROM Courses AS C LEFT JOIN Students AS S ON [Link] = [Link]
GROUP BY [Link];
8.7 Procedural SQL (Triggers and Procedures)
MS Access doesn’t support procedural SQL directly, but uses macros and VBA for automation.
Other systems (like Oracle) use triggers, functions, and stored procedures.
8.8 Embedded SQL
Embedded SQL allows SQL queries to be used inside programming languages like VBA, Java, or
C++.
Example (MS Access VBA):
Dim rs As Recordset
Set rs = [Link]("SELECT * FROM Students WHERE Age > 20")
Do While Not [Link]
[Link] rs!StudentName
[Link]
Loop
Summary
Advanced SQL adds power to database management with set operations, joins, subqueries, and
functions. These features make it easier to perform analytical queries, create reports, and manage
data efficiently in relational databases such as MS Access.
