uiz Space

May 2025 term · Database Management Systems · BSCS2001

Database Management Systems Quiz 1: 13 July 2025 (May 2025 term)

The IIT Madras BS Database Management Systems (DBMS) Quiz 1 paper sat on 13 Jul 2025, in the May 2025 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
11
MSQ
1
Numerical
2
Take as mock testPractise with answers

Updated

Official paper: IIT M DIPLOMA AN EXAM QDD2 13 July 2025 · No negative marking.

Question 1

+4 marksOne correct option

Consider the following table Sales:

EmpIDRegionAmount
101East5000
102East3000
103West7000
104East2000
105West4000
106North3500

Table : Sales

SQL Query:

sql
SELECT Region, SUM(Amount) AS TotalSales
FROM Sales
WHERE Amount > 3000
GROUP BY Region
HAVING SUM(Amount) > 6000;

What will be the output of the query?

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • C
Question 1 on its own page

Question 2

+4 marksOne correct option

Consider the following SQL statements executed in sequence on a table students:

sql
CREATE TABLE students (
    roll_no INT PRIMARY KEY,
    name VARCHAR(50),
    marks INT
);


INSERT INTO students VALUES (1, 'Amit', 80);
INSERT INTO students VALUES (2, 'Neha', 90);
INSERT INTO students VALUES (3, 'Ravi', 70);


UPDATE students
SET marks = marks + 10
WHERE marks < 85;


DELETE FROM students
WHERE name = 'Neha';


INSERT INTO students (roll_no, name, marks)
VALUES (4, 'Neha', 95);

What will be the content of the students table after execution?

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • A
Question 2 on its own page

Question 3

+4 marksOne correct option

Consider the following relations:
auto_part(pid, pname, color)
auto_suppliers(sid, sname, location)
catalog(pid, sid, price)

TRC

  1. {x∣∃s∈auto_suppliers ∃c∈catalog ∃p∈auto_part(s.location=‘Mumbai’∧c.price=5000∧x.sid=c.sid∧x.pname=p.pname∧s.sid=c.sid∧p.pid=c.pid)}\{x \mid \exists s \in auto\_suppliers\ \exists c \in catalog\ \exists p \in auto\_part(s.location = \textit{‘Mumbai’} \land c.price = 5000 \land x.sid = c.sid \land x.pname = p.pname \land s.sid = c.sid \land p.pid = c.pid)\}
  2. {x∣∃p∈auto_parts ∃c∈catalog(p.pname=‘Suspension’∧c.price=5000∧x.pid=p.pid∧p.pid=c.pid)}\{x \mid \exists p \in auto\_parts\ \exists c \in catalog(p.pname = \textit{‘Suspension’} \land c.price = 5000 \land x.pid = p.pid \land p.pid = c.pid)\}
  3. {x∣∃p∈auto_parts ∃c∈catalog ∃s∈auto_suppliers(p.pname=‘Suspension’∧c.price=5000∧x.pid=p.pid∧x.sname=s.sname∧p.pid=c.pid∧s.sid=c.sid)}\{x \mid \exists p \in auto\_parts\ \exists c \in catalog\ \exists s \in auto\_suppliers(p.pname = \textit{‘Suspension’} \land c.price = 5000 \land x.pid = p.pid \land x.sname = s.sname \land p.pid = c.pid \land s.sid = c.sid)\}
  4. {x∣∃s∈auto_suppliers ∃c∈catalog(s.location=‘Mumbai’∧c.price=5000∧x.sid=c.sid∧s.sid=c.sid)}\{x \mid \exists s \in auto\_suppliers\ \exists c \in catalog(s.location = \textit{‘Mumbai’} \land c.price = 5000 \land x.sid = c.sid \land s.sid = c.sid)\}

DRC

a. {<m>∣∃m,n,o(<m,n,o>∈auto_parts∧n=‘Suspension’)∧∃a,b,c(<a,b,c>∈catalog∧c=5000∧m=a)}\{< m > \mid \exists m, n, o(< m, n, o > \in auto\_parts \land n = \textit{‘Suspension’}) \land \exists a, b, c(< a, b, c > \in catalog \land c = 5000 \land m = a)\}
b. {<p>∣∃p,q,r(<p,q,r>∈auto_suppliers∧r=‘Mumbai’)∧∃a,b,c(<a,b,c>∈catalog∧c=5000∧p=b)}\{< p > \mid \exists p, q, r(< p, q, r > \in auto\_suppliers \land r = \textit{‘Mumbai’}) \land \exists a, b, c(< a, b, c > \in catalog \land c = 5000 \land p = b)\}
c. {<p>∣∃p,q,r(<p,q,r>∈auto_suppliers∧r=‘Mumbai’)∧∃a,b,c(<a,b,c>∈catalog∧c=5000)}\{< p > \mid \exists p, q, r(< p, q, r > \in auto\_suppliers \land r = \textit{‘Mumbai’}) \land \exists a, b, c(< a, b, c > \in catalog \land c = 5000)\}
d. {<m>∣(<m,n,o>∈auto_parts∧n=‘Suspension’)∧(<a,b,c>∈catalog∧c=5000∧m=a)}\{< m > \mid (< m, n, o > \in auto\_parts \land n = \textit{‘Suspension’}) \land (< a, b, c > \in catalog \land c = 5000 \land m = a)\}
e. {<p,n>∣∃m,n,o(<m,n,o>∈auto_parts)∧∃p,q,r(<p,q,r>∈auto_suppliers∧r=‘Mumbai’)∧∃a,b,c(<a,b,c>∈catalog∧c=5000∧m=a∧p=b)}\{< p, n > \mid \exists m, n, o(< m, n, o > \in auto\_parts) \land \exists p, q, r(< p, q, r > \in auto\_suppliers \land r = \textit{‘Mumbai’}) \land \exists a, b, c(< a, b, c > \in catalog \land c = 5000 \land m = a \land p = b)\}
f. {<m,q>∣∃m,n,o(<m,n,o>∈auto_parts∧n=‘Suspension’)∧∃p,q,r(<p,q,r>∈auto_suppliers)∧∃a,b,c(<a,b,c>∈catalog∧c=5000∧m=a∧p=b)}\{< m, q > \mid \exists m, n, o(< m, n, o > \in auto\_parts \land n = \textit{‘Suspension’}) \land \exists p, q, r(< p, q, r > \in auto\_suppliers) \land \exists a, b, c(< a, b, c > \in catalog \land c = 5000 \land m = a \land p = b)\}
g. {<p,n>∣∃m,n,o(<m,n,o>∈auto_parts)∧∃p,q,r(<p,q,r>∈auto_suppliers∧r=‘Mumbai’)∧∃a,b,c(<a,b,c>∈catalog∧c=5000)}\{< p, n > \mid \exists m, n, o(< m, n, o > \in auto\_parts) \land \exists p, q, r(< p, q, r > \in auto\_suppliers \land r = \textit{‘Mumbai’}) \land \exists a, b, c(< a, b, c > \in catalog \land c = 5000)\}
h. {<m,q>∣∃m,n,o(<m,n,o>∈auto_parts∧n=‘Suspension’)∧∃p,q,r(<p,q,r>∈auto_suppliers)∧∃a,b,c(<a,b,c>∈catalog∧c=5000)}\{< m, q > \mid \exists m, n, o(< m, n, o > \in auto\_parts \land n = \textit{‘Suspension’}) \land \exists p, q, r(< p, q, r > \in auto\_suppliers) \land \exists a, b, c(< a, b, c > \in catalog \land c = 5000)\}

Match the TRC expression to its correct equivalent DRC expression.

  1. A

    1-e, 2-d, 3-f, 4-c

  2. B

    1-e, 2-a, 3-f, 4-b

  3. C

    1-g, 2-a, 3-h, 4-b

  4. D

    1-g, 2-d, 3-h, 4-c

Show answer

Correct answer

  • B

    1-e, 2-a, 3-f, 4-b

Question 3 on its own page

Question 4

+4 marksOne correct option

Consider the relational schema Employees:

Employees(EmpId, Name, Salary, Department, Experience)

Now consider the following SQL query:

sql
SELECT Name
FROM Employee E1
WHERE Salary > (
    SELECT MAX(Salary)
    FROM Employee E2
    WHERE E2.Department = E1.Department
      AND E2.Experience < E1.Experience
)

The query returns:

  1. A

    Names of employees who earn less than the most experienced person in their department.

  2. B

    Names of employees who earn more than the most experienced person in their department.

  3. C

    Names of employees who earn more than all less experienced colleagues in their department.

  4. D

    Names of employees who earn less than all less experienced colleagues in their department.

Show answer

Correct answer

  • C

    Names of employees who earn more than all less experienced colleagues in their department.

Question 4 on its own page

Question 5

+4 marksOne correct option

Consider the relational schemas Student, Course and Enrollment:

Student(SId, Name, Age, DOB, Phone_Number)
Course(CId, CName, Credits)
Enrollment(EId, SId, CId, Term)

Now consider the following SQL query:

sql
SELECT S.Name
FROM Student S
WHERE NOT EXISTS (
    SELECT *
    FROM Course C
    WHERE NOT EXISTS (
        SELECT *
        FROM Enrollment E
        WHERE E.SId = S.SId
          AND E.CId = C.CId
    )
)

The query returns:

  1. A

    Names of students who have enrolled in at least one course

  2. B

    Names of students who have enrolled in exactly one course

  3. C

    Names of students who have enrolled in all available courses

  4. D

    Names of students who have not enrolled in any course

Show answer

Correct answer

  • C

    Names of students who have enrolled in all available courses

Question 5 on its own page

Question 6

+4 marksOne correct option

Consider the relational schemas Student, Professor, Course, Enrolled and Teaches:

Student(StudentId, Name, Age)

Professor(ProfId, Name, Age)

Course(CourseId, Title, Credits)

Enrolled(StudentId, CourseId, Grade)

Teaches(ProfId, CourseId)

Which of the following relational algebra queries will list the names of all students who have taken every course taught by Professor 'Smith' ?

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • A
Question 6 on its own page

Question 7

+3 marksOne correct option

Your task is to create a table Employee

(EmpID,Name,Email,PAN,Department)(EmpID, Name, Email, PAN, Department)

The following rules must be enforced:

  • Every employee must have a unique EmpID.
  • Each employee has a unique Email and PAN.
  • Employees may share the same Name and Department.

Which of the following SQL query is used to create the table Employee?

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • B
Question 7 on its own page

Question 8

+3 marksOne correct option

Consider the relation Product shown in Table 1:

pidpnameprice
P1Sunscreen cream70
P2Face Wash65
P3Tooth Paste20
P4Brush45
P5CombNULL
P6Carry bag0
P7Olive OilNULL

Table 1: Relation Product

Identify the output for the following SQL statement.

sql
SELECT AVG(price) FROM Product;
  1. A

    30.00

  2. B

    35.00

  3. C

    40.00

  4. D

    42.00

Show answer

Correct answer

  • C

    40.00

Question 8 on its own page

Question 9

+3 marksOne correct option
  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • D
Question 9 on its own page

Question 10

+3 marksOne correct option

Consider the following relations:

  • Book(bookid, title, genre)
  • Author(authorid, name)
  • Writes(bookid, authorid)

Choose the correct relational algebra expression to find the author IDs (authorid) of those authors who have written books of both the 'Science Fiction' and 'Fantasy' genres.

  1. A
  2. B
  3. C
  4. D
Show answer

Correct answer

  • A
Question 10 on its own page

Question 11

+2 marksOne correct option

Which of the following is the main role of the query parser in query processing?

  1. A

    Optimize the query by reordering joins

  2. B

    Translate high-level queries into relational algebra

  3. C

    Execute the query on the database

  4. D

    Maintain transaction logs

Show answer

Correct answer

  • B

    Translate high-level queries into relational algebra

Question 11 on its own page

Question 12

+4 marksOne or more correct options

Select all that apply.

  1. A

    The instructor can work in various courses and courses can have more than one instructors working in them.

  2. B

    We will not store the information about a health record once the instructor leaves the organization.

  3. C

    The instructor can manage more than one courses.

  4. D

    Total 5 tables are required to represent the given ERD into a relational schema.

Show answer

Correct answers

  • A

    The instructor can work in various courses and courses can have more than one instructors working in them.

  • B

    We will not store the information about a health record once the instructor leaves the organization.

  • C

    The instructor can manage more than one courses.

Question 12 on its own page

Question 13

+4 marksNumerical answer
Show answer

Correct answer: 100

Question 13 on its own page

Question 14

+4 marksNumerical answer

Consider the tables Department, Employee and Project given below:

DeptIDDeptName
1HR
2IT
3Finance

Department Table

EmpIDEmpNameDeptID
101Alice1
102Bob2
103Carol2
104Dave3
105Eve2
106Frank1

Employee Table

ProjIDProjNameEmpID
1001Alpha101
1002Beta102
1003Gamma103
1004Delta104
1005Epsilon105
1006Zeta102
1007Eta103
1008Theta106

Project Table

If the following statement is executed:

sql
DELETE FROM Department WHERE DeptID = 2;

how many total rows will be deleted across all three tables, assuming we have used ON DELETE Cascade? (Excluding the row originally deleted from the Department table)

Show answer

Correct answer: 8

Question 14 on its own page