Using MySQL: 4. Write a SELECT statement that returns these column names and dat

Question

Using MySQL:

4. Write a SELECT statement that returns these column names and data from the Products table:
product_name The product_name column
list_price The list_price column
discount_percent The discount_percent column
discount_amount A column that’s calculated from the previous two columns
discount_price A column that’s calculated from the previous three columns
Round the discount_amount and discount_price columns to 2 decimal places.
Sort the result set by discount price in descending sequence.
Use the LIMIT clause so the result set contains only the first 5 rows.

Explanation / Answer

SELECT product_name, list_price, discount_percent, discount_amount,
ROUND((list_price - discount_amount), 2) as discount_price
FROM
(SELECT product_name, list_price, discount_percent,
ROUND((discount_percent * list_price / 100), 2) as discount_amount
FROM Products) ORDER BY discount_price DESC LIMIT 5;

We are using nested query because we need to use a calculated column to calculate another column.

In the inner query, we're calculating the first calculated column which, we 'll use as a table for the outer query where we're calculating the second calculated column that is depending on the first calculated column. After this, we're applying for ORDER BY clause and then the LIMIT clause.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.