mysql经典50道题自我练习
测试数据和练习题均转载自CSDN博主@启明星的指引的文章sql语句练习50题(Mysql版),用于mysql的每日自我练习
-
表名和字段
–1.学生表 Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) --课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) --教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_score) --学生编号,课程编号,分数
-
测试数据
--建表 --学生表 CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(`s_id`) ); --课程表 CREATE TABLE `Course`( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY(`c_id`) ); --教师表 CREATE TABLE `Teacher`( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(`t_id`) ); --成绩表 CREATE TABLE `Score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) ); --插入学生表测试数据 insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙风' , '1990-05-20' , '男'); insert into Student values('04' , '李云' , '1990-08-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吴兰' , '1992-03-01' , '女'); insert into Student values('07' , '郑竹' , '1989-07-01' , '女'); insert into Student values('08' , '王菊' , '1990-01-20' , '女'); --课程表测试数据 insert into Course values('01' , '语文' , '02'); insert into Course values('02' , '数学' , '01'); insert into Course values('03' , '英语' , '03'); --教师表测试数据 insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五'); --成绩表测试数据 insert into Score values('01' , '01' , 80); insert into Score values('01' , '02' , 90); insert into Score values('01' , '03' , 99); insert into Score values('02' , '01' , 70); insert into Score values('02' , '02' , 60); insert into Score values('02' , '03' , 80); insert into Score values('03' , '01' , 80); insert into Score values('03' , '02' , 80); insert into Score values('03' , '03' , 80); insert into Score values('04' , '01' , 50); insert into Score values('04' , '02' , 30); insert into Score values('04' , '03' , 20); insert into Score values('05' , '01' , 76); insert into Score values('05' , '02' , 87); insert into Score values('06' , '01' , 31); insert into Score values('06' , '03' , 34); insert into Score values('07' , '02' , 89); insert into Score values('07' , '03' , 98);
-
练习题
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
6、查询"李"姓老师的数量
7、查询学过"张三"老师授课的同学的信息
8、查询没学过"张三"老师授课的同学的信息
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
11、查询没有学全所有课程的同学的信息
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
16、检索"01"课程分数小于60,按分数降序排列的学生信息
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
19、按各科成绩进行排序,并显示排名
mysql没有rank函数
20、查询学生的总成绩并进行排名
21、查询不同老师所教不同课程平均分从高到低显示
21、查询不同老师所教不同课程平均分从高到低显示
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
24、查询学生平均成绩及其名次
25、查询各科成绩前三名的记录
1.选出b表比a表成绩大的所有组
2.选出比当前id成绩大的 小于三个的
26、查询每门课程被选修的学生数
27、查询出只有两门课程的全部学生的学号和姓名
28、查询男生、女生人数
29、查询名字中含有"风"字的学生信息
30、查询同名同性学生名单,并统计同名人数
31、查询1990年出生的学生名单
32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
34、查询课程名称为"数学",且分数低于60的学生姓名和分数
35、查询所有学生的课程及分数情况
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
37、查询不及格的课程
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
39、求每门课程的学生人数
40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
42、查询每门功成绩最好的前两名
43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
44、检索至少选修两门课程的学生学号
45、查询选修了全部课程的学生信息
46、查询各学生的年龄
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
47、查询本周过生日的学生
48、查询下周过生日的学生
49、查询本月过生日的学生
50、查询下月过生日的学生
-
答案提交
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT t1.*, t2.s_score AS 01_score, t3.s_score AS 02_score FROM Student t1 LEFT JOIN Score t2 ON t1.s_id = t2.s_id AND t2.c_id = '01' LEFT JOIN Score t3 ON t1.s_id = t3.s_id AND t3.c_id = '02' WHERE t2.s_score > t3.s_score;
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT t1.*, t2.s_score AS 01_score, t3.s_score AS 02_score FROM Student t1 LEFT JOIN Score t2 ON t1.s_id = t2.s_id AND t2.c_id = '01' LEFT JOIN Score t3 ON t1.s_id = t3.s_id AND t3.c_id = '02' WHERE t2.s_score < t3.s_score;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
-- ROUND(x,d) 函数指四舍五入,x表示要处理的数值,d表示保留几位小数 SELECT t1.s_id, t1.s_name, round( avg( t2.s_score ), 2 ) AS avg_score FROM Student t1 LEFT JOIN Score t2 ON t1.s_id = t2.s_id GROUP BY t1.s_id HAVING avg_score >= 60;
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)
SELECT t1.s_id, t1.s_name, round( avg( t2.s_score ), 2 ) AS avg_score FROM Student t1 LEFT JOIN Score t2 ON t1.s_id = t2.s_id GROUP BY t1.s_id HAVING avg_score <= 60 UNION ALL SELECT t3.s_id, t3.s_name, 0 AS avg_score FROM Student t3 WHERE t3.s_id NOT IN ( SELECT DISTINCT ( s_id ) FROM Score );
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT t1.s_id, t1.s_name, count( t2.c_id ) courses, sum( t2.s_score ) total_score FROM Student t1 LEFT JOIN Score t2 ON t1.s_id = t2.s_id GROUP BY t1.s_id;
6、查询"李"姓老师的数量
SELECT count(*) FROM Teacher WHERE t_name LIKE '李%';
7、查询学过"张三"老师授课的同学的信息
SELECT stu.* FROM Student stu LEFT JOIN Score s ON stu.s_id = s.s_id LEFT JOIN Course c ON s.c_id = c.c_id LEFT JOIN Teacher t ON c.t_id = t.t_id WHERE t_name = "张三";
8、查询没学过"张三"老师授课的同学的信息
SELECT * FROM Student WHERE s_id NOT IN ( SELECT s_id FROM Score WHERE c_id =( ( SELECT c_id FROM Course WHERE t_id = ( SELECT t_id FROM Teacher WHERE t_name = '张三' ))))
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT t1.* FROM Student t1 LEFT JOIN Score t2 ON t1.s_id = t2.s_id LEFT JOIN Score t3 ON t1.s_id = t3.s_id WHERE t2.c_id = '01' AND t3.c_id = '02'
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT t1.* FROM Student t1 INNER JOIN ( SELECT * FROM Score WHERE s_id IN ( SELECT s_id FROM Score WHERE c_id = '01' ) AND c_id != '02' GROUP BY s_id ) t2 ON t1.s_id = t2.s_id
11、查询没有学全所有课程的同学的信息
SELECT t1.*, count( c_id ) courses FROM Student t1 LEFT JOIN Score t2 ON t1.s_id = t2.s_id GROUP BY t1.s_id HAVING courses < ( SELECT count(*) FROM Course)
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT DISTINCT ( t1.s_id ), s_name FROM Student t1 LEFT JOIN Score t2 ON t1.s_id = t2.s_id WHERE t2.c_id IN ( SELECT c_id FROM Score WHERE s_id = '01')
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT t1.* FROM Student t1 INNER JOIN ( SELECT s_id, group_concat( c_id ) group1 FROM Score WHERE s_id > '01' GROUP BY s_id ) t2 ON t1.s_id = t2.s_id INNER JOIN ( SELECT s_id, group_concat( c_id ) group2 FROM Score WHERE s_id = '01' GROUP BY s_id ) t3 ON t2.group1 = t3.group2
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s_name FROM Student WHERE s_id NOT IN ( SELECT t4.s_id FROM Score t4 RIGHT JOIN ( SELECT c_id FROM Course t1 LEFT JOIN Teacher t2 ON t1.t_id = t2.t_id WHERE t_name = '张三' ) t3 ON t3.c_id = t4.c_id )
15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT t1.s_name, avg( t2.s_score ) avg_score FROM Student t1 JOIN Score t2 ON t1.s_id IN ( SELECT s_id FROM Score WHERE s_score < 60 GROUP BY s_id HAVING count( 1 ) >= 2 ) AND t1.s_id = t2.s_id GROUP BY t1.s_id
16、检索"01"课程分数小于60,按分数降序排列的学生信息
SELECT t1.*, t2.s_score FROM Student t1 JOIN Score t2 ON t2.c_id = '01' AND t2.s_score < 60 AND t1.s_id = t2.s_id GROUP BY t2.s_id ORDER BY t2.s_score DESC
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT t2.s_id, t2.s_name, max( CASE c_id WHEN '01' THEN s_score ELSE 0 END ) chinese, max( CASE c_id WHEN '02' THEN s_score ELSE 0 END ) math, max( CASE c_id WHEN '03' THEN s_score ELSE 0 END ) english, round( avg( s_score )) avg_score FROM Score t1 LEFT JOIN Student t2 ON t1.s_id = t2.s_id GROUP BY t1.s_id ORDER BY avg_score DESC
18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90SELECT t1.c_id, t2.c_name, max( t1.s_score ) max_score, min( t1.s_score ) min_score, round( avg( t1.s_score ), 2 ) avg_score, round( 100 * ( sum( CASE WHEN t1.s_score >= 60 THEN 1 ELSE 0 END )/ sum( CASE WHEN t1.s_score THEN 1 ELSE 0 END )), 2 ) pass_rate, round( 100 * ( sum( CASE WHEN t1.s_score >= 70 AND t1.s_score < 80 THEN 1 ELSE 0 END )/ sum( CASE WHEN t1.s_score THEN 1 ELSE 0 END )), 2 ) mid_rate, round( 100 * ( sum( CASE WHEN t1.s_score >= 80 AND t1.s_score < 90 THEN 1 ELSE 0 END )/ sum( CASE WHEN t1.s_score THEN 1 ELSE 0 END )), 2 ) good_rate, round( 100 * ( sum( CASE WHEN t1.s_score >= 90 THEN 1 ELSE 0 END )/ sum( CASE WHEN t1.s_score THEN 1 ELSE 0 END )), 2 ) great_rate FROM Score t1 LEFT JOIN Course t2 ON t1.c_id = t2.c_id GROUP BY t1.c_id;
19、按各科成绩进行排序,并显示排名
SELECT * FROM ( SELECT c_id, s_score, ( SELECT count( DISTINCT ( s_score )) FROM Score s2 WHERE s2.s_score >= s1.s_score AND s2.c_id = '01' ) rank FROM Score s1 WHERE c_id = '01' ORDER BY s_score DESC ) t1 UNION ALL SELECT * FROM ( SELECT c_id, s_score, ( SELECT count( DISTINCT ( s_score )) FROM Score s2 WHERE s2.s_score >= s1.s_score AND s2.c_id = '02' ) rank FROM Score s1 WHERE c_id = '02' ORDER BY s_score DESC ) s2 UNION ALL SELECT * FROM ( SELECT c_id, s_score, ( SELECT count( DISTINCT ( s_score )) FROM Score s2 WHERE s2.s_score >= s1.s_score AND s2.c_id = '03' ) rank FROM Score s1 WHERE c_id = '03' ORDER BY s_score DESC ) s3
20、查询学生的总成绩并进行排名
SELECT s_id, s_name, total_score, ( SELECT count( DISTINCT ( total_score )) FROM ( SELECT t1.s_id, t1.s_name, ifnull( sum( t2.s_score ), 0 ) total_score FROM Student t1 LEFT JOIN Score t2 ON t1.s_id = t2.s_id GROUP BY t2.s_id ) t3 WHERE t4.total_score <= t3.total_score ) rank FROM ( SELECT t1.s_id, t1.s_name, ifnull( sum( t2.s_score ), 0 ) total_score FROM Student t1 LEFT JOIN Score t2 ON t1.s_id = t2.s_id GROUP BY t2.s_id ) t4 ORDER BY total_score DESC
21、查询不同老师所教不同课程平均分从高到低显示
SELECT t1.t_name, t2.c_name, round( avg( t3.s_score )) avg_score FROM Teacher t1 LEFT JOIN Course t2 ON t1.t_id = t2.t_id LEFT JOIN Score t3 ON t2.c_id = t3.c_id GROUP BY t3.c_id ORDER BY avg_score
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT s2.s_name, s1.* FROM ( SELECT s_id, c_id, t1.s_score, ( SELECT count( DISTINCT ( s_score )) FROM Score t2 WHERE t2.s_score >= t1.s_score AND t2.c_id = '01' ) rank FROM Score t1 WHERE t1.c_id = '01' ORDER BY t1.s_score DESC ) s1 JOIN Student s2 ON s1.s_id = s2.s_id AND s1.rank BETWEEN 2 AND 3 UNION SELECT s4.s_name, s3.* FROM ( SELECT s_id, c_id, t1.s_score, ( SELECT count( DISTINCT ( s_score )) FROM Score t2 WHERE t2.s_score >= t1.s_score AND t2.c_id = '02' ) rank FROM Score t1 WHERE t1.c_id = '02' ORDER BY t1.s_score DESC ) s3 JOIN Student s4 ON s3.s_id = s4.s_id AND s3.rank BETWEEN 2 AND 3 UNION SELECT s6.s_name, s5.* FROM ( SELECT s_id, c_id, t1.s_score, ( SELECT count( DISTINCT ( s_score )) FROM Score t2 WHERE t2.s_score >= t1.s_score AND t2.c_id = '03' ) rank FROM Score t1 WHERE t1.c_id = '03' ORDER BY t1.s_score DESC ) s5 JOIN Student s6 ON s5.s_id = s6.s_id AND s5.rank BETWEEN 2 AND 3
23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT t2.c_id, t1.c_name, sum( CASE WHEN t2.s_score <= 100 AND t2.s_score > 85 THEN 1 ELSE 0 END ) AS '80-100', round( 100 * ( sum( CASE WHEN t2.s_score <= 100 AND t2.s_score > 85 THEN 1 ELSE 0 END ) / count(*)), 2 ) AS '80-100-rate', sum( CASE WHEN t2.s_score <= 85 AND t2.s_score > 70 THEN 1 ELSE 0 END ) AS '70-85', round( 100 * ( sum( CASE WHEN t2.s_score <= 85 AND t2.s_score > 70 THEN 1 ELSE 0 END ) / count(*)), 2 ) AS '70-85-rate', sum( CASE WHEN t2.s_score <= 70 AND t2.s_score > 60 THEN 1 ELSE 0 END ) AS '60-70', round( 100 * ( sum( CASE WHEN t2.s_score <= 70 AND t2.s_score > 60 THEN 1 ELSE 0 END ) / count(*)), 2 ) AS '60-70-rate', sum( CASE WHEN t2.s_score <= 60 AND t2.s_score >= 0 THEN 1 ELSE 0 END ) AS '0-60', round( 100 * ( sum( CASE WHEN t2.s_score <= 60 AND t2.s_score >= 0 THEN 1 ELSE 0 END ) / count(*)), 2 ) AS '0-60-rate' FROM Course t1 LEFT JOIN Score t2 ON t1.c_id = t2.c_id GROUP BY t2.c_id;
24、查询学生平均成绩及其名次(MySQL版)
SELECT t1.s_id, t1.avg_score, ( SELECT count( DISTINCT ( t2.avg_score )) FROM ( SELECT s_id, round( avg( s_score )) avg_score FROM Score GROUP BY s_id ) t2 WHERE t2.avg_score >= t1.avg_score ) rank FROM ( SELECT s_id, round( avg( s_score )) avg_score FROM Score GROUP BY s_id ) t1 ORDER BY rank
24、查询学生平均成绩及其名次(Hive版)
SELECT s2.s_id, s2.s_name, s2.avg_score, ROW_NUMBER() OVER (ORDER BY avg_score DESC) rank FROM( SELECT s1.s_id s_id, stu.s_name s_name, ROUND(AVG(s_score),2) avg_score FROM Score s1 LEFT JOIN Student stu ON stu.s_id = s1.s_id GROUP BY s1.s_id,stu.s_name ORDER BY avg_score DESC ) s2;
25、查询各科成绩前三名的记录(MySQL版)
SELECT t1.* FROM Score t1 LEFT JOIN Score t2 ON t1.c_id = t2.c_id AND t1.s_score < t2.s_score GROUP BY t1.s_id, t1.c_id, t1.s_score HAVING count( t2.s_id ) < 3 ORDER BY t1.c_id, t1.s_score DESC
25、查询各科成绩前三名的记录(HiveSQL版)
SELECT c_name, s_name, s_score, rank FROM( SELECT c_id, s_id, s_score, ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) rank FROM Score ) tbl1 LEFT JOIN Student stu ON stu.s_id = tbl1.s_id LEFT JOIN Course c ON tbl1.c_id = c.c_id WHERE rank BETWEEN 1 AND 3;
26、查询每门课程被选修的学生数
SELECT c_id, count(*) FROM Score GROUP BY c_id
27、查询出只有两门课程的全部学生的学号和姓名
SELECT t1.s_id, t2.s_name FROM Score t1 LEFT JOIN Student t2 ON t1.s_id = t2.s_id GROUP BY s_id HAVING count(*) = 2
28、查询男生、女生人数
SELECT s_sex, count(*) FROM Student GROUP BY s_sex
29、查询名字中含有"风"字的学生信息
SELECT * FROM Student WHERE s_name LIKE '%风%';
30、查询同名同性学生名单,并统计同名人数
SELECT t1.s_name, t1.s_sex, count(*) FROM Student t1 LEFT JOIN Student t2 ON t1.s_name = t2.s_name AND t1.s_id != t2.s_id AND t1.s_sex = t2.s_sex GROUP BY t1.s_name, t1.s_sex
31、查询1990年出生的学生名单
SELECT s_name, s_birth FROM Student WHERE s_birth LIKE '1990%'
32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT c_id, round( avg( s_score )) avg_score FROM Score GROUP BY c_id ORDER BY avg_score DESC, c_id ASC
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT t1.s_id, t2.s_name, avg( t1.s_score ) avg_score FROM Score t1 LEFT JOIN Student t2 ON t1.s_id = t2.s_id GROUP BY t1.s_id HAVING avg_score >= 85
34、查询课程名称为"数学",且分数低于60的学生姓名和分数
SELECT t2.c_name, t3.s_name, t1.s_score FROM Score t1 JOIN Course t2 ON t2.c_name = '数学' AND t1.c_id = t2.c_id JOIN Student t3 ON t1.s_id = t3.s_id AND t1.s_score < 60;
35、查询所有学生的课程及分数情况
SELECT t2.c_name, t1.s_id, t1.s_name, t3.s_score FROM Student t1 LEFT JOIN Score t3 ON t1.s_id = t3.s_id LEFT JOIN Course t2 ON t2.c_id = t3.c_id
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT t2.c_name, t1.s_id, t1.s_name, t3.s_score FROM Student t1 JOIN Score t3 ON t1.s_id = t3.s_id AND t3.s_score > 70 LEFT JOIN Course t2 ON t2.c_id = t3.c_id
37、查询不及格的课程
SELECT t1.c_name FROM Course t1 JOIN Score t2 ON t1.c_id = t2.c_id AND t2.s_score < 60
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT t1.s_id, t2.s_name, t1.s_score FROM Score t1 JOIN Student t2 ON t1.s_id = t2.s_id AND t1.s_score > 80 AND t1.c_id = '01';
39、求每门课程的学生人数
SELECT c_id, count(*) FROM Score GROUP BY c_id
40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT s1.*, s2.s_score FROM Student s1 RIGHT JOIN ( SELECT s_id, t1.c_id, max( s_score ) s_score FROM Score t1 LEFT JOIN Course t2 ON t1.c_id = t2.c_id LEFT JOIN Teacher t3 ON t2.t_id = t3.t_id AND t3.t_name = '张三' ) s2 ON s1.s_id = s2.s_id;
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT t1.s_id, t1.c_id, t1.s_score FROM Score t1 LEFT JOIN Score t2 ON t1.c_id = t2.c_id AND t1.s_score = t2.s_score WHERE t1.s_id != t2.s_id UNION SELECT t3.s_id, t3.c_id, t3.s_score FROM Score t3 LEFT JOIN Score t4 ON t3.s_id = t4.s_id AND t3.s_score = t4.s_score WHERE t3.c_id != t4.c_id
42、查询每门功成绩最好的前两名
SELECT t1.s_id, t1.c_id, t1.s_score FROM Score t1 WHERE ( SELECT count( 1 ) FROM Score t2 WHERE t1.c_id = t2.c_id AND t2.s_score >= t1.s_score ) <= 2 ORDER BY t1.c_id
43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT c_id, count(*) counts FROM Score GROUP BY c_id HAVING counts > 5 ORDER BY counts DESC, c_id ASC
44、检索至少选修两门课程的学生学号
SELECT s_id FROM Score GROUP BY s_id HAVING count(*) >= 2
45、查询选修了全部课程的学生信息
SELECT t2.* FROM Score t1 LEFT JOIN Student t2 ON t1.s_id = t2.s_id GROUP BY t1.s_id HAVING count(*) = ( SELECT count( DISTINCT ( c_id )) FROM Course)
46、查询各学生的年龄
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一SELECT s_name, s_birth, date_format( now(), '%Y' ) - date_format( s_birth, '%Y' ) - ( CASE WHEN date_format( now(), '%m%d' ) > date_format( s_birth, '%m%d' ) THEN 0 ELSE 1 END ) age FROM Student
47、查询本周过生日的学生
SELECT s_name FROM Student WHERE WEEK ( date_format( now(), '%Y%m%d' )) = WEEK (s_birth)
48、查询下周过生日的学生
SELECT s_name FROM Student WHERE ( WEEK ( date_format( now(), '%Y%m%d' )) + 1 ) = WEEK (s_birth)
49、查询本月过生日的学生
SELECT s_name FROM Student WHERE MONTH (date_format( now(), '%Y%m%d' )) = MONTH (s_birth)
50、查询下月过生日的学生
SELECT s_name FROM Student WHERE ( MONTH ( date_format( now(), '%Y%m%d' )) + 1 ) = MONTH (s_birth)