DBS3110 Assignment 1-Advanced SQL Solution

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_Group#.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.)
For each question, you have both query and the query result in your answer.
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 10 6 10
2 10 7 10
3 10 8 10
4 10 9 10
5 10 10 10

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 output columns given in that question.

The query returns 6 rows.
Include both the query and the result in your answer.
See the output columns:

EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE
----------- ---------- --------- ----------
2. Display manager ID for managers with more than one employee. Answer this question without using the COUNT()function.
Sort the result by manager ID. The query returns 15 rows.
Include both the query and the result in your answer.
See the output columns:
Manager ID
----------
3. Use the previous query and SET Operator(s) to display manager ID for managers who have only one employee.
Sort the result by manager ID.
You are not allowed to use COUNT(). The query returns 3 rows.
Include both the query and the result in your answer.
See the output columns:
Manager ID
----------

The query returns 6 rows.
Include both the query and the result in your answer. See the following output columns:
---------- ---------- ----------------
5. Write a query to display customer ID and customer name for customers who have purchased all these three products: Products with ID 7, 40, 94.
Sort the result by customer ID. The query returns 1 row.
Include both the query and the result in your answer.
CUSTOMER ID NAME
----------- ----
6. Write a query to display employee ID and the number of orders for employees with the maximum number of orders (sales).
Sort the result by employee ID.
The query returns one row.
Include both the query and the result in your answer. See the following output:
Employee ID Number of Orders
----------- ----------------
7. Write a query to display the month number, month name, year, total number of orders, and total sales amount for each month in 2017.
Sort the result according to month number.
Include both the query and the result in your answer.
The query returns 10 rows. See the output columns:
Month Number Month Year Total Number of Orders Sales Amount
------------ ----- ---- ---------------------- ------------
8. Write a query to display month number, month name, and average sales amount for months with the average sales amount greater than average sales amount in 2017.
Round the average amount to two decimal places.
Sort the result by the month number.
The query returns 5 rows.
Include both the query and the result in your answer. See the output columns:
Month Number Month Year Average Sales Amount
------------ ----- ---- --------------------
9. Write a query to display first names in EMPLOYEES that start with letter B but do not exist in CONTACTS.
Sort the result by first name.
The query returns 2 rows.
Include both the query and the result in your answer.
See the sample output.

First Name
-----------

10. Write a query to calculate the values corresponding to each line and generate the following output including the calculated values. Include your query and the query result in your answer.

The number of employees with total order amount over average order amount: ?
The number of employees with total number of orders greater than 10: ?
The number of employees with no order: ? The number of employees with orders: ?
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 order_id AS "order id", COUNT(item_id) AS "number of items"
FROM order_items
WHERE order_id < 5
GROUP BY order_id
ORDER BY order_id;
order id number of items
------------ ---------------------
1 13
2 9
3 8
4 8

Good luck.