万事俱备之ORACLE_SQL 练手目录和 part1

重点内容：
1.提供下载ORACLE SQL：经典查询练手系列文章收尾(目录篇)
2.纯净版 ORACLE_SQL练手
更多内容：
ORACLE_SQL 练手 part2
ORACLE_SQL 练手 part3
ORACLE_SQL 练手 part4
ORACLE_SQL 练手 part5
脚本下载：
ORACLE_SQL练手脚本

-----------Some methods for searching table------------
--查找指定用户下的表
SELECT * FROM ALL_TABLES WHERE OWNER='UAT_ADAM_ODS' ;
--查找owner
select owner from all_tables where table_name like '%TMFDBPCALMBR%';
select owner from all_tables where table_name = 'RPAS_TMFDBPCALMBR';
SELECT table_name FROM ALL_TABLES WHERE table_name like '%TMFDBPCALMBR%';
select owner from all_tables where table_name = 'ADAM_MEMBER_INFO_TEMP';

--emp
create table EMP ( empno INTEGER,ename VARCHAR2(50),job VARCHAR2(50),mgr INTEGER,hiredate DATE,sal NUMBER(10,2),comm NUMBER(10,deptno INTEGER );

insert into EMP (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (104,'huyong','PM',1455,to_date('26-05-2011','dd-mm-yyyy'),5500,14,10);
insert into EMP (empno,deptno) values (105,'WANGJING','Developer',deptno) values (102,'EricHu',deptno) values (1455,'Robot','QM',null,to_date('06-04-2011',8500,deptno) values (7369,'SMITH','CLERK',7902,to_date('17-12-1980',800,0,20);
insert into EMP (empno,deptno) values (7499,'ALLEN','SALESMAN',7698,to_date('20-02-1981',1600,300,30);
insert into EMP (empno,deptno) values (7521,'WARD',to_date('22-02-1981',1250,500,deptno) values (7566,'JONES','MANAGER',7839,to_date('02-04-1981',2975,deptno) values (7654,'MARTIN',to_date('28-09-1981',1400,deptno) values (7698,'BLAKE',to_date('01-05-1981',2850,deptno) values (7782,'CLARK',to_date('09-06-1981',2450,deptno) values (7788,'SCOTT','ANALYST',7566,to_date('19-04-1987',3000,deptno) values (7839,'KING','PRESIDENT',to_date('17-11-1981',5000,deptno) values (7844,'TURNER',to_date('08-09-1981',1500,deptno) values (7876,'ADAMS',7788,to_date('23-05-1987',1100,deptno) values (7900,'JAMES',to_date('03-12-1981',950,deptno) values (7902,'FORD',deptno) values (7934,'MILLER',7782,to_date('23-01-1982',1300,10);
commit;

--dept
create table DEPT ( deptno INTEGER,dname VARCHAR2(50),loc VARCHAR2(50) );

insert into DEPT (deptno,dname,loc) values (20,'RESEARCH','DALLAS');
insert into DEPT (deptno,loc) values (30,'SALES','CHICAGO');
insert into DEPT (deptno,loc) values (40,'OPERATIONS','BOSTON');
insert into DEPT (deptno,loc) values (50,'50abc','50def');
insert into DEPT (deptno,loc) values (60,'HaiKou');
insert into DEPT (deptno,loc) values (10,'ACCOUNTING','NEW YORK');
commit;

--练习和答案
INSERT INTO EMP VALUES (102,DATE '2011-5-26',5500.00,14.00,10 );
--count group by
select * from (select empno,count(empno) as num from emp group by empno) t1 where num>1;

delete from emp where empno in (select empno as num from emp group by empno having count(*)>1) and rowid not in (select min(rowid) from emp group by empno having count(*)>1);

select * from emp;     
select * from dept;
--------1．列出至少有一个员工的所有部门。--------- 
select distinct(dname) from emp left join dept on emp.deptno = dept.deptno ;
select dname from dept where deptno in (select deptno from emp where count(deptno)>1);
select dname from dept where deptno in (select deptno from emp group by deptno having count(deptno)>1);

--------2．列出薪金比“SMITH”多的所有员工。---------- 
select ename,sal from emp where sal>(select sal from emp where ename='SMITH');

--------3．列出所有员工的姓名及其直接上级的姓名。---------- 
select a.ename,b.ename as mag_name from emp a,emp b where b.empno=a.mgr;
select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;   

--------4．列出受雇日期早于其直接上级的所有员工。---------- 
select a.ename from emp a,emp b where b.empno=a.mgr and a.hiredate<b.hiredate;
--细
select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);   
--------5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门---------- 
--join on
select a.deptno,a.dname,b.empno,b.ename from dept a left join emp b on a.deptno=b.deptno;
--------6．列出所有“CLERK”（办事员）的姓名及其部门名称。---------- 
select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';  
--------7．列出最低薪金大于1500的各种工作。---------- 
--min(x) > y
select * from (select job,min(sal) ms from emp group by job ) where ms>1500;
select distinct job as HighSalJob，min(sal) from emp group by job having mi+n(sal)>1500;   
--------8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。---------- 
--不知到就不连接，直接筛选出来，做in判断
select ename from emp where deptno=(select deptno from dept where dname='SALES');  
--------10．列出与“SCOTT”从事相同工作的所有员工。-------- 
select empno,ename from emp where job = (select job from emp where ename='SCOTT');
--------11．列出薪部门中多于1人的所有部门员工信息。--------- 
-- where & gb having .count/min/max X to select X / group by X 
select ename,empno,deptno,sal from emp where job in( select job from emp group by job having count(*)>1);
--------12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。--------- 
--not in max/min/count need to be in group by
select ename,sal from emp where sal> (select max(sal) from emp group by deptno having deptno=30);
--------12．列出平均薪水的员工及薪水。--------- 
select deptno,count(*),avg(sal) from emp group by deptno;

select emp.deptno,min(sal) from emp left join dept on emp.deptno=dept.deptno group by emp.deptno,dname;
select job,min(sal) from emp group by job;
--
update emp SET comm=0 where comm is null;
select comm from emp;
---------14．列出所有员工的姓名、部门名称和工资。--------- 
-- nvl()null处理
select ename,(sal+comm)*12 salinyear from emp order by salinyear desc;
select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal desc;  
---------19．用一条sql语句查询出scott.emp表中每个部门工资前三位的数据，显示结果如下：
 select deptno,max(sal)sal1,max (decode(rn,2,sal) )sal2,min(sal)sal3 from ( select sal,row_number() over(partition by deptno order by sal desc) rn from emp ) where rn<4 group by deptno ;
--
select deptno,max(sal) sal1,max(decode(rn,sal)) sal2,min(sal) sal3 from (select sal,rn from(select deptno,row_number() over (partition by deptno order by sal desc) rn from emp) e where e.rn<=3) group by deptno；

万事俱备之ORACLE_SQL 练手目录和 part1

猜你在找的Oracle相关文章