SQL练习题

1.

创建一个表，表如图所示

id	department	salary	name
1	1	200	张三
2	1	300	李四
3	1	100	王五
4	2	200	牛六
5	2	300	张耀

需求：
找出两个部门中工资最高的员工

需输出

department	salary	name
1	300	李四
2	300	张耀

步骤1：创建表

CREATE TABLE test0211(
    id INT,
    department INT,
    salary INT,
    NAME VARCHAR(20)
    );

步骤2：插入数据

INSERT INTO test0211
VALUES	(001,001,200,'张三')
	,(002,001,300,'李四')
	,(003,001,100,'王五')
        ,(004,002,200,'牛六')
        ,(005,002,300,'张耀')

步骤3：分析需求编写代码

1.需查出两个部门的最高薪资，所以使用子查询先通过
group by 按部门分组，再使用max函数求出最高薪资
2.使用where条件去筛选部门和薪资与表二相等的员工
输出

SELECT department, salary, NAME
FROM test0211
WHERE (department, salary) 
IN
(SELECT department, MAX(salary)
 FROM test0211
 GROUP BY department)

2.

表Stadium:

Column	Type
id	int
visit_date	date
people	int

visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加

Stadium table:

id	visit_date	people
1	2017-01-01	10
2	2017-01-02	109
3	2017-01-03	150
4	2017-01-04	99
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-09	188

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列的结果表。

查询结果格式如下所示:

Result table:

id	visit_date	people
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-09	188

题解：

id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。
所以我们要思考如何输出这四条连续的记录
因为至少需要三条id连续的记录，首先我们应该查询三张表

select t1.*
from stadium as t1, stadium as t2, stadium as t3

筛选出大于100的人数记录

select t1.*
from stadium as t1, stadium as t2, stadium as t3
where
 t1.people >= 100 and t2.people >= 100 andt3.people >= 100

筛选出id连续的记录

t1.id + 1 = t2.id and t2.id = t3.id - 1
)
or(
    t2.id + 1 = t1.id and t1.id = t3.id - 1
)
or(
    t3.id + 1 = t1.id and t1.id = t2.id - 1
) 

对返回结果进行升序排序

order by t1.id 

完整代码如下：

select t1.*
from stadium as t1, stadium as t2, stadium as t3
where
 t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
    t1.id + 1 = t2.id and t2.id = t3.id - 1
)
or(
    t2.id + 1 = t1.id and t1.id = t3.id - 1
)
or(
    t3.id + 1 = t1.id and t1.id = t2.id - 1
)   
order by t1.id

3.

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	Jeames

假如数据输出的是上表，则输出结果如下

id	student
1	Doris
2	Abbot
3	Green
4	Emerson
5	Jeames

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

题解：

思考：题目实际要求的就是两两互换位置，且如果学生总人数为奇数的话，最后一个学生座位不变。所以我们需要考虑两种情况

CASE 
            WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
            WHEN MOD(id,2) = 1 THEN id+1
            ELSE id-1
END

MOD(id,2) = 1即id为奇数时且id =（select count（*） from seat）即学生总人数为计数且id为最后一人时。不变

完整代码如下：

SELECT (CASE 
            WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
            WHEN MOD(id,2) = 1 THEN id+1
            ELSE id-1
        END) AS id, student
FROM seat
ORDER BY id;

方法2：

COALESCE()函数
主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理，其参数格式如下：
COALESCE ( expression,value1,value2……,valuen)
COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。

如果expression不为空值则返回expression；否则判断value1是否是空值，

如果value1不为空值则返回value1；否则判断value2是否是空值，

如果value2不为空值则返回value2；……以此类推，
如果所有的表达式都为空值，则返回NULL。

SELECT
s1.id, COALESCE(s2.student, s1.student) AS student
FROM
seat s1
LEFT JOIN
seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id
ORDER BY s1.id;