Oracle平时作业

2–用户管理作业

–1、创建一个用户，用户名为自己的名字首字母为jm；

create user jm identified by 123456;

–2、给该用户授予连接数据库的权限；

grant create session to jm;

–3、给该用户授予连接和访问数据库的角色；

grant connect, resource to jm;

–4、创建一个角色student;

create role student;

–5、给角色student赋予create session的权限；

grant create session to student;

–6、删除刚刚创建的用户。(因为在用户下面没有建表等数据库对象，所以不用加cascade)

drop user jm;

3–创建表，插入数据

–1、创建好学生表t_student 并插入数据

create table t_student
(
     SID char(6) primary key,
     SNAME varchar2(10),
     SSEX char(2),
     SBIRTHDAY  date,
     STEL varchar2(13),
     SCLASS number(2)     
);
insert into t_student values('10001','谢娜','f',to_date('1986-4-1','yyyy/mm/dd'),'13697855678','1');
insert into t_student values  ('10002','姚明','m',to_date('1986-6-6','yyyy/mm/dd'),'13680888086','2');
insert into t_student values ('10003','马云','m',to_date('1986-5-23','yyyy/mm/dd'),'13520035611','1');
insert into t_student values  ('10004','张朝阳','m',to_date('1986-9-8','yyyy/mm/dd'),'15597844378','3');
insert into t_student values ('10005','杨澜','f',to_date('1986-7-3','yyyy/mm/dd'),'13897856666','2');
insert into t_student values ('10006','白岩松','m',to_date('1986-11-11','yyyy/mm/dd'),'13266668888','3');
select * from t_student;

–2、创建好课程表t_course并插入数据

create table t_course
(
     CID char(2) primary key,
     CNAME varchar2(18),
     CTYPE char(5),
     CHOURS number(3) 
);
insert into t_course values('1','操作系统','选修',32);
insert into t_course values('2','数据结构','选修',48);
insert into t_course values('3','教育心理学','选修',24);
insert into t_course values('4','java核心技术','必修',64);
insert into t_course values('5','java web开发技术','必修',48);
insert into t_course values('6','java框架技术','必修',64);
select * from t_course;

–3、创建好成绩表t_score 并插入数据

create table t_score(
     SID char(6) ,
     CID number(2) ,
     SCORE number(3)
 );
insert into t_score values('10001',1,56);
insert into t_score values('10001',2,46);
insert into t_score values('10001',3,37);
insert into t_score values('10001',4,59);
insert into t_score values('10001',5,55);
insert into t_score values('10001',6,49);
insert into t_score values('10002',1,76);
insert into t_score values('10002',2,66);
insert into t_score values('10002',3,57);
insert into t_score values('10002',4,79);
insert into t_score values('10002',5,75);
insert into t_score values('10002',6,69); 
insert into t_score values('10003',1,86);
insert into t_score values('10003',2,68);
insert into t_score values('10003',3,58);
insert into t_score values('10003',4,77);
insert into t_score values('10003',5,92);
insert into t_score values('10003',6,89);
insert into t_score values('10004',1,75);
insert into t_score values('10004',2,63);
insert into t_score values('10004',3,87);
insert into t_score values('10004',4,67);
insert into t_score values('10004',5,82);
insert into t_score values('10004',6,96);
insert into t_score values('10001',1,66);
insert into t_score values('10001',2,61);
insert into t_score values('10001',3,54);
insert into t_score values('10001',3,60);
select * from t_score;

–4、创建好教师表t_teacher并插入数据

create table t_teacher(
     TID number(3) primary key,
     TNAME char(8),
     TACE number(5)
);
insert into t_teacher values(1,'赵老师',20);
insert into t_teacher values(2,'王老师',43);
insert into t_teacher values(3,'张老师',33);
insert into t_teacher values(4,'孙老师',25);
insert into t_teacher values(5,'卢老师',50);
insert into t_teacher values(6,'徐老师',35);
select * from t_teacher;

–5、创建好教师课程表t_teachercourse 并插入数据

create table t_teachercourse(
     TID number(2)，
     CID number（2）
);
insert into t_teachercourse values(1,1);
insert into t_teachercourse values(2,2);
insert into t_teachercourse values(3,3);
insert into t_teachercourse values(4,4);
insert into t_teachercourse values(5,5);
insert into t_teachercourse values(7,5);                                                          
 select * from  t_teachercourse;                                   

4-约束

create table t_student
(
     SID number(6) primary key,
     SNAME varchar2(10) unique,
     SSEX char(2) check(ssex='f' or ssex='m'),
     SBIRTHDAY  date,
     STEL varchar2(13),
     SCLASS number(2)     
);
insert into t_student values('10001','谢娜','f','19-7月-2000','13697855678','1');
insert into t_student values('10002','姚明','m','6-6月-1986','13680888086','2');
insert into t_student values ('10003','马云','m','23-5月-1986','13520035611','1');
insert into t_student values  ('10004','张朝阳','m','8-9月-1986','15597844378','3');
insert into t_student values ('10005','杨澜','f','3-7月-1986','13897856666','2');
insert into t_student values ('10006','白岩松','m','11-11月-1986','13266668888','3');
select * from t_student;

create table t_score
(
sid number(5) primary key,
cid char(2),
score number(3)
);
alter table t_score add constraint FK_t_score_sid foreign key(sid) references t_student(SID); 
select * from t_score;

5-多表查询

1.显示所有员工的姓名ename，部门号deptno和部门名称dname。

select ename,d.deptno,dname from emp e join dept d on e.deptno=d.deptno;

–2.查询20号部门员工的job和20号部门的loc

select ename,job,loc from emp a,dept b where a.deptno=b.deptno and a.deptno=20;

–3.选择所有有奖金comm的员工的ename , dname , loc

select ename,dname,loc from emp a,dept b where a.deptno=b.deptno and comm is not null;

–4.选择在DALLAS工作的员工的ename , job , deptno, dname

select ename,job,a.deptno,dname from emp a,dept b where a.deptno=b.deptno and loc='DALLAS';

–5.选择所有员工的姓名ename，员工号empno，以及他的管理者mgr的姓名ename和员工号empno

select a.ename "employees",a.empno "Emp#",b.ename "manager",b.empno "Mgr#"
from emp a,emp b where a.mgr=b.empno;

–6.查询各部门员工姓名和他们所在位置

select a.deptno "Deptno",ename "Ename",loc "Loc" from emp a,dept b where a.deptno=b.deptno;

6-分组查询及子查询

–基于scott.emp表进行以下查询：
select * from user_tables;
select * from emp;
select * from dept;
–1. 查询公司员工工资的最大值，最小值，平均值，总和

select max(sal) ,min(sal) ,avg(sal) , sum(sal) from emp;

–2. 查询各种job的员工工资的最大值，最小值，平均值，总和

select job,max(sal) ,min(sal) ,avg(sal) , sum(sal) from emp group by job;

–3. 查询各种job的员工人数(提示:对job进行分组)

select job,count(empno) from emp group by job;

–4. 查询员工最高工资和最低工资的差距（DIFFERENCE）

select max(sal)-min(sal) difference from emp;

–5. 查询各个管理者手下员工的最低工资，其中最低工资不能低于800，没有管理者的员工不计算在内

select mgr ,min(sal) from emp  where mgr is not null group by mgr having  min(sal)>=800;

–6. 查询所有部门的名字dname，所在位置loc，员工数量和工资平均值

select dname,loc,count(empno),avg(sal) from emp  join dept on 
emp.deptno=dept.deptno group by dname,loc;

–7. 查询公司的人数，以及在1980-1987年之间，每年雇用的人数，结果类似下面的格式
total 1980 1981 1982 1987
30 3 4 6 7
–DECODE函数，意思是，YEAR字段如果是1987，那么返回1，否则返回0，然后用sum函数进行统计，
–这样就能统计每一年有多少人。case when也可以。

select count(*) total,
sum(decode((to_char(hiredate, 'YYYY')), '1980', 1, 0)) year1980,
sum(decode((to_char(hiredate, 'YYYY')), '1981', 1, 0)) year1981,
sum(decode((to_char(hiredate, 'YYYY')), '1982', 1, 0)) year1982,
sum(decode((to_char(hiredate, 'YYYY')), '1983', 1, 0)) year1983,
sum(decode((to_char(hiredate, 'YYYY')), '1984', 1, 0)) year1984,
sum(decode((to_char(hiredate, 'YYYY')), '1985', 1, 0)) year1985,
sum(decode((to_char(hiredate, 'YYYY')), '1986', 1, 0)) year1986,
sum(decode((to_char(hiredate, 'YYYY')), '1987', 1, 0)) year1987
from emp;

–8. 查询和scott相同部门的员工姓名ename和雇用日期hiredate

select ename,hiredate from emp where deptno in(select deptno from emp where ename='SCOTT');

–9. 查询工资比公司平均工资高的所有员工的员工号empno，姓名ename和工资sal。

select empno,ename,sal from emp,(select deptno,avg(sal) as avgSal from emp group by deptno) 
dept where emp.deptno=dept.deptno and emp.sal>dept.avgSal;    

–10. 查询和姓名中包含字母u的员工在相同部门的员工的员工号empno和姓名ename

select empno,ename from emp where deptno in
(select deptno from emp where ename like '%U%');

–11. 查询在部门的loc为newYork的部门工作的员工的员工姓名ename，部门名称dname和岗位名称job

select emp.ename,dept.dname,emp.job from emp join dept using(deptno) 
where deptno=(select deptno from dept where loc='NEW YORK');

–12. 查询管理者是king的员工姓名ename和工资sal

select ename,sal  from emp where mgr=(select empno from emp where ename='KING');