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.
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: #########
-- 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;
