Nobel Quiz/zh
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年間有多少個化學獎。
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)
選擇代碼以顯示有多少年沒有頒發醫學獎。
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 |
5) 選擇代碼以顯示哪一年沒有頒發物理獎,亦沒有頒發化學獎。
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'))
選擇代碼以顯示哪一年有頒發醫學獎,但沒有頒發和平或文學獎。
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 |