Lab Manual: Database Systems

University of Management and

Technology, Lahore Campus
Lab- 10 Manual
Lab Instructor: Riaz Ahmad
Department of Computer Science
Email: [email protected]
Lab: 10 SQL Joins
Objectives:
Objective: This lab manual is designed to help students understand and apply different types of
joins in SQL databases. By the end of this lab, students should be able to:

 Explain the concept and significance of joins in relational databases.

 Identify and differentiate between various types of joins.
 Write SQL queries using different join types to retrieve data from multiple tables.
 Use join statements to filter and manipulate data efficiently.

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

● Each SELECT statement within UNION must have the same number of
columns
● The columns must also have similar data types
● The columns in each SELECT statement must also be in the same order

UNION Syntax
SELECT column1 , column2 ….
FROM table1
WHERE condition
INTERSECT
SELECT column1 , column2 ….
FROM table2
WHERE condition’

Example:
SELECT firstName AS CommonColumn FROM customers
UNION
SELECT CAST(orderDate AS VARCHAR(50))
FROM orders ORDER BY CommonColumn;

The SQL INTERSECTION Operator

The INTERSECT operator is used to return the result-set of two or more SELECT statements
which are common.

Department of Computer Science, UMT, Lahore. 1 Riaz Ahmad

 Lab Manual: Database Systems

● Each SELECT statement within INTERSECT must have the same number
of columns
● The columns must also have similar data types
● The columns in each SELECT statement must also be in the same order

INTERSECTION Syntax
SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;

Demo Database / Relational Model

Department of Computer Science, UMT, Lahore. 2 Riaz Ahmad

 Lab Manual: Database Systems

Examples:
Example 1: Find Customers Who Ordered Products
SELECT firstName FROM customers
INTERSECT
SELECT firstName FROM orders
JOIN customers ON orders.customerID = customers.customerID;

OR
select * from orders;
select * from customers;

SELECT firstName, lastname,email,password FROM customers

INTERSECT
SELECT firstName, lastname,email,password FROM orders
JOIN customers ON orders.customerID = customers.customerID;
Example 2: Find Product Categories That Have Orders
SELECT c.name
FROM categories c

INTERSECT

SELECT c.name
FROM products p
JOIN orderDetails od ON p.productID = od.productID
JOIN categories c ON p.categoryID = c.categoryID;
Example 3: Find Customers Who Ordered Specific Products
SELECT firstName FROM customers
INTERSECT
SELECT firstName FROM orders
JOIN orderDetails ON orders.orderID = orderDetails.orderID
JOIN customers ON orders.customerID = customers.customerID
WHERE productID = 1;

Department of Computer Science, UMT, Lahore. 3 Riaz Ahmad

 Lab Manual: Database Systems

Implementation in SQL
CREATE DATABASE Shops;

USE Shops;

/* Customers Table */
CREATE TABLE customers (
customerID INT IDENTITY(1,1) PRIMARY KEY,
firstName VARCHAR(50) NOT NULL,
lastName VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL
);

/* Categories Table */
CREATE TABLE categories (
categoryID INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50) NOT NULL
);

/* Products Table */
CREATE TABLE products (
productID INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
categoryID INT NOT NULL,
FOREIGN KEY (categoryID) REFERENCES categories(categoryID)
);

/* Orders Table */
CREATE TABLE orders (
orderID INT IDENTITY(1,1) PRIMARY KEY,
customerID INT NOT NULL,
orderDate DATE NOT NULL,
FOREIGN KEY (customerID) REFERENCES customers(customerID)
);

/* Order Details Table */

CREATE TABLE orderDetails (
orderID INT NOT NULL,
productID INT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (orderID, productID),
FOREIGN KEY (orderID) REFERENCES orders(orderID),
FOREIGN KEY (productID) REFERENCES products(productID)
);

Department of Computer Science, UMT, Lahore. 4 Riaz Ahmad

 Lab Manual: Database Systems

/* Insert Data into Customers */

INSERT INTO customers (firstName, lastName, email, password) VALUES
('Riaz', 'Ahmad', '[email protected]', 'password123'),
('Bilal', 'Ali', '[email protected]', 'pak123'),
('Hamza', 'Yaseen', '[email protected]', 'ham1223'),
('Naveed', 'Iqbal', '[email protected]', 'nav13'),
('Iqra', 'Mehmood', '[email protected]', 'iqiqra');

/* Insert Data into Categories */

INSERT INTO categories (name) VALUES
('Laptop'),
('Mobile'),
('Portable Devices');

/* Insert Data into Products */

INSERT INTO products (name, price, categoryID) VALUES
('HP EliteBook 830', 70000, 1),
('HP Pavilion 360', 90000, 1),
('Samsung A32', 40000, 2),
('USB 3.2 Gen 1', 2000, 3);

/* Insert Data into Orders */

INSERT INTO orders (customerID, orderDate) VALUES
(1, '2023-01-01'),
(1, '2023-03-01'),
(2, '2023-03-03'),
(3, '2023-04-07'),
(1, '2023-05-06'),
(2, '2023-02-07'),
(4, '2023-04-05'),
(4, '2023-05-06');

/* Insert Data into OrderDetails */

INSERT INTO orderDetails (orderID, productID, quantity) VALUES
(1, 1, 2),
(1, 2, 2),
(2, 3, 3),
(3, 1, 4),
(4, 1, 1),
(5, 1, 5),
(6, 1, 2),
(6, 2, 1),
(6, 3, 2),
(7, 1, 4),
(8, 4, 6);

Department of Computer Science, UMT, Lahore. 5 Riaz Ahmad

 Lab Manual: Database Systems

Introduction to Joins

What is a Join? A join in SQL is used to retrieve data from multiple tables based on a related
column. It allows combining rows from two or more tables into a single result set.

Why Are Joins Important?

 Helps in organizing data spread across multiple tables.

 Eliminates data redundancy and improves database efficiency.
 Allows complex queries that provide meaningful insights.

Types of Joins:
 Inner Join
 Left Join
 Right Join
 Full Outer Join

SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a
related column between them.
SELECT customers.customerID, customers.firstName, customers.lastName,
orders.orderDate
FROM customers
JOIN orders
ON customers.customerID = orders.customerID;

SELECT orders.orderID, orders.orderDate, customers.customerID,

customers.firstName, customers.lastName
FROM orders JOIN
customers
ON customers.customerID = orders.customerID;

SQL INNER JOIN

The INNER JOIN keyword selects records that have matching values in both
tables.
-- inter join
SELECT orders.orderID, customers.firstName, customers.customerID FROM
orders
INNER JOIN customers
ON orders.customerID = customers.customerID;

Department of Computer Science, UMT, Lahore. 6 Riaz Ahmad

 Lab Manual: Database Systems

SQL LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and
the matching records from the right table (table2). The result is 0 records from
the right side, if there is no match.
-- SQL LEFT JOIN
SELECT customers.customerID, customers.firstName, orders.orderID,
orders.orderDate
FROM customers
LEFT JOIN orders ON customers.customerID = orders.customerID;

Department of Computer Science, UMT, Lahore. 7 Riaz Ahmad

 Lab Manual: Database Systems

SQL RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table2),
and the matching records from the left table (table1). The result is 0 records
from the left side, if there is no match.

-- Right join
SELECT Orders.OrderID, orders.OrderDate, customers.firstName
FROM Orders
RIGHT JOIN Customers ON orders.customerID=customers.customerID;

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all records when there is a match in
left (table1) or right (table2) table records.

Tip: FULL OUTER JOIN and FULL JOIN are the same.
SELECT customers.customerID, customers.firstName, orders.orderID, orders.orderDate
FROM customers
FULL OUTER JOIN orders ON customers.customerID = orders.customerID;

Department of Computer Science, UMT, Lahore. 8 Riaz Ahmad

 Lab Manual: Database Systems

Graded Exercise
Note:Use the same dataset that is given in the statrt of Manual

🔗 JOIN-Based Queries (Inner, Left, Right, Full Outer)

1. List all products ordered along with customer first names who ordered them.
🔹 Hint: Use INNER JOIN on orders, orderDetails, and customers.
2. List all customers and their orders (if any). Include customers with no orders.
🔹 Hint: Use LEFT JOIN between customers and orders.
3. List all orders and include customers even if the order is not associated properly.
🔹 Hint: Use RIGHT JOIN between customers and orders.
4. Show all customers and all orders, even if they don’t match.
🔹 Hint: Use FULL OUTER JOIN between customers and orders.
5. List order details including customer name, product name, and quantity.
🔹 Hint: Join customers, orders, orderDetails, and products.
6. List customers who haven't placed any orders.
🔹 Hint: Use LEFT JOIN from customers to orders, filter orderID IS NULL.
7. List all orders with products priced over 50,000.
🔹 Hint: Join orders, orderDetails, and products, filter price > 50000.
8. Display all categories and the products that fall under them.
🔹 Hint: Use LEFT JOIN between categories and products.
9. List each customer and total number of products they ordered.
🔹 Hint: Use multiple joins and SUM(quantity) with GROUP BY.

🔁 UNION and INTERSECT-Based Queries

10. Get all distinct names from both categories and products tables.
🔹 Hint: Use UNION on SELECT name FROM categories and products.
11. Get all names from categories and products (including duplicates).
🔹 Hint: Same as above but use UNION ALL.

Department of Computer Science, UMT, Lahore. 9 Riaz Ahmad

 Lab Manual: Database Systems

12. Find category names that also appear as product names.

🔹 Hint: Use INTERSECT between SELECT name FROM categories and products.
13. List first names of customers who have placed orders.
🔹 Hint: Join customers and orders, then INTERSECT with customers.

14. Find products that were never ordered and orders with missing product links.
🔹 Hint: Use FULL OUTER JOIN between products and orderDetails, then filter WHERE
orderID IS NULL OR productID IS NULL.
15. List each order ID, product name, category name, and quantity ordered.
🔹 Hint: Join orders, orderDetails, products, and categories.

Happy Learning! May Allah Enhance your knowledge😊

Department of Computer Science, UMT, Lahore. 10 Riaz Ahmad