Oracle入门--day4 -扩展练习

主要是综合运用，但且看题：

 

-- 学生表：学号、姓名、性别、年龄
-- 教师表：编号、姓名
-- 课程表：课程编号、课程名称、教师编号
-- 成绩表：学号、课程编号、成绩

---------建表、调整表开始 -------
--  继续采用昨天的表  --
select * from t_student;
select * from t_teacher;
select * from t_course;
select * from t_grade;
---------建表、调整表结束 -------

-- 1、建一张视图，把学生、课程、老师关联起来，
--查询出每个学生的信息、选修了的课程、及任课教师
drop view stu_cou_tea_gra;

--隐性连接，都有数据才会显示
create view stu_cou_tea_gra 
as select s.sno,s.sname,c.cno,c.cname,g.grade,t.tno,t.tname
from t_student s,t_teacher t,t_course c,t_grade g
where c.tno = t.tno and g.sno = s.sno 
and g.cno = c.cno ; 

--左连接，更符合需求
create view stu_cou_tea_gra 
as select s.sno,s.sname,c.cno,c.cname,g.grade,t.tno,t.tname
from t_student s left join t_grade g
on  g.sno = s.sno left join t_course c 
on g.cno = c.cno left join t_teacher t
on c.tno = t.tno ;

select * from stu_cou_tea_gra;


-- 2、建一张视图，统计出各科老师授课的平均分，并按分数进行排序
create view tea_avg(gra) 
as
select t.tno,t.tname,c.cno,c.cname,tab.gravg
from t_teacher t ,t_course c,
(select cno,avg(grade) gravg from t_grade  
group by cno order by avg(grade) desc) tab 
where tab.cno = c.cno and t.tno = c.tno; 

-- 员工表：工号、姓名、上级领导工号、职位、基本工资、绩效工资、部门名称、入职时间，

---------建表、调整表开始 -------
select * from t_employee;

create table t_employee2(
eid varchar2(11) primary key,
ename varchar2(20) not null,
superiorid varchar2(11),
jobname varchar2(20),
salary number(7,2),
bonus number(7,2),
depname varchar(20),
hiredate date
);
select * from t_employee2;
delete from t_employee2 where eid = 'yc000';

insert into t_employee2 values('yc000','明','','总经理',1000,15000,'办公室',date'2008-3-12');
insert into t_employee2 values('yc001','张三','yc000','副总经理',8000,5000,'办公室',date'2010-2-12');


insert into t_employee2 values('yc002','李斯','yc001','组长一号',4000,5000,'生产线',date'2011-2-12');
insert into t_employee2 values('yc003','王武','yc001','组长二号',5000,4000,'生产线',date'2011-2-12');

insert into t_employee2 values('yc004','赵柳','yc002','工具人一号',3000,1500,'生产线',date'2018-2-12');
insert into t_employee2 values('yc005','孙琦','yc002','工具人二号',4000,500,'生产线',date'2018-2-12');

insert into t_employee2 values('yc006','周霸','yc003','工具人三号',2500,2000,'生产线',date'2018-2-12');
insert into t_employee2 values('yc007','吴玖','yc003','工具人四号',3500,1000,'生产线',date'2018-2-12');

---------建表、调整表结束 -------

-- 1、查询出“张三”管辖下的所有员工
--递归查询
select * from t_employee2 e 
start with e.ename = '张三' 
connect by e.superiorid = prior e.eid;


-- 2、查出所有绩效工资高于基本工资的员工数据
select * from t_employee2 where salary < bonus ;

-- 3、查出绩效工资高于基本工资60%的员工
select * from t_employee2 where (salary*0.6) < bonus ;

-- 4、查出基本工资相同的所有职位
--没看懂题目，同一职位基本工资一定相同？？那我数据全是错的。。。
select e1.depname,e2.depname,e1.salary 
from t_employee2 e1,t_employee2 e2
where e1.salary = e2.salary 
and e1.depname <> e2.depname;

-- 5、查出绩效工资为空或者小于2000的员工
select * from t_employee2 where bonus < 2000 or  bonus IS NULL;

-- 6、找出5年前入职的员工
select * from t_employee2
where floor(months_between(sysdate,hiredate)/12)>=5;

select * from t_employee2 
where to_char(sysdate ,'yyyy') - to_char(hiredate ,'yyyy') > =5;

-- 7、查询所有员工的姓名、入职的年份、月份，月份相同则按月份升序
select ename,to_char(hiredate, 'MM' ) as "月份"，
to_char(hiredate, 'yyyy' ) as "年份"
from t_employee2 order by hiredate;

-- 8、按一个月30天，查询出所有员工的日薪（不计余数）
select eid,ename,
floor（(salary+bonus)/30） as "日薪"
from t_employee2;

-- 9、查询出所有属于3月分入职的员工
select * from t_employee2 
where to_char(hiredate, 'MM' ) = '03';

-- 10、查出员工的工龄（年、月、日），显示字段：姓名、入职时间、年、月、日
select ename,hiredate,
floor(months_between(sysdate,hiredate)/12） as "年",
mod(floor(months_between(sysdate,hiredate)),1) as "月",
floor(to_char(sysdate - hiredate)) as "日"
from t_employee2 order by hiredate;

注：留了个小问题：查询年月日相差，几年几月几天，不知道这个怎么算，年和月都还好说，日的话，直接日期相减，然后负数怎么处理？？？我在想，是不是实际也没这个需求，可能是，我理解题目有偏差