This week’s lab continues using the SELECT command in addition to now incorporating multiple tables and various set operators to produce results.
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 L04-lastname-firstname
For example: L04-King-Les.sql and L04-King-Les.output or L04-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
1. Display cities that no warehouse is located in them. (use set operators to answer this question)
2. Display the category ID, category name, and the number of products in category 1, 2, and 5. In your result, display first the number of products in category 5, then category 1 and then 2.
3. Display product ID for products whose quantity in the inventory is less than to 5. (You are not allowed to use JOIN for this question.)
4. We need a single report to display all warehouses and the state that they are located in and all states regardless of whether they have warehouses in them or not. (Use set operators in you answer.)
Example Submission
-- ***********************
-- Name: Your Name
-- ID: #########
-- Purpose: Lab 4 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;
