SQLzoo 习题记录03-SELECT from Nobel Tutorial & Nobel Quiz

最新推荐文章于 2024-03-12 20:15:32 发布

Moist_Grass

最新推荐文章于 2024-03-12 20:15:32 发布

阅读量341

点赞数

分类专栏： SQLzoo笔记/习题文章标签： sql

本文链接：https://blog.csdn.net/Moist_Grass/article/details/117398657

版权

SQLzoo笔记/习题专栏收录该内容

9 篇文章 0 订阅

订阅专栏

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

nobel
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

Change the query shown so that it displays Nobel prizes for 1950.

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

1962 Literature

Show who won the 1962 prize for Literature.

SELECT winner FROM nobel
WHERE yr = 1962
AND subject = 'Literature';

Albert Einstein

Show the year and subject that won 'Albert Einstein' his prize.

Select yr,subject From nobel
Where winner = 'Albert Einstein';

Recent Peace Prizes

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

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

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

Show the winners with first name John

Select winner from nobel
Where winner like 'John%';

Chemistry and Physics from different years

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

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

nobel
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

Moist_Grass

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
SQLzoo 习题记录03-SELECT from Nobel Tutorial & Nobel Quiz

SELECT from Nobel Tutorial网址：SELECT from Nobel Tutorial - SQLZOO
复制链接

扫一扫