1.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名
SELECT student_id,student_name FROM student WHERE student_id IN (SELECT student_id FROM score WHERE course_id IN (SELECT course_id FROM score WHERE student_id = ‘1))’;
2. 删除学习“李迪”老师课的 SC表记录
DELETE FROM score WHERE course_id IN (SELECT course_id FROM course WHERE teacher_id IN(SELECT teacher_id FROM teacher WHERE name =”LiDi”));
3. 查询各科成绩最高和最低分：以如下形式显示：课程ID，最高分， 最低分
SELECT course_id,MAX(number) AS maxnumber,MIN(number) AS minnumber FROM score GROUP BY course_id;
4. 按各科平均成绩从低到高和及格率的百分比从高到低顺序
SELECT course_id,AVG(number)FROM score GROUP BY course_id ORDER BY AVG(number);
SELECT course_id , CONCAT (COUNT(number>=60)/COUNT(number)*100,’%’) FROM score GROUP BY course_id ORDER BY COUNT(number>=60)/COUNT(number)*100 DESC;
5. 课程平均分从高到低显示（显示任课老师姓名）
SELECT name,AVG(number)FROM teacher JOIN course ON teacher . teacher_id = course . teacher_id JOIN score ON score.course_id=course.course_id;
6. 查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT score_id,number FROM score GROUP BY course_id ORDER BY number DESC LIMIT 3;
7. 查询每门课程被选修的学生数
SELECT course_id,COUNT(student_id) FROM score GROUP BY student_id;
8.查询出只选修了一门课程的全部学生的学号和姓名
SELECT student_id,name FROM student JOIN score ON student.student_id=score.student_id GROUP BY score student_id HAVING COUNT(course_id)=1;
9.查询男生、女生的人数
SELECT COUNT(student_id) FROM student GROUP BY gender;
10.查询姓“张”的学生名单
SELECT name FROM student WHERE name LIKE '张%';
11.查询同名同姓学生名单，并统计同名人数
SELECT name,COUNT(name)FROM student GROUP BY name HAVING COUNT(name)>1;
12.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT course_id,AVG(number) FROM score GROUP BY course_id ORDER BY AVG(number),course_id DESC;
13.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT student_id,student_name,AVG(number) FROM student JOIN score ON student.student_id=score.student_id HAVING AVG(number)>85;
14.查询课程名称为“体育”，且分数低于 60的学生姓名和分数
SELECT name,number FROM student JOIN score ON student.student_id=score.student_id JOIN course ON score.course_id=course.course_id WHERE name='体育' AND number<60;
15.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
SELECT student_id,name FROM student JOIN score ON student.student_id = score.student_id WHERE course_id=3 AND number>80;
16.查询选修“李迪”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT student_name,number FROM student JOIN score ON student.student_id= score.student_id JOIN course ON course.course_id=score.course_id JOIN teacher ON course.teacher_id=teacher.teacher_id WHERE teacher.name=’LiDi’ ORDER BY number DESC LIMIT 1;
17.查询各个课程及相应的选修人数
SELECT course_id,COUNT(student_id) FROM score GROUP BY course_id;
18.查询不同课程但成绩相同的学生的学号、课程号、学生成绩
SELECT a.student_id,a.course_id,a.number FROM score a,score b WHERE a.course_id<>b.course_id AND a.number=b.number;
19.查询每门课程成绩最好的前两名
SELECT student.student_id,name FROM student JOIN score ON student.student_id= score.student_id GROUP BY course_id ORDER BY number DESC LIMIT 2;
20.检索至少选修两门课程的学生学号
SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)>2;
21.查询全部学生都选修的课程号和课程名
SELECT score.course_id,course_name FROM course JOIN score ON course.course_id= score.course_id JOIN student GROUP BY score.course_id HAVING COUNT(student_id) = COUNT(student.student_id);
22.查询两门以上不及格课程的同学的学号及其平均成绩
SELECT student_id,AVG(number) FROM score GROUP BY student_id HAVING COUNT(number<60)>=2;
23.检索“2”课程分数小于60，按分数降序排列的同学学号
SELECT student_id FROM score WHERE course_id=2 AND number<60 ORDER BY number DESC;
24.删除“2”同学的“1”课程的成绩
DELETE FROM score WHERE student_id=2 AND course_id=1;
