1、建表语句

create schema "MYDATE" authorization "SYSDBA";
--学生表
create table MYDATE."student"(
Sno varchar(7) primary key,
Sname varchar(10) not null,
Ssex varchar(4) CHECK(SSex='男' or SSex='女'),
Sage int ,
Dept varchar(20));
--课程表
create table MYDATE."course"
(Cno varchar(10) primary key,
Cname varchar(20) not null,
Credit int Check(Credit>0),
Semester int);
--sc表
create table MYDATE."sc"
(Sno varchar(7)references MYDATE."student"(Sno),
Cno varchar(10) references MYDATE."course" (Cno),
Grade int check(Grade>0 and Grade<=100),
primary key(Sno,Cno)
);

2、插入数据

--student 学生表
insert into MYDATE."student" values('0811101','李勇','男',21,'计算机系');
insert into MYDATE."student" values('0811102','刘晨','男',20,'计算机系');
insert into MYDATE."student" values('0811103','王敏','女',20,'计算机系');
insert into MYDATE."student" values('0811104','张小红','女',19,'计算机系');
insert into MYDATE."student" values('0821101','张立','男',20,'信息管理系');
insert into MYDATE."student" values('0821102','吴宾','女',19,'信息管理系');
insert into MYDATE."student" values('0821103','张海','男',20,'信息管理系');
insert into MYDATE."student" values('0831101','钱小平','女',21,'通信工程系');
insert into MYDATE."student" values('0831102','王大力','男',20,'通信工程系');
insert into MYDATE."student" values('0831103','张珊珊','女',19,'通信工程系');

--course 课程表
insert into MYDATE."course" values('C001','高等数学',4,1);
insert into MYDATE."course" values('C002','大学英语',3,1);
insert into MYDATE."course" values('C003','大学英语',3,2);
insert into MYDATE."course" values('C004','计算机文化学',2,2);
insert into MYDATE."course" values('C005','Java',2,3);
insert into MYDATE."course" values('C006','数据库基础',4,5);
insert into MYDATE."course" values('C007','数据结构',4,4);
insert into MYDATE."course" values('C008','计算机网络',4,4);

--sc 学生结构表
insert into MYDATE."sc" values('0811101','C001',96);
insert into MYDATE."sc" values('0811101','C002',80);
insert into MYDATE."sc" values('0811101','C003',84);
insert into MYDATE."sc" values('0811101','C005',62);
insert into MYDATE."sc" values('0811102','C001',92);
insert into MYDATE."sc" values('0811102','C002',90);
insert into MYDATE."sc" values('0811102','C004',84);
insert into MYDATE."sc" values('0811102','C006',76);
insert into MYDATE."sc" values('0811102','C003',85);
insert into MYDATE."sc" values('0811102','C005',73);
insert into MYDATE."sc" values('0811102','C007',NULL);
insert into MYDATE."sc" values('0811103','C001',50);
insert into MYDATE."sc" values('0811103','C004',80);
insert into MYDATE."sc" values('0831101','C001',50);
insert into MYDATE."sc" values('0831101','C004',80);
insert into MYDATE."sc" values('0831102','C007',NULL);
insert into MYDATE."sc" values('0831103','C004',78);
insert into MYDATE."sc" values('0831103','C005',65);
insert into MYDATE."sc" values('0831103','C007',NULL);

commit;

3、单表查询

--（1）查询全体学生的学号与姓名，查询结果存入新表s1。
create or replace view MYDATE.s1
as
	select Sno,Sname from MYDATE."student";
--（2）查询全体学生的姓名、学号和所在系，查询结果存入新表s2。
create or replace view MYDATE.s2
as
	select Sname,Sno,Dept from MYDATE."student";
--（3）查询全体学生的详细记录，查询结果存入新表s3。
create or replace view MYDATE.s3
as
	select * from MYDATE."student";
--（4）查询全体学生的姓名及其出生年份，查询结果存入新表s4。
create or replace view  MYDATE.s4
as
	select Sname,(2023-Sage) from MYDATE."student";
--（5）查询计算机系全体学生，查询结果存入新表s5。
create or replace view  MYDATE.s5
as
	select  * from MYDATE."student" where Dept='计算机系';
--（6）查询所有年龄20岁以下的学生的姓名及年龄，查询结果存入新表s6。
create or replace view  MYDATE.s6
as
	select Sname,Sage from MYDATE."student" where Sage<20;
--（7）查询成绩不及格学生的学号，查询结果存入新表s7。
create or replace view  MYDATE.s7
as
	select Sno from MYDATE."sc" where Grade<60;
--（8）查询考试成绩在80～90之间的学生学号、课程号和成绩，查询结果存入新表s8。
create or replace view  MYDATE.s8
as
	select Sno,Cno,Grade from MYDATE."sc" where Grade>80 and Grade<90;
--（9）查询考试成绩不在80～90之间的学生学号、课程号和成绩，查询结果存入新表s9。
create or replace view  MYDATE.s9
as
	select Sno,Cno,Grade from MYDATE."sc" where Grade<=80 or Grade>=90;
--（10）查询信息管理系、通信工程系和计算机系学生的姓名和性别，查询结果存入新表s10。
create or replace view  MYDATE.s10
as
	select Sname,Ssex from MYDATE."student" where Dept='计算机系' or Dept='信息管理系' or Dept='通信工程系';	
--（11）查询信息管理系、通信工程系和计算机系三个系之外的其他系学生的姓名和性别，查询结果存入新表s11。
create or replace view  MYDATE.s11
as
	select Sname,Ssex from MYDATE."student" where  Dept not in('计算机系','信息管理系' ,'通信工程系');	
--（12）查询姓“张”的学生详细信息，查询结果存入新表s12。
create or replace view  MYDATE.s12
as
	select * from MYDATE."student" where Sname like '张%';	
--（13）查询姓“张”、姓“李”和姓“刘”的学生的详细信息，查询结果存入新表s13。
create or replace view  MYDATE.s13
as
	select * from MYDATE."student" where Sname like '张%' or Sname like '李%' or Sname like '刘%';	
--（14）查询名字的第2个字为“小”或“大”的学生的姓名和学号，查询结果存入新表s14。
create or replace view  MYDATE.s14
as
	select Sname,Sno from MYDATE."student" where Sname like '_小%' or Sname like '_大%';
--(15) 查询所有不姓“刘”的学生姓名，查询结果存入新表s15
create or replace view  MYDATE.s15
as
	select Sname from MYDATE."student" where Sname not like '刘%';
--（16）在Student表中查询学号的最后一位不是2、3、5的学生信息，查询结果存入新表s16。
create or replace view  MYDATE.s16
as
	select * from MYDATE."student" where Sno not like '%2' and Sno not like '%3' and Sno not like '%5';
--（17）查询还没有考试的学生的学号和相应的课程号，查询结果存入新表s17。
create or replace view  MYDATE.s17
as
	select Sno,Cno from MYDATE."sc" where Grade is null;
--（18）查询计算机系男生的姓名，查询结果存入新表s18。
create or replace view  MYDATE.s18
as
	select Sname from MYDATE."student" where Dept='计算机系' and Ssex='男';
--（19）查询C002和C003课程中考试成绩在80～90的学生的学号、课程号和成绩，查询结果存入新表s19。
create or replace view  MYDATE.s19
as
	select Sno,Cno,Grade from MYDATE."sc" where Cno in('C002','C003') and (Grade>80 and Grade<90);
--（20）查询修了“C002”课程的学生的学号及成绩，查询结果按成绩降序排列，查询结果存入新表s20。
create or replace view  MYDATE.s20
as
	select Sno,Grade from MYDATE."sc" where Cno='C002' Order by Grade desc;
--（21）查询全体学生详细信息，结果按系名升序排列，同一个系的学生按出生日期降序排列，查询结果存入新表s21。
create or replace view  MYDATE.s21
as
	select * from MYDATE."student"  order by Dept asc ,(2023-Sage) desc;
--（22）统计学生总人数，查询结果存入新表s22。
create or replace view  MYDATE.s22
as
	select count(*) from MYDATE."student" ;
--（23）统计选修了课程的学生人数，查询结果存入新表s23。
create or replace view  MYDATE.s23
as
	select count(distinct Sno) from MYDATE."sc";
--去重 distinct
--（24）计算学号为“0811101”的学生的考试总成绩，查询结果存入新表s24。
create or replace view  MYDATE.s24
as
	select sum(Grade) from MYDATE."sc" where Sno='0811101';
--（25）计算“0831103”学生的平均成绩，查询结果存入新表s25。
create or replace view  MYDATE.s25
as
	select Sno,Grade from MYDATE."sc" where Cno='C002' Order by Grade desc;
--（26）查询“C001”课程考试成绩的最高分和最低分，查询结果存入新表s26。
create or replace view  MYDATE.s26
as
	select max(Grade),min(Grade) from MYDATE."sc" where Cno='C001' ;
--（27）统计每门课程的选课人数，列出课程号和选课人数，查询结果存入新表s27。
create or replace view  MYDATE.s27
as
	select count(Cno),Cno from MYDATE."sc" Group by Cno;
--（28）统计每个学生的选课门数和平均成绩，查询结果存入新表s28。
create or replace view  MYDATE.s28
as
	select count(*),round(avg(Grade),2) from MYDATE."sc" group by Sno;
--（29）统计每个系的女生人数，查询结果存入新表s29。
create or replace view  MYDATE.s29
as
	select count(*),Ssex,Dept from MYDATE."student" group by Dept,Ssex having Ssex='女';
--（30）统计每个系的男生人数和女生人数以及男生的最大年龄和女生的最大年龄。结果按系名的升序排序，查询结果存入新表s30。
create or replace view  MYDATE.s30
as
	select count(Sno),max(Sage),Ssex,Dept from MYDATE."student" group by Dept,Ssex order by Dept asc;
--(31) 查询选课门数超过3门的学生的学号和选课门数，查询结果存入新表s31。
create or replace view  MYDATE.s31
as
	select count(*),Sno from MYDATE."sc" group by Sno having count(*)>3;
--(32) 查询选课门数大于等于4门的学生的平均成绩和选课门数，查询结果存入新表s32。
create or replace view  MYDATE.s32
as
	select count(*),avg(Grade) from MYDATE."sc" group by Sno having count(*)>=4;
--(33) 查询计算机系和信息管理系每个系的学生人数，查询结果存入新表s33。
create or replace view  MYDATE.s33
as
	select count(*),Dept from MYDATE."student" group by Dept having Dept='计算机系' or Dept='信息管理系';

4、多表查询

--（1）查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。
select stu.Sname,stu.Sno,sc.Grade from MYDATE.”student” stu 
inner join MYDATE.”sc” sc on stu.Sno=sc.Sno
where Dept='计算机系' ;

--（2）查询“信息管理系”修了“计算机文化学” 的学生姓名和成绩。
select stu.Sname,sc.Grade from MYDATE.”student” stu 
inner join MYDATE.”sc” sc on stu.Sno= sc.Sno
inner join MYDATE.”course” cr on sc.Cno=cr.Cno
where  Dept='信息管理系' and Cname='计算机文化学' ; 

--（3）查询所有选修了Java课程的学生情况，列出学生姓名和所在系。
select Sname,Dept from MYDATE.”student” stu
inner join MYDATE.”sc” sc on stu.Sno= sc.Sno
inner join MYDATE.”course” cr on sc.Cno=cr.Cno
where Cname='Java';

--（4）统计每个系的学生的考试平均成绩。
select Dept,avg(Grade) from MYDATE.”student” stu
left join MYDATE.”sc” sc on stu.Sno= sc.Sno
group by Dept;

--（5）统计计算机系学生每门课程的选课人数、平均成绩、最高成绩和最低成绩。
select Cno,count(stu.Sno),avg(Grade),max(Grade),min(Grade) 
from MYDATE.”sc” sc
inner join MYDATE.”student” stu on sc.Sno= stu.Sno
where Dept='计算机系' 
group by Cno;

--（6）查询与刘晨在同一个系学习的学生的姓名和所在的系。
select s2.Sname,s2.Dept from MYDATE.”student” s1, MYDATE.”student” s2 
where s1.Dept=s2.Dept and s1.Sname='刘晨' and s2.Sname!='刘晨';

--（7）查询与“数据结构”在同一个学期开设的课程的课程名和开课学期。
select c2.Cname,c2.Semeste from MYDATE.”course” c1, MYDATE.”course” c2 
where c1.Semeste=c2.Semeste and c1.Cname='数据结构' and c2.Cname!='数据结构';

--（8）查询至少被两个学生选的课程的课程号。
select cr.Cno,count(sc.Sno) from MYDATE.”course” cr
left join MYDATE.”sc” sc on cr.Cno=sc.Cno
group by cr.Cno having count(sc.Sno)>=2 ;

--（9）查询全体学生的选课情况，包括选修了课程的学生和没有选修课程的学生。
select * from MYDATE.”student” stu
left join MYDATE.”sc” sc on stu.Sno=sc.Sno
left join MYDATE.”course” cr on sc.Cno=cr.Cno;

--（10）查询没人选的课程的课程名。
select Cname from MYDATE.”course” cr
left join MYDATE.”sc” sc on cr.Cno=sc.Cno
where sc.Sno is null;

--（11）查询计算机系没有选课的学生，列出学生姓名和性别。
select Sname,Ssex from MYDATE.”student” stu
left join MYDATE.”sc” sc on stu.Sno=sc.Sno
where Dept='计算机系' and sc.Cno is null;

--（12）统计计算机系每个学生的选课门数，包括没有选课的学生。
select stu.Sno,count(sc.Cno) from MYDATE.”student” stu
left join MYDATE.”sc” sc on stu.Sno=sc.Sno
group by stu.Sno;

--（13）查询信息管理系选课门数少于3门的学生的学号和选课门数，包括没有选课的学生。查询结果按选课门数递增排序。
select stu.Sno,count(Cno) from MYDATE.”student” stu 
left join MYDATE.”sc” sc on stu.Sno=sc.Sno
where Dept='信息管理系'
group by stu.Sno
having count(Cno)<3
order by count(Cno)asc;

--（14）查询考试成绩最高的三个成绩，列出学号、课程号和成绩。
select top 3 Sno,Cno,Grade from(select Sno,Cno,Grade from MYDATE.”sc” where Grade is not null order by Grade desc); 

--(15) 查询Java考试成绩最高的前三名的学生的姓名、所在系
select top 3 * from
(
	select stu.Sname,Dept from MYDATE.”student” stu 
	inner join MYDATE.”sc” sc on stu.Sno= sc.Sno
	inner join MYDATE.”course” cr on sc.Cno=cr.Cno
	where Cname='Java'
	order by Grade desc
);

--（16）查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数。
select top 2 * from
(
	select sc.Cno,count(Sno) from  MYDATE.”sc” sc 
	inner join MYDATE.”course” cr on sc.Cno=cr.Cno
	group by sc.Cno
	order by count(Sno) asc
);

--（17）查询计算机系选课门数超过2门的学生中，考试平均成绩最高的前2名（包括并列的情况）学生的学号、选课门数和平均成绩。
select top 2 * from
(
	select stu.Sno,count(Cno),avg(Grade) from MYDATE.”student” stu
	inner join MYDATE.”sc” sc on stu.Sno=sc.Sno
	where Dept='计算机系' 
	group by stu.sno
	having count(Cno)>2
	order by avg(Grade) desc
);

--（18）将计算机系的学生信息保存到#ComputerStudent局部临时表中。
create or replace view MYDATA.ComputerStudent
as
	select * from MYDATE.”student” where Dept='计算机系' ;
	
--（19）将选了Java课程的学生的学号及成绩存入永久表Java_Grade中。
create or replace view MYDATA.Java_Grade
as
	select Sno,Grade from MYDATE.”sc” sc 
	inner join MYDATE.”course” cr on sc.Cno=cr.Cno
	where cr.Cname='Java';
	
--（20）统计每个学期开设的课程总门数，将结果保存到永久表Cno_Count表中
create or replace view MYDATA.Cno_Count
as
	select Semeste,count(Cno) from MYDATE.”course” group by Semeste;
	
--（21）查询第2学期总学分和课程总门数。
select sum(Credit),count(Cno) from MYDATE.”course” group by Semeste having Semeste=2 ;