Query: For each customer, show the number of orders placed, and the largest, smallest and total freight ordered. Only show those customers whose total freight is greater than $1000.
Query: Display all product columns for products that are red and black and have a list price greater than or equal to $400 and less than or equal to $800. Use the keywords BETWEEN and IN.
Query: Rewrite query #9 to remove the use of BETWEEN and IN, and replace with the equivalent use of =, <, >, /= and AND and OR expressions.
Write SQL statements that will retrieve the following data from a database, using Subqueries and Joins. Using the AdventureWorksLT2012 database, write a SQL SELECT statement that will retrieve the data for the following questions:
Query 1: For each order, display the Customer's Company Name, Phone, and the Email for the customer who placed the order. Include the Sales Order ID, Order Date and the Due Date. Sort the results by Company Name in ascending order.
Query 2: For each order, list the Sales Order ID, Order Date, Product Name, sales order Unit Price, Quantity Ordered. Sort the data by the Sales Order ID and then the Product Name.
Query 3: Modify the first query to list the same fields, but include all customers even if they have not placed an order.
Query 4: List all product categories (including product categoryID and name), the number of products in each category as well as the average list price of the products in that category. Display those categories that have 5 products or less including categories that have no products assigned to it. Name the count column ‘ProductCount' and the average column ‘Average List Price'. Sort the results by the product count in descending order.
Query 5: Display the Product Name, and List Price of all products that have a list price larger than the Cable Lock product. Sort the results by List Price in descending order. Note: use of a subquery is required.
Query 6: Display the Customer Name of all customers who have placed orders between May 2004 and September 2004. Sort the results by Company Name in ascending order. The name should appear once on the list.
Query 7: Display the ProductID, name and weight of all products that weigh less than ‘Front Brakes'. Sort by the results by weight in descending order. Use a subquery.
Query 8: List the Product Name and ProductID for all products that have not sold. Use a sub query.
Query 9: Display the Customer ID and Customer Company Name for all customer's shipping addresses in the United States.
Query 10: Display the Sales OrderID and Total Due for all orders where the Total Due is larger than the average Total Due for all orders.