http://gate-exam.

in/cs/Syllabus/Computer-Science-Information-Technology/Databases/Relational-
model-relational-algebra-tuple-calculus

Q1: Consider the following relations P(X,Y,Z), Q(X,Y,T) and R(Y,V). (GATE 2019-2)

P
X Y Z
X1 Y1 Z1
X1 Y1 Z2
X2 Y2 Z2
X2 Y4 Z4

Q
X Y T
X2 Y1 2
X1 Y2 5
X1 Y1 6
X3 Y3 1

R
Y V
Y1 V1
Y3 V2
Y2 V3
Y2 V2
How many tuples will be returned by the following relational algebra query?

∏X(σ(P.Y=R.Y∧R.V=V2)(P×R))−∏X(σ(Q.Y=R.Y∧Q.T>2)(Q×R))

Answer: 1
Q2: Consider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key
referencing s.B. Consider the query (GATE 2018-2)

Q: r⋈(σB<5(S))
Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.

Which one of the following queries is NOT equivalent to Q?

A) σB<5(r⋈S)

B) σB<5(r LOJ s)

C) r LOJ(σB<5(s))

D) σB<5(r) LOJ s

Answer: C
Q3: Consider a database that has the relation schems EMP (Empld, EmpName, DeptId),
and DEPT (DeptName, DeptId). Note that the DeptId can be permited to be NULL in the
relation EMP. Consider the following queries on the database expressed in tuple relational calculus.
(GATE 2017-2)

(I){t|∃u∈EMP(t[EmpName]=u[EmpName]∧∀v∈DEPT(t[DeptId]≠v[DeptId]))}
(II){t|∃u∈EMP(t[EmpName]=u[EmpName]∧∃v∈DEPT(t[DeptId]≠v[DeptId]))}
(III){t|∃u∈EMP(t[EmpName]=u[EmpName]∧∃v∈DEPT(t[DeptId]=v[DeptId]))}

Which of the above queries are safe?

A) I & II only

B) I & III only

C) II & III only

D) I, II & III

Answer: D

Q4: Consider a database that has the relation schema CR (StudentName, CourseName).An instance of
the schema CR is as given below. (GATE 2017-2)

CR
StudentName CourseName
SA CA
SA CB
SA CC
SB CB
SB CC
SC CA
SC CB
SC CC
SD CA
SD CB
SD CC
SD CD
SE CD
SE CA
SE CB
SF CA
SF CB
SF CC

The following query is made on the database.

T1←πCourseName(σStudentName=′SA′(CR))T2←CR÷T1
The number of rows in T2 is__________

Answer: 4
Q5: SELECT operation in SQL is equivalent to (GATE 2015-1)

A) the selection operation in relational algebra

B) the selection operation in relational algebra, except that SELECT in SQL retains duplicates
C) the projection operation in relational algebra
D) the projection operation in relational algebra, except that SELECT in SQL retains duplicates

Answer: D

Q6: Consider two relations R1(A,B) with the tuples(1, 5), (3, 7) and R2(A, C) = (1, 7), (4, 9). Assume
that R(A,B,C) is the full natural outer join of R1 and R2. Consider the following tuples of the form
(A, B, C): a =(1, 5, null), b =(1, null, 7), c = (3, null, 9), d = (4, 7, null), e = (1, 5, 7), f = (3,
7, null), g = (4, null, 9). Which one of the following statements is correct? (GATE 2015-2)

A) R contains a, b, e, f, g but not c, d.

B) R contains all of a, b, c, d, e, f, g.

C) R contains e, f, g but not a, b

D) R contains e but not f,g

Answer: C

Q7: Consider a join (relation algebra) between relations r(R) and s(S) using the nested loop method.
There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for
intermediate results. Assuming size (r(R))<size(s(S)), the join will have fewer number of disk block
accesses if (GATE 2014-2)

A) relation r(R) is in the outer loop.

B) relation s(S) is in the outer loop
C) join selection factor between r(R) and s(S) is more than 0.5
D) join selection factor between r(R) and s(S) is less than 0.5.

Answer: A

Q8: What is the optimized version of the relation algebra expression

πA1(πA2(σF1(σF2(r)))), where A1, A2 are sets of attributes in r with A1 ⊂ A2 and F1, F2 are Boolean
expressions based on the attributes in r? (GATE 2014-1)

A) πA1(σ(F1ΛF2)(r))

B) πA1(σ(F1∨F2)(r))

C) πA2(σ(F1∧F2)(r))

D) πA2(σ(F1∨F2)(r))

Answer: A
Q9: Consider the relational schema given below, where eId of the relation dependent is a foreign key
referring to empId of the relation employee. Assume that every employee has at least one
associated dependent in the dependent relation. (GATE 2014-2)

employee (empId, empName, empAge)

dependent(depId, eId, depName, depAge)
Consider the following relational algebra query:

πempId(employee)−πempId(employee⋈(empId=eID)∧(empAge≤depAge)Dependent)

The above query evaluates to the set of empIds of employees whose age is greater than that of

A) some dependent.
B) all dependents
C) some of his/her dependents
D) all of his/her dependents.

Answer: D

Q10: Consider the following relational schema. (GATE 2013-2)

Students(rollno: integer, sname: string)
Courses(courseno: integer, cname: string)
Registration(rollno: integer, courseno: integer, percent: real)
Which of the following queries are equivalent to this query in English?
“Find the distinct names of all students who score more than 90% in the course numbered 107”

(I) SELECT DISTINCT S.sname

FROM Students as S, Registration as R
WHERE R.rollno=S.rollno AND R.courseno=107 AND R.percent >90

(II) ∏sname(σcourseno=107∧percent>90(Registration⋈Students)

(III) {T | ∃S∈∈ Students, ∃ R∈∈ Registration ( S.rollno=R.rollno ∧ R.courseno=107 ∧

R.percent>90 ∧T.sname=S.sname)}

(IV) {<SN> | ∃SR ∃RP ( <SR, SN>∈∈ Students ∧ <SR, 107, RP>∈∈ Registration ∧ RP>90)}

A) I, II, III and IV B) I, II and III only C) I, II and IV only D) II, III and IV only

Answer: A

Q11: Suppose R1(A, B) and R2(C, D) are two relation schemas. Let r1 and r2 be the corresponding
relation instances. B is a foreign key that refers to C in R 2. If data in r1 and r2 satisfy referential
integrity constraints, which of the following is ALWAYS TRUE? (GATE 2012-2)

A) ΠB(r1)−ΠC(r2)=∅

B) ΠC(r2)−ΠB(r1)=∅

C) ΠB(r1)=ΠC(r2)

D) ΠB(r1)−ΠC(r2)≠∅

Answer: A
Q12: Consider a relational table with a single record for each registered student with the following
attributes. (GATE 2011-1)

1. Registration_Num: Unique registration number of each registered student

2. UID: Unique identity number, unique at the national level for each citizen

3. BankAccount_Num: Unique account number at the bank. A student can have multiple accounts or
joint accounts. This attribute stores the primary account number.

4. Name: Name of the student

5. Hostel_Room: Room number of the hostel

Which of the following options is INCORRECT?

A) BankAccount_Num is a candidate key

B) Registration_Num can be a primary key

C) UID is a candidate key if all students are from the same country

D) If S is a superkey such that S∩UID is NULL then S∪UID is also a superkey

Answer: A

Q13: Consider a relational table r with sufficient number of records, having attributes A1, A2,……
An and let 1 ≤ p ≤ n. Two queries Q1 and Q2 are given below. (GATE 2011-2)

Q1: π A1......Ap(σAp=c(r)) where c is a constant

Q2: π A1......Ap(σ c1≤Ap≤c2(r)) where c1 and c2 are constants
The database can be configured to do ordered indexing on Ap or hashing on Ap. Which of the
following statements is TRUE?

A) Ordered indexing will always outperform hashing for both queries

B) Hashing will always outperform ordered indexing for both queries

C) Hashing will outperform ordered indexing on Q1, but not on Q2

D) Hashing will outperform ordered indexing on Q2, but not on Q1

Answer: C

Q14: Let R and S be relational schemes such that R={a,b,c} and S={c}. Now consider the following
queries on the database: (GATE 2009-2)

I. πR−S(r)−πR−S(πR−S(r)×S−πR−S,S(r))
II. {t|t∈πR−S(r)∧∀u∈s(∃v∈r(u=v[s]∧t=v[R−S]))}
III. {t|t∈πR−S(r)∧∀v∈r(∃u∈s(u=v[s]∧t=v[R−S]))}
IV. Select R.a, R.b from R, S where R.c = S.c
A) I & II
B) I & III
C) II & IV
D) III & IV

Answer: C

Q15: Consider the following relational schema: (GATE 2009-2)

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 (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 blue parts.

D) Find the names of all suppliers who have not supplied only blue parts.

Answer: A

to ∀t∈r(P(t))∀t∈rPt? (GATE 2008-1)

Q16: Which of the following tuple relational calculus expression(s) is/are equivalent

I. ¬∃t∈r(P(t))
II.∃t∉r(P(t))

IV. ∃t∉r(¬P(t))
III. ¬∃t∈r(¬P(t))

A) I only B) II only C) III only D) III and IV only

Answer: D

Q17: Let R and S be two relations with the following schema (GATE 2008-2)
R (P,Q.R1,R2,R3)
S (P,Q,S1,S2)
Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?

I. ∏p (R ⋈ S)
II. ∏p (R) ⋈∏p (S)
III. ∏p (∏p, Q (R) ∩ ∏ p, Q (S))
IV. ∏p (∏p, Q (R) - (∏p, Q (R) - ∏ p, Q (S))
A) Only I and II B) Only I and III C) Only I, II and III D) Only I , III and IV

Answer: D

Q18: Information about a collection of students is given by the relation studInfo(studId, name, sex).
The relation enroll(studId, courseId) gives which student has enrolled for (or taken) what course(s).
Assume that every course is taken by at least one male and at least one female student. What does the
following relational algebra expression represent? (GATE 2007-2)

∏courseId((∏studId(σsex="female"(studInfo))×∏courseId(enroll))−enroll)

A) Courses in which all the female students are enrolled

B) Courses in which a proper subset of female students are enrolled
C) Courses in which only male students are enrolled.
D) None of the above

Answer: B

Q19: Consider the relation employee(name, sex, supervisorName) with name as the
key. supervisorName gives the name of the supervisor of the employee under consideration. What
does the following Tuple Relational Calculus query produce? (GATE 2007-2)

{e.name | employee(e) ∧ (∀x) [¬employee(x) ∨ x.supervisorName ≠ e.name ∨ x.sex = "male" ] }

A) Names of employees with a male supervisor

B) Names of employees with no immediate male subordinates

C) Names of employees with no immediate female subordinates

D) Names of employees with a female supervisor

Answer: B

https://www.geeksforgeeks.org/database-management-system-set-1/
Q20: Given the relations
employee (name, salary, deptno) and
department (deptno, deptname, address)
Which of the following queries cannot be expressed using the basic relational algebra
operations (U, -, x, π, σ, p)? (GATE CS 2000)
(a) Department address of every employee
(b) Employees whose name is the same as their department name
(c) The sum of all employees’ salaries
(d) All employees of a given department

Answer: (c)

Q21: Given relations r(w, x) and s(y, z), the result of

select distinct w, x
from r, s
is guaranteed to be same as r, provided (GATE CS 2000)
(a) r has no duplicates and s is non-empty
(b) r and s have no duplicates
(c) s has no duplicates and r is non-empty
(d) r and s have the same number of tuples
Answer: (a)

Q22: In SQL, relations can contain null values, and comparisons with null values are treated
as unknown. Suppose all comparisons with a null value are treated as false. Which of the
following pairs is not equivalent? (GATE CS 2000)
(a) x = 5, not (not (x = 5)
(b) x = 5, x > 4 and x < 6, where x is an integer
(c) x < 5, not(x = 5)
(d) None of the above
Answer (c)

https://www.geeksforgeeks.org/database-management-system-set-2/

Q23: Which of the following statements are TRUE about an SQL query? (GATE 2012)
P: An SQL query can contain a HAVING clause even if it does not a GROUP BY clause
Q: An SQL query can contain a HAVING clause only if it has a GROUP BY clause
R: All attributes used in the GROUP BY clause must appear in the SELECT clause
S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
(A) P and R
(B) P and S
(C) Q and R
(D) Q and S
Answer (C)
https://www.geeksforgeeks.org/database-management-system-set-3/
Q24: Consider the following relations A, B, C. How many tuples does the result of the
following relational algebra expression contain? Assume that the schema of A U B is the same
as that of A. (GATE 2012)

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
99 Rohit 11

Table C
Id Phone Area
-----------------
10 2200 02
99 2100 01

(A) 7
(B) 4
(C) 5
(D) 9
Answer (A)
Q25: Consider the above tables A, B and C. How many tuples does the result of the following
SQL query contains? (GATE 2012)
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
Answer (B)
https://www.geeksforgeeks.org/database-management-systems-set-4/
Q26: Database table by name Loan_Records is given below. (GATE 2011)
Borrower Bank_Manager Loan_Amount
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 );
(A) 3
(B) 9
(C) 5
(D) 6
Answer (C)

Q27: Consider a database table T containing two columns X and Y each of type integer. After
the creation of the table, one record (X=1, Y=1) is inserted in the table.
Let MX and My denote the respective maximum values of X and Y among all records in the
table at any point in time. Using MX and MY, new records are inserted in the table 128 times
with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after
the insertion, values of MX and MY change. What will be the output of the following SQL
query after the steps mentioned above are carried out? (GATE 2011)
SELECT Y FROM T WHERE X=7;
(A) 127
(B) 255
(C) 129
(D) 257
Answer (A)
https://www.geeksforgeeks.org/database-management-systems-set-5/

Q28: A relational schema for a train reservation database is given below. (GATE 2010)
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?
SLECT 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
(S) 1, 5
Answer (C)

https://www.geeksforgeeks.org/database-management-systems-set-8/
Q29: The following table has two attributes A and C where A is the primary key and C is the
foreign key referencing A with on-delete cascade. (GATE 2005)
A C
-----
2 4
3 4
4 3
5 2
7 2
9 5
6 4
The set of all tuples that must be additionally deleted to preserve referential integrity when
the tuple (2,4) is deleted is:
(a) (3,4) and (6,4)
(b) (5,2) and (7,2)
(c) (5,2), (7,2) and (9,5)
(d) (3,4), (4,3) and (6,4)

Answer (C)
Q30: 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? (GATE 2005)
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
Answer (d)
https://www.geeksforgeeks.org/isro-cs-2020/

Q31: Properties of ‘DELETE’ and ‘TRUNCATE’ commands indicate that

After the execution of ‘TRUNCATE’ operation, COMMIT and ROLLBACK statements

A cannot be performed to retrieve the lost data, while ‘DELETE’ allow it.

After the execution of ‘DELETE’ and ‘TRUNCATE’ operation retrieval is easily possible

B for the lost data.

After the execution of ‘DELETE’ operation, COMMIT and ROLLBACK statements can

C be performed to retrieve the lost data, while TRUNCATE do not allow it.
 After the execution of ‘DELETE’ and ‘TRUNCATE’ operation no retrieval is possible for

D the lost data.

Answer: C
https://www.geeksforgeeks.org/isro-cs-2018/

Q32: Consider the following table in a relational database

Last Name Rank Room Shift

Smith Manager 234 Morning
Jones Custodian 33 Afternoon
Smith Custodian 33 Evening
Doe Clerical 222 Morning
According to the data shown in the table, which of the following could be a candidate key of the table?
{Last Name}
A
{Room}
B
{Shift}
C
{Room, Shift}
D
Answer: D
https://www.geeksforgeeks.org/isro-cs-2018/

Q33: Consider the set of relations given below and the SQL query that follows

Students : (Roll number, Name, Date of birth)

Courses: (Course number, Course name, instructor)
Grades: (Roll number, Course number, Grade)
SELECT DISTINCT Name
FROM Students, Courses, Grades
WHERE Students.Roll_number = Grades.Roll_number
AND Courses.Instructor =Sriram
AND Courses.Course_number = Grades.Course_number
AND Grades.Grade = A
Which of the following sets is computed by the above query?
Names of Students who have got an A grade in all courses taught by Sriram
A
Names of Students who have got an A grade in all courses
B
Names of Students who have got an A grade in at least one of the courses taught by

C Sriram

None of the above

D
Answer: C
https://www.geeksforgeeks.org/isro-cs-2018/

Q34: Given relations R(w,x) and S(y,z), the result of

SELECT DISTINCT w, x
FROM R, S
Is guaranteed to be same as R, if
A R has no duplicates and S is non-empty

R and S have no duplicates

B
S has no duplicates and R is non-empty
C
R and S have the same number of tuples
D
Answer: A

https://www.geeksforgeeks.org/isro-cs-2017-may/

Q35:

What is the output of the following SQL query?

select count(*) from ((select Employee, Department from Overtime_allowance) as S
natural join (select Department, OT_allowance from Overtime_allowance) as T);
16
A
4
B
8
C
None of the above
D
Answer: B

https://www.geeksforgeeks.org/isro-cs-2017/

Q36: Consider the following schema :

Sailors (sid, sname, rating, age)

Boats (bid, bname, colour)
Reserves (sid, bid, day)
Two boats can have the same name but the colour differentiates them. The two relations

Ⲡ sname (Tempsids ⋈ Sailors)

ρ (Tempsids, (Ⲡ sid, bid Reserves)/(Ⲡ bid ( σ bname ='Ganga' Boats))),

If / is division operation, the above set of relations represents the query

Names of sailors who have reserved all boats called Ganga
A
Names of sailors who have not reserved any Gangaboat
B
Names of sailors who have reserved at least one Gangaboat
C
Names of sailors who have reserved at most one Ganga boat
D
Answer: A

Q37: