Reference no: EM13780715
Write SQL statements that will retrieve the following data from a database, using Subqueries and Joins. Using the Northwind database, write a SQL SELECT statement that will retrieve the data for the following questions
For each order, display the Company Name, City, and the Country for the customer who placed the order.
- Include the Order Date and the Required Date. Order the results by Company Name in ascending order.
- 830 rows returned.
For each order, list the OrderID, Orderdate, Product Name, UnitPrice, Quantity Ordered, and Total Cost. Label the column 'Product Cost.'
- For each product (including the discount), only show those orders that were placed in March of 1997.
- Sort the data by the OrderID, then the Product Name.
- 77 rows returned.
For each order, display the OrderID, ShipName, and the Employee First and Last Name.
- Order the results by Employee Last Name in descending order, then by Employee First Name in ascending Order, then by OrderID in ascending order.
- 830 rows returned.
Modify the first query to list the orders with customer details, but include all customers even if they have not placed an order.
832 rows returned.
- List all customers (include CustomerId and Company Name) who have placed less than 5 orders.
- Include those customers who have placed 0 orders. Name the count field "OrderCount."
- Order by number of placed orders in reverse order.
- 18 rows returned.
Display the ProductName, and UnitPrice of all products that have a unit price larger than Tarte au Sucre.
- Order the results by UnitPrice in descending order.
- 7 rows returned.
Display the Customer Name of all customers who have placed orders in 1996.
- Order the results by Company Name in ascending order.
- 67 rows returned.
Display the OrderID of all orders that where placed after all orders placed by Bottom-Dollar Markets.
- Order the result by OrderID in ascending order.
- 28 rows returned.
List the Company Name of all U.S.-based customers who are NOT located in the same state (or region) as any of the employees.
- Order the results by Company Name.
- 10 rows returned.
Display the Product Names of all products that were placed by customers in CA.
- Order the result by Product Name in ascending order.
- Eliminate duplicate rows in the results.
- 10 Rows Returned.
Query #1
For each order, display the Company Name, City, and the Country for the customer who placed the order.
• Include the Order Date and the Required Date.
• Order the results by Company Name in ascending order.
Hints & Requirements:
• Inner Join on CustomerID from [Orders] and [Customers]. The order of the ON statement will change results
• 830 rows returned.
Query #2
For each order,
• List the OrderID, Orderdate, Product Name, UnitPrice, Quantity Ordered, and Total Cost. Label the column 'Product Cost.'
• For each product (including the discount), only show those orders that were placed in March of 1997.
• Sort the data by the OrderID, then the Product Name.
Hints & Requirements:
• Join on orderid from [Orders] and [Order Details] tables.
• Join on productID from [Products] and [Order Details] tables.
• ‘Product cost' is a calculated field which includes *(1.0-discount)
• 77 rows returned
Query #3
For each order:
• Display the OrderID, ShipName, and the Employee First and Last Name.
• Order the results by:
o Employee Last Name in descending order, then by
o Employee First Name in ascending order, then by
o OrderID in ascending order
Hints & Requirements:
• Join on EmployeeID from [Orders] and [Employees] tables.
• 830 rows returned.
Query #4
Modify the first query to list the orders with customer details, but include all customers even if they have not placed an order.
Hints & Requirements:
• Left outer join on CustomerID field from [Customers] and [Orders] tables.
• 832 rows returned.
Query #5
List all customers (include CustomerId and Company Name) who have placed less than 5 orders.
• Include those customers who have placed 0 orders.
• Name the count field "OrderCount."
• Order by number of placed orders (ie OrderCount) in reverse order.
Hints & Requirements:
• Left outer join on customerid field from customers and orders table. Must use count(orderid) in query, and later in the SQL statement. Use a GROUP BY and HAVING.
• 18 rows returned
Query #6
Display the ProductName, and UnitPrice of all products that have a unit price larger than ‘Tarte au Sucre'.
• Order the results by UnitPrice in descending order.
Hints & Requirements:
• Two WHERE clauses required. Sub query required in the first WHERE clause
• 7 rows returned.
Query #7
Display the Customer Name of all customers who have placed orders in 1996.
• Order the results by Company Name in ascending order.
Hints & Requirements:
• Join CUSTOMERS and ORDERS.
• Use the keyword DISTINCT.
• 67 rows returned
Query #8
Display the OrderID of all orders that where placed after all orders placed by "Bottom-Dollar Markets".
• Order the result by OrderID in ascending order.
Hints & Requirements:
• Three WHERE clauses required for this query.
o First WHERE clause checks for OrderDate and uses a sub query with ALL keyword.
o Second WHERE clause use equals and sub query.
o Third WHERE clause uses equal and company name.
• 28 rows returned.
Query #9
List the Company Name of all U.S.-based customers who are NOT located in the same state (or region) as any of the employees.
• Order the results by Company Name.
Hints & Requirements:
• Use the CUSTOMERS and EMPLOYEES tables. Use two WHERE clauses. First WHERE clause uses reverse(NOT) set notation and sub-query. Second WHERE clause is a compound statement testing country and null.
• 10 rows returned
Query #10
Display the Product Names of all products that were placed by customers in CA.
• Order the result by Product Name in ascending order.
• Eliminate duplicate rows in the results.
Hints & Requirements:
• Join Customer, Orders and [Order Details]
• Use the keyword: DISTINCT (look it up)
• 10 rows returned