经典 SQL 联表查询 70 题
题目1,学生成绩管理系统
建表
# 学生表 Student
-- SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
# 科目表 Course
-- CId 课程编号,Cname 课程名称,TId 教师编号
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
# 教师表 Teacher
-- TId 教师编号,Tname 教师姓名
create table Teacher(TId varchar(10),Tname varchar(10));
# 成绩表 SC
-- SId 学生编号,CId 课程编号,score 分数
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
题目
# 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT student.*, s1.*
FROM student, (
SELECT *
FROM sc
WHERE cid = 1
) s1, (
SELECT *
FROM sc
WHERE cid = 2
) s2
WHERE s1.score > s2.score
AND s1.sid = s2.sid
AND student.sid = s1.sid;
# 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT *
FROM (
SELECT *
FROM sc
WHERE cid = 1
) s1, (
SELECT *
FROM sc
WHERE cid = 2
) s2
WHERE s1.sid = s2.sid;
#
SELECT sc.*
FROM sc, (
SELECT sid, count(sid) AS num
FROM sc
WHERE cid IN (1, 2)
GROUP BY sid
HAVING num > 1
) sc2
WHERE sc.sid = sc2.sid
AND sc.cid IN (1, 2);
#查询存在" 01 "课程但,不存在" 02 "课程的情况(不存在时显示为 null )
SELECT *
FROM sc
WHERE sc.SId NOT IN (
SELECT SId
FROM sc
WHERE sc.CId = '02'
)
AND sc.CId = '01';
# 2. 查询平均成绩大于等于 60 分的同学的 学生编号 和 学生姓名 和 平均成绩
SELECT student.SId, student.Sname, s.avg
FROM student, (
SELECT sid, AVG(score) AS avg
FROM sc
GROUP BY sid
HAVING avg >= 60
) s
WHERE student.SId = s.SId;
# 3. 查询在 SC 表存在成绩的学生信息
SELECT *
FROM student
WHERE sid IN (
SELECT sid
FROM sc
GROUP BY sid
);
# 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
# 课程总数
SELECT sname, t.class_count, t.score_count
FROM student, (
SELECT sid, count(CId) AS class_count, SUM(score) AS score_count
FROM sc
GROUP BY sid
) t
WHERE student.sid = t.sid;
# 查有成绩的学生信息
SELECT *
FROM student
WHERE student.sid IN (
SELECT sid
FROM sc
GROUP BY sid
);
# 查询「李」姓老师的数量
SELECT COUNT(tid)
FROM teacher
WHERE Tname LIKE '李%';
# 6. 查询学过「张三」老师授课的同学的信息
SELECT *
FROM student
WHERE SId IN (
SELECT sid
FROM sc
WHERE cid = (
SELECT CId
FROM course
WHERE tid IN (
SELECT tid
FROM teacher
WHERE Tname = '张三'
)
)
);
# 7. 查询没有学全所有课程的同学的信息
SELECT *
FROM student
WHERE sid IN (
SELECT sid
FROM sc
GROUP BY sid
HAVING COUNT(sid) < (
SELECT count(cid)
FROM course
)
);
# 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT *
FROM student
WHERE sid IN (
SELECT sid
FROM sc
WHERE cid IN (
SELECT CId
FROM sc
WHERE sid = 1
)
AND sid != 1
GROUP BY SId
);
# 9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT *
FROM student
WHERE SId IN (
SELECT sc.sid
FROM sc
WHERE cid IN (
SELECT cid
FROM sc
WHERE SId = 1
)
AND sid != 1
GROUP BY sid
HAVING # 课程数为 01 同学的课程数量一样
count(sid) = (
SELECT COUNT(sid)
FROM sc
WHERE sid = 1
GROUP BY sid
)
);
# 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT *
FROM student
WHERE sid NOT IN (
SELECT sid
FROM sc
WHERE cid IN (
SELECT CId
FROM course
WHERE TId IN (
SELECT tid
FROM teacher
WHERE Tname = '张三'
)
)
);
# 11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT student.sid, student.sname, avg(score)
FROM student, sc
WHERE student.sid IN (
SELECT sid
FROM sc
WHERE score < 60
GROUP BY sid
HAVING count(*) >= 2
)
AND student.sid = sc.sid
GROUP BY sid;
#
SELECT student.sid, student.sname, avg(score)
FROM student, sc
WHERE student.sid = sc.sid
AND score < 60
GROUP BY sid
HAVING count(*) >= 2;
# 12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT *
FROM student
WHERE sid IN (
SELECT sid
FROM sc
WHERE CId = 1
AND score < 60
ORDER BY score DESC
);
#
SELECT student.*
FROM student, sc
WHERE sc.CId = 1
AND sc.score < 60
AND student.sid = sc.sid
ORDER BY score DESC;
# 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT sc.*, t.score_avg
FROM sc, (
SELECT sid, avg(score) AS score_avg
FROM sc
GROUP BY SId
) t
WHERE sc.sid = t.sid
ORDER BY t.score_avg DESC;
# 解法2
SELECT sc.SId, sc.CId, sc.score, t1.avgscore
FROM sc
LEFT JOIN (
SELECT sc.SId, avg(sc.score) AS avgscore
FROM sc
GROUP BY sc.SId
) t1
ON sc.SId = t1.SId
ORDER BY t1.avgscore DESC;
# 14. 查询各科成绩最高分、最低分和平均分：
#以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
#及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
#要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT course.cid AS "课程ID", course.cname AS "课程name", max_score AS "最高分", min_score AS "最低分", avg_score AS "平均分"
, count_stu AS "选修人数", t.pass AS "及格率", t.middle AS "中等率", t.high AS "优秀率"
FROM course, sc, (
SELECT CId, max(score) AS max_score, AVG(score) AS avg_score
, min(score) AS min_score, count(sid) AS count_stu
, sum(CASE
WHEN sc.score >= 60 THEN 1
ELSE 0
END) / count(*) AS pass
, sum(CASE
WHEN sc.score >= 70
AND sc.score < 80
THEN 1
ELSE 0
END) / count(*) AS middle
, sum(CASE
WHEN sc.score >= 90 THEN 1
ELSE 0
END) / count(*) AS high
FROM sc
GROUP BY CId
) t
WHERE course.CId = t.CId
GROUP BY t.CId
ORDER BY t.count_stu DESC, t.CId ASC;
-- # 15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select
sc.SId,
sc.CId,
CASE
when @pre_parent_code = sc.CId then (
case
when @prefontscore = sc.score then @curRank
when @prefontscore: = sc.score then @curRank: = @curRank + 1
end
)
when @prefontscore: = sc.score then @curRank: = 1
end as rank,
sc.score,
@pre_parent_code: = sc.CId
from
(
select
@curRank: = 0,
@pre_parent_code: = '',
@prefontscore: = null
) as t,
sc
ORDER by
sc.CId,
sc.score desc;
# 16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select
t1.*,
@currank: = @currank + 1 as rank
from
(
select
sc.SId,
sum(score)
from
sc
GROUP BY
sc.SId
ORDER BY
sum(score) desc
) as t1,(
select
@currank: = 0
) as t;
#17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT c.CId, c.cname
, concat(t.r1 / t.num * 100, '%') AS "100_85"
, concat(t.r2 / t.num * 100, '%') AS "85_70"
, concat(t.r3 / t.num * 100, '%') AS "70_60"
, concat(t.r4 / t.num * 100, '%') AS "60_0"
FROM course c, (
SELECT cid, count(*) AS num
, count(CASE
WHEN score <= 100
AND score >= 85
THEN score
END) AS r1
, count(CASE
WHEN score < 85
AND score >= 70
THEN score
END) AS r2
, count(CASE
WHEN score < 70
AND score >= 60
THEN score
END) AS r3
, count(CASE
WHEN score < 60
AND score >= 0
THEN score
END) AS r4
FROM sc
GROUP BY cid
) t;
# 18. 查询各科成绩前三名的记录 (难点)
# 思路：若大于此成绩的数量少于3 即为前三名。
SELECT *
FROM sc
WHERE (
SELECT count(*)
FROM sc a
WHERE sc.CId = a.CId
AND sc.score < a.score
) < 3
ORDER BY CId ASC, sc.score DESC;
# 19. 查询每门课程被选修的学生数
SELECT course.*, t.num
FROM course
LEFT JOIN (
SELECT CId, count(*) AS num
FROM sc
GROUP BY CId
) t
ON course.CId = t.CId;
# 20. 查询出只选修两门课程的学生学号和姓名
SELECT SId, sname
FROM student
WHERE sid IN (
SELECT sid
FROM sc
GROUP BY SId
HAVING COUNT(*) = 2
);
# 21. 查询男生、女生人数
SELECT ssex, count(ssex)
FROM student
GROUP BY ssex;
# 22. 查询名字中含有「风」字的学生信息
SELECT *
FROM student
WHERE sname LIKE '%风%';
# 23. 查询同名同性学生名单，并统计同名人数
#
SELECT student.*, s.num
FROM student, (
SELECT sname, ssex, count(sname) AS num
FROM student
WHERE sname IN (
SELECT sname
FROM student
GROUP BY sname
HAVING count(sname) > 1
)
GROUP BY ssex
HAVING count(ssex) > 1
) s
WHERE student.sname = s.sname
AND student.ssex = s.ssex;
#
SELECT *
FROM student
LEFT JOIN (
SELECT sname, ssex, count(*) AS num
FROM student
GROUP BY sname, ssex
) s
ON student.sname = s.sname
AND student.ssex = s.ssex
WHERE s.num > 1;
# 24. 查询 1990 年出生的学生名单
SELECT *
FROM student
WHERE Year(sage) = '1990';
# 25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT cid, avg(score)
FROM sc
GROUP BY cid
ORDER BY cid ASC;
# 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT student.sid, sname, t.avg_score
FROM student
RIGHT JOIN (
SELECT sid, avg(score) AS avg_score
FROM sc
GROUP BY sid
HAVING avg_score >= 85
) t
ON student.sid = t.sid;
# 27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT student.Sname, t.score
FROM student, (
SELECT sid, score
FROM sc
WHERE CId = (
SELECT cid
FROM course
WHERE Cname = '数学'
)
AND score < 60
) t
WHERE student.sid = t.sid;
# 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT student.sid, student.Sname, sc.cid, sc.score
FROM student
LEFT JOIN sc ON student.sid = sc.SId
ORDER BY student.sid;
# 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT sname, cname, score
FROM student, course, (
SELECT *
FROM sc
WHERE score > 70
) t
WHERE student.sid = t.sid
AND course.CId = t.CId;
# 30. 查询不及格的课程
SELECT cname
FROM course
WHERE cid IN (
SELECT cid
FROM sc
WHERE score < 60
GROUP BY CId
);
# 31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT sid, sname
FROM student
WHERE sid IN (
SELECT SId
FROM sc
WHERE cid = 1
AND score > 80
);
# 32. 求每门课程的学生人数
SELECT c.cid, c.cname, t.num
FROM course c, (
SELECT cid, count(*) AS num
FROM sc
GROUP BY cid
) t
WHERE c.cid = t.cid;
# 33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT student.*, t.score
FROM student, (
SELECT *
FROM sc
WHERE CId = (
SELECT cid
FROM course
WHERE course.CId = (
SELECT tid
FROM teacher
WHERE Tname = '张三'
)
)
ORDER BY score DESC
LIMIT 0, 1
) t
WHERE student.sid = t.SId;
# 34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT student.*, t.score
FROM student, sc, (
SELECT cid, score, count(*) AS num
FROM sc
WHERE CId = (
SELECT cid
FROM course
WHERE course.CId = (
SELECT tid
FROM teacher
WHERE Tname = '张三'
)
)
GROUP BY score
ORDER BY score DESC
LIMIT 0, 1
) t
WHERE sc.CId = t.cid
AND sc.score = t.score
AND student.sid = sc.sid;
# 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 (重点)
SELECT sc.sid, sc.CId, sc.score
FROM sc, (
SELECT SId, score
FROM sc
GROUP BY score, sid
HAVING count(*) > 1
) t
WHERE sc.sid = t.sid
AND sc.score = t.score;
#
SELECT t1.*
FROM sc t1
WHERE EXISTS (
SELECT *
FROM sc t2
WHERE t1.SId = t2.SId
AND t1.CId != t2.CId
AND t1.score = t2.score
);
#
SELECT t1.*
FROM sc t1, sc t2
WHERE t1.sid = t2.sid
AND t1.CId != t2.CId
AND t1.score = t2.score
GROUP BY sid, cid, score;
# 36. 查询每门功成绩最好的前两名 (难点)
SELECT *
FROM sc s1
WHERE (
SELECT count(*)
FROM sc s2
WHERE s1.cid = s2.cid
AND s1.score < s2.score
) < 3
ORDER BY CId DESC, score DESC;
# 37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）
SELECT cid, count(*) AS num
FROM sc
GROUP BY cid
HAVING num > 5;
# 38. 检索至少选修两门课程的学生学号
SELECT sid, count(*) AS num
FROM sc
GROUP BY SId
HAVING num >= 2;
# 39. 查询选修了全部课程的学生信息
SELECT *
FROM student
WHERE sid IN (
SELECT sid
FROM sc
GROUP BY SId
HAVING count(*) = (
SELECT count(*)
FROM course
)
);
# 40. 查询各学生的年龄，只按年份来算
SELECT sid, sname
, Year(Now()) - Year(sage)
FROM student;
# 41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT student.SId AS "学生编号", student.Sname AS "学生编号"
, TIMESTAMPDIFF(YEAR, student.Sage, CURDATE()) AS "学生年龄"
FROM student;
# 42. 查询本周过生日的学生
SELECT *
FROM student
WHERE YEARWEEK(student.Sage) = YEARWEEK(CURDATE());
# 43. 查询下周过生日的学生
SELECT *
FROM student
WHERE YEARWEEK(student.Sage) = YEARWEEK(CURDATE()) + 1;
# 44. 查询本月过生日的学生
SELECT *
FROM student
WHERE MONTH(student.Sage) = MONTH(CURDATE());
#
SELECT *
FROM student
WHERE EXTRACT(YEAR_MONTH FROM student.Sage) = EXTRACT(YEAR_MONTH FROM CURDATE());
# 45. 查询下月过生日的学生
SELECT *
FROM student
WHERE MONTH(sage) = Month(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));
题目 2, 公司员工管理系统
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`deptno` int(11) NOT NULL COMMENT '部门编号',
`dname` varchar(255) DEFAULT NULL COMMENT '部门名称',
`loc` varchar(255) DEFAULT NULL COMMENT '部门地址',
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';
-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('10', '教研部', '北京');
INSERT INTO `department` VALUES ('20', '学工部', '上海');
INSERT INTO `department` VALUES ('30', '销售部', '广州');
INSERT INTO `department` VALUES ('40', '财务部', '武汉');
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`empno` int(11) NOT NULL COMMENT '员工编号',
`ename` varchar(255) DEFAULT NULL COMMENT '员工姓名',
`job` varchar(255) DEFAULT NULL COMMENT '职位名称',
`mgr` varchar(255) DEFAULT NULL COMMENT '上司ID',
`hiredate` date DEFAULT NULL COMMENT '入职事件',
`sal` double(255,2) DEFAULT NULL COMMENT '薪水',
`comm` double(255,2) DEFAULT NULL COMMENT '奖金',
`deptno` int(255) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1001', '小明', '文员', '1013', '2020-10-24', '8000.00', '0.00', '20');
INSERT INTO `employee` VALUES ('1002', '张三', '销售员', '1006', '2020-10-24', '16000.00', '3000.00', '30');
INSERT INTO `employee` VALUES ('1003', '李四', '销售员', '1006', '2020-10-24', '12500.00', '5000.00', '30');
INSERT INTO `employee` VALUES ('1004', '王五', '经理', '1009', '2020-10-24', '29750.00', '0.00', '20');
INSERT INTO `employee` VALUES ('1005', '张飞', '销售员', '1006', '2020-10-24', '12500.00', '1400.00', '20');
INSERT INTO `employee` VALUES ('1006', '智障', '经理', '1009', '2020-10-24', '28500.00', '0.00', '30');
INSERT INTO `employee` VALUES ('1007', '特朗', '经理', '1009', '2020-10-24', '24500.00', '0.00', '30');
INSERT INTO `employee` VALUES ('1008', '阿三', '分析师', '1004', '2020-10-24', '30000.00', '0.00', '20');
INSERT INTO `employee` VALUES ('1009', '阿四', '董事长', 'NULL', '2020-10-24', '50000.00', '0.00', '30');
INSERT INTO `employee` VALUES ('1010', '阿五', '销售员', '1006', '2020-10-24', '15000.00', '0.00', '10');
INSERT INTO `employee` VALUES ('1011', '阿六', '文员', '1008', '2020-10-24', '11000.00', '0.00', '20');
INSERT INTO `employee` VALUES ('1012', '傻瓜', '文员', '1006', '2020-10-24', '9500.00', '0.00', '30');
INSERT INTO `employee` VALUES ('1013', '二蛋', '分析师', '1004', '2020-10-24', '30000.00', '0.00', '20');
INSERT INTO `employee` VALUES ('1014', '麻子', '文员', '1007', '2020-10-24', '13000.00', '0.00', '10');
INSERT INTO `employee` VALUES ('1015', '阿巴', '保洁员', '1001', '2020-10-24', '8000.00', '100.00', '50');
-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(11) NOT NULL COMMENT '薪水等级',
`losal` int(11) DEFAULT NULL COMMENT '最低薪水',
`hisal` int(11) DEFAULT NULL COMMENT '最高薪水'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='薪水登记表';
-- ----------------------------
-- Records of salgrade
-- ----------------------------
练习
# 1 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
SELECT e1.empno, e1.ename, dep.dname
FROM employee e1, department dep
WHERE e1.hiredate < (
SELECT hiredate
FROM employee
WHERE e1.mgr = employee.empno
)
AND e1.deptno = dep.deptno;
# 2 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT department.dname, employee.*
FROM employee
RIGHT JOIN department ON employee.deptno = department.deptno;
#3 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号
SELECT ename
FROM employee
WHERE deptno = (
SELECT deptno
FROM department
WHERE dname = '销售部'
);
#4 列出与张飞从事相同工作的所有员工及部门名称。
SELECT employee.*, department.dname
FROM employee, department
WHERE employee.job = (
SELECT job
FROM employee
WHERE ename = '张飞'
)
AND department.deptno = employee.deptno;
#5 列出薪金高于在部门 30 工作的所有员工的薪金 的员工姓名和薪金、部门名称
SELECT ename, dname
FROM employee, department
WHERE sal > (
SELECT MAX(sal)
FROM employee
WHERE deptno = 30
)
AND employee.deptno = department.deptno;
#6 列出薪金高于公司平均薪金的所有员工信息. 所在部门名称，上级领导，工资等级。
SELECT e1.empno, e1.ename, e2.ename AS "上级名称"
FROM employee e1, employee e2
WHERE e1.sal > (
SELECT AVG(sal)
FROM employee
)
AND e1.mgr = e2.empno;
#7 查询出部门编号为30的所有员工
SELECT *
FROM employee
WHERE deptno = 30;
#8 所有销售员的姓名、编号和部门编号。
SELECT ename, empno, deptno
FROM employee
WHERE job = '销售员';
#9 找出奖金高于工资的员工。
SELECT *
FROM employee
WHERE comm > sal;
#10 找出奖金高于工资60%的员工。
SELECT *
FROM employee
WHERE comm > sal * 0.6;
#11 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。
SELECT *
FROM employee
WHERE (deptno = 10
AND job = '经理')
OR (deptno = 20
AND job = '销售员');
#12 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。
SELECT *
FROM employee
WHERE (deptno = 10
AND job = '经理')
OR (deptno = 20
AND job = '销售员')
OR (job != '经理'
AND job != '销售员'
AND sal > 20000);
#13 无奖金或奖金低于1000的员工。
SELECT *
FROM employee
WHERE comm < 1000
OR comm = 0;
#14 查询名字由三个字组成的员工。
SELECT *
FROM employee
WHERE ename LIKE ' ___ ';
#15 查询2000年入职的员工。
SELECT *
FROM employee
WHERE hiredate = ' 2000 ';
#16 查询所有员工详细信息，用编号升序排序
SELECT *
FROM employee
ORDER BY empno ASC;
#17 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
SELECT *
FROM employee
ORDER BY sal DESC, hiredate ASC;
#18 查询每个部门的平均工资
SELECT t.deptno, t.avg_sal, dname
FROM department, (
SELECT deptno, AVG(sal) AS avg_sal
FROM employee
GROUP BY deptno
) t
WHERE t.deptno = department.deptno;
#19 查询每个部门的雇员数量。
SELECT job, count(*)
FROM employee
GROUP BY job;
#20 查询每种工作的最高工资、最低工资、人数
SELECT job AS '工种', MAX(sal) AS ' 最 高 工 资 ', MIN(sal) AS '最低工资'
, count(*)
FROM employee
GROUP BY job;
#21 列出所有员工的姓名及其直接上级的姓名
SELECT e1.ename, e2.ename
FROM employee e1, employee e2
WHERE e1.mgr = e2.empno;
CREATE VIEW dept_emp_num
AS
SELECT deptno, COUNT(deptno) AS num
FROM employee
GROUP BY deptno;
# 使用视图查询
SELECT d.deptno, d.dname, d.loc, num
FROM department d, dept_emp_num
WHERE d.deptno = dept_emp_num.deptno
AND dept_emp_num.num >= 1;
# 解法2
SELECT d.deptno, d.dname, d.loc, z.num
FROM department d, (
SELECT deptno, count(*) AS num
FROM employee
GROUP BY deptno
) z
WHERE z.deptno IN (d.deptno)
HAVING d.deptno IN (
SELECT deptno
FROM employee
GROUP BY deptno
HAVING count(deptno) >= 1
)
