-- 1、查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
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、查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績
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、查詢學(xué)過"張三"老師授課的學(xué)生的信息
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、查詢"李"姓老師的數(shù)量
①： SELECT COUNT(DISTINCT(t_name)) FROM Teacher WHERE t_name LIKE '%李%';
②： SELECT COUNT(*)FROM teacher WHERE t_name LIKE "%李%";

-- 5、查詢學(xué)過編號為"01"并且也學(xué)過編號為"02"的課程的同學(xué)的信息
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、查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息
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、查詢沒有學(xué)全所有課程的同學(xué)的信息
SELECT s_id,COUNT()
FROM score
GROUP BY s_id
HAVING COUNT() != (SELECT COUNT(*) FROM course);

-- 8、查詢至少有一門課與學(xué)號為"01"的同學(xué)所學(xué)相同的同學(xué)的信息
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、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
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、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號，姓名及其平均成績
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.查出至少有一個員工的部門，顯示部門編號、部門名稱、部門位置、部門人數(shù)
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.由于公司架構(gòu)的更新，所有的文員都隸屬于mgr='1009'下，請更新員工信息表
UPDATE emp SET mgr='1009' WHERE job='文員';

-- 5.列出薪資高于公司平均薪資的所有員工信息，所在部門名稱，上級領(lǐng)導(dǎo)，工資等級
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);