Nobel Quiz/ja
From SQLZoo
Language: | English • 日本語 • 中文 |
---|
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 | ||
... |
C で始まり n で終わる名前の受賞者を表示するコードを選べ。
SELECT name FROM nobel
WHERE winner LIKE '%C%' AND winner LIKE '%n%'
SELECT name FROM nobel
WHERE winner LIKE '%C' AND winner LIKE 'n%'
SELECT name FROM nobel
WHERE winner LIKE 'C%' AND winner LIKE '%n'
SELECT winner FROM nobel
WHERE winner LIKE '%C' AND winner LIKE 'n%'
SELECT winner FROM nobel
WHERE winner LIKE 'C%' AND winner LIKE '%n'
1950 から 1960 年の化学賞 Chemistry の受賞数を表示するコードを選べ。
SELECT COUNT(subject) FROM nobel
WHERE subject = 'Chemistry'
AND BETWEEN 1950 and 1960
SELECT COUNT(subject) FROM nobel
WHERE subject = 'Chemistry'
AND yr BETWEEN (1950, 1960)
SELECT COUNT(subject) FROM nobel
WHERE subject = 'Chemistry'
AND yr BETWEEN 1950 and 1960
SELECT subject FROM nobel
WHERE subject = 'Chemistry'
AND yr BETWEEN 1950 and 1960
SELECT subject FROM nobel
WHERE subject = 'Chemistry'
AND yr BETWEEN (1950, 1960)
医学賞 Medicine の受賞者がいない年度の回数を表示するコードを選べ。
SELECT COUNT(DISTINCT yr) FROM nobel
WHERE yr IN (SELECT DISTINCT yr FROM nobel WHERE subject <> 'Medicine')
SELECT COUNT(DISTINCT yr) FROM nobel
WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')
SELECT DISTINCT yr FROM nobel
WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject LIKE 'Medicine')
SELECT COUNT(DISTINCT yr) FROM nobel
WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject NOT LIKE 'Medicine')
SELECT COUNT(yr) FROM nobel
WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')
次のコードから得られる結果を選べ:
SELECT subject, winner FROM nobel WHERE winner LIKE 'Sir%' AND yr LIKE '196%'
Medicine | John Eccles |
Medicine | Frank Macfarlane Burnet |
Chemistry | Sir Cyril Hinshelwood |
Medicine | Sir John Eccles |
Medicine | Sir Frank Macfarlane Burnet |
Medicine | John Eccles |
Medicine | Frank Macfarlane Burnet |
Chemistry | Willard F.Libby |
Sir John Eccles |
Sir Frank Macfarlane Burnet |
物理賞 Physics も化学賞 Chemistry もいなかった年度を表示するコードを選べ。
SELECT yr FROM nobel
WHERE subject NOT IN(SELECT yr
FROM nobel
WHERE subject IN ('Chemistry','Physics'))
SELECT yr FROM nobel
WHERE subject NOT IN(SELECT subject
FROM nobel
WHERE subject IN ('Chemistry','Physics'))
SELECT yr FROM nobel
WHERE yr NOT IN(SELECT yr
FROM nobel
WHERE subject IN ('Chemistry','Physics'))
SELECT yr FROM nobel
WHERE yr NOT IN(SELECT subject
FROM nobel
WHERE subject IN ('Chemistry','Physics'))
SELECT yr FROM subject
WHERE yr NOT IN (SELECT yr
FROM nobel
WHERE subject IN ('Chemistry','Physics'))
医学賞 Medicine が授与されたが、平和賞 Peace も文学賞 Literature もいない年度を表示するコードを選べ。
SELECT DISTINCT yr
FROM nobel
WHERE subject='Medicine' AND
subject NOT IN(SELECT yr from nobel
WHERE subject='Literature')
AND yr NOT IN (SELECT yr
FROM nobel
WHERE subject='Peace')
SELECT DISTINCT yr
FROM nobel WHERE subject='Medicine'
AND yr NOT IN(SELECT yr from nobel
WHERE subject='Literature'
AND subject='Peace')
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')
SELECT DISTINCT yr
FROM subject
WHERE subject='Medicine'
AND yr NOT IN(SELECT yr from nobel
WHERE subject='Literature'
AND subject='Peace')
SELECT DISTINCT yr
FROM subject
WHERE subject='Medicine' AND
yr NOT IN('Literature','Peace')
次のコードから得られる結果を選べ:
SELECT subject, COUNT(subject)
FROM nobel
WHERE yr ='1960'
GROUP BY subject
1 |
1 |
2 |
1 |
1 |
Chemistry | 6 |
Chemistry | 3 |
Literature | 1 |
Medicine | 2 |
Peace | 0 |
Physics | 2 |
Chemistry | 1 |
Literature | 1 |
Medicine | 2 |
Peace | 1 |
Physics | 1 |
Chemistry | 1 |
Literature | 1 |
Peace | 1 |
Physics | 1 |