每日作业5/8
-- 1、查询所有的课程的名称以及对应的任课老师姓名
#SELECT course.cname,teacher.tname FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id;
-- 2、查询学生表中男女生各有多少人
#SELECT gender AS "性别",COUNT(sid) AS "人数" FROM student GROUP BY gender;
-- 3、查询物理成绩等于100的学生的姓名
#SELECT sname FROM student WHERE sid IN (SELECT student_id FROM score WHERE course_id = (SELECT cid FROM course WHERE cname="物理") AND num = 100);
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
#SELECT student.sname as "姓名",t1.avg_num AS "平均成绩" FROM student INNER JOIN (SELECT student_id,AVG(num) as avg_num FROM score GROUP BY student_id HAVING AVG(num) > 80) AS t1 ON student.sid = t1.student_id;
-- 5、查询所有学生的学号，姓名，选课数，总成绩
#SELECT student.sid AS "学号",student.sname AS "姓名",t1.count_c AS "选课数",t1.sum_num AS "总成绩" FROM student INNER JOIN (SELECT student.sid as student_id,COUNT(score.course_id) AS count_c,SUM(score.num) AS sum_num FROM score RIGHT JOIN student ON score.student_id = student.sid GROUP BY student.sid) AS t1 ON student.sid = t1.student_id;
-- 6、 查询姓李老师的个数
#SELECT COUNT(tid) FROM teacher WHERE tname LIKE "李%";
-- 7、 查询没有报李平老师课的学生姓名
#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 teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = "李平老师"));
-- 8、 查询物理课程比生物课程高的学生的学号
-- SELECT wt.student_id FROM (SELECT student_id ,num FROM score WHERE course_id IN (SELECT cid FROM course WHERE cname = "物理")) AS wt
-- INNER JOIN
-- (SELECT student_id ,num FROM score WHERE course_id IN (SELECT cid FROM course WHERE cname = "生物")) AS st ON wt.student_id = st.student_id WHERE wt.num > st.num;
-- 9、 查询没有同时选修物理课程和体育课程的学生姓名
-- SELECT t1.student_id AS "学生学号" FROM (SELECT student_id FROM score WHERE course_id IN (SELECT cid FROM course WHERE cname = "物理")) AS t1
-- INNER JOIN
-- (SELECT student_id FROM score WHERE course_id IN (SELECT cid FROM course WHERE cname = "体育")) AS t2 ON t1.student_id = t2.student_id;
-- 10、查询挂科超过两门(包括两门)的学生姓名和班级
#SELECT sname,class_id FROM student WHERE sid IN (SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING COUNT(course_id) >= 2) ;
-- 11、查询选修了所有课程的学生姓名
#SELECT sname FROM student WHERE sid IN (SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id) =4);
-- 12、查询李平老师教的课程的所有成绩记录
#SELECT * FROM score WHERE course_id IN (SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "李平老师");
-- 13、查询全部学生都选修了的课程号和课程名
#SELECT cid,cname FROM course WHERE cid IN (SELECT course_id FROM score GROUP BY course_id HAVING COUNT(student_id) = (SELECT COUNT(student.sid) FROM student));
-- 14、查询每门课程被选修的次数
#SELECT course_id,COUNT(student_id) FROM score GROUP BY course_id;
-- 15、查询之选修了一门课程的学生姓名和学号
#SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id) = 1
-- 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
-- SELECT DISTINCT
-- num
-- FROM
-- score
-- ORDER BY
-- num DESC;
-- 17、查询平均成绩大于85的学生姓名和平均成绩
#SELECT student.sname,t1.avg_num FROM student INNER JOIN (SELECT student_id,AVG(num) AS avg_num FROM score GROUP BY student_id HAVING AVG(num) > 85) as t1 ON student.sid = t1.student_id;
-- 18、查询生物成绩不及格的学生姓名和对应生物分数
#SELECT student.sname,t1.num FROM student INNER JOIN (SELECT student_id,num FROM score WHERE course_id IN (SELECT cid FROM course WHERE cname = "生物") AND num <60) as t1 ON student.sid = t1.student_id;
-- 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
-- SELECT
-- sname
-- FROM
-- student
-- WHERE
-- sid = (
-- SELECT
-- student_id
-- FROM
-- score
-- WHERE
-- course_id IN ( SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = '李平老师' )
-- GROUP BY
-- student_id
-- ORDER BY
-- AVG( num ) DESC
-- LIMIT 1
-- );
-- 20、查询每门课程成绩最好的前两名学生姓名
-- 21、查询不同课程但成绩相同的学号，课程号，成绩
-- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
--
-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
--
-- 24、任课最多的老师中学生单科成绩最高的学生姓名
