SQL练习题2.1

建表

# 学生表
create table t_student
(
    stu_id   varchar(10),
    stu_name varchar(10),
    stu_age  datetime,
    stu_sex  varchar(10)
);

# 课程表
create table t_t_course
(
    c_id    varchar(10),
    c_name  varchar(10),
    c_teaid varchar(10)
);

# 教师表
create table t_t_teacher
(
    tea_id   varchar(10),
    tea_name varchar(10)
);

# 成绩表
create table t_t_score
(
    s_stuid varchar(10),
    s_cid   varchar(10),
    s_score decimal(18, 1)
);

-- 向t_student表插入数据
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('01', '赵雷', '1990-01-01', '男');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('02', '钱电', '1990-12-21', '男');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('03', '孙风', '1990-12-20', '男');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('04', '李云', '1990-12-06', '男');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('05', '周梅', '1991-12-01', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('06', '吴兰', '1992-01-01', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('07', '郑竹', '1989-01-01', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('09', '张三', '2017-12-20', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('10', '李四', '2017-12-25', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('11', '李四', '2012-06-06', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('12', '赵六', '2013-06-13', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('13', '孙七', '2014-06-01', '女');

-- 向t_t_course表插入数据
insert into t_t_course(c_id, c_name, c_teaid)
values ('01', '语文', '02');
insert into t_course(c_id, c_name, c_teaid)
values ('02', '数学', '01');
insert into t_course(c_id, c_name, c_teaid)
values ('03', '英语', '03');

-- 向t_t_teacher表插入数据
insert into t_teacher(tea_id, tea_name)
values ('01', '张三');
insert into t_teacher(tea_id, tea_name)
values ('02', '李四');
insert into t_teacher(tea_id, tea_name)
values ('03', '王五');

-- 向t_t_score表插入数据
insert into t_score(s_stuid, s_cid, s_score)
values ('01', '01', 80);
insert into t_score(s_stuid, s_cid, s_score)
values ('01', '02', 90);
insert into t_score(s_stuid, s_cid, s_score)
values ('01', '03', 99);
insert into t_score(s_stuid, s_cid, s_score)
values ('02', '01', 70);
insert into t_score(s_stuid, s_cid, s_score)
values ('02', '02', 60);
insert into t_score(s_stuid, s_cid, s_score)
values ('02', '03', 80);
insert into t_score(s_stuid, s_cid, s_score)
values ('03', '01', 80);
insert into t_score(s_stuid, s_cid, s_score)
values ('03', '02', 80);
insert into t_score(s_stuid, s_cid, s_score)
values ('03', '03', 80);
insert into t_score(s_stuid, s_cid, s_score)
values ('04', '01', 50);
insert into t_score(s_stuid, s_cid, s_score)
values ('04', '02', 30);
insert into t_score(s_stuid, s_cid, s_score)
values ('04', '03', 20);
insert into t_score(s_stuid, s_cid, s_score)
values ('05', '01', 76);
insert into t_score(s_stuid, s_cid, s_score)
values ('05', '02', 87);
insert into t_score(s_stuid, s_cid, s_score)
values ('06', '01', 31);
insert into t_score(s_stuid, s_cid, s_score)
values ('06', '03', 34);
insert into t_score(s_stuid, s_cid, s_score)
values ('07', '02', 89);
insert into t_score(s_stuid, s_cid, s_score)
values ('07', '03', 98);

练习

# **1. 查询同名学生名单，并统计同名人数，找到同名的名字并统计个数**
select count(*), stu_name
from t_student
group by stu_name;

# **2. 查询列出同名的全部学生的信息**
select *
from t_student
order by convert(stu_name using GBK);

# **3. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列**
select avg(s_score)
from t_score
group by s_cid
ORDER BY avg(s_score) DESC, s_cid ASC;

#**4. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩**
select s.stu_id, s.stu_name, avg(sc.s_score)
from t_student s
         inner join t_score sc on s.stu_id = sc.s_stuid
group by s.stu_id, s.stu_name
having avg(s_score) >= 85;

#5. 查询课程名称为"数学"，且分数低于 60 的学生姓名和分数
select *
from t_student s
         inner join t_score sc on s.stu_id = sc.s_stuid
         inner join t_course c on sc.s_cid = c.c_id
where c.c_name = '数学'
  and sc.s_score < 60;

#6. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select s.stu_name, c.c_name, sc.s_score
from t_student s
         inner join t_score sc
                    on s.stu_id = sc.s_stuid
         inner join t_course c on sc.s_cid = c.c_id
where sc.s_score > 70;

#**7. 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名**
select s.stu_id, s.stu_name
from t_student s
         inner join t_score sc
                    on s.stu_id = sc.s_stuid
         inner join t_course c on sc.s_cid = c.c_id
where sc.s_score >= 80
  and c.c_id = '01';

#**8. 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩**
select s.stu_id, s.stu_name ,s_score
from t_student s
         inner join t_score sc on s.stu_id = sc.s_stuid
         inner join t_course c on sc.s_cid = c.c_id
         inner join t_teacher t on c.c_teaid = t.tea_id
where t.tea_name = '张三'
  and sc.s_score = (select MAX(sc1.s_score)
                    from t_score sc1
                             inner join t_course c1 on sc1.s_cid = c1.c_id
                             inner join t_teacher t1 on c1.c_teaid = t1.tea_id
                    where t1.tea_name = '张三');