Lab 4: Set Operations and Advanced Queries

🎯 Learning Objectives

By the end of this lab, you will be able to: - Understand set operations (UNION, UNION ALL) - Work with multiset operations - Implement SQL Division pattern - Write complex nested queries - Handle advanced subquery scenarios - Perform bulk UPDATE and DELETE operations

Multiset Operations

MySQL does not have INTERSECT or EXCEPT keyword. In the next lecture, we will explain an alternative. Compare these two queries:

SELECT  R.A 
FROM   R, S 
WHERE  R.A=S.A
UNION  
SELECT R.A 
FROM   R, T  
WHERE  R.A=T.A

SELECT  R.A
FROM   R, S
WHERE  R.A=S.A
UNION ALL
SELECT R.A
FROM   R, T
WHERE  R.A=T.A

Try the following SQL code

DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS Company;

CREATE TABLE Product(
  PName CHAR(20) PRIMARY KEY,
    maker CHAR(20),
    factory_loc CHAR(20)
);
CREATE TABLE Company(
  CName CHAR(20) PRIMARY KEY,
    hq_city CHAR(20)
);

ALTER TABLE Product ADD FOREIGN KEY (maker) REFERENCES Company(CName);

INSERT INTO Company VALUES('X co.','Seattle');
INSERT INTO Company VALUES('Y inc.','Seattle');
INSERT INTO Company VALUES('A co.', 'Tokyo');

INSERT INTO Product VALUES('X','X co.','US');
INSERT INTO Product VALUES('A','A co.','China');
INSERT INTO Product VALUES('A+','A co.','US');
INSERT INTO Product VALUES('Y','Y inc.','China');

Compare the three queries below:

SELECT hq_city
FROM   Company, Product
WHERE  maker = cname 
  AND factory_loc='US'
AND hq_city IN (
SELECT hq_city
FROM   Company, Product
WHERE  maker = cname
AND factory_loc='China'
);

SELECT maker, hq_city
FROM   Company, Product
WHERE  maker = cname 
  AND factory_loc='US'
AND (maker,hq_city) IN (
SELECT maker, hq_city
FROM   Company, Product
WHERE  maker = cname
AND factory_loc='China'
);

SELECT DISTINCT hq_city
FROM   Company, Product
WHERE  maker = cname 
       AND cname IN (
        SELECT maker
        FROM   Product
        WHERE  factory_loc = 'US')
     AND cname IN (
        SELECT maker
        FROM   Product
        WHERE  factory_loc = 'China');

To Delete or update multiple rows

To disable safe updates or delete

SET SQL_SAFE_UPDATES=0;

To enable them back

SET SQL_SAFE_UPDATES=1;

Lab2.2

Run the following query

SELECT DISTINCT hq_city
FROM   Company, Product
WHERE  maker = cname 
       AND cname IN (
    SELECT maker
      FROM   Product
      WHERE  factory_loc = 'US')
   AND cname IN (
    SELECT maker
      FROM   Product
      WHERE  factory_loc = 'China')

To understand the previous query, you may break it into three parts.

Q1:

    SELECT maker
    FROM   Product
      WHERE  factory_loc = 'US'

Q2:


SELECT maker
      FROM   Product
      WHERE  factory_loc = 'China'

Q3:

SELECT *
FROM   Company, Product
WHERE  maker = cname

Another way:

SELECT DISTINCT hq_city
FROM   Company, (SELECT maker
      FROM   Product
      WHERE  factory_loc = 'US') US, 
      (SELECT maker
      FROM   Product
      WHERE  factory_loc = 'China') Ch
WHERE  us.maker = cname 
AND    ch.maker= cname

Another one:

SELECT DISTINCT hq_city
FROM   Company, Product p1,   Product p2
WHERE  p1.maker = cname 
AND    p2.maker= cname 
AND  p1.factory_loc = 'US'
AND  p2.factory_loc = 'China';

Division


 CREATE TABLE student (SID INT PRIMARY KEY,Name VARCHAR(10),Major
 VARCHAR(4)); 
 CREATE TABLE enrolled (SID INT,cid VARCHAR(5),PRIMARY KEY
 (sid,cid)); 
 INSERT INTO student VALUES (40,'alex','cs'), (41, 'john','cs'), (42, 'mat', 'cs'), (43, 'zack', 'math');
 INSERT INTO Enrolled VALUES (40,'cs101'),(40,  'cs102'),(41, 'cs101'),(41,  'cs404'),(41,'cs405'), (42,'cs101'),(42,'cs102'),(42,'cs203'),(43,'cs101'),(43,'cs102');

Find students who are taking all courses taken by student 40

Solution


SELECT DISTINCT NAME FROM STUDENT S WHERE major='cs’ AND NOT EXISTS
 (SELECT * FROM ENROLLED E2 WHERE SID=40 AND CID NOT IN (SELECT CID
 FROM ENROLLED E WHERE E.SID=S.SID) )

  SELECT DISTINCT NAME FROM STUDENT S WHERE major=‘cs’ AND  NOT EXISTS
     (SELECT * FROM ENROLLED E2  WHERE SID=40 AND NOT EXISTS ( SELECT *
     FROM ENROLLED E WHERE E.SID=S.SID AND E2.CID=E.CID) )

🎯 Additional Practice Examples

Example 1: Combining Customer Data from Multiple Sources

Merge customer records from different systems using UNION.

-- Combining data from multiple customer tables
CREATE TABLE OnlineCustomers (
    customer_id INT,
    customer_name VARCHAR(100),
    email VARCHAR(100),
    source VARCHAR(20) DEFAULT 'Online'
);

CREATE TABLE StoreCustomers (
    customer_id INT,
    customer_name VARCHAR(100),
    phone VARCHAR(20),
    source VARCHAR(20) DEFAULT 'Store'
);

-- Query: Get all customers from both sources (no duplicates)
SELECT customer_id, customer_name, email AS contact, source
FROM OnlineCustomers
UNION
SELECT customer_id, customer_name, phone AS contact, source
FROM StoreCustomers
ORDER BY customer_name;

-- Query: Get all customers including duplicates
SELECT customer_id, customer_name, source
FROM OnlineCustomers
UNION ALL
SELECT customer_id, customer_name, source
FROM StoreCustomers;

Example 2: Finding Common Elements (INTERSECT Alternative)

Find customers who exist in both online and store systems.

-- MySQL doesn't have INTERSECT, so we use subqueries or joins
-- Method 1: Using IN
SELECT DISTINCT customer_id, customer_name
FROM OnlineCustomers
WHERE customer_id IN (
    SELECT customer_id FROM StoreCustomers
);

-- Method 2: Using INNER JOIN (more efficient)
SELECT DISTINCT o.customer_id, o.customer_name
FROM OnlineCustomers o
INNER JOIN StoreCustomers s ON o.customer_id = s.customer_id;

-- Method 3: Using EXISTS
SELECT DISTINCT customer_id, customer_name
FROM OnlineCustomers o
WHERE EXISTS (
    SELECT 1 FROM StoreCustomers s 
    WHERE s.customer_id = o.customer_id
);

Example 3: Complex Filtering with Subqueries

Find products that have never been ordered using NOT EXISTS.

-- Schema for products and orders
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

CREATE TABLE OrderItems (
    order_id INT,
    product_id INT,
    quantity INT
);

-- Query: Find products that have never been ordered
SELECT p.product_id, p.product_name, p.category, p.price
FROM Products p
WHERE NOT EXISTS (
    SELECT 1 FROM OrderItems oi 
    WHERE oi.product_id = p.product_id
);

-- Alternative using LEFT JOIN
SELECT p.product_id, p.product_name, p.category, p.price
FROM Products p
LEFT JOIN OrderItems oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;

Example 4: Bulk UPDATE with Subquery

Update prices based on complex conditions using subqueries.

-- Increase prices for products in popular categories
UPDATE Products
SET price = price * 1.10
WHERE category IN (
    SELECT category 
    FROM (
        SELECT p.category
        FROM Products p
        JOIN OrderItems oi ON p.product_id = oi.product_id
        GROUP BY p.category
        HAVING SUM(oi.quantity) > 100
    ) AS popular_categories
);

-- Update discontinued products based on last order date
UPDATE Products p
SET price = price * 0.50
WHERE NOT EXISTS (
    SELECT 1 
    FROM OrderItems oi 
    JOIN Orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id 
    AND o.order_date > DATE_SUB(NOW(), INTERVAL 1 YEAR)
);

Example 5: SQL Division Pattern - Advanced

Find customers who have ordered all products in a specific category.

-- Find customers who ordered ALL products from 'Electronics' category
-- This is the relational division problem

-- Method 1: Using NOT EXISTS with double negation
SELECT DISTINCT c.customer_id, c.customer_name
FROM Customers c
WHERE NOT EXISTS (
    -- Find products in Electronics category
    SELECT p.product_id
    FROM Products p
    WHERE p.category = 'Electronics'
    AND NOT EXISTS (
        -- Check if customer ordered this product
        SELECT 1
        FROM Orders o
        JOIN OrderItems oi ON o.order_id = oi.order_id
        WHERE o.customer_id = c.customer_id
        AND oi.product_id = p.product_id
    )
);

-- Method 2: Using COUNT and GROUP BY
SELECT c.customer_id, c.customer_name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
WHERE p.category = 'Electronics'
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(DISTINCT p.product_id) = (
    SELECT COUNT(*) 
    FROM Products 
    WHERE category = 'Electronics'
);

💡 Key Takeaways - Set Operations & Advanced Queries

UNION removes duplicates; UNION ALL keeps all rows (faster)
MySQL doesn't have INTERSECT/EXCEPT - use IN, EXISTS, or JOIN instead
NOT EXISTS is often more efficient than NOT IN with NULLs
Use subqueries in WHERE for dynamic filtering based on aggregated data
Division pattern (A÷B) finds entities related to ALL members of another set
Bulk UPDATE/DELETE can use subqueries for complex conditions
Always test complex queries on sample data first!

🏠 Portal | 🚀 Getting Started | 📝 SQL Cheat Sheet