1.查詢和“1”號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號和姓名
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. 刪除學(xué)習(xí)“李迪”老師課的 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. 查詢每門課程被選修的學(xué)生數(shù)
SELECT course_id,COUNT(student_id)?FROM?score?GROUP?BY?student_id;
8.查詢出只選修了一門課程的全部學(xué)生的學(xué)號和姓名
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.查詢男生咳短、女生的人數(shù)
SELECT COUNT(student_id) FROM student GROUP BY gender;
10.查詢姓“張”的學(xué)生名單
SELECT name FROM student WHERE name LIKE '張%';
11.查詢同名同姓學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
SELECT name,COUNT(name)FROM student GROUP BY name HAVING?COUNT(name)>1;
12.查詢每門課程的平均成績执泰，結(jié)果按平均成績升序排列，平均成績相同時渡蜻，按課程號降序排列
SELECT course_id,AVG(number) FROM score GROUP BY course_id ORDER BY AVG(number),course_id DESC;
13.查詢平均成績大于85的所有學(xué)生的學(xué)號术吝、姓名和平均成績
SELECT student_id,student_name,AVG(number) FROM student JOIN score ON student.student_id=score.student_id HAVING AVG(number)>85;
14.查詢課程名稱為“體育”，且分?jǐn)?shù)低于 60的學(xué)生姓名和分?jǐn)?shù)
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分以上的學(xué)生的學(xué)號和姓名
SELECT? student_id,name FROM student JOIN score ON student.student_id = score.student_id WHERE course_id=3 AND number>80;
16.查詢選修“李迪”老師所授課程的學(xué)生中茸苇，成績最高的學(xué)生姓名及其成績
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.查詢各個課程及相應(yīng)的選修人數(shù)
SELECT course_id,COUNT(student_id) FROM score GROUP BY course_id;
18.查詢不同課程但成績相同的學(xué)生的學(xué)號排苍、課程號、學(xué)生成績
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.檢索至少選修兩門課程的學(xué)生學(xué)號
SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)>2;
21.查詢?nèi)繉W(xué)生都選修的課程號和課程名
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.查詢兩門以上不及格課程的同學(xué)的學(xué)號及其平均成績
SELECT student_id,AVG(number) FROM score GROUP BY student_id HAVING COUNT(number<60)>=2;
23.檢索“2”課程分?jǐn)?shù)小于60税弃，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號
SELECT student_id FROM score WHERE course_id=2 AND number<60 ORDER BY number DESC;
24.刪除“2”同學(xué)的“1”課程的成績
DELETE FROM score WHERE student_id=2 AND course_id=1;
