mysql平台workb_HOMEWORK (Mysql）

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;