Relational Databases and SQL - Database Management and Warehousing - Data Science & Artificial Intelligence Previous Year Questions

1

2024 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science & Artificial Intelligence (DA) 2024

Consider a database that includes the following relations:
Defender(name, rating, side, goals)
Forward(name, rating, assists, goals)
Team(name, club, price)
Which ONE of the following relational algebra expressions checks that every name occurring in Team appears in either Defender or Forward, where \(\phi\) denotes the empty set?

A

\(\Pi_{name}(\text{Team}) \setminus (\Pi_{name}(\text{Defender}) \cap \Pi_{name}(\text{Forward})) = \phi\)

B

\((\Pi_{name}(\text{Defender}) \cap \Pi_{name}(\text{Forward})) \setminus \Pi_{name}(\text{Team}) = \phi\)

C

\(\Pi_{name}(\text{Team}) \setminus (\Pi_{name}(\text{Defender}) \cup \Pi_{name}(\text{Forward})) = \phi\)

D

\((\Pi_{name}(\text{Defender}) \cup \Pi_{name}(\text{Forward})) \setminus \Pi_{name}(\text{Team}) = \phi\)

Open complete paper

2

2024 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science & Artificial Intelligence (DA) 2024

Consider the following two tables named Raider and Team in a relational database maintained by a Kabaddi league. The attribute ID in table Team references the primary key of the Raider table, ID.

Raider
ID	Name	Raids	RaidPoints
1	Arjun	200	250
2	Ankush	190	219
3	Sunil	150	200
4	Reza	150	190
5	Prathem	175	220
6	Gopal	193	215

Team
City	ID	BidPoints
Jaipur	2	200
Patna	3	195
Hyderabad	5	175
Jaipur	1	250
Patna	4	200
Jaipur	6	200

The SQL query described below is executed on this database:
SELECT *
FROM Raider, Team
WHERE Raider.ID=Team.ID AND City=”Jaipur” AND RaidPoints > 200;
The number of rows returned by this query is ______.

Check answer

Open complete paper

3

2025 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science & Artificial Intelligence (DA) 2025

Consider the following three relations:
Car (model, year, serial, color)
Make (maker, model)
Own (owner, serial)
A tuple in Car represents a specific car of a given model, made in a given year, with a serial number and a color. A tuple in Make specifies that a maker company makes cars of a certain model. A tuple in Own specifies that an owner owns the car with a given serial number. Keys are underlined; (owner, serial) together form key for Own. (⋈ denotes natural join)
\(\pi_{owner}(\text{Own} \bowtie (\sigma_{color='red'}(\text{Car}) \bowtie (\sigma_{maker='ABC'}(\text{Make}))))\)
Which one of the following options describes what the above expression computes?

A

All owners of a red car, a car made by ABC, or a red car made by ABC

B

All owners of more than one car, where at least one car is red and made by ABC

C

All owners of a red car made by ABC

D

All red cars made by ABC

Open complete paper

4

2025 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science & Artificial Intelligence (DA) 2025

On a relation named Loan of a bank:

Loan
loan_number	branch_name	amount
L11	Banjara Hills	90000
L14	Kondapur	50000
L15	SR Nagar	40000
L22	SR Nagar	25000
L23	Balanagar	80000
L25	Kondapur	70000
L19	SR Nagar	65000

the following SQL query is executed.
SELECT L1.loan_number
FROM Loan L1
WHERE L1.amount > (SELECT MAX (L2.amount)
FROM Loan L2
WHERE L2.branch_name = 'SR Nagar');
The number of rows returned by the query is ______________ (Answer in integer)

Check answer

Open complete paper

5

2025 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science & Artificial Intelligence (DA) 2025

Consider the following two relations, named Customer and Person, in a database:
Person (
aadhaar CHAR(12) PRIMARY KEY,
name VARCHAR(32) );

Customer (
name VARCHAR(32),
email VARCHAR(32) PRIMARY KEY,
phone CHAR(10),
aadhaar CHAR(12),
FOREIGN KEY (aadhaar) REFERENCES Person(aadhaar) );Which of the following statements is/are correct?

A

aadhaar is a candidate key in the Customer relation

B

phone can be NULL in the Customer relation

C

aadhaar is a candidate key in the Person relation

D

aadhaar can be NULL in the Person relation

Check answer

Open complete paper

6

2025 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science & Artificial Intelligence (DA) 2025

Consider the following tables, Loan and Borrower, of a bank.

Loan
loan_num	branch_name	amount
L11	Banjara Hills	90000
L14	Kondapur	50000
L15	SR Nagar	40000
L22	SR Nagar	25000
L23	Balanagar	80000
L25	Kondapur	70000
L19	SR Nagar	65000

Borrower
customer_name	loan_num
Anand	L11
Karteek	L11
Karteek	L14
Ankita	L15
Gopal	L19
Karteek	L22
Karteek	L23
Sunil	L23
Sunil	L25

Query: \(\pi_{\text{branch\_name, customer\_name}} (\text{Loan} \bowtie \text{Borrower}) \div \pi_{\text{branch\_name}} (\text{Loan})\) where \(\bowtie\) denotes natural join. The number of tuples returned by the above relational algebra query is __________ (Answer in integer)

Check answer

Open complete paper

7

2026 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science and Artificial Intelligence (DA) 2026

Consider two relations r and s defined on the relational schemas R(A, B) and S(E, C), respectively. A is the primary key of R and E is a foreign key of S referencing A in R.
Which of the following operations will NEVER violate the foreign key constraint?

A

Inserting records into relation r

B

Deleting records from relation s

C

Deleting records from relation r

D

Inserting records into relation s

Check answer

Open complete paper

8

2026 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science and Artificial Intelligence (DA) 2026

Consider the given relations \(X\), \(Y\) and \(Z\). The relation \(X\) has three columns \(P\), \(Q\) and \(R\). The relation \(Y\) has three columns \(P\), \(Q\) and \(S\). The relation \(Z\) has two columns \(P\) and \(T\).

X
P	Q	R
P1	Q1	R1
P2	Q2	R2
P3	Q3	R2

Y
P	Q	S
P1	Q1	2
P1	Q2	5
P2	Q1	6
P3	Q3	1

Z
P	T
P1	T1
P3	T2
P4	T3
P4	NULL

Consider the relational algebra expression
\[\Pi_{PRS}\left[\left(\sigma_{Q = Q3 \vee R = R2}[X] \bowtie Y\right) \bowtie \left(\sigma_{(S > 1)}[Y \bowtie Z]\right)\right]\]
where \(\bowtie\) denotes natural join operation.
Which of the following options is the correct output for the given expression?

A

Two rows (P1, R1, 2) and (P1, R1, 5)

B

Three rows (P1, R1, 2), (P1, R1, 5) and (P2, R2, 6)

C

One row (P1, R1, 2)

D

Zero rows

Open complete paper

9

2026 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science and Artificial Intelligence (DA) 2026

Consider a table Employee(EmpID, TeamID), where the column EmpID (ID of an employee) is the primary key. The column TeamID denotes the team ID of the team of which the employee is a member. TeamID is a NOT NULL column.
We want to display the size of the team (denoted as TeamSize) in which each employee is a member by using SQL. As an example, the desired output for the given Employee table is also shown in tabular form.
Which of the following is/are correct?

A

SELECT E.EmpID, B.TeamSize
FROM Employee AS E, (SELECT TeamID, COUNT(TeamID) AS TeamSize FROM Employee GROUP BY TeamID) AS B
WHERE E.TeamID = B.TeamID

B

SELECT A.EmpID, COUNT(B.TeamID) AS TeamSize
FROM Employee AS A, Employee AS B
WHERE A.TeamID = B.TeamID AND A.EmpID = B.EmpID
GROUP BY A.EmpID

C

SELECT B.EmpID, B.TeamSize
FROM (SELECT EmpID, COUNT(TeamID) AS TeamSize FROM Employee GROUP BY EmpID) AS B

D

SELECT A.EmpID, B.TeamSize
FROM Employee AS A, (SELECT COUNT(TeamID) AS TeamSize FROM Employee GROUP BY TeamID) AS B
WHERE A.TeamID = B.TeamID

Open complete paper

10

2026 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science and Artificial Intelligence (DA) 2026

Let there be two relations X and Y as shown. X has three columns P, Q and R. Y has two columns P and S.

X				Y
P	Q	R		P	S
P1	Q1	R1		P1	10
P2	Q2	R2		P1	15
P3	Q3	R2		P2	20
				P3	1

Consider that the following tuple relational calculus expression is evaluated.

\[ \{t \mid t \in X \land \exists z \in X (t[P] = z[P]) \land \exists m \in Y (m[P] = t[P] \land m[S] > 1)\} \]

The number of tuples that will be returned is __________ . (Answer in integer)

Check answer

Open complete paper

11

2026 · Data Science & Artificial Intelligence · Database Management and Warehousing · Relational Databases and SQL

Data Science and Artificial Intelligence (DA) 2026

Let Account be a relation as shown.

AccNo	Balance
A1	5000
A2	5000
A3	10000
A4	15000
A5	18000

Consider the given SQL query.

SELECT AccNo FROM Account AS A
WHERE (SELECT COUNT(*) FROM Account AS B
WHERE A.Balance < B.Balance) >= (SELECT COUNT(*) FROM Account AS C WHERE A.Balance > C.Balance)

The number of rows returned by the SQL query is __________ . (Answer in integer)

Check answer

Open complete paper