CSE 3221 — Database Management Systems

Module 3: SQL Queries — Complete Model Answers (Full Marks Guide)

Question 1 — Suppliers / Parts / Catalog

Schema: Suppliers(supplier_id, supplier_name, address) | Parts(part_id, part_name, color) |
Catalog(supplier_id, part_id, cost)

(i) Find the names of suppliers who supply some red part.
SELECT DISTINCT S.supplier_name
FROM Suppliers S, Catalog C, Parts P
WHERE S.supplier_id = C.supplier_id
AND C.part_id = P.part_id
AND [Link] = 'red';

DISTINCT ensures each supplier name appears only once even if they supply multiple red parts.

(ii) Find the supplier_ids of suppliers who supply every part.

SELECT C.supplier_id
FROM Catalog C
GROUP BY C.supplier_id
HAVING COUNT(DISTINCT C.part_id) = (SELECT COUNT(*) FROM Parts);

A supplier 'supplies every part' when the count of distinct parts they supply equals the total number of
parts.

(iii) Find the part_ids of the most expensive parts supplied by suppliers named 'Pipe
Supplier'.
SELECT C.part_id
FROM Catalog C, Suppliers S
WHERE C.supplier_id = S.supplier_id
AND S.supplier_name = 'Pipe Supplier'
AND [Link] = (
SELECT MAX([Link])
FROM Catalog C2, Suppliers S2
WHERE C2.supplier_id = S2.supplier_id
AND S2.supplier_name = 'Pipe Supplier'
);

The subquery finds the maximum cost among all parts supplied by 'Pipe Supplier'; the outer query retrieves
all parts at that price.

Question 2 — Engineering College Database

Schema: STUDENT(rollno, name, courseId, session) | COURSE(courseId, courseName, Dept_Id) |
SUBJECT_PAPER(pcode, pname, courseId, semesterNo) | MARKS_OBTAINED(rollno, pcode,
marks, year_of_exam) | DEPARTMENT(Dept_Id, Dname) | Faculty(empid, name, sal, Dept_id) |
Subject_Taught(empid, pcode, session)

(i) Find the name of the topper(s) of CSE 1st Semester of 2005 session.
SELECT [Link]
FROM STUDENT ST
WHERE [Link] = '2005'
 AND [Link] IN (SELECT courseId FROM COURSE WHERE courseName LIKE '%C
SE%')
AND [Link] IN (
SELECT [Link]
FROM MARKS_OBTAINED M, SUBJECT_PAPER SP
WHERE [Link] = [Link]
AND [Link] = 1
AND [Link] IN (SELECT courseId FROM COURSE WHERE courseNa
me LIKE '%CSE%')
AND M.year_of_exam = 2005
GROUP BY [Link]
HAVING SUM([Link]) = (
SELECT MAX(total)
FROM (
SELECT SUM([Link]) AS total
FROM MARKS_OBTAINED M2, SUBJECT_PAPER SP2
WHERE [Link] = [Link]
AND [Link] = 1
AND [Link] IN (SELECT courseId FROM COU
RSE WHERE courseName LIKE '%CSE%')
AND M2.year_of_exam = 2005
GROUP BY [Link]
) AS agg
)
);

The innermost subquery computes the aggregate for every student; HAVING picks those whose aggregate
equals the maximum.

(ii) Faculties who taught the maximum number of subjects in odd semesters of 2017
session, with count.
SELECT [Link], COUNT(DISTINCT [Link]) AS subjects_taught
FROM Faculty F
JOIN Subject_Taught ST ON [Link] = [Link]
JOIN SUBJECT_PAPER SP ON [Link] = [Link]
WHERE [Link] = '2017'
AND [Link] % 2 = 1 -- odd semesters (1,3,5,...)
GROUP BY [Link], [Link]
HAVING COUNT(DISTINCT [Link]) = (
SELECT MAX(cnt)
FROM (
SELECT COUNT(DISTINCT [Link]) AS cnt
FROM Subject_Taught ST2
JOIN SUBJECT_PAPER SP2 ON [Link] = [Link]
WHERE [Link] = '2017'
AND [Link] % 2 = 1
GROUP BY [Link]
) AS counts
 );

(iii) Display the name of the department that conducts '[Link] in VLSI Design'.
SELECT [Link]
FROM DEPARTMENT D, COURSE C
WHERE D.Dept_Id = C.Dept_Id
AND [Link] = '[Link] in VLSI Design';

(iv) Lowest salary of each department with department name, in descending order of lowest
salary.
SELECT [Link], MIN([Link]) AS lowest_salary
FROM DEPARTMENT D, Faculty F
WHERE D.Dept_Id = F.Dept_id
GROUP BY D.Dept_Id, [Link]
ORDER BY lowest_salary DESC;

Question 3(a) — Sailors / Boats / Reserves

Schema: Sailors(sid, sname, rating, age) | Reserves(sid, bid, date) | Boats(bid, bname, color)

1) Find names of sailors who've reserved boat id 103.

SELECT DISTINCT [Link]
FROM Sailors S, Reserves R
WHERE [Link] = [Link]
AND [Link] = 103;

2) Find names of sailors who've reserved a red boat.

SELECT DISTINCT [Link]
FROM Sailors S, Reserves R, Boats B
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'red';

3) Find sailors who've reserved a red OR a green boat.

SELECT DISTINCT [Link]
FROM Sailors S, Reserves R, Boats B
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] IN ('red', 'green');

4) Find sailors who've reserved a red AND a green boat.

SELECT [Link]
FROM Sailors S
WHERE [Link] IN (
SELECT [Link] FROM Reserves R1, Boats B1
WHERE [Link] = [Link] AND [Link] = 'red'
)
AND [Link] IN (
SELECT [Link] FROM Reserves R2, Boats B2
WHERE [Link] = [Link] AND [Link] = 'green'
 );

AND means both conditions must hold for the same sailor — achieved by two separate IN subqueries.

5) Find the names of sailors who've reserved ALL boats.

SELECT [Link]
FROM Sailors S
WHERE NOT EXISTS (
SELECT [Link]
FROM Boats B
WHERE NOT EXISTS (
SELECT [Link]
FROM Reserves R
WHERE [Link] = [Link]
AND [Link] = [Link]
)
);

Double NOT EXISTS is the standard SQL idiom for 'for all' (universal quantification).

Question 3(b) — INSERT vs UPDATE

INSERT adds a new row (tuple) to a table. It does not modify existing data.
-- Syntax:
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

-- Example: Add a new sailor

INSERT INTO Sailors (sid, sname, rating, age)
VALUES (101, 'Alice', 8, 25);

UPDATE modifies existing rows that satisfy a condition. No new rows are added.
-- Syntax:
UPDATE table_name SET col1 = val1, col2 = val2 WHERE condition;

-- Example: Change Alice's rating to 9

UPDATE Sailors
SET rating = 9
WHERE sname = 'Alice';

Key Differences:
• INSERT creates a new record; UPDATE modifies an existing record.
• INSERT requires values for all NOT NULL columns; UPDATE changes only specified columns.
• Without a WHERE clause, UPDATE modifies ALL rows — always use WHERE with UPDATE.

Question 4(a) — Train / Berth_Seat Database

Schema: Train(train_no, train_name, start_station) | Berth_Seat(SeatNo, train_no, coach_type,
price_perKm)

(i) List all train names starting from 'Kolkata'.

 SELECT train_name
FROM Train
WHERE start_station = 'Kolkata';

(ii) Train number and SL price for trains from 'Howrah' terminating at 'Delhi'.
Note: The given schema has start_station but no end_station column. Assuming Train is extended with
end_station for this query.
SELECT T.train_no, B.price_perKm
FROM Train T, Berth_Seat B
WHERE T.train_no = B.train_no
AND B.coach_type = 'SL'
AND T.start_station = 'Howrah'
AND T.end_station = 'Delhi';

(iii) Train name whose price/km under '3AC' coach type is maximum.
SELECT T.train_name
FROM Train T, Berth_Seat B
WHERE T.train_no = B.train_no
AND B.coach_type = '3AC'
AND B.price_perKm = (
SELECT MAX(price_perKm)
FROM Berth_Seat
WHERE coach_type = '3AC'
);

Question 4(b) — Referential, Entity & Domain Integrity

1. Entity Integrity:
The primary key of a table must be unique and NOT NULL. No part of a primary key can hold a null
value, because the primary key is used to uniquely identify every row.
-- Example: sid is PK — cannot be NULL or duplicate
CREATE TABLE Sailors (
sid INT PRIMARY KEY, -- enforces entity integrity
sname VARCHAR(50),
rating INT,
age INT
);

2. Referential Integrity:
A foreign key value in a child table must either match an existing primary key value in the referenced
(parent) table, or be NULL. It prevents orphan records.
-- Example: [Link] must exist in [Link]
CREATE TABLE Reserves (
sid INT,
bid INT,
date DATE,
FOREIGN KEY (sid) REFERENCES Sailors(sid) -- referential integrity
);

3. Domain Integrity:
 Every value stored in a column must belong to the defined domain (data type, range, format,
constraints like CHECK, NOT NULL, DEFAULT).
-- Example: rating must be between 1 and 10
CREATE TABLE Sailors (
sid INT PRIMARY KEY,
sname VARCHAR(50) NOT NULL,
rating INT CHECK (rating BETWEEN 1 AND 10), -- domain integrity
age INT CHECK (age > 0)
);

Question 5 — Employee / Department

Schema: Employee(Emp_Code, Emp_Name, Desig, Manager, Date_of_Joining, Salary, Dept_Code) |
Department(Dept_Code, Dept_Name, Location)

(i) Average salary and number of employees in each department.

SELECT D.Dept_Name,
AVG([Link]) AS avg_salary,
COUNT(E.Emp_Code) AS num_employees
FROM Department D, Employee E
WHERE D.Dept_Code = E.Dept_Code
GROUP BY D.Dept_Code, D.Dept_Name;

(ii) Names of departments where total salary > 15000.

SELECT D.Dept_Name
FROM Department D, Employee E
WHERE D.Dept_Code = E.Dept_Code
GROUP BY D.Dept_Code, D.Dept_Name
HAVING SUM([Link]) > 15000;

(iii) Names of employees and the names of their managers.

SELECT E.Emp_Name AS employee,
M.Emp_Name AS manager
FROM Employee E
JOIN Employee M ON [Link] = M.Emp_Code;

Self-join on Employee: E is the employee, M is their manager (also an employee).

(iv) Employees earning more than the average salary of their department.
SELECT E.*
FROM Employee E
WHERE [Link] > (
SELECT AVG([Link])
FROM Employee E2
WHERE E2.Dept_Code = E.Dept_Code
);

Correlated subquery: for each employee, compute the average of their own department and compare.

Question 6 — EMPLOYEE Table (CREATE + Queries)

Table Creation:
 CREATE TABLE EMPLOYEE (
EName VARCHAR(100) NOT NULL,
E_Id INT PRIMARY KEY,
Date_of_Birth DATE,
Salary DECIMAL(10,2),
City VARCHAR(50),
Pincode VARCHAR(10)
);

(i) Name and Id of employees with salary between 80,000 and 90,000 (exclusive).
SELECT EName, E_Id
FROM EMPLOYEE
WHERE Salary > 80000
AND Salary < 90000;

Question says 'greater than 80,000 AND lesser than 90,000' — exclusive bounds, so > and < are used (not
BETWEEN).

(ii) Name and Id of employees from Mumbai/Kolkata/Pune/Hyderabad with salary > 100000.
SELECT EName, E_Id
FROM EMPLOYEE
WHERE City IN ('MUMBAI', 'KOLKATA', 'PUNE', 'HYDERABAD')
AND Salary > 100000;

Question 7 — DEPT / EMP Tables

Schema: DEPT(DCODE, DNAME) | EMP(ECODE, ENAME, BASIC, DCODE, DT_JN)

(i) For each department, show DNAME and total basic salary.
SELECT [Link], SUM([Link]) AS total_basic
FROM DEPT D, EMP E
WHERE [Link] = [Link]
GROUP BY [Link], [Link];

(ii) Name of departments where no person is working.

SELECT [Link]
FROM DEPT D
WHERE [Link] NOT IN (
SELECT DISTINCT [Link]
FROM EMP E
WHERE [Link] IS NOT NULL
);

Alternative using LEFT JOIN:

SELECT [Link]
FROM DEPT D
LEFT JOIN EMP E ON [Link] = [Link]
WHERE [Link] IS NULL;

(iii) Names of employees working in department named 'PQR'.

SELECT [Link]
FROM EMP E, DEPT D
 WHERE [Link] = [Link]
AND [Link] = 'PQR';

(iv) Maximum basic among employees who joined after year 2000.
SELECT MAX(BASIC) AS max_basic
FROM EMP
WHERE YEAR(DT_JN) > 2000;

Use EXTRACT(YEAR FROM DT_JN) > 2000 in standard SQL / PostgreSQL instead of YEAR().

Question 8 — Flight / Seat / Schedule

Schema: Flight(flight_no, flight_name, start_airport) | Seat(seat_no, flight_no, type, price) |
Schedule(flight_no, day_of_week, type)

(i) All flight names starting from station 'ABC'.

SELECT flight_name
FROM Flight
WHERE start_airport = 'ABC';

(ii) Price and type of all seats of 'PQR' flight.

SELECT [Link], [Link]
FROM Seat S, Flight F
WHERE S.flight_no = F.flight_no
AND F.flight_name = 'PQR';

(iii) Flight number and price of all 'business' type seats with price below Rs 6000.
SELECT flight_no, price
FROM Seat
WHERE type = 'business'
AND price < 6000;

(iv) All flight names scheduled to run on BOTH Saturdays AND Sundays.
SELECT F.flight_name
FROM Flight F
WHERE F.flight_no IN (
SELECT S1.flight_no
FROM Schedule S1
WHERE S1.day_of_week = 'Saturday'
)
AND F.flight_no IN (
SELECT S2.flight_no
FROM Schedule S2
WHERE S2.day_of_week = 'Sunday'
);

Two IN subqueries ensure the flight is scheduled on BOTH days, not just one.

— End of Model Answers — CSE 3221 Module 3: SQL —