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.

Practice

1.

What does the division operation in a database primarily help to find?

easy

A. Entries that appear only once in a table

B. The sum of values in a column

C. The total count of rows in a table

D. Entries in one table related to all entries in another table

Solution

1. Step 1: Understand the division operation purpose

   The division operation is used to find records in one table that are related to every record in another table.

2. Step 2: Compare with other options

   The other options describe operations like finding unique entries, counting rows, or summing values, not the division operation.

3. Final Answer:

   Entries in one table related to all entries in another table -> Option D

4. Quick Check:

   Division operation = find all related entries [OK]

Hint: Division finds entries matching all in another set [OK]

Common Mistakes:

• Confusing division with counting or summing
• Thinking it finds entries related to some, not all
• Mixing division with join operations

2.

Which SQL keyword is commonly used to implement the division operation?

easy

A. GROUP BY

B. NOT EXISTS

C. HAVING

D. EXISTS

Solution

1. Step 1: Recall division implementation in SQL

   Division is often implemented using NOT EXISTS to check that no unmatched entries exist.

2. Step 2: Analyze other keywords

   GROUP BY groups rows, HAVING filters groups, and EXISTS checks existence but not for division logic.

3. Final Answer:

   NOT EXISTS -> Option B

4. Quick Check:

   Division uses NOT EXISTS for completeness check [OK]

Hint: Use NOT EXISTS to ensure all related entries exist [OK]

Common Mistakes:

• Using EXISTS instead of NOT EXISTS
• Confusing GROUP BY with division logic
• Assuming HAVING filters individual rows

3.

Consider two tables: Suppliers(supplier_id) and Parts(part_id), and a relation Supplies(supplier_id, part_id). Which SQL query finds suppliers who supply all parts?

SELECT supplier_id FROM Suppliers S WHERE NOT EXISTS (
  SELECT part_id FROM Parts P WHERE NOT EXISTS (
    SELECT * FROM Supplies SP WHERE SP.supplier_id = S.supplier_id AND SP.part_id = P.part_id
  )
);

medium

A. Finds parts not supplied by any supplier

B. Finds suppliers supplying no parts

C. Finds suppliers supplying all parts

D. Finds parts supplied by all suppliers

Solution

1. Step 1: Understand the nested NOT EXISTS logic

   The inner NOT EXISTS checks if a supplier does not supply a part. The outer NOT EXISTS ensures no such part exists, meaning the supplier supplies all parts.

2. Step 2: Match query purpose with options

   This query finds suppliers who supply every part, matching Finds suppliers supplying all parts.

3. Final Answer:

   Finds suppliers supplying all parts -> Option C

4. Quick Check:

   Double NOT EXISTS = all parts supplied [OK]

Hint: Double NOT EXISTS means 'for all' condition [OK]

Common Mistakes:

• Misreading NOT EXISTS as EXISTS
• Confusing suppliers with parts
• Ignoring nested query logic

4.

Identify the error in this SQL query intended to find students who have completed all courses:

SELECT student_id FROM Students S WHERE EXISTS (
  SELECT course_id FROM Courses C WHERE NOT EXISTS (
    SELECT * FROM Completed WHERE student_id = S.student_id AND course_id = C.course_id
  )
);

medium

A. Should use NOT EXISTS instead of EXISTS in outer query

B. Missing GROUP BY clause

C. Incorrect table alias usage

D. Should use INNER JOIN instead of subqueries

Solution

1. Step 1: Analyze the logic of EXISTS and NOT EXISTS

   The inner NOT EXISTS checks if a student hasn't completed a course. The outer EXISTS checks if such a course exists, meaning the student missed some courses.

2. Step 2: Correct the logic for 'all courses completed'

   To find students who completed all courses, the outer condition should be NOT EXISTS to ensure no missing courses.

3. Final Answer:

   Should use NOT EXISTS instead of EXISTS in outer query -> Option A

4. Quick Check:

   Use NOT EXISTS outside to confirm completeness [OK]

Hint: Outer NOT EXISTS ensures no missing entries [OK]

Common Mistakes:

• Using EXISTS instead of NOT EXISTS outside
• Assuming GROUP BY is needed here
• Confusing alias names

5.

You have two tables: Employees(emp_id) and Projects(proj_id), and a relation WorksOn(emp_id, proj_id). How would you find employees who work on every project that a specific manager manages, given a table Manages(manager_id, proj_id)?

hard

A. Use division operation with WorksOn and projects managed by the manager

B. Use a simple JOIN between Employees and Projects

C. Use GROUP BY on WorksOn without filtering projects

D. Use EXISTS to find employees working on any project

Solution

1. Step 1: Identify the problem as a division operation

   We want employees who work on all projects managed by a specific manager, which is a classic division problem.

2. Step 2: Apply division using WorksOn and filtered projects

   Filter projects by the manager in Manages, then find employees whose WorksOn covers all those projects.

3. Final Answer:

   Use division operation with WorksOn and projects managed by the manager -> Option A

4. Quick Check:

   Division finds employees working on all manager's projects [OK]

Hint: Filter projects by manager, then apply division on WorksOn [OK]

Common Mistakes:

• Ignoring filtering projects by manager
• Using JOIN without division logic
• Assuming GROUP BY alone solves the problem