Description

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 be a single text-based SQL file 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 L04_ID#_LASTNAME.sql
Your submission needs to be commented and include the question number and the solutions.
Using comments to number the question answers, write the SQL code to complete the following tasks.
Example Submission
— ***********************
— Name: Your Name
— ID: #########
— Purpose: Lab 04 DBS311
— ***********************

— Q1 SOLUTION —
SELECT * FROM TABLE;

— Q2 Solution – SELECT * FROM TABLE;

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;
Marking Scheme
Question Weight
1 25%
2 25%
3 25%
4 25%

Grade Policy
 Submissions with errors do not get any marks.
o Execute your .sql file using the “Run Script” button to make sure there is no errors in your file.
 If your result in a question does not match the sample output results, you do not get any marks.
 You do not receive marks for the missing solutions.
Your submission needs to be commented and include the question, the solutions.
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.)