SQLZoo SELECT Queries with Order BY

I practiced SQL fundamentals using the Nobel Prize database from SQLZoo. Each query below solves a specific filtering or sorting problem. They start simple and get progressively trickier.

Basic filtering

Nobel prizes from 1950:

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950

Literature winner in 1962:

SELECT winner
FROM nobel
WHERE yr = 1962 AND subject = 'literature'

When did Einstein win, and in what subject?

SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'

Filtering with ranges and lists

Peace winners since 2000:

SELECT winner
FROM nobel
WHERE yr >= 2000 AND subject = 'peace'

Literature winners from 1980 to 1989:

SELECT *
FROM nobel
WHERE yr >= 1980 AND yr <= 1989 AND subject = 'literature'

Specific presidential winners using IN:

SELECT *
FROM nobel
WHERE winner IN (
    'Theodore Roosevelt',
    'Thomas Woodrow Wilson',
    'Jimmy Carter',
    'Barack Obama'
)

Pattern matching with LIKE

Winners whose first name is John:

SELECT winner
FROM nobel
WHERE winner LIKE 'John%'

% matches any sequence of characters. LIKE 'John%' catches "John Nash", "John Hume", etc.

Combining conditions

Physics winners in 1980 and chemistry winners in 1984:

SELECT *
FROM nobel
WHERE (subject = 'physics' AND yr = '1980')
   OR (subject = 'chemistry' AND yr = '1984')

Winners in 1980, excluding chemistry and medicine:

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1980 AND subject NOT IN ('chemistry', 'medicine')

Early medicine prizes (before 1910) and late literature prizes (2004 onward):

SELECT yr, subject, winner
FROM nobel
WHERE (subject = 'medicine' AND yr < 1910)
   OR (subject = 'literature' AND yr >= 2004)

Special characters

Finding a winner with a special character in the name:

SELECT *
FROM nobel
WHERE winner = 'PETER GRÜNBERG'

Handling single quotes inside strings -- use two single quotes to escape:

SELECT *
FROM nobel
WHERE winner = 'EUGENE O''NEILL'

Sorting with ORDER BY

Winners starting with "Sir", most recent first, then alphabetically:

SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC, winner ASC

ORDER BY yr DESC puts the newest first. winner ASC breaks ties alphabetically. You can sort by multiple columns in sequence.

Using expressions as values

The expression subject IN ('chemistry', 'physics') evaluates to 0 or 1. You can use this to control sort order:

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject IN ('physics', 'chemistry'), subject, winner

This pushes physics and chemistry to the end of the results. A neat trick for custom sort orders without a CASE statement.