GATE DA / Database Management and Warehousing

SQL

Data definition (CREATE, constraints, ALTER) and data manipulation (INSERT, UPDATE, DELETE), the SELECT statement and its logical evaluation order, filtering, joins (inner, self, outer), aggregation with GROUP BY and HAVING, NULLs and three-valued logic, nested and correlated subqueries (IN, EXISTS, ANY, ALL), set operations, views, and window functions — with hand-evaluation practice.

Section 511 practice questions

SQL (Structured Query Language) is the standard language of relational databases. It is mostly declarative and works on bags (multisets): duplicates are kept unless you ask for DISTINCT.

Data definition (DDL)

SQL

CREATE TABLE Dept (
    dname   VARCHAR(20) PRIMARY KEY,
    budget  DECIMAL(10, 2) CHECK (budget >= 0)
);

CREATE TABLE Emp (
    eid    INT PRIMARY KEY,
    name   VARCHAR(50) NOT NULL,
    dept   VARCHAR(20) REFERENCES Dept(dname) ON DELETE SET NULL,
    sal    INT,
    mgr    INT REFERENCES Emp(eid),
    email  VARCHAR(100) UNIQUE
);

ALTER TABLE Emp ADD COLUMN joined DATE;
DROP TABLE Emp;          -- removes the table; TRUNCATE removes all rows

Constraints (PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY ... ON DELETE/UPDATE CASCADE | SET NULL | RESTRICT) are covered in integrity constraints.

Data manipulation (DML)

SQL

INSERT INTO Emp (eid, name, dept, sal) VALUES (6, 'Zara', 'CS', 75);
UPDATE Emp SET sal = sal * 1.10 WHERE dept = 'CS';
DELETE FROM Emp WHERE sal IS NULL;

The SELECT statement

SQL

SELECT   [DISTINCT] dept, COUNT(*) AS n, AVG(sal) AS avg_sal
FROM     Emp
WHERE    sal IS NOT NULL
GROUP BY dept
HAVING   COUNT(*) >= 2
ORDER BY avg_sal DESC
LIMIT    10;

Logical step	Clause
1	`FROM` / `JOIN` — build the combined rows
2	`WHERE` — keep rows whose condition is TRUE
3	`GROUP BY` — form groups
4	`HAVING` — keep groups whose condition is TRUE
5	`SELECT` — compute output expressions; `DISTINCT`
6	`ORDER BY` — sort
7	`LIMIT` / `OFFSET`

Rule: with GROUP BY, every non-aggregated column in SELECT must appear in GROUP BY.

Useful predicates: BETWEEN a AND b (inclusive), IN (...), LIKE 'A%' (% any string, _ one character), IS NULL.

Joins

SQL

-- inner join: matching pairs only
SELECT e.name, d.budget FROM Emp e JOIN Dept d ON e.dept = d.dname;

-- left outer join: every employee, NULL budget if no matching department
SELECT e.name, d.budget FROM Emp e LEFT JOIN Dept d ON e.dept = d.dname;

-- self join: employee with manager
SELECT e.name, m.name AS manager FROM Emp e JOIN Emp m ON e.mgr = m.eid;

Join	Keeps
`INNER JOIN`	matching pairs
`LEFT [OUTER] JOIN`	all left rows (+ NULLs)
`RIGHT [OUTER] JOIN`	all right rows
`FULL [OUTER] JOIN`	all rows of both
`CROSS JOIN`	Cartesian product
`NATURAL JOIN`	equi-join on all same-named columns

Aggregation

COUNT, SUM, AVG, MIN, MAX ignore NULLs (except COUNT(*)). On an empty input, COUNT returns 0 and the others return NULL. COUNT(DISTINCT dept) counts distinct non-NULL values.

NULLs and three-valued logic

Comparisons involving NULL yield UNKNOWN. WHERE and HAVING keep only TRUE.

AND	T	U	F
T	T	U	F
U	U	U	F
F	F	F	F

OR	T	U	F
T	T	T	T
U	T	U	U
F	T	U	F

NOT U = U. Consequences: x = NULL is never TRUE (use IS NULL); x NOT IN (1, NULL) is never TRUE; p OR NOT p can be UNKNOWN.

Subqueries

SQL

-- uncorrelated: inner query runs once
SELECT name FROM Emp WHERE sal > (SELECT AVG(sal) FROM Emp);

-- correlated: inner query depends on the outer row (above their department's average)
SELECT e.name FROM Emp e
WHERE e.sal > (SELECT AVG(x.sal) FROM Emp x WHERE x.dept = e.dept);

-- EXISTS / NOT EXISTS: semi-join / anti-join
SELECT d.dname FROM Dept d
WHERE NOT EXISTS (SELECT * FROM Emp e WHERE e.dept = d.dname);

Operator	TRUE when
`x IN (subq)`	$x$ equals some value
`x > ANY (subq)` / `SOME`	$x$ exceeds at least one value (> the minimum)
`x > ALL (subq)`	$x$ exceeds every value (> the maximum); TRUE if the subquery is empty
`EXISTS (subq)`	the subquery returns at least one row

Division in SQL ("students enrolled in all courses") uses a double negation:

SQL

SELECT s.sid FROM Student s
WHERE NOT EXISTS (
    SELECT c.cid FROM Course c
    WHERE NOT EXISTS (
        SELECT * FROM Enrolled e WHERE e.sid = s.sid AND e.cid = c.cid));

Set operations

UNION, INTERSECT, EXCEPT (set semantics, duplicates removed) and UNION ALL etc. (bag semantics). Operands must have compatible columns.

Views and window functions

A view is a stored query used like a table: CREATE VIEW HighPaid AS SELECT * FROM Emp WHERE sal > 75;.

Window functions compute values over related rows without collapsing them:

SQL

SELECT name, dept, sal,
       RANK()   OVER (PARTITION BY dept ORDER BY sal DESC) AS rnk,
       AVG(sal) OVER (PARTITION BY dept)                    AS dept_avg
FROM Emp;

RANK leaves gaps after ties (1, 1, 3); DENSE_RANK does not (1, 1, 2); ROW_NUMBER is always unique.

Quick revision

Practice questions

GATE-style questions: 3 one-mark and 8 two-mark. Wrong MCQs cost ⅓ of their marks in the real exam; MSQ and NAT have no negative marking. Attempt each before opening the solution.

Q1NAT1 mark

Table Emp(eid, name, dept, sal, mgr):

eid	name	dept	sal	mgr
1	Asha	CS	90	NULL
2	Ravi	CS	70	1
3	Meena	EE	60	1
4	John	EE	80	3
5	Lee	ME	NULL	3

The result of SELECT COUNT(sal) FROM Emp; is ______.

Q2NAT1 mark

Table Emp(eid, name, dept, sal, mgr):

eid	name	dept	sal	mgr
1	Asha	CS	90	NULL
2	Ravi	CS	70	1
3	Meena	EE	60	1
4	John	EE	80	3
5	Lee	ME	NULL	3

For this Emp table, the result of SELECT AVG(sal) FROM Emp; is ______.

Q3NAT2 marks

Table Emp(eid, name, dept, sal, mgr):

eid	name	dept	sal	mgr
1	Asha	CS	90	NULL
2	Ravi	CS	70	1
3	Meena	EE	60	1
4	John	EE	80	3
5	Lee	ME	NULL	3

For this Emp table, how many rows does this query return?

SQL

SELECT dept, AVG(sal)
FROM Emp
GROUP BY dept
HAVING AVG(sal) > 70;

Q4MCQ2 marks

Table Emp(eid, name, dept, sal, mgr):

eid	name	dept	sal	mgr
1	Asha	CS	90	NULL
2	Ravi	CS	70	1
3	Meena	EE	60	1
4	John	EE	80	3
5	Lee	ME	NULL	3

For this Emp table, what does this query return?

SQL

SELECT name FROM Emp
WHERE sal > ALL (SELECT sal FROM Emp WHERE dept = 'EE');

Q5NAT2 marks

Table Emp(eid, name, dept, sal, mgr):

eid	name	dept	sal	mgr
1	Asha	CS	90	NULL
2	Ravi	CS	70	1
3	Meena	EE	60	1
4	John	EE	80	3
5	Lee	ME	NULL	3

For this Emp table, the result of SELECT COUNT(*) FROM Emp WHERE sal > 65 OR sal <= 65; is ______.

Q6MCQ2 marks

Table Emp(eid, name, dept, sal, mgr):

eid	name	dept	sal	mgr
1	Asha	CS	90	NULL
2	Ravi	CS	70	1
3	Meena	EE	60	1
4	John	EE	80	3
5	Lee	ME	NULL	3

For this Emp table, what does this query return?

SQL

SELECT e.name
FROM Emp e JOIN Emp m ON e.mgr = m.eid
WHERE e.sal > m.sal;

Q7NAT2 marks

Table Emp(eid, name, dept, sal, mgr):

eid	name	dept	sal	mgr
1	Asha	CS	90	NULL
2	Ravi	CS	70	1
3	Meena	EE	60	1
4	John	EE	80	3
5	Lee	ME	NULL	3

For this Emp table, the result of this query is ______.

SQL

SELECT COUNT(*) FROM Emp
WHERE dept NOT IN (SELECT dept FROM Emp WHERE sal < 65);

Q8MSQ2 marks

Which statements about SQL are correct?

One or more options are correct.

Q9NAT2 marks

Table Emp(eid, name, dept, sal, mgr):

eid	name	dept	sal	mgr
1	Asha	CS	90	NULL
2	Ravi	CS	70	1
3	Meena	EE	60	1
4	John	EE	80	3
5	Lee	ME	NULL	3

A table Dept(dname) contains the four rows CS, EE, ME and HR. Using this Emp table, the result of

SQL

SELECT COUNT(*) FROM Dept d LEFT JOIN Emp e ON d.dname = e.dept;

is ______.

Q10MCQ1 mark

In what logical order are the clauses of a SELECT query evaluated?

Q11NAT2 marks

Table Emp(eid, name, dept, sal, mgr):

eid	name	dept	sal	mgr
1	Asha	CS	90	NULL
2	Ravi	CS	70	1
3	Meena	EE	60	1
4	John	EE	80	3
5	Lee	ME	NULL	3

For this Emp table, the result of

SQL

SELECT MAX(sal) FROM Emp WHERE sal < (SELECT MAX(sal) FROM Emp);

is ______.