2018-12-28数据库练习

-- 1、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.s_id,b.s_name,AVG(a.s_score) 平均成绩
FROM score a JOIN student b ON a.s_id = b.s_id
GROUP BY a.s_id
HAVING AVG(a.s_score) >= 60;

-- 2、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.s_id,a.s_name,COUNT(*),SUM(b.s_score)
FROM student a JOIN score b ON a.s_id = b.s_id JOIN course c ON b.c_id = c.c_id
GROUP BY a.s_id;

-- 3、查询学过"张三"老师授课的学生的信息
SELECT a.t_name,d.*
FROM teacher a JOIN course b ON a.t_id=b.t_id JOIN score c ON b.c_id = c.c_id JOIN student d ON c.s_id = d.s_id
WHERE a.t_name = '张三';

-- 4、查询"李"姓老师的数量
①： SELECT COUNT(DISTINCT(t_name)) FROM Teacher WHERE t_name LIKE '%李%';
②： SELECT COUNT(*)FROM teacher WHERE t_name LIKE "%李%";

-- 5、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT a.s_id,a.s_name,a.s_sex,a.s_birth FROM student a JOIN score b ON a.s_id = b.s_id WHERE b.c_id IN ('01','02') GROUP BY a.s_id HAVING COUNT(*) = 2;

SELECT a.s_id FROM score a JOIN score b ON a.s_id = b.s_id WHERE a.c_id = '01' AND b.c_id = '02';

-- 6、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT a.*
FROM student a JOIN score b ON a.s_id = b.s_id
WHERE b.c_id = '01' AND a.s_id NOT IN (SELECT s_id FROM score WHERE c_id = '02');

-- 7、查询没有学全所有课程的同学的信息
SELECT s_id,COUNT()
FROM score
GROUP BY s_id
HAVING COUNT() != (SELECT COUNT(*) FROM course);

-- 8、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT * FROM student
WHERE s_id IN(SELECT DISTINCT s_id FROM score WHERE c_id IN (SELECT c_id FROM score WHERE s_id = '01'));

-- 9、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s_name
FROM student
WHERE s_id NOT IN (SELECT s_id FROM score WHERE c_id IN (SELECT c_id FROM teacher a JOIN course b ON a.t_id = b.t_id WHERE a.t_name = '张三'));

-- 10、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT a.s_id,b.s_name,AVG(a.s_score)
FROM score a JOIN student b ON a.s_id = b.s_id
WHERE a.s_id IN (SELECT s_id FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT(*) >= 2)
GROUP BY a.s_id;

-- 1.查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数
SELECT a.deptno,a.dname,a.loc,COUNT(*)
FROM dept a JOIN emp b ON a.deptno = b.deptno
GROUP BY a.deptno;

-- 2.列出薪资比e高的所有员工
SELECT sal FROM emp WHERE ename='e'
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='e');

-- 3.列出所有员工的姓名及其直接上级的姓名
SELECT * FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'e');

-- 4.由于公司架构的更新，所有的文员都隶属于mgr='1009'下，请更新员工信息表
UPDATE emp SET mgr='1009' WHERE job='文员';

-- 5.列出薪资高于公司平均薪资的所有员工信息，所在部门名称，上级领导，工资等级
SELECT a.ename 员工姓名,b.ename 直接上级姓名 FROM emp a LEFT JOIN emp b ON a.mgr = b.empno

SELECT b.empno,b.ename,b.job,a.dname,
(SELECT ename FROM emp WHERE empno = IFNULL(b.mgr,'1009')) 上级姓名,
(SELECT grade FROM salgrade WHERE b.sal BETWEEN lowsal AND hisal) 薪资等级
FROM dept a JOIN emp b ON a.deptno = b.deptno
WHERE b.sal > (SELECT AVG(sal) FROM emp);