Previous Year Questions: SQL | Database Management System (DBMS) - Computer Science Engineering (CSE) PDF Download

Q1: Consider the following table named Student in a relational database. The primary key of this table is rollNum.  (2023)

The SQL query below is executed on this database.
SELECT *
FROM Student
WHERE gender = 'F' AND
marks > 65;
The number of rows returned by the query is ______
(a) 0
(b) 1
(c) 2
(d) 3
Ans: (c)
Sol: The output of the given SQL query is simply to return the female students whose marks is > 65 . we can also solve by first we retrieve all the female students then we can select marks > 65 .
output is as follows:

So the output of the given query is 2.

Q2: Consider the relational database with the following four schemas and their respective instances.  (2022)

The number of rows returned by the above SQL query is ___
(a) 1
(b) 2
(c) 4
(d) 8
Ans: (b)
Sol:  
Step1:
In the above case, the first inner query return is independent and return <{C11, C12>. The second inner query is dependent on student relations.
Step 2:
The condition is sNo = S.sNo. So, for sNo = S01, second inner query returns <C11, C12>. And the except operation between two inner queries gives an empty result.
Except operation: R1 EXCEPT R2 = R1 – (R1 ∩ R2)
Now, NOT EXIST returns TRUE, and the outer query gives <S01, James, D01> as output.
Step 3:
When the second inner query is checked for sNo = S02. It return <C11>. And the except operation between two inner queries gives a non-empty result. AND NOT EXISTS gives FALSE result. And outer query doesn’t give any result.
Similarly for sNo = S04, the outer query gives output <S04, jane, D01>.  Hence 2 rows were found.
Hence the correct answer is 2.

Q3: 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.  (2021 SET 2)
emp (empId, name, gender, salary, deptId)
Consider the following SQL query:

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 department
(b) employees in the company
(c) female employees in the department
(d) female employees in the company
Ans: (b)
Sol: It’s a nested query but not Co-related query.
Evaluate the innermost query first.
select avg(salary)
from emp
It is given that emp represent employees of a company.
So, Option B is the correct answer.

Q4:Consider a relational database containing the following schemas.  (2020) 

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

The number of rows returned by the above SQL query is
(a) 4
(b) 5
(c) 0
(d) 2
Ans: (a)
Sol: The given query is a nested subquery but not co-related subquery (inner query is independent of the outer and so can be executed independently)
SELECT AVG (cost) FROM Catalogue WHERE pno= 'P4' GROUP BY pno

First, we will select the tuples with pno = ‘P4’ and then group by pno (so just one group) and then find the average cost.

So average cost  200 + 250 / 2 = 225
∴ the inner query will return 225
Now the given SQL query would become
SELECT s.sno,s.sname FROM Supplier s , Catalogue c WHERE s.sno=c.sno AND cost> 225
So here we need to do cross product of supplier table s and Catalogue table c and from the cross product we will select those rows where s . s n o = c . s n o AND
cost > 225
Since it is given that cost > 225 so we do not need to consider rows from the Catalogue table having c o s t ≤ 225 while doing cross product. Hence from the Catalogue table only the row numbers 5 , 6 , 8 , 9 need to be taken while doing the cross product.
After doing cross product we’ll get,

Now after doing cross product only 4 tuples will be selected from the table due to the condition
s. sno = c. sno

∴ Option A. 4 is the correct answer

Q5: A relational database contains two tables Student and Performance as shown below:  (2019)

The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and Subject_code together from the primary key. Consider the SQL query given below:
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 _________ .
(a) 3
(b) 6
(c) 5
(d) 4
Ans: (c) 
Sol: Group by Student_name ⟹ number of distinct values of Student_name
in the instance of the relation all rows have distinct name then it should results 5 tuples !

Q6: Consider the following two tables and four queries in SQL.  (2018) 

Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?
(a)Query 1
(b) Query 2
(c) Query 3
(d) Query 4
Ans: (d)
Sol: These images are self-explanatory. The answer is D.

Q7: Consider the following database table named top_scorer.  (2017 SET 2)

Consider the following SQL query:

The number of tuples returned by the above SQL query is ___________.
(a) 5
(b)6
(c) 7
(d) 8
Ans: (c) 
Sol: ALL (EMPTY SET) always returns TRUE. So first where condition is always satisfied.
Second where condition will return all those rows who have more goals than ANY German player. Since, minimum goals by a German is 10 , all the rows which are greater than 10 Goals will be returned.
I.e. first 7 rows in the table.
Hence, answer: 7.

Q8: 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.  (2017 SET 1) 

The output of executing the SQL query is _____.
(a) 1.3
(b) 6.5
(c) 2.6
(d) 5
Ans: (c)
Sol: The inner query will return

Now AVG(EC.Num) will find the average of Num values in the above-returned query, which is ( 4 + 3 + 3 + 2 + 1 ) ÷ 5 = 2.6
 So according to me, the answer should be 2.6

Q9: Consider the following database table named water_schemes :  (2016 SET 2)

The number of tuples returned by the following SQL query is __________.

(a) 1
(b) 2
(c) 3
(d) 4
Ans: (b)
Sol:1st  query will return the following:
Table Name : Total (name, capacity)

2nd Query will return, Total_avg (capacity)
Since sum of capacity = 100 / 4 = 25
3rd query will be final and it's tuples will be considered as output, where name of district and its total capacity should be more than or equal to 25

Hence, 2 tuples returned.

Q10: Consider the following relation  (2015 SET 3)
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
Ans: (a)
Sol:  A is the answer
B - Returns the addresses of all theaters.
C - Returns null set. max() returns a single value and there won't be any value > max.
D - Returns null set. Same reason as C. All and ANY works the same here as max returns a single value.

Q11: Consider the following relations:  (2015 SET 1)

Consider the following SQL query.

The number of rows that will be returned by the SQL query is _____________.
(a) 2
(b) 3
(c) 4
(d) 5
Ans: (a)
Sol: It means all name that are same should be kept in one row.
There are 3 names. But in that there is a duplicate with Raj being repeated ⟹ Raj produces one row and Rohit produces one row ⟹ Total
2 rows.
For better understanding, I'll just analyze the whole query
1^st statement which is executed from the query is From Clause : From Student S, Performance P
⟹ cross product of those two tables will be

2^ndstatement which is executed from the query is Where Clause :Where S.Roll_no = P.Roll_no
⟹delete those rows which does not satisfy the WHERE condition. Then the result will be

3^rd statement which is executed from the query is Group by Clause : Group by S.Student_Name
⟹Merge those rows which are having same name, then result will be

Note that, this can't be used as final result as it violates 1NF (multiple values in each tuple for S .Roll_no, P. Roll_no, P .Course and (P.marks)
4^thstatement which is executed from the query is Select Clause : Select S. Student_Name, SUM(P.marks)
⟹ Delete un-necessary columns and calculate the aggregate functions, then result will be

Q12: SELECT operation in SQL is equivalent to  (2015 SET 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
Ans: (d) 
Sol: Option D is correct because SELECT operation in SQL is equivalent to The projection operation in relational algebra, except that SELECT in SQL retains duplicates but projection gives only distinct.

Q13: Consider the following relational schema:  (2014 SET 3)
Employee (empId, empName, empDept )
Customer (custId,custName, salesRepId, rating)
SalesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

(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
Ans: (d)
Sol:

So, an employee whose ALL customers gives him GOOD rating is chosen;
All such employees are chosen.
Answer = option D

Q14: SQL allows duplicate 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:  (2014 SET 2)
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
Ans: (c)
Sol:  Consider the following instances of R and  S

Now output of given query
select * from R where a in (select S.a from S)

For Option,
A) since multiplicity of tuples is disturbed
select R.* from R, S where R.a=S.a
∴ Output will be 

B)
select distinct R.* from R,S where R.a=S.a
∵ only Distinct R will be chosen in the end so, output will be

C) ANSWER
select R.* from R,(select distinct a from S) as S1 where R.a=S1.a
Multiplicity of tuples is maintained. ∵ Multiplicity of duplicate tuples will be distributed when there is a match between R.a and S.a and for that match S.a’s value is repeated.
So, Output will be

Q15: Given the following schema:  (2014 SET 1)
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:

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 sub-query
Ans: (b)
Sol: 

This inner query will give the max hire date of each department whose location_id = 1700
and outer query will give the last name and hire-date of all those employees who joined on max hire date.
answer should come to (B) no errors.
And we can use group by and where together, who said we can not :(
Example: create table departments(dept_id number, dept_name varchar2(25), location_id number);
Query: select d1.dept_name,max(d1.location_id)
from departments d1, departments d2
where  d1.dept_name = d2.dept_name
and d1.dept_name='AA'
group by d1.dept_name;
will give output.

Q16: Given the following statements:   (2014 SET 1)
aS1: 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?
(a) S1 is TRUE and S2 is a FALSE
(b) Both S1 and S2 are TRUE
(c) S1 is FALSE and S2 is a TRUE
(d) Both S1 and S2 are FALSE
Ans: (d) 
Sol:  (D) Both are false
S1: Foreign key constraint means a lot of constraints it has to be a primary key(which in turn has few constraints).
Alternate reason: Using a check condition we can have the same effect as Foreign key while adding elements to the child table. But when we delete an element from the parent table the referential integrity constraint is no longer valid. So, a check constraint cannot replace a foreign key.
So, we cannot replace it with a single check.
S2:  if a and b form a primary key in R , a alone cannot form a foreign key. i.e. R ( a , b  , c ) and S ( a , d , e ) , a of S references to a of R , but a of R is not candidate key, instead a prime attribute since a , b combined is a key.
Foreign key definition: it should be a candidate key in some other table(in our case it is only a prime attribute).

Q17: Consider the following relational schema.  (2013)

(a) I, II, III and IV
(b) I, II and III only
(c) I, II and IV only
(d) II, III and IV only
Ans: (a)
Sol: Option A:
This is a SQL query expression. It first perform a cross product of Students
and Registration, then WHERE clause only keeps those rows in the cross product
set where the student is registered for course no 107, and percentage is > 90.
Then select distinct statement gives the distinct names of those students as the
result set.
Option B:
This is a relational algebra expression. It first perform a NATURAL JOIN
of Students and Registration (NATURAL JOIN implicitly joins on the basis
of common attribute, which here is rollno ), then the select operation( sigma)
keeps only those rows where the student is registered for courseno 107,
and percentage is > 90. And then the projection operation (pi) projects only
distinct student names from the set.
Note: Projection operation (pi) always gives the distinct result.
Option C:
This is a Tuple Relational Calculus (TRC) language expression,
It is not a procedural language (i.e. it only tells “what to do”,
not “how to do”). It just represents a declarative mathematical
expression.
Here T is a Tuple variable.
From left to right, it can be read like this, “It is a set of
tuples T, where, there exists a tuple S in Relation Students, and
there exist a tuple R in relation Registration, such that
S.rollno = R.rollno AND R.couseno = 107 AND R.percent > 90 AND
T.sname = S.sname”. And the schema of this result is (sname), i.e. each
tuple T will contain only student name, because only T.sname has been defined
in the expression.
As TRC is a mathematical expression, hence it is expected to give only distinct result set.
Option D:
This is a Domain Relational Calculus (DRC) language expression.
This is also not procedural. Here SN is a Domain Variable. It can be read
from left to right like this “The set of domain variable SN, where,
there exist a domain variable SR , and a domain variable Rp, such that,
SN and SR domain variables is in relation Students and SR,107,RP is a domain
variables set in relation Registration, AND RP > 90 “
Above, SN represents sname domain attribute in Students relation, SR
represents rollno domain attribute in Students relation, and RP represents
percentage domain attribute in Registration relation.
The schema for the result set is (SN), i.e. only student name.
As DRC is a mathematical expression, hence it is expected to
give only distinct result set.
So clearly all options are correct.

Q18: Consider the following relations A, B and C:  (2012)
How many tuples does the result of the following SQL query contain?

(a) 4
(b) 3
(c) 0
(d) 1
Ans: (b) 
Sol: The meaning of “ALL” is the A. Age should be greater than all the values returned by the subquery. There is no entry with name “arun” in table B. So the subquery will return NULL. If a subquery returns NULL, then the condition becomes true for all rows of A (See this for details). So all rows of table A are selected.

Q19: Which of the following statements are TRUE about an SQL query?  (2012) 
P : An SQL query can contain a HAVING clause even if it does not have 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
Ans: (c) 
Sol:  Answer key is (C) Q and R are true.
But correct answer should be B. 

• When group by is not present, having is applied to the whole table

"A grouped table is a set of groups derived during the evaluation of a <group by clause> or a <having clause>. A group is a multiset of rows in which all values of the grouping column or columns are equal if a <group by clause> is specified, or the group is the entire table if no <group by clause> is specified. A grouped table may be considered as a collection of tables. Set functions may operate on the individual tables within the grouped table."
This shows that P is indeed correct.
So, as per standard it is not allowed, but in most current DBMS it is allowed. And there is no reason why this shouldn't be allowed. So, ideally 'S' is more correct than 'R' or both are debatable and marks should have been given to all.

Q20: Database table by name Loan_Records is given below.  (2011)

What is the output of the following SQL query?

(a)3
(b) 9
(c) 5
(d) 6
Ans: (c) 
Sol: When we perform the natural join on S and T then result will be like this

After that count (*) will count total tuples present in this table  so here it is  5.

Q21: 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.  (2011) 
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?
SELECT Y FROM T WHERE X=7;
(a) 127
(b) 255
(c) 129
(d) 257
Ans: (a)
Sol: X =1, Y = 1
X = 2, Y = 2 x 1 + 1 = 3
X = 3, Y = 2 x 3 + 1 = 7
X = 4, Y = 2 x 7 + 1 = 15
X = 5, Y = 2 x 15 +1 = 31
X = 6, Y = 2 x 31 + 1 = 63
X = 7, Y = 2 x  63 + 1 = 127

Q22: A relational schema for a train reservation database is given below  (2010) 

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

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

Working of a co related query
As we can see Reservation is written outside brackets and passenger is written inside brackets
So here we will take each tuple of Reservation table and compare it with all the tuples of passenger table
1^st Tuple of Reservation table

We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is True
So, we will now switch to Passenger table and check the conditions given for it i.e.
age>65 AND Passenger.pid=Reservation.pid
As we can see here the 1 ^st tuple of Passenger table is satisfying Passenger.pid =0 (= Resesrvation.pid ) but it is not satisfying age>65 so we can't select it.
The remaining tuples of Passenger table are satisfying age>65 but not Passenger.pid =0 (= Resesrvation.pid ) so we will not select pid=0 since no match is found.
∴ pid =0 from Reservation table is not selected in the output
2^ndTuple of Reservation table
We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is True
 So, we will now switch to Passenger table and check the conditions given for it i.e.
age>65 AND Passenger.pid=Reservation.pid
As we can see here only the 2 ^nd tuple of Passenger table is satisfying Passenger.pid =1 (= Resesrvation.pid ) and satisfying age>65 condition so we found a match
 ∴ pid =1 from Reservation table is selected in the output.
3^rd Tuple of Reservation table

We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is False
So, we will not select this tuple.
∴ pid =2 from Reservation table is not selected in the output.
4^th Tuple of Reservation table
We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is True
 So, we will now switch to Passenger table and check the conditions given for it i.e.
age>65 AND Passenger.pid=Reservation.pid
As we can see here only the no tuple of Passenger table is satisfying Passenger.pid =5 (= Resesrvation.pid )
So, we will not select this tuple.
∴ pid =5 from Reservation table is not selected in the output.
5^thTuple of Reservation table

We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is False
So, we will not select this tuple.
∴ pid =1 from Reservation table is not selected in the output.
6^th Tuple of Reservation table

We will fist check the conditions that are given  for Reservation table i.e. Class = "AC" which is True
 So, we will now switch to Passenger table and check the conditions given for it i.e.
age>65 AND Passenger.pid=Reservation.pid
As we can see here only the 4 n d tuple of Passenger table is satisfying Passenger.pid =3 (= Resesrvation.pid ) and satisfying age>65 condition so we found a match
 ∴ pid =3 from Reservation table is selected in the output.
So, finally only 1 and 3 are selected in the output.
 ∴ Option C . is the correct choice