Paper 2 CS · UNIT 04 · MOCK TEST

Mock 4 – SQL (Part 1)

Unit 4: Database Management Systems · 19 questions · about 22 minutes. Press End Mock any time to see your score and the correct answers.

All 11 topic-wise mocks in this unit

0 / 19 answered 00:00

Q1. Which of the following relational algebraic expressions is equivalent to the SQL query SELECT X FROM B WHERE Y ?

Aσ_y(π_x(B)) Bπ_y(σ_x(B)) Cσ_x(π_x(B)) Dπ_x(σ_y(B))

Q2. Which of the following commands can be used to modify data in an SQL table?
A. INSERT
B. DELETE
C. UPDATE
D. SELECT
Choose the correct answer from the options given below:

AA, B, D only BA, C, D only CB, C, D only DA, B, C only

Q3. The INSERT command is used to
A. add a single tuple to a relation
B. add multiple tuples to a relation
C. add values to specific attributes
D. insert a new table
Choose the correct answer from the options given below:

AA only BA, B only CA, B, C only DB, C, D only

Q4. Match List I with List II

List I	List II
A. Project Join Normal Form	I. Concurrency
B. The lost update problem	II. Fifth Normal Form
C. Discretionary Access Control	III. SQL
D. Data Manipulation Language	IV. Security

AA-II, B-I, C-IV, D-III BA-I, B-II, C-IV, D-III CA-I, B-II, C-III, D-IV DA-IV, B-II, C-III, D-I

Q5. What is SQL primarily used for in the context of relational databases?

ATo design user interfaces BTo create and manipulate databases CTo display data on web pages DTo format printed reports

Q6. "CREATE TABLE T" in SQL is an example of:

ANormalization BDML CDDL DPrimary key

Q7. Which of the following statements is/are FALSE?
A. A user can assign privileges to any user on any relation.
B. A user can assign privileges to other users only on its own relations.
C. A system error that leads to access to a portion of the database is an example of a malicious attack.
D. Passwords themselves need to be secured from unauthorised access.
Choose the correct answer from the options given below:

AA and C Only BA and B Only CB and D Only DB and C Only

Directions (Q96–100): A company is implementing a new database system to manage its customer information. The database contains tables for customers, orders, products and sales representatives. Each customer can place multiple orders and each order can contain multiple products. Each order is associated with a specific sales representative who manages the account. The database is designed to improve efficiency in tracking sales and customer interactions.

Q8. Which SQL query retrieves the total number of orders placed by a specific customer?

ASELECT COUNT(*) AS total_orders FROM orders WHERE customer_id = [Customer_ID]; BSELECT COUNT(order_id) AS total_orders FROM orders WHERE customer_id = [Customer_ID]; CSELECT COUNT(DISTINCT order_id) AS total_orders FROM orders WHERE customer_id = [Customer_ID]; DSELECT SUM(order_id) AS total_orders FROM orders WHERE customer_id = [Customer_ID];

Q9. Which SQL query retrieves the total number of distinct customers who have placed orders?

ASELECT COUNT(DISTINCT customer_id) AS total_customers FROM orders; BSELECT COUNT(*) AS total_customers FROM customers; CSELECT COUNT(DISTINCT order_id) AS total_customers FROM orders; DSELECT COUNT(DISTINCT customer_id) AS total_customers FROM customers;

Q10. Given the query
SELECT s.rollno FROM student s WHERE s.sex = ALL (SELECT f.sex FROM professor f WHERE f.empid = s.advisor);
which retrieves students whose gender is the same as their advisor's. Consider:
S1. It has a correlated subquery.
S2. It has an uncorrelated subquery.
S3. If we replace ALL by ANY, the result will be different.
S4. If we replace ALL by ANY, the result will be the same.
Identify the TRUE statements:

AS1 and S3 are true BS1 and S4 are true CS2 and S3 are true DS2 and S4 are true

Directions (Q96–100): Use the following schema of an academic institution's relational database:

student (rollno, name, degree, year, sex, deptno, advisor)
department (deptid, name, hod, phone)
professor (empid, name, sex, startyear, deptno, phone)
course (courseid, cname, credits, deptno)
enrollment (rollno, courseid, sem, year, grade)
teaching (empid, courseid, sem, year, classroom)
prerequisite (precourseid, courseid)

deptno in student, professor and course refers to department.deptid; advisor (student) and hod (department) refer to professor.empid; rollno in enrollment refers to student; courseid in enrollment and teaching refers to course; empid in teaching refers to professor; precourseid and courseid in prerequisite refer to course.

Q11. Which of the following queries computes, for each department, the total credits of all the courses offered by the department?

SELECT deptid, name, SUM(credits) AS totalcredits
FROM department, course WHERE deptid = deptno
GROUP BY deptid, name;

SELECT deptid, name, COUNT(credits) AS totalcredits
FROM department, course WHERE deptid = deptno
GROUP BY deptid, name;

SELECT deptid, name, SUM(credits) AS totalcredits
FROM department, course;

SELECT deptid, name, SUM(credits) AS totalcredits
FROM department, course
GROUP BY deptid, name HAVING deptid = deptno;

Correct answer: A.

SELECT deptid, name, SUM(credits) AS totalcredits
FROM department, course WHERE deptid = deptno
GROUP BY deptid, name;

Join on deptid = deptno in WHERE, group by department, and SUM the credits. COUNT counts courses; C has no join or grouping; D filters the join after grouping, which is invalid.

Open this question →

Directions (Q96–100): Use the following schema of an academic institution's relational database:

student (rollno, name, degree, year, sex, deptno, advisor)
department (deptid, name, hod, phone)
professor (empid, name, sex, startyear, deptno, phone)
course (courseid, cname, credits, deptno)
enrollment (rollno, courseid, sem, year, grade)
teaching (empid, courseid, sem, year, classroom)
prerequisite (precourseid, courseid)

Q12. Which of the following queries retrieves the deptid and name of all departments whose total credits of offered courses is strictly greater than 40?

SELECT deptid, name, SUM(credits) AS totalcredits
FROM department, course WHERE totalcredits > 40
GROUP BY deptid, name HAVING deptid = deptno;

SELECT deptid, name, SUM(credits) AS totalcredits
FROM department, course
WHERE deptid = deptno AND totalcredits > 40
GROUP BY deptid, name;

SELECT deptid, name, SUM(credits) AS totalcredits
FROM department, course
GROUP BY deptid, name
HAVING deptid = deptno AND totalcredits > 40;

SELECT deptid, name, SUM(credits) AS totalcredits
FROM department, course WHERE deptid = deptno
GROUP BY deptid, name
HAVING totalcredits > 40;

Correct answer: D.

SELECT deptid, name, SUM(credits) AS totalcredits
FROM department, course WHERE deptid = deptno
GROUP BY deptid, name
HAVING totalcredits > 40;

Join conditions belong in WHERE, and conditions on aggregates belong in HAVING after GROUP BY.

Open this question →

Directions (Q96–100): Use the following schema of an academic institution's relational database:

student (rollno, name, degree, year, sex, deptno, advisor)
department (deptid, name, hod, phone)
professor (empid, name, sex, startyear, deptno, phone)
course (courseid, cname, credits, deptno)
enrollment (rollno, courseid, sem, year, grade)
teaching (empid, courseid, sem, year, classroom)
prerequisite (precourseid, courseid)

Q13. Given the query
SELECT s.rollno, s.name FROM student s WHERE EXISTS (SELECT c.courseid FROM course c WHERE c.deptno = 5 AND EXISTS (SELECT e.* FROM enrollment e WHERE e.courseid = c.courseid AND e.rollno = s.rollno));
which statement is correct?

AIt retrieves students who have not enrolled in all courses offered by department 5. BIt retrieves students who have enrolled in all courses offered by department 5. CIt retrieves students who have enrolled in at least one course offered by department 5. DIt retrieves students for whom there exists at least one course of department 5 that they have not enrolled in.

Q14. What are the drawbacks of using file systems to store data?
1. Data inconsistency
2. Difficulty in accessing data
3. Data isolation
4. Atomicity of updates
Choose the correct answer from the options given below:

A1, 2 only B2, 3, 4 only C1, 2, 3 only D1, 2, 3, 4

Q15. A trigger is

Aa statement that enables the DBMS to start Ba statement that is executed by the user when debugging an application program Ca condition the system tests for the validity of the database user Da statement that is executed automatically by the system as a side effect of a modification to the database

Q16. Assume that a software system P is to be developed with the following parameters:
Estimated size of the software: 33,480 LOC
Productivity: 620 LOC per person-month
Number of developers available: 6
Salary of each developer: Rs. 50,000 per month
Find (E, D, C), where E is the effort in person-months, D is the development time in months and C is the total cost in lakhs of rupees.

A(48, 8, 24) B(54, 9, 27) C(60, 10, 30) D(42, 7, 21)

Q17. Consider a disk system having 60 cylinders. Disk requests are received by the disk drive for cylinders 10, 22, 20, 2, 40, 6, and 38, in that order. Assuming the disk head is currently at cylinder 20, what is the time taken to satisfy all the requests if it takes 2 milliseconds to move from one cylinder to an adjacent one and the Shortest Seek Time First (SSTF) algorithm is used?

A240 milliseconds B96 milliseconds C120 milliseconds D112 milliseconds

Q18.

Match List I with List II. List I: A. Project Join Normal Form, B. The lost update problem, C. Discretionary Access Control, D. Data Manipulation Language. List II: I. Concurrency, II. Fifth Normal Form, III. SQL, IV. Security.

AA-II, B-I, C-IV, D-III BA-I, B-II, C-IV, D-III CA-I, B-II, C-III, D-IV DA-IV, B-II, C-III, D-I

Q19.

Which of the following statements is/are false?

A. A user can assign privileges to any user on any relation.

B. A user can assign privileges to any user on its own relations only,

C. System error that leads to accessibility to the portion of database is an example of malicious attack.

D. Passwords itself need to be secured from unauthorized access.

AA and C Only BA and B Only CB and D Only DB and C Only

Next: Mock 5 – SQL (Part 2) →