Lab 4 – Set Operators

DBS311 
1 | P a g e
Lab 4 – Set Operators
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)
DBS311 Summer 2020
2 | P a g e
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.)
DBS311 Summer 2020
3 | P a g e
Example Submission
-- ***********************
-- Name: Your Name
-- ID: #########
-- Date: The current date
-- 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;
DBS311 Summer 2020
4 | P a g e