SQL语句练习

-- 1、查询所有的课程的名称以及对应的任课老师姓名
SELECT course.teacher_id, course.cname, teacher.tname FROM course LEFT JOIN teacher ON course.teacher_id=teacher.tid;

-- 2、查询学生表中男生有多少人
SELECT student.gender, count(student.gender) FROM student WHERE student.gender='男';

-- 3、查询物理成绩等于100的学生的姓名、
-- 思路：
-- a.先查询物理学科的ID
SELECT course.cid FROM course WHERE course.cname='物理';
-- b.查询score表中等于100的学生ID
SELECT score.student_id FROM score WHERE score.num=100 AND score.course_id=(SELECT course.cid FROM course WHERE course.cname='物理')
-- c.拿到学生ID查询student表中学生的名字
SELECT student.sname FROM student WHERE student.sid IN (SELECT score.student_id FROM score WHERE score.num=100 AND score.course_id=(SELECT course.cid FROM course WHERE course.cname='物理'))

-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
-- 思路：
-- a.先查询每个学生的平均成绩大于80的
SELECT score.student_id, avg(score.num) FROM score GROUP BY score.student_id HAVING avg(score.num)>80;
-- b.根据上面结果的学生ID查询在student表中查询姓名
-- 把a的语句结果当做一张结果表，利用inner jion链表查询相同的学生ID，查询学生名字
SELECT student.sid, student.sname FROM student INNER JOIN (SELECT score.student_id, avg(score.num) FROM score GROUP BY score.student_id HAVING avg(score.num)>80) AS T1 ON T1.student_id=student.sid

-- 5、查询所有学生的学号，姓名，选课数，总成绩(详细看下)
-- 思路：
-- a.先查询每个学生总成绩和选课数
SELECT score.student_id, SUM(score.num) AS sum_num, COUNT(score.course_id) AS course_num FROM score GROUP BY score.student_id;
-- b.查询学生的学号和姓名
SELECT student.sid,student.sname FROM student;
-- c.最终结果
SELECT sid, sname,sum_num, course_num FROM (SELECT student.sid,student.sname FROM student) AS T1 LEFT JOIN (SELECT score.student_id, SUM(score.num) AS sum_num, COUNT(score.course_id) AS course_num FROM score GROUP BY score.student_id) AS T2 ON T1.sid=T2.student_id

-- 6、查询姓李老师的个数(看似简单，实际有点绕)
SELECT COUNT(teacher.tname) AS teacher_num FROM teacher WHERE teacher.tname LIKE '李%'

-- 7、查询没有报李平老师课的学生姓名
-- 思路：
-- a.查询李平老师的tid
SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师' ;
-- b.根据a查询李平老师教的课程id
SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师');
-- c.根据b查询报名李平老师课程的学生ID
SELECT score.student_id FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师'));
-- d.根据c学生ID 查询学生表里不在c结果里的学生ID
SELECT student.sname FROM student WHERE student.sid NOT IN (SELECT score.student_id FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师')));

-- 8、查询物理课程成绩比生物课程成绩高的学生的学号
-- 思路：
-- a.查询等于物理或等于生物的学科ID
SELECT course.cid FROM course WHERE course.cname='物理';
SELECT course.cid FROM course WHERE course.cname='生物';
-- b.拿到a中的学科ID在score中查询对应的学生的物理成绩和生物成绩
SELECT score.student_id, score.course_id, score.num FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.cname='物理');
SELECT score.student_id, score.course_id, score.num FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.cname='生物');

-- c.将物理成绩表当作基表进行左查询物理成绩大于生物成绩的
SELECT T1.student_id,T1.num AS '物理',T2.num AS '生物' FROM (SELECT score.student_id, score.course_id, score.num FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.cname='物理')) AS T1 LEFT JOIN (SELECT score.student_id, score.course_id, score.num FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.cname='生物')) AS T2 ON T1.student_id=T2.student_id WHERE T1.num>T2.num

-- 9、查询没有同时选修物理课程和体育课程的学生姓名（严谨一点是：查询只选一门物理或者体育课程的学生姓名，两门都选的与两门都不选的学生都不算！！！）
-- 思路：
-- a.先查绚物理课id和体育课id
SELECT course.cid FROM course WHERE course.cname='物理' OR course.cname='体育';
-- b.查询选择a结果的学生ID
SELECT score.student_id FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.cname='物理' OR course.cname='体育') GROUP BY score.student_id HAVING COUNT(score.course_id)<2
-- c.根据b的学生ID在学生表里查询学生姓名
SELECT student.sname FROM student WHERE student.sid IN (SELECT score.student_id FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.cname='物理' OR course.cname='体育') GROUP BY score.student_id HAVING COUNT(score.course_id)<2)

-- 10、查询挂科超过两门(包括两门)的学生姓名和班级
-- 思路：
-- a.先查询成绩低于60分的信息
SELECT score.student_id, score.course_id, score.num FROM score WHERE score.num < 60;
-- b.根据a的结果查看出现两次的学生ID
SELECT T1.student_id FROM (SELECT score.student_id, score.course_id, score.num FROM score WHERE score.num < 60) AS T1 GROUP BY T1.student_id HAVING COUNT(T1.student_id) >=2;
-- c.根据b的学生ID结果查询student表中的学生名
SELECT student.sname FROM student WHERE student.sid=(SELECT T1.student_id FROM (SELECT score.student_id, score.course_id, score.num FROM score WHERE score.num < 60) AS T1 GROUP BY T1.student_id HAVING COUNT(T1.student_id) >=2)

-- 11、查询选修了所有课程的学生姓名
-- 思路：
-- a.查询所有学生的选修数量
SELECT score.student_id, score.course_id, COUNT(score.student_id) AS total_num FROM score GROUP BY score.student_id HAVING total_num
-- b.查询选课数为4的信息
SELECT score.student_id, COUNT(score.student_id) AS total_num FROM score GROUP BY score.student_id HAVING total_num=4
-- c.查询学生表的学生ID和学生姓名
SELECT student.sid,student.sname FROM student
-- d.联表查询选课数为4的学生姓名
SELECT T1.sname FROM (SELECT student.sid,student.sname FROM student) AS T1 RIGHT JOIN (SELECT score.student_id, COUNT(score.student_id) AS total_num FROM score GROUP BY score.student_id HAVING total_num=4) AS T2 ON T2.student_id=T1.sid

-- 12、查询李平老师教的课程的所有成绩记录
-- 思路：
-- a.查询李平老师的ID
SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师'
-- b.查询course表中查询李平老师的课程ID
SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师')
-- c.查询score表中的成绩和课程ID
SELECT score.course_id,score.num FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师'))

-- 13、查询全部学生都选修了的课程号和课程名
-- 思路：
-- a.先查询所有学生的ID
SELECT student.sid FROM student
-- b.跟据学生ID查询选课的课程ID，没选的为空
SELECT student.sid, student.sname, coures_student_table.course_id FROM student LEFT JOIN (SELECT score.course_id, score.student_id FROM score) AS coures_student_table ON coures_student_table.student_id=student.sid
-- c.根据b的课程ID查询课程
SELECT course.cid, T1.sname, course.cname FROM course INNER JOIN (SELECT student.sid, student.sname, coures_student_table.course_id FROM student LEFT JOIN (SELECT score.course_id, score.student_id FROM score) AS coures_student_table ON coures_student_table.student_id=student.sid) AS T1 ON course.cid=T1.course_id

-- 14、查询每门课程被选修的次数
-- 思路：
-- a.先查询score表中每个course_id出现的次数
SELECT score.course_id,COUNT(score.course_id) FROM score GROUP BY score.course_id
-- b.再根据a中的course_id查询course中对应的科目名
SELECT course.cid,course.cname,cid_cname_t.course_id_num FROM course INNER JOIN (SELECT score.course_id,COUNT(score.course_id) AS course_id_num FROM score GROUP BY score.course_id) AS cid_cname_t ON cid_cname_t.course_id=course.cid

-- 15、查询只选修了一门课程的学生姓名和学号
-- 思路：
-- a.查询score中student_id只出现过一次
SELECT score.student_id, COUNT(score.student_id) FROM score GROUP BY score.student_id HAVING COUNT(score.student_id)=1
-- b.根据a中的student_id在student表中查询学生名
SELECT student.sid,student.sname,T1.student_id_count FROM student INNER JOIN (SELECT score.student_id, COUNT(score.student_id) AS student_id_count FROM score GROUP BY score.student_id HAVING student_id_count=1) AS T1 ON T1.student_id=student.sid

-- 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
-- 思路：
-- a.查询score表中成绩排序并去重
SELECT DISTINCT(score.num) FROM score ORDER BY -score.num

-- 17、查询平均成绩大于85的学生姓名和平均成绩
-- 思路：
-- a.先把所有学生的平均成查出来
SELECT score.student_id, AVG(score.num) AS avg_num FROM score GROUP BY score.student_id HAVING avg_num>85;
-- b.根据a中的student_id查询学生的姓名
SELECT student.sid, student.sname, T1.avg_num FROM student INNER JOIN (SELECT score.student_id, AVG(score.num) AS avg_num FROM score GROUP BY score.student_id HAVING avg_num>85) AS T1 ON T1.student_id=student.sid;

-- 18、查询生物成绩不及格的学生姓名和对应生物分数
-- 思路：
-- a.先查询学科表的学科ID
SELECT course.cid FROM course WHERE course.cname='生物';
-- b.查询score表中学生ID和生物的成绩
SELECT score.student_id, score.num FROM score WHERE score.course_id=(SELECT course.cid FROM course WHERE course.cname='生物')
-- c.根据b结果的学生ID查询学生姓名
SELECT student.sid, student.sname, T1.num FROM student INNER JOIN (SELECT score.student_id, score.num FROM score WHERE score.course_id=(SELECT course.cid FROM course WHERE course.cname='生物')) AS T1 ON T1.student_id=student.sid WHERE T1.num < 60

-- 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
-- 思路：
-- a.先查询李老师的ID
SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师'
-- b.根据a.查询李平老师的课程ID
SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师')
-- c.根据b.查询课程ID的平均成绩和对应的学生ID
SELECT score.student_id, AVG(score.num) AS avg_num FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师')) GROUP BY score.student_id
-- d.查询最高成绩和对应的学生ID
SELECT T2.student_id, T2.avg_num FROM (SELECT score.student_id, AVG(score.num) AS avg_num FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师')) GROUP BY score.student_id) AS T2 WHERE T2.avg_num=(SELECT MAX(T1.avg_num) AS max_avg_num FROM (SELECT score.student_id, AVG(score.num) AS avg_num FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师')) GROUP BY score.student_id ) AS T1)
-- e.根据d的学生ID查询对应的学生姓名
SELECT student.sid,student.sname,T3.avg_num FROM student INNER JOIN (SELECT T2.student_id, T2.avg_num FROM (SELECT score.student_id, AVG(score.num) AS avg_num FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师')) GROUP BY score.student_id) AS T2 WHERE T2.avg_num=(SELECT MAX(T1.avg_num) AS max_avg_num FROM (SELECT score.student_id, AVG(score.num) AS avg_num FROM score WHERE score.course_id IN (SELECT course.cid FROM course WHERE course.teacher_id=(SELECT teacher.tid FROM teacher WHERE teacher.tname='李平老师')) GROUP BY score.student_id ) AS T1)) AS T3 ON T3.student_id=student.sid