SQL ZOO 练习打卡第 3 天_show year, subject, and name of people who won a '-CSDN博客

知识点汇总

1. 运算符

1.1 比较运算符

比较运算符
运算符	功能描述	示例
=	等于
<=>	安全的等于
◇或!=	不等于
<=	小于等于
>=	大于等于
>	大于
<	小于
IS NULL	判断一个值是否为空
IS NOT NULL	判断一个值是否不空
BETWEEN x AND y	判断一个值是否在[x,y]之间
NOT BETWEEN x AND y	判断一个值是否不在[x,y]之间
IN	判断一个值是否是列表中的任意一个值	6
NOT IN	判断一个值是否不是列表中的任意一个值
LIKE	模糊匹配，LIKE一般会搭配通配符“%”或“_”使用，其中“%”表示任意个字符，“_ ”代表一个字符	7，13
REGEXP 或 RLIKE	正则表达式匹配

2. 函数

2.1 数学函数

数学函数
运算符	功能描述	示例
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x除以y的余数
RANDO	返回0~1的随机数
ROUND(x,y)	返回参数x四舍五入后包含y位小数的值，y不指定时表示四舍五入到整数
TRUNCATE(x,y)	返回数字x截断为y位小数的结果，y不指定时表示截取所有小数部分
SQRT(x)	返回x的平方根
POW(x,y)	返回x的y次方

2.2 字符串函数

字符串函数
运算符	功能描述	示例
LENGTH(s)	返回字符串s的字节数，和字符集有关
LEFT(s,n)	返回字符串s最左边的n个字符

2.3 流程函数

函数

功能描述

示例

CASE WHEN [value1] THEN [result1]......

ELSE [default] END

如果value1是真，返回result1，否则返回default

CASE[expr] WHEN [value1] THEN [RESULT1]......

ELSE [default] END

如果expr等于value1，返回result1，否则返回default

3 高级查询语句

3.1 order by

ASC：表示按升序排序。
DESC：表示按降序排序。

SELECT from Nobel Tutorial

1.Change the query shown so that it displays Nobel prizes for 1950.

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

2.Show who won the 1962 prize for literature.

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

3.Show the year and subject that won 'Albert Einstein' his prize.

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

4.Give the name of the 'peace' winners since the year 2000, including 2000.

select winner from nobel
where yr >=2000 and subject ='peace'

5.Show all details (yr, subject, winner) of the literature prize winners for 1980 to 1989 inclusive.

select * from nobel
where subject = 'literature' and yr >= 1980 and yr <= 1989

6.Show all details of the presidential winners:

Theodore Roosevelt
Thomas Woodrow Wilson
Jimmy Carter
Barack Obama

select * from nobel
where winner IN ('Theodore Roosevelt',
'Thomas Woodrow Wilson',
'Jimmy Carter','Barack Obama')

7.Show the winners with first name John

select winner from nobel
where winner like 'john%'

8.Show the year, subject, and name of physics winners for 1980 together with the chemistry winners for 1984.

select * from nobel
where (yr='1980' and subject = 'physics')
or (yr = '1984' and subject = 'chemistry')

9.Show the year, subject, and name of winners for 1980 excluding chemistry and medicine

select * from nobel
where yr = '1980'
and (subject <> 'chemistry' and subject <> 'medicine')

10.Show year, subject, and name of people who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)

select * from nobel
where (yr < '1910' and subject = 'Medicine')
or (yr >= '2004' and subject = 'Literature' )

11.Find all details of the prize won by PETER GRÜNBERG

select * from nobel
where winner = 'PETER GRÜNBERG'

12.Find all details of the prize won by EUGENE O'NEILL

select * from nobel
where winner = "EUGENE O'NEILL"

13.List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.

select winner,yr,subject from nobel
where winner like"Sir%"
order by yr desc,winner ;

14.Show the 1984 winners and subject ordered by subject and winner name; but list chemistry and physics last.

The expression subject IN ('chemistry','physics') can be used as a value - it will be 0 or 1.

select winner, subject from nobel
where yr = '1984'
order by
case
when subject IN ('chemistry', 'physics') then 1
else 0
end,
subject,winner;