GATE CS SQL Previous Year Questions

GATE CS 2025 Set 1 | Question 55

NAT · 2 marks

Consider the following database tables of a sports league.

player(pid,pname,age)          team(tid,tname,city,cid)
coach(cid,cname)               members(pid,tid)

An instance of the table and an SQL query are given.

player
pid	pname	age
1	Jasprit	31
2	Atharva	24
3	Ishan	26
4	Axar	30

coach
cid	cname
101	Ricky
102	Mark
103	Trevor

team
tid	tname	city	cid
10	MI	Mumbai	102
20	DC	Delhi	101
30	PK	Mohali	103

members
pid	tid
1	10
2	30
3	10
4	20

SELECT MIN(P.age)
FROM player P
WHERE P.pid IN (
    SELECT M.pid
    FROM team T, coach C, members M
    WHERE C.cname = 'Mark'
          AND T.cid = C.cid
          AND M.tid = T.tid
    )

The value returned by the given SQL query is ______ . (Answer in integer)

Your answer

Explanation

Answer: 26

The subquery joins coach, team and members to find the players in teams coached by Mark.

coach: Mark has cid = 102.
team: the team with cid = 102 is MI, with tid = 10.
members: tid = 10 gives pid 1 and pid 3.

The outer query takes the smaller age of pid 1 (Jasprit, 31) and pid 3 (Ishan, 26), which is 26.

Answer: 26

GATE CS 2025 Set 2 | Question 54

MSQ · 2 marks

Consider the following relational schema:

Students (rollno: integer, name: string, age: integer, cgpa: real)

Courses (courseno: integer, cname: string, credits: integer)

Enrolled (rollno: integer, courseno: integer, grade: string)

Which of the following options is/are correct SQL query/queries to retrieve the names of the students enrolled in course number (i.e., courseno) 1470?

SELECT S.name
FROM Students S
WHERE EXISTS (SELECT * FROM Enrolled E
              WHERE E.courseno = 1470
                      AND E.rollno = S.rollno);

SELECT S.name
FROM Students S
WHERE SIZEOF (SELECT * FROM Enrolled E
              WHERE E.courseno = 1470
                      AND E.rollno = S.rollno) > 0;

SELECT S.name
FROM Students S
WHERE  0 < (SELECT COUNT(*)
            FROM Enrolled E
            WHERE E.courseno = 1470
                      AND E.rollno = S.rollno);

SELECT S.name
FROM Students S NATURAL JOIN Enrolled E
WHERE E.courseno = 1470;

Explanation

Answer: (A), (C), (D)

(A) For each student, the correlated EXISTS subquery is true exactly when an Enrolled row has that student's roll number and course 1470. Correct.
(B) SIZEOF is not an SQL operator or aggregate function, so this is not a valid query.
(C) The correlated COUNT(*) counts the student's enrolments in course 1470, and requiring it to be greater than 0 is the same test as (A). Correct.
(D) rollno is the only attribute common to Students and Enrolled, so the natural join pairs each student with their enrolments and the filter keeps course 1470. Because (rollno, courseno) is the key of Enrolled, each such student appears once. Correct.

Answer: A, C, D