Assignment 1 Single text-based SQL file (.sql)

Assignment 1
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.
1. Display the employee number, full employee name, job title, and hire date of all
employees hired in September with the most recently hired employees displayed
first.
2 | P a g e
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.
4. Display customer ID, customer name, and the order ID and the order date of all
orders for customer whose ID is 44.
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.
3 | P a g e
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.
7. Display the total number of orders per month. Sort the result from January to
December.
4 | P a g e
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: #########
-- Date: The date of assignment completion
-- Purpose: Assignment 1 - DBS311
-- ***********************
-- Question 1 – Copy the question from above (excluding result set)
-- Q1 SOLUTION --
5 | P a g e
SELECT * FROM TABLE1;
-- Question 2 – Copy the question from above (excluding result set)
-- Q2 SOLUTION --
SELECT * FROM TABLE2;