Write the following SQL Statements: Write a SELECT statement that returns one row for each customer that has orders with these columns: The email_address from the Customers table A count of the number of orders The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.) Return only those rows where the customer has more than 1 order. Sort the result set in descending sequence by the sum of the line item amounts. Write a SELECT statement that answers this question: What is the total amount ordered for each product? Return these columns: The product name from the Products table The total amount for each product in the Order_Items (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity) Use the ROLLUP operator to include a row that gives the grand total. Write a SELECT statement that answers this question: Which customers have ordered more than one product? Return these columns: The email address from the Customers table The count of distinct products from the customer’s orders Write a SELECT statement that returns the same result set as this SELECT statement, but don’t use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword. SELECT DISTINCT category_name FROM categories c JOIN products p ON c.category_id = p.category_id ORDER BY category_name Write a SELECT statement that answers this question: Which products have a list price that’s greater than the average list price for all products? Return the product_name and list_price columns for each product. Sort the results by the list_price column in descending sequence. Write a SELECT statement that returns the category_name 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 subquery introduced with the NOT EXISTS operator. Write a SELECT statement that returns the name and discount percent of each product that has a unique discount percent. In other words, don’t include products that have the same discount percent as another product. Sort the results by the product_name column. Use a correlated subquery to return one row per customer, representing the customer’s oldest order (the one with the earliest date). Each row should include these three columns: email_address, order_id, and order_date.
Write the following SQL Statements:
- Write a SELECT statement that returns one row for each customer that has orders with these columns:
The email_address from the Customers table
A count of the number of orders
The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)
Return only those rows where the customer has more than 1 order.
Sort the result set in descending sequence by the sum of the line item amounts.
- Write a SELECT statement that answers this question: What is the total amount ordered for each product? Return these columns:
The product name from the Products table
The total amount for each product in the Order_Items (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity)
Use the ROLLUP operator to include a row that gives the grand total.
- Write a SELECT statement that answers this question: Which customers have ordered more than one product? Return these columns:
The email address from the Customers table
The count of distinct products from the customer’s orders
- Write a SELECT statement that returns the same result set as this SELECT statement, but don’t use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword.
SELECT DISTINCT category_name
FROM categories c JOIN products p
ON c.category_id = p.category_id
ORDER BY category_name
- Write a SELECT statement that answers this question: Which products have a list price that’s greater than the average list price for all products?
Return the product_name and list_price columns for each product.
Sort the results by the list_price column in descending sequence.
- Write a SELECT statement that returns the category_name 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 subquery introduced with the NOT EXISTS operator.
- Write a SELECT statement that returns the name and discount percent of each product that has a unique discount percent. In other words, don’t include products that have the same discount percent as another product.
Sort the results by the product_name column.
- Use a correlated subquery to return one row per customer, representing the customer’s oldest order (the one with the earliest date). Each row should include these three columns: email_address, order_id, and order_date.
Trending now
This is a popular solution!
Step by step
Solved in 7 steps with 3 images