GATE CS - DATABASES (DBMS):SQL Queries

SQL Queries for GATE CS

GATE SQL is almost never “write a full application schema.” It is “what does this query return?”, “which join is correct?”, or “WHERE vs HAVING / GROUP BY” on a tiny schema. Read every alias carefully.

A schema to keep in your head

Student(sid, name, dept, age)
Enrollment(sid, cid, grade)
Course(cid, cname, credits)

SELECT, WHERE, and aggregates

SELECT name, age
FROM Student
WHERE age > 20;

Aggregates: COUNT, SUM, AVG, MAX, MIN. COUNT(*) counts rows; COUNT(col) ignores NULLs in col.

SELECT dept, COUNT(*) AS n
FROM Student
GROUP BY dept
HAVING COUNT(*) > 10;

WHERE filters rows before grouping. HAVING filters groups after GROUP BY. Mixing them up is a classic wrong option.

Joins

INNER JOIN — rows with a match on both sides.

SELECT s.name, e.grade
FROM Student s
INNER JOIN Enrollment e ON s.sid = e.sid;

LEFT JOIN — all left rows; right columns NULL when unmatched. RIGHT / FULL are symmetric ideas. CROSS JOIN — Cartesian product (no ON).

Common trap: assuming LEFT JOIN “filters” like WHERE on the right table without thinking about NULLs. A condition on the right table in WHERE can turn a LEFT JOIN into an INNER JOIN effectively.

Subqueries

Scalar (one value):

SELECT name FROM Student
WHERE age > (SELECT AVG(age) FROM Student);

Correlated (references outer row):

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

IN (subquery) is common; empty IN is false. Prefer EXISTS when you only care about presence.

Set ops

UNION removes duplicates; UNION ALL keeps them. INTERSECT / EXCEPT (MINUS) follow the usual set meanings. Column counts and compatible types must match.

Worked pattern: “students in course 1470”

SELECT S.name
FROM Student S
WHERE EXISTS (
  SELECT * FROM Enrollment E
  WHERE E.cid = 1470 AND E.rollno = S.sid
);

(Same idea with IN or a join.) If options include fake functions like SIZEOF(...), eliminate them — GATE SQL sticks to standard constructs.

What to drill

1. Trace a join + WHERE on 4–5 sample rows by hand
2. One GROUP BY + HAVING question
3. One correlated EXISTS vs IN question
4. Know when a LEFT JOIN row survives

Then move to transactions/indexing on the next chapters, and timed practice on the GATE hub.