Sqlzoo 练习3
We continue practicing simple SQL queries on a single table.This tutorial is concerned with a table of Nobel prize winners:
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950;
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature';
select yr, subject
from nobel
where winner = 'Albert Einstein';
select winner
from nobel
where subject = 'Peace'
and yr >= 2000;
select yr, subject, winner
from nobel
where subject='Literature'
and (yr between 1980 and 1989);
select *
from nobel
where winner in ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter','Barack Obama');
select winner
from nobel
where winner like 'John%'; -- 使用单引号
select yr, subject, winner
from nobel
where (yr = 1980 and subject = 'Physics')
or (yr = 1984 and subject = 'Chemistry');
select yr, subject, winner
from nobel
where yr = 1980
and subject not in ('Chemistry', 'Medicine');
select yr, subject, winner
from nobel
where (subject = 'Medicine' and yr < 1910)
or (subject = 'Literature' and yr >= 2004);
11.Find all details of the prize won by PETER GRÜNBERG
select yr, subject, winner
from nobel
where winner like 'PETER%erg'; -- 使用通配符来解决
select yr, subject, winner
from nobel
where winner like 'EU%ILL'
select winner,yr, subject
from nobel
where winner like 'Sir%'
order by yr desc, winner asc; -- 指定时间的降序,名字的升序
SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'),subject,winner
select winner, subject
from nobel
where yr=1984
order by
case when subject in ('Physics','Chemistry') then 1 else 0 end, -- 满足when语句为1,否则是0
subject, winner;