Division operation in DBMS Theory - Mini Project: Build & Apply

Division Operation in SQL

📖 Scenario: You work in a library database. You want to find authors who have written books in all genres available in the library.

🎯 Goal: Build an SQL query using the division operation concept to find authors who have written books in every genre listed.

📋 What You'll Learn

Create a table called AuthorsBooks with columns author and genre.

Create a table called Genres with a single column genre listing all genres.

Write a query that finds authors who have written books in all genres using division operation logic.

Use NOT EXISTS and EXCEPT or equivalent to implement the division operation.

💡 Why This Matters

🌍 Real World

Finding entities that relate to all items in another set is common in business and data analysis, such as customers who bought all products or employees trained in all required skills.

💼 Career

Understanding division operation helps in writing complex SQL queries for data filtering and reporting, a valuable skill for data analysts, database administrators, and backend developers.

1

Create the AuthorsBooks table with sample data

Create a table called AuthorsBooks with columns author and genre. Insert these exact rows: ('Alice', 'Fiction'), ('Alice', 'History'), ('Bob', 'Fiction'), ('Bob', 'Science'), ('Carol', 'Fiction'), ('Carol', 'History'), ('Carol', 'Science').

DBMS Theory

-- Create AuthorsBooks table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the Genres table with all genres

Create a table called Genres with a single column genre. Insert these exact genres: 'Fiction', 'History', 'Science'.

DBMS Theory

CREATE TABLE AuthorsBooks (author VARCHAR(50), genre VARCHAR(50));
INSERT INTO AuthorsBooks (author, genre) VALUES
('Alice', 'Fiction'),
('Alice', 'History'),
('Bob', 'Fiction'),
('Bob', 'Science'),
('Carol', 'Fiction'),
('Carol', 'History'),
('Carol', 'Science');
-- Create Genres table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE and INSERT INTO to add the genres.

3

Write the division operation query to find authors with all genres

Write a query using NOT EXISTS and EXCEPT to find authors who have written books in all genres listed in the Genres table. Use author and genre columns from AuthorsBooks.

DBMS Theory

CREATE TABLE AuthorsBooks (author VARCHAR(50), genre VARCHAR(50));
INSERT INTO AuthorsBooks (author, genre) VALUES
('Alice', 'Fiction'),
('Alice', 'History'),
('Bob', 'Fiction'),
('Bob', 'Science'),
('Carol', 'Fiction'),
('Carol', 'History'),
('Carol', 'Science');

CREATE TABLE Genres (genre VARCHAR(50));
INSERT INTO Genres (genre) VALUES
('Fiction'),
('History'),
('Science');

-- Write the division operation query below
-- Your code here

Need a hint?

Use NOT EXISTS with a subquery that uses EXCEPT to find missing genres per author.

4

Complete the query with ordering and final touches

Add an ORDER BY author clause at the end of the query to list authors alphabetically.

DBMS Theory

CREATE TABLE AuthorsBooks (author VARCHAR(50), genre VARCHAR(50));
INSERT INTO AuthorsBooks (author, genre) VALUES
('Alice', 'Fiction'),
('Alice', 'History'),
('Bob', 'Fiction'),
('Bob', 'Science'),
('Carol', 'Fiction'),
('Carol', 'History'),
('Carol', 'Science');

CREATE TABLE Genres (genre VARCHAR(50));
INSERT INTO Genres (genre) VALUES
('Fiction'),
('History'),
('Science');

SELECT DISTINCT author
FROM AuthorsBooks A
WHERE NOT EXISTS (
  SELECT genre FROM Genres
  EXCEPT
  SELECT genre FROM AuthorsBooks WHERE author = A.author
);
-- Add ORDER BY clause below
-- Your code here

Need a hint?

Use ORDER BY author to sort the results alphabetically.