Sqlzoo 练习3
We continue practicing simple SQL queries on a single table.This tutorial is concerned with a table of Nobel prize winners:
select子句顺序
- select
- from
- where
- group by
- having
- order by(desc是降序)
练习
- Change the query shown so that it displays Nobel prizes for 1950.
1 | SELECT yr, subject, winner |
- Show who won the 1962 prize for Literature.
1 | SELECT winner |
- Show the year and subject that won ‘Albert Einstein’ his prize.
1 | select yr, subject |
- Give the name of the ‘Peace’ winners since the year 2000, including 2000.
1 | select winner |
- Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.
1 | select yr, subject, winner |
- Show all details of the presidential winners:Theodore Roosevelt、Woodrow Wilson、Jimmy Carter、Barack Obama
1 | select * |
- Show the winners with first name John
1 | select winner |
- Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.
1 | select yr, subject, winner |
- Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine
1 | select yr, subject, winner |
- Show year, subject, and name of people who won a ‘Medicine’ prize in an early year (before 1910, not including 1910) together with winners of a ‘Literature’ prize in a later year (after 2004, including 2004)
1 | select yr, subject, winner |
11.Find all details of the prize won by PETER GRÜNBERG
1 | select yr, subject, winner |
- Find all details of the prize won by EUGENE O’NEILL
1 | select yr, subject, winner |
- List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.
1 | select winner,yr, subject |
-
The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1:满足条件是1,否则是0
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
- 1984年获奖
- 学科与名字排序
- 化学和物理最后排序(满足条件为1,排在后面)
1 | SELECT winner, subject |
1 | select winner, subject |