section A
1.创建员工信息表：
CREATE TABLE Employee
(s_product_id char(4) NOT NULL,
s_name VARCHAR(32) NOT NULL,
s_salary INTEGER,
s_department_id INTEGER);

创建部门信息表：
CREATE TABLE department
(d_id char(4) NOT NULL,
d_name VARCHAR(32) NOT NULL
);

插入员工信息表数据：
INSERT INTO employee VALUES(‘1’,‘Joe’,70000,1);
INSERT INTO employee VALUES(‘2’,‘Henry’,80000,2);
INSERT INTO employee VALUES(‘3’,‘Sam’,60000,2);
INSERT INTO employee VALUES(‘4’,‘Max’,7000,1);

记录问题：
为什么姓名和薪水不匹配

2.改变相邻两个同学的位置
如果学生人数是奇数，则不需要改变最后一个同学的座位。
select s.id , s.student from
(
select id-1 as id ,student from seat where mod(id,2)=0
union
select id+1 as id,student from seat where mod(id,2)=1 and id !=(select count() from seat)
union
select id,student from seat where mod(id,2)=1 and id = (select count() from seat)
) s order by id;

练习三
1.使用ROW_NUMBER()进行排序
ROW_NUMBER()排序的序号是连续不重复的，即使表中存在多个一样的数值仍然按顺序依次编号。
SELECT score_avg,
ROW_NUMBER() OVER(ORDER BY score_avg DESC) AS ‘rank’
FROM Scores;
2.使用rank()进行排序
rank()在排序时，会把多个数值相同的归为一组，以同样的序号进行编号。但是编号不连续，会按照实际次序编辑下一组序号。
SELECT score_avg,
rank() OVER(ORDER BY score_avg DESC) AS ‘rank’
FROM Scores;
3.使用dense_rank()进行排序
dense_rank()在排序时，会把多个数值相同的归为一组，以同样的序号进行编号。但是他编号是连续的。
SELECT score_avg,
dense_rank() OVER(ORDER BY score_avg DESC) AS ‘rank’
FROM Scores;
4.使用ntile()进行排序
ntile(num)会讲所有的记录分成num个组，每个组序号一样。但是编号连续。
SELECT score_avg,
ntile(2) OVER(ORDER BY score_avg DESC) AS ‘rank’
FROM Scores;

练习四
–MySql
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num