For each information request below, formulate a single SQL query to produce the required information. In each
Question:
For each information request below, formulate a single SQL query to produce the required information. In each case, you should display only the columns requested. Be sure that your queries do not produce duplicate records unless otherwise directed.
1. List the name and list price of products that were sold on a sale for which the shipping cost was over $60. (use two levels of subqueries in where clauses.)
2. List Employees (first and last names) and their hire dates of employees who were hired before their managers. (whose hire date is earlier than their managers hire date). Use a subquery in the where clause.
3. Count the number of unique values of the set of values of PurchaseDate and EmployeeID from the Purchase table. (Use a subquery in FROM) (name column NumbOfEmpPurch)
4. What is the total amount of purchases made in 2015 made by each employee? Include EmployeeID, First and Last names and total amount. (Use a join with a select subquery in the FROM clause. Remember to name the subquery.) Name the column TotalPurchases. When you create it, use both the round and sum functions, i.e. Round (Sum ( ... ) , 2).
Cornerstones of Financial and Managerial Accounting
ISBN: 978-1111879044
2nd edition
Authors: Rich, Jeff Jones, Dan Heitger, Maryanne Mowen, Don Hansen