Lab 02 – Multi-row Functions

DBS311 – Advanced Database Services 
Lab 02 – Multi-row Functions

This week’s lab continues using the SELECT command and learning the interfaces for both SQL
Developer and introduces the use of multi-row functions.
Submission
Your submission will consist of two files:
(a) A single text-based SQL file with appropriate header and commenting – and
(b) An output file demonstrating that your queries work
Please ensure your SQL file runs when the entire file is executed.
Your file names should be L02-lastname-firstname
For example: L02-King-Les.sql and L02-King-Les.output or L02-King-Les.pdf, etc.
Your submission needs to be commented. At a minimum, your comments before each SQL
statement should include the complete question below you are answering.
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;
To save time, you can write all SQL statement in your SQL developer. To make sure that your SQL
statements style follows the standard SQL style guide, copy and paste your SQL statement onto the
following website and click on “FORMAT SQL” or “FORMAT SQL IN NEW WINDOW”.
https://www.freeformatter.com/sql-formatter.html#ad-output
You can also upload your SQL file. See the setting in the following image. Have SQL keywords (SELECT,
INSERT, UPDATE, etc.) uppercase and user defined objects and identifiers (tables, columns, etc.)
lowercase.
DBS311 – Advanced Database Services Summer 2020
Tasks
For each question, the title of columns and the output result must match the provided
output result in that question.
1. For each job title display the number of employees. Sort the result according to the number
of employees.
2. Display the highest, lowest, and average customer credit limits. Name these results high,
low, and average. Add a column that shows the difference between the highest and the
lowest credit limits named “High and Low Difference”. Round the average to 2 decimal
places.
DBS311 – Advanced Database Services Summer 2020
3. Display the order id, the total number of products, and the total order amount for orders
with the total amount over $1,000,000. Sort the result based on total amount from the high
to low values.
4. Display the warehouse id, warehouse name, and the total number of products for each
warehouse. Sort the result according to the warehouse ID.
5. For each customer display customer number, customer full name, and the total number of
orders issued by the customer.
▪ If the customer does not have any orders, the result shows 0.
▪ Display only customers whose customer name starts with ‘O’ and contains ‘e’.
▪ Include also customers whose customer name ends with ‘t’.
▪ Show the customers with highest number of orders first.
DBS311 – Advanced Database Services Summer 2020
6. Write a SQL query to show the total and the average sale amount for each category. Round
the average to 2 decimal places.
Example Submission
-- ***********************
-- Name: Your Name
-- ID: #########
-- Date: The current date
-- Purpose: Lab 2 DBS311
-- ***********************
-- Question 1 – Copy the question from above here
-- Q1 SOLUTION --
SELECT * FROM TABLE;
-- Question 2 – Copy the question from above here
-- Q2 Solution –
SELECT * FROM TABLE;