经典 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 as e1,
  department as 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 AS e1,
  employee AS 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
  ) AS t
where
  t.deptno = department.deptno;
-----------------------------------------------------------------
  #19 查询每个部门的雇员数量。
select
  job,
  count(*)
from
  employee
GROUP BY
  job;
----------------------------------------------------------------
  #20 查询每种工作的最高工资、最低工资、人数
select
  job ' 工 种 ',
  MAX(sal) ' 最 高 工 资 ',
  MIN(sal) ' 最 低 工 资 ',
  count(*)
from
  employee
group by
  job;
----------------------------------------------------------------
  #21  列出所有员工的姓名及其直接上级的姓名
select
  e1.ename,
  e2.ename
from
  employee e1,
  employee e2
where
  e1.mgr = e2.empno;
----------------------------------------------------------------
  #22 查出至少有 2 个员工的部门。显示部门编号、部门名称、部门位置、部门人数
  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
  ) AS z
WHERE
  z.deptno IN (d.deptno)
HAVING
  d.deptno in (
    select
      deptno
    from
      employee
    GROUP by
      deptno
    HAVING
      count(deptno) >= 1
  )
上一篇:web,H5下载


下一篇:金三银四季来了!Java 面试题大放送,能答对70%就去BATJTMD试试~