JUN/JULY -2025 Problems
Core Answer
(i)
SELECT DISTINCT Sname
FROM Student, Class, Enrolled, Faculty
WHERE Student.Snum = Enrolled.Snum
AND Enrolled.Cname = Class.Cname
AND Class.fid = Faculty.fid
AND Student.level = 'JR'
AND Faculty.fname = 'I. Teach';
(ii)
SELECT DISTINCT Cname
FROM Class
WHERE room = 'R128'
UNION
SELECT Cname
FROM Enrolled
GROUP BY Cname
HAVING COUNT(*) >= 5;
(iii)
SELECT level, AVG(age)
FROM Student
WHERE level <> 'JR'
GROUP BY level;
(iv)
SELECT F.fname, COUNT(DISTINCT C.Cname)
FROM Faculty F JOIN Class C ON F.fid = C.fid
WHERE NOT EXISTS (
SELECT 1
FROM Class C2
WHERE C2.fid = F.fid AND C2.room <> 'R128'
GROUP BY F.fname;
(v)
SELECT Sname
FROM Student
WHERE Snum NOT IN (SELECT Snum FROM Enrolled);
DEC/JAN-2025 PROBLEMS
MQ1 PROBLEMS
Q4.a Consider the Sailors-Boats-Reserves DB described
s (sid, sname, rating, age) b (bid, bname, color)
r (sid, bid, date)
Write each of the following queries in SQL.
1. Find the colors of boats reserved by Alber.
2. Find all sailor ids of sailors who have a rating of at least 8 or reserved boat
103.
3. Find the names of sailors who have not reserved a boat whose name
contains the string
“storm”. Order the names in ascending order.
4. Find the sailor ids of sailors with age over 20 who have not reserved a boat
whose name
includes the string “thunder”.
1. Find the colors of boats reserved by Alber.
To find the colors of boats reserved by a sailor named "Alber," you need to
join the Sailors,
Reserves, and Boats tables. First, identify the sid for the sailor named
"Alber," and then use it
to find the colors of the boats reserved by this sailor.
SELECT DISTINCT b.color
FROM Sailors s
JOIN Reserves r ON s.sid = r.sid
JOIN Boats b ON r.bid = b.bid
WHERE s.sname = 'Alber';
2. Find all sailor ids of sailors who have a rating of at least 8 or reserved boat
103.
To find all sailor ids of sailors who either have a rating of at least 8 or have
reserved boat 103,
you need to combine results based on these two conditions.
SELECT DISTINCT s.sid
FROM Sailors s
LEFT JOIN Reserves r ON s.sid = r.sid
WHERE s.rating >= 8 OR r.bid = 103;
3. Find the names of sailors who have not reserved a boat whose name
contains
the string “storm”. Order the names in ascending order.
To find the names of sailors who have not reserved any boat with a name
containing "storm,"
you need to use a subquery to exclude sailors who have reserved such boats.
SELECT DISTINCT s.sname
FROM Sailors s
WHERE s.sid NOT IN (
SELECT r.sid
FROM Reserves r
JOIN Boats b ON r.bid = b.bid
WHERE b.bname LIKE '%storm%'
ORDER BY s.sname ASC;
4. Find the sailor ids of sailors with age over 20 who have not reserved a boat
whose name includes the string “thunder”.
To find the sailor ids of sailors older than 20 who have not reserved a boat
with a name
containing "thunder," you need a subquery to exclude those who have
reserved such boat
SELECT DISTINCT s.sid
FROM Sailors s
WHERE s.age > 20 AND s.sid NOT IN (
SELECT r.sid
FROM Reserves r
JOIN Boats b ON r.bid = b.bid
WHERE b.bname LIKE '%thunder%'
);
Q5.a Explain the Cursor & its properties in embedded SQL with an example.
A cursor is a database object used to retrieve and process rows from a query
result set one at a
time.
Properties of Cursors
1. Declare: Define the cursor with a DECLARE statement, specifying the SQL
query.
2. Open: Use OPEN to execute the query and create the result set.
3. Fetch: Use FETCH to retrieve individual rows from the result set.
4. Close: Use CLOSE to release resources associated with the cursor.
5. Deallocate: Optionally use DEALLOCATE to remove the cursor definition
and free
resources.
Example
Here’s a basic example using a cursor in embedded SQL: -- Declare the
cursor
DECLARE emp_cursor CURSOR FOR
SELECT emp_id, emp_name, salary FROM Employees; -- Open the cursor
OPEN emp_cursor; -- Fetch rows
FETCH NEXT FROM emp_cursor INTO @emp_id, @emp_name, @salary; --
Process rows
WHILE @@FETCH_STATUS = 0
BEGIN -- Do something with the data (e.g., print it)
PRINT 'ID: ' + CAST(@emp_id AS VARCHAR) + ', Name: ' + @emp_name; --
Fetch the next row
FETCH NEXT FROM emp_cursor INTO @emp_id, @emp_name, @salary;
END; -- Close the cursor
CLOSE emp_cursor; -- Deallocate the cursor
DEALLOCATE emp_cursor;
