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)
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more