SELECT and WHERE

Querying a single table: choosing columns, expressions and aliases in SELECT, then filtering rows in WHERE with comparisons, AND, OR, NOT, BETWEEN, IN and LIKE.

24 cards
4 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
The numbers

A few cards from the deck

SELECT clause: what is it?
Show the answer
The part of a query that lists the columns or expressions shown in each row of the result.
SELECT * returns every column; SELECT titre, duree / 60 picks two, one of them computed. It filters no row: that is the job of WHERE.
How many rows does this query return?

```sql
-- film : titre, annee, genre, duree
-- Orbite 2012 SF 125 | Nuit blanche 1998 Drame 88
-- Zéro absolu 2004 SF 140 | Les Voisins 2015 Comédie 95
-- Cendres 2021 Drame 110 | Horizon 2010 SF 102
-- Terminus 1985 SF 85 | Bal masqué 2019 Comédie 82
SELECT titre FROM film
WHERE annee > 2000 AND genre = 'SF' OR duree < 90;
`
- 6
- 3
- 4
- 8
Show the answer
6
AND goes before OR: the condition is (annee > 2000 AND genre = 'SF') OR duree < 90. It keeps Orbite, Zéro absolu, Horizon (recent SF) then Nuit blanche, Terminus and Bal masqué (under 90 minutes). With parentheses around the OR there would be only 4 rows.
In a condition, … is evaluated before …: without parentheses, a OR b AND c reads a OR (b AND c).
Show the answer
In a condition, AND is evaluated before OR: without parentheses, a OR b AND c reads a OR (b AND c).
This is the most common WHERE mistake: as soon as AND and OR are mixed, add parentheses.
WHERE clause: what is it?
Show the answer
The part of a query that keeps only the rows for which a condition is true.
A row whose condition is false, or unknown because of a NULL, is dropped before SELECT computes anything.

Study this deck