SQL Assignment: Mastering Joins with Financial Data
Objective:
This assignment will help you gain hands-on experience with SQL INNER JOIN, LEFT JOIN,
RIGHT JOIN, FULL JOIN, and CROSS JOIN while working with large financial datasets.
Step 1: Create Sample Data
1. Customers Table
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
City VARCHAR(50),
State VARCHAR(50),
Country VARCHAR(50),
Email VARCHAR(100),
Phone VARCHAR(15)
);
INSERT INTO Customers (CustomerID, CustomerName, City, State, Country,
Email, Phone) VALUES
(1, 'Alice Johnson', 'New York', 'NY', 'USA', '
[email protected]', '123-456-
7890'),
(2, 'Bob Smith', 'Chicago', 'IL', 'USA', '
[email protected]', '234-567-8901'),
(3, 'Charlie Brown', 'Los Angeles', 'CA', 'USA', '
[email protected]', '345-
678-9012'),
(4, 'David White', 'Houston', 'TX', 'USA', '
[email protected]', '456-789-
0123'),
(5, 'Eva Green', 'San Francisco', 'CA', 'USA', NULL, '567-890-1234'),
(6, 'Frank Black', 'Seattle', 'WA', 'USA', '
[email protected]', '678-901-
2345'),
(7, 'Grace Adams', 'Boston', 'MA', 'USA', '
[email protected]', '789-012-
3456'),
(8, 'Henry Clark', 'Miami', 'FL', 'USA', NULL, '890-123-4567'),
(9, 'Ivy Martinez', 'Dallas', 'TX', 'USA', '
[email protected]', '901-234-
5678'),
(10, 'Jack Wilson', 'Denver', 'CO', 'USA', '
[email protected]', '012-345-
6789');
2. Orders Table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
Amount DECIMAL(12,2),
Currency VARCHAR(5),
Status VARCHAR(20),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount, Currency,
Status) VALUES
(101, 1, '2023-01-15', 1500.00, 'USD', 'Completed'),
(102, 2, '2023-02-20', 2300.50, 'USD', 'Completed'),
(103, 3, '2023-03-05', 1750.00, 'USD', 'Pending'),
(104, 4, '2023-03-22', 2000.75, 'USD', 'Shipped'),
(105, 5, '2023-04-10', 3200.00, 'USD', 'Completed'),
(106, 6, '2023-05-18', 1800.00, 'USD', 'Cancelled'),
(107, 7, '2023-06-01', 2950.25, 'USD', 'Completed'),
(108, 8, '2023-06-15', 3100.50, 'USD', 'Pending'),
(109, 9, '2023-07-10', 4000.00, 'USD', 'Completed'),
(110, 10, '2023-08-05', 2750.75, 'USD', 'Shipped');
3. Payments Table
CREATE TABLE Payments (
PaymentID INT PRIMARY KEY,
OrderID INT,
PaymentDate DATE,
PaymentMethod VARCHAR(20),
Amount DECIMAL(12,2),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
INSERT INTO Payments (PaymentID, OrderID, PaymentDate, PaymentMethod,
Amount) VALUES
(201, 101, '2023-01-16', 'Credit Card', 1500.00),
(202, 102, '2023-02-21', 'PayPal', 2300.50),
(203, 105, '2023-04-12', 'Bank Transfer', 3200.00),
(204, 107, '2023-06-02', 'Credit Card', 2950.25),
(205, 109, '2023-07-11', 'Debit Card', 4000.00),
(206, 110, '2023-08-06', 'PayPal', 2750.75);
4. Products Table
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Category VARCHAR(50),
Price DECIMAL(10,2)
);
INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES
(301, 'Laptop', 'Electronics', 1200.00),
(302, 'Smartphone', 'Electronics', 800.00),
(303, 'Tablet', 'Electronics', 600.00),
(304, 'Headphones', 'Accessories', 150.00),
(305, 'Keyboard', 'Accessories', 100.00),
(306, 'Mouse', 'Accessories', 50.00),
(307, 'Smartwatch', 'Wearables', 300.00),
(308, 'Monitor', 'Electronics', 250.00),
(309, 'Printer', 'Office Supplies', 200.00),
(310, 'Desk Chair', 'Furniture', 180.00);
Step 2: Assignment Questions
Part 1: Basic Joins
1. Retrieve all orders along with the corresponding customer details.
2. Fetch all customers and their respective orders, ensuring even those without orders are
included.
3. Retrieve all payments along with their corresponding order details.
4. Fetch all customers and payments made by them, even if they haven't made any
payments.
Part 2: Complex Joins
5. Find customers who have placed orders but have not made any payments.
6. Retrieve a list of customers along with the total amount they have spent, even if they
haven't placed an order.
7. Find all orders that were canceled but still have a payment record.
8. List all customers along with their orders and the products they purchased (join
Customers, Orders, and Products).
9. Retrieve orders with their payment details, ensuring even unpaid orders appear in the
result.
Part 3: Advanced Queries
10. Find the total amount spent by each customer using JOINs.
11. List all customers who have made multiple payments.
12. Identify the top 3 highest-paying customers.
13. Retrieve orders with missing customer information.
14. Identify customers who have placed at least 3 orders.
15. Find customers who live in the same city and have placed an order in 2023.
16. List customers who have placed an order but have NULL email addresses.
Part 4: Special Cases
17. Retrieve the most expensive order made by each customer.
18. Identify the most popular payment method based on the number of transactions.
19. Find orders that have been paid using multiple payment methods.
20. Retrieve customers who have placed an order but have no valid contact information
(NULL email and phone).
21. Identify cities where no customers have placed an order.
22. Retrieve orders where the payment amount does not match the order amount.
23. Find customers whose orders are still in "Pending" status.
24. Identify customers who placed an order in 2023 but have never made a payment.
25. Find all customers who have placed more than 5 orders in the last 2 years.
Submission Guidelines
• Write SQL queries for all the above questions.
• Provide query outputs along with explanations.
• Ensure optimized queries for large datasets.
