oracle数据库学习-练习2（修改与查询）

oracle数据库学习-练习2

• 练习1 建立了表空间与一些表，在练习2中将会对给表插入一些数据，并且做一些修改与查询
• 需要掌握的知识
  • 基本的Insert, update, delete语句
  • 掌握查询，多表查询，分组函数，子查询等

修改与查询

--实验三
--练习Insert, update, delete语句，对表中的数据进行增加，修改，删除

--向Student表中插入2行数据，1行为你的信息，另一行自定。
insert into student_lzq(sno, sname, ssex, sdept, sbirthday)
values('2013460422','lzq','男','软件学院',to_date('22-10-2001', 'dd-mm-yyyy'));
insert into student_lzq(sno, sname, ssex, sdept, sbirthday)
values('2013460400','张三','男','软件学院',to_date('21-10-2001', 'dd-mm-yyyy'));

insert into student_lzq(sno, sname, ssex, sdept, sbirthday)
values('2012340400','李四','男','CS',to_date('21-10-2001', 'dd-mm-yyyy'));

insert into student_lzq(sno, sname, ssex, sdept, sbirthday)
values('2012340401','王五','女','CS',to_date('21-10-2001', 'dd-mm-yyyy'));

insert into student_lzq(sno, sname, ssex, sdept, sbirthday)
values('2012340480','刘晨','女','CS',to_date('21-01-2001', 'dd-mm-yyyy'));

--向Course表中插入2行数据，1行为本门课程的信息，另一行自定。
insert into Course_lzq(Cno,Cname,ccredit) values(01,'数据库技术',3);
insert into Course_lzq(Cno,Cname,ccredit) values(1012,'高等数学',4);
insert into Course_lzq(Cno,Cname,Cpno,ccredit) values(1010,'计算机基础',1012,3);
insert into Course_lzq(Cno,Cname,Cpno,ccredit) values(02,'java技术',1010,4);



--向SC表中插入数据，插入你的这门课程的选课信息。
insert into SC_lzq(Sno,Cno,Grade) values( 2013460422,01,85);
insert into SC_lzq(Sno,Cno,Grade) values( 2013460422,02,97);

insert into SC_lzq(Sno,Cno,Grade) values( 2013460422,1010,95);
insert into SC_lzq(Sno,Cno,Grade) values( 2013460422,1012,90);

insert into SC_lzq(Sno,Cno,Grade) values( 2013460400,01,100);
insert into SC_lzq(Sno,Cno,Grade) values( 2013460400,02,90);

insert into SC_lzq(Sno,Cno,Grade) values( 2013460400,1010,95);
insert into SC_lzq(Sno,Cno,Grade) values( 2013460400,1012,90);

insert into SC_lzq(Sno,Cno,Grade) values( 2012340400,01,100);
insert into SC_lzq(Sno,Cno,Grade) values( 2012340400,02,90);
insert into SC_lzq(Sno,Cno,Grade) values( 2012340400,1012,90);

insert into SC_lzq(Sno,Cno,Grade) values( 2012340401,01,70);
insert into SC_lzq(Sno,Cno,Grade) values( 2012340401,02,90);

insert into SC_lzq(Sno,Cno,Grade) values( 2012340480,01,70);
insert into SC_lzq(Sno,Cno,Grade) values( 2012340480,02,90);

commit;

--查找所有姓李的同学
select * from Student_lzq where sname like '李%';

--删除所有姓李的同学（需要联级删除约束才能直接删除）
delete from Student_lzq where sname like '李%'; 

--查询所有’软件学院’系同学的选课信息。
select t.* ,c.CNAME,c.CNO
from  student_lzq t ,SC_lzq a , course_lzq c 
where c.cno=a.cno and a.sno 
in(
	select b.sno from student_lzq b where b.sdept='软件学院' 
	) and t.sno=a.sno;

--将’cs’系同学的选课信息中的成绩置0。
update SC_lzq a set a.grade=0 
where a.sno 
in(select b.sno from student_lzq b where b.sdept='CS' )

--查询与’刘晨’在同一个系学习的学生
select a.* from student_lzq a 
where a.sdept 
in(select t.sdept from student_lzq t  where t.sname='刘晨')

--删除和刘晨在同一个系的学生的信息。
delete from student_lzq a 
where a.sdept 
in(select t.sdept from student_lzq t  where t.sname='刘晨')

--基本查询

--(1)查询每个选课学生的学生姓名、课程号和分数。（2表连接）
--(多表查询，等值连接)
select t.sname,s.cno,s.grade from student_lzq  t,sc_lzq s where t.sno=s.sno ;

--求学生的学号、姓名、选修课程的课程名及成绩。（3表连接）
select t.sno,t.sname,c.cname,s.grade 
from student_lzq  t,sc_lzq s,course_lzq c 
where t.sno=s.sno and c.cno=s.cno ;

--求选修课程号为1010或课程号为1012的学生姓名和学号。//去重distinct
select distinct t.sname,s.sno 
 from student_lzq t, sc_lzq s 
 where (s.cno=1010 or s.cno=1012) and t.sno=s.sno

--查询每一门课程的间接先行课的课程号
select c.cno，c.cname,s.cno as"先行课课程号" 
from course_lzq c,course_lzq s where c.cpno=s.cno

--查询与’刘晨’在同一个系学习的学生
select a.* from student_lzq a 
where a.sdept 
in(select t.sdept from student_lzq t  where t.sname='刘晨');

--查询选修了课程名为'java技术’的学生学号和姓名。
select distinct t.sname,s.sno  
from student_lzq t, sc_lzq s ,course_lzq c
 where c.cname='java技术' and c.cno=s.cno and t.sno=s.sno ;

--查询各个学生的平均成绩。
--select sno,avg(grade) from sc_lzq  group by sno
select t.sno,t.sname,avg(s.grade) 
from sc_lzq s,student_lzq t 
where t.sno=s.sno 
group by s.sno,t.sname,t.sno;

--查询平均成绩在80分以上的学生学号和平均成绩
select t.sno,t.sname,avg(s.grade) 
from sc_lzq s,student_lzq t 
	where t.sno=s.sno 
	group by s.sno,t.sname,t.sno 
	having  avg(s.grade)>80;

--查询选修了3门且平均分在80分以下课程的学生的学号
select t.sno,t.sname 
from sc_lzq s,student_lzq t 
	where t.sno=s.sno 
	group by s.sno,t.sname,t.sno 
		having  avg(s.grade)>80 and count(s.sno)=3;

--以学生为主体显示学生的信息及其选课的信息
select t.*,c.cno,c.cname 
from student_lzq t,sc_lzq s,course_lzq c 
	where t.sno=s.sno(+) and s.cno=c.cno ;

--对学生表和选课表做自然连接，并输出结果
select * from student_lzq natural join sc_lzq ;

--输出学号大于’ 刘晨’的学生的姓名和学号
select t.sname,t.sno from student_lzq t 
where t.sno>(select sno from student_lzq where sname='刘晨');

--查询选修2号课程且成绩等于“2012340400”号学生成绩（2号课程的成绩）的所有学生姓名。
select t.sname
from student_lzq t,sc_lzq s
where s.cno=2 and s.sno=t.sno and s.sno!=2012340400
	and s.grade=(select s.grade
		from sc_lzq s
		where s.cno=2 and s.sno=2012340400);

--嵌套查询
--求选修了’java技术’学生的学号和姓名。
select t.sno,t.sname from student_lzq t where t.sno in
(select s.sno from sc_lzq s,course_lzq c 
	where c.cname='java技术' and s.cno=c.cno);

--查询与刘晨在同一个系学习的学生
select a.* from student_lzq a where a.sdept in(select t.sdept from student_lzq t  where t.sname='刘晨');

--求选修1号课程的成绩高于刘晨的成绩（指刘晨选修的所有的课程的成绩）的学生学号姓名及成绩
select t.sno,t.sname,s.grade
from student_lzq t,sc_lzq s
where s.cno=1 and s.sno=t.sno
	and s.grade>(select max(s.grade)
		from sc_lzq s,student_lzq t
		where t.sname='刘晨' and s.sno=t.sno
		group by s.sno);

--求其他系中比cs系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）
select t.* from student_lzq t where t.sdept!='CS' and t.sbirthday>(
	select min(sbirthday) from student_lzq where sdept='CS' );
	
--求其他系中比计算机系学生年龄都小的学生姓名及年龄
select t.* from student_lzq t 
where t.sdept!='CS' and t.sbirthday>(
	select max(sbirthday) from student_lzq where sdept='CS' );

--求没有选修1010号课程的学生姓名
--minus为补集操作
(select t.* from student_lzq t)
minus (select st.* from student_lzq st,sc_lzq s
 where st.sno=s.sno and s.cno=1010) ;
 
--查询选修了全部课程的学生姓名。
select t.sname from student_lzq t where t.sno in(
	select s.sno from sc_lzq s 
	group by s.sno having count(s.sno)=
		(select count(*) from course_lzq c)
);

--求至少选修了 学号为“2012340401”的学生所选修全部课程 的学生学号和姓名
select t.sno,t.sname from student_lzq t where t.sno in(
	select s.sno from sc_lzq s where s.cno in 
		(select s.cno from sc_lzq s where s.sno=2012340401)
			group by s.sno having count(s.sno)=
				(select count(*) from sc_lzq s where s.sno=2012340401)
) and t.sno!=2012340401 ;

--求选修课程超过2门的学生的学号和姓名。
select t.sno,t.sname from student_lzq t where t.sno in( 
	select s.sno from sc_lzq s 
		group by s.sno having count(s.sno)>=2
);