Chapter 3

Relational Query Languages

7 hours9 marks31 past questionsBack to chapters Open filtered PYQs Previous: Ch 2 Next: Ch 4

Chapter Notes

Block-based notes with markdown, diagrams, code, and math.

Chapter 3 — Relational Query Languages

3.1 Relational Algebra Operators

Operator	Symbol	What it does
Selection	σ	Filter rows satisfying a predicate
Projection	π	Keep only specified columns (removes duplicates)
Cartesian product	×	All tuple combinations from two relations
Natural join	⋈	Join on matching attribute names, merge duplicates
Theta join	⋈θ	Join with an arbitrary predicate θ
Equijoin	⋈	Join on equality; both copies of join attribute kept
Union	∪	All tuples in either relation (both must be union-compatible)
Intersection	∩	Tuples in both relations
Difference	−	Tuples in first but not second
Rename	ρ	Rename relation or attributes
Assignment	←	Assign intermediate result to a name

\sigma_{dept='CS' \wedge gpa > 3.5}(Student)

\pi_{name,\, gpa}\!\left(\sigma_{dept='CS'}(Student)\right)

Student \bowtie_{Student.sid = Enrollment.sid} Enrollment

Optimization tip: always apply σ before π and before ×/⋈. This reduces the size of intermediate results.

3.2 SQL Reference Schema (used in all examples)

CREATE TABLE Student (
  sid    INT          PRIMARY KEY,
  name   VARCHAR(100) NOT NULL,
  dept   VARCHAR(50),
  gpa    DECIMAL(3,2) CHECK (gpa BETWEEN 0 AND 4)
);
CREATE TABLE Course (
  cid     INT          PRIMARY KEY,
  title   VARCHAR(100) NOT NULL,
  credits INT,
  dept    VARCHAR(50)
);
CREATE TABLE Enrollment (
  sid   INT  REFERENCES Student(sid) ON DELETE CASCADE,
  cid   INT  REFERENCES Course(cid)  ON DELETE CASCADE,
  grade CHAR(2),
  year  INT,
  PRIMARY KEY (sid, cid)
);
CREATE TABLE Instructor (
  iid    INT         PRIMARY KEY,
  name   VARCHAR(100),
  dept   VARCHAR(50),
  salary DECIMAL(10,2)
);
CREATE TABLE Teaches (
  iid INT REFERENCES Instructor(iid),
  cid INT REFERENCES Course(cid),
  PRIMARY KEY (iid, cid)
);

3.3 SELECT Statement — Full Syntax

SELECT  [DISTINCT] col_list | *
FROM    table_list | join_expression
[WHERE  row_predicate]
[GROUP BY group_columns]
[HAVING group_predicate]
[ORDER BY col [ASC|DESC]]
[LIMIT  n]

Logical execution order (not written order):
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

-- Basic filter + sort
SELECT name, gpa
FROM   Student
WHERE  dept = 'CS' AND gpa > 3.5
ORDER  BY gpa DESC;

-- DISTINCT — remove duplicates
SELECT DISTINCT dept FROM Student;

-- Aggregate functions
SELECT
  dept,
  COUNT(*)       AS total,
  AVG(gpa)       AS avg_gpa,
  MAX(gpa)       AS top_gpa,
  MIN(gpa)       AS low_gpa,
  SUM(credits)   AS total_credits   -- from Course
FROM Student
GROUP BY dept
HAVING AVG(gpa) > 3.0
ORDER BY avg_gpa DESC;

-- ── JOINS ────────────────────────────────────────────────────────────────────

-- INNER JOIN — only matching rows
SELECT s.name, c.title, e.grade
FROM   Student s
JOIN   Enrollment e ON s.sid = e.sid
JOIN   Course c     ON e.cid = c.cid
WHERE  c.dept = 'CS';

-- LEFT OUTER JOIN — all students, even unenrolled
SELECT s.name, e.cid
FROM   Student s
LEFT JOIN Enrollment e ON s.sid = e.sid;

-- RIGHT OUTER JOIN
SELECT s.name, c.title
FROM   Enrollment e
RIGHT JOIN Course c ON e.cid = c.cid;

-- FULL OUTER JOIN
SELECT s.name, c.title
FROM   Student s
FULL OUTER JOIN Enrollment e ON s.sid = e.sid
FULL OUTER JOIN Course c     ON e.cid = c.cid;

-- CROSS JOIN (Cartesian product)
SELECT s.name, c.title
FROM Student s CROSS JOIN Course c;

-- NATURAL JOIN — auto-joins on same-named columns
SELECT * FROM Student NATURAL JOIN Enrollment;

-- Self join — students in the same department
SELECT a.name AS s1, b.name AS s2, a.dept
FROM   Student a JOIN Student b ON a.dept = b.dept AND a.sid < b.sid;

-- ── SUBQUERIES ───────────────────────────────────────────────────────────────

-- Non-correlated — independent of outer query, runs once
SELECT name FROM Student
WHERE sid IN (SELECT sid FROM Enrollment WHERE grade = 'A');

-- Correlated — references outer row, runs once per outer row
SELECT name, gpa, dept
FROM   Student s
WHERE  gpa > (SELECT AVG(gpa) FROM Student s2 WHERE s2.dept = s.dept);

-- EXISTS / NOT EXISTS
SELECT name FROM Student s
WHERE EXISTS (
  SELECT 1 FROM Enrollment e WHERE e.sid = s.sid AND e.grade = 'A'
);

-- Students who never enrolled in any course
SELECT name FROM Student s
WHERE NOT EXISTS (SELECT 1 FROM Enrollment e WHERE e.sid = s.sid);

-- Scalar subquery in SELECT
SELECT name, (SELECT COUNT(*) FROM Enrollment e WHERE e.sid = s.sid) AS course_count
FROM   Student s;

-- ── SET OPERATIONS ───────────────────────────────────────────────────────────

-- UNION — students in CS or IT (no duplicates)
SELECT sid FROM Student WHERE dept = 'CS'
UNION
SELECT sid FROM Student WHERE dept = 'IT';

-- UNION ALL — keep duplicates
SELECT sid FROM Student WHERE dept = 'CS'
UNION ALL
SELECT sid FROM Student WHERE gpa > 3.8;

-- INTERSECT — enrolled in BOTH course 101 and 102
SELECT sid FROM Enrollment WHERE cid = 101
INTERSECT
SELECT sid FROM Enrollment WHERE cid = 102;

-- EXCEPT / MINUS — enrolled in 101 but NOT 102
SELECT sid FROM Enrollment WHERE cid = 101
EXCEPT
SELECT sid FROM Enrollment WHERE cid = 102;

-- ── DML: INSERT · UPDATE · DELETE ───────────────────────────────────────────

-- INSERT single row
INSERT INTO Student (sid, name, dept, gpa) VALUES (10, 'Ravi', 'CS', 3.7);

-- INSERT multiple rows
INSERT INTO Student VALUES
  (11, 'Priya', 'IT', 3.5),
  (12, 'Sita',  'CS', 3.9);

-- INSERT from SELECT
INSERT INTO CS_Backup SELECT * FROM Student WHERE dept = 'CS';

-- UPDATE — give all CS students a GPA boost (capped at 4.0)
UPDATE Student
SET    gpa = LEAST(gpa + 0.1, 4.0)
WHERE  dept = 'CS';

-- UPDATE with subquery
UPDATE Student
SET    dept = 'CS'
WHERE  sid IN (SELECT sid FROM Enrollment WHERE cid = 101);

-- DELETE rows
DELETE FROM Student WHERE gpa < 1.5;

-- DELETE with subquery — remove students not enrolled anywhere
DELETE FROM Student
WHERE sid NOT IN (SELECT DISTINCT sid FROM Enrollment);

-- ── VIEWS ────────────────────────────────────────────────────────────────────

-- Simple view
CREATE VIEW CS_Students AS
  SELECT sid, name, gpa
  FROM   Student
  WHERE  dept = 'CS';

-- View with join
CREATE VIEW Student_Courses AS
  SELECT s.name, c.title, e.grade
  FROM   Student s
  JOIN   Enrollment e ON s.sid = e.sid
  JOIN   Course c     ON e.cid = c.cid;

-- WITH CHECK OPTION — prevents inserts/updates that violate the view's WHERE
CREATE VIEW High_GPA AS
  SELECT * FROM Student WHERE gpa > 3.5
  WITH CHECK OPTION;

-- Materialized view (stores the result physically — PostgreSQL)
CREATE MATERIALIZED VIEW Dept_Stats AS
  SELECT dept, COUNT(*) AS cnt, AVG(gpa) AS avg_gpa
  FROM   Student
  GROUP BY dept;

REFRESH MATERIALIZED VIEW Dept_Stats;

-- Drop view
DROP VIEW CS_Students;

-- ── TRIGGERS ─────────────────────────────────────────────────────────────────

-- PostgreSQL trigger: validate GPA before insert or update
CREATE OR REPLACE FUNCTION validate_gpa()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.gpa < 0 OR NEW.gpa > 4.0 THEN
    RAISE EXCEPTION 'GPA must be between 0.0 and 4.0, got %', NEW.gpa;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_gpa_check
BEFORE INSERT OR UPDATE ON Student
FOR EACH ROW EXECUTE FUNCTION validate_gpa();

-- Audit trigger — log all deletions
CREATE TABLE Student_Audit (
  deleted_at TIMESTAMP DEFAULT NOW(),
  sid INT, name VARCHAR(100), dept VARCHAR(50)
);

CREATE OR REPLACE FUNCTION audit_student_delete()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO Student_Audit(sid, name, dept) VALUES (OLD.sid, OLD.name, OLD.dept);
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_student_audit
AFTER DELETE ON Student
FOR EACH ROW EXECUTE FUNCTION audit_student_delete();

-- ── STORED PROCEDURES ────────────────────────────────────────────────────────

CREATE OR REPLACE PROCEDURE enroll_student(
  p_sid   INT,
  p_cid   INT,
  p_grade CHAR(2) DEFAULT 'IP'
)
LANGUAGE plpgsql AS $$
BEGIN
  -- Check student exists
  IF NOT EXISTS (SELECT 1 FROM Student WHERE sid = p_sid) THEN
    RAISE EXCEPTION 'Student % not found', p_sid;
  END IF;
  -- Insert enrollment
  INSERT INTO Enrollment(sid, cid, grade, year)
  VALUES (p_sid, p_cid, p_grade, EXTRACT(YEAR FROM NOW())::INT)
  ON CONFLICT (sid, cid) DO UPDATE SET grade = EXCLUDED.grade;
END;
$$;

CALL enroll_student(1, 101, 'A');

-- ── GRANT / REVOKE ────────────────────────────────────────────────────────────

CREATE ROLE student_role;
GRANT SELECT ON Student, Course, Enrollment TO student_role;

CREATE USER alice WITH PASSWORD 'pass123';
GRANT student_role TO alice;

GRANT INSERT, UPDATE ON Enrollment TO alice;
GRANT SELECT ON Student TO alice WITH GRANT OPTION; -- alice can further grant

REVOKE INSERT ON Enrollment FROM alice;
REVOKE student_role FROM alice;

Exam Quick-Reference

Relational algebra evaluation order: σ first, π last, reduce intermediate sizes.
SQL logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
HAVING filters groups; WHERE filters rows (before grouping).
Correlated subquery re-runs per outer row; non-correlated runs once.
View = stored query; Materialized view = stored query result (refreshed).
Trigger fires automatically on INSERT/UPDATE/DELETE; procedure is called explicitly.

Full Syllabus Outline

Every topic from the syllabus data is preserved here.

3.1
Relational algebra
DEPS
Description and usage of relational algebra operators with examples. Selection (σ), projection (π), Cartesian product (×), various types of join (⋈), rename (ρ). Set operations: union (∪), intersection (∩), difference (−). Modification of relations with assignment operator. Example queries and practice problems.
3.2
Concept of DDL, DML and DCL
DE
Description and distinction of Data Definition Language, Data Manipulation Language, and Data Control Language queries along with examples in SQL.
3.3
Overview of the SQL query language — DDL and DML queries
DEPS
Description of SQL and basics. Relation with relational algebra. DDL queries (CREATE, ALTER, DROP) and DML queries (SELECT, INSERT, UPDATE, DELETE) with examples and practice.
3.4
Set operations
DEPS
UNION, INTERSECT, and EXCEPT set operations in SQL queries along with examples and problem solving.
3.5
Aggregate functions – GROUP BY – HAVING
DEPS
COUNT, SUM, AVG, MIN, MAX aggregate functions. GROUP BY clause for grouping. HAVING clause for filtering groups. Examples and problem solving.
3.6
Joins and types of joins
DEPS
INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN, NATURAL JOIN. Join conditions. Practice with given problems.
3.7
Nested sub queries
DEPS
Correlated and non-correlated subqueries. IN / NOT IN, EXISTS / NOT EXISTS, scalar subqueries. Examples and problem solving.
3.8
Database modification (Insert, update, delete)
DEPS
INSERT INTO, UPDATE … SET … WHERE, DELETE FROM … WHERE syntax and examples. Practice with given problems.
3.9
Views
DEPS
Concept of views and significance. CREATE VIEW syntax. WITH CHECK OPTION. Managing access control with views. Updating views. Materialized views.
3.10
Triggers and stored procedures
DEPS
CREATE TRIGGER syntax. BEFORE / AFTER triggers. Row-level vs statement-level. SQL functions and language constructs for stored procedures. CREATE PROCEDURE / FUNCTION. External language routines.
3.11
Privilege and roles management – GRANT and REVOKE statements
DE
Privilege and role-based access control in DBMS. GRANT and REVOKE commands. Creation of users and roles. Authorization graph for database administration.

Solved PYQs

A few past questions with short answers.

Chapter 36 marksasked 1x2082 Kartik B

Consider the University schema: Student, Course, Enrollment, Department, Professor, Teaches. Write relational algebra expressions for: (i) Find sid and name of students who enrolled in 'Database Systems'. (ii) List cid and title of courses with more than 4 credits in Computer Science. (iii) Increase salary of all professors in Mathematics by 15%.

Solution

Relational Algebra Query Strategy:

Relational algebra is a procedural query language using a set of operators applied to relations. For exam queries on a given schema:

Key Operators:

Selection σ_p(R): Filter rows satisfying predicate p. Maps to SQL WHERE.
Projection π_{A1,...,An}(R): Keep only listed attributes; removes duplicates. Maps to SQL SELECT DISTINCT.
Natural Join R ⋈ S: Join on all common attribute names. Maps to SQL NATURAL JOIN.
Theta Join R ⋈_p S: Join on explicit predicate p. Maps to SQL JOIN ... ON p.
Rename ρ_{S(A1,...,An)}(R): Rename relation or attributes to avoid ambiguity.
Assignment R ← expression: Name an intermediate result.
Division R ÷ S: Find tuples in R that relate to all tuples in S. Maps to "for all" SQL queries.

Strategy for Writing Relational Algebra:

Apply σ (selection) first to reduce the number of tuples early.
Apply ⋈ (join) with the minimal predicate needed.
Apply π (projection) to keep only the required attributes.
Use ρ (rename) when the same relation appears twice in a query.

Example: Names of students enrolled in course 'DBMS':

π_{name}(σ_{title='DBMS'}(Student ⋈ Enrollment ⋈ Course))

Chapter 38 marksasked 1x2082 Kartik B

Consider the schema: Person(driver_id, name, address), Accident(report_no, date, location), Owns(driver_id, car), Participated(driver_id, car, report_no, damage_amount). Write SQL for: (i) Names and addresses of drivers who own a car and have been in an accident. (ii) Total damage cost for each accident report. (iii) Accidents where total damage cost exceeded Rs 50,000. (iv) Cars involved in accidents and names of their owners.

Solution

Relational Algebra Operators:

Fundamental Operators:

Operator	Symbol	Description	SQL Equivalent
Selection	σ_p(R)	Filter rows where predicate p holds	WHERE p
Projection	π_{A}(R)	Keep only columns A; remove duplicates	SELECT DISTINCT A
Cartesian Product	R × S	All combinations of rows	FROM R, S (no join condition)
Union	R ∪ S	All rows from either; remove duplicates	UNION
Difference	R − S	Rows in R but not in S	EXCEPT
Rename	ρ_S(R)	Rename relation R to S	AS alias

Derived Operators (built from fundamental ones):

Operator	Symbol	Definition
Natural Join	R ⋈ S	σ_{eq on common attrs}(R × S), then project to remove duplicates
Theta Join	R ⋈_p S	σ_p(R × S)
Intersection	R ∩ S	R − (R − S)
Division	R ÷ S	Tuples in R that pair with every tuple in S

Set Operation Requirements: Union, intersection, and difference require union-compatible relations: same number of attributes with compatible domains in corresponding positions.

Example Queries on Student(sid, name, dept, gpa) schema:

-- Students in CS with GPA > 3.5
σ_{dept='CS' ∧ gpa>3.5}(Student)

-- Names and departments only
π_{name, dept}(Student)

-- Students enrolled in course 101
π_{name}(Student ⋈ σ_{cid=101}(Enrollment))

Chapter 36 marksasked 1x2081 Chaitra R

Consider the Library database: Book, Member, IssuedBook, Librarian, Manages. Write relational algebra for: (i) Find Book title, author name and publication year where genre is Novel. (ii) Delete all information of members from Kathmandu City. (iii) Find book title and librarian name who managed books published by Pearson.

Solution

Writing Relational Algebra Queries:

Key Principles:

Push σ before ⋈: Apply selections as early as possible to reduce the size of intermediate results before joining. This is the most important optimization in relational algebra.
Use ρ for self-joins: When the same relation appears twice, rename one instance: ρ_{E2(eid2, mgr_id)}(Employee) to avoid attribute name conflicts.
Use Assignment ← to break complex expressions into named intermediate steps for readability.
Division ÷ for "for all" queries: "Find students enrolled in all courses offered by dept CS."

Mapping Relational Algebra to SQL:

Relational Algebra	SQL Equivalent
σ_{p}(R)	WHERE p
π_{A1,...,An}(R)	SELECT DISTINCT A1,...,An
R ⋈ S	NATURAL JOIN or JOIN...ON
R ⋈_p S	JOIN...ON p
R ∪ S	UNION
R ∩ S	INTERSECT
R − S	EXCEPT
ρ_S(R)	AS alias in FROM clause

Example: Find the names of employees who work in the same department as employee with eid=5:

T ← σ_{eid=5}(Employee)
Result ← π_{name}(Employee ⋈_{Employee.dept = T.dept} T) − π_{name}(T)

More PYQs

Additional practice from this chapter.

Consider the University schema: Student, Course, Enrollment, Department, Professor, Teaches. Write relational algebra expressions for: (i) Find sid and name of students who enrolled in 'Database Systems'. (ii) List cid and title of courses with more than 4 credits in Computer Science. (iii) Increase salary of all professors in Mathematics by 15%.
6 marksasked 1x2082 Kartik B
Consider the schema: Person(driver_id, name, address), Accident(report_no, date, location), Owns(driver_id, car), Participated(driver_id, car, report_no, damage_amount). Write SQL for: (i) Names and addresses of drivers who own a car and have been in an accident. (ii) Total damage cost for each accident report. (iii) Accidents where total damage cost exceeded Rs 50,000. (iv) Cars involved in accidents and names of their owners.
8 marksasked 1x2082 Kartik B
Consider the Library database: Book, Member, IssuedBook, Librarian, Manages. Write relational algebra for: (i) Find Book title, author name and publication year where genre is Novel. (ii) Delete all information of members from Kathmandu City. (iii) Find book title and librarian name who managed books published by Pearson.
6 marksasked 1x2081 Chaitra R
Consider the Library database. Write SQL for: (i) Display member names who issued books on Jan 20, 2025. (ii) Display librarian name who issued book title starting with 'D' using subquery. (iii) Update salaries: 10% raise for salary < 50000, 5% for the rest. (iv) Create view BOOKMANAGER with Book Id, Title, Librarian ID and Name for books published 1990–2010 by 'Wiley'.
8 marksasked 1x2081 Chaitra R
Consider: lives(person-name, street, city), works(person-name, company-name, salary), located-in(company-name, city), manages(person-name, manager-name). Write relational algebra for: (i) Persons working at 'City Bank' earning more than $50,000. (ii) Persons living in the same city as the company they work for. (iii) Increase salary of all City Bank persons by 5%. (iv) QBE expression for Manager records with name starting with 'M' and salary > 60,000.
8 marksasked 1x2081 Ashwin B

Relational Query Languages

Chapter 3 — Relational Query Languages

3.1 Relational Algebra Operators

3.2 SQL Reference Schema (used in all examples)

3.3 SELECT Statement — Full Syntax

Exam Quick-Reference

Relational algebra

Concept of DDL, DML and DCL

Overview of the SQL query language — DDL and DML queries

Set operations

Aggregate functions – GROUP BY – HAVING

Joins and types of joins

Nested sub queries

Database modification (Insert, update, delete)

Views

Triggers and stored procedures

Privilege and roles management – GRANT and REVOKE statements