SQL学习-sqlzoo练习题记录(2)

3-SELECT FROM Nobel
1)

SELECT *
FROM nobel
WHERE yr = 1950

2)

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

3)

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

4)

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

5)

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

6)

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

7)

SELECT winner
FROM nobel
WHERE winner LIKE 'John%'

8)

SELECT *
FROM nobel
WHERE (yr=1980 AND subject='physics') OR (yr=1984 AND subject='chemistry')

9)

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

10)

SELECT *
FROM nobel
WHERE (yr<1910 AND subject='medicine') OR (yr>=2004 AND subject='literature')

11)

SELECT *
FROM nobel
WHERE winner='Peter Grünberg'

12)

SELECT *
FROM nobel
WHERE winner='Eugene O''Neill'

注意点：’为转义字符
13)

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

注意点：ORDER BY 中 DESC表示降序，ASC表示升序(默认)
14)

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

注意点：IN 可以返回0/1值；ORDER BY 前优先
错误点：读题，’list Chemistry and Physics last’order by先放