oracle 练习题

sqlplus sys/111111 as sysdba
show parameter name;
--建立test表空间
create tablespace test datafile 'D:\app/test.dbf' size 50m maxsize 1g autoextend on;




show parameter name;  --显示数据库名，实例名等
show parameter db_name; --显示数据库名
show parameter instance;
select *from v$database;
--查看实例信息
select *from v$instance;


--查看实例名
select name  from v$database;
select  instance_name from v$instance;


show  parameter instance


--查看用户
select *from dba_users;


--建用户授权
create user test identified by 111111;
grant connect,resource to test;
conn test/111111;
drop user test cascade;
conn /as sysdba;
drop user test cascade;


--test有connect的管理权限，可将connect 权限赋给另一个人
create user test identified by 111111;
grant connect,resource to test with admin option;


--查询系统权限
select *from sys.dba_sys_privs; //查询所有用户权限
select *from user_sys_privs;//查询sys用户权限


--回收权限
show user;
conn /as sysdba;
revoke connect from test;


--解锁用户
sqlplus  sys/111111 as sysdba
show user;
conn scott/tiger;
show user; //显示没有用户
alter user scott account unlock; //退出重新登录,在有用户的时候登
conn scott/tiger; //连接之后输入新密码


--解锁后用plsql登录 scott/tiger 用户
select *from user_tables; //查询表名


select * from dept;     //dept部门表
select emp.* from emp;  //emp员工表
select *from emp;
select *from bonus;     //bonus奖金表
select *from salgrade;  //收入等级表


select * from dept;
select distinct dname from dept;
select  dname from dept;


select empno,ename from emp ;
select empno,ename from emp where ename  = 'SMITH';
select ename from emp where ename  = 'SMITH';
select *from emp where empno between 7369 and 7830;
select *from emp where empno like '%99';
select empno,ename from emp where ename  = 'SMITH';
select *from emp where empno like '%56%';
select *from emp where empno in(7499,8000);
select *from emp where empno in(7499,7839);
select *from emp where empno is null;
select *from emp where empno is not null;
select *from emp where empno >= '7521';
select *from emp where rownum <= 5;
select empno,ename,sal+300 from emp where rownum <= 10;
select empno as0000 from emp;
select empno www from emp;
select empno as wwww from emp where rownum <= 10;
select *from emp;
select empno ,ename,job,mgr aaaa from emp order by empno desc;
select deptno from emp group by deptno;
select empno,ename||'m' from emp where rownum <= 5;
select empno,ename||'+'||sal from emp;
select deptno,max(sal) from emp group by deptno;
select deptno,substr(ename,5) from emp where rownum <= 5;


select *from emp;
insert into emp(empno,deptno) values(7368,10);
insert into emp(empno,deptno) values(7368,50); //部门表的部门编号只有10,20,30，报错
delete from dept where deptno = 10; //删除父表中的记录，子表中有记录报错






1.查询姓名首字母为A或第二个字符为A的所有员工信息
//错误
select *from emp;
select *from emp where substr(ename,1,1) = 'a' or substr(ename,2,1)='a';
select *from emp where ename like 'A%' or like '_A%';
//修正
select *from emp;
select *from emp where substr(ename,1,1) = 'A' or substr(ename,2,1)='A';
select *from emp where ename like 'A%' or ename like '_A%';


2.查询部门20,30 中的岗位不是"CLERK"或"SALESMAN"的所有员工信息
select *from emp where job not in('CLERK');
select *from emp where job not in('SALESMAN');
select *from emp;


select *
from emp
where job<>'CLERK'
 and job<>'SALESMAN'
 and deptno in (20, 30);


 select *
from emp
where job not in ('CLERK','SALESMAN')
 and deptno in (20, 30);




3.查询工资在2500-3500之间,1981年入职,没有奖金的所有员工信息
select *from emp where sal between 2500 and 3500 and to_char(hiredate,'YYYY-MM-DD') like '1981%' and comm is null;
--用了子查询还有extract函数
select *from (select *from emp where sal between 2500 and 3500 and comm is null)where extract(year from hiredate) = '1981';
select *from emp where extract (year from hiredate) = '1981';


4.查询比平均员工工资高的员工信息
//错误
select *from emp where sal > avg(sal);
//正确
select *from emp where sal >  (select avg(sal) from emp);

select avg(sal) from emp; //是子查询

5.查询平均工资高于2000的部门信息 //select avg(sal) from emp 是 所有人平均工资
--1.先找到平均工资大于两千的部门编号，即每个部门的平均工资大于两千的先找出来
select deptno,avg(sal) from emp  by deptno having avg(sal) >2000
--2.根据部门编号查找部门信息
select deptno,dname,loc from dept where deptno in(select deptno from emp group by deptno having avg(sal) >2000 );


6.查询出WARD的工作所在地
//这边用in不太好,in是个范围,已经确定了姓名，用等号也不太好
select loc from dept where deptno in (select deptno from emp where ename = 'WARD')；
--用两个表的自然链接
select ename,dept.loc from dept,emp where emp.deptno = dept.deptno and ename = 'WARD';


7.查询出工资比ADAMS高的所有人的姓名、部门、所在地
select ename,dname,loc from dept,emp where emp.deptno = dept.deptno and emp.sal > (select sal from emp where ename = 'ADAMS' );


8.--查询工资排名第7的员工信息
//将员工工资先降序排列，查出前7条数据，取前7条数据中的最小工资
select sal from emp order by sal desc;
select *from emp where sal = (select min(sal) from (select *from emp order by sal desc)where rownum <= 7 )


//利用minus将两个降序结果集相减,union则是将两个结果集相并
select *from emp order by sal desc;
select sal from (select *from emp order  by sal desc )where rownum <= 7;
select sal from (select *from emp order  by sal desc )where rownum <= 6;


select *
  from emp
 where sal = (select sal
                from (select * from emp order by sal desc)
               where rownum <= 7  minus
select sal
         from (select * from emp order by sal desc)
        where rownum <= 6);


//oracle函数
select ename,to_char(hiredate,'YYYY_MM_DD HH:MM:SS') from emp;
select ename,hiredate from emp; //显示日历格式
select to_date('2016-11-23','YYYY-MM-DD') from dual;


select sysdate,add_months(sysdate,3),add_months(sysdate,2)from dual;
select sysdate,last_day(sysdate),last_day(sysdate)+1 from dual;


select
months_between('17-9月-2013','17-9月-2013')as 第一个结果是,
months_bet