IT452 Unit 4 Outer Joins and Subqueries

IT452 Unit 4 Outer Joins and Subqueries

a) List all order ID’s for the customer named ‘Customer LOLJO’. You must use a subquery.

b) List the lastname and firstname of all employees who have sold to customer ‘Customer LOLJO’. Use a nested subquery (no joins).

c) List the empid, lastname, and firstname of all employees who are managers. (If an employee is a manager, his/her empid will appear as someone else’s mgrid.) Use a subquery. (HINT: The subquery will list all the distinct mgrid values.)

d) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Write an uncorrelated subquery that uses NOT IN.

c) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Write a correlated subquery that uses NOT EXISTS.

e) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Use an outer join.
(HINT: First, write an inner join that returns all the (distinct) employees who *did* take an order during that month. Next, change WHERE to AND so that the predicate is part of the ON clause of the join. Finally, change your join to an outer join, using the WHERE clause to filter for only non-matching rows.)

f) Write a subquery that returns the first order placed in the orders table

g) Using a scalar subquery (returns only 1 row) to outer query, write a query to returns the customer id for orders placed by the sales rep Judy Lew

h) Select the EmployeeID, lastname, firstname for the employee that had sales but does NOT have a manager. (requires Inner Join and subquery)

i) Find the customer, shipcountry and number of orders that the customer made where the customer is not located in the United States. (Requires a Count, subquery and condition)