【SQL训练】22道经典题及答案解析

文章导览

        创建表格

        表格结构预览

        例题及答案详解

1、创建表格

【代码展示】

create table Student(sid varchar(10),sname varchar(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' , '女');  
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));  
insert into Course values('01' , '语文' , '02');  
insert into Course values('02' , '数学' , '01');  
insert into Course values('03' , '英语' , '03');  
create table Teacher(tid varchar(10),tname varchar(10));  
insert into Teacher values('01' , '张三');  
insert into Teacher values('02' , '李四');  
insert into Teacher values('03' , '王五');  
create table SC(sid varchar(10),cid 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);

【代码详解】

 2、表格结构预览

【学生表】
Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

【课程表】
Course(CId,Cname,TId)
--CId 课程编号,Cname 课程名称,TId 教师编号

 

 
【教师表】
Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名

 
【成绩表】
SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数

 3、例题分析

3、1 查询“01”课程比“02”课程成绩高的所有学生的学号

【需求分析】需要查询最终结果是学生学号；条件是语文成绩比数学高；

【代码思路】把成绩表中01语文课程的所有字段单独拿出作为一个表，02数学的所有字段单独拿出做一个表。两个表连接后直接比较成绩列的大小，并取出符合条件的数据

【代码展示】

select distinct t1.sid as sid
# 查询并取唯一 table1（表1）的sid字段重命名为 sid

from   
    (select * from sc where cid='01')t1  # 从课程表中取出课程号为1的所有字段，命名为table1
  
left join   # 连表操作
    (select * from sc where cid='02')t2  # 从课程表中取出课程号为2的所有字段，命名为table2

on t1.sid=t2.sid  # 关键字链接
 
where t1.score>t2.score  # 条件是table1的成绩字段大于table2的成绩字段

【代码运行】

 3、2 查询平均成绩大于60分的同学的学号和平均成绩

【需求分析】查询结果是学号、平均成绩；条件是平均成绩大于60

【代码思路】对sc表根据学号分组，在对成绩求平均，在筛选出平均成绩大于60 的数据

【代码展示】

select sid,avg(score)  
from sc  
group by sid  
having avg(score)>60

【运行结果】

 3、3 查询所有同学的学号、姓名、选课数、总成绩

【需求分析】查询字段是学号，姓名，选课数，总成绩

【代码思路】学生表和成绩表连表操作；对学生ID字段分组，对sid字段用count函数求选课数；对score字段用sum函数求总成绩

【代码展示】

select  
    student.sid as sid  
    ,sname  
    ,count(distinct cid) course_cnt  
    ,sum(score) as total_score  
from student  
left join sc  
on student.sid=sc.sid  
group by sid,sname

【运行结果】

3、4 查询姓“李”的老师的个数

【需求分析】查询字段是新字段个数，条件是姓李的老师

【代码思路】对教师表的tname字段进行条件筛查，用李+通配符%表示符合条件的文本；最后对该字段使用count函数求数量

【代码展示】

select  
    count(distinct tid) as teacher_cnt  
from teacher  
where tname like '李%'

【运行结果】

3、5 查询没学过“张三”老师课的同学的学号、姓名； 

【需求分析】查询字段是学生学号，姓名，条件是没上过张三老师的课

【代码思路】找出上过张三老师课程的学生ID 然后取反就行

【代码展示】

select  
    sid,sname  
from student  
where sid not in   
    (  
        select  
            sc.sid  
        from teacher  
        left join course  
            on teacher.tid=course.tid  
        left join sc  
            on course.cid=sc.cid  
        where teacher.tname='张三'  
    )

【运行结果】

3、6  查询学过“张三”老师所教的课的同学的学号、姓名 

 【需求分析】查询字段是学生学号，姓名，条件是上过张三课程

【代码思路】同3、5 并且更简单

【代码展示】

select  
    student.sid  
    ,sname  
from   
    (  
        select  
            distinct cid   
        from course  
        left join teacher   
        on course.tid=teacher.tid  
        where teacher.tname='张三'  
    )course  
left join sc   
    on course.cid=sc.cid  
left join student  
    on sc.sid=student.sid  
group by student.sid,sname

【运行结果】

3、7  查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名 

 【需求分析】查询字段是学生学号，姓名，条件是成绩01比02更低

【代码思路】把sc表cid字段是01号课程的所有数据单列成一个表，02课程的单列一个表。筛出01成绩低的学生ID；跟学生表连表操作，再取出ID姓名。

【代码展示】

select  
    t1.sid,sname  
from   
    (  
        select distinct t1.sid as sid  
        from   
            (select * from sc where cid='01')t1  
        left join   
            (select * from sc where cid='02')t2  
        on t1.sid=t2.sid  
        where t1.score>t2.score  
    )t1  
left join student  
    on t1.sid=student.sid

【运行结果】

【未完待续。。。】