一、基本信息
1.学生表
Student(S,Sname,Sage,Ssex)
--S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
2.课程表
Course(C,Cname,T)
C 课程编号,Cname 课程名称,T 教师编号
3.教师表
Teacher(T,Tname)
T 教师编号,Tname 教师姓名
4.成绩表
SC(S,C,score)
S 学生编号,C 课程编号,score 分数
二、创建数据库
1.学生表 Student
create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
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' , '女');
2.科目表 Course
create table Course(C varchar(10),Cname nvarchar(10),T varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
3.教师表 Teacher
create table Teacher(T varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
4.成绩表 SC
create table SC(S varchar(10),C varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
50道联系题目及参考答案（若有错，请指出，谢谢！）
1.查询"01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT c.*,d.c,d.score
FROM
(
SELECT student.s,sname,c,score
FROM student,sc
WHERE student.s=sc.s AND c='01'
)
c,
(
SELECT student.s,c,score
FROM student,sc
WHERE student.s=sc.s AND c='02'
)d
WHERE c.s=d.s AND c.score >d.score;
+------+--------+------+-------+------+-------+
| s | sname | c | score | c | score |
+------+--------+------+-------+------+-------+
| 02 |钱电 | 01 | 70.0 | 02 | 60.0 |
| 04 |李云 | 01 | 50.0 | 02 | 30.0 |
+------+--------+------+-------+------+-------+
1.1查询同时存在"01 "课程和" 02 "课程的情况
SELECT A.S,A.C,B.C
FROM
(
SELECT *
FROM sc
WHERE sc.c='01'
)A
INNER JOIN
(
SELECT *
FROM sc
WHERE sc.c='02'
)B
ON A.s=B.s;
+------+------+------+
| S | C | C |
+------+------+------+
| 01 | 01 | 02 |
| 02 | 01 | 02 |
| 03 | 01 | 02 |
| 04 | 01 | 02 |
| 05 | 01 | 02 |
+------+------+------+
1.2查询存在"01 "课程但可能不存在" 02 "课程的情况(不存在时显示为NULL )
SELECT A.c,A.c,A.score,B.c,B.score
FROM
(
SELECT *
FROM sc
WHERE sc.c='01'
)A
LEFT JOIN
(
SELECT *
FROM sc
WHERE sc.c='02'
)B
ON A.s=B.s
WHERE B.c is NULL;
+------+------+-------+------+-------+
| C | C | score | C | score |
+------+------+-------+------+-------+
| 01 | 01 | 31.0 | NULL | NULL |
+------+------+-------+------+-------+
1.3查询不存在"01 "课程但存在" 02 "课程的情况
SELECT *
FROM
(
SELECT *
FROM sc
WHERE sc.c='01'
)A
RIGHT JOIN
(
SELECT *
FROM sc
WHERE sc.c='02'
)B
ON A.s=B.s
WHERE A.c is NULL;
+------+------+-------+------+------+-------+
| S | C | score | S | C | score |
+------+------+-------+------+------+-------+
| NULL | NULL | NULL | 07 | 02 | 89.0 |
+------+------+-------+------+------+-------+
2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT st.s,st.sname,avg(score)
FROM student AS st,sc
WHERE st.s=sc.s
GROUP BY st.s
HAVING avg(score)>=60;
+------+--------+------------+
| s | sname | avg(score) |
+------+--------+------------+
| 01 |赵雷 | 89.66667 |
| 02 |钱电 | 70.00000 |
| 03 |孙风 | 80.00000 |
| 05 |周梅 | 81.50000 |
| 07 |郑竹 | 93.50000 |
+------+--------+------------+
3.查询在SC表存在成绩的学生信息
SELECT st.s,st.ssex,st.sage,sc.c,sc.score
FROM student AS st,sc
WHERE st.s=sc.s
GROUP BY st.s;
+------+------+---------------------+------+-------+
| s | ssex | sage | c | score |
+------+------+---------------------+------+-------+
| 01 |男 | 1990-01-01 00:00:00 | 01 | 80.0 |
| 02 |男 | 1990-12-21 00:00:00 | 01 | 70.0 |
| 03 |男 | 1990-05-20 00:00:00 | 01 | 80.0 |
| 04 |男 | 1990-08-06 00:00:00 | 01 | 50.0|
| 05 |女 | 1991-12-01 00:00:00 | 01 | 76.0 |
| 06 |女 | 1992-03-01 00:00:00 | 01 | 31.0 |
| 07 |女 | 1989-07-01 00:00:00 | 02 | 89.0 |
+------+------+---------------------+------+-------+
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为NULL )
SELECT A.s,sname,COUNT(c)选课总数,score 总成绩
FROM student A
LEFT JOIN sc B
ON A.s=B.s
GROUP BY A.s;
+------+--------+--------------+-----------+
| s | sname |选课总数 |总成绩 |
+------+--------+--------------+-----------+
| 01 |赵雷 | 3 | 80.0 |
| 02 |钱电 | 3 | 70.0 |
| 03 |孙风 | 3 | 80.0 |
| 04 |李云 | 3 | 50.0 |
| 05 |周梅 | 2 | 76.0 |
| 06 |吴兰 | 2 | 31.0 |
| 07 |郑竹 | 2 | 89.0 |
| 08 |王菊 | 0 | NULL |
+------+--------+--------------+-----------+
4.1查有成绩的学生信息
SELECT A.s,sname,COUNT(c)选课总数,score 总成绩
FROM student A
INNER JOIN sc B
ON A.s=B.s
GROUP BY A.s ;
+------+--------+--------------+-----------+
| s | sname |选课总数 |总成绩 |
+------+--------+--------------+-----------+
| 01 |赵雷 | 3 | 80.0 |
| 02 |钱电 | 3 | 70.0 |
| 03 |孙风 | 3 | 80.0 |
| 04 |李云 | 3 | 50.0 |
| 05 |周梅 | 2 | 76.0 |
| 06 |吴兰 | 2 | 31.0 |
| 07 |郑竹 | 2 | 89.0 |
+------+--------+--------------+-----------+
5.查询「李」姓老师的数量
SELEC Tteacher.tname,COUNT(Tname)
FROM teacher
WHERE tname
LIKE '李%'
GROUP BY tname;
+--------+--------------+
| tname | COUNT(Tname) |
+--------+--------------+
|李四 | 1 |
+--------+--------------+
6.查询学过「张三」老师授课的同学的信息
SELECT student.*
FROM student
WHERE s
IN
(
SELECT s
FROM sc
WHERE c
IN
(
SELECT c
FROM course
WHERE t
IN
(
SELECT t
FROM teacher
WHERE tname ='张三'
)
)
);
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 |赵雷 | 1990-01-01 00:00:00 |男 |
| 02 |钱电 | 1990-12-21 00:00:00 |男 |
| 03 |孙风 | 1990-05-20 00:00:00 |男 |
| 04 |李云 | 1990-08-06 00:00:00 |男 |
| 05 |周梅 | 1991-12-01 00:00:00 |女 |
| 07 |郑竹 | 1989-07-01 00:00:00 |女 |
+------+--------+---------------------+------+
7.查询没有学全所有课程的同学的信息
SELECT st.*,sc.c,sc.score
FROM student AS st,sc
WHERE st.s=sc.s
GROUP BY st.s
HAVING COUNT(st.sname)< 3;
+------+--------+---------------------+------+------+-------+
| S | Sname | Sage | Ssex |c | score |
+------+--------+---------------------+------+------+-------+
| 05 |周梅 | 1991-12-01 00:00:00 |女 | 01 | 76.0 |
| 06 |吴兰 | 1992-03-01 00:00:00 |女 | 01 | 31.0 |
| 07 |郑竹 | 1989-07-01 00:00:00 |女 | 02 | 89.0 |
+------+--------+---------------------+------+------+-------+
8.查询至少有一门课与学号为"01 "的同学所学相同的同学的信息
SELECT *
FROM
(
SELECT st.*
FROM student
AS st,sc
WHERE st.s=sc.s
AND sc.c= any
(
SELECT c
FROM sc
WHERE s='01'
)
) A
GROUP BY s;
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 |赵雷 | 1990-01-01 00:00:00 |男 |
| 02 |钱电 | 1990-12-21 00:00:00 |男 |
| 03 |孙风 | 1990-05-20 00:00:00 |男 |
| 04 |李云 | 1990-08-06 00:00:00 |男 |
| 05 |周梅 | 1991-12-01 00:00:00 |女 |
| 06 |吴兰 | 1992-03-01 00:00:00 |女 |
| 07 |郑竹 | 1989-07-01 00:00:00 |女 |
+------+--------+---------------------+------+
9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
SELECT st.*
FROM student AS st,sc
WHERE st.s=sc.s
GROUP BY st.s
HAVING st.s<>'01'
AND sum(c)=
(
SELECT sum(c)
FROM sc
WHERE s='01'
GROUP BY s
);
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 02 |钱电 | 1990-12-21 00:00:00 |男 |
| 03 |孙风 | 1990-05-20 00:00:00 |男 |
| 04 |李云 | 1990-08-06 00:00:00 |男 |
+------+--------+---------------------+------+
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT st.*
FROM student AS st,sc
WHERE st.s=sc.s
AND sc.c in
(
SELECT c
FROM course
WHERE t = any
(
SELECT t
FROM teacher
WHERE tname='张三'
)
);
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 |赵雷 | 1990-01-01 00:00:00 |男 |
| 02 |钱电 | 1990-12-21 00:00:00 |男 |
| 03 |孙风 | 1990-05-20 00:00:00 |男 |
| 04 |李云 | 1990-08-06 00:00:00 |男 |
| 05 |周梅 | 1991-12-01 00:00:00 |女 |
| 07 |郑竹 | 1989-07-01 00:00:00 |女 |
+------+--------+---------------------+------+
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT A.*
FROM student A ,
(
SELECT *
FROM sc
WHERE score <60
GROUP BY s
HAVING COUNT(s)>=2
)B
WHERE A.s = B.s;
#或者
SELECT A.*
FROM student A
INNER JOIN
(
SELECT *
FROM sc
WHERE score <60
GROUP BY s
HAVING COUNT(s)>=2
)B
ON A.s = B.s;
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 04 |李云 | 1990-08-06 00:00:00 |男 |
| 06 |吴兰 | 1992-03-01 00:00:00 |女 |
+------+--------+---------------------+------+
12.检索"01 "课程分数小于60，按分数降序排列的学生信息
SELECT st.*,A.score
FROM student AS st,
(
SELECT *
FROM sc
WHERE c='01'
AND score<60
)A
WHERE st.s=A.s
ORDER BY score DESC;
+------+--------+---------------------+------+-------+
| S | Sname | Sage | Ssex |score |
+------+--------+---------------------+------+-------+
| 04 |李云 | 1990-08-06 00:00:00 |男 | 50.0 |
| 06 |吴兰 | 1992-03-01 00:00:00 |女 | 31.0 |
+------+--------+---------------------+------+-------+
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT sc.*,平均值
FROM sc LEFT JOIN
(
SELECT *,avg(score) as平均值
FROM sc GROUP BY s
)A
ON sc.s=A.s
ORDER BY平均值DESC;
+------+------+-------+-----------+
| S | C | score |平均值 |
+------+------+-------+-----------+
| 07 | 02 | 89.0 | 93.50000 |
| 07 | 03 | 98.0 | 93.50000 |
| 01 | 03 | 99.0 | 89.66667 |
| 01 | 01 | 80.0 | 89.66667 |
| 01 | 02 | 90.0 | 89.66667 |
| 05 | 01 | 76.0 | 81.50000 |
| 05 | 02 | 87.0 | 81.50000 |
| 03 | 01 | 80.0 | 80.00000 |
| 03 | 02 | 80.0 | 80.00000 |
| 03 | 03 | 80.0 | 80.00000 |
| 02 | 01 | 70.0 | 70.00000 |
| 02 | 02 | 60.0 | 70.00000 |
| 02 | 03 | 80.0 | 70.00000 |
| 04 | 02 | 30.0 | 33.33333 |
| 04 | 03 | 20.0 | 33.33333 |
| 04 | 01 | 50.0 | 33.33333 |
| 06 | 01 | 31.0 | 32.50000 |
| 06 | 03 | 34.0 | 32.50000 |
+------+------+-------+-----------+
14.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT A.c,cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
FROM course A
LEFT JOIN
(
SELECT c,max(score)最高分,min(score)最低分,avg(score)平均分
FROM sc
GROUP BY c) B
ON A.c=B.c
LEFT JOIN
(
SELECT c,
(
CONVERT
(
(
(
sum(CASE WHEN score >=60 then 1
ELSE 0 END
)
*1.00
)/COUNT(*)
)*100,
DECIMAL(5,2)))及格率
FROM sc
GROUP BY c
)C
ON A.c=C.c
LEFT JOIN
(
SELECT c,
(
CONVERT
(
(
(
sum(CASE WHEN score >=70 AND score <80 then 1
ELSE 0 END
)*1.00
)/COUNT(*))*100,
DECIMAL(5,2)
)
)中等率
FROM sc
GROUP BY c
)D
ON A.c=D.c
LEFT JOIN
(
SELECT c,
(
CONVERT
(
(
(
sum(CASE WHEN score >=80 AND score <90 then 1
ELSE 0 END
)*1.00
)/COUNT(*)
)*100,
DECIMAL(5,2)
)
)优良率
FROM sc
GROUP BY c
)E
ON A.c=E.c
LEFT JOIN
(
SELECT c,
(
CONVERT
(
(
(
sum(CASE WHEN score >=90 then 1
ELSE 0 END
)*1.00
)/COUNT(*)
)*100,
DECIMAL(5,2)
)
)优秀率
FROM sc
GROUP BY c
)F
ON A.c=F.c;
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|c | cname |最高分 |最低分 |平均分 |及格率 |中等率 |优良率 |优秀率 |
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|01 |语文 | 80.0 | 31.0 | 64.50000 | 66.67 | 33.33 | 33.33 | 0.00 |
|02 |数学 | 90.0 | 30.0 | 72.66667 | 83.33 | 0.00 | 50.00 | 16.67 |
|03 |英语 | 99.0 | 20.0 | 68.50000 | 66.67 | 0.00 | 33.33 | 33.33 |
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
15.按各科成绩进行排序，并显示排名，Score重复时合并保留名次空缺
SELECT *,RANK() OVER(PARTITION BY c ORDER BY score DESC)排名
FROM sc;
+------+------+-------+--------+
| S | C | score |排名 |
+------+------+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 1 |
| 05 | 01 | 76.0 | 3 |
| 02 | 01 | 70.0 | 4 |
| 04 | 01 | 50.0 | 5 |
| 06 | 01 | 31.0 | 6 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 03 | 02 | 80.0 | 4 |
| 02 | 02 | 60.0 | 5 |
| 04 | 02 | 30.0 | 6 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
| 03 | 03 | 80.0 | 3 |
| 06 | 03 | 34.0 | 5 |
| 04 | 03 | 20.0 | 6 |
+------+------+-------+--------+
15.1按各科成绩进行排序，并显示排名，Score重复时合并名次
SELECT *,ROW_NUMBER() OVER(PARTITION BY c ORDER BY score DESC)排名
FROM sc;
+------+------+-------+--------+
| S | C | score |排名 |
+------+------+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 2 |
| 05 | 01 | 76.0 | 3 |
| 02 | 01 | 70.0 | 4 |
| 04 | 01 | 50.0 | 5 |
| 06 | 01 | 31.0 | 6 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 03 | 02 | 80.0 | 4 |
| 02 | 02 | 60.0 | 5 |
| 04 | 02 | 30.0 | 6 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
| 03 | 03 | 80.0 | 4 |
| 06 | 03 | 34.0 | 5 |
| 04 | 03 | 20.0 | 6 |
+------+------+-------+--------+
16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT s,RANK() OVER(ORDER BY sum(score) DESC)排名,sum(score)
FROM sc
GROUP BY s
ORDER BY sum(score) DESC;
+------+--------+------------+
| s |排名 | sum(score) |
+------+--------+------------+
| 01 | 1 | 269.0 |
| 03 | 2 | 240.0 |
| 02 | 3 | 210.0 |
| 07 | 4 | 187.0 |
| 05 | 5 | 163.0 |
| 04 | 6 | 100.0 |
| 06 | 7 | 65.0 |
+------+--------+------------+
16.1查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT s,DENSE_RANK() OVER(ORDER BY sum(score) DESC)排名,sum(score)
FROM sc
GROUP BY s
ORDER BY sum(score) DESC;
+------+--------+------------+
| s |排名 | sum(score) |
+------+--------+------------+
| 01 | 1 | 269.0 |
| 03 | 2 | 240.0 |
| 02 | 3 | 210.0 |
| 07 | 4 | 187.0 |
| 05 | 5 | 163.0 |
| 04 | 6 | 100.0 |
| 06 | 7 | 65.0 |
+------+--------+------------+
17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比
SELECT A.c,100到85人数,100到85占比, 85到70人数,85到70占比, 70到60人数,70到60占比,60到0人数,60到0占比
FROM sc A
LEFT JOIN
(
SELECT s,c,sum(CASE WHEN score >85 then 1 ELSE 0 END)
AS 100到85人数,
CONVERT
(
(
sum(CASE WHEN score >85 then 1 ELSE 0 END)*100
)/COUNT(score),decimal(5,1)
) AS 100到85占比
FROM sc
GROUP BY c)B
ON A.S=B.S
LEFT JOIN
(
SELECT s,c,
sum(CASE WHEN score >70 AND score<=85 then 1 ELSE 0 END)
AS 85到70人数,
CONVERT
(
(
sum(CASE WHEN score >70 AND score<=85 then 1 ELSE 0 END)*100
)/COUNT(score),decimal(5,1)
) AS 85到70占比
FROM sc
GROUP BY c)C
ON A.S=C.S
LEFT JOIN
(
SELECT s,c,sum(CASE WHEN score >60 AND score <=70 then 1 ELSE 0 END)
AS 70到60人数,
CONVERT
(
(
sum(CASE WHEN score >60 AND score<=70 then 1 ELSE 0 END)*100
)/COUNT(score),decimal(5,1)
) AS 70到60占比
FROM sc
GROUP BY c)D
ON A.S=D.S
LEFT JOIN
(
SELECT s,c,sum(CASE WHEN score >=0 AND score<=60 then 1 ELSE 0 END)
AS 60到0人数,
CONVERT
(
(
sum(CASE WHEN score >=0 AND score <=60 then 1 ELSE 0 END)*100
)/COUNT(score),decimal(5,1)
) AS 60到0占比 FROM sc GROUP BY c)E
ON A.S=E.S
WHERE A.C=B.C AND A.C=C.C AND A.C=D.C AND A.C=E.C;
+------+----------------+----------------+---------------+---------------+---------------+---------------+--------------+--------------+
| c | 100到85人数 | 100到85占比 | 85到70人数 | 85到70占比 | 70到60人数 | 70到60占比 | 60到0人数 | 60到0占比 |
+------+----------------+----------------+---------------+---------------+---------------+---------------+--------------+--------------+
| 01 | 0 | 0.0 | 3 | 50.0 | 1 | 16.7 | 2 | 33.3 |
| 02 | 3 | 50.0 | 1 | 16.7 | 0 | 0.0 | 2 | 33.3 |
| 03 | 2 | 33.3 | 2 | 33.3 | 0 | 0.0 | 2 | 33.3 |
+------+----------------+----------------+---------------+---------------+---------------+---------------+--------------+--------------+
18.查询各科成绩前三名的记录
SELECT *
FROM
(
SELECT *,row_number() over(PARTITION BY c ORDER BY score DESC)排名
FROM sc
ORDER BY c,排名
)A
WHERE 排名<=3;
+------+------+-------+--------+
| S | C | score |排名 |
+------+------+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 2 |
| 05 | 01 | 76.0 | 3 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 05 | 02 | 87.0 | 3 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
| 02 | 03 | 80.0 | 3 |
+------+------+-------+--------+
19.查询每门课程被选修的学生数
SELECT c,COUNT(s)
FROM sc
GROUP BY c;
+------+----------+
| c | COUNT(s) |
+------+----------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+----------+
20.查询出只选修两门课程的学生学号和姓名
SELECT s,sname,COUNT(s)课程数
FROM
(
SELECT st.s,sname,c
FROM student AS st,sc
WHERE st.s=sc.s
)A
GROUP BY s
HAVING COUNT(s)=2;
+------+--------+-----------+
| s | sname |课程数 |
+------+--------+-----------+
| 05 |周梅 | 2 |
| 06 |吴兰 | 2 |
| 07 |郑竹 | 2 |
+------+--------+-----------+
21.查询男生、女生人数
SELECT ssex,COUNT(ssex)
FROM student st
GROUP BY ssex;
+------+-------------+
| ssex | COUNT(ssex) |
+------+-------------+
|男 | 4 |
|女 | 4 |
+------+-------------+
22.查询名字中含有「风」字的学生信息
SELECT *
FROM student st
WHERE sname LIKE '%风%';
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 03 |孙风 | 1990-05-20 00:00:00 |男 |
+------+--------+---------------------+------+
23.查询同名同性学生名单，并统计同名人数
SELECT s,sname,COUNT(sname)
FROM student
GROUP BY sname
HAVING COUNT(sname) >=2;
Empty set (0.00 sec)
24.查询1990年出生的学生名单
SELECT *
FROM student st
WHERE sage LIKE '1990-%';
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 |赵雷 | 1990-01-01 00:00:00 |男 |
| 02 |钱电 | 1990-12-21 00:00:00 |男 |
| 03 |孙风 | 1990-05-20 00:00:00 |男 |
| 04 |李云 | 1990-08-06 00:00:00 |男 |
| 08 |王菊 | 1990-01-20 00:00:00 |女 |
+------+--------+---------------------+------+
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT *,avg(score)
FROM sc
GROUP BY s
ORDER BY avg(score) DESC,c ASC;
+------+------+-------+------------+
| S | C | score | avg(score) |
+------+------+-------+------------+
| 07 | 02 | 89.0 | 93.50000 |
| 01 | 01 | 80.0 | 89.66667 |
| 05 | 01 | 76.0 | 81.50000 |
| 03 | 01 | 80.0 | 80.00000 |
| 02 | 01 | 70.0 | 70.00000 |
| 04 | 01 | 50.0 | 33.33333 |
| 06 | 01 | 31.0 | 32.50000 |
+------+------+-------+------------+
26.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT s,sname,avg(score)
FROM
(
SELECT st.s,sname,score
FROM student st,sc
WHERE st.s=sc.s
)A
GROUP BY s
HAVING avg(score)>=85;
+------+--------+------------+
| s | sname | avg(score) |
+------+--------+------------+
| 01 |赵雷 | 89.66667 |
| 07 |郑竹 | 93.50000 |
+------+--------+------------+
27.查询课程名称为「数学」，且分数低于60的学生姓名和分数
SELECT st.s,score
FROM student AS st,sc
WHERE st.s=sc.s AND score<=60 AND c in
(
SELECT cname
FROM course
WHERE cname = '数学'
);
Empty set (0.00 sec)
28.查询所有学生的课程及分数情况(存在学生没成绩，没选课的情况)
SELECT st.*,sc.c,sc.score
FROM student AS st
LEFT JOIN sc
ON st.s = sc.s
ORDER BY st.s,sc.c;
+------+--------+---------------------+------+------+-------+
| S | Sname | Sage | Ssex |c | score |
+------+--------+---------------------+------+------+-------+
| 01 |赵雷 | 1990-01-01 00:00:00 |男 | 01 | 80.0 |
| 01 |赵雷 | 1990-01-01 00:00:00 |男 | 02 | 90.0 |
| 01 |赵雷 | 1990-01-01 00:00:00 |男 | 03 | 99.0 |
| 02 |钱电 | 1990-12-21 00:00:00 |男 | 01 | 70.0 |
| 02 |钱电 | 1990-12-21 00:00:00 |男 | 02 | 60.0 |
| 02 |钱电 | 1990-12-21 00:00:00 |男 | 03 | 80.0 |
| 03 |孙风 | 1990-05-20 00:00:00 |男 | 01 | 80.0 |
| 03 |孙风 | 1990-05-20 00:00:00 |男 | 02 | 80.0 |
| 03 |孙风 | 1990-05-20 00:00:00 |男 | 03 | 80.0 |
| 04 |李云 | 1990-08-06 00:00:00 |男 | 01 | 50.0 |
| 04 |李云 | 1990-08-06 00:00:00 |男 | 02 | 30.0 |
| 04 |李云 | 1990-08-06 00:00:00 |男 | 03 | 20.0 |
| 05 |周梅 | 1991-12-01 00:00:00 |女 | 01 | 76.0 |
| 05 |周梅 | 1991-12-01 00:00:00 |女 | 02 | 87.0 |
| 06 |吴兰 | 1992-03-01 00:00:00 |女 | 01 | 31.0 |
| 06 |吴兰 | 1992-03-01 00:00:00 |女 | 03 | 34.0 |
| 07 |郑竹 | 1989-07-01 00:00:00 |女 | 02 | 89.0 |
| 07 |郑竹 | 1989-07-01 00:00:00 |女 | 03 | 98.0 |
| 08 |王菊 | 1990-01-20 00:00:00 |女 | NULL | NULL |
+------+--------+---------------------+------+------+-------+
29.查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT st.s,st.sname,c,score
FROM student AS st
INNER JOIN sc
ON st.s=sc.s
WHERE score >=70
ORDER BY st.s,c;
+------+--------+------+-------+
| s | sname | c | score |
+------+--------+------+-------+
| 01 |赵雷 | 01 | 80.0 |
| 01 |赵雷 | 02 | 90.0 |
| 01 |赵雷 | 03 | 99.0 |
| 02 |钱电 | 01 | 70.0 |
| 02 |钱电 | 03 | 80.0 |
| 03 |孙风 | 01 | 80.0 |
| 03 |孙风 | 02 | 80.0 |
| 03 |孙风 | 03 | 80.0 |
| 05 |周梅 | 01 | 76.0 |
| 05 |周梅 | 02 | 87.0 |
| 07 |郑竹 | 02 | 89.0 |
| 07 |郑竹 | 03 | 98.0 |
+------+--------+------+-------+
30.查询不及格的课程
SELECT c,score
FROM sc
WHERE score <60
ORDER BY c;
+------+-------+
| c | score |
+------+-------+
| 01 | 50.0 |
| 01 | 31.0 |
| 02 | 30.0 |
| 03 | 20.0 |
| 03 | 34.0 |
+------+-------+
31.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT st.s,st.sname,c,score
FROM student AS st,sc
WHERE st.s=sc.s AND c='01' AND score >=80;
+------+--------+------+-------+
| s | sname | c | score |
+------+--------+------+-------+
| 01 |赵雷 | 01 | 80.0 |
| 03 |孙风 | 01 | 80.0 |
+------+--------+------+-------+
32.求每门课程的学生人数
SELECT c,COUNT(c)
FROM sc
GROUP BY c;
+------+----------+
| c | COUNT(c) |
+------+----------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+----------+
33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT st.s,st.sname,score
FROM student AS st,sc
WHERE st.s=sc.s AND c in
(
SELECT c
FROM course,teacher
WHERE course.t = teacher.t AND teacher.tname= '张三'
)
ORDER BY score DESC limit 1 ;
+------+--------+-------+
| s | sname | score |
+------+--------+-------+
| 01 |赵雷 | 90.0 |
+------+--------+-------+
34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT * FROM
(
SELECT st.s,st.sname,score,dense_rank() over
(
ORDER BY score DESC
)排名
FROM student AS st,sc
WHERE st.s=sc.s AND c in
(
SELECT c
FROM course,teacher
WHERE course.t = teacher.t AND teacher.tname = '张三'
)
)A
WHERE 排名='1';
+------+--------+-------+--------+
| s | sname | score |排名 |
+------+--------+-------+--------+
| 01 |赵雷 | 90.0 | 1 |
+------+--------+-------+--------+
35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT s,c,score
FROM
(
SELECT max(score),avg(score),COUNT(score),student.s,c,score
FROM student
LEFT JOIN sc
ON student.s=sc.s
GROUP BY student.s
HAVING max(score)=avg(score) ANDCOUNT(score)>=2
)A;
+------+------+-------+
| s | c | score |
+------+------+-------+
| 03 | 01 | 80.0 |
+------+------+-------+
36.查询每门功成绩最好的前两名
SELECT s,c,score,排名
FROM
(
SELECT s,c,score,row_number() over(partitionby c ORDER BY score DESC)排名
FROM sc
ORDER BY score DESC
)A WHERE 排名<=2
ORDER BY c,排名;
+------+------+-------+--------+
| s | c | score |排名 |
+------+------+-------+--------+
| 01 | 01 | 80.0 | 1 |
| 03 | 01 | 80.0 | 2 |
| 01 | 02 | 90.0 | 1 |
| 07 | 02 | 89.0 | 2 |
| 01 | 03 | 99.0 | 1 |
| 07 | 03 | 98.0 | 2 |
+------+------+-------+--------+
37.统计每门课程的学生选修人数(超过5人的课程才统计)。
SELECT c,COUNT(c)
FROM sc
GROUP BY c
HAVING COUNT(c)>5 ;
+------+----------+
| c | COUNT(c) |
+------+----------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+----------+
38.检索至少选修两门课程的学生学号
SELECT s,COUNT(c)
FROM sc
GROUP BY s
HAVING COUNT(c)>=2;
+------+----------+
| s | COUNT(c) |
+------+----------+
| 01 | 3 |
| 02 | 3 |
| 03 | 3 |
| 04 | 3 |
| 05 | 2 |
| 06 | 2 |
| 07 | 2 |
+------+----------+
39.查询选修了全部课程的学生信息
SELECT sc.s,st.sname,COUNT(c)
FROM student AS st,sc
WHERE st.s=sc.s
GROUP BY s
HAVING COUNT(c)>=3;
+------+--------+----------+
| s | sname | COUNT(c) |
+------+--------+----------+
| 01 |赵雷 | 3 |
| 02 |钱电 | 3 |
| 03 |孙风 | 3 |
| 04 |李云 | 3 |
+------+--------+----------+
40.查询各学生的年龄，只按年份来算
SELECT s,sname,(year(curdate())-year(sage))年龄
FROM student
ORDER BY 年龄;
+------+--------+--------+
| s | sname |年龄 |
+------+--------+--------+
| 06 |吴兰 | 27 |
| 05 |周梅 | 28 |
| 01 |赵雷 | 29 |
| 02 |钱电 | 29 |
| 03 |孙风 | 29 |
| 04 |李云 | 29 |
| 08 |王菊 | 29 |
| 07 |郑竹 | 30 |
+------+--------+--------+
41.按照出生日期来算，当前月日<出生年月的月日则，年龄减一
SELECT s,sname,timestampdiff(year,sage,curdate())年龄
FROM student
ORDER BY 年龄;
+------+--------+--------+
| s | sname |年龄 |
+------+--------+--------+
| 06 |吴兰 | 26 |
| 05 |周梅 | 27 |
| 02 |钱电 | 28 |
| 03 |孙风 | 28 |
| 04 |李云 | 28 |
| 08 |王菊 | 28 |
| 01 |赵雷 | 29 |
| 07 |郑竹 | 29 |
+------+--------+--------+
42.查询本周过生日的学生
--今天是2019.1.16
SET @day =8-dayofweek(curdate());
SELECT *
FROM student
WHERE date_format(sage, '%m%d')
BETWEEN date_format(curdate(),'%m%d')
AND
date_format (date_add(curdate(),interval @day day), '%m%d');
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 08 |王菊 | 1990-01-20 00:00:00 |女 |
+------+--------+---------------------+------+
43.查询下周过生日的学生
set @day =9-dayofweek(curdate());
SELECT *
FROM student
WHERE date_format(sage, '%m%d')
BETWEEN date_format (date_add(curdate(),interval @day day), '%m%d')
AND
date_format (date_add(curdate(),interval @day+6 day), '%m%d');
Empty set (0.00 sec)
44.查询本月过生日的学生
SELECT *
FROM student
WHERE date_format(sage,'%m')= date_format(curdate(),'%m');
+------+--------+---------------------+------+
| S | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 |赵雷 | 1990-01-01 00:00:00 |男 |
| 08 |王菊 | 1990-01-20 00:00:00 |女 |
+------+--------+---------------------+------+
45.查询下月过生日的学生
SELECT *
FROM student
WHERE date_format(sage,'%m')= date_format(date_add(curdate(),interval 1 month),'%m');
Empty set (0.00 sec)
网友评论