DBS3110 Assignment 1 Solution

Submission
Your submission will be a single text-based SQL file (.sql) – and – an output file showing the result set when queries are executed. Your .sql file needs to contain appropriate header and commenting. Your output file needs to contain appropriate header. Please ensure your file runs when the entire file is executed in SQL Developer.
Create a new Worksheet in SQL Developer. Save the file as:
A1-<lastname>-<firstname>.sql

Your output file should be called:
A1-<lastname>-<firstname>-output

Make sure that you have fresh tables with the exact correct data within them. This is essential to achieving the correct result sets outlined below.

Assignment Marking Scheme

Question Weight Question Weight
1 10% 6 10%
2 10% 7 10%
3 10% 8 10%
4 10% 9 10%
5 10% 10 10%

Tasks
For each question, the columns’ title and the format of the output result must match the sample result given in that question.



2. The company wants to see the total sale amount per sales person (salesman) for all orders. Assume that online orders do not have any sales representative. For online orders (orders with no salesman ID), consider the salesman ID as 0. Display the salesman ID and the total sale amount for each employee.
Sort the result according to employee number.


3. Display customer Id, customer name and total number of orders for customers that the value of their customer ID is in values from 35 to 45. Include the customers with no orders in your report if their customer ID falls in the range 35 and 45. Sort the result by the value of total orders.


a. Show also the total quantity and the total amount of each customer’s order.
b. Sort the result from the highest to lowest total order amount.

5. Display customer Id, name, total number of orders, the total number of items ordered, and the total order amount for customers who have more than 30 orders. Sort the result based on the total number of orders.


6. Display Warehouse Id, warehouse name, product category Id, product category name, and the lowest product standard cost for this combination.
• In your result, include the rows that the lowest standard cost is less then $200.
• Also, include the rows that the lowest cost is more than $500.
• Sort the output according to Warehouse Id, warehouse name and then product category Id, and product category name.




8. Display product Id, product name for products that their list price is more than any highest product standard cost per warehouse outside Americas regions. (You need to find the highest standard cost for each warehouse that is located outside the Americas regions. Then you need to return all products that their list
price is higher than any highest standard cost of those warehouses.) Sort the result according to list price from highest value to the lowest.


9. Write a SQL statement to display the most expensive and the cheapest product (list price). Display product ID, product name, and the list price.


10. Write a SQL query to display the number of customers with total order amount over the average amount of all orders, the number of customers with total order amount under the average amount of all orders, number of customers with no orders, and the total number of customers.
See the format of the following result.


Example Submission
-- ***********************
-- Student Name: Your Name
-- Student1 ID: #########
-- Purpose: Assignment 1 - DBS311
-- ***********************

-- Question 1 – Copy the question from above (excluding result set) -- Q1 SOLUTION --


SELECT * FROM TABLE1;

-- Question 2 – Copy the question from above (excluding result set) -- Q2 SOLUTION --

SELECT * FROM TABLE2;