SQL练习
Ele_sorriu
这个作者很懒,什么都没留下…
展开
-
-- 1、查询“01“课程比“02“课程成绩高的学生的信息及课程分数
SELECT st.*,sc.s_score AS '语文',sc2.s_score AS '数学'FROM student stLEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01'LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02'WHERE sc.s_score>sc2.s_score原创 2020-10-04 21:04:41 · 20169 阅读 · 0 评论 -
-- 2、查询“01“课程比“02“课程成绩低的学生的信息及课程分数
SELECT st.*,sc.s_score AS '语文',sc2.s_score AS '数学'FROM student stLEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01'LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02'WHERE sc.s_score < sc2.s_score原创 2020-10-04 21:05:59 · 19216 阅读 · 0 评论 -
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT st.s_id,st.s_name,ROUND (AVG(sc.s_score),2)FROM student st LEFT JOIN score sc ON st.s_id=sc.s_idGROUP BY st.s_id HAVING AVG(sc.s_score)>=60;原创 2020-10-04 21:08:42 · 22101 阅读 · 0 评论 -
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 -- (包括有成绩的和无成绩的)
SELECTst.s_id,st.s_name,CASE WHEN AVG(sc.s_score) IS NULL THEN '无' ELSE ROUND (AVG(sc.s_score) ,2) END AS '平均分'FROM student stLEFT JOIN score sc ON sc.s_id=st.s_idGROUP BY st.s_id HAVING AVG(sc.s_score)<60 OR AVG(sc.s_score) IS NULL...原创 2020-10-04 21:10:04 · 22588 阅读 · 1 评论 -
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT st.s_id,st.s_name,COUNT(sc.c_id),CASE WHEN SUM(sc.s_score) IS NULL THEN 0.00 ELSE ROUND(SUM(sc.s_score),2) END AS '总成绩'FROM student st LEFT JOIN score sc ON sc.s_id=st.s_idGROUP BY st.s_id原创 2020-10-04 21:10:56 · 25768 阅读 · 1 评论 -
-- 6、查询“李“姓老师的数量
SELECT COUNT(1)FROM teacher tWHERE t.t_name LIKE '李%';原创 2020-10-04 21:11:41 · 30683 阅读 · 1 评论 -
-- 7、查询学过“张三“老师授课的同学的信息
SELECT st.*FROM student stLEFT JOIN score sc ON sc.s_id=st.s_idLEFT JOIN course c ON c.c_id=sc.c_idLEFT JOIN teacher t ON t.t_id=c.t_idWHERE t.t_name="张三";原创 2020-10-04 21:12:16 · 21609 阅读 · 0 评论 -
-- 8、查询没学过“张三“老师授课的同学的信息
SELECT s.* FROM student s WHERE s.s_id NOT IN(SELECT st.s_idFROM student stLEFT JOIN score sc ON sc.s_id=st.s_idLEFT JOIN course c ON c.c_id=sc.c_idLEFT JOIN teacher t ON t.t_id=c.t_idWHERE t.t_name="张三")原创 2020-10-04 21:13:11 · 19808 阅读 · 1 评论 -
-- 9、查询学过编号为“01“并且也学过编号为“02“的课程的同学的信息
SELECT st.*FROM student st INNER JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01'WHERE st.s_id IN (SELECT st2.s_idFROM student st2INNER JOIN score sc2 ON sc2.s_id=st2.s_id AND sc2.c_id='02')原创 2020-10-04 21:13:44 · 30934 阅读 · 1 评论 -
-- 10、查询学过编号为“01“但是没有学过编号为“02“的课程的同学的信息
SELECT st.*FROM student st INNER JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01'WHERE st.s_id NOT IN (SELECT st2.s_idFROM student st2INNER JOIN score sc2 ON sc2.s_id=st2.s_id AND sc2.c_id='02')原创 2020-10-04 21:14:14 · 19491 阅读 · 0 评论 -
-- 12、查询至少有一门课与学号为“01“的同学所学相同的同学的信息
SELECT DISTINCT st.*FROM student st LEFT JOIN score sc ON sc.s_id=st.s_idWHERE sc.c_id IN (SELECT sc2.c_id FROM student st2LEFT JOIN score sc2 ON sc2.s_id=st2.s_idWHERE st2.s_id ='01')原创 2020-10-05 10:28:47 · 20854 阅读 · 1 评论 -
-- 13、查询和“01“号的同学学习的课程完全相同的其他同学的信息
SELECT st.* FROM student st LEFT JOIN score sc ON sc.s_id=st.s_idGROUP BY st.s_idHAVING GROUP_CONCAT(sc.c_id) = (SELECT GROUP_CONCAT(sc2.c_id) FROM student st2LEFT JOIN score sc2 ON sc2.s_id=st2.s_idWHERE st2.s_id ='01')原创 2020-10-05 10:30:40 · 19645 阅读 · 0 评论 -
-- 14、查询没学过“张三“老师讲授的任一门课程的学生姓名
SELECT st.s_name FROM student st WHERE st.s_id NOT IN (SELECT sc.s_idFROM score sc INNER JOIN course c ON c.c_id=sc.c_idINNER JOIN teacher t ON t.t_id=c.t_id AND t.t_name="张三")原创 2020-10-05 10:31:12 · 22535 阅读 · 0 评论 -
-- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT st.s_id,st.s_name,AVG(sc.s_score) FROM student stLEFT JOIN score sc ON sc.s_id=st.s_idWHERE sc.s_id IN(SELECT sc.s_id FROM score sc WHERE sc.s_score<60 OR sc.s_score IS NULLGROUP BY sc.s_id HAVING COUNT(1)>=2)GROUP BY st.s_id原创 2020-10-05 10:31:49 · 21581 阅读 · 1 评论 -
-- 16、检索“01“课程分数小于60,按分数降序排列的学生信息
SELECT st.*,sc.s_scoreFROM student stLEFT JOIN score sc ON sc.s_id=st.s_idWHERE sc.c_id='01' AND sc.s_score<60ORDER BY sc.s_score DESC ;原创 2020-10-05 10:32:21 · 21612 阅读 · 0 评论 -
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT st.s_id,st.s_name,(CASE WHEN AVG(sc4.s_score) IS NULL THEN 0 ELSE AVG(sc4.s_score) END) "平均分",(CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END) "语文",(CASE WHEN sc2.s_score IS NULL THEN 0 ELSE sc2.s_score END) "数学",(CASE WHEN sc3.s_score原创 2020-10-05 10:32:54 · 21438 阅读 · 0 评论 -
-- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 -- 及格为>=60,中等为:70-80,优良为:80-
SELECT c.c_id,c.c_name,MAX(sc.s_score) "最高分",MIN(sc.s_score) "最低分",AVG(sc.s_score) "平均分" ,((SELECT COUNT(s_id) FROM score WHERE s_score>=60 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) "及格率",((SELECT COUNT(s_id) FROM score WHER原创 2020-10-05 10:33:37 · 24602 阅读 · 3 评论 -
-- 19、按各科成绩进行排序,并显示排名(实现不完全)
SELECT * FROM (SELECT * FROM score WHERE c_id = '01' ORDER BY s_score DESC LIMIT 0,100 ) aUNION ALL SELECT * FROM (SELECT * FROM score WHERE c_id = '02' ORDER BY s_score DESC LIMIT 0,100) bUNION ALL SELECT * FROM (SELECT * FROM score WHERE c_id = '03'原创 2020-10-05 10:34:19 · 22860 阅读 · 0 评论 -
-- 20、查询学生的总成绩并进行排名
SELECT st.s_id,st.s_name,(CASE WHEN SUM(sc.s_score) IS NULL THEN 0 ELSE SUM(sc.s_score) END) AS '总成绩'FROM student stLEFT JOIN score sc ON sc.s_id=st.s_idGROUP BY st.s_id ORDER BY SUM(sc.s_score) DESC原创 2020-10-05 10:34:46 · 22278 阅读 · 4 评论 -
-- 21、查询不同老师所教不同课程平均分从高到低显示
SELECT t.t_id,t.t_name,c.c_name,AVG(sc.s_score) FROM teacher t LEFT JOIN course c ON c.t_id=t.t_id LEFT JOIN score sc ON sc.c_id =c.c_idGROUP BY t.t_idORDER BY AVG(sc.s_score) DESC原创 2020-10-06 07:45:07 · 21139 阅读 · 0 评论 -
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT t1.* FROM(SELECT st.*,c.c_id,c.c_name,sc.s_score FROM student stLEFT JOIN score sc ON sc.s_id=st.s_idINNER JOIN course c ON c.c_id =sc.c_id AND c.c_id="01"ORDER BY sc.s_score DESC LIMIT 1,2) t1UNION ALLSELECT t2.* FROM(SELECT st.*,c.c_id,c.原创 2020-10-06 07:46:02 · 21589 阅读 · 2 评论 -
-- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT c.c_id,c.c_name ,((SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=100 AND sc.s_score>80)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id )) "100-85",((SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score&原创 2020-10-06 07:46:27 · 23200 阅读 · 0 评论 -
-- 24、查询学生平均成绩及其名次
SELECT st.s_id,st.s_name,ROUND((CASE WHEN AVG(sc.s_score) IS NULL THEN 0 ELSE AVG(sc.s_score) END),2) "avg" FROM student stLEFT JOIN score sc ON sc.s_id=st.s_idGROUP BY st.s_id ORDER BY AVG(sc.s_score) DESC原创 2020-10-06 07:47:00 · 21950 阅读 · 1 评论 -
-- 25、查询各科成绩前三名的记录
SELECT a.* FROM (SELECT st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score FROM student stLEFT JOIN score sc ON sc.s_id=st.s_idINNER JOIN course c ON c.c_id=sc.c_id AND c.c_id='01'ORDER BY sc.s_score DESC LIMIT 0,3) aUNION ALL SELECT b.* FROM (SELECT st原创 2020-10-06 07:47:48 · 21106 阅读 · 0 评论 -
-- 26、查询每门课程被选修的学生数
SELECT c.c_id,c.c_name,COUNT(1)FROM course c LEFT JOIN score sc ON sc.c_id=c.c_idGROUP BY sc.c_id原创 2020-10-06 07:48:14 · 23078 阅读 · 0 评论 -
-- 27、查询出只有两门课程的全部学生的学号和姓名
SELECT st.s_id,st.s_name FROM student st LEFT JOIN score sc ON sc.s_id=st.s_idINNER JOIN course c ON c.c_id=sc.c_id GROUP BY st.s_id HAVING COUNT(1)=2原创 2020-10-06 07:48:45 · 20965 阅读 · 0 评论 -
-- 28、查询男生、女生人数
SELECT st.s_sex,COUNT(1) FROM student st GROUP BY st.s_sex原创 2020-10-06 07:49:25 · 21609 阅读 · 0 评论 -
-- 29、查询名字中含有“风“字的学生信息
SELECT st.* FROM student st WHERE st.s_name LIKE "%风%";原创 2020-10-06 07:49:57 · 22947 阅读 · 0 评论 -
-- 30、查询同名同性学生名单,并统计同名人数
SELECT st.*,COUNT(1) FROM student st GROUP BY st.s_name,st.s_sex HAVING COUNT(1)>1原创 2020-10-06 07:50:31 · 23039 阅读 · 0 评论 -
-- 31、查询1990年出生的学生名单
SELECT st.* FROM student st WHERE st.s_birth LIKE "1990%";原创 2020-10-07 07:45:34 · 21574 阅读 · 0 评论 -
-- 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT c.c_id,c.c_name,AVG(sc.s_score) FROM course cINNER JOIN score sc ON sc.c_id=c.c_id GROUP BY c.c_id ORDER BY AVG(sc.s_score) DESC,c.c_id ASC原创 2020-10-07 07:46:05 · 24264 阅读 · 1 评论 -
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT st.s_id,st.s_name,AVG(sc.s_score) FROM student stLEFT JOIN score sc ON sc.s_id=st.s_idGROUP BY st.s_id HAVING AVG(sc.s_score)>=85原创 2020-10-07 07:46:36 · 24142 阅读 · 0 评论 -
-- 34、查询课程名称为“数学“,且分数低于60的学生姓名和分数
SELECT st.s_id,st.s_name,sc.s_score FROM student stINNER JOIN score sc ON sc.s_id=st.s_id AND sc.s_score<60INNER JOIN course c ON c.c_id=sc.c_id AND c.c_name ="数学"原创 2020-10-07 07:47:10 · 20875 阅读 · 0 评论 -
-- 35、查询所有学生的课程及分数情况;
SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student stLEFT JOIN score sc ON sc.s_id=st.s_idLEFT JOIN course c ON c.c_id =sc.c_idORDER BY st.s_id,c.c_name原创 2020-10-07 07:47:43 · 20330 阅读 · 0 评论 -
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT st2.s_id,st2.s_name,c2.c_name,sc2.s_score FROM student st2LEFT JOIN score sc2 ON sc2.s_id=st2.s_idLEFT JOIN course c2 ON c2.c_id=sc2.c_id WHERE st2.s_id IN(SELECT st.s_id FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id GROUP BY st.s_id H原创 2020-10-07 07:48:15 · 21235 阅读 · 0 评论 -
-- 37、查询不及格的课程
SELECT st.s_id,c.c_name,st.s_name,sc.s_score FROM student stINNER JOIN score sc ON sc.s_id=st.s_id AND sc.s_score<60INNER JOIN course c ON c.c_id=sc.c_id原创 2020-10-07 07:48:44 · 20444 阅读 · 0 评论 -
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT st.s_id,st.s_name,sc.s_score FROM student stINNER JOIN score sc ON sc.s_id=st.s_id AND sc.c_id="01" AND sc.s_score>=80原创 2020-10-07 07:49:19 · 25874 阅读 · 1 评论 -
-- 39、求每门课程的学生人数
SELECT c.c_id,c.c_name,COUNT(1) FROM course cINNER JOIN score sc ON sc.c_id=c.c_idGROUP BY c.c_id原创 2020-10-07 07:50:12 · 20789 阅读 · 0 评论 -
-- 40、查询选修“张三“老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT st.*,c.c_name,sc.s_score,t.t_name FROM student stINNER JOIN score sc ON sc.s_id=st.s_idINNER JOIN course c ON c.c_id=sc.c_id INNER JOIN teacher t ON t.t_id=c.t_id AND t.t_name="张三"ORDER BY sc.s_score DESCLIMIT 0,1原创 2020-10-07 07:50:42 · 21835 阅读 · 0 评论 -
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT st.s_id,st.s_name,sc.c_id,sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id=st.s_idLEFT JOIN course c ON c.c_id=sc.c_idWHERE (SELECT COUNT(1) FROM student st2 LEFT JOIN score sc2 ON sc2.s_id=st2.s_idLEFT JOIN course c2 ON c2.c_id=sc2.c_原创 2020-10-08 07:48:05 · 29705 阅读 · 6 评论