1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

SQLZOO 「SELECT from Nobel Tutorial」 回答集

Last updated at Posted at 2018-08-18

###<2020/08/15 追記>
現在、SQLの学習をSQLZOOで開始するのはあまりお勧めできません。
別のサービスの活用をご検討ください。


SQLZOO 「SELECT from Nobel Tutorial」 の回答集を作成しました。
前回の「basics」「SELECT from WORLD Tutorial」の続編です。


##SELECT from Nobel Tutorial

1 . Winners from 1950

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

年を書き換えるだけ。

2 . 1962 Literature

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

コピペスキルのトレーニングですね。

3 . Albert Einstein

SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'

yearを「yr」にするなら他のも省略してほしい...

4 . Recent Peace Prizes

SELECT winner
FROM nobel
WHERE subject = 'Peace' and yr >= 2000

5 . Literature in the 1980's

SELECT *
FROM nobel
WHERE subject = 'Literature'
  AND yr BETWEEN 1980 and 1989

地味に初登場の「*」は全カラムという意味です。

6 . Only Presidents

SELECT * 
FROM nobel
WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter', 'Barack Obama')

7 . John

SELECT winner
FROM nobel
WHERE winner LIKE 'John%'

ジョン汁...(特に意味はないです)

8 . Chemistry and Physics from different years

SELECT * 
FROM nobel
WHERE (subject = 'Physics' and yr = 1980)
  OR (subject = 'Chemistry' and yr = 1984)

算数的なカッコの使い方ですね。

9 . Exclude Chemists and Medics

SELECT * 
FROM nobel
WHERE yr = 1980
  AND subject NOT IN ('Chemistry', 'Medicine')

NOT INの()内でORを使わないように注意です。

10 . Early Medicine, Late Literature

SELECT * 
FROM nobel
WHERE (yr < 1910 and subject = 'Medicine')
  OR (yr >= 2004 and subject = 'Literature')

ORとANDの使い分け。

11 . Umlaut

SELECT *
FROM nobel
WHERE winner = 'PETER GRÜNBERG'

ビビらせてくる割にコピペですんなりいけるんですが、ウムラウト文字を手打ちしたければこちらを参照

12 . Apostrophe

SELECT *
FROM nobel
WHERE winner = 'EUGENE O''NEILL'

文字列内の「'」(シングルクオーテーション)は2個続けて書けばいいよ、ということ。

13 . Knights of the realm

SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'Sir%'
  ORDER BY yr DESC, winner

年だけ新しい順なので、DESCを用いる。@secondtac さん、 @soemono さん、ご指摘ありがとうございました。

14 . Chemistry and Physics last

SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY subject IN ('Physics','Chemistry'), subject, winner

突然ハイレベルなんですが、
要は「INに含まれるものに1を、それ以外には0を付与される」ことを利用し、それ順に並べるということをしています。


###続きはこちら
https://qiita.com/TakahiroNAMBU/items/8fcfe1aa8977bfcd201c

1
0
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?