Based on the Dolphin Market database, write the SQL that would be required to answer the following
Question:
Based on the Dolphin Market database, write the SQL that would be required to answer the following questions. Provide the SQL and the number of rows returned or answer generated, depending on the question.
For how many different customers has each employee recorded orders? Your query should show the employee ID, the employee last name, and the number of different customers; only employees who have recorded orders are required in the output. Provide SQL and number of rows.
2. Which vendors have a contact whose title is some type of representative? Your query should show the vendor's company name, contact name, and contact title. Provide SQL and number of rows.
3. Which customers have placed less than three (3) orders? Your query should show the customer company names and the number of orders that were placed; customers who have placed orders aswell as customers who have not placed any orders are required in the output. Provide SQL and the number of rows.
4. Which customer(s) has placed the most orders? Your query should show the customer company name (or more if tied) and number of orders. Provide SQL and customer company name(s).
5. Which employees recorded orders either after May 10, 1998 and before May 16, 1998 or for customers who are from district WA? Your query should show the employee ID, employee last name, order date and customer district. Provide SQL and number of rows.
6. How many customers from each district have placed orders handled by each shipper? Your query should show the shipper company name, the customer district, and the number of customers from that district for each shipper used. If a shipper has not handled any orders, they should be included in the list as well. Provide SQL and number of rows.
7. Which employee(s) recorded the order(s) with the freight that costs the least? Your query should show the employee ID, the employee last name (or names if tied) and the freight cost. Provide SQL and name of the employee(s).
8. Which item(s) have been ordered the most in terms of total quantity ordered on all orders placed?Your query should show the item name (or names if tied) and the total quantity of that item ordered on all orders placed. Provide SQL and name of the item(s).
9. Which vendors provide more items than the average for all vendors (who provide items; do not include vendors who do not provide any items)? Your query should show the vendor company name and the number of items provided by the vendor. Provide SQL and number of rows.
10. Which employees have recorded more than the average number of orders recorded by all employees (who have recorded orders; do not include employees who have not recorded any orders)? Your query should show the employee id, employee last name, and the number of orders (over the average) that they have recorded. Provide SQL and number of rows.
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George