Lab 2: JOINs and Multi-Table Queries

🎯 Learning Objectives

By the end of this lab, you will be able to: - Work with complex multi-table database schemas - Master JOIN operations (INNER, LEFT, RIGHT) - Write complex multi-table queries - Use string functions with JOINs - Handle NULL values in JOIN queries

📋 Prerequisites

Completion of Lab 1 or equivalent knowledge
Understanding of basic SQL operations
Familiarity with primary and foreign keys

🏫 Scenario: University Database System

In this lab, we'll work with a university database containing information about students, courses, and enrollments. This realistic scenario will help you understand how databases are used in real-world applications.

Database Schema Overview

Our database consists of three related tables: - Student: Contains student information (ID, name, GPA) - Course: Contains course details (ID, code, name, credit hours) - Enrolled: Links students to courses with grades (many-to-many relationship)

🚀 Getting Started

Step 1: Create the Database

-- Clean slate: DROP existing DATABASE IF it EXISTS
DROP DATABASE IF EXISTS lab2;
CREATE DATABASE lab2;
USE lab2;

Step 2: Create the Table Structure

-- DROP TABLES IN correct ORDER (child TABLES first due to FOREIGN KEY constraints)
DROP TABLE IF EXISTS Enrolled;  -- Junction TABLE (child)
DROP TABLE IF EXISTS Student;   -- Parent TABLE
DROP TABLE IF EXISTS Course;    -- Parent TABLE

-- CREATE Student TABLE
CREATE TABLE Student (
    sid INT PRIMARY KEY,           -- Student ID (UNIQUE identifier)
    fname CHAR(20),               -- First name (fixed LENGTH)
    lname CHAR(20),               -- Last name (fixed LENGTH)
    gpa FLOAT                     -- Grade Point Average (can be NULL)
);

-- CREATE Course TABLE  
CREATE TABLE Course (
    cid INT PRIMARY KEY,          -- Course ID (UNIQUE identifier)
    code CHAR(10),               -- Course code (e.g., "CS101")
    name CHAR(40),               -- Course name (e.g., "Intro to Programming")
    credithours SMALLINT         -- Credit hours (1-4 typically)
);

-- CREATE Enrolled TABLE (junction TABLE FOR many-to-many relationship)
CREATE TABLE Enrolled (
    sid INT,                     -- Student ID (FOREIGN KEY)
    cid INT,                     -- Course ID (FOREIGN KEY)
    grade CHAR(2),               -- Letter grade (A+, A, B+, etc.)
    PRIMARY KEY (sid, cid),      -- Composite PRIMARY KEY
    FOREIGN KEY (sid) REFERENCES Student(sid),   -- Link to Student TABLE
    FOREIGN KEY (cid) REFERENCES Course(cid)     -- Link to Course TABLE
);

Key Design Concepts: - Composite Primary Key: (sid, cid) ensures one grade per student per course - Foreign Key Constraints: Maintain referential integrity - Many-to-Many Relationship: Students can enroll in multiple courses, courses can have multiple students

Step 3: Populate the Database with Sample Data

Insert Student Data

-- INSERT student records (SOME students have NULL GPA - NOT yet calculated)
INSERT INTO Student VALUES(1,'Amie','Massey',3.0);
INSERT INTO Student VALUES(2,'Abigail','Larsen',NULL);
INSERT INTO Student VALUES(3,'Cora','Rowland',4);
INSERT INTO Student VALUES(4,'Alesha','Ferrell',3.5);
INSERT INTO Student VALUES(5,'Nina','Lowery',NULL);
INSERT INTO Student VALUES(6,'Scarlet','Lane',2.6);
INSERT INTO Student VALUES(7,'Lukas','Reeves',3.6);
INSERT INTO Student VALUES(8,'Ray','Chang',3.2);
INSERT INTO Student VALUES(9,'Ioan','Reid',2.5);
INSERT INTO Student VALUES(10,'Ryan','Peck',3.1);
INSERT INTO Student VALUES(11,'Marco','Lowery',3.8);
INSERT INTO Student VALUES(12,'Pearl', 'Mayo',3.7);
INSERT INTO Student VALUES(13,'Elmer', 'Cain',2.7);
INSERT INTO Student VALUES(14,'Isla', 'Mccall',2.2);
INSERT INTO Student VALUES(15,'Kate', 'Kimmel',NULL);

Verify student data:

SELECT * FROM Student ORDER BY sid;

Insert Course Data

-- INSERT computer science courses WITH varying credit hours
INSERT INTO Course VALUES(1,'CS101', 'Computer Programming I',3);
INSERT INTO Course VALUES(2,'CS102', 'Computer Programming II',3);
INSERT INTO Course VALUES(3,'CS321', 'Data Structures',4);
INSERT INTO Course VALUES(4,'CS5710', 'Network',4);
INSERT INTO Course VALUES(5,'CS4550', 'DATABASE',4);
INSERT INTO Course VALUES(6,'CS5990', 'Capstone Project',4);
INSERT INTO Course VALUES(7,'CS6020', 'Data Mining',4);

Verify course data:

SELECT * FROM Course ORDER BY cid;

Insert Enrollment Data

-- CS101 enrollments (ALL 15 students enrolled)

INSERT INTO Enrolled VALUES(1,1, 'A+');
INSERT INTO Enrolled VALUES(2,1, 'A-');
INSERT INTO Enrolled VALUES(3,1, 'B');
INSERT INTO Enrolled VALUES(4,1, 'C');
INSERT INTO Enrolled VALUES(5,1, 'D');
INSERT INTO Enrolled VALUES(6,1, 'A');
INSERT INTO Enrolled VALUES(7,1, 'B-');
INSERT INTO Enrolled VALUES(8,1, 'C+');
INSERT INTO Enrolled VALUES(9,1, 'D');
INSERT INTO Enrolled VALUES(10,1, 'C+');
INSERT INTO Enrolled VALUES(11,1, 'A-');
INSERT INTO Enrolled VALUES(12,1, 'A');
INSERT INTO Enrolled VALUES(13,1, 'B+');
INSERT INTO Enrolled VALUES(14,1, 'C+');
INSERT INTO Enrolled VALUES(15,1, 'A');
INSERT INTO Enrolled VALUES(1,2, 'A');
INSERT INTO Enrolled VALUES(2,2, 'C');
INSERT INTO Enrolled VALUES(3,2, 'B+');
INSERT INTO Enrolled VALUES(4,2, 'C-');
INSERT INTO Enrolled VALUES(5,2, 'D+');
INSERT INTO Enrolled VALUES(6,2, 'A-');
INSERT INTO Enrolled VALUES(7,2, 'C-');
INSERT INTO Enrolled VALUES(8,2, 'C');
INSERT INTO Enrolled VALUES(9,2, 'D+');
INSERT INTO Enrolled VALUES(10,2, 'C+');
INSERT INTO Enrolled VALUES(11,2, 'A-');
INSERT INTO Enrolled VALUES(12,2, 'A');
INSERT INTO Enrolled VALUES(13,2, 'B-');
INSERT INTO Enrolled VALUES(14,2, 'C');

INSERT INTO Enrolled VALUES (1,3,'C-');
INSERT INTO Enrolled VALUES (2,3,'B-');
INSERT INTO Enrolled VALUES (3,3,'D+');
INSERT INTO Enrolled VALUES (4,3,'C');
INSERT INTO Enrolled VALUES (5,3,'A+');
INSERT INTO Enrolled VALUES (6,3,'D+');
INSERT INTO Enrolled VALUES (7,3,'F');
INSERT INTO Enrolled VALUES (8,3,'A-');
INSERT INTO Enrolled VALUES (9,3,'D+');
INSERT INTO Enrolled VALUES (10,3,'D');
INSERT INTO Enrolled VALUES (11,3,'A+');
INSERT INTO Enrolled VALUES (12,3,'A');
INSERT INTO Enrolled VALUES (13,3,'B-');
INSERT INTO Enrolled VALUES (14,3,'C');
INSERT INTO Enrolled VALUES (15,3,'B-');

INSERT INTO Enrolled VALUES (1,4,'F');
INSERT INTO Enrolled VALUES (2,4,'A');
INSERT INTO Enrolled VALUES (3,4,'D-');
INSERT INTO Enrolled VALUES (4,4,'A-');
INSERT INTO Enrolled VALUES (5,4,'B-');
INSERT INTO Enrolled VALUES (6,4,'A-');
INSERT INTO Enrolled VALUES (7,4,'D+');
INSERT INTO Enrolled VALUES (8,4,'B+');
INSERT INTO Enrolled VALUES (9,4,'C+');
INSERT INTO Enrolled VALUES (10,4,'B');
INSERT INTO Enrolled VALUES (11,4,'F');
INSERT INTO Enrolled VALUES (12,4,'F');
INSERT INTO Enrolled VALUES (13,4,'A+');
INSERT INTO Enrolled VALUES (14,4,'A+');

INSERT INTO Enrolled VALUES (1,5,'A+');
INSERT INTO Enrolled VALUES (2,5,'A+');
INSERT INTO Enrolled VALUES (3,5,'B');
INSERT INTO Enrolled VALUES (4,5,'D+');
INSERT INTO Enrolled VALUES (5,5,'C-');
INSERT INTO Enrolled VALUES (6,5,'B-');
INSERT INTO Enrolled VALUES (7,5,'B');
INSERT INTO Enrolled VALUES (8,5,'B+');
INSERT INTO Enrolled VALUES (9,5,'A');
INSERT INTO Enrolled VALUES (10,5,'B');
INSERT INTO Enrolled VALUES (11,5,'D');
INSERT INTO Enrolled VALUES (12,5,'B');
INSERT INTO Enrolled VALUES (13,5,'B');
INSERT INTO Enrolled VALUES (14,5,'A+');

INSERT INTO Enrolled VALUES (1,6,'A+');
INSERT INTO Enrolled VALUES (2,6,'D');
INSERT INTO Enrolled VALUES (3,6,'D-');
INSERT INTO Enrolled VALUES (4,6,'C-');
INSERT INTO Enrolled VALUES (5,6,'C');
INSERT INTO Enrolled VALUES (6,6,'C-');
INSERT INTO Enrolled VALUES (7,6,'A-');
INSERT INTO Enrolled VALUES (8,6,'D');
INSERT INTO Enrolled VALUES (9,6,'B');
INSERT INTO Enrolled VALUES (10,6,'B-');
INSERT INTO Enrolled VALUES (11,6,'F');
INSERT INTO Enrolled VALUES (12,6,'C+');
INSERT INTO Enrolled VALUES (15,6,'F');

Verify enrollment data:

-- CHECK total enrollments
SELECT COUNT(*) AS total_enrollments FROM Enrolled;

-- See sample enrollments WITH student AND course names
SELECT s.fname, s.lname, c.code, c.name, e.grade
FROM Student s
JOIN Enrolled e ON s.sid = e.sid
JOIN Course c ON e.cid = c.cid
LIMIT 10;

🔍 Query Exercises

Now that we have our database populated, let's practice various types of queries. Each exercise builds on previous concepts while introducing new techniques.

Exercise 1: Multi-Table JOIN with String Functions

Problem: Find all students who received an 'A' or 'A+' in the Database course, ordered by their full name.

Analysis: - We need data from all three tables: - Student table: for student names - Course table: to identify the Database course - Enrolled table: for grades - This requires joining three tables with two join conditions

Your Solution:

-- Write your query here

💡 Click to see the solution

SELECT CONCAT(fname, ' ', lname) AS student_name 
FROM Student s, Course c, Enrolled e
WHERE c.cid = e.cid 
  AND s.sid = e.sid
  AND (e.grade = 'A+' OR e.grade = 'A')
  AND c.name LIKE '%DATABASE%' 
ORDER BY student_name;

Alternative using explicit JOIN syntax:

SELECT CONCAT(s.fname, ' ', s.lname) AS student_name
FROM Student s
JOIN Enrolled e ON s.sid = e.sid
JOIN Course c ON e.cid = c.cid
WHERE (e.grade = 'A+' OR e.grade = 'A')
  AND c.name LIKE '%DATABASE%'
ORDER BY student_name;

Expected Output:

student_name
Abigail Larsen
Amie Massey
Isla Mccall

Key Concepts: - `CONCAT()`: Combines first and last names - Multiple table joins: Connect related data across tables - `LIKE '%Database%'`: Pattern matching for course names - `OR` condition: Multiple grade criteria

Query 2

Find all countries that manufacture some product in the ‘Gadgets’ category. Assume you have the following data:

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

CREATE TABLE Product(
    PName CHAR(20) PRIMARY KEY,
    Price DOUBLE,
    category CHAR(20),
    Manufacturer CHAR(20)
);

CREATE TABLE Company(
    CName CHAR(20) PRIMARY KEY,
    StockPrice DOUBLE,
    Country CHAR(20)
);

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

INSERT INTO Company VALUES('GizmoWorks',25,'USA');
INSERT INTO Company VALUES('Canon',65,'Japan');
INSERT INTO Company VALUES('Hitachi',15, 'Japan');

INSERT INTO Product VALUES('Gizmo',19.99,'Gadgets','GizmoWorks');
INSERT INTO Product VALUES('Powergizmo',29.99,'Gadgets','GizmoWorks');
INSERT INTO Product VALUES('SingleTouch',149.99,'Photography','Canon');
INSERT INTO Product VALUES('MultiTouch',203.99,'Household','Hitachi');
SELECT Country FROM Product, Company
WHERE Manufacturer=CName AND Category='Gadgets'

This would return: Country USA USA How to get USA only once.

Query 3: An Unintuitive Query

Prepare the database as follows:

DROP TABLE IF EXISTS R;
DROP TABLE IF EXISTS T;
DROP TABLE IF EXISTS S;

CREATE TABLE R(A INT);
CREATE TABLE S(A INT);
CREATE TABLE T(A INT);

INSERT INTO R VALUES (1);
INSERT INTO R VALUES (1);
INSERT INTO R VALUES (2);
INSERT INTO R VALUES (3);
INSERT INTO R VALUES (5);
INSERT INTO R VALUES (7);
INSERT INTO R VALUES (7);
INSERT INTO R VALUES (8);

INSERT INTO S VALUES (1);
INSERT INTO S VALUES (1);
INSERT INTO S VALUES (2);
INSERT INTO S VALUES (3);

INSERT INTO T VALUES (1);
INSERT INTO T VALUES (2);
INSERT INTO T VALUES (3);
INSERT INTO T VALUES (7);

Run this query

SELECT  DISTINCT R.A
FROM R, S, T
WHERE R.A=S.A OR R.A=T.A

This would return the following

A
1
2
3
7

Remove all tuples from S

SET SQL_SAFE_UPDATES = 0;
DELETE FROM S;
SET SQL_SAFE_UPDATES = 1;

MySQL has something called safe mode, to disable it. We run SET SQL_SAFE_UPDATES = 0; and to enable it again SET SQL_SAFE_UPDATES = 1; Rerun the query

SELECT DISTINCT R.A
FROM   R, S, T
WHERE  R.A=S.A OR R.A=T.A

🎯 Additional Practice Examples

Example 1: Employee-Department Relationships

Consider a company database with employees and departments. Find all employees along with their department names, including employees without departments (using LEFT JOIN).

-- Sample data structure
CREATE TABLE Department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    location VARCHAR(50)
);

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10,2),
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

-- Query: Find all employees with their department info
SELECT e.emp_name, d.dept_name, d.location, e.salary
FROM Employee e
LEFT JOIN Department d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.emp_name;

Example 2: Product Catalog with Suppliers

Find all products along with their suppliers, showing product details even for products without suppliers.

-- Sample schema
CREATE TABLE Supplier (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(100),
    country VARCHAR(50)
);

CREATE TABLE Product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    supplier_id INT,
    price DECIMAL(10,2),
    FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id)
);

-- Query: List all products with supplier information
SELECT 
    p.product_name,
    p.price,
    COALESCE(s.supplier_name, 'No Supplier') AS supplier,
    COALESCE(s.country, 'N/A') AS supplier_country
FROM Product p
LEFT JOIN Supplier s ON p.supplier_id = s.supplier_id
ORDER BY p.product_name;

Example 3: Self-Join for Hierarchical Data

Find employees and their managers using a self-join on the Employee table.

-- Employee table with manager reference
CREATE TABLE EmployeeHierarchy (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES EmployeeHierarchy(emp_id)
);

-- Query: Show each employee with their manager's name
SELECT 
    e.emp_name AS employee,
    COALESCE(m.emp_name, 'No Manager') AS manager
FROM EmployeeHierarchy e
LEFT JOIN EmployeeHierarchy m ON e.manager_id = m.emp_id
ORDER BY manager, employee;

Example 4: Complex Multi-Table Join

Find customers, their orders, and product details in a sales database.

-- E-commerce example with multiple tables
CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE OrderHeader (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

CREATE TABLE OrderItem (
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES OrderHeader(order_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

-- Query: Complete order information with customer and product details
SELECT 
    c.customer_name,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total
FROM Customer c
JOIN OrderHeader o ON c.customer_id = o.customer_id
JOIN OrderItem oi ON o.order_id = oi.order_id
JOIN Product p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY c.customer_name, o.order_date, p.product_name;

💡 Key Takeaways

Use INNER JOIN when you only want matching records from both tables
Use LEFT JOIN to keep all records from the left table even without matches
Use RIGHT JOIN to keep all records from the right table even without matches
Self-joins are powerful for hierarchical or relationship data within the same table
Always use table aliases for clarity, especially with multiple joins
COALESCE function helps handle NULL values in join results

🏠 Portal | 🚀 Getting Started | 📝 SQL Cheat Sheet