mysql练习1

-- -- 第一题：将所有的课程的名称以及对应的任课老师姓名打印出来
SELECT cname,tname FROM course INNER JOIN teacher ON course.teacher_id=teacher.tid

-- -- 第二题：查询学生表中男女生各有多少人？
SELECT gender,COUNT(sid) FROM student GROUP BY gender

-- -- 第三题：查询物理成绩等于100的学生的姓名
SELECT sid,sname FROM student WHERE sid in (SELECT student_id FROM score WHERE course_id=2 AND num=100)

-- -- 第四题：查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT sname 姓名,平均成绩 FROM student INNER JOIN (SELECT student_id,AVG(num) 平均成绩 FROM score GROUP BY student_id HAVING AVG(num)>80)AS s ON student.sid=s.student_id

-- -- 第五题：查询所有学生的学号，姓名，选课数，总成绩
SELECT sid,sname,选课数,总成绩 FROM student LEFT JOIN (SELECT student_id,COUNT(sid) 选课数,SUM(num) 总成绩 FROM score GROUP BY student_id)AS s ON student.sid=s.student_id

-- -- 第六题：查询姓李老师的个数
SELECT COUNT(tid) FROM teacher WHERE tname LIKE '李%'

-- -- 第七题：查询没有报李平老师课的学生姓名
SELECT sid,sname FROM student WHERE sid NOT in (SELECT DISTINCT  student_id FROM score WHERE course_id in (SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tname="李平老师")))

-- -- 第八题：查询物理课程比生物课程高的学生的学号
SELECT s1.student_id FROM (SELECT * FROM score WHERE student_id in (SELECT student_id FROM score WHERE course_id=(SELECT cid FROM course WHERE cname="物理") OR course_id==(SELECT cid FROM course WHERE cname="生物") GROUP BY student_id HAVING COUNT(sid)=2) AND course_id=(SELECT cid FROM course WHERE cname="生物"))AS s1
LEFT JOIN
(SELECT * FROM score WHERE student_id in (SELECT student_id FROM score WHERE course_id==(SELECT cid FROM course WHERE cname="物理") OR course_id=(SELECT cid FROM course WHERE cname="生物") GROUP BY student_id HAVING COUNT(sid)=2) AND course_id==(SELECT cid FROM course WHERE cname="物理"))AS s2
ON s1.student_id=s2.student_id
WHERE s1.num<s2.num

-- -- 第九题：查询没有同时选修物理课程和体育课程的学生姓名
SELECT sid,sname FROM student WHERE sid NOT in (SELECT student_id FROM score WHERE course_id=2 or course_id=3
GROUP BY student_id HAVING COUNT(course_id)=2)

-- -- 第十题：查询挂科超过两门(包括两门)的学生姓名和班级
SELECT sname,caption FROM class
INNER JOIN
(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))AS s
ON class.cid=s.class_id

-- -- 第十一题：查询选修了所有课程的学生姓名
SELECT sname FROM student WHERE sid in (SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)=(SELECT COUNT(cid) FROM course))

-- -- 第十二题：查询李平老师教的课程的所有成绩记录
SELECT course_id 课程,num 分数 FROM score WHERE course_id in(SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tname="李平老师"))

-- -- 第十三题：查询全部学生都选修了的课程号和课程名
SELECT course_id,COUNT(course_id) FROM score GROUP BY course_id HAVING COUNT(course_id)=(SELECT COUNT(DISTINCT student_id) FROM score)

SELECT COUNT(DISTINCT student_id) FROM score