Advanced SQL Assignment
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:
- Customers: Stores customer information.
- Orders: Records customer orders.
- Shippings: Tracks order shipments.
- Employees: Maintains employee and manager details.
Assignment Tasks:
1. Data Retrieval Using Joins:
a. Write a query to list all customers and their corresponding order details. Include customers who
haven't placed any orders.
b. Create a report showing all employees and their respective managers. Include employees who
don't have a manager.
2. Combining Queries:
a. Combine the results of customers who have either placed orders or have shipments, avoiding
duplicate entries. Include only their customer_id, first_name, and last_name.
3. Query Optimization:
a. Write an optimized query to find the total amount of orders placed by each customer. Display
the customer name and total amount.
4. Working with Hierarchical Data:
a. Write a query to create a hierarchy of employees, showing their name, position, and their
manager's name.
5. Full Outer Join and Exclusions:
a. Simulate a FULL OUTER JOIN between the Customers and Orders tables and retrieve all
records.
6. Advanced Challenge:
a. Write a single query to generate a customer report:
- Customer Name.
- Total Order Amount (NULL if no orders).
- Shipment Status (Pending, Delivered, or No Shipments if none).
- A column indicating if the customer is a High-Value Customer (total order amount > 10,000).
Submission Guidelines:
1. Save all queries in a .sql file with comments explaining the logic for each query.
2. Test your queries against the dataset and ensure they produce correct results.
3. Include screenshots of query outputs if possible.
