Advanced SQL Assignment 1

DBS311 – Advanced Data Services Last update
Advanced SQL
Assignment 1
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.
DBS311 – Advanced Data Services Last update: Fall 2022
2 | Page
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.
It is suggested that you ALL do it individually and then meet to compare answers. Those
not doing the work may be barred from your group resulting in a zero and incomplete on
the assignment.
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.
1. Write a query to display employee ID, first name, last name, and hire date for employees
who have been hired after the last employee hired in August 2016 but two months before
the first employee hired in December 2016.
Sort the result by hire date and employee ID.
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
----------- ---------- --------- ----------
DBS311 – Advanced Data Services Last update: Fall 2022
3 | Page
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
----------

4. Write a SQL query to display products that have been ordered multiple times in one day
in 2016.
Display product ID, order date, and the number of times the product has been ordered on
that day.
Sort the result by order date and product ID.
The query returns 6 rows.
Include both the query and the result in your answer.
See the following output columns:
Product ID Order Date Number of orders
---------- ---------- ----------------
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
----------- ----
DBS311 – Advanced Data Services Last update: Fall 2022
4 | Page
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: ?
DBS311 – Advanced Data Services Last update: Fall 2022
5 | Page
Example Submission
-- ***********************
-- Student1 Name: Your Name Student1 ID: #########
-- Student2 Name: Your Name Student2 ID: #########
-- Student3 Name: Your Name Student3 ID: #########
-- Date: The date of assignment completion
-- 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.