SQL50题---1~5题

2020-07-28 本文已影响0人耕耘时空

首先，创建表格和数据。我用的是Toad for Oracle这款软件，再最开始创建表格添加数据时，发现全选所有代码点击运行后会提示错误，后来才发现在开头加上begin，结尾加上end; 即可全部代码一步运行，省去了逐条运行的麻烦。

数据导入


begin

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values('01' , '赵雷' , to_date('1990-01-01' , 'yyyy-mm-dd') , '男');

insert into Student values('02' , '钱电' , to_date('1990-12-21' , 'yyyy-mm-dd') , '男');

insert into Student values('03' , '孙风' , to_date('1990-12-20' , 'yyyy-mm-dd') , '男');

insert into Student values('04' , '李云' , to_date('1990-12-06' , 'yyyy-mm-dd') , '男');

insert into Student values('05' , '周梅' , to_date('1991-12-01' , 'yyyy-mm-dd') , '女');

insert into Student values('06' , '吴兰' , to_date('1992-01-01' , 'yyyy-mm-dd') , '女');

insert into Student values('07' , '郑竹' , to_date('1989-01-01' , 'yyyy-mm-dd') , '女');

insert into Student values('09' , '张三' , to_date('2017-12-20' , 'yyyy-mm-dd') , '女');

insert into Student values('10' , '李四' , to_date('2017-12-25' , 'yyyy-mm-dd') , '女');

insert into Student values('11' , '李四' , to_date('2012-06-06' , 'yyyy-mm-dd') , '女');

insert into Student values('12' , '赵六' , to_date('2013-06-13' , 'yyyy-mm-dd') , '女');

insert into Student values('13' , '孙七' , to_date('2014-06-01' , 'yyyy-mm-dd') , '女');

create table Course(CId varchar(10), Cname  nvarchar2(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);

end;

练习题目

1、查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

2、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

3、查询在 SC 表存在成绩的学生信息

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

4.1 查有成绩的学生信息

5、查询「李」姓老师的数量

答案

1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

PS：

1、right / left + join……on……用于右/左外连接表格，根据以左表还是右表的数据为显示基准选择不同的语句，例如：以基准的表不会有NULL值，而另一个拼接表可以有NULL值。

2、字段名/数据名 as 代替名，改写法主要用于以简单明了代称在代码中替换原繁琐的字段名或数据表名。实际使用时，在Toad for Oracle中字段名的替代使用as，但发现数据表的替不需要使用as连接，直接数据名替代名即可，不然会报错。


select * from student

right join(

    select t1.SID as 学号 , t1.SCORE as 课程1得分 , t2.SCORE as 课程2得分

    from (select SID , SCORE from SC where SC.CID='01') t1 ,

    (select SID , SCORE from SC where SC.CID='02') t2

    where t1.SCORE>t2.SCORE and t1.SID = t2.SID

) r

on STUDENT.SID = r.学号

;

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

PS：

1、inner join 内连接，左右两表都不显示NULL值。

2、order by 排序作用。


select * from (

    select * from SC where CID='01') t1

    inner join(

        select * from SC where CID='02') t2

    on t1.SID=t2.SID

order by t1.SID

;

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

PS：很简单，延续前两题的知识点。


select * from (

    select * from SC where CID='01') t1 

    left join (

        select * from SC where CID='02') t2

    on t1.SID=t2.SID

;

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况


select * from (

    select * from SC where CID='01') a

    right join (

        select * from SC where CID='02') b

    on a.SID=b.SID

where a.SCORE is NULL

;


select * from SC

    where SID not in

        (select SID from SC where CID='01')

    and CID='02'

;

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

PS：group by 分组作用（例如字段名将学生分为小学/初中/高中，该对该字段使用语句后会将小学的数据合在一起，达到分组效果。），该语句一般与聚合函数（例如count、sum、avg等）共同使用，后面的条件不用where，使用having表达条件语句。


SELECT b.SID , b.SNAME , avgs from

    student b,(

    select SID , avg(SCORE) as avgs from SC

    group by SID

    having avg(SCORE)>=60

    ) a

where a.SID=b.SID

;

3.查询在 SC 表存在成绩的学生信息

PS：distinct表达去重含义。


select distinct student.* from student, SC

where student.SID=SC.SID

order by student.SID

;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和


select a.SID , a.SNAME , ALLC , ALLSC from student a

    left join (

    select SID , count(CID) as ALLC, sum(SCORE) as ALLSC

    from SC group by SID

    ) b

    on a.SID=b.SID

order by a.SID

;

4.1 查有成绩的学生信息

PS：exists()与in()两个语句用途基本一致，但根据外层查询表与子查询表的数据规模，选用节省时间的语句，外层查询表与子查询表用 exists()，外层查询表大于子查询表用 in() 。


select * from student

where exists (

    select SC.SID from SC

    where STUDENT.SID = SC.SID

);


select * from STUDENT

where STUDENT.SID in (

    select SID from SC

);

5.查询「李」姓老师的数量

PS：涉及聚合函数与通配符的运用。count()与excel中的count函数同一个含义即为计数，通配符%的含义为替代一个或多个任意字符。


select count(TNAME) from teacher

where TNAME like '李%'
;

知识点整理

1、left/right/inner join on的区别，以及三者与（select * from A表，B表）该表达的区别。

2、exists与in的用法，数据量大时为了效率应选择哪个语句。

3、group by having/order by 等基本用法。

4、通配符种类和用法。

原本想发到第十题的，发现只是整理文本时间就太晚了。改天接着发~
今天的学习就到这里啦，有时间的话，会把上述知识点出专题整理~

一时耕耘一时爽，一直耕耘一直爽

2020年7月21日遂川县

SQL50题---1~5题

数据导入

练习题目

答案

知识点整理

猜你喜欢

热点阅读