admin管理员组文章数量:1630191
目录
SELECT from Nobel Tutorial
- Nobel Laureates
- Winners from 1950
- 1962 Literature
- Albert Einstein
- Recent Peace Prizes
- Literature in the 1980's
- Only Presidents
- John
- Chemistry and Physics from different years
- Exclude Chemists and Medics
- Early Medicine, Late Literature
- Harder Questions
- Umlaut
- Apostrophe
- Knights of the realm
- Chemistry and Physics last
Nobel Quiz
- 1. Pick the code which shows the name of winner's names beginning with C and ending in n
- 2. Select the code that shows how many Chemistry awards were given between 1950 and 1960
- 3. Pick the code that shows the amount of years where no Medicine awards were given
- 4. Select the result that would be obtained from the following code:
- 5. Select the code which would show the year when neither a Physics or Chemistry award was given
- 6. Select the code which shows the years when a Medicine award was given but no Peace or Literature award was
- 7. Pick the result that would be obtained from the following code:
SELECT from Nobel Tutorial
网址:SELECT from Nobel Tutorial - SQLZOO
yr | subject | winner | ||
---|---|---|---|---|
1960 | Chemistry | Willard F. Libby | ||
1960 | Literature | Saint-John Perse | ||
1960 | Medicine | Sir Frank Macfarlane Burnet | ||
1960 | Medicine | Peter Madawar | ||
... |
Nobel Laureates
We continue practicing simple SQL queries on a single table.
This tutorial is concerned with a table of Nobel prize winners:
nobel(yr, subject, winner)
Using the SELECT
statement.
Winners from 1950
1.
Change the query shown so that it displays Nobel prizes for 1950.
- SELECT yr, subject, winner FROM nobel
- WHERE yr = 1950;
1962 Literature
2.
Show who won the 1962 prize for Literature.
- SELECT winner FROM nobel
- WHERE yr = 1962
- AND subject = 'Literature';
Albert Einstein
3.
Show the year and subject that won 'Albert Einstein' his prize.
- Select yr,subject From nobel
- Where winner = 'Albert Einstein';
Recent Peace Prizes
4.
Give the name of the 'Peace' winners since the year 2000, including 2000.
- Select winner From nobel
- Where yr>=2000
- And subject= 'Peace' ;
Literature in the 1980's
5.
Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.
- Select yr,subject,winner From nobel
- Where subject='Literature'
- And yr between 1980 and 1989;
Only Presidents
6.
Show all details of the presidential winners:
- Theodore Roosevelt
- Woodrow Wilson
- Jimmy Carter
- Barack Obama
- SELECT * FROM nobel
- WHERE winner in ('Theodore Roosevelt',
- 'Woodrow Wilson',
- 'Jimmy Carter',
- 'Barack Obama');
John
7.
Show the winners with first name John
- Select winner from nobel
- Where winner like 'John%';
Chemistry and Physics from different years
8.
Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.
- Select * from nobel
- Where subject = 'Physics' and yr=1980
- Or subject='Chemistry' and yr=1984;
Exclude Chemists and Medics
9.
Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine
- Select * from nobel
- Where yr = 1980 and subject not in ('Chemistry','Medicine');
Early Medicine, Late Literature
10.
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)
- Select * from nobel
- Where subject = 'Medicine' and yr <1910
- Or subject = 'Literature' and yr >=2004;
Harder Questions
Umlaut
11.
Find all details of the prize won by PETER GRÜNBERG
Non-ASCII characters
- Select * from nobel
- Where winner ='PETER GRÜNBERG';
Apostrophe
12.
Find all details of the prize won by EUGENE O'NEILL
Escaping single quotes, 查询含有单引号的字符串时要改为双单引号。
- Select * from nobel
- Where winner ='EUGENE O''NEILL';
Knights of the realm
13.
Knights in order
List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.
- Select winner,yr,subject from nobel
- Where winner like 'Sir%'
- Order by yr desc, winner;
Chemistry and Physics last
14.
The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
做不出来QAQ,求教。
Nobel Quiz
网址:Nobel Quiz - SQLZOO
yr | subject | winner | ||
---|---|---|---|---|
1960 | Chemistry | Willard F. Libby | ||
1960 | Literature | Saint-John Perse | ||
1960 | Medicine | Sir Frank Macfarlane Burnet | ||
1960 | Medicine | Peter Medawar | ||
1960 | Physics | Donald A. Glaser | ||
1960 | Peace | Albert Lutuli | ||
... |
1. Pick the code which shows the name of winner's names beginning with C and ending in n
- SELECT winner FROM nobel
- WHERE winner LIKE 'C%' AND winner LIKE '%n'
2. Select the code that shows how many Chemistry awards were given between 1950 and 1960
- SELECT COUNT(subject) FROM nobel
- WHERE subject = 'Chemistry' AND yr BETWEEN 1950 and 1960
3. Pick the code that shows the amount of years where no Medicine awards were given
- SELECT COUNT(DISTINCT yr) FROM nobel
- WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')
4. Select the result that would be obtained from the following code:
SELECT subject, winner FROM nobel
WHERE winner LIKE 'Sir%' AND yr LIKE '196%'
Medicine Sir John Eccles Medicine Sir Frank Macfarlane Burnet
5. Select the code which would show the year when neither a Physics or Chemistry award was given
- SELECT yr FROM nobel
- WHERE yr NOT IN (SELECT yr FROM nobel WHERE subject IN ('Chemistry','Physics'))
6. Select the code which shows the years when a Medicine award was given but no Peace or Literature award was
- SELECT DISTINCT yr FROM nobel
- WHERE subject='Medicine'
- AND yr NOT IN(SELECT yr FROM nobel
- WHERE subject='Literature')
- AND yr NOT IN (SELECT yr FROM nobel
- WHERE subject='Peace')
7. Pick the result that would be obtained from the following code:
SELECT subject, COUNT(subject)
FROM nobel
WHERE yr ='1960'
GROUP BY subject
Chemistry 1 Literature 1 Medicine 2 Peace 1 Physics 1
版权声明:本文标题:SQLzoo 习题记录03-SELECT from Nobel Tutorial & Nobel Quiz 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://www.elefans.com/xitong/1729057349a1184122.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论