Oracle day10

create table category(
       类目id number,
       父类目id number,
       描述 char(500)       
)
--insert into category(类目id,父类目id) values(1001,1);
/*insert into category values(1,0,'0为根类目');
insert into category(类目id,父类目id) values(1002,2);
insert into category(类目id,父类目id) values(1001003,1001);
insert into category(类目id,父类目id) values(1001004,1001);
insert into category(类目id,父类目id) values(2,0);
insert into category(类目id,父类目id) values(1002005,1002);*/

select* from category;

with tt as (select c1.类目id 三级类目,c2.类目id 二级类目,c2.父类目id from category c1
join category c2 on c1.父类目id = c2.类目id) 
select t1.三级类目,t1.二级类目,t2.二级类目 一级类目,t2.父类目id 根类目 from tt t1 join tt t2 
on t1.父类目id = t2.二级类目

select c1.类目id 三级类目,c2.类目id 二级类目,c3.类目id 一级类目，c3.父类目id 根类目 from category  c1
join category c2 on c2.类目id=c1.父类目id
join category  c3 on c3.类目id=c2.父类目id

create table stu_table(
       sno number primary key,
       card_id number not null unique,
       name char(500) not null,
       gender char(3) check(gender in ('男','女'))
)
/*insert into stu_table values(101,10001,'张三','男');
insert into stu_table values(102,10002,'李四','女');
insert into stu_table values(103,10003,'王五','男');
insert into stu_table values(104,10004,'赵六','女');*/
create table grade(
       sno number references stu_table(sno),
       levle char 
)
--删除字段
alter table grade drop column levle;

--增加字段
alter table grade add grade char(50);
alter table stu_table add address char(500);

select * from stu_table;
select * from grade;
/*insert into grade values(101,'一年级');
insert into grade values(102,'二年级');
insert into grade values(103,'三年级');
insert into grade values(104,'四年级');
--105 插不进去，因为主表stu_table 中不含有sno=105的数据
insert into grade values(105,'五年级');
*/

--修改表名
alter table stu_table rename to stu;
--修改字段属性
alter table stu modify address number;
--修改字段名
alter table stu rename column address to age;
--添加约束
alter table stu add constraint c_age check(age between 1 and 120)
--删除约束
alter table stu drop unique(card_id);
alter table stu add constraint c_unique unique(card_id);

update stu set age = 22 where sno = 101;
--删除表
--drop table 表名

select * from stu;
select OWNER,CONSTRAINT_NAME 本表约束名
,TABLE_NAME,R_OWNER,R_CONSTRAINT_NAME 外键关联他表约束名
from user_constraints where TABLE_NAME in('stu','grade');
------------------------------------------------------------------------------------

--给四张表创建备份
create table student1 as select * from student;
create table course1 as select * from course;
create table sc1 as select * from sc;
create table teacher1 as select * from teacher;

drop table student1 ;
drop table course1 ;
drop table sc1 ;
drop table teacher1 ;

--1.删除学习‘谌燕’老师课的SC 表记录：
select * from sc1;
delete from sc1 where cno in 
(select cno from course1 where tno in 
(select tno from teacher1 where tname = '谌燕'));

--2.将‘c002’课程的成绩增加5分：
update sc1 set score = score+5 where cno = 'c002';

--3.将‘c001’课程成绩小于80分的同学的成绩增加10分：
update sc1 set score = score+10 where cno = 'c001' and score < 80;

--4.增加一个学生信息：学号：'s013'，姓名：'王麻子'，年龄：28，性别：男：
insert into student1 values('s013','王麻子',28,'男')

--5.向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号‘c002’课程的同学学号、‘c002’和‘c002’号课的平均成绩：
insert into sc1 select sno  ,'c002',(select avg(score) from sc1 where cno = 'c002') 
from sc1 where sno not in (select sno from sc1 where cno = 'c002') 

--6.找出没有选择‘c003’课程的学生，并为他们选上‘c003’课程，默认分类为60分：

insert into sc1 select t.sno
,'c003',60 from (select distinct sno from sc1 where sno not in 
(select sno from sc1 where cno = 'c003')) t

--7.给所有女学生的成绩加10分：
update sc1 set score = score + 10 where sno in 
(select sno from student1 where ssex = '女');

--8.创建一张和sc表相同结构的表，并将‘s001’和‘s002’学生的选课信息插入新表中：
create table sc2 as select * from sc1;
truncate table sc2;
select * from sc2;
insert into sc2 select sno,cno,score from (select * from sc1 where sno in ('s001','s002'));

--9.将所有‘c001’课程成绩低于平均成绩的同学的分数改为60分：
update sc1 set score = 60 where cno = 'c001' and score < (select avg(score) from sc1)

--10.删除‘s002’同学选择‘c001’课程的记录：
delete from sc1 where sno='s002' and cno = 'c001'

--11.删除‘s002’同学的‘c001’课程的成绩：
update sc1 set score = null where sno='s002' and cno = 'c001'

--12.将s001学生的所有课程成绩改为他自己的平均成绩：
update sc1 set score =
(select avg(score) score from sc1 where sno = 's001'group by sno)
where sno = 's001';

--13.将s001学生的所有课程成绩改为各科的平均成绩：
merge into sc1 t
using (select 's001' as sno,cno,avg(score) score from sc1 group by cno) s
on (t.sno = s.sno and t.cno = s.cno)
when matched then
update set t.score = s.score;

--14.使用备份表将30号部门的员工工资修改为10号部门的平均工资加上300元：
create table emp_bak as select * from emp;
select * from emp_bak;
update emp_bak set sal = 300 + (select avg(sal) from emp_bak where deptno = 10) where deptno = 30

--15.给任职日期超过30年的员工加薪10%：
update (select * from emp_bak where months_between(sysdate,hiredate) > 30*12) set sal = sal+0.1*sal 

--16.删除下属工资低于1500的领导员工信息：
delete from emp_bak where empno in
(select distinct mgr from emp_bak where sal < 1500);

--17.将各种工作的最低薪金，上调1500：
update emp_bak set sal = sal+1500 where (job,sal) in
(select job,min(sal) from emp_bak group by job);

--18.删除与‘SCOTT’从事相同工作的所有雇员：
delete from emp_bak where job = 
(select job from emp_bak where ename = 'SCOTT')

--19.将工作地点在‘NEW YORK’或‘CHICAGO’的员工工资增加500：
create table dept_bak as select * from dept;
update emp_bak set sal=sal+500 where deptno in 
(select deptno from dept_bak where loc in ('NEW YORK','CHICAGO'));

--20.更改奖金为NULL的员工，将奖金设置为0：
update emp_bak set sal = 0 where comm is null;

-- 创建一张学生表(stuu)，包含以下信息，学号(主键)(sno)，姓名(sname)，年龄(sage)，性别(ssex)，家庭住址(addr)，联系电话(phone)；
drop table stuu;
create table stuu(
       sno number primary key,
       sname char(50),
       sage number,
       ssex char(3),
       addr char(500),
       phone char(11)       
)
-- 修改学生表的结构，添加一列信息，学历(edu)；
alter table stuu add edu char(50);

-- 修改学生表的结构，删除一列信息，家庭地址(addr)；
alter table stuu drop column addr;
alter table stuu modify sno char(50);

-- 向学生表添加如下信息：
/*
学号   姓名   年龄   性别   联系电话   学历

s01   张三     22     男     123      小学
s02   李四     21     男     119      中学
s03   王五     23     男     110      高中
s04   赵六     18     女     114      大学
*/
insert into stuu values('s01','张三',22,'男',123,'小学');
insert into stuu values('s02','李四',21,'男',110,'中学');
insert into stuu values('s03','王五',23,'男',123,'高中');
insert into stuu values('s04','赵六',18,'女',114,'小学');

create table stuu1 as select * from stuu;
create table stuu2 as select * from stuu;
select * from stuu1;
-- 修改学生表的数据，将电话号码以11开头的学员的学历改为“大专”；
update stuu1 set edu = '大专' where phone like '11%';

-- 删除学生表的数据，将姓王，并且性别为‘男’的记录删除；
delete from stuu1 where ssex = '男' and sname like '王%';

-- 查询学生表的数据，将所有年龄小于22岁的，学历为“大专”的，学生的姓名和学号显示出来；
select sname,sno from stuu1 where sage < 22 and edu = '大专';

-- 查询学生表的数据，查询所有信息，列出学历最高的前三名的记录；
select * from stuu;
select t.*,rownum from
(select s.*,decode(trim(s.edu),'大学',1,'大专',2,'高中',3,'中学',4,'小学',5) grade from stuu s order by grade)t
where rownum < 4;

-- 查询出所有学生的姓名、性别、年龄，按年龄降序排列；
select sname,ssex,sage from stuu order by sage desc;

-- 按照性别分组查询所有的平均年龄；
select ssex,avg(sage) from stuu group by ssex;