您好,欢迎访问代理记账网站
  • 价格透明
  • 信息保密
  • 进度掌控
  • 售后无忧

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

目录

SELECT from Nobel Tutorial

  1. Nobel Laureates
  2. Winners from 1950
  3. 1962 Literature
  4. Albert Einstein
  5. Recent Peace Prizes
  6. Literature in the 1980's
  7. Only Presidents
  8. John
  9. Chemistry and Physics from different years
  10. Exclude Chemists and Medics
  11. Early Medicine, Late Literature
  12. Harder Questions
  13. Umlaut
  14. Apostrophe
  15. Knights of the realm
  16. 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
yrsubjectwinner
1960ChemistryWillard F. Libby
1960LiteratureSaint-John Perse
1960MedicineSir Frank Macfarlane Burnet
1960MedicinePeter 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 (yrsubjectwinner) 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

nobel
yrsubjectwinner
1960ChemistryWillard F. Libby
1960LiteratureSaint-John Perse
1960MedicineSir Frank Macfarlane Burnet
1960MedicinePeter Medawar
1960PhysicsDonald A. Glaser
1960PeaceAlbert 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%'
MedicineSir John Eccles
MedicineSir 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
Chemistry1
Literature1
Medicine2
Peace1
Physics1

 


分享:

低价透明

统一报价,无隐形消费

金牌服务

一对一专属顾问7*24小时金牌服务

信息保密

个人信息安全有保障

售后无忧

服务出问题客服经理全程跟进