Division operation in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the purpose of the division operation in SQL

What is the main purpose of the division operation in relational databases?

A To find all records in one table that have matching records in another table for every value in a set

B To divide numeric values in one column by values in another column

C To split a table into two equal parts based on a condition

D To calculate the average of values across multiple tables

💡 Hint

Think about queries that require matching all items from one set to another.

The division operation is used to find tuples in one relation that are related to all tuples in another relation. It helps answer queries like 'Find all entities related to every item in a set.'

🚀 Application

intermediate

Applying division operation to find students enrolled in all courses

Given two tables, StudentsCourses(StudentID, CourseID) and Courses(CourseID), which SQL query correctly finds students enrolled in all courses?

A SELECT StudentID FROM StudentsCourses WHERE CourseID IN (SELECT CourseID FROM Courses);

B SELECT DISTINCT StudentID FROM StudentsCourses WHERE CourseID = ALL (SELECT CourseID FROM Courses);

C SELECT StudentID FROM StudentsCourses WHERE CourseID = ANY (SELECT CourseID FROM Courses);

D SELECT StudentID FROM StudentsCourses GROUP BY StudentID HAVING COUNT(DISTINCT CourseID) = (SELECT COUNT(*) FROM Courses);

💡 Hint

Count how many unique courses each student is enrolled in and compare it to the total number of courses.

Option D counts distinct courses per student and compares it to the total courses count, ensuring the student is enrolled in all courses. Other options either check partial matches or use incorrect operators.

🔍 Analysis

advanced

Analyzing the result of a division operation query

Consider the following tables:

Suppliers(SupplierID)

Parts(PartID)

Supplies(SupplierID, PartID)

What does the query below return?

SELECT SupplierID FROM Supplies GROUP BY SupplierID HAVING COUNT(DISTINCT PartID) = (SELECT COUNT(*) FROM Parts);

A Suppliers who supply at least one part from the Parts table

B Suppliers who supply every part listed in the Parts table

C Parts supplied by all suppliers

D Suppliers who supply no parts

💡 Hint

Focus on the HAVING clause comparing counts of parts per supplier to total parts.

The query groups supplies by supplier and selects those whose count of distinct parts matches the total number of parts, meaning they supply all parts.

❓ Comparison

advanced

Comparing division operation with nested NOT EXISTS queries

Which SQL query correctly implements the division operation to find employees who have completed all required trainings listed in Trainings(TrainingID) using NOT EXISTS?

A SELECT EmployeeID FROM Employees WHERE TrainingID IN (SELECT TrainingID FROM Trainings);

B SELECT EmployeeID FROM Employees E WHERE EXISTS (SELECT TrainingID FROM Trainings T WHERE EXISTS (SELECT * FROM Completed C WHERE C.EmployeeID = E.EmployeeID AND C.TrainingID = T.TrainingID));

C SELECT EmployeeID FROM Employees E WHERE NOT EXISTS (SELECT TrainingID FROM Trainings T WHERE NOT EXISTS (SELECT * FROM Completed C WHERE C.EmployeeID = E.EmployeeID AND C.TrainingID = T.TrainingID));

D SELECT EmployeeID FROM Completed GROUP BY EmployeeID HAVING COUNT(*) = (SELECT COUNT(*) FROM Trainings);

💡 Hint

Think about how double negation with NOT EXISTS can express 'for all' conditions.

Option C uses double NOT EXISTS to ensure no training exists that the employee has not completed, correctly implementing division. Other options either check partial completion or misuse EXISTS.

❓ Reasoning

expert

Reasoning about division operation with missing data

Given tables Projects(ProjectID), Employees(EmployeeID), and WorksOn(EmployeeID, ProjectID), which statement is true if the following query returns an empty set?

SELECT EmployeeID FROM WorksOn GROUP BY EmployeeID HAVING COUNT(DISTINCT ProjectID) = (SELECT COUNT(*) FROM Projects);

A No employee works on all projects listed in the Projects table

B All employees work on all projects

C There are no projects in the Projects table

D Every project has at least one employee working on it

💡 Hint

Consider what an empty result means for the count comparison in the HAVING clause.

The query filters employees who work on every project. An empty result means no employee meets this condition, so no one works on all projects.