Lab 03 – Sub-Queries

Lab 03 – Sub-Queries
This week’s lab continues using the SELECT command and learning the interfaces for both SQL Developer and
introduces the use of sub-queries.
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 L03-lastname-firstname
For example: L03-King-Les.sql and L03-King-Les.output or L03-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;
Tasks
For each question, the title of columns and the output result must match the provided output result in that
question.
1. Write a SQL query to display the last name and hire date of all employees who were hired before the
employee with ID 107 got hired but after March 2016. Sort the result by the hire date and then
employee ID.
2. Write a SQL query to display customer name and credit limit for customers with lowest credit limit. Sort
the result by customer ID.
3. Write a SQL query to display the product ID, product name, and list price of the highest paid product(s)
in each category. Sort by category ID and the product ID.
4. Write a SQL query to display the category ID and the category name of the most expensive (highest list
price) product(s).
5. Write a SQL query to display product name and list price for products in category 1 which have the list
price less than the lowest list price in ANY category. Sort the output by top list prices first and then by
the product ID.
6. Display the maximum price (list price) of the category(s) that has the lowest price product.
Example Submission
-- ***********************
-- Name: Your Name
-- ID: #########
-- Date: The current date
-- Purpose: Lab 3 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;