I need these 6 queries completed. Thank you.

/* Query 1.

Write a SELECT statement that returns the same result set as the SELECT statement below, but dont use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword.

SELECT DISTINCT CategoryName

FROM Categories c JOIN Products p

ON c.CategoryID = p.CategoryID

ORDER BY CategoryName;

*/

/* Query 2.

Write a SELECT statement that answers this question: Which products have a list price thats greater than the average list price for all products? Return the

ProductName and ListPrice columns for each product. Sort the results by the ListPrice column in descending sequence.

Hint: two rows

*/

/* Query 3.

Write a SELECT statement that returns the CategoryName column from the Categories table. Return one row for each category that has NEVER been assigned to any product in the Products table. To do that, use a correlated subquery introduced with the NOT EXISTS operator.

Hint: one row

*/

/* Query 4.

This query requires you to write two SELECT statements described below.

Write a SELECT statement that returns three columns: EmailAddress, OrderID, and the order total for each customer. To do this, you can group the result set by the EmailAddress and OrderID columns; otherwise, there are many customers, each has many orders and each order contains many items. In addition, you must calculate the order total from the columns in the OrderItems table. Sort the results by the EmailAddress column in ascending sequence so you can see easily customers with multiple orders.

Hint 1: an order's total is the sum of (ItemPrice - DiscountAmount) * Quantity of all items of the order.

Hint 2: 41 rows

Hint 3: a few customers have multiple orders like

david.goldstein@hotmail.com, which has

three orders with ID 5, 9, and 29.

Write a second SELECT statement that uses the first SELECT statement in its FROM clause.

Because ORDER BY is not allowed in a subquery, you must remove it from the first SELECT statement.

The main query (i.e., the outer SELECT statement) should return

two columns:

the customers email address and MaxOrderTotal for the largest order for that customer.

To do this, you need to aggregate or group the result set of the subquery by its EmailAddress column. Sort the results by the MaxOrderTotal column in ascending sequence so you can see easily who has the smallest MaxOrderTotal.

Hint 1: MAX

Hint 2: 35 rows

Hint 3: "erinv" has the smallest MaxOrderTotal

*/

/* Query 5.

A few products in [MyGuitarShop] database have the same discount percent as others. Write a SELECT statement that returns the name and discount percent of each product

that has a unique discount percent. In other words, dont include products that have the same discount percent as another product. Sort the results by the ProductName column in ascending sequence.

Hint 1: a subquery that uses GROUP BY and HAVING

Hint 2: 6 rows

*/

/* Query 6.

The Orders table in [MyGuitarShop] database contains orders of many customers. If we know the CustomerID of a specific customer, say, 432, we can easily find this customer's orders by a WHERE clause with a condition 'CustomerID = 432'. When 432's orders are found, we can ask any query, for example, her newest order by using MAX(OrderDate) in the SELECT clause. The statement would look like

SELECT OrderID, MAX(OrderDate)

FROM Orders

WHERE CustomerID = 432;

The challenge is, what if we want EVERY customers newest order, not just a specific customer's? And the output contains EmailAddress, OrderID, and OrderDate of the newest order. If every customer has only one order, then this becomes a trivial query because the output is nothing but contains every customer's only order.

Unfortunately, this is not the case in most business.

To solve this challenging query, firstly you need a join to get all three output columns.

Secondly, you may be thinking to put MAX(OrderDate) into the SELECT clause as shown above.

Unfortunately, if you do so, the MAX() applies to all rows of the join result (review p.162~3 of Chapter 5 to know why), meaning you only get one row returned, that is whichever

customer's order that has the newest date compared to every order of every customer. In other words, you won't get the order with the newest date of EVERY customer.

Because the join result contains every customer's orders, to solve the above challenging query, you are supposed to compare the order date of orders that belong to the same customer, not orders of everybody. This should be a clear enough hint to you, meaning if you add a WHERE clause to filter rows of the join result, you can pass the row's CustomerID to a subquery, which can use the CustomerID to search Orders table to find his/her newest order.

The subquery will very similar to the SELECT statement shown above except 432 is replaced with the CustomerID passed from the main query. Because the WHERE will examine one row a time, if the row's order date does not match the newest date returned by the subquery, that row is discarded and will not be passes to the SELECT clause for output.

If you don't understand this process, please read the above two paragraphs.

Once the main query and subquery are drafted down, you only need to tie them up by matching with order date. If you make all correctly, you are using a correlated subquery to return one row per customer, representing the customers newest order.

Each row in the output should include these three columns: EmailAddress, OrderID, and OrderDate. Sort the results by the EmailAddress column in ascending sequence.

Hint: 35 rows, including the newest order of david.goldstein@hotmail.com, who has three orders and the newest one should be order 29.

*/