练习题（共82题）"/>
SQL练习题（共82题）
数据表
-- 部门表
CREATE TABLE dept (id INT PRIMARY KEY PRIMARY KEY, -- 部门iddname VARCHAR(50), -- 部门名称 ------loc VARCHAR(50) -- 部门位置 ------
);-- 职务表，职务名称，职务描述
CREATE TABLE job (id INT PRIMARY KEY,jname VARCHAR(20), -------description VARCHAR(50) -------
);-- 员工表
CREATE TABLE emp (id INT PRIMARY KEY, -- 员工idename VARCHAR(50), -- 员工姓名 -------job_id INT, -- 职务idmgr INT , -- 上级领导joindate DATE, -- 入职日期salary DECIMAL(7,2), -- 工资 -------bonus DECIMAL(7,2), -- 奖金dept_id INT, -- 所在部门编号CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);-- 工资等级表
CREATE TABLE salarygrade (grade INT PRIMARY KEY,losalary INT, -- 最低薪资 -------hisalary INT -- 最高薪资
);
测试数据
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);SQL题1
- 查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
思路:1.要查询4个表 全部都要连表查询么 有两个表并不存在某种逻辑外键 也直接连接么
2.查询条件 连表查询需要去除笛卡尔积 另外两个表怎么办
3.显示的 员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级难点在于怎么 找到连接条件seletc * from emp e join j join dept d join salarygrade s on e.job_id = j.id and e.dept_id = d.id and e.sarlary Between s.losalary and s.hisalary and j.jname='经理'SQL题2
- 查询出每个部门的部门编号、部门名称、部门位置、部门人数
1.查询的表 单个部门表么？
2.单个表就不需要条件了 但是要分组
3 显示部门编号、部门名称、部门位置、部门人数
SELECT d.id, d.dname, d.loc, e.total 部门人数
FROM dept d INNER JOIN (SELECT dept_id, COUNT(*) total FROM emp GROUP BY dept_id) AS e
ON e.dept_id = d.id;
数据表
-- 部门表
create table dept(deptno int primary key auto_increment, -- 部门编号dname varchar(14) , -- 部门名字loc varchar(13) -- 地址
) ;
-- 员工表
create table emp(empno int primary key auto_increment,-- 员工编号ename varchar(10), -- 员工姓名 -job varchar(9), -- 岗位mgr int, -- 直接领导编号hiredate date, -- 雇佣日期，入职日期sal int, -- 薪水comm int, -- 提成deptno int not null, -- 部门编号foreign key (deptno) references dept(deptno)
);测试数据
#部门表
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');#员工表
insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭靖','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'令狐冲','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'张无忌','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'杨过','职员',7782,'1983-01-23',1300,null,10);
SQL题1
- 列出至少有一个员工的所有部门（部门编号、部门名称、部门人数）
思路： 这是什么查询 多表中的 子查询行子查询 还是列子查询？
三大步骤：
1.牵扯到了哪些表 部门表 员工表（部门人数要从员工表中去统计）
2.连接条件 主键连接 部门人数>=1 要分组么？
3.显示条件 部门编号 部门名称 部门人数select dept.deptno'部门编号' ,dept.name '部门名称' , count(empno) '部门人数' from
dept left join emp on dept.deptno =emp.deptno
group by dept.deptno having count(empno)>=1;### SQL题2- 列出受雇日期早于其直接上级的所有员工逻辑 先要找出所有的员工的受雇日期 在查出 他们的上级的受雇日期 大于的 就显示出来怎么查出 它们的上级 有个字段 mgr 表示直属上级seletc * fromemp as e1,
emp as e2
where e1.mgr = e2.empon and e1.hiredate>e2.hiredate自连接 SQL题3
- 列出所有员工的年工资,按年薪从低到高排序
select sal '年薪’from emp where sal*12 order by sal ;
### SQL题4- 将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序select * from emp
order by convert(substring(ename,1,1) using gbk) asc, sal desc;### SQL题5- 查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示思路：
需要查询什么表 部门表和员工表
连接条件+ 其他条件 两表之间的外键连接条件 部门编号为10的部门不显示
显示最高薪水和最低薪水部门select dept.dname as '部门名称',maxSal '薪水',minSal '薪水'
from dept left join(select deptno,max(sal)as maxSal,min(sal) as minSalfrom empgroup by deptno) tempon dept.deptno = temp.deptno
where dept.deptno<>10;这题学到了什么 不等于10 用<> 多表查询注意到低是那种查询 是行还是列 jion里面接上子查询### SQL题6- 查出emp表中所有部门的名称、最高薪水、最低薪水，部门编号为10的部门不显示SQL题7
- 删除10号部门薪水最高的员工
SQL题8
- 查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
数据表结构
学生表：t_student
| 字段名 | 类型 | 备注 |
|---|---|---|
| stu_id | varchar(10) | 学生编号 |
| stu_name | varchar(10) | 学生姓名 |
| stu_age | datetime | 出生年月 |
| stu_sex | varchar(10) | 学生性别 |
课程表：t_course
| 字段名 | 类型 | 备注 |
|---|---|---|
| c_id | varchar(10) | 课程编号 |
| c_name | varchar(10) | 课程名称 |
| c_teaid | varchar(10) | 教师编号（关联teacher表的tea_id） |
教师表：t_teacher
| 字段名 | 类型 | 备注 |
|---|---|---|
| tea_id | varchar(10) | 教师编号 |
| tea_name | varchar(10) | 教师姓名 |
成绩表：t_score
| 字段名 | 类型 | 备注 |
|---|---|---|
| s_stuid | varchar(10) | 学生编号（关联student表的stu_id） |
| s_cid | varchar(10) | 课程编号（关联course表的c_id） |
| s_score | decimal(18,1) | 分数 |
创建表
# 学生表
create table t_student
(stu_id varchar(10),stu_name varchar(10),stu_age datetime,stu_sex varchar(10)
);# 课程表
create table t_course
(c_id varchar(10),c_name varchar(10),c_teaid varchar(10)
);# 教师表
create table t_teacher
(tea_id varchar(10),tea_name varchar(10)
);# 成绩表
create table t_score
(s_stuid varchar(10),s_cid varchar(10),s_score decimal(18,1)
);
测试数据
-- 向t_student表插入数据
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('01','赵雷','1990-01-01' ,'男');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('02' , '钱电' , '1990-12-21' , '男');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('03' , '孙风' , '1990-12-20' , '男');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('04' , '李云' , '1990-12-06' , '男');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('05' , '周梅' , '1991-12-01' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('06' , '吴兰' , '1992-01-01' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('07' , '郑竹' , '1989-01-01' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('09' , '张三' , '2017-12-20' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('10' , '李四' , '2017-12-25' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('11' , '李四' , '2012-06-06' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('12' , '赵六' , '2013-06-13' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('13' , '孙七' , '2014-06-01' , '女');-- 向t_course表插入数据
insert into course(c_id , c_name , c_teaid) values('01' , '语文' , '02');
insert into course(c_id , c_name , c_teaid) values('02' , '数学' , '01');
insert into course(c_id , c_name , c_teaid) values('03' , '英语' , '03');-- 向t_teacher表插入数据
insert into teacher(tea_id , tea_name) values('01' , '张三');
insert into teacher(tea_id , tea_name) values('02' , '李四');
insert into teacher(tea_id , tea_name) values('03' , '王五');-- 向t_score表插入数据
insert into score(s_stuid,s_cid,s_score) values('01' , '01' , 80);
insert into score(s_stuid,s_cid,s_score) values('01' , '02' , 90);
insert into score(s_stuid,s_cid,s_score) values('01' , '03' , 99);
insert into score(s_stuid,s_cid,s_score) values('02' , '01' , 70);
insert into score(s_stuid,s_cid,s_score) values('02' , '02' , 60);
insert into score(s_stuid,s_cid,s_score) values('02' , '03' , 80);
insert into score(s_stuid,s_cid,s_score) values('03' , '01' , 80);
insert into score(s_stuid,s_cid,s_score) values('03' , '02' , 80);
insert into score(s_stuid,s_cid,s_score) values('03' , '03' , 80);
insert into score(s_stuid,s_cid,s_score) values('04' , '01' , 50);
insert into score(s_stuid,s_cid,s_score) values('04' , '02' , 30);
insert into score(s_stuid,s_cid,s_score) values('04' , '03' , 20);
insert into score(s_stuid,s_cid,s_score) values('05' , '01' , 76);
insert into score(s_stuid,s_cid,s_score) values('05' , '02' , 87);
insert into score(s_stuid,s_cid,s_score) values('06' , '01' , 31);
insert into score(s_stuid,s_cid,s_score) values('06' , '03' , 34);
insert into score(s_stuid,s_cid,s_score) values('07' , '02' , 89);
insert into score(s_stuid,s_cid,s_score) values('07' , '03' , 98);
SQL题
1. 查询同名学生名单，并统计同名人数，找到同名的名字并统计个数
2. 查询列出同名的全部学生的信息
3. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
4. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
5. 查询课程名称为"数学"，且分数低于 60 的学生姓名和分数
6. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
7. 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
**8. 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
**9. 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
- 备注：有重复成绩
10. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
11. 查询每门功成绩最好的前两名
12. 查询选修了全部课程的学生信息
13. 查询本周过生日的学生
14. 查询下月过生日的学生
15. 查询出只选修两门课程的学生学号和姓名
16. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
17.查询学过编号为"02"但没有学过编号为"01"课程的学生信息和相关课程成绩
18. 查询同时选修了"01"课程和"02"课程的学生信息及相关课程成绩
19. 查询选修了"01"课程但可能没有选修"02"课程的学生信息及相关课程成绩（不存在时显示为0）
**20. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
**21. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
22. 查询学过「张三」老师授课的同学的信息
23. 查询没有学全所有课程的同学的信息
24. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息 （不含学号为"01"的学生）
25. 查询和学号"01"同学学习的课程完全相同的其他同学的信息
26. 查询没学过"张三"老师讲授的任一门课程的学生姓名
27. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 （成绩保留两位小数）
28. 检索"01"课程分数小于 60，按分数降序排列的学生信息
29. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 （平均成绩保留两位小数）
30. 查询各科成绩最高分、最低分、平均分、选修人数、及格率 （及格率以百分比格式显示）
31. 按各科成绩进行排序，并显示排名， 成绩重复时不保留名次空缺
32. 按各科成绩进行排序，并显示排名，成绩重复时保留名次空缺
33. 查询学生的总成绩，并按照总成绩依次进行排名（不保留名次空缺）
34. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
35. 查询各科成绩前三名的记录
36. 查询每个同学的学号、姓名、选课总数、总成绩
37. 查询编号是2的课程比编号是1的课程最高成绩高的学生姓名和成绩
数据表结构
学生表：t_student
| 字段名 | 类型 | 备注 |
|---|---|---|
| stu_id | varchar(10) | 学生编号 |
| stu_name | varchar(10) | 学生姓名 |
| stu_age | datetime | 出生年月 |
| stu_sex | varchar(10) | 学生性别 |
课程表：t_course
| 字段名 | 类型 | 备注 |
|---|---|---|
| c_id | varchar(10) | 课程编号 |
| c_name | varchar(10) | 课程名称 |
| c_teaid | varchar(10) | 教师编号（关联teacher表的tea_id） |
教师表：t_teacher
| 字段名 | 类型 | 备注 |
|---|---|---|
| tea_id | varchar(10) | 教师编号 |
| tea_name | varchar(10) | 教师姓名 |
成绩表：t_score
| 字段名 | 类型 | 备注 |
|---|---|---|
| s_stuid | varchar(10) | 学生编号（关联student表的stu_id） |
| s_cid | varchar(10) | 课程编号（关联course表的c_id） |
| s_score | decimal(18,1) | 分数 |
创建表
# 学生表
create table t_student
(stu_id varchar(10),stu_name varchar(10),stu_age datetime,stu_sex varchar(10)
);# 课程表
create table t_course
(c_id varchar(10),c_name varchar(10),c_teaid varchar(10)
);# 教师表
create table t_teacher
(tea_id varchar(10),tea_name varchar(10)
);# 成绩表
create table t_score
(s_stuid varchar(10),s_cid varchar(10),s_score decimal(18,1)
);### 测试数据~~~mysql
-- 向t_student表插入数据
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('01','赵雷','1990-01-01' ,'男');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('02' , '钱电' , '1990-12-21' , '男');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('03' , '孙风' , '1990-12-20' , '男');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('04' , '李云' , '1990-12-06' , '男');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('05' , '周梅' , '1991-12-01' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('06' , '吴兰' , '1992-01-01' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('07' , '郑竹' , '1989-01-01' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('09' , '张三' , '2017-12-20' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('10' , '李四' , '2017-12-25' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('11' , '李四' , '2012-06-06' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('12' , '赵六' , '2013-06-13' , '女');
insert into t_student(stu_id,stu_name,stu_age,stu_sex) values('13' , '孙七' , '2014-06-01' , '女');-- 向t_course表插入数据
insert into course(c_id , c_name , c_teaid) values('01' , '语文' , '02');
insert into course(c_id , c_name , c_teaid) values('02' , '数学' , '01');
insert into course(c_id , c_name , c_teaid) values('03' , '英语' , '03');-- 向t_teacher表插入数据
insert into teacher(tea_id , tea_name) values('01' , '张三');
insert into teacher(tea_id , tea_name) values('02' , '李四');
insert into teacher(tea_id , tea_name) values('03' , '王五');-- 向t_score表插入数据
insert into score(s_stuid,s_cid,s_score) values('01' , '01' , 80);
insert into score(s_stuid,s_cid,s_score) values('01' , '02' , 90);
insert into score(s_stuid,s_cid,s_score) values('01' , '03' , 99);
insert into score(s_stuid,s_cid,s_score) values('02' , '01' , 70);
insert into score(s_stuid,s_cid,s_score) values('02' , '02' , 60);
insert into score(s_stuid,s_cid,s_score) values('02' , '03' , 80);
insert into score(s_stuid,s_cid,s_score) values('03' , '01' , 80);
insert into score(s_stuid,s_cid,s_score) values('03' , '02' , 80);
insert into score(s_stuid,s_cid,s_score) values('03' , '03' , 80);
insert into score(s_stuid,s_cid,s_score) values('04' , '01' , 50);
insert into score(s_stuid,s_cid,s_score) values('04' , '02' , 30);
insert into score(s_stuid,s_cid,s_score) values('04' , '03' , 20);
insert into score(s_stuid,s_cid,s_score) values('05' , '01' , 76);
insert into score(s_stuid,s_cid,s_score) values('05' , '02' , 87);
insert into score(s_stuid,s_cid,s_score) values('06' , '01' , 31);
insert into score(s_stuid,s_cid,s_score) values('06' , '03' , 34);
insert into score(s_stuid,s_cid,s_score) values('07' , '02' , 89);
insert into score(s_stuid,s_cid,s_score) values('07' , '03' , 98);
SQL题
1. 查询同名学生名单，并统计同名人数，找到同名的名字并统计个数
2. 查询列出同名的全部学生的信息
3. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
4. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
5. 查询课程名称为"数学"，且分数低于 60 的学生姓名和分数
6. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
7. 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
**8. 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
- 备注：成绩不重复
9. 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
- 备注：有重复成绩
10. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
11. 查询每门功成绩最好的前两名
12. 查询选修了全部课程的学生信息
13. 查询本周过生日的学生
14. 查询下月过生日的学生
15. 查询出只选修两门课程的学生学号和姓名
16. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
17.查询学过编号为"02"但没有学过编号为"01"课程的学生信息和相关课程成绩
18. 查询同时选修了"01"课程和"02"课程的学生信息及相关课程成绩
19. 查询选修了"01"课程但可能没有选修"02"课程的学生信息及相关课程成绩（不存在时显示为0）
20. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 （成绩保留2位小数）
**21. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
22. 查询学过「张三」老师授课的同学的信息
23. 查询没有学全所有课程的同学的信息
24. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息 （不含学号为"01"的学生）
25. 查询和学号"01"同学学习的课程完全相同的其他同学的信息
26. 查询没学过"张三"老师讲授的任一门课程的学生姓名
27. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 （成绩保留两位小数）
28. 检索"01"课程分数小于 60，按分数降序排列的学生信息
29. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 （平均成绩保留两位小数）
30. 查询各科成绩最高分、最低分、平均分、选修人数、及格率 （及格率以百分比格式显示）
31. 按各科成绩进行排序，并显示排名， 成绩重复时不保留名次空缺
32. 按各科成绩进行排序，并显示排名，成绩重复时保留名次空缺
33. 查询学生的总成绩，并按照总成绩依次进行排名（不保留名次空缺）
34. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
35. 查询各科成绩前三名的记录
*36. 查询每个同学的学号、姓名、选课总数、总成绩
37. 查询编号是2的课程比编号是1的课程最高成绩高的学生姓名和成绩
SQL练习题
1.组合两个表
题目：
表1: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+PersonId 是上表主键
表2: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+AddressId 是上表主键
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State
题解：
2. 第二高的薪水
题目：
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
目标：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
题解：
3. 第N高的薪水
题目：
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。
目标：
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
题解：
4. 分数排名
题目：
编写一个 SQL 查询来实现分数排名。
如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
目标：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
题解：
5.连续出现的数字
题目：
表：Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示：
目标：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 是唯一连续出现至少三次的数字。
题解：
6.超过经理收入的员工
题目：
Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
题解：
7.查找重复的电子邮箱
题目：
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
示例：
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b |
| 2 | c@d |
| 3 | a@b |
+----+---------+
根据以上输入，你的查询应返回以下结果：
目标：
+---------+
| Email |
+---------+
| a@b |
+---------+
说明：所有电子邮箱都是小写字母。
题解：
8. 从不订购的客户
题目：
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格，你的查询应返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
题解：
9. 部门工资最高的员工
题目：
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。
对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）
目标：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解释：
Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。
题解：
10. 部门工资前三高的所有员工
题目：
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
目标：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：
IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
题解：
11. 删除重复的电子邮箱
题目：
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example |
| 2 | bob@example |
| 3 | john@example |
+----+------------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
目标：
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example |
| 2 | bob@example |
+----+------------------+
题解：
12. 上升的温度
题目：
表 Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是这个表的主键
该表包含特定日期的温度信息
编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id
返回结果不要求顺序 。
Weather表数据：
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
目标:
+----+
| id |
+----+
| 2 |
| 4 |
+----+
题解：
13. 行程和用户
题目：
表：Trips
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| Id | int |
| Client_Id | int |
| Driver_Id | int |
| City_Id | int |
| Status | enum |
| Request_at | date |
+-------------+----------+
id 是这张表的主键
这张表中存所有出租车的行程信息。每段行程有唯一 Id ，其中 Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
Status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
表：Users
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| Users_Id | int |
| Banned | enum |
| Role | enum |
+-------------+----------+
Users_Id 是这张表的主键。
这张表中存所有用户，每个用户都有一个唯一的 Users_Id ，Role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
Banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。
写一段 SQL 语句查出 “2013-10-01” 至 “2013-10-03” 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 Banned 为 No 的用户，禁止用户即 Banned 为 Yes 的用户。
取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。
返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。
Trips表数据：
+----+-----------+-----------+---------+---------------------+------------+
| Id | Client_Id | Driver_Id | City_Id | Status | Request_at |
+----+-----------+-----------+---------+---------------------+------------+
| 1 | 1 | 10 | 1 | completed | 2013-10-01 |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 |
| 6 | 2 | 11 | 6 | completed | 2013-10-02 |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 |
| 8 | 2 | 12 | 12 | completed | 2013-10-03 |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 |
+----+-----------+-----------+---------+---------------------+------------+
Users表数据：
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
目标：
```
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
```2013-10-01：- 共有 4 条请求，其中 2 条取消。- 然而，Id=2 的请求是由禁止用户（User_Id=2）发出的，所以计算时应当忽略它。- 因此，总共有 3 条非禁止请求参与计算，其中 1 条取消。- 取消率为 (1 / 3) = 0.332013-10-02：- 共有 3 条请求，其中 0 条取消。- 然而，Id=6 的请求是由禁止用户发出的，所以计算时应当忽略它。- 因此，总共有 2 条非禁止请求参与计算，其中 0 条取消。- 取消率为 (0 / 2) = 0.002013-10-03：- 共有 3 条请求，其中 1 条取消。- 然而，Id=8 的请求是由禁止用户发出的，所以计算时应当忽略它。- 因此，总共有 2 条非禁止请求参与计算，其中 1 条取消。- 取消率为 (1 / 2) = 0.50
题解：
14.游戏玩法分析 I
题目：
活动表 Activity：
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
表的主键是 (player_id, event_date)。
这张表展示了一些游戏玩家在游戏平台上的行为活动。
每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）
写一条 SQL 查询语句获取每位玩家第一次登陆平台的日期。
Activity表数据：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
目标：
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
+-----------+-------------+
题解：
15. 游戏玩法分析 II
题目：
Table: Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
(player_id, event_date) 是这个表的两个主键
这个表显示的是某些游戏玩家的游戏活动情况
每一行是在某天使用某个设备登出之前登录并玩多个游戏（可能为0）的玩家的记录
请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称
Activity表数据:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
目标:
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
+-----------+-----------+
题解：
16.游戏玩法分析 III
题目：
Table: Activity
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
（player_id，event_date）是此表的主键。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0 ）。
编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。详细情况请查看示例。
Activity表数据:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 1 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
目标:
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1 | 2016-03-01 | 5 |
| 1 | 2016-05-02 | 11 |
| 1 | 2017-06-25 | 12 |
| 3 | 2016-03-02 | 0 |
| 3 | 2018-07-03 | 5 |
+-----------+------------+---------------------+
对于 ID 为 1 的玩家，2016-05-02 共玩了 5+6=11 个游戏，2017-06-25 共玩了 5+6+1=12 个游戏。
对于 ID 为 3 的玩家，2018-07-03 共玩了 0+5=5 个游戏。
请注意，对于每个玩家，我们只关心玩家的登录日期。
对于 ID 为 1 的玩家，2016-05-02 共玩了 5+6=11 个游戏，2017-06-25 共玩了 5+6+1=12 个游戏。
对于 ID 为 3 的玩家，2018-07-03 共玩了 0+5=5 个游戏。
请注意，对于每个玩家，我们只关心玩家的登录日期。
题解：
17.员工薪水中位数
题目：
Employee 表包含所有员工。Employee 表有三列：员工Id，公司名和薪水。
+-----+------------+--------+
|Id | Company | Salary |
+-----+------------+--------+
|1 | A | 2341 |
|2 | A | 341 |
|3 | A | 15 |
|4 | A | 15314 |
|5 | A | 451 |
|6 | A | 513 |
|7 | B | 15 |
|8 | B | 13 |
|9 | B | 1154 |
|10 | B | 1345 |
|11 | B | 1221 |
|12 | B | 234 |
|13 | C | 2345 |
|14 | C | 2645 |
|15 | C | 2645 |
|16 | C | 2652 |
|17 | C | 65 |
+-----+------------+--------+
请编写SQL查询来查找每个公司的薪水中位数。
- 挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题。
目标：
+-----+------------+--------+
|Id | Company | Salary |
+-----+------------+--------+
|5 | A | 451 |
|6 | A | 513 |
|12 | B | 234 |
|9 | B | 1154 |
|14 | C | 2645 |
+-----+------------+--------+
题解：
18. 至少有5名直接下属的经理
题目：
Employee 表包含所有员工和他们的经理。每个员工都有一个 Id，并且还有一列是经理的 Id。
+------+----------+-----------+----------+
|Id |Name |Department |ManagerId |
+------+----------+-----------+----------+
|101 |John |A |null |
|102 |Dan |A |101 |
|103 |James |A |101 |
|104 |Amy |A |101 |
|105 |Anne |A |101 |
|106 |Ron |B |101 |
+------+----------+-----------+----------+
给定 Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。
目标：
+-------+
| Name |
+-------+
| John |
+-------+
- 注意：没有人是自己的下属
题解：
19. 给定数字的频率查询中位数【困难】
题目：
Numbers 表保存数字的值及其频率。
+----------+-------------+
| Number | Frequency |
+----------+-------------|
| 0 | 7 |
| 1 | 1 |
| 2 | 3 |
| 3 | 1 |
+----------+-------------+
在此表中，数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位数是 (0 + 0) / 2 = 0。
目标：
+--------+
| median |
+--------|
| 0.0000 |
+--------+
请编写一个查询来查找所有数字的中位数并将结果命名为 median
题解：
20.当选者
题目：
表: Candidate
+-----+---------+
| id | Name |
+-----+---------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
+-----+---------+ 表: Vote
+-----+--------------+
| id | CandidateId |
+-----+--------------+
| 1 | 2 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 5 |
+-----+--------------+
id 是自动递增的主键，
CandidateId 是 Candidate 表中的 id.
请编写 sql 语句来找到当选者的名字，上面的例子将返回当选者 B
目标：
+------+
| Name |
+------+
| B |
+------+
注意:
- 你可以假设没有平局（换言之，最多只有一位当选者）
题解：
21.员工奖金
题目：
选出所有 bonus < 1000 的员工的 name 及其 bonus。
Employee 表单
+-------+--------+-----------+--------+
| empId | name | supervisor| salary |
+-------+--------+-----------+--------+
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 3 | Brad | null | 4000 |
| 4 | Thomas | 3 | 4000 |
+-------+--------+-----------+--------+
empId 是这张表单的主关键字
Bonus 表单
+-------+-------+
| empId | bonus |
+-------+-------+
| 2 | 500 |
| 4 | 2000 |
+-------+-------+
empId 是这张表单的主关键字
目标：
+-------+-------+
| name | bonus |
+-------+-------+
| John | null |
| Dan | 500 |
| Brad | null |
+-------+-------+
题解：
22.查询回答率最高的问题[困难]
题目：
从 survey_log 表中获得回答率最高的问题，survey_log 表包含这些列：id, action, question_id, answer_id, q_num, timestamp。
id 表示用户 id；action 有以下几种值：“show”，“answer”，“skip”；当 action 值为 “answer” 时 answer_id 非空，而 action 值为 “show” 或者 “skip” 时 answer_id 为空；q_num 表示当前会话中问题的编号。
请编写 SQL 查询来找到具有最高回答率的问题。
数据：
+------+-----------+--------------+------------+-----------+------------+
| id | action | question_id | answer_id | q_num | timestamp |
+------+-----------+--------------+------------+-----------+------------+
| 5 | show | 285 | null | 1 | 123 |
| 5 | answer | 285 | 124124 | 1 | 124 |
| 5 | show | 369 | null | 2 | 125 |
| 5 | skip | 369 | null | 2 | 126 |
+------+-----------+--------------+------------+-----------+------------+
目标：
+-------------+
| survey_log |
+-------------+
| 285 |
+-------------+
题解：
23.查询员工的累计薪水
题目：
Employee 表保存了一年内的薪水信息。
请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。
结果请按 Id 升序，然后按 Month 降序显示。
数据：
| Id | Month | Salary |
|---|---|---|
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 1 | 2 | 30 |
| 2 | 2 | 30 |
| 3 | 2 | 40 |
| 1 | 3 | 40 |
| 3 | 3 | 60 |
| 1 | 4 | 60 |
| 3 | 4 | 70 |
目标：
| Id | Month | Salary |
|---|---|---|
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 3 | 100 |
| 3 | 2 | 40 |
解释：
员工 ‘1’ 除去最近一个月（月份 ‘4’），有三个月的薪水记录：月份 ‘3’ 薪水为 40，月份 ‘2’ 薪水为 30，月份 ‘1’ 薪水为 20。
所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90，(30 + 20) = 50 和 20。
| Id | Month | Salary |
|---|---|---|
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 员工 ‘2’ 除去最近的一个月（月份 ‘2’）的话，只有月份 ‘1’ 这一个月的薪水记录。 |
| Id | Month | Salary |
|---|---|---|
| 2 | 1 | 20 |
| 员工 ‘3’ 除去最近一个月（月份 ‘4’）后有两个月，分别为：月份 ‘3’ 薪水为 60 和 月份 ‘2’ 薪水为 40。所以各月的累计情况如下： |
| Id | Month | Salary |
|---|---|---|
| 3 | 3 | 100 |
| 3 | 2 | 40 |
题解：
24.统计各专业学生人数
题目：
一所大学有 2 个数据表，分别是 student 和 department ，这两个表保存着每个专业的学生数据和院系数据。
写一个查询语句，查询 department 表中每个专业的学生人数 （即使没有学生的专业也需列出）。
将你的查询结果按照学生人数降序排列。 如果有两个或两个以上专业有相同的学生数目，将这些部门按照部门名字的字典序从小到大排列。
student 表格如下：
| Column Name | Type |
|---|---|
| student_id | Integer |
| student_name | String |
| gender | Character |
| dept_id | Integer |
| 其中， student_id 是学生的学号， student_name 是学生的姓名， gender 是学生的性别， dept_id 是学生所属专业的专业编号。 |
department 表格如下：
| Column Name | Type |
|---|---|
| dept_id | Integer |
| dept_name | String |
| dept_id 是专业编号， dept_name 是专业名字。 |
这里是一个示例输入：
student 表格：
| student_id | student_name | gender | dept_id |
|---|---|---|---|
| 1 | Jack | M | 1 |
| 2 | Jane | F | 1 |
| 3 | Mark | M | 2 |
| department 表格： |
| dept_id | dept_name |
|---|---|
| 1 | Engineering |
| 2 | Science |
| 3 | Law |
目标：
| dept_name | student_number |
|---|---|
| Engineering | 2 |
| Science | 1 |
| Law | 0 |
题解：
25.寻找用户推荐人
题目：
给定表 customer ，里面保存了所有客户信息和他们的推荐人。
+------+------+-----------+
| id | name | referee_id|
+------+------+-----------+
| 1 | Will | NULL |
| 2 | Jane | NULL |
| 3 | Alex | 2 |
| 4 | Bill | NULL |
| 5 | Zack | 1 |
| 6 | Mark | 2 |
+------+------+-----------+
写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都 不是 2。
对于上面的示例数据，
目标：
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+
题解：
26.2016年的投资
题目：
写一个查询语句，将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。
对于一个投保人，他在 2016 年成功投资的条件是：
他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。
输入格式:
表 insurance 格式如下：
| Column Name | Type |
|---|---|
| PID | INTEGER(11) |
| TIV_2015 | NUMERIC(15,2) |
| TIV_2016 | NUMERIC(15,2) |
| LAT | NUMERIC(5,2) |
| LON | NUMERIC(5,2) |
| PID 字段是投保人的投保编号， TIV_2015 是该投保人在2015年的总投保金额， TIV_2016 是该投保人在2016年的投保金额， LAT 是投保人所在城市的维度， LON 是投保人所在城市的经度。 |
样例输入
| PID | TIV_2015 | TIV_2016 | LAT | LON |
|---|---|---|---|---|
| 1 | 10 | 5 | 10 | 10 |
| 2 | 20 | 20 | 20 | 20 |
| 3 | 10 | 30 | 20 | 20 |
| 4 | 10 | 40 | 40 | 40 |
目标：
| TIV_2016 |
|---|
| 45.00 |
| 解释 |
就如最后一个投保人，第一个投保人同时满足两个条件：1. 他在 2015 年的投保金额 TIV_2015 为 '10' ，与第三个和第四个投保人在 2015 年的投保金额相同。
2. 他所在城市的经纬度是独一无二的。第二个投保人两个条件都不满足。他在 2015 年的投资 TIV_2015 与其他任何投保人都不相同。
且他所在城市的经纬度与第三个投保人相同。基于同样的原因，第三个投保人投资失败。所以返回的结果是第一个投保人和最后一个投保人的 TIV_2016 之和，结果是 45 。
题解：
27.订单最多的客户
题目：
在表 orders 中找到订单数最多客户对应的 customer_number 。
数据保证订单数最多的顾客恰好只有一位。
表 orders 定义如下：
| Column | Type |
|---|---|
| order_number (PK) | int |
| customer_number | int |
| order_date | date |
| required_date | date |
| shipped_date | date |
| status | char(15) |
| comment | char(200) |
| 样例输入： |
| order_number | customer_number | order_date | required_date | shipped_date | status | comment |
|---|---|---|---|---|---|---|
| 1 | 1 | 2017-04-09 | 2017-04-13 | 2017-04-12 | Closed | |
| 2 | 2 | 2017-04-15 | 2017-04-20 | 2017-04-18 | Closed | |
| 3 | 3 | 2017-04-16 | 2017-04-25 | 2017-04-20 | Closed | |
| 4 | 3 | 2017-04-18 | 2017-04-28 | 2017-04-25 | Closed |
目标
| customer_number |
|---|
| 3 |
解释
- customer_number 为 ‘3’ 的顾客有两个订单，比顾客 ‘1’ 或者 ‘2’ 都要多，因为他们只有一个订单
所以结果是该顾客的 customer_number ，也就是 3 。
进阶： 如果有多位顾客订单数并列最多，你能找到他们所有的 customer_number 吗？
题解：
28.大的国家
题目：
这里有张 World 表
+-----------------+------------+------------+--------------+---------------+
| name | continent | area | population | gdp |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
+-----------------+------------+------------+--------------+---------------+
如果一个国家的面积超过 300 万平方公里，或者人口超过 2500 万，那么这个国家就是大国家。
编写一个 SQL 查询，输出表中所有大国家的名称、人口和面积。
目标:
+--------------+-------------+--------------+
| name | population | area |
+--------------+-------------+--------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+--------------+-------------+--------------+
题解：
29.超过5名学生的课
题目：
有一个courses 表 ，有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
表数据：
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+------------+
目标:
+---------+
| class |
+---------+
| Math |
+---------+
提示：学生在每个课中不应被重复计算。
题解：
30.好友申请 I：总体通过率
题目：
在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。
表：FriendRequest
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| sender_id | int |
| send_to_id | int |
| request_date | date |
+----------------+---------+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求的日期。
表：RequestAccepted
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| requester_id | int |
| accepter_id | int |
| accept_date | date |
+----------------+---------+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求通过的日期。
写一个查询语句，求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。
提示：
通过的好友申请不一定都在表 friend_request 中。你只需要统计总的被通过的申请数（不管它们在不在表 FriendRequest 中），并将它除以申请总数，得到通过率
一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
如果一个好友申请都没有，通过率为 0.00 。
FriendRequest 表：
+-----------+------------+--------------+
| sender_id | send_to_id | request_date |
+-----------+------------+--------------+
| 1 | 2 | 2016/06/01 |
| 1 | 3 | 2016/06/01 |
| 1 | 4 | 2016/06/01 |
| 2 | 3 | 2016/06/02 |
| 3 | 4 | 2016/06/09 |
+-----------+------------+--------------+
RequestAccepted 表：
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1 | 2 | 2016/06/03 |
| 1 | 3 | 2016/06/08 |
| 2 | 3 | 2016/06/08 |
| 3 | 4 | 2016/06/09 |
| 3 | 4 | 2016/06/10 |
+--------------+-------------+-------------+
目标：
+-------------+
| accept_rate |
+-------------+
| 0.8 |
+-------------+
总共有 5 个请求，有 4 个不同的通过请求，所以通过率是 0.80
总共有 5 个请求，有 4 个不同的通过请求，所以通过率是 0.80
题解：
更多推荐
SQL练习题（共82题）
发布评论