Consider the following relation schema: Product(id, name, price) What will be the output of the following SQL code? SELECT max(price) FROM Product WHERE price < (SELECT max(price) FROM Product);

Consider the following relation schema: Product(id, name, price) What will be the output of the following SQL code? SELECT max(price) FROM Product WHERE price < (SELECT max(price) FROM Product); Correct Answer Second highest price of a product

The correct answer is option 1.

Concept:

Order or SQL query evaluation:

1. From: Cartesian product of tables.
2. Where: Select the rows.
3. Group by: Divide the rows into groups.
4. Having: Select the groups.
5. Expressions: Expressions In select are evaluated.
6. Distinct: Distinct in select (Eliminate the duplicates).
7. Ser-Operations: (Union, intersect, Except)
8. Order By: Sort the rows.

Relation schema:

Product(id, name, price)

Query:

SELECT max(price)

FROM Product

WHERE price < (SELECT max(price) FROM Product);

Step 1:

Choose the product table from the database.

Step 2:

Go to the conditions in where clause, price < (SELECT max(price) FROM Product);

Step 3:

Execute the inner query, and select the product table from the database.

Step 4:

Evaluate the expressions, and select the max price from the database. And gives output to the outer query.

Asper example max price is 120.

Step 5:

The outer query compares the next max value(second max value) of the price with the output inner query. 

The output of the query second-highest price of a product satisfies these conditions (max price < inner query max price).

As per the example, it compares like,

Max value of price in the outer query, 120<120 is false.

The next max value of price in the outer query 105<120 is True. Here 105 is the second-highest price of the product.

Hence the correct answer is the second-highest price of a product.