Database Basics
A product table has five rows of (name, price): (pen, 120), (notebook, 250), (eraser, 80), (file, 300), (ruler, 150). Which shows, in order, the rows returned by SELECT name FROM product WHERE price >= 150 ORDER BY price DESC?
- Aruler, notebook, file
- Bfile, notebook
- Cfile, notebook, ruler
Show answer and explanation
Answer: C. file, notebook, ruler
WHERE price >= 150 selects only rows whose price is 150 or more. The ruler, at exactly 150, is included, so three rows are selected: notebook (250), file (300) and ruler (150). ORDER BY price DESC then sorts them from the highest price (descending order), giving file (300), notebook (250), ruler (150). Specifying ASC, or nothing, with ORDER BY sorts from the lowest (ascending order). Without ORDER BY, the order of the result rows is not guaranteed. After SELECT you write the columns to retrieve (here name), after FROM the table, and after WHERE the condition that selects rows.
- A. This is sorted from the lowest price (ascending), the opposite of DESC.
- B. This mistakes >= for > and leaves out the ruler at exactly 150.
- C. Correct. These are the three rows priced 150 or more, sorted from the highest price.