/*
1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
*/
SELECT
st.*,
sc.C_id AS '01',
sc2.C_id AS '02' /*选择学生所有列,成绩表列*/
FROM
student st
LEFT JOIN sorce sc ON sc.s_id = st.s_id
AND sc.c_id = '01' /*选择条件,将课程表和学生表建立链接*/
LEFT JOIN sorce sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
WHERE
sc.S_sorce > sc2.S_sorce
/*
2 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
*/
SELECT
st.*,
sc.C_id AS '01',
sc2.C_id AS '02' /*选择学生所有列,成绩表列*/
FROM
student st
LEFT JOIN sorce sc ON sc.s_id = st.s_id
AND sc.c_id = '01' /*选择条件,将课程表和学生表建立链接*/
LEFT JOIN sorce sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
WHERE
sc.S_sorce < sc2.S_sorce
/*
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
*/
SELECT
st.s_id,
st.s_name,
avgscore
FROM
student AS st
INNER JOIN (
SELECT s_id, AVG( s_sorce )
AS avgscore
FROM score
GROUP BY s_id HAVING avgscore >= 60
)
AS s ON st.s_id = s.s_id
/*
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(去尾保留一位小数)
(包括有成绩的和无成绩的)
*/
SELECT
st.s_id,
st.s_name,
CASE
WHEN AVG( sc.s_sorce ) IS NULL THEN
' ' ELSE ROUND ( AVG( sc.s_sorce ), 1 )
END AS avgscore
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
AVG( sc.s_sorce )< 60
OR AVG( sc.s_sorce ) IS NULL
/*
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
*/
SELECT
st.s_id,
st.s_name,
COUNT( s.s_id ) AS '选课总数',
SUM( s.s_sorce ) AS '总成绩'
FROM
student st
INNER JOIN score s ON st.s_id = s.s_id
GROUP BY
st.s_id,
st.s_name
/*
6、查询"李"姓老师的数量
*/
SELECT
count( t_name )
FROM
Teacher
WHERE
t_name LIKE '李%';
/*
7、查询学过"张三"老师授课的同学的信息
*/
SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
LEFT JOIN teacher t ON t.t_id = c.t_id
WHERE
t.t_name = "张三";
/*
8、查询没学过"张三"老师授课的同学的信息
*/
SELECT
st.*
FROM
student st
WHERE
st.s_id NOT IN (
SELECT
st.s_id
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
LEFT JOIN teacher t ON t.t_id = c.t_id
WHERE
t.t_name = "张三"
/*
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
*/
SELECT
st.*
FROM
student st
WHERE
s_id IN (
SELECT
a.s_id
FROM
( SELECT * FROM score WHERE c_id = '01' ) AS a
INNER JOIN ( SELECT * FROM score WHERE c_id = '02' ) AS b ON a.s_id = b.s_id
)
/*
查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
*/
SELECT
st.*
FROM
student st
WHERE
st.s_id IN ( SELECT sc.s_id FROM score sc WHERE sc.c_id = '01' )
AND st.s_id NOT IN ( SELECT sc.s_id FROM score sc WHERE sc.c_id = '02' )
/*
11、查询没有学全所有课程的同学的信息
*/
SELECT
st.*
FROM
student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id
HAVING
COUNT( sc.s_sorce )<(
SELECT
COUNT( c_id )
FROM
course)
/*
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
*/
SELECT DISTINCT
st.*
FROM
student st
LEFT JOIN score sc ON st.s_id = sc.s_id
WHERE
c_id IN ( SELECT c_id FROM score WHERE s_id = "01" );
/*
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
*/
SELECT st.*
FROM student st
WHERE s_id IN
(SELECT s_id FROM score WHERE s_id NOT IN
(SELECT s_id FROM score WHERE c_id NOT IN
(SELECT c_id FROM score WHERE s_id='01'))
GROUP BY s_id
HAVING COUNT(*)=(SELECT COUNT(*) FROM score WHERE s_id='01') AND s_id != '01')
/*
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
*/
SELECT
st.s_name
FROM
student st
WHERE
st.s_id NOT IN (
SELECT
sc.s_id
FROM
score sc
INNER 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 = "张三"
)
/*
15、查询两名及其以上不及格课程的同学的学号,姓名及其平均成绩
*/
SELECT
st.s_id,
st.s_name,
avg( sc.s_so ) AS avgscore
FROM
Student st
INNER JOIN score sc ON st.s_id = sc.s_id
WHERE
s_sorce < 60 GROUP BY st.s_id, st.s_name HAVING count( DISTINCT c_id ) >= 2;
/*
16、检索"01"课程分数小于60,按分数降序排列的学生信息
*/
SELECT
st.*,
sc.s_sorce
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
WHERE
sc.c_id = '01'
AND sc.s_sorce < 60
ORDER BY
sc.s_sorce DESC;
/*
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
*/
SELECT
st.s_id,
st.s_name,
c.c_name,
sc.s_sorce,
avgscore
FROM
score sc,
course c,
student st,
( SELECT s_id, avg( s_sorce ) AS avgscore FROM score GROUP BY s_id ) AS a
WHERE
sc.c_id = c.c_id
AND sc.s_id = st.s_id
AND a.s_id = st.s_id
ORDER BY
avgscore DESC
/*
18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
*/
SELECT
c.c_id,
c.c_name,
MAX( sc.s_sorce ) '最大值',
MIN( sc.s_sorce ) '最小值',
AVG( sc.s_sorce ) '平均分',
SUM( CASE WHEN sc.s_sorce >= 60 THEN 1 ELSE 0 END )/ COUNT( sc.s_id ) '及格率',
SUM( CASE WHEN sc.s_sorce >= 70 AND sc.s_sorce < 80 THEN 1 ELSE 0 END )/ COUNT( sc.s_id ) '中等率',
SUM( CASE WHEN sc.s_sorce >= 80 AND sc.s_sorce < 90 THEN 1 ELSE 0 END )/ COUNT( sc.s_id ) '优良率',
SUM( CASE WHEN sc.s_sorce >= 90 THEN 1 ELSE 0 END )/ COUNT( sc.s_id ) '优秀率'
FROM
course AS c
LEFT JOIN score AS sc ON c.c_id = sc.c_id
GROUP BY
sc.c_id
/*
19、按各科成绩进行排序,并显示排名(实现不完全) -----> ※可以不做
*/
SELECT
( @i := CASE WHEN @pre_group_id = c_id THEN @i + 1 ELSE 1 END ) AS rank,
( @pre_group_id := c_id ) AS c_id,
c_name,
s_id,
s_name,
s_sorce
FROM
( SELECT @i := 0, @pre_group_id := 1 ) var
CROSS JOIN (
SELECT
c.c_id,
c.c_name,
s.s_id,
s.s_name,
s_sorce
FROM
score sc
INNER JOIN student s ON sc.s_id = s.s_id
INNER JOIN course c ON sc.c_id = c.c_id
GROUP BY
c.c_id,
s.s_id
ORDER BY
c.c_id,
s_sorce DESC
) t1;
/*
-- 20、查询学生的总成绩并进行排名 -----> ※可以不做
*/
SELECT
( @i := @i + 1 ) AS rank,
t2.*
FROM
( SELECT @i := 0 ) var
CROSS JOIN (
SELECT
s.s_id,
s.s_name,
sum_sorce
FROM
student s
INNER JOIN ( SELECT s_id, sum( s_sorce ) AS sum_sorce FROM sorce GROUP BY s_id ) t1 ON s.s_id = t1.s_id
ORDER BY
sum_sorce DESC
) t2;
/*
21、查询不同老师所教不同课程平均分从高到低显示
*/
SELECT
t.t_id,
t.t_name,
c.c_name,
AVG( sc.s_score ) AS '平均成绩'
FROM
teacher t
LEFT JOIN course c ON c.t_id = t.t_id
LEFT JOIN score sc ON sc.c_id = c.c_id
GROUP BY
t.t_id
ORDER BY
AVG( sc.s_sorce ) DESC
/*
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
*/
(SELECT st.*,sc.s_sorce FROM score sc,student st WHERE sc.s_id = st.s_id AND sc.c_id = '01' ORDER BY sc.s_sorce DESC LIMIT 1,2)
UNION ALL
(SELECT st.*,sc.s_sorce FROM score sc,student st WHERE sc.s_id = st.s_id AND sc.c_id = '02' ORDER BY sc.s_sorce DESC LIMIT 1,2)
UNION ALL
(SELECT st.*,sc.s_sorce FROM score sc,student st WHERE sc.s_id = st.s_id AND sc.c_id = '03' ORDER BY sc.s_sorce DESC LIMIT 1,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_sorce<=100 AND sc.s_sorce>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_sorce<=85 AND sc.s_sorce>70)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id )) "85-70"
,((SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_sorce<=70 AND sc.s_sorce>60)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id )) "70-60"
,((SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_sorce<=60 AND sc.s_sorce>=0)/(SELECT COUNT(1) FROM score sc WHERE sc.c_id=c.c_id )) "60-0"
FROM course c ORDER BY c.c_id
/*
24、查询学生平均成绩及其名次
*/
SELECT
st.s_id,
st.s_name,
ROUND(( CASE WHEN AVG( sc.s_sorce ) IS NULL THEN 0 ELSE AVG( sc.s_sorce ) END ), 2 ) "avg"
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
ORDER BY
AVG( sc.s_sorce ) DESC
/*
25、查询各科成绩前三名的记录
*/
SELECT
score.c_id,
course.c_name,
student.s_name,
s_sorce
FROM
score
JOIN student ON student.s_id = score.s_id
JOIN course ON score.c_id = '01'
AND course.c_id = score.c_id
ORDER BY
s_sorce DESC
LIMIT 3;
/*
26、查询每门课程被选修的学生数
*/
SELECT
course.c_name,
COUNT( score.s_id ) stucount
FROM
score
JOIN course ON score.c_id = course.c_id
GROUP BY
course.c_name
/*
27、查询出只有两门课程的全部学生的学号和姓名
*/
SELECT
student.s_id,
student.s_name
FROM
student
JOIN score ON student.s_id = score.s_id
GROUP BY
s_id
HAVING
count( c_id ) =2
/*
28、查询男生、女生人数
*/
SELECT
s_sex,
COUNT( s_id )
FROM
student
GROUP BY
s_sex
-- 29、查询名字中含有"风"字的学生信息
SELECT
*
FROM
student
WHERE
s_name LIKE '%风%'
/*
30、查询同名同性学生名单,并统计同名人数
*/
select s_name, count(*) from student
group by s_name
having count(*)>1;
select * from
(select t1.* from student t1, student t2
where t1.s_name = t2.s_name and t1.s_id != t2.s_id group by t1.s_id)
/*
31、查询1990年出生的学生名单
*/
SELECT
*
FROM
student
WHERE
YEAR ( s_birth )= '1990'
/*
32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
*/
SELECT
c.c_id,
c.c_name,
AVG( sc.s_sorce ) AS '平均成绩'
FROM
course c
INNER JOIN score sc ON sc.c_id = c.c_id
GROUP BY
c.c_id
ORDER BY
AVG( sc.s_sorce ) DESC,
c.c_id ASC
/*
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
*/
SELECT st.s_id, st.s_name, AVG(sc.s_sorce) AS '平均成绩'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
AVG( sc.s_sorce )>= 85
/*
34、查询课程名称为"数学",且分数低于60的学生姓名和分数
*/
SELECT st.s_name, st.s_id, co.c_name, sc.s_sorce
FROM
student AS st
INNER JOIN score AS sc ON st.s_id = sc.s_id
INNER JOIN course AS co ON sc.c_id = co.c_id
WHERE
co.c_name = '数学'
AND sc.s_sorce < 60
/*
35、查询所有学生的课程及分数情况;
*/
SELECT
st.s_id,
st.s_name,
MAX( CASE WHEN co.c_name = '语文' THEN sc.s_sorce ELSE NULL END ) AS '语文',
MAX( CASE WHEN co.c_name = '数学' THEN sc.s_sorce ELSE NULL END ) AS '数学',
MAX( CASE WHEN co.c_name = '英语' THEN sc.s_sorce ELSE NULL END ) AS '英语'
FROM
student AS st
LEFT JOIN score AS sc ON st.s_id = sc.s_id
LEFT JOIN course AS co ON sc.c_id = co.c_id
GROUP BY
st.s_id,
st.s_name
/*
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
*/
SELECT
s.s_name,
c.c_name,
sc.s_sorce
FROM
student s
JOIN score sc ON s.s_id = sc.s_id
JOIN course c ON sc.c_id = c.c_id
WHERE
sc.s_sorce > 70;
/*
37、查询不及格的课程
*/
SELECT
s.s_id,
c.c_name,
s.s_name,
sc.s_sorce
FROM
student s
INNER JOIN score sc ON sc.s_id = s.s_id
AND sc.s_sorce < 60
INNER JOIN course c ON c.c_id = sc.c_id;
/*
查询课程编号为01且课程成绩在80分以上的学生的学号和姓名;
*/
SELECT
s.s_id,
s.s_name,
sc.s_sorce
FROM
student s
INNER JOIN score sc ON sc.s_id = s.s_id
AND sc.c_id = 1
AND sc.s_sorce >= 80;
/*
39、求每门课程的学生人数
*/
SELECT
c_id,
COUNT( c_id )
FROM
score
GROUP BY
c_id
/*
40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
*/
SELECT
s.*,
c.c_name,
sc.s_sorce,
t.t_name
FROM
student s
INNER JOIN score sc ON sc.s_id = s.s_id
INNER 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_sorce DESC
/*
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
*/
SELECT DISTINCT
a.s_id,
a.c_id,
a.s_sorce
FROM
score a
JOIN score b ON a.s_sorce = b.s_sorce
AND a.c_id != b.c_id;
/*
42、查询每门功成绩最好的前两名
*/
SELECT
c.c_id,
c.c_name,
s.s_id,
s.s_name,
s_sorce
FROM
(
SELECT
*
FROM
score sc
WHERE
( SELECT count(*) FROM score sc1 WHERE sc.c_id = sc1.c_id AND sc.s_sorce < sc1.s_sorce ) < 2
) t1
INNER JOIN student s ON t1.s_id = s.s_id
INNER JOIN course c ON t1.c_id = c.c_id
ORDER BY
c.c_id,
s_sorce DESC;
/*
43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
*/
SELECT
a.*,
b.c_name
FROM
( SELECT c_id, count(*) count FROM score GROUP BY c_id ) a
JOIN Course b ON a.c_id = b.c_id;
/*
44、检索至少选修两门课程的学生学号
*/
SELECT
b.s_name,
a.*
FROM
(SELECT s_id, count(*) count FROM score GROUP BY s_id HAVING count >= 2) a
JOIN Student b on a.s_id = b.s_id;
/*
45、查询选修了全部课程的学生信息
*/
SELECT
a.*,
b.s_name,
b.s_birth,
b.s_sex
FROM
(SELECT s_id, COUNT(c_id) count FROM score GROUP BY s_id HAVING count = (SELECT count(*) from Course)) a
JOIN Student b on a.s_id = b.s_id;
/*
46、查询各学生的年龄
*/
SELECT
s_name,
YEAR(NOW()) - YEAR(s_birth) age
FROM
Student;
/*
47、查询本周过生日的学生
*/
SELECT s_name
FROM Student
WHERE WEEK(s_birth) = WEEK(CURRENT_DATE)
/*
48、查询下周过生日的学生
*/
SELECT s_name
FROM Student
WHERE WEEK(s_birth) = WEEK(CURRENT_DATE) + 1
/*
49、查询本月过生日的学生
*/
SELECT s_name
FROM Student
WHERE MONTH(s_birth) = MONTH(CURRENT_DATE)
/*
50、查询下月过生日的学生
*/
SELECT s_name
FROM Student
WHERE MONTH(s_birth) = MONTH(CURRENT_DATE) + 1