High yield ●●●

🗄️ SQL

Why it matters: Output-of-a-query questions are the most frequent database marks. Know the evaluation order, aggregates with NULL, and nested queries.

Subtopics

Each subtopic has its own page: idea, why, how, example, play, check. Mark each one understood when it clicks. 0 of 5 done.

Formulas

Evaluation order
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
COUNT(*) vs COUNT(col)
COUNT(*) counts all rows; COUNT(col) skips NULLs
Empty set
COUNT = 0; SUM / AVG / MIN / MAX = NULL; x > ALL(∅) = true; x > ANY(∅) = false
Three-valued logic
F∧U = F, T∧U = U, T∨U = T, F∨U = U, ¬U = U
Row counts of joins
cross join = m · n; inner join ≤ m · n; left outer ≥ m
Division in SQL
NOT EXISTS (… WHERE NOT EXISTS (…))

What GATE asks from this

Question types seen repeatedly in past papers. More stars means more frequent.

★★★
Output of a SQL query on given tables
Run FROM, WHERE, GROUP BY, HAVING in order; track NULLs.
★★★
Number of rows returned by a join or aggregate
Count matches per key; remember outer-join padding.
★★★
Which SQL query solves this requirement
Nested / correlated subqueries, NOT EXISTS, HAVING versus WHERE.
★★★
Statements about views, constraints, NULL
Updatable views, UNIQUE with NULL, CHECK passing on UNKNOWN.

Ratings are approximate, based on recurring patterns in past GATE CSE papers.

Previous year questions

13 real questions from the official GATE CSE papers (2011 to 2025), newest first, each with the official answer key and a worked solution. Questions are the property of the GATE organising institutes; this copy is for your personal study.

13 questions · 0 correct of 0 attempted · marks 0 / 22

Year:
GATE 2025 CS2 · Q54MSQ2 marks
Consider the relational schema Students(rollno, name, age, cgpa), Courses(courseno, cname, credits), Enrolled(rollno, courseno, grade). Which of the following is/are correct SQL queries to retrieve the names of the students enrolled in course number 1470? (A)
SELECT S.name
FROM Students S
WHERE EXISTS (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno)
(B) the same with WHERE SIZEOF(
SELECT *
FROM Enrolled E
WHERE E.courseno = 1470 AND E.rollno = S.rollno) > 0
(C) the same with WHERE 0 < (
SELECT COUNT(*)
FROM Enrolled E
WHERE E.courseno = 1470 AND E.rollno = S.rollno)
(D)
SELECT S.name
FROM Students S NATURAL JOIN Enrolled E
WHERE E.courseno = 1470

One or more options may be correct. Select all that apply, then submit.

GATE 2025 CS1 · Q55NAT2 marks
Consider the following database tables of a sports league: player(pid, pname, age), coach(cid, cname), team(tid, tname, city, cid), members(pid, tid). Instance:
player = (1, Jasprit, 31), (2, Atharva, 24), (3, Ishan, 26), (4, Axar, 30);
coach = (101, Ricky), (102, Mark), (103, Trevor);
team = (10, MI, Mumbai, 102), (20, DC, Delhi, 101), (30, PK, Mohali, 103);
members = (1, 10), (2, 30), (3, 10), (4, 20). Query:
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 is ______.
GATE 2023 · Q61NAT2 marks
Consider the following table named Student in a relational database. The primary key of this table is rollNum. Student(rollNum, name, gender, marks): (1, Naman, M, 62), (2, Aliya, F, 70), (3, Aliya, F, 80), (4, James, M, 82), (5, Swati, F, 65). The SQL query
SELECT *
FROM Student
WHERE gender = 'F' AND marks > 65
is executed. The number of rows returned by the query is ______.
GATE 2022 · Q56NAT2 marks
Consider the relational database with schemas Student(sNo, sName, dNo), Dept(dNo, dName), Course(cNo, cName, dNo), Register(sNo, cNo). Instances: Student = (S01, James, D01), (S02, Rocky, D01), (S03, Jackson, D02), (S04, Jane, D01), (S05, Milli, D02). Dept = (D01, CSE), (D02, EEE). Course = (C11, DS, D01), (C12, OS, D01), (C21, DE, D02), (C22, PT, D02), (C23, CV, D03). Register = (S01, C11), (S01, C12), (S02, C11), (S03, C21), (S03, C22), (S03, C23), (S04, C11), (S04, C12), (S05, C11), (S05, C21). SQL Query:
SELECT *
FROM Student AS S
WHERE NOT EXISTS (SELECT cNo FROM Course WHERE dNo = "D01" EXCEPT SELECT cNo FROM Register WHERE sNo = S.sNo)
The number of rows returned by the above SQL query is ______.
GATE 2020 · Q13MCQ1 mark
Consider a relational database containing the schemas Catalogue(sno, pno, cost) with rows (S1,P1,150), (S1,P2,50), (S1,P3,100), (S2,P4,200), (S2,P5,250), (S3,P1,250), (S3,P2,150), (S3,P5,300), (S3,P4,250); Suppliers(sno, sname, location) with (S1, M/s Royal furniture, Delhi), (S2, M/s Balaji furniture, Bangalore), (S3, M/s Premium furniture, Chennai); Parts(pno, pname, part_spec).
SELECT s.sno, s.sname
FROM Suppliers s, Catalogue c
WHERE s.sno = c.sno AND cost > (SELECT AVG(cost) FROM Catalogue WHERE pno = 'P4' GROUP BY pno)
The number of rows returned by the above SQL query is
GATE 2019 Set 2 · Q51NAT2 marks
A relational database contains two tables Student and Performance. Student(Roll_no, Student_name) = (1, Amit), (2, Priya), (3, Vinit), (4, Rohan), (5, Smita). Performance(Roll_no, Subject_code, Marks) = (1, A, 86), (1, B, 95), (1, C, 90), (2, A, 89), (2, C, 92), (3, C, 80). The primary key of the Student table is Roll_no. For the Performance table, Roll_no and Subject_code together form the primary key.
SELECT S.Student_name, sum(P.Marks)
FROM Student S, Performance P
WHERE P.Marks > 84
GROUP BY S.Student_name
The number of rows returned by the above SQL query is ______.
GATE 2018 · Q12MCQ1 mark
Consider the following two tables and four queries in SQL. Book(isbn, bname), Stock(isbn, copies). Query 1:
SELECT B.isbn, S.copies
FROM Book B INNER JOIN Stock S ON B.isbn = S.isbn
Query 2: the same with LEFT OUTER JOIN; Query 3: the same with RIGHT OUTER JOIN; Query 4: the same with FULL OUTER JOIN. Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?
GATE 2017 Set 1 · Q23NAT1 mark
Consider a database that has the relation schema EMP(EmpId, EmpName, DeptName). EMP = (1,XYA,AA), (2,XYB,AA), (3,XYC,AA), (4,XYD,AA), (5,XYE,AB), (6,XYF,AB), (7,XYG,AB), (8,XYH,AC), (9,XYI,AC), (10,XYJ,AC), (11,XYK,AD), (12,XYL,AD), (13,XYM,AE). The query is:
SELECT AVG(EC.Num)
FROM EC
WHERE (DeptName, Num) IN (SELECT DeptName, COUNT(EmpId) AS EC(DeptName, Num) FROM EMP GROUP BY DeptName)
The output of executing the SQL query is ______.
GATE 2017 Set 1 · Q46NAT2 marks
Consider a database that has the relation schema CR(StudentName, CourseName). The instance is: SA: CA, CB, CC; SB: CB, CC; SC: CA, CB, CC; SD: CA, CB, CC, CD; SE: CD, CA, CB; SF: CA, CB, CC. The following query is made: T1 ← πCourseName(σStudentName = 'SA'(CR)); T2 ← CR ÷ T1. The number of rows in T2 is ______.
GATE 2014 Set 1 · Q22MCQ1 mark
Given the following statements: S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL. S2: Given the table R(a, b, c) where a and b together form the primary key, the following is a valid table definition: CREATE TABLE S (a INTEGER, d INTEGER, e INTEGER, PRIMARY KEY (d), FOREIGN KEY (a) references R). Which one of the following statements is CORRECT?
GATE 2014 Set 1 · Q54MCQ2 marks
Given the schema employees(emp-id, first-name, last-name, hire-date, dept-id, salary) and departments(dept-id, dept-name, manager-id, location-id). You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the query:
SELECT last-name, hire-date
FROM employees
WHERE (dept-id, hire-date) IN (SELECT dept-id, MAX(hire-date) FROM employees JOIN departments USING(dept-id) WHERE location-id = 1700 GROUP BY dept-id)
What is the outcome?
GATE 2012 Code A · Q51MCQ2 marks
Using the relations A = {(12, Arun, 60), (15, Shreya, 24), (99, Rohit, 11)} and B = {(15, Shreya, 24), (25, Hari, 40), (98, Rohit, 20), (99, Rohit, 11)} (Id, Name, Age), how many tuples does the result of the following SQL query contain?
SELECT A.Id
FROM A
WHERE A.Age > ALL (SELECT B.Age FROM B WHERE B.Name = 'Arun')
GATE 2011 Code A · Q46MCQ2 marks
The database table Loan_Records(Borrower, Bank_Manager, Loan_Amount) is: (Ramesh, Sunderajan, 10000.00), (Suresh, Ramgopal, 5000.00), (Mahesh, Sunderajan, 7000.00). What is the output of the following SQL query?
SELECT count(*)
FROM ((SELECT Borrower, Bank_Manager FROM Loan_Records) AS S NATURAL JOIN (SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T)

Common traps

WHERE cannot use aggregates; HAVING can.
COUNT(col) ignores NULL, COUNT(*) does not.
NOT IN with a NULL in the subquery returns nothing; NOT EXISTS does not have this problem.
A UNIQUE column can hold several NULLs in most systems; a primary key cannot.