Question

Which of the following SQL query will filter products having cost more than 500?

• A. SELECT ProductName FROM PRODUCTS WHERE Cost$>$500;
• B. SELECT ProductName FROM PRODUCTS GROUP BY Cost$>$500;
• C. SELECT ProductName FROM PRODUCTS HAVING Cost$>$500;
• D. SELECT DISTINCT ProductName FROM PRODUCTS HAVING Cost$>$500;

Hint:

Always use WHERE for conditions on individual records.
Always use HAVING for conditions on aggregated groups!
WHERE comes before GROUP BY, while HAVING comes after GROUP BY!

Answer 1

The Correct Option is A

Step 1: Understanding the Question:
The question asks to identify the correct SQL query syntax to retrieve the names of individual products whose cost is strictly greater than 500.

Step 2: Syntactic Rules for Row Filtering:
- To filter individual database records based on a specific attribute value (like Cost > 500), we must use the WHERE clause.
- The HAVING clause is reserved for filtering aggregate groupings (often combined with a GROUP BY clause). It should not be used for individual row evaluation.

Step 3: Detailed Explanation:
- In the given requirement, "cost more than 500" is a simple condition on a single row's column value. There is no aggregation (such as average, sum, or count of the cost) requested.
- Therefore, the WHERE clause must be used.
- Let us evaluate the options:
- Option (A) SELECT ProductName FROM PRODUCTS WHERE Cost>500; is syntactically perfect. It queries the ProductName column from the PRODUCTS table and uses WHERE Cost>500 to filter out any products cheaper than or equal to 500.
- Option (B) is incorrect because it tries to group by a boolean condition.
- Options (C) and (D) are incorrect because they use the HAVING clause to perform a single-row filter on a non-aggregated attribute without a GROUP BY clause.

Step 4: Final Answer:
The standard SQL query to perform this operation is represented in Option (A).
Hence, option (A) is the correct choice.