Combine conditions
Use AND, OR, NOT and brackets to say exactly which rows you want. About 8 minutes.
AND needs both, OR needs one
One test is often not enough. You want novels that came out after 1950, or poetry or stories. Combine tests with AND and OR.
AND keeps a row only when both tests pass. OR keeps it when at least one passes. NOT flips a test: the rows it would have kept are dropped and the others are kept.
Each side must be a full test: genre = 'Novel' OR genre = 'Poetry', not genre = 'Novel' OR 'Poetry'.
WHERE genre = 'Novel' AND year > 1950 WHERE genre = 'Novel' OR genre = 'Poetry' WHERE NOT genre = 'Novel'
AND goes first, so use brackets
Mix AND with OR and SQL works out the AND first, like multiplication before addition. So genre = 'Novel' OR genre = 'Poetry' AND year > 1950 means any Novel at all, or a Poetry book after 1950. Rarely what you meant.
Brackets fix it. Put the OR inside brackets, and the year test then applies to both genres.
WHERE (genre = 'Novel' OR genre = 'Poetry') AND year > 1950
| id | title | genre | year |
|---|---|---|---|
| 1 | The Tea Estate | Novel | 1968 |
| 2 | Madhushala | Poetry | 1935 |
| 3 | Jungle Tales | Fables | 1960 |
| 4 | River Songs | Poetry | 1987 |
Every row must pass both: the genre test in brackets, and the year test.
WHERE city = 'Pune' OR city = 'Delhi' AND score > 60. Which of these rows is kept?
- Acity Pune, score 40
- Bcity Delhi, score 40
- Ccity Mumbai, score 90
Show the answer
city Pune, score 40. AND runs first, so the query reads Pune, or (Delhi and above 60). Pune alone is enough.
Movie night
The movies table has one row per movie. Show the title of every Hindi or Tamil movie that is rated above 8.
movies(id, title, language, rating, year)
Up to 2,000 movies