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.

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question

Write the following SQL Statements:

  1. 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.

  1. 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.

  1. 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

 

  1. 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

 

  1. 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.

 

  1. 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.

  1. 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.

  1. 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.

 

Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 7 steps with 3 images

Blurred answer
Knowledge Booster
Intermediate SQL concepts
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education