-- 6、查詢"李"姓老師的數(shù)量

SELECT COUNT(*) FROM teacher WHERE t_name LIKE '李%'

-- 7、查詢學(xué)過"張三"老師授課的同學(xué)的信息

SELECT st.* FROM student st WHERE EXISTS (SELECT 1 FROM score s INNER JOIN course c ON s.c_id = c.c_id 
INNER JOIN teacher t ON t.t_id = c.t_id WHERE t.t_name = '張三' AND st.`s_id` = s.`s_id`);

-- 8、查詢沒學(xué)過"張三"老師授課的同學(xué)的信息

SELECT st.* FROM student st WHERE NOT EXISTS(SELECT 1 FROM score s INNER JOIN course c ON s.c_id = c.c_id 
INNER JOIN teacher t ON t.t_id = c.t_id WHERE t.t_name = '張三' AND st.`s_id` = s.`s_id`);

-- 9、查詢學(xué)過編號(hào)為"01"并且也學(xué)過編號(hào)為"02"的課程的同學(xué)的信息

SELECT * FROM student stu WHERE EXISTS(SELECT 1 FROM score s WHERE s.`s_id` = stu.`s_id` AND s.`c_id` = '01')AND EXISTS(SELECT 1 FROM score s1 WHERE s1.`s_id`
=stu.`s_id` AND s1.`c_id` = '02');

-- 10、查詢學(xué)過編號(hào)為"01"但是沒有學(xué)過編號(hào)為"02"的課程的同學(xué)的信息

SELECT * FROM student stu WHERE EXISTS(SELECT 1 FROM score s WHERE s.`s_id` = stu.`s_id` AND s.`c_id` = '01')AND NOT EXISTS(SELECT 1 FROM score s1 WHERE s1.`s_id`
=stu.`s_id` AND s1.`c_id` = '02');

-- 11、查詢沒有學(xué)全所有課程的同學(xué)的信息

SELECT st.`s_name`,st.`s_sex`,cc.課程數(shù) FROM student st,(SELECT COUNT(1) 課程數(shù) ,s.`s_id`FROM score s INNER JOIN course c ON s.`c_id` =c.`c_id` GROUP BY s.`s_id`) AS cc WHERE st.`s_id`
= cc.s_id AND 課程數(shù) < 3 ;

-- 12、查詢至少有一門課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息

SELECT  st.* FROM student st WHERE st.`s_id`
IN(SELECT DISTINCT s.`s_id` FROM score s WHERE s.`c_id` IN (SELECT score.`c_id` FROM student INNER JOIN score ON 
student.`s_id` = score.`s_id` AND student.`s_id`='01'))

-- 13、查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

SELECT st.* FROM
(SELECT GROUP_CONCAT(s.`c_id` ORDER BY c_id) group1,s_id FROM score s  WHERE s.`s_id` > '01' GROUP BY `s_id`) t1 INNER JOIN
(SELECT GROUP_CONCAT(s.`c_id` ORDER BY c_id) group2,s_id FROM score s  WHERE s.`s_id` = '01' GROUP BY `s_id`) t2 ON t1.group1 = t2.group2
INNER JOIN student st ON st.`s_id` = t1.s_id

-- 14、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名

SELECT st.* FROM student st WHERE NOT EXISTS(SELECT 1 FROM score s INNER JOIN course c ON s.`c_id` =c.`c_id` INNER JOIN 
teacher t ON t.`t_id` = c.`t_id` AND t.`t_name` = '張三' WHERE st.`s_id` = s.`s_id`);

-- 15、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

SELECT st.* FROM student st LEFT JOIN score s ON st.`s_id` = s.`s_id` WHERE IFNULL(s.`s_score`,0) < 60 
GROUP BY st.`s_id` HAVING COUNT(1) >=2;

-- 16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息

SELECT st.* FROM student st LEFT JOIN score s ON s.`s_id`= st.`s_id` AND s.`c_id` = '01' WHERE s.`s_score` < 60 ORDER BY s.`s_score`

-- 17、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)

SELECT st.*,IFNULL(score1.s_score,0) 科目一成績(jī),IFNULL(score2.s_score,0) 科目二成績(jī),IFNULL(score3.s_score,0) 科目三成績(jī),average.平均成績(jī) FROM student st LEFT JOIN(SELECT s.`s_id`, s.`s_score` FROM score s WHERE s.`c_id` = '01') AS score1
ON st.`s_id` = score1.`s_id` LEFT JOIN 
(SELECT s.`s_id`,s.`s_score` FROM score s WHERE s.`c_id` = '02') AS score2 ON st.`s_id` = score2.`s_id`
LEFT JOIN(SELECT s.`s_id`,s.`s_score`FROM score s WHERE s.`c_id` = '03') AS score3 ON st.`s_id` = score3.`s_id`
LEFT JOIN (SELECT s_id,AVG(s_score)平均成績(jī) FROM score s GROUP BY s_id) AS average ON st.`s_id` = average.s_id
ORDER BY 平均成績(jī) DESC;