【SQL練習】【SQLZOO】 3 SELECT from Nobel
3 SELECT from Nobel
問題はこちらです。
SELECT from Nobel Tutorial/ja - SQLZOO
以下が解答となります。
1.1950 年度の受賞者
【解答】
SELECT yr, subject, winner FROM nobel WHERE yr = 1950;
4.近年の平和賞
【解答】
SELECT winner FROM nobel WHERE subject = 'Peace' AND yr >= 2000;
5.1980年代の文学賞
【解答】
SELECT yr, subject, winner FROM nobel WHERE subject = 'Literature' AND yr BETWEEN 1980 AND 1989;
6.大統領のみ
【解答】
SELECT * FROM nobel WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter', 'Barack Obama');
7.ジョン
【解答】
SELECT winner FROM nobel WHERE winner LIKE 'John%';
8.異なる年度の化学賞と物理学賞
【解答】
SELECT yr, subject, winner FROM nobel WHERE (subject = 'Physics' AND yr = 1980) OR (subject = 'Chemistry' AND yr = 1984);
9.化学と医学を除く
【解答】
SELECT yr, subject, winner FROM nobel WHERE yr = 1980 AND subject NOT IN ('Chemistry', 'Medicine');
10.初期の医学と最近の文学
【解答】
SELECT yr, subject, winner FROM nobel WHERE (subject = 'Medicine' AND yr < 1910) OR (subject = 'Literature' AND yr >= 2004);
13.騎士の領土
【解答】
SELECT winner, yr, subject FROM nobel WHERE winner LIKE 'Sir%' ORDER BY yr DESC, winner ASC;
14.化学賞と物理賞は末尾に
【解答】
①:subject IN ('Physics' , Chemistry')を使用するとエラーになる
②:問題に沿った解答になっていると思うが正解できない
■ 2021.6.18 追記
SELECT winner,subject FROM nobel WHERE yr = 1984 ORDER BY (CASE subject WHEN 'Chemistry' THEN 1 WHEN 'Physics' THEN 2 ELSE 0 END), subject, winner