Advanced SQL Assignment with Answers
Scenario
You are hired as a data analyst for a company that manages customers, their orders, shipments,
and employee relationships. Your task is to use SQL to extract insights, optimize queries, and
analyze
hierarchical relationships using the following tables.
Dataset Definitions and Sample Data
1. Customers
| customer_id | first_name | last_name | age | country |
|-------------|------------|------------|-----|---------|
|1 | John | Doe | 31 | USA |
|2 | Robert | Luna | 22 | USA |
|3 | David | Robinson | 22 | UK |
|4 | John | Reinhardt | 25 | UK |
|5 | Betty | Doe | 28 | UAE |
2. Orders
| order_id | item | amount | customer_id |
|----------|------------|---------|-------------|
|1 | Keyboard | 400.00 | 4 |
|2 | Mouse | 300.00 | 4 |
|3 | Monitor | 12000.00 | 3 |
|4 | Keyboard | 400.00 | 1 |
|5 | Mousepad | 250.00 | 2 |
3. Shippings
| shipping_id | status | customer |
|-------------|------------|----------|
|1 | Pending |2 |
|2 | Pending |4 |
|3 | Delivered | 3 |
|4 | Pending |5 |
|5 | Delivered | 1 |
Assignment with Answers
1. Data Retrieval Using Joins
a. Query: List all customers and their corresponding order details. Include customers who haven't
placed any orders.
Answer:
SELECT c.customer_id, c.first_name, c.last_name, o.item, o.amount
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id;
b. Query: Create a report showing all employees and their respective managers. Include employees
who don't have a manager.
Answer:
SELECT e1.name AS employee, e2.name AS manager
FROM Employees e1
LEFT JOIN Employees e2
ON e1.manager_id = e2.employee_id;
2. Combining Queries
a. Query: Combine the results of customers who have either placed orders or have shipments,
avoiding duplicate entries.
Answer:
SELECT customer_id, first_name, last_name
FROM Customers
WHERE customer_id IN (
SELECT customer_id FROM Orders
UNION
SELECT customer FROM Shippings
);
3. Query Optimization
a. Query: Write an optimized query to find the total amount of orders placed by each customer.
Answer:
SELECT c.first_name, c.last_name, SUM(o.amount) AS total_amount
FROM Customers c
JOIN Orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;
4. Working with Hierarchical Data
a. Query: Create a hierarchy of employees, showing their name, position, and their manager's
name.
Answer:
WITH EmployeeHierarchy AS (
SELECT e1.name AS employee, e1.position, e2.name AS manager
FROM Employees e1
LEFT JOIN Employees e2
ON e1.manager_id = e2.employee_id
SELECT * FROM EmployeeHierarchy;
5. Full Outer Join and Exclusions
a. Query: Simulate a FULL OUTER JOIN between Customers and Orders.
Answer:
SELECT c.customer_id, c.first_name, c.last_name, o.item, o.amount
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
UNION
SELECT c.customer_id, c.first_name, c.last_name, o.item, o.amount
FROM Orders o
LEFT JOIN Customers c
ON c.customer_id = o.customer_id;
6. Advanced Challenge
a. Query: Generate a customer report.
Answer:
SELECT c.first_name, c.last_name,
SUM(o.amount) AS total_order_amount,
COALESCE(s.status, 'No Shipments') AS shipment_status,
CASE
WHEN SUM(o.amount) > 10000 THEN 'High-Value Customer'
ELSE 'Regular Customer'
END AS customer_type
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
LEFT JOIN Shippings s
ON c.customer_id = s.customer
GROUP BY c.customer_id, c.first_name, c.last_name, s.status;
