sql语法DEMO8

数据库对象管理及 SQL 应用 1
基于以下代码创建表和插入数据，并完成以下实验
drop table if exists student;
drop table if exists class;
create table student(sno int,sname varchar(20),score int , month int,cno int); --score 表示当月月考总分，月考总分为 Null，说明当月缺考
create table class(cno int ,cname varchar(20)); --导入数据
insert into class values (1,'class1'),(2,'class2');
insert into student values (1,'Lee',610,1,1),(2,'Jerry',510,1,1),(5,'Lee',410,1,1), (3,'Tom',400,1,2),(4,'Jack',300,1,2),(6,'Jacy',NULL,1,2),(1,'Lee',410,2,1),(2,'Jenry',510,2,1)
,(5,'Lee',210,2,1),(3,'Tom',600,2,2),(4,'Jack',300,2,2),(6,'Jack',510,2,2),(1,'Lee',410,3,1), (2,'Jerry',510,3,1),(5,'Lee',NULL,3,1),(3,'Tom',NULL,3,2),(4,'Jack',300,3,2),(6,'Jack',410,3,2);
（1）输出每月月考总分都比学号为 5 的同学分数高的所有学生信息
--------------------------------------------------- -----------------------------------------------------
--解题思路：统计每个学生成绩大学学号为 5 的学生次数，计算月考次数，判断学生
出现次数是否等于月考次数
select * from student where sno in(select t1.sno from student t1
join (select month, nvl(score,0) as score from student where sno=5) t2
on t1.month=t2.month and t1.score>t2.score group by t1.sno
having count(t1.sno)=(select count(distinct month) from student));
（2）输出每次月考缺考的学生信息，要求打印姓名、班级编号和缺考次数
--------------------------------------------------- ----------------------------------------------------- select sname,cno,count(1) from student where score is null group by sname,cno;
（3）输出每次月考都和 tom 同时缺考的所有学生信息，要求打印学号、姓名和
月考总分
--------------------------------------------------- -----------------------------------------------------
--解题思路：分别统计 tom 那几个月缺考以及总缺考几次，计算其他学生缺考月份和
缺考次数是否与 tom 相同
select student.sno,sname,score from student join
(select sno,month,count(1) over(partition by sno) from student where score is null and
sname<>'Tom')t1 on student.sno=t1.sno join
(select month,count(1) over(partition by sno) from student where sname='Tom' and
score is null) t2 on t1.month=t2.month and t1.count=t2.count;
（4）输出全校月考中位数分数。
--------------------------------------------------- -----------------------------------------------------
--解题思路：使用 row_number 根据月粉分组，成绩排序，给所有成绩生产一个从 1 开始的序
号，根据序号选取中间值，
如果序号最大值为偶数，选取序号=最大学号/2 和下位数取平均值，如果需要为奇数，选取序
号为 ceil（最大序号/2)
select month,round(avg(score),2) from
(select student.*,count,row_number() over(partition by student.month order by
nvl(score,0)) as rowno from student, (select month,count(1) as count from student group by month ) tcount where
student.month=tcount.month)
where case when count%2=0 then rowno=(count/2) or rowno=(count/2)+1 else
rowno=ceil(count/2) end group by month;
（5）统计每个班每次月考的最高分数，要求打印班级名称，考试时间和月考分
数
--------------------------------------------------- ----------------------------------------------------- select cname,month,max(score) from student,class where student.cno=class.cno
group by cname,month order by cname,month;
2、数据库对象管理及 SQL 应用 2
表同上题
（1）输出 class1 班级中比 class2 班级每月月考最低分还低的学生信息，要求
打印学号、姓名和月考总分
--------------------------------------------------- ----------------------------------------------------- select t1.sno,t1.sname,t1.score from
(select * from student where cno=(select cno from class where cname='class1')) t1
join
(select month,min(nvl(score,0)) score from student where cno=(select cno from class
where cname='class2') group by month )t2
on t1.month=t2.month and nvl(t1.score,0) <t2.score;
（2）打印月考总分平均分最高的学生信息、输出学号，姓名和月考总分平均分
--------------------------------------------------- ----------------------------------------------------- select sno,sname ,round(avg(nvl(score,0)),2) avgscore from student group by
sno ,sname having avgscore=(select max(score) from (select round(avg(nvl(score,0)),2)
from student group by sno,sname));
（3）输出每个学生月考平均分和最高月考平均分学生之间的分数差距，打印学
号、姓名、月考平均分和差距分数
--------------------------------------------------- ----------------------------------------------------- select sno,sname,round(avg(nvl(score,0)),2) avgscore ,(select max(avgscore) from
(select round(avg(nvl(score,0)),2) avgscore from student group by sno))-avgscore
from student group by sno ,sname;
3、数据库对象管理及 SQL 应用 3
基于以下学生成绩表，完成以下实验要求
--------------------------------------------------- -----------------------------------------------------
--创建表
create table stu(id int,math int,art int,phy int ,music int); --导入数据
insert into stu values(1,60,33,66,86);
insert into stu values(2,61,53,86,75);
insert into stu values(3,70,63,66,53);
insert into stu values(4,90,63,76,65);
insert into stu values(5,59,69,79,95);
insert into stu values(6,63,73,66,36);
insert into stu values(7,61,53,88,75);
insert into stu values(8,74,63,64,53);
insert into stu values(9,40,83,78,35);
insert into stu values(10,59,49,89,65);
（1）求 math、phy 总成绩以及 art、music 的总成绩
--------------------------------------------------- ----------------------------------------------------- select sum(math+phy),sum(art+music) from stu;
（2）计算学生总成绩，并基于总成绩排序
--------------------------------------------------- ----------------------------------------------------- select id,math+phy+art+music all_score from stu order by all_score desc;
（3）art 和 music 总分排名前 5 的总成绩加 5 分，查询最终的所有学生总成绩
--------------------------------------------------- -----------------------------------------------------
(select stu.id,score+math+phy allscore from stu,(select id,art+music+5 score,rank()
over(order by score desc) as ranknum from stu) stu2 where stu.id=stu2.id and
ranknum<=5)
union all
(select stu.id ,score+math+phy allscore ,rank() over(order by score desc ) as ranknum
from stu,(select id,art+music score from stu) stu2 where stu.id=stu2.id and
ranknum>5);
4、数据库对象管理及 SQL 应用 4
基于以下学生成绩表，完成以下实验
--创建表
create table scopes(student_id int,chinese int ,math int,english int ,music int); --导入数据
insert into scopes values(1,90,88,100,88);
insert into scopes values(2,88,88,100,99);
insert into scopes values(3,87,89,98,89);
insert into scopes values(4,91,88,76,99);
insert into scopes values(5,92,88,78,98);
insert into scopes values(6,93,88,76,87);
（1）计算每个学生的 chinese 和 math 总分，以及 english 和 music 总分。要求
一条 SQL 语句实现，不能使用临时表
--------------------------------------------------- ----------------------------------------------------- select student_id ,chinese+math,english+music from scopes;
（2）目前有一张权重表(各科有不同的权重，目前权重策略有 2 各），请算出每
个学生结合权重计算后的成绩总和。要求一条 SQL 语句实现，不能使用临时表。
每个学生都对应两个权重成绩
--权重表结构
create table weight(weight_id int ,chinese decimal(10,2),math decimal(10,2), english decimal(10,2),music decimal(10,2));
insert into weight values(1,0.3,0.2,0.2,0.3);
insert into weight values(2,0.2,0.1,0.3,0.4);
要求最终效果如下
Student_id Weight_id Weight_sum
1 1 1 87.7
2 1 2 67.7
3 2 1 78.8
4 2 2 66.7
--------------------------------------------------- ----------------------------------------------------- select student_id,weight_id ,(t1.chinese*t2.chinese +t1.math*t2.math+
t1.english*t2.english +t1.music*t2.music) as weight_sum from scopes t1, weight t2 order by 1,2;
（3）结合上面的结果，将一个学生对应的两个权重成绩，合到一行。要求一条
SQL 语句实现，不能使用临时表。
Student_id Weight_sum1 Weight_sum2
1 1 87.7 67.7
2 2 78.8 66.7
--------------------------------------------------- ----------------------------------------------------- select tb1.student_id,weight_sum1,weight_sum2 from
(select student_id,(t1.chinese*t2.chinese+t1.math*t2.math+t1.english*t2.english+
t1.music*t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1)tb1
join
(select student_id,(t1.chinese*t2.chinese+t1.math*t2.math+t1.english*t2.english+
t1.music*t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2)tb2
on tb1.student_id=tb2.student_id order by 1;
（4）按照两个权重成绩之和的大小，进行从大到小排序，且生成排序序号，要
求生成连续排序序号，相同的值具有相同的序号。一条 SQL 语句实现，不能使用
临时表。
效果如下：
Student_id Weight_sum1 Weight_rank Weight_sum2 Weight_rank
1 87.8 1 67.7 1
2 78.7 2 66.7 2
--------------------------------------------------- ----------------------------------------------------- select tb1.student_id ,weight_sum1,dense_rank() over(order by weight_sum1 desc)
weight_rank1 ,weight_sum2,dense_rank() over(order by weight_sum2 desc)
weight_rank2 from
(select student_id,(t1.chinese*t2.chinese+t1.math*t2.math+t1.english*t2.english+
t1.music*t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1)tb1
join
(select student_id,(t1.chinese*t2.chinese+t1.math*t2.math+t1.english*t2.english+
t1.music*t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2)tb2
on tb1.student_id=tb2.student_id
5、性能优化 1
当 前 有 一 张 表 create table test(student_id int ,class_id int ,kemu
varchar(20) ,score int)
里面有 8 万条数据
--------------------------------------------------- -----------------------------------------------------
insert into test values(101,202201,'语文','80');
insert into test values(102,202201,'语文','85');
insert into test values(103,202201,'语文','66');
insert into test values(101,202201,'数学','65');
insert into test values(102,202201,'数学','62');
insert into test values(103,202201,'数学','73');
insert into test values(101,202201,'英语','90');
insert into test values(102,202201,'英语','88');
insert into test values(103,202201,'英语','78');
insert into test values(101,202201,'英语','90');
insert into test values(102,202201,'英语','88');
insert into test values(103,202201,'英语','78');
insert into test values(201,202202,'语文','90');
insert into test values(202,202202,'语文','95');
insert into test values(203,202202,'语文','86');
insert into test values(201,202202,'数学','85');
insert into test values(202,202202,'数学','72');
insert into test values(203,202202,'数学','83');
insert into test values(201,202202,'英语','95');
insert into test values(202,202202,'英语','88');
insert into test values(203,202202,'英语','88');
（1）查 202202 班级里面语文最低分是多少，要保障走索引
--------------------------------------------------- -----------------------------------------------------
--收集统计信息
analyze test; --获取推荐索引
select * from gs_index_advise('select min(score) from test where kemu=''语文'' and
classid=''202202'''); --创建索引
create index idx01 on test(classid,kemu ) --查询 202202 班级里面语文最低分
select /*+indexscan(test,idx01)*/ min(score) from test where kemu='语文' and class_id='202202';
（2）查 202202 班级同一科目成绩比 202201 班级最高分高的同学，根据以下 SQL
优化重写
原 SQL:select * from test where score>(select max(score) from test wehre
class_id='202201') and class_id='202202'
--------------------------------------------------- ----------------------------------------------------- select * from test t1 join (select max(score) as max_score from test
where class_id='202201' )t2 on t1.score>t2.max_score where t1.class_id='202202';
6、性能优化 2
当前有三张表，分别是学生信息表 student 和 202201 班级成绩表 score1，2022
班级成绩表 score2。考试时详细查看题目并确定各表字段
（1）查 202201 班级和 202202 班级所有人语文成绩前 10 的记录，第一个查询使
用 union
--------------------------------------------------- ----------------------------------------------------- select * from score1 order by chinese limit 10
union
select * from score2 order by chinese limit 10;
（2）对以上 SQL 语句进行优化
--------------------------------------------------- ----------------------------------------------------- --将 union 更改为 union all
select * from score1 order by chinese limit 10
union all
select * from score2 order by chinese limit 10;
(3）查看两个班级相同的科目，202201 班在 score2 中不存在的成绩，要求使用
not in（考试时详细确认题目要求，查看是具体哪些科目成绩）
--------------------------------------------------- ----------------------------------------------------- select chinese from score where chinese not in (select chinese from score2);
（4）对以上 SQL 语句进行优化
--------------------------------------------------- ----------------------------------------------------- --将 not in 更改为 not exists
select chinese from score where not exists (select chinese from score2);
（5）查询班级 202201 语文成绩最高的学生，要求先创建索引，并且能保证一定
会使用索引
--------------------------------------------------- ----------------------------------------------------- --查询 SQL 语句
select max(chinese) from score1; --使用索引推荐
Select gs_index_advise(‘select max(chinese) from score1); --创建索引
Create index idx01 on score1(chinese);
7、性能优化 3
基于学生表(sno,sname,cno),班级表(cno,cname),课程表(courid,courname)，
成绩表(sno,courid,score)完成关联查询
--创建表
create table student(sno varchar(20),sname varchar(50),cno int);
create table class(cno int,cname varchar(50));
create table course(courid int ,courname varchar(50));
create table score(sno varchar(20),courid int ,score int); --数据插入
insert into student values('1001','张三',1),('1002','李四',1),('1003','王五',2),('1004','赵六',2);
insert into class values(1,'1 班'),(2,'2 班');
insert into course values(1,'语文'),(2,'数学'),(3,'英语'),(4,'物理');
insert into score values('1001',1,84),('1001',1,64),('1001',2,86),('1001',2,94);
insert into score values('1001',3,76),('1001',3,56),('1001',4,48),('1001',4,84);
insert into score values('1002',1,83),('1002',1,85),('1002',2,46),('1002',2,74);
insert into score values('1002',3,65),('1002',3,76),('1002',4,56),('1002',4,98);
insert into score values('1003',1,86),('1003',1,74),('1003',2,88),('1003',2,54);
insert into score values('1003',3,86),('1003',3,76),('1003',4,67),('1003',4,76);
insert into score values('1004',1,100),('1004',1,100),('1004',2,87),('1004',2,86);
insert into score values('1004',3,69),('1004',3,67),('1004',4,84),('1004',4,92);
（1）语文平均成绩大于 80 的所有成绩，输出班级名，学号（或班级号），平均
成绩，要求 where 推荐里有两个非相关子查询
--------------------------------------------------- -----------------------------------------------------
--in where 均属于子查询
select t1.sno,t3.cname,avg(score) from score t1 ,student t2,class t3
where t1.sno=t2.sno and t2.cno=t3.cno and
t1.sno in(select sno from score where courid in (select courid from course
where courname='语文') group by sno having avg(score)>80)
and courid in (select courid from course where courname='语文')
group by t1.sno,t3.cname;
（2）在上一题基础上，将 where 条件中的非相关子查询改为 from 后边的范围表
--------------------------------------------------- ----------------------------------------------------- 
select t1.sno,cname,avgscore from (select t3.sno,t3.cno,avg(score) avgscore from
score t1,course t2,student t3 where t1.sno=t3.sno and t1.courid=t2.courid and
courname='语文' group by t3.sno,t3.cno having avgscore>80) t1,class t2 where
t1.cno=t2.cno
（3）在上一题的基础上，将 from 后面的子查询优化为附表的关联查询
--------------------------------------------------- ----------------------------------------------------- 
select t3.cno,t3.cname,avg(score) avgscore from score t1,student t2,class t3, course t4 where t1.sno=t2.sno and t2.cno=t3.cno and t1.courid=t4.courid and
courname='语文' group by t1.sno ,t3.cno ,t3.cname having avgscore>80;
8、存储过程
当前有一张表 stu(sno,math,art,physics,cno);
create table stu(sno varchar(30),math float ,art float ,physics float,cno int);
insert into stu values('1001',56,85,72,1),('1002',66,75,82,1);
（1）查看学生每门成绩与每门平均成绩的差值
--------------------------------------------------- ----------------------------------------------------- 
select sno,math,math-avgmath,art,art-avgart,physics,physics-avgphysic
from stu,(select
round(avg(nvl(math,0)),2) avgmath, round(avg(nvl(art,0)),2) avgart, round(avg(nvl(physics,0)),2) avgphysic
from stu);
（2）编写存储过程，输入学生 id 和对应科目名称输出对应的绩点值，对于学生
每科成绩，0-59,绩点 0，60-69 绩点 0.1，70-79，绩点 0.2，80-89，绩点 0.3，
90-100 绩点 0.4
--------------------------------------------------- ----------------------------------------------------- 
create or replace procedure pro_cal_point(id1 int,coursename varchar(30),point out
float) as
begin
case when coursename='math' then
select (case when math<=59 then 0
when math<=69 then 0.1
when math<=79 then 0.2
when math<=89 then 0.3
when math<=100 then 0.4
else 0 end) into point from student where student_id=id1 limit 1;
when coursename='physical' then
select (case when physical<=59 then 0
when physical<=69 then 0.1
when physical<=79 then 0.2
when physical<=89 then 0.3
when physical<=100 then 0.4
else 0 end) into point from student where student_id=id1 limit 1;
when coursename='art' then
select (case when art<=59 then 0
when art<=69 then 0.1
when art<=79 then 0.2
when art<=89 then 0.3
when art<=100 then 0.4
else 0 end) into point from student where student_id=id1 limit 1;
when coursename='music' then
select (case when music<=59 then 0
when music<=69 then 0.1
when music<=79 then 0.2
when music<=89 then 0.3
when music<=100 then 0.4
else 0 end) into point from student where student_id=id1 limit 1;
end case;
end;
/
（3）编写存储过程，根据学号，班级，获取学生的总分
--------------------------------------------------- ----------------------------------------------------- 
create or replace procedure get_score(stuid varchar(30),cid int ,all_score out float)
as
begin
select (math+art+physcis) into all_score from stu where sno=stuid and cno=cid;
end;
/
9、触发器 1
本体根据以下表完成相应触发器创建使用
create table tab1(sname text,deptno int ,salary float,title text);
create table dept(id int,dept_name text);
create table logger(sname text, dept_name text ,log_date date);
创建触发器，要求在 tab1 表插入一行数据库时，自动往 logger 表中插入一条记
录，记录 sname 和部门名称，并用当天的日期来标记该行数据的生成时间
（1）创建触发器函数 T_INS_F
--------------------------------------------------- ----------------------------------------------------- 
create or replace function T_INS_F() returns trigger as
$$
begin
insert into logger values(new.name,(select dept_name from dept
where id=new.deptno),sysdate);
return new;
end;
$$language plpgsql;
（2）创建触发器 T_INS_TR
--------------------------------------------------- -----------------------------------------------------
 create trigger T_INS_TR after insert on tab1 for each row execute
procedure T_INS_TR();
(3) 禁用表 tab1 上的所有触发器
--------------------------------------------------- -----------------------------------------------------
 Alter table tab1 disable trigger all;
（4）删除 T_INS_TR 触发器
--------------------------------------------------- ----------------------------------------------------- 
Drop trigger T_INS_TR ON TAB1
10、触发器 2
根据以下表完成相应的触发器创建使用
--创建表
create table stu(
sid int, sname varchar(20))with (orientation=row,compression=no)
distribute by hash(sid) to group group_version1;
create table selecttive(
sid int, course_name varchar(20))with (orientation=row,compression=no)
distribute by hash(sid) to group group_version1; --导入数据
insert into stu values(1,'tom'),(2,'marry'),(3,'lzy');
insert into selecttive values(1,'数学'),(2,'语文'),(3,'英语');
（1）创建触发器函数 delete_stive
--------------------------------------------------- ----------------------------------------------------- 
create or replace FUNCTION delete_stive() returns trigger as
$$
begin
delete from selecttive where sid=old.sid;
return old;
end;
$$language plpgsql;
（2）创建触发器 delete_selecttive_trigger
--------------------------------------------------- ----------------------------------------------------- 
create trigger delete_selecttive_trigger before delete on stu
for each row execute procedure delete_stive();