Self-checkedTier 1 12 min readdbms.sql-nested-views

Nested queries, views and NULL

Subqueries in SQL (IN, NOT IN, EXISTS, NOT EXISTS, scalar comparison, ANY and ALL, correlated and uncorrelated, derived tables and WITH), expressing 'for all' with a double NOT EXISTS, views (definition, use, updatability, security and logical independence), and the three-valued logic of NULL (truth tables, the NOT IN trap, how WHERE, GROUP BY, DISTINCT and aggregates treat NULL); every statement shown was executed on a real SQL engine.

Why it exists

Many questions cannot be answered by one flat query: "students older than the average", "students enrolled in all courses of an instructor", "students with no enrolment". Subqueries express them. NULL turns the logic of the language into three values, which is the source of many GATE traps. Views give a query a name so that it can be reused and so that users see only what they should.

Prerequisites

You need joins, grouping and aggregates (SQL joins and aggregates); the logic behind EXISTS is the tuple calculus (tuple relational calculus).

Precise definitions

A subquery is a SELECT inside another statement, in parentheses. It can appear

in WHERE or HAVING: x IN (subquery), x NOT IN (subquery), EXISTS (subquery), NOT EXISTS (subquery), x θ (scalar subquery), x θ ANY (subquery), x θ ALL (subquery);
in FROM (a derived table; it needs a name in some systems), or in a WITH name AS (subquery) clause (a common table expression);
in the SELECT list (a scalar subquery, which must return one value).

A subquery is correlated if it uses a column of the outer query; it is then (conceptually) re-evaluated for every outer row. An uncorrelated subquery is evaluated once.

Meaning of the comparisons (SQL standard): x θ ANY (S) is true if x θ s for some value s of S; x θ ALL (S) is true if it holds for every value (and is true for an empty S); x IN (S) is x = ANY (S); x NOT IN (S) is x <> ALL (S). EXISTS (S) is true if S returns at least one row, whatever the values are.

A view is a named query: CREATE VIEW v [(columns)] AS SELECT .... It stores no data; using v runs its query on the current tables.

NULL marks a missing or inapplicable value. Any comparison with NULL yields UNKNOWN, the third truth value. A WHERE or HAVING condition selects a row only if it is TRUE.

How it works

IN, EXISTS and scalar subqueries

SELECT sname FROM Student WHERE sid IN (SELECT sid FROM Enrolled WHERE cid = 'C1');

sname
-----
Asha
Bilal
Chen
Esha
Hari
(5 rows)

IN tests membership in the subquery result. The same query with a correlated EXISTS:

SELECT sname FROM Student s WHERE EXISTS (SELECT 1 FROM Enrolled e WHERE e.sid = s.sid AND e.cid = 'C1');

sname
-----
Asha
Bilal
Chen
Esha
Hari
(5 rows)

The two always return the same rows (here Asha, Bilal, Chen, Esha and Hari). "No enrolment at all":

SELECT sname FROM Student s WHERE NOT EXISTS (SELECT 1 FROM Enrolled e WHERE e.sid = s.sid);

sname
-----
Dara
(1 row)

A scalar subquery (one row, one column) can be compared with a column:

SELECT sname, age FROM Student WHERE age > (SELECT AVG(age) FROM Student) ORDER BY age;

sname | age
------+----
Bilal | 22
Gita  | 22
Esha  | 23
(3 rows)

The average is $149/7 \approx 21.29$ , so the students aged $22$ or $23$ qualify.

ANY and ALL

age > ALL (SELECT age FROM Student WHERE dept = 'EE') means older than every EE student. Systems that lack ALL (like SQLite) use an aggregate instead; the two forms agree when the subquery returns at least one non-NULL value and no NULL:

SELECT sname FROM Student WHERE age > (SELECT MAX(age) FROM Student WHERE dept = 'EE');

sname
-----
Esha
(1 row)

Only Esha (23) is older than the oldest EE student (Bilal, 22). x > ANY (S) is x > (SELECT MIN ...). Empty S: > ALL is TRUE, > ANY is FALSE (standard rule).

Correlated subqueries

A correlated subquery refers to the outer row. "Students older than the average age of their own department":

SELECT sname, dept, age FROM Student s WHERE age > (SELECT AVG(age) FROM Student WHERE dept = s.dept) ORDER BY sid;

sname | dept | age
------+------+----
Bilal | EE   | 22
Esha  | CS   | 23
(2 rows)

The animation evaluates it row by row: for each outer student the inner average is recomputed for that student's department:

A correlated subquery, one outer row at a time: start

Computed, not typed

SELECT sname FROM Student s WHERE age > (SELECT AVG(age) FROM Student WHERE dept = s.dept)

Student8 tuples

sid	sname	dept	age
1	Asha	CS	20
2	Bilal	EE	22
3	Chen	CS	21
4	Dara	ME	NULL
5	Esha	CS	23
6	Farid	EE	21
7	Gita	ME	22
8	Hari	CS	20

Step 0

A correlated subquery refers to the outer row (s.dept). Conceptually it is re-evaluated for every row of the outer query: take a student, compute the average age of that student's department, compare.

0 / 8

computed by script: the SQL statements are executed by SQLite, the calculus formulas are evaluated over a finite domain (scripts/db_sql.py, scripts/db_lib.py). python3 scripts/gen-db2-traces.py

"For all" with a double NOT EXISTS

Students enrolled in every course taught by instructor 10: there is no course of instructor 10 for which the student has no enrolment (division, written in SQL):

SELECT sname FROM Student s WHERE NOT EXISTS (SELECT 1 FROM Teaches t WHERE t.iid = 10 AND NOT EXISTS (SELECT 1 FROM Enrolled e WHERE e.sid = s.sid AND e.cid = t.cid)) ORDER BY sid;

sname
-----
Asha
Chen
Esha
(3 rows)

The same students as the calculus query of the earlier page: Asha, Chen and Esha (each is in both C1 and C2).

Derived tables and WITH

SELECT dept, avg_age FROM (SELECT dept, AVG(age) AS avg_age FROM Student GROUP BY dept) WHERE avg_age > 21 ORDER BY dept;

dept | avg_age
-----+--------
EE   | 21.5
ME   | 22
(2 rows)

The subquery in FROM computes the average per department; the outer query filters on it. The same with a named temporary result:

WITH busy AS (SELECT sid FROM Enrolled GROUP BY sid HAVING COUNT(*) >= 3) SELECT sname FROM Student JOIN busy USING (sid) ORDER BY sid;

sname
-----
Chen
Hari
(2 rows)

NULL and three-valued logic

A comparison with NULL is UNKNOWN. The connectives follow these tables (produced by the database engine):

Three-valued logic in SQL: start

Computed by a script, not typed

a, b	a AND b	a OR b	NOT a
TRUE, TRUE
TRUE, FALSE
TRUE, UNKNOWN
FALSE, TRUE
FALSE, FALSE
FALSE, UNKNOWN
UNKNOWN, TRUE
UNKNOWN, FALSE
UNKNOWN, UNKNOWN

Step 0

A comparison with NULL is neither true nor false but UNKNOWN. FALSE AND anything is FALSE and TRUE OR anything is TRUE, otherwise UNKNOWN spreads. A WHERE clause keeps a row only when the condition is TRUE (not when it is UNKNOWN).

0 / 9

computed by script: the SQL statements are executed by SQLite, the calculus formulas are evaluated over a finite domain (scripts/db_sql.py, scripts/db_lib.py). python3 scripts/gen-db2-traces.py

FALSE AND anything is FALSE and TRUE OR anything is TRUE; otherwise UNKNOWN spreads. NOT UNKNOWN is UNKNOWN. A row is selected only if the condition is TRUE, so age <> 21 loses the NULL row, and NOT (age = 21) loses it too.

The NOT IN trap. If the subquery returns a NULL, then x NOT IN (S) is never TRUE: x <> NULL is UNKNOWN, and a conjunction with UNKNOWN is not TRUE.

NOT IN and NULL: start

Computed, not typed

Instructor4 tuples

iid	iname	dept	salary
10	Rao	CS	90000
11	Iyer	CS	80000
12	Khan	EE	70000
13	Lal	ME	NULL

subquery: salary of ME instructors1 tuple

salary
NULL

Step 0

The only instructor of the ME department, Lal, has a NULL salary, so the subquery returns the single value NULL.

0 / 2

computed by script: the SQL statements are executed by SQLite, the calculus formulas are evaluated over a finite domain (scripts/db_sql.py, scripts/db_lib.py). python3 scripts/gen-db2-traces.py

SELECT iname FROM Instructor WHERE salary NOT IN (SELECT salary FROM Instructor WHERE dept = 'ME');

iname
-----
(0 rows)

SELECT iname FROM Instructor i WHERE NOT EXISTS (SELECT 1 FROM Instructor j WHERE j.dept = 'ME' AND j.salary = i.salary) ORDER BY iid;

iname
-----
Rao
Iyer
Khan
Lal
(4 rows)

Where NULL is treated as a value. DISTINCT, GROUP BY, UNION and ORDER BY treat all NULLs as equal / together:

SELECT grade, COUNT(*) AS n FROM Enrolled GROUP BY grade ORDER BY grade;

grade | n
------+--
NULL  | 1
A     | 7
B     | 5
C     | 1
(4 rows)

The single NULL grade forms its own group. COALESCE(x, default) replaces NULL by a default:

SELECT sname, COALESCE(age, -1) AS age_or_minus_one FROM Student WHERE dept = 'ME';

sname | age_or_minus_one
------+-----------------
Dara  | -1
Gita  | 22
(2 rows)

Views

CREATE VIEW CSEnrol AS SELECT s.sname, e.cid, e.grade FROM Student s JOIN Enrolled e ON s.sid = e.sid WHERE s.dept = 'CS';
SELECT sname, cid FROM CSEnrol WHERE grade = 'A' ORDER BY sname, cid;

sname | cid
------+----
Asha  | C1
Chen  | C2
Chen  | C3
Esha  | C1
Hari  | C3
(5 rows)

The view hides the join; users query it like a table. The view reflects later changes of the base tables because it is recomputed when it is used:

Views: start

Computed, not typed

CREATE VIEW CSEnrol AS SELECT s.sname, e.cid, e.grade FROM Student s JOIN Enrolled e ON s.sid = e.sid WHERE s.dept = 'CS'

CSEnrol (the view)10 tuples

sname	cid	grade
Asha	C1	A
Asha	C2	B
Chen	C1	NULL
Chen	C2	A
Chen	C3	A
Esha	C1	A
Esha	C2	C
Hari	C1	B
Hari	C3	A
Hari	C4	B

Step 0

A view is a stored query. Nothing is copied: whenever the view is used, its query is run on the current base tables.

0 / 2

computed by script: the SQL statements are executed by SQLite, the calculus formulas are evaluated over a finite domain (scripts/db_sql.py, scripts/db_lib.py). python3 scripts/gen-db2-traces.py

A view can also hide columns, which is its use for security: users of the view cannot see the other columns.

CREATE VIEW StudentPublic AS SELECT sid, sname, dept FROM Student;
SELECT * FROM StudentPublic WHERE dept = 'ME';

sid | sname | dept
----+-------+-----
4   | Dara  | ME
7   | Gita  | ME
(2 rows)

Updating through a view. The SQL standard allows an update through a view that is defined on one table without aggregation, DISTINCT or GROUP BY (a simple view), and rejects it otherwise (a join, an aggregate, DISTINCT). SQLite refuses every update through a view unless an INSTEAD OF trigger is defined:

-- error
CREATE VIEW CSStudent AS SELECT sid, sname, age FROM Student WHERE dept = 'CS';
UPDATE CSStudent SET age = 30 WHERE sid = 1;

Error: cannot modify CSStudent because it is a view

WITH CHECK OPTION (standard) makes the system reject updates through a view that would make the row disappear from the view. A materialised view stores the result and must be refreshed (not in SQLite). Advantages of views: simplify queries, hide data (security), provide logical data independence (changing the base tables while keeping the view's shape).

Worked examples

EX 1IN or EXISTS

Easy

"Students who have an enrolment" is ... WHERE sid IN (SELECT sid FROM Enrolled) or ... WHERE EXISTS (SELECT 1 FROM Enrolled e WHERE e.sid = s.sid); both return 7 students (all but Dara). With NOT in front, one student (Dara).

EX 2Evaluation of a correlated subquery

Easy

For each outer student the inner average is recomputed for that student's department: CS average $21$ (Asha 20, Chen 21, Esha 23, Hari 20), EE average $21.5$ , ME average $22$ (Dara's NULL ignored). Students above their department average: Esha (23 > 21) and Bilal (22 > 21.5); Gita (22) is not above 22. The animation shows each step.

EX 3The NOT IN trap

Medium

salary NOT IN (SELECT salary FROM Instructor WHERE dept = 'ME') returns no rows, because the subquery returns NULL (Lal has no salary). The NOT EXISTS version returns all four instructors. The two queries look equivalent but are not when NULLs are possible.

EX 4Division in SQL

Medium

Students enrolled in all CS courses ( $C1$ to $C4$ ): NOT EXISTS (SELECT 1 FROM Course c WHERE c.dept = 'CS' AND NOT EXISTS (SELECT 1 FROM Enrolled e WHERE e.sid = s.sid AND e.cid = c.cid)). Nobody takes all four, so the result is empty; Hari has C1, C3 and C4 but not C2. Replace "CS" with the courses of instructor 10 and three students qualify.

EX 5Typical questions

GATE level

(a) "What does SELECT COUNT(*) FROM T WHERE x NOT IN (1, NULL) return?" $0$ . (b) "A condition evaluates to UNKNOWN for a row: the row is:" not selected. (c) "TRUE AND UNKNOWN = ?" UNKNOWN; "FALSE AND UNKNOWN = ?" FALSE; "TRUE OR UNKNOWN" TRUE. (d) "Which views are updatable?" Simple views on one table (standard); not views with joins, aggregates or DISTINCT. (e) "x IN (S) is equivalent to:" x = ANY (S). (f) "EXISTS returns TRUE when:" the subquery returns at least one row.

Cheat box

IN = membership; EXISTS = at least one row; correlated = uses an outer column.
NOT IN with a NULL in the subquery never yields TRUE; use NOT EXISTS.
> ALL (S) = greater than the maximum (TRUE for empty $S$ ); > ANY (S) = greater than the minimum (FALSE for empty $S$ ).
3-valued logic: F AND U = F, T OR U = T, otherwise U; NOT U = U. WHERE keeps TRUE only.
NULLs group together (GROUP BY, DISTINCT) but never compare equal.
View = stored query; simple views updatable (standard); good for security and logical independence.
"For all" = double NOT EXISTS.

Traps and GATE patterns

Connections

Builds on

Joins, grouping and aggregates

Unlocks

Tuple relational calculusimplemented by Safe expressions and domain calculusimplemented by Joins, grouping and aggregatesimplemented by Integrity constraints and triggersuses

Across subjects

No cross-subject links yet.

Within this subject

Joins, grouping and aggregatesuses Tuple relational calculusanalogous-to Integrity constraints and triggersimplemented-by

The subqueries correspond to the quantifiers of the calculus (tuple relational calculus); constraints and triggers can enforce rules that views cannot (constraints and triggers).

PYQs

21 previous year questions from your book. I re-solved each one myself (15 by running or simulating, 6 by reasoning only); the book's key is shown next to my answer. All PYQs

GATE 2025 · Set 2 · MSQ · 2 marksQ1 · page 400 of the book

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?

(a)SELECT S.name FROM Students S WHERE EXISTS (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno)
(b)SELECT S.name FROM Students S WHERE SIZEOF (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno) > 0
(c)SELECT S.name FROM Students S 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

GATE 2025 · Set 1 · NAT · 2 marksQ2 · page 400 of the book

Consider the following database tables of a sports
league.
1.player (pid, pname, age)team(tid, tname, city, cid)
2. coach (cid, cname) members (pid,tid)

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)

GATE 2022 · NAT · 2 marksQ5 · page 401 of the book

Consider the relational database with the following four
schemas and their respective
instances.

Student(sNo, sName, dNo)
 Dept(dNo, dName)
 Course(cNo, cName, dNo)
 Register(sNo, cNo)

Student

sNo
sName
dNo
S01
James
D01
S02
Rocky
D02
S03
Jackson
D02
S04
Jane
D01
S05
Milli
D02

Dept
dNo
dName
D01
CSE
D02
EEE

Course

cNo
cName
dNo
C11
DS
D01
C12
OS
D01
C21
DE
D02
C22
PT
D02
C23
CV
D03

Register

sNo
cNo
S01
C11
S01
C12
S02
C11
S03
C21
S03
C22
S03
C23
S04
C11
S04
C12
S05
C11
S05
C21

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
______.

DATABASE MANAGEMENT  SYSTEM

GATE 2021 · Set 2 · MCQQ6 · page 402 of the book

The relation scheme given below is used to store
information about the employees of a company, where
empId is the key and deptId indicates the department to
which the employee is assigned. Each employee is
assigned to exactly one department.

emp (EMPID, name, gender, salary, deptId)

Consider the following SQL query:

select deptId, count(*)

from emp

where gender = "female" and salary > (select
avg(salary) from emp)

group by deptId;

The above query gives, for each department in the
company, the number of female employees whose
salary is greater than the average salary of:

(a)employees in the company
(b)female employees in the department.
(c)employees in the department.
(d)female employees in the company.

GATE 2020 · NAT · 2 marksQ7 · page 402 of the book

Consider a relational database containing the following
schemas.

Catalogue

Sno
Sname
location
S1
M/s Royal furniture
Delhi
S2
M/s Balaji furniture
Bangalore
S3
M/s Premium furniture
Chennai

pno
Sname
Location
P1
Table
Wood
P2
Chair
Wood
P3
Table
Steel
P4
Almirah
Steel
P5
Almirah
Wood

The primary key of each table is indicated by underlying
the constituent fields.

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 2017 · Set 2 · NAT · 2 marksQ10 · page 403 of the book

Consider the following database table named

top_scorer:

player
country
goals
Klose
Germany
16
Ronaldo
Brazil
15
G Muller
Germany
14
Fontaine
France
13
Pele
Brazil
12
Klinsmann
Germany
11
Kocsis
Hungary
11
Batistuta
Argentina
10
Cubillas
Peru
10
Lato
Poland
10
Lineker
England
10
T Muller
Germany
10
Rahn
Germany
10

Consider the following SQL query:
SELECT ta.player

FROM top_scorer AS ta

WHERE ta.goals > ALL (

    SELECT tb.goals

    FROM top_scorer AS tb

    WHERE tb.country = 'Spain'

)

AND ta.goals > ANY (

    SELECT tc.goals

FROM top_scorer AS tc

  WHERE tc.country = 'Germany');

The number of tuples returned by the above SQL query is
______.

GATE 2017 · Set 1 · NAT · 1 markQ11 · page 403 of the book

Consider a database that has the relation schema EMP
(EmpId, EmpName, and DeptName). An instance of the
schema EMP and a SQL query on it are given below.

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 ______?

EMP
Empld Emp Name
DeptName
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

DATABASE MANAGEMENT  SYSTEM

GATE 2016 · Set 2 · NAT · 2 marksQ12 · page 404 of the book

Consider the following database table named
water_schemes:

water-schemes
scheme_no district_name Capacity
1
1
2
3
1
2
1
Ajmer
Bikaner
Bikaner
Bikaner
Churu
Churu
Dungargarh
20
10
10
20
10
20
10
The number of tuples returned by the following SQL
query is _______.

with total (name, capacity) as

select district_name, sum(capacity)

from water_schemes

group by district_name

with total_avg(capacity) as

select avg(capacity)

from total

select name

from total, total_avg

where total.capacity>total_avg.capacity;

GATE 2015 · Set 3 · MCQ · 1 markQ13 · page 404 of the book

Consider the following relation

Cinema (theater, address, capacity)

Which of the following options will be needed at the
end of the SQL query

SELECT P1. address

FROM CINEMA P1

such that it always finds the addresses of theaters with
maximum capacity?

(a)WHERE P1.capacity>= All (select P2.capacity from Cinema P2)
(b)WHERE P1.capacity>= Any (select P2.capacity from Cinema P2)
(c)WHERE P1.capacity> All (select max(P2.capacity) from Cinema P2)
(d)WHERE P1.capacity> Any (select max(P2.capacity) from Cinema P2)

GATE 2014 · Set 3 · MCQ · 2 marksQ16 · page 404 of the book

Consider the following relational schema:
employee(empid,empName.empDept)
customer(custid,custName,salesRepld,rating) salesRepId
is a foreign key referring to empId of theemployee
relation. Assume that each employee makes a sale to at
least one customer.

What does the following query return?

DATABASE MANAGEMENT  SYSTEM

SELECT empName

FROM employee E

WHERE NOT EXISTS (SELECT custld

 FROM customer C

WHERE C.salesRepId= E.empld

AND(C.rating<>'GOOD');

(a)Names of all the employees with at least one of their customers having a 'GOOD' rating.
(b)Names of all the employees with at most one of their customers having a 'GOOD' rating.
(c)Names of all the employees with none of their customers having a 'GOOD' rating.
(d)Names of all the employees with all their customers having a 'GOOD' rating.

GATE 2014 · Set 2 · MCQQ17 · page 405 of the book

SQL allows duplicates tuples in relations, and
correspondingly defines the multiplicity of tuples in the
result of joins. Which one of the following queries
always gives the same answer as the nested query
shown below:

select * from R

where a in (select S.a from S)

(a)select R.* from R,S where R.a=S.a
(b)select distinct R.* from R,S where R.a=S.a
(c)select R.* from R,(select distinct a from S) as S1 where R.a = S1.a
(d)select R * from R,S where R.a=S.a and is unique R

GATE 2014 · Set 1 · MCQQ18 · page 405 of the book

Given the following schema:

employees(emp-id, first-name, last-name, hire-date,
dept-id, salary)

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 following query:

SQL> select last-name, hire-date

FROM employees

WHERE (dept-id, hire-date) IN ( SELECTdept-id,
MAX(hire-date)

FROM employees JOIN departments USING(dept-id)

WHERE location-id = 1700

GROUP BYdept-id);

What is the outcome?

(a)It executes but does not give the correct result.
(b)It executes and gives the correct result.
(c)It generates an error because of pairwise comparison.
(d)It generates an error because the GROUP BY clause cannot be used with table joins in a subquery.

GATE 2012 · MCQQ19 · page 405 of the book

Consider the above tables A, B and C. How many tuples
does the result of the  following SQL query contains?

Table A
Id
Name
Age
12
Arun
60
15
Shreya
24
99
Rohit
11
Table B
Id
Name
Age
15
Shreya
24
25
Hari
40
98
Rohit
20
98
Rohit
11

Table C
Id
Name
Age
10
2200
02
99
2100
01

SELECT A.id

FROM   A

WHERE  A.age> ALL (SELECT B.age
           FROM   B
          WHERE  B. name = "arun");

(a)4
(b)3
(c)0
(d)1 DATABASE MANAGEMENT SYSTEM

GATE 2010 · MCQ · 1 markQ24 · page 407 of the book

A relational schema for a train reservation database is
given below. Passenger (pid , pname, age) Reservation
(pid , class, tid)

Table : Passenger

pid
pname
age
0
Sachin
65
1
Rahul
66
2
Sourav
67
3
Anil
69

Table : Reservation

pid
class
tid
0
AC
8200
1
AC
8201
2
SC
8201
5
AC
8203
1
SC
8204
3
AC
8202

What pids are returned by the following SQL query for
the above instance of the tables?

SELECT pid

FROM Reservation ,

WHERE class='AC' AND

EXISTS (SELECT *

FROM Passenger

WHERE age > 65 AND

Passenger.pid = Reservation.pid )

(a)1,0
(b)1,2
(c)1,3
(d)1,5

GATE 2009 · MCQ · 2 marksQ25 · page 407 of the book

Consider the following relational schema:

Suppliers(sid:integer,sname:string,
city:string,street:string)
Parts(pid:integer,pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)

Consider the following relational query on the above
database:

SELECT S.sname

FROM Suppliers S

WHERE S.sid NOT IN (SELECT C.sid

          FROM Catalog C
          WHERE C.pid NOT IN (SELECT P.pid
          FROM Parts P
          WHERE P.color<> 'blue'))

Assume that relations corresponding to the above schema
are not empty. Which one of the following is the correct
interpretation of the above query?

(a)Find the names of all suppliers who have supplied a non-blue part.
(b)Find the names of all suppliers who have not supplied a non-blue part.
(c)Find the names of all suppliers who have supplied only non-blue part.
(d)Find the names of all suppliers who have not supplied only blue parts.

GATE 2008 · MCQ · 2 marksQ26 · page 407 of the book

Consider the following relational schema:
Student (school-id, sch-roll-no, sname, saddress)
School (school-id, sch-name, sch-address, sch-
phone)
Enrolment (school-id, sch-rollno, erollno,
examname)
ExamResult (erollno, examname, marks)
What does the following SQL query output?

SELECT sch-name, COUNT(*)
FROM School C, Enrolment E, ExamResult R
WHERE E.school-id = C.school-id
                 AND E.examname = R.examname
                  AND E.erollno = R.erollno
                  AND R.marks = 100
                   AND S.school-id IN
                   (SELECT school-id
                   FROM student
                   GROUP BY school-id
                    HAVING COUNT(*) > 200)
                    GROUP BY school-id;

DATABASE MANAGEMENT  SYSTEM

(a)for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students.
(b)for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students.
(c)for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one exam.
(d)nothing; the query has a syntax error.

GATE 2007 · MCQ · 2 marksQ27 · page 408 of the book

Consider the table employee(empId, name, department,
salary) and the two queries Q1, Q2 below. Assuming
that department 5 has more than one employee, and we
want to find the employees who get higher salary than
anyone in the department 5, which one of the
statements is TRUE for any arbitrary employee table?

Q1:
 Select e.empId
 From employee e
 Where not exists
 (Select * From employee s Where s.department = "5"
and s.salary>= e.salary)

Q2:
 Select e.empId
 From employee e
 Where e.salary> Any
 (Select distinct salary From employee s Where
s.department = "5")

(a)Q1 is the correct query
(b)Q2 is the correct query
(c)Both Q1 and Q2 produce the same answer
(d)Neither Q1 nor Q2 is the correct query

GATE 2006 · MCQQ29 · page 408 of the book

Consider the relation "enrolled(student, course)" in which
(student, course) is the primary key, and the relation
"paid(student, amount)" where student is the primary key.
Assume no null values and no foreign keys or integrity
constraints. Given the following four queries:

Query1: select student from enrolled where

student in (select student from paid)

Query2: select student from paid where

student in (select student from enrolled)

Query3: select E.student from enrolled E, paid P

 where E.student = P.student

Query4:  select student from paid where exists

(select * from enrolled where enrolled.student=
paid.student)

Which one of the following statements is correct?

(a)All queries return identical row sets for any database
(b)Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets.
(c)There exist databases for which Query3 returns strictly fewer rows than Query2
(d)There exist databases for which Query4 will encounter an integrity violation at runtime. DATABASE MANAGEMENT SYSTEM

GATE 2006 · MCQ · 2 marksQ32 · page 410 of the book

Consider a database with three relation instances shown
below. The primary keys for the Drivers and Cars
relation are did and cid respectively and the records are
stored in ascending order of these primary keys as given
in the tables. No indexing is available in the database.

D: Drivers relation

did
dname
rating
age
22
Karthikeyan
7
25
20
Salman
1
33
31
Boris
8
55
32
Amoldt
8
25
58
Schumacher
10
35
64
Sachin
7
35
71
Senna
10
16
74
Sachin
9
35
83
Rahul
3
25
95
Ralph
3
53

R: Reserves relation

did
cid
day
22
101
10/10/06
22
102
10/10/06
22
103
08/10/06
22
104
07/10/06
31
102
10/11/06
31
103
06/11/06
31
104
12/11/06
64
101
05/09/06
64
102
08/09/06
74
103
08/09/06

C: cars celation

cid
cname
colour
101
Renault
blue
102
Renault
red
103
Ferrari
green
104
Jaguar
red

What is the output of the following SQL query?

select D.dname

from Drivers D

where D.didin  (

select R.did

from Cars C, Reserves R

where R.cid = C.cid and C.colour = 'green'

(a)Karthikeyan, Boris
(b)Sachin, Salman
(c)Karthikeyan, Boris, Sachin
(d)Schumacher, Senna

GATE 2005 · MCQ · 2 marksQ33 · page 410 of the book

The relation book(title, price) contains the titles and
prices of different books. Assuming that no two books
have the same price, what does the following SQL query
list?

DATABASE MANAGEMENT  SYSTEM

select title
from book as B
     where (select count(*)

             from book as T
                         where T.price>B.price) < 5

(a)Titles of the four most expensive books
(b)Title of the fifth most inexpensive book
(c)Title of the fifth most expensive book
(d)Titles of the five most expensive books

GATE 2005 · MCQQ35 · page 411 of the book

In an inventory management system implemented at a
trading corporation, there are several tables designed to
hold all the information. Amongst these, the following
two tables hold information on which items are supplied
by which suppliers, and which warehouse keeps which
items along with the stock-level of these items. Supply =
(supplierid, itemcode) Inventory = (itemcode, warehouse,
stocklevel) For a specific information required by the
management, following SQL query has been written

Select distinct STMP.supplierid
From Supply as STMP
Where not unique (Select ITMP.supplierid
                       From Inventory, Supply as ITMP
                     Where STMP.supplierid = ITMP.supplierid
                    And ITMP.itemcode = Inventory.itemcode
                   And Inventory.warehouse = 'Nagpur');

For the warehouse at Nagpur, this query will find all
suppliers who

(a)do not supply any item
(b)supply exactly one item
(c)supply one or more items
(d)supply two or more items

Self-check

Question 1

SELECT * FROM T WHERE x NOT IN (SELECT y FROM U) returns no rows when $U$ contains a NULL in y. Why?

Show answer

For each x, x <> NULL is UNKNOWN, so the conjunction of all comparisons is never TRUE.

Question 2

Evaluate: NOT (NULL = NULL).

Show answer

NULL = NULL is UNKNOWN, so NOT of it is UNKNOWN.

Question 3

Which of IN and EXISTS may be correlated?

Show answer

Both can be, but EXISTS is almost always correlated; an uncorrelated IN subquery is evaluated once.

Question 4

Why can't one insert through a view defined by a join?

Show answer

The system cannot tell which base table rows to create or how to fill the columns that are not in the view.

Revision card

One-screen revision

IN / EXISTS / NOT EXISTS / scalar / ANY / ALL; correlated subqueries; derived tables; WITH.
Division = NOT EXISTS ... NOT EXISTS.
3-valued logic; NOT IN with NULL is empty; NULLs group together.
Views: stored queries; simple views updatable; security and independence.

References

·Silberschatz, Korth, Sudarshan, Database System Concepts (7th edition), Sections 3.8 (nested subqueries), 4.2 (views) and 3.6 (null values)
·Elmasri, Navathe, Fundamentals of Database Systems (7th edition), Sections 7.1.2 to 7.1.7 (more complex queries) and 7.3 (views)

Verification

scripts/db_sql.py executes every verified SQL block on a fresh SQLite database with the sample data, and scripts/enum-checks/db_sqlcheck.py compares the printed outputs again. The truth tables were produced by evaluating SELECT a AND b, a OR b and NOT a in SQLite for all nine combinations of TRUE, FALSE and NULL. The equivalences between IN, = ANY, EXISTS and the scalar form with MAX were checked on random tables with SQLite (the rewriting of > ALL as > (SELECT MAX ...) is valid only when the subquery returns no NULL and at least one row; SQLite does not implement ANY and ALL, so their definitions follow the SQL standard and are not run). SQLite does not allow updating a view without an INSTEAD OF trigger, whereas the SQL standard and most systems allow updating simple views; WITH CHECK OPTION and materialised views do not exist in SQLite; these points are stated from the standard and are not run. Textbook chapter references are from memory. SECOND PASS (2026-11-02, done by the assistant, not a human check): every Self-check question was solved before the printed answer was read and all agreed; the definitions, worked examples, cheat box and traps were read claim by claim and re-derived against the textbook definitions and the sample database. No defect found. Status raised to self-checked; verified needs a textbook or human check.

last verified: never

My notes (saved in this browser)

Joins, grouping and aggregates

Integrity constraints and triggers