Description

Submission
Your submission will be a single text-based SQL file (.sql) with appropriate header and commenting. 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_ID1#_ID2#_—.sql. Only one submission per group please. Style Guide
Your SQL should be written using the standard coding style:
• all keywords are to be upper case,
• all user-defined names are to be lower case, (example: table and field names)
• there should be a carriage return before each major part of the SQL statements (i.e. before SELECT, FROM, WHERE and ORDER BY)
See the following sample:

SELECT columns
FROM tables
WHERE conditions
ORDER BY column1, column2; Submission Checklist
Use the following checklist, to make sure you have completed the assignment successfully.

Tasks to be completed Yes No
You have read the assignment group submission and completion policies and all instructions provided in the assignment document and have not missed a word.
Student information and the assignment information have been added to the header of the submission. (Same as the template provided in the assignment documents)
All questions are answered in a text file (a SQL worksheet) and are saved as a .sql file.
Comments are included. (questions definition or any additional explanation)
All SQL statements are executed successfully without errors. (Use “Run Script” to execute all statements together.)
The result of your SQL statements matches the given results in the assignment document.
Assignment Milestones
Milestone Week Questions
1 2 1,
2 3 2, 3, 4, 5, and 6
3 4 7 and 8
4 5 9 and 10
Group Work
This assignment is to be completed in groups of 3. Please only one submission per group. The comment header MUST have all students’ name and student number.
Assignment Marking Scheme
Question Weight Question Weight
1 5% 6 10%
2 5% 7 10%
3 10% 8 15%
4 10% 9 10%
5 10% 10 15%

VERY IMPORTANT:
Being part of a group is the same as being a part of a team for these assignments. When you submitted your work as part of a group, you are saying that:
• You understood what was submitted and that you fully participated with ALL the group members.
• It does not mean letting others do your work for you.
• It does not mean watching the others do the work.
• For your full participation, you get a mark equal to all the others in the group.
• If on the test, which is very much like the assignment, you cannot answer it strongly indicates that you did not participate and understand the assignment but depended on others for the mark you received. That is very much like submitting their work and claiming it is your work.
Tasks
For each question, the columns’ title and the format of the output result must match the sample result given in that question.
Milestone 1 – Week 2 (Question 1)

Milestone 2 – Week 3 (Question 2, 3, 4, 5, and 6)
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.

Milestone 3 – Week 4 (Question 7 and 8)

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.

Milestone 4 – Week 5 (Question 9 and 10)
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
— ***********************
— Student1 Name: Your Name Student1 ID: #########
— Student2 Name: Your Name Student2 ID: #########
— Student3 Name: Your Name Student3 ID: #########
— Purpose: Assignment 1 – DBS311
— ***********************

— Question 1 – write a brief note about what the question is asking
— Q1 SOLUTION —

SELECT * FROM TABLE1;

— Question 2 –
– write a brief note about what the question is asking
— Q2 SOLUTION —

SELECT * FROM TABLE2;