Average time to complete: 4-6 hours
Create queries that answer the questions below using the database from exercise 1. As in exercise 1, use a text editor to save each query in a separate file named q1.sql, q2.sql, etc.
Create a report with just a cover page and academic integrity statement (no queries, analysis, or results). Use the procedure from Exercise 1 to submit your answers and review the autograder results. Use the GitHub Submission Repo link on this page to submit. As in Exercise 1, there is no deduction for up to 3 submissions.
See tips below.
List the order number, date, employee number, and employee name (first and last, in a column named empname) for orders placed on January 23, 2015. Be sure to include orders that have no associated employee (employee name and number should be NULL for those orders). Order by order number.
List the customer number and first and last name (in a column named custname) of Washington customers who have not placed orders during February 1-14, 2015, sorted by customer last name. Do not use the word EXISTS in your solution.
Solve #2 using a second technique. Use the word EXISTS in your solution.
List the employee number and name (first and last, in a column named empname) of employees who have not taken orders for products manufactured by Connex, sorted by employee number. Do not use the word EXISTS in your solution.
Solve #4 using a second technique. Use the word EXISTS in your solution.
Display a list of employees ordered by employee number with the following info for each employee: employee number, full name (as empname), supervisor name (as supname; this should be NULL if the employee has no supervisor), commission rate, and commission category. The commission category is a column named EmpCommCategory that is computed as follows: For employee commission rates from 0 to .03 (inclusive), the category is ‘STANDARD’; for rates from .03 (exclusive) to .04 (inclusive), the category is ‘SILVER’; for rates from .04 (exclusive) and up, the category is ‘GOLD’. The category should be NULL if the rate is NULL. (Tip: Review the lecture notes on Conditional Logic.)
Display a list of all customers and employees. The first column should contain an identifying number (either customer number or employee number, named id), and the second column should contain the full name of the customer or employee, and be named name.
When using a nested query in a NOT IN constraint, if you are not getting the desired results, test the nested query by itself as a standalone query. If it produces NULLs, that will interfere with correct NOT IN processing. To correct the problem, revise the nested query so that it does not include NULLs.