数据库设计与查询分析（练习--对小白友好）

---

数据库设计与查询分析

1. 数据库表创建

在本节中，我们将创建所需的数据库表，包括学生表、课程表、教师表和成绩表。

1.1 学生表创建

-- 创建学生表
create table student(
    s_id int,
    s_name varchar(8),
    s_birth date,
    s_sex varchar(4)
)

1.2 课程表创建

-- 创建课程表
create table course (
    c_id int,
    c_name varchar(8),
    t_id int
);

1.3 教师表创建

-- 创建教师表
create table teacher (
    t_id int,
    t_name varchar(8)
);

1.4 成绩表创建

-- 创建成绩表
create table score (
    s_id int,
    c_id int,
    s_score int
);

2. 数据插入

2.1 学生数据插入

-- 插入学生数据
insert into student values
(1,'赵雷','1990-01-01','男'),
(2,'钱电','1990-12-21','男'),
(3,'孙风','1990-05-20','男'),
(4,'李云','1990-08-06','男'),
(5,'周梅','1991-12-01','女'),
(6,'吴兰','1992-03-01','女'),
(7,'郑竹','1989-07-01','女'),
(8,'王菊','1990-01-20','女');

2.2 课程数据插入

-- 插入课程数据
insert into course values
(1,'语文',2),
(2,'数学',1),
(3,'英语',3);

2.3 教师数据插入

-- 插入教师数据
insert into teacher values
(1,'张三'),
(2,'李四'),
(3,'王五');

2.4 成绩数据插入

-- 插入成绩数据
insert into score values
(1,1,80),
(1,2,90),
(1,3,99),
(2,1,70),
(2,2,60),
(2,3,65),
(3,1,80),
(3,2,80),
(3,3,80),
(4,1,50),
(4,2,30),
(4,3,40),
(5,1,76),
(5,2,87),
(6,1,31),
(6,3,34),
(7,2,89),
(7,3,98);

3. 查询分析

在本节中，我们将执行一系列SQL查询，以分析和提取数据库中的有用信息。

3.1 查询平均成绩大于等于60分的同学

-- 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.s_id,s.s_name,round(avg_score, 2) as avg_score
from student s
inner join (
    select s_id,avg(s_score) as avg_score
    from score
    group by s_id
    having avg_score >= 60
) t1
on s.s_id = t1.s_id;

3.2 查询所有学生的姓名和生日

-- 查询所有学生的姓名和生日
select s_name,s_birth from student;

3.3 查询所有课程的名称和对应教师的姓名

-- 查询所有课程的名称和对应教师的姓名
select c.c_name,t.t_name
from course c
join teacher t on c.t_id = t.t_id;

3.4 查询所有成绩在90分以上的学生姓名和成绩

-- 查询所有成绩在90分以上的学生姓名和成绩
SELECT s.s_name,sc.s_score
from student s
join score sc on s.s_id = sc.s_id
where sc.s_score >90;

3.5 查询没有选修任何课程的学生姓名

-- 查询没有选修任何课程的学生姓名
select s.s_name
from student s 
where s.s_id not in (select s_id from score);

3.6 查询至少选修了两门课程的学生姓名

-- 查询至少选修了两门课程的学生姓名
SELECT s.s_name
FROM student s
JOIN score sc ON s.s_id = sc.s_id
GROUP BY s.s_name
HAVING COUNT(sc.c_id) >= 2;

3.7 查询所有教师的姓名和他们所教授的课程数量

-- 查询所有教师的姓名和他们所教授的课程数量
select t.t_name,count(c.c_id) as course_count
from teacher t
join course c on t.t_id = c.t_id
GROUP BY t.t_name;

3.8 查询所有成绩不及格的学生姓名和成绩

-- 查询所有成绩不及格的学生姓名和成绩
select s.s_name,sc.s_score
from student s
join score sc on s.s_id=sc.s_id
where sc.s_score <60;

3.9 查询所有选修了英语课程的学生姓名

-- 查询所有选修了英语课程的学生姓名
SELECT s.s_name
FROM student s
JOIN score sc ON s.s_id = sc.s_id
JOIN course c ON sc.c_id = c.c_id
WHERE c.c_name = '英语';

3.10 查询所有教师的姓名和他们所教授的课程名称

-- 查询所有教师的姓名和他们所教授的课程名称
SELECT t.t_name, c.c_name
FROM teacher t
JOIN course c ON t.t_id = c.t_id;

3.11 查询所有学生的平均成绩，并按平均成绩从高到低排序

-- 查询所有学生的平均成绩，并按平均成绩从高到低排序
SELECT s_name, AVG(s_score) AS average_score
FROM student s
JOIN score sc ON s.s_id = sc.s_id
GROUP BY s.s_name
ORDER BY average_score DESC;

3.12 查询每门课程的平均成绩，并按平均成绩从高到低排序

-- 查询每门课程的平均成绩，并按平均成绩从高到低排序
SELECT c.c_name, AVG(sc.s_score) AS average_score
FROM course c
JOIN score sc ON c.c_id = sc.c_id
GROUP BY c.c_name
ORDER BY average_score DESC;

3.13 查询所有学生中，选修课程数量最多的学生的姓名和选修课程数量

-- 查询所有学生中，选修课程数量最多的学生的姓名和选修课程数量
SELECT s.s_name, COUNT(sc.c_id) AS course_count
FROM student s
JOIN score sc ON s.s_id = sc.s_id
GROUP BY s.s_name
ORDER BY course_count DESC
LIMIT 1;

3.14 查询所有学生的姓名和他们所选课程的总成绩，只包括至少选修了一门课程的学生

-- 查询所有学生的姓名和他们所选课程的总成绩，只包括至少选修了一门课程的学生
SELECT s.s_name, SUM(sc.s_score) AS total_score
FROM student s
JOIN score sc ON s.s_id = sc.s_id
GROUP BY s.s_name
HAVING total_score IS NOT NULL;

3.15 查询所有学生中，成绩最好的学生的姓名和成绩

-- 查询所有学生中，成绩最好的学生的姓名和成绩
SELECT s.s_name, sc.s_score
FROM student s
JOIN score sc ON s.s_id = sc.s_id
WHERE sc.s_score = (
  SELECT MAX(s_score) FROM score
);

3.16 查询所有教师的姓名和他们所教授课程的平均成绩

-- 查询所有教师的姓名和他们所教授课程的平均成绩
SELECT t.t_name, AVG(sc.s_score) AS average_score
FROM teacher t
JOIN course c ON t.t_id = c.t_id
JOIN score sc ON c.c_id = sc.c_id
GROUP BY t.t_name;

3.17 查询所有学生中，选修了张三老师课程的学生姓名

-- 查询所有学生中，选修了张三老师课程的学生姓名
SELECT DISTINCT s.s_name
FROM student s
JOIN score sc ON s.s_id = sc.s_id
JOIN course c ON sc.c_id = c.c_id
JOIN teacher t ON c.t_id = t.t_id
WHERE t.t_name = '张三';

3.18 查询所有课程的平均成绩，只包括至少有5名学生选修的课程

-- 查询所有课程的平均成绩，只包括至少有5名学生选修的课程
SELECT c.c_name, AVG(sc.s_score) AS average_score
FROM course c
JOIN score sc ON c.c_id = sc.c_id
GROUP BY c.c_name
HAVING COUNT(DISTINCT sc.s_id) >= 5;

3.19 查询所有课程中，没有学生选修的课程名称

-- 查询所有课程中，没有学生选修的课程名称
SELECT c.c_name
FROM course c
WHERE c.c_id NOT IN (SELECT c_id FROM score);

3.20 查询所有学生的平均成绩，并按平均成绩从低到高排序

-- 查询所有学生的平均成绩，并按平均成绩从低到高排序
SELECT s.s_name, AVG(sc.s_score) AS average_score
FROM student s
JOIN score sc ON s.s_id = sc.s_id
GROUP BY s.s_name
ORDER BY average_score ASC;

3.21 查询所有学生中，至少选修了3门课程的学生姓名和选修课程数量

-- 查询所有学生中，至少选修了3门课程的学生姓名和选修课程数量
SELECT s.s_name, COUNT(sc.c_id) AS course_count
FROM student s
JOIN score sc ON s.s_id = sc.s_id
GROUP BY s.s_name
HAVING course_count >= 3;

1