作业32

数据在作业31

1、查询所有的课程的名称以及对应的任课老师姓名

SELECT cname,tname FROM course INNER JOIN teacher ON course.teacher_id=teacher.tid ;

2、查询学生表中男女生各有多少人

SELECT gender,COUNT(sid) FROM student GROUP BY gender;

3、查询物理成绩等于100的学生的姓名

SELECT sname FROM student WHERE sid in (SELECT student_id FROM score WHERE course_id in (SELECT cid FROM course WHERE cname='物理') and num = 100);

4、查询平均成绩大于八十分的同学的姓名和平均成绩

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)>80) AS t1 ON  student.sid = t1.student_id;

5、查询所有学生的学号，姓名，选课数，总成绩

SELECT t1.student_id as '学号',student.sname as '名字',t1.score as '总成绩',t1.num as '选课数'  FROM student INNER JOIN (SELECT student_id,SUM(num) as score,COUNT(course_id) as num FROM score GROUP BY student_id) as t1 on student.sid = t1.student_id;

6、查询姓李老师的个数

SELECT COUNT(tid) FROM teacher WHERE teacher.tname LIKE '李%';

7、查询没有报李平老师课的学生姓名

SELECT * FROM student WHERE sid not in (SELECT DISTINCT score.student_id FROM score WHERE course_id in (SELECT cid FROM course WHERE teacher_id in (SELECT tid FROM teacher WHERE tname = '李平老师')));

8、查询物理课程比生物课程高的学生的学号

SELECT
	id1 
FROM
	( SELECT student_id AS id1, score.num AS phy_score FROM score WHERE course_id = ( SELECT cid FROM course WHERE cname = '物理' ) ) AS t1
	INNER JOIN ( SELECT student_id AS id2, score.num AS bio_score FROM score WHERE course_id = ( SELECT cid FROM course WHERE cname = '生物' ) ) AS t2 ON t1.id1 = t2.id2 
WHERE
	phy_score > bio_score;

9、查询没有同时选修物理课程和体育课程,只选了其中一门的学生姓名

SELECT student.sname FROM student INNER JOIN (SELECT student_id FROM score WHERE course_id IN (SELECT cid FROM course WHERE course.cname in ('体育','物理')) GROUP BY student_id HAVING COUNT(course_id)=1) as t1 on student.sid = t1.student_id;

10、查询挂科超过两门(包括两门)的学生姓名和班级

SELECT student.sname,class.caption FROM student INNER JOIN class on student.class_id = class.cid 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) = (SELECT COUNT(cid) FROM course));

12、查询李平老师教的课程的所有成绩记录

SELECT student.sname,t1.course_ID,t1.num FROM student INNER JOIN (SELECT * FROM score WHERE course_id in (SELECT cid FROM course WHERE teacher_id in (SELECT tid FROM teacher WHERE tname = '李平老师'))) AS t1 ON student.sid = t1.student_id;

13、查询全部学生都选修了的课程号和课程名

SELECT
	course.cid,
	cname 
FROM
	course 
WHERE
	cid IN ( SELECT course_id FROM score GROUP BY course_id HAVING COUNT( sid ) = ( SELECT COUNT( student.sid ) FROM student ) );

14、查询每门课程被选修的次数

SELECT course_id,COUNT(sid) FROM score GROUP BY course_id;

15、查询只选修了一门课程的学生姓名和学号

SELECT sid,sname FROM student WHERE sid in (SELECT student_id FROM score GROUP BY student_id HAVING COUNT(sid) = 1);

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

SELECT DISTINCT num FROM score ORDER BY num DESC;

17、查询平均成绩大于85的学生姓名和平均成绩

SELECT sname,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 = (SELECT cid FROM course WHERE cname = '生物') and num<60) as t1 on student.sid = t1.student_id;

SELECT
    sname 姓名,
    num 生物成绩
FROM
    score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE
    course.cname = '生物'
AND score.num < 60;

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓

名

SELECT sname FROM student WHERE sid = (SELECT student_id FROM score WHERE course_id in (SELECT cid FROM course WHERE teacher_id in (SELECT tid FROM teacher WHERE tname = '李平老师')) GROUP BY student_id ORDER BY AVG(num) DESC LIMIT 1);

20、查询每门课程成绩最好的前两名学生姓名

# 得到学生id
SELECT
	* 
FROM
	score
	INNER JOIN (
	SELECT
		t1.course_id AS course_id,
		t1.f_num AS f_num,
		t2.s_num AS s_num 
	FROM
		(
		SELECT
			score.course_id AS course_id,
			max( num ) AS s_num 
		FROM
			score
			INNER JOIN ( SELECT course_id, max( num ) AS f_num FROM score GROUP BY course_id ) AS t ON score.course_id = t.course_id 
		WHERE
			score.num < t.f_num 
		GROUP BY
			course_id 
		) AS t2
		INNER JOIN ( SELECT course_id, max( num ) AS f_num FROM score GROUP BY course_id ) AS t1 ON t2.course_id = t1.course_id 
	) AS t3 ON score.course_id = t3.course_id 
WHERE num BETWEEN t3.s_num and t3.f_num

# 所有前两名的学生姓名
SELECT
	sname 
FROM
	student 
WHERE
	sid IN (
	SELECT
		student_id 
	FROM
		score
		INNER JOIN (
		SELECT
			t1.course_id AS course_id,
			t1.f_num AS f_num,
			t2.s_num AS s_num 
		FROM
			(
			SELECT
				score.course_id AS course_id,
				max( num ) AS s_num 
			FROM
				score
				INNER JOIN ( SELECT course_id, max( num ) AS f_num FROM score GROUP BY course_id ) AS t ON score.course_id = t.course_id 
			WHERE
				score.num < t.f_num 
			GROUP BY
				course_id 
			) AS t2
			INNER JOIN ( SELECT course_id, max( num ) AS f_num FROM score GROUP BY course_id ) AS t1 ON t2.course_id = t1.course_id 
		) AS t3 ON score.course_id = t3.course_id 
	WHERE
		num BETWEEN t3.s_num 
	AND t3.f_num 
	)

21、查询不同课程但成绩相同的学号，课程号，成绩

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

24、任课最多的老师中学生单科成绩最高的学生姓名