AD 599
Business Analysis
Assignment 4
The Northwind database (Provided on Blackboard) is a classic sample database developed by Microsoft. It simulates the operations of a fictional company, Northwind Traders, which imports and exports specialty foods worldwide. The database encompasses various aspects of business operations, including customers, orders, inventory, suppliers, shipping, and employees, providing a comprehensive schema for learning and practicing SQL queries.
Write SQL queries in SQLite Studio and obtain the expected results for the following questions. Paste your queries and results in this Word document under each question and submit the Word document.
1. Retrieve a Unique List of Customers and Suppliers (1 point)
Task: Generate a unique list of entities from both the Customers and Suppliers tables, displaying the company name, contact name, and city. Sort the results by company name in descending order.
2. Calculate Freight Statistics by Ship Country (1 points)
Task: For each ShipCountry, compute the total, average, minimum, and maximum freight charges. Display the results ordered by the maximum freight in descending order.
3. Determine Total Sales Amount per Product (1 points)
Task: For each product, calculate the total sales amount, accounting for discounts, and order the results by the highest sales amount
4. Identify Products with Above-Average Prices (2 points)
Task: List products that have a unit price above the average unit price of all products. Display the product name and unit price.
5. List Employees and Their Supervisors (2 points)
Task: Create a report displaying each employee alongside their supervisor. Include the employee's ID, name, supervisor's ID, and supervisor's name.