Consider the following Entity Relationship Diagram: ER diagram with entities Employee, Department, Project and weak entity Dependent, relationships Works In, Works On, Has and Dependent of, attributes E_ID, Name, Email, D_ID, D_Name, P_ID, P_Name, Dep_ID, and a legend (E_ID: Employee ID, D_ID: Department ID, P_ID: Project ID, Dep_ID: Dependent ID) Choose the correct statements.

(A) An employee can work on more than one project.

(B) A project can have employees from more than one department, working on it.

(D) Every department must have at least one employee working in it.

(C) A dependent, who is not dependent on any employee, can exist.

We want to create the table nominee by using following SQL statements: CREATE TABLE nominee ( nominee_id VARCHAR(20) PRIMARY KEY, NomineeName VARCHAR(20), relationship VARCHAR(20), ins_id VARCHAR(20), FOREIGN KEY (ins_id) REFERENCES insurance(ins_id) ON DELETE CASCADE); Which of the following is/are the correct SQL statement that can be used to create table insurance?

(C) Option figure from the original question paper

(D) Option figure from the original question paper

(A) Option figure from the original question paper

(B) Option figure from the original question paper

Consider the following relational schema and answer the given subquestions. Figure from the passage in the original paper Figure from the original question paper

(D) Unique First names of players who are either in the Warriors team or in the Lakers team

(A) First names of players who are in either the Warriors or the Lakers team

(B) First names of players who are in both the Warriors and the Lakers teams

(C) Unique First names of players who are in both the Warriors and the Lakers teams

Consider the tables 3 and 4 and answer the given subquestions. Table 3: Relation team | team_id | name | ranking | country | |---|---|---|---| | Cric-1 | BCCI | 1 | India | | Cric-2 | ACB | 3 | Australia | | Cric-3 | PCB | 10 | Pakistan | | Cric-4 | NCB | 9 | Nepal | | Cric-5 | ECB | 4 | England | Table 4: Relation players | team_id | player_id | name | ranking | |---|---|---|---| | Cric-1 | BCCI-11 | Virat | 1 | | Cric-1 | BCCI-23 | Rohit | 3 | | Cric-3 | PCB-22 | Azam | 2 | | Cric-2 | ACB-12 | David | 6 | | Cric-2 | ACB-29 | Smith | 3 | | Cric-5 | ECB-88 | Cris | 4 | | Cric-5 | ECB-82 | Ben | 1 | Figure from the original question paper

(B) Option figure from the original question paper

(C) Option figure from the original question paper

(A) Option figure from the original question paper

(D) Option figure from the original question paper

Consider the tables 3 and 4 and answer the given subquestions. Table 3: Relation team | team_id | name | ranking | country | |---|---|---|---| | Cric-1 | BCCI | 1 | India | | Cric-2 | ACB | 3 | Australia | | Cric-3 | PCB | 10 | Pakistan | | Cric-4 | NCB | 9 | Nepal | | Cric-5 | ECB | 4 | England | Table 4: Relation players | team_id | player_id | name | ranking | |---|---|---|---| | Cric-1 | BCCI-11 | Virat | 1 | | Cric-1 | BCCI-23 | Rohit | 3 | | Cric-3 | PCB-22 | Azam | 2 | | Cric-2 | ACB-12 | David | 6 | | Cric-2 | ACB-29 | Smith | 3 | | Cric-5 | ECB-88 | Cris | 4 | | Cric-5 | ECB-82 | Ben | 1 | Using the SQL query shown below, a view named BestTeam is created. CREATE VIEW BestTeam AS SELECT p.player_id, p.name, t.country FROM players p join team t on p.team_id = t.team_id WHERE t.ranking < 4 Which among the following SQL queries will display the table shown below? | player_id | name | |---|---| | BCCI-11 | Virat | | BCCI-23 | Rohit |

(B) Option figure from the original question paper

(A) Option figure from the original question paper

(C) Option figure from the original question paper

(D) Option figure from the original question paper

September 2024 term · Database Management Systems · BSCS2001

Database Management Systems Quiz 1: 27 October 2024 (September 2024 term)

The IIT Madras BS Database Management Systems (DBMS) Quiz 1 paper sat on 27 Oct 2024, in the September 2024 term: 14 questions for 50 marks in 120 minutes. Every question is below with its answer. Take it as a timed mock test to be marked, or read it through first.

Questions: 14
Marks: 50
Duration: 120 min
MCQ: 8
MSQ: 4
Numerical: 2

Take as mock test Practise with answers

Updated 25 Sept 2026

Official paper: IIT M DIPLOMA AN EXAM QDD2 27 Oct 2024 · No negative marking.

Question 1

+2 marksOne correct option

Which of the following is the correct sequence for query processing?

A
Query → Parser and Translator → Evaluation Engine → Optimizer → Query Output
B
Query → Optimizer → Evaluation Engine → Parser and Translator → Query Output
C
Query → Parser and Translator → Optimizer → Evaluation Engine → Query Output
D
Query → Evaluation Engine → Parser and Translator → Optimizer → Query Output

Show answer

Correct answer

C
Query → Parser and Translator → Optimizer → Evaluation Engine → Query Output

Question 1 on its own page

Question 2

+2 marksOne correct option

A
1-A, 2-B, 3-C
B
1-B, 2-A, 3-C
C
1-B, 2-C, 3-A
D
1-C, 2-A, 3-B

Show answer

Correct answer

B
1-B, 2-A, 3-C

Question 2 on its own page

Question 3

+4 marksOne correct option

A relational database contains two separate tables called Employee and Department.
The Employee table has the attributes (Emp_ID, Name, Dept_ID, Salary) and the Department table contains the attributes (Dept_ID, Dept_Name).
The following insert statements were executed to populate the tables:

sql

1Insert into Employee values(001,'Mark',1,80000)
2Insert into Employee values(002,'Finn',2,60000)
3Insert into Employee values(003,'Rory',1,90000)
4Insert into Department values(1,'Forensics')
5Insert into Department values(2,'Cyber Crime')

How many rows and columns will be returned by the following SQL query?

sql

1select *
2from Employee, Department
3where salary>65000

A
4 rows and 6 columns
B
2 rows and 5 columns
C
4 rows and 5 columns
D
2 rows and 6 columns

Show answer

Correct answer

A
4 rows and 6 columns

Question 3 on its own page

Question 4

+4 marksOne correct option

The relation Students(Name, Total_Marks) contains the names and marks of different students where no two students have the same name or total marks. What will the following SQL query return?

sql

1select name
2from Students as S
3where (select count(*)
4       from Students as T
5       where T.Total_Marks>S.Total_Marks)<3

A
Names of the students with the first four highest marks
B
Name of the student with the 4th highest mark
C
Names of the students with the first three highest marks
D
Name of the student with the 3rd highest mark

Show answer

Correct answer

C
Names of the students with the first three highest marks

Question 4 on its own page

Question 5

+4 marksOne correct option

Consider the table Faculty(ID, Name, Department, Salary) and the two queries Q1 and Q2 given below. Our goal is to find the IDs of faculty members whose salary is greater than the highest salary in the Computer Science department. Assume that 'Computer Science' department has more than one faculty member. Which of the following queries will give the required output?

Q1:

sql

1select F.ID
2from Faculty as F
3where F.salary>All(select distinct salary
4                 from Faculty as T
5                 where T.department='Computer Science')

Q2:

sql

1select F.ID
2from Faculty as F
3where not exists(select *
4               from Faculty as T
5               where T.department='Computer Science' and T.salary>=F.salary)

A
Q1 is correct but Q2 is wrong
B
Q2 is correct but Q1 is wrong
C
Both Q1 and Q2 are wrong
D
Both Q1 and Q2 are correct

Show answer

Correct answer

D
Both Q1 and Q2 are correct

Question 5 on its own page

Question 6

+4 marksOne or more correct options

Consider the following Entity Relationship Diagram:

Choose the correct statements.

Select all that apply.

A
An employee can work on more than one project.
B
A project can have employees from more than one department, working on it.
C
A dependent, who is not dependent on any employee, can exist.
D
Every department must have at least one employee working in it.

Show answer

Correct answers

A
An employee can work on more than one project.
B
A project can have employees from more than one department, working on it.
D
Every department must have at least one employee working in it.

Question 6 on its own page

Question 7

+4 marksOne or more correct options

We want to create the table nominee by using following SQL statements:

sql

1CREATE TABLE nominee (
2    nominee_id VARCHAR(20) PRIMARY KEY,
3    NomineeName VARCHAR(20),
4    relationship VARCHAR(20),
5    ins_id VARCHAR(20),
6    FOREIGN KEY (ins_id) REFERENCES insurance(ins_id) ON DELETE CASCADE);

Which of the following is/are the correct SQL statement that can be used to create table insurance?

Select all that apply.

Show answer

Correct answers

Question 7 on its own page

Question 8

+4 marksNumerical answer

Consider relations Book and Author shown in Table 1 and Table 2

BookID	Title	AuthorID	Genre
1	Harry Potter and the Sorcerer's Stone	101	Fantasy
2	Pride and Prejudice	103	Fiction
3	The Great Gatsby	104	Fiction
4	The chamber of secrets	101	Adventure
5	The Catcher in the Rye	103	Fiction
6	1984	101	Dystopian

Table 1: Book Table

AuthorID	Name
101	J.K. Rowling
102	F. Scott Fitzgerald
103	Jane Austen
104	Herman Melville

Table 2: Author Table

How many tuples are returned by the following relational algebra expression?

$\prod_{Title}(\sigma_{Genre=\textit{'Fiction'}}(Book) \bowtie \sigma_{Name=\textit{'JaneAusten'}}(Author))$

Show answer

Correct answer: 2

Question 8 on its own page

Question 9

+3 marksNumerical answer

Consider the following relations:
Instructor(InsID, InsName, email)
Course(CID, CName)
Teaches(InsID, CID, Semester, Year)

If the relation Instructor and Teaches have 12, 8 rows respectively.
(Note: Consider all the attributes are having NOT NULL constraint.)

Execute the following query and compute the value of A - B where:
A = Maximum number of rows returned by the following query.
B = Minimum number of rows returned by the following query.

sql

1SELECT * FROM Instructor LEFT OUTER JOIN Teaches
2ON Instructor.InsID = Teaches.InsID;

Show answer

Correct answer: 7

Question 9 on its own page

Question 10

+4 marksOne or more correct options

Consider the following relational schema and answer the given subquestions.

Questions:

Find the first names of players whose names start with 'A' and have at least 4 characters.
Find out the number of players in each team.
List the team names that have players with the last name 'Smith'.

SQL queries:

sql

1a. SELECT FirstName FROM Player WHERE FirstName LIKE 'A___%';
2b. SELECT FirstName FROM Player WHERE FirstName LIKE 'A___';
3c. SELECT TeamID, COUNT(PlayerID) FROM Player GROUP BY PlayerID;
4d. SELECT TeamID, COUNT(PlayerID) FROM Player GROUP BY TeamID;
5e. SELECT distinct t.TeamName FROM Team t INNER JOIN Player p
6   ON t.TeamID = p.TeamID WHERE p.LastName = 'Smith';
7f. SELECT distinct t.TeamName FROM Team t, Player p WHERE t.TeamID = p.TeamID
8   AND p.LastName = 'Smith';

Match the correct SQL queries with the corresponding Questions.

Select all that apply.

A
1-a, 2-d, 3-f
B
1-b, 2-c, 3-e
C
1-a, 2-d, 3-e
D
1-a, 2-e, 3-f

Show answer

Correct answers

A
1-a, 2-d, 3-f
C
1-a, 2-d, 3-e

Question 10 on its own page

Question 11

+4 marksOne correct option

Consider the following relational schema and answer the given subquestions.

A
First names of players who are in either the Warriors or the Lakers team
B
First names of players who are in both the Warriors and the Lakers teams
C
Unique First names of players who are in both the Warriors and the Lakers teams
D
Unique First names of players who are either in the Warriors team or in the Lakers team

Show answer

Correct answer

D
Unique First names of players who are either in the Warriors team or in the Lakers team

Question 11 on its own page

Question 12

+3 marksOne correct option

Consider the tables 3 and 4 and answer the given subquestions.

Table 3: Relation team

team_id	name	ranking	country
Cric-1	BCCI	1	India
Cric-2	ACB	3	Australia
Cric-3	PCB	10	Pakistan
Cric-4	NCB	9	Nepal
Cric-5	ECB	4	England

Table 4: Relation players

team_id	player_id	name	ranking
Cric-1	BCCI-11	Virat	1
Cric-1	BCCI-23	Rohit	3
Cric-3	PCB-22	Azam	2
Cric-2	ACB-12	David	6
Cric-2	ACB-29	Smith	3
Cric-5	ECB-88	Cris	4
Cric-5	ECB-82	Ben	1

Show answer

Correct answer

Question 12 on its own page

Question 13

+4 marksOne or more correct options

Consider the tables 3 and 4 and answer the given subquestions.

Table 3: Relation team

team_id	name	ranking	country
Cric-1	BCCI	1	India
Cric-2	ACB	3	Australia
Cric-3	PCB	10	Pakistan
Cric-4	NCB	9	Nepal
Cric-5	ECB	4	England

Table 4: Relation players

team_id	player_id	name	ranking
Cric-1	BCCI-11	Virat	1
Cric-1	BCCI-23	Rohit	3
Cric-3	PCB-22	Azam	2
Cric-2	ACB-12	David	6
Cric-2	ACB-29	Smith	3
Cric-5	ECB-88	Cris	4
Cric-5	ECB-82	Ben	1

Select all that apply.

Show answer

Correct answers

Question 13 on its own page

Question 14

+4 marksOne correct option

Consider the tables 3 and 4 and answer the given subquestions.

Table 3: Relation team

team_id	name	ranking	country
Cric-1	BCCI	1	India
Cric-2	ACB	3	Australia
Cric-3	PCB	10	Pakistan
Cric-4	NCB	9	Nepal
Cric-5	ECB	4	England

Table 4: Relation players

team_id	player_id	name	ranking
Cric-1	BCCI-11	Virat	1
Cric-1	BCCI-23	Rohit	3
Cric-3	PCB-22	Azam	2
Cric-2	ACB-12	David	6
Cric-2	ACB-29	Smith	3
Cric-5	ECB-88	Cris	4
Cric-5	ECB-82	Ben	1

Using the SQL query shown below, a view named BestTeam is created.

sql

1CREATE VIEW BestTeam AS
2SELECT p.player_id, p.name, t.country
3FROM players p join team t on p.team_id = t.team_id
4WHERE t.ranking < 4

Which among the following SQL queries will display the table shown below?

player_id	name
BCCI-11	Virat
BCCI-23	Rohit

Show answer

Correct answer

Question 14 on its own page