oracle平时的demo

select * from emp;
select A.*,rowid from emp A;
select sysdate from dual;
select to_char(sysdate,'YYYY-MM-DD HH:MI:Ss') 今天的日子是 from dual;
select empid,empname,sex,decode(age, 30,'年轻') from emp;---decode里面不可以再加函数
--------decode主要用于函数判断
select 1*1-3+3 from dual;
select mod(2,3) from dual;

declare
v_var boolean;
begin
v_var:=5>4;
if(v_var) then
dbms_output.put_line('tdd');
end if;
end;

declare
tr varchar2(50):=&hhh;
begin
dbms_output.put_line(tr);
end;
select * from orderinfo where 1>=1 ----可以返回true和false的都可以放到where后面
------------------封装dbms_output_line(tr)------------------


create or replace procedure print(tr varchar2)
as
begin
dbms_output.put_line(tr);
end;

declare
tr varchar2(50):=&hhh;
begin
print(tr);
end;

---下面的这个就是查询empiid最大值为空的时候就用零代替
---对插入数据很有用
select nvl(max(empid),0)+1 from emp;

declare

begin

dbms_output.put_line(to_char(sysdate,'YYYY-MM-DD HH:MI:SS'));

end;
-- for i in 1..10
--loop
-- ………
--- end loop;

declare
begin
for i in 1..10
loop

dbms_output.put_line(to_char(sysdate+i,'YYYY-MM-DD HH:MI:SS'));
end loop;
end;

declare
i number:=1;

begin
while(i<=10)
loop
i:=i+1;
dbms_output.put_line(to_char(sysdate+i,'YYYY-MM-DD HH:MI:SS'));
end loop;
end;

--- while (条件)
--- loop
--……..
--- end loop;

declare
i number:=1;
j number:=1;
begin
while(j<=100)
loop
i:=i+1;
if(mod(i,2)=0) then
j:=j+1;
dbms_output.put_line(i);
end if;
end loop;
end;

declare
begin
for i in 1..10
loop

print(i);
end loop;
end;

declare
begin
for i in
1..10
loop
print(i);
end loop;
end;



create table mydept(
deptid number(11) not null,
deptname varchar2(111) not null,
deptnum number(11),
deptdesc varchar2(111),
constraint dept1_PK primary key(deptid)

)
select * from mydept;
create table myemp(
empid number(11) not null,
empname varchar2(111) not null,
age number(11),
sex char(2) default '男' not null,
birthday date,
phone varchar2(111),
hobby varchar(111),
deptid number(11),
constraint myemp_PK primary key(empid),
constraint myemp_FK foreign key(deptid) references mydept(deptid),
constraint myage_check check(age>21 and age<100),
constraint mybirthday_check check(to_date('2000-12-12','YYYY-MM-DD')<birthday and birthday<to_date('2010-12-12','YYYY-MM-DD')),
constraint myhobby_check check(length(phone)>4 and length(phone)<12)
)

drop table myemp;
select * from emp;


select empid,empname,birthday,decode(age,33,'非男',23,'男') from emp;
alter table emp add moneytype number(11);
insert into emp(empid,empname,sex,age,address,birthday,phone,emphobby,deptid,moneytype)
values(2,'Mary','女',35,'New York',to_date('2008-12-12 12:12','YYYY-MM-DD HH:MI:SS'),134322342,'do everything for',1,1)

create or replace procedure test(v_char varchar2)
as
i number:=1;
v_sql varchar2(1000);
begin i:=i+5;
v_sql:=i||''||v_char;
dbms_output.put_line(v_sql);
end;

declare
begin
test('我爱你');
end;
create or replace procedure test(v_char varchar2)
as
i number:=1;
v_sql varchar2(2222);
begin
i:=i+5;
v_sql:=i||''v_char;
dbms_output.put_line(v_sql);
end;


create view empview as select empid,empname,decode(sex,'男','man','女','woman') state ,age from emp;
select t.*,t.rowid from empview t
select A.* from empview A
create view empview2 as select empid id,empname empmc,decode(sex,'男','Man','女','Woman') state,age from emp;
select t.*,t.rowid from empview2 t
drop view empview2
select t.* from dept t;
create or replace procedure saveDept(did in number,deptmc in varchar2,dnum in number,ddesc in varchar2,hehe in varchar2)
as begin
insert into dept(deptid,deptname,deptnum,deptdesc,hehe) values(did,deptmc,dnum,ddesc,hehe);
end;

select * from dept;
declare
begin
saveDept(3,'销售部',22,'销售相关','221');
end;
-------------创建update更新dept
create or replace procedure updatedept(did in number,deptmc in varchar2,dnum in number,ddesc in varchar2,hehe in varchar2)
as
v_sql varchar(200):='';
begin
v_sql:='update dept set deptname='||deptmc||',deptnum='||dnum||',deptdesc='||ddesc||' where deptid='||did;

execute immediate v_sql;
commit;
end;

select * from dept;

select' Name is' ||A.deptname from dept A;

declare
insert_sql varchar(2345):='';
begin
for i in 1..10
loop
insert_sql:='insert into dept(deptid,deptname,deptnum,deptdesc,hehe) values('||i||,'什么部门',45,'什么部门相关','asdf')||;
print(insert_sql);

end loop;
end;

create table mytable(
myid number(33),
myname varchar2(323),
constraint mytable_PK primary key(myid)
)

----------------第一道题
select * from mytable;
declare
insert_sql varchar(2345):='';
begin
for i in 1..10
loop
insert_sql:='insert into mytable(myid,myname) values('||i||',''aaa'')';
execute immediate insert_sql ;

end loop;
end;
declare

begin
for i in 1..10
loop
insert into mytable(myid,myname) values(i,'bbsa');
end loop;
end;
select * from mytable
truncate table mytable
--------------------------2、编写一个PL/SQL程序块以显示所给出雇员编号的雇员的详细信息。---------------------------
select * from emp;
declare
eid number:=&输入编号;
select_sql varchar2(3232):='';
begin
select_sql:='select A.* from emp A where 1=1 and A.empid='||eid;
execute immediate select_sql;
end;


---------方法
create or replace procedure getemp(empid number,emp out varchar2)
as
select_sql varchar(200):='';
begin
select_sql:='select A.* from emp A where 1=1 and A.empid='||eid;

execute immediate select_sql into emp;
commit;
end;

-------------------test游标--------
----------声明一个包在其中封装一个类型变量---------------
create or replace package testpackage as
type test_cursor is ref cursor;---------前面是声明了一个游标类型
end;

----------建个游标-----------
create or replace procedure test_cur(dp_cursor out testpackage.test_cursor)
as
cursor p_cursor is select * from emp;
begin
dp_cursor:=p_cursor;
for rowData in p_cursor
loop
dbms_output.put_line(rowData.empid);
end loop;
end;

-------------调用cursor----
declare
begin
test_cur();
end;

----------------select count(*) 获得总数量--------下面的num是返回值----------
create or replace procedure getcount(num out number)
as
begin
select count(*) into num from dept;
end;
---------------------下面的是程序块调用getcount()-----------------------------------
declare
num1 number:=1;
begin
getcount(num1);
dbms_output.put_line(num1);
end;
-------------------------------3、编写一PL/SQL以向"emp"表添加10个新雇员编号。--------
----------------------------4、编写一个PL/SQL程序块以计算某个雇员的年度薪水总额。

declare

cursor v_cur is select * from emp;
begin
for i in v_cur
loop
dbms_output.put_line('Name:'||i.ename||' Sal:'||i.sal);
end loop;

end;
-------------------6、用while循环打印不同数字
declare
i number:=0;
begin
while (i<100) loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
----我要创建一个包
create or replace package test
as
type
name like '[a-b]%'
select A.* from emp A where A.empname like 'b%'
insert into emp(empid,empname,sex) values(5,'bbdadd','男');

--_________________________________________创建包和创建游标_____________________________________

create or replace package te
as
procedure p(str varchar2);
end;

create or replace package body te
as
procedure getcountbycursor(str varchar2)-------具体的在包里面实现,向存储,校验
as
begin
print(str);
end;
end;

create or replace package test1
as
type r_cursor is ref cursor;

end;
--返回一个数组
create or replace procedure te.getcountbycursor(num2 out test1.r_cursor,str varchar2)
as
count_sql varchar2(222):='';
begin
count_sql:='select * from dept where 1=1 '||str;

open num2 for count_sql;---打开游标(返回的是结果集的概念)
end;

declare
str varchar2(222):='and deptid>1';
num2 number:=0;
begin
getcount1(num2,str);
dbms_output.put_line(num2);
end;
-----------------------------下面是查询有条件的总数量------------------------
create or replace procedure getcount1(num2 out number,str varchar2)
as
count_sql varchar2(222):='';
begin
count_sql:='select count(*) from dept where 1=1 '||str;

execute immediate count_sql into num2;
end;
declare
str varchar2(222):='and deptid>1';
num2 number:=0;
begin
getcount1(num2,str);
dbms_output.put_line(num2);
end;
---------------创建序列---------------------------
create table person(personid number,personname varchar2(121));
--关于序列的使用 1:创建序列 ----nocycle的意思是不循环cache20
create sequence pid_seq start with 1 increment by 1 maxvalue 30 minvalue nocycle cache 20
--使用序列实现自增长字段:sequenceName.nextval,
--每使用一次sequence.nextval则nextval自动增长一个步长
insert into person values(pid_seq.nextval,'accp');
insert into person values(pid_seq.nextval,'bbbb');

select * from person;
---查看序列当前值:使用sequenceName.currval
select pid_seq.currval from dual;
--问题是序列的currval和nextval的不同,如果我们查看nextval则nextval会自增
select pid_seq.nextval from dual;---此时的nextval已经自增了,那么要向表中插入nextval是多少?
select pid_seq.currval from dual;
insert into person values(pid_seq.nextval,'cccc');

---视图
create or replace view v_sal as
select ename as 姓名,sal as 薪水
from emp order by sal
select * from v_sal where rownum<=10


create or replace view v_dept as
select deptid as 部门编号,deptname as 部门名称,deptnum as 部门人数,deptdesc as 部门描述
from dept order by deptid
select * from v_dept where rownum<=10
下面的是程序块2调用getcount()
-------------------- 测试---------------------------
create or replace package my_pac as
type my_cursor is ref cursor;
end;
create or replace procedure my_pac.hehe
as
sel_sql varchar2(232):='';
begin
sel_sql:=select * from dept;
execute immediate sel_sql;
end;


-------------------下面是建了一个包和包体
create or replace package te
as
procedure p(str varchar2);
end;

create or replace package body te
as
procedure p(str varchar2)
as
begin
print(str);
end;
end;



create or procedue getpagedata(cond varchar2,unit number,curPage number,mycursor out my_pac.my_cursor,allrecord out number(11))
as
num number(11):=0;
get_sql varchar(222):='';
begin
get_sql:='select A.* from dept A where 1=1 '||cond||'';
end;
select A.* from dept A between 1,and 2;




----------------SQL分页-------
SELECT DECODE(mod(COUNT(*),2),0,COUNT(*)/2,trunc(COUNT(*)/2,0)+1)
AS pages FROM dept

SELECT x.* from (SELECT z.*,rownum numbers from XZQH z where rownum<101) x where x.numbers>90

select nvl(max(deptid),0)+1 into
select nvl(max(deptno),0)+1 from emp;
-----------------触发器十月15日---------------
create table a_user(
userid number(11),
username varchar2(112),
age number(11),
constraint user_pk primary key(userid)

)

create table a_role(
roleid number(11),
rolename varchar(111),
constraint role_pk primary key(roleid)


)
create table role_user(
role_userid number(11),
userid number(11),
roleid number(11),
constraint role_user_pk primary key(role_userid),
constraint role_user_fk1 foreign key (userid) references a_user(userid),
constraint role_user_fk2 foreign key (roleid) references a_role(roleid)
)


create or replace trigger delteall
before delete on a_role
for each row
begin
if(deleting) then



role_user set roleid=null where roleid=:old.roleid;
end if;
end;
select * from a_role;
select * from role_user;
delete from a_role A where A.roleid=1;
select * from emp;
-------------------十月15日-触发器的学习--------------
库存表有商品编号,商品名称
商品表,库存是多,商品,一个商品可以放到多个创库里,
入口单表,
一个创库
select A.* from emp A;
select B.*,rownum numbers1 from(select A.*,rownum numbers from (select * from emp order by empno desc ) A where rownum<11) B where rownum<2;
select A.* from emp A where A.empno<3
select A.* from emp A where A.empno<2


SELECT X.*
FROM (
select
rownum() over(order by empno) as numbers
,emp.*
FROM emp
WHERE empno=1
) X
WHERE X.numbers < 101
AND X.numbers > 90


select ROW_NUMBER() OVER(order by empno) as numbers,emp.* from emp where empno>1

select * from emp where 1=1 and empno>1 order by empno;
select * from emp where rownum<=2 order by sal;
select B.* from (select A.*,rownum rid from emp A order by sal) B where rownum <3
----传个分页单位,查出有多少页--
select decode(mod(count(*),2),0,count(*)/2,trunc(count(*)/2,0)+1) from emp;


declare
page number(10,0);
begin
select trunc((count(*)+5-1)/5) from emp;
dbms_output.put_line(page);
end;

select * from emp;


----------、1接受两个数相除并且显示结果。如果第二个数为0，则显示消息"DIVIDE BY ZERO"。


create or replace procedure twonumber(num1 number,num2 number,num3 out number)
as

v_sql varchar2(222):='';
begin
if(num2=0) then
dbms_output.put_line('divide by zero');
else
v_sql:='select '||num1||'/'||num2||' from dual';
execute immediate v_sql into num3;
end if;
end;

----2 编写一个PL/SQL程序块，对名字以"A"或"S"开始的所有雇员按他们的基本薪水的10%加薪。
declare

v_sql varchar2(222):='';
begin
select A.ename,A.sal*1.1 from emp A where 1=1 and A.ename like 'A%' or A.ename like 'S%';

end;
select A.ename,A.sal*1.1 from emp A where 1=1 and A.ename like 'A%' or A.ename like 'S%';
select * from emp;
---- 03、编写一PL/SQL，对所有的"销售员"(SALESMAN)增加佣金500.
select A.ename,A.sal*1.5,A.job from emp A where 1=1 and A.job='SALESMAN'
----04、编写一PL/SQL，以提升两个资格最老的"职员"为"高级职员"。（工作时间越长，优先级越高）
select C.ename,C.job,C.sal,C.hiredate,C.rr from ( select B.ename,B.job,B.sal,B.hiredate,rownum rr from(select A.ename,A.job,A.sal,A.hiredate,rownum numbers from emp A where 1=1 and A.job='CLERK' order by hiredate asc) B where rownum<10 ) C where rr>1 and rr<4
------ 05、编写一PL/SQL，对所有雇员按他们基本薪水的10%加薪，如果所增加的薪水大于5000，则取消加薪。
declare
cursor v_cur is select * from emp for update;
begin
for i in v_cur
loop
if((i.sal)*1.1>5000) then
dbms_output.put_line(i.sal);
else
update emp set sal=sal*1.1 where empno=i.empno;
end if;
end loop;
end;

--------对查得的第三条记录加薪
declare
cursor v_cur is select * from emp for update;
m number(11):=0;
begin
for i in v_cur
loop
m:=m+1;
if(m=3)then
update emp set sal=sal*1.1 where empno=i.empno;
commit;
exit;
end if;
end loop;
end;
select * from emp;
----------
declare
cursor v_cur is select * from emp for update;
m number(11):=0;
begin
for i in v_cur
loop
if(v_cur%rowcount=3)then
update emp set sal=sal*1.1 where empno=i.empno;
commit;
exit;
end if;
end loop;
end;
----------------06、显示EMP中的第四条记录。
select B.empno,B.ename,B.sal,B.numbers from (select A.ename,A.empno,A.sal ,rownum numbers from emp A) B where B.numbers<5 and B.numbers>3
---------07.根据部门编号，按下列加薪比执行：Deptno 递升
-- 10 5%
-- 20 10%
-- 30 15%
--- 40 20%

declare
cursor v_cur is select * from emp;
m number(11):=0;
begin
for i in v_cur
loop
if(i.deptno=10)then
update emp set sal=sal*1.05 where empno=i.empno;
elsif(i.deptno=20) then
update emp set sal=sal*1.1 where empno=i.empno;
elsif(i.deptno=30) then
update emp set sal=sal*1.15 where empno=i.empno;
else
update emp set sal=sal where empno=i.empno;
end if;
end loop;
end;

select * from emp B
where 1>2 ----条件
group by deptno ---分组
having dad ---------对分组进行限制
order by
----------查出每个部门平均工资
------每一个部门里头挣钱最多的那个人的名字
-----查出每个部门工资最多和最少的
----------查出每个部门平均工资
select * from dept;
select * from emp;
select avg(sal) from emp;----所以员工的平均工资
select avg(A.sal),B.dname from emp A,dept B where 1=1 and A.Deptno=B.Deptno
select avg(A.sal),(select B.dname from dept B) from emp A group by A.deptno
---------------每一个部门里头挣钱最多的那个人的名字
select * from emp;
select * from dept;
select A.* from emp A,( select max(sal) mSal,deptno from emp group by deptno) B where A.sal=B.mSal and A.deptno=B.deptno

select A.* from emp A,(select max(sal) c ,deptno from emp group by deptno) B where A.sal=B.c and A.deptno=B.deptno
select A.ename,B.dname,B.deptno from emp A,dept B where A.deptno=B.deptno;---查员工表及员工所对应的部门
select avg(sal),deptno from emp group by deptno;
select avg(sal),deptno from emp group by deptno having avg(sal)>2000---平均薪水大于2000值取出来
-------查出每个部门工资最多和最少的
---方法一
select E.*,F.* from (select A.* from emp A,(select max(sal) c,deptno from emp group by deptno) B where A.sal=B.c and A.deptno=B.deptno) E,
(select A.* from emp A,(select min(sal) d,deptno from emp group by deptno ) C where A.sal=C.d and A.deptno=C.deptno) F
where E.deptno=F.deptno
----方法二
select A.* ,B.* from emp A,emp B,(select max(sal) mSal,min(sal) lSal,deptno from emp group by deptno) C
where A.deptno = C.deptno and B.deptno=C.deptno and A.deptno=B.deptno and A.sal=C.mSal and B.sal=C.lSal
select max(sal),min(sal),deptno from emp group by deptno
select emp.*,dept.* from emp,dept;

--------------十月17日

select A.r_constraint_name from user_constraints A where A.Constraint_Name='PK_EMP'
-----------有外键约束查得对应主键表的表明.主键约束.主键字段

-------不用max查出工资最高的那个人
---分页查最大值
select C.*,C.numbers from (select B.*,rownum numbers from (select A.* from emp A order by sal desc) B) C where C.numbers<2

---------通用的写法查询
---------两个表相比较
-----A 100 200 300 B 100 200 300
--看A B这两张表,100(A表)看B表中的字段是否有比100小的,有那出来,没有就不拿
---200(A表)有比他小的是100,300(A表)有比他小的是200,最总你就可以得到最大的
select * from emp where empno not in (select distinct A.sal,B.sal from emp A,emp B where A.sal>B.sal order by A.sal)
select sal from emp order by sal;
-------方法一---查平均工资排名2-4的部门名
select D.deptno,D.Bsal,D.Bnumbers from (select C.deptno,C.Bsal, rownum Bnumbers from (select B.deptno,B.avgsal Bsal from (select A.deptno,avg(sal) avgsal from emp A group by A.deptno) B where 1=1 order by B.avgsal) C ) D where D.Bnumbers>4 and D.Bnumbers<6;
--------方法二---
select deptno,count(deptno) from emp group by deptno;
select count(*) from emp;


select C.*,D.* from (select B.ename,B.sal from (select A.ename,A.sal from emp A order by A.sal desc) B) C,
(select B.ename,B.sal from (select A.ename,A.sal from emp A order by A.sal desc) B) D
-----------查看

---查看不用rownum为表,给每条记录一个id(不通过row把部门的第二到第四那出来)
select C.* from (select A.* ,(select count(*)+1 from emp where 1=1 and empno<A.empno) bianhao from emp A) C where C.bianhao<5 and C.bianhao>1
select * from emp;
--------------------分页1020号
create or replace procedure getcount(cond varchar,num out number)
as
v_sql varchar(222):='';
begin
select count(*) into num from emp;


-----------------------十月20日学习游标PL_SQL语言初级教程------
select distinct age,empname from emp;
----%TYPE
declare
v_a number(8):=10;
v_b v_a%TYPE:=15;
v_c v_a%TYPE;
begin
dbms_output.put_line('v_a'||v_a||' v_b'||v_b||'v_c'||v_c);
end;
--在PLSQL中可以将常量和变量声明为内建或用户定义的数据类型,以应用一个列名,同时继承他的数据类型和大小,这
---种动态赋值的方法是非常有用的,
---第一步
select * from user_all_tables;
---第二步
select distinct A.table_name,B.Column_Name,A.Constraint_Name,decode(A.Constraint_Type,'R','外键') from user_constraints A,user_cons_columns B

------------------下列数据字典视图提供表和表的列的信息:------------------------
select * from Dba_Tables;
DBA_ALL_TABLES;
select * from User_Tables;
USER_ALL_TABLES;
All_Tables;
ALL_ALL_TABLES;
Dba_Tab_Columns;
select * from User_Tab_Columns;
ALL_TAB_COLUMNS
select distinct A.table_name,B.column_name,A.constraint_name,decode(A.constraint_type,'R','外键')
from user_constraints A,user_cons_columns B
where A.table_name=B.table_name and A.constraint_name=B.constraint_name and A.table_name='EMP' and A.constraint_type in('R')
select u.table_name,u.column_name,u.data_type from user_tab_cols u where u.table_name='EMP';

select distinct A.table_name,B.column_name,A.constraint_name,decode(A.constraint_type ,'P','主键','R','外键')
from user_constraints A,user_cons_columns B
where A.table_name=B.table_name and A.constraint_name=B.constraint_name and A.table_name='EMP' and A.constraint_type in('P','R')


-----------1类名(表名),2字段名,3字段类型,4主键,5外键

select distinct A.table_name,A.constraint_name from user_constraints A,user_cons_columns B where A.table_name='EMP'

select * from user_tab_columns;
select * from user_tab_cols;
------1类名(表名),2字段名,3字段类型
select A.column_name,A.data_type,A.DATA_SCALE from user_tab_cols A where A.table_name='EMP'
select distinct A.*,B.data_scale from emp A,user_tab_cols B where B.table_name='EMP'
---------查出主键和外键
select distinct A.table_name,B.column_name,A.constraint_name,A.constraint_type from user_constraints A,user_cons_columns B where A.table_name=B.table_name and A.table_name='EMP' and A.constraint_name=B.constraint_name
and A.constraint_type in('R','P')

select distinct A.table_name,B.column_name,A.constraint_name,A.constraint_type from user_constraints A,user_cons_columns B where A.table_name=B.table_name and A.table_name='DEPT' and A.constraint_name=B.constraint_name
and A.constraint_type in('R','P')


select * from user_constraints
select * from user_tab_columns
select * from user_cons_columns where table_name='emp'
select * from user_tab_columns


select A.table_name, from user_constraints A,user_cons_columns
--- 查看约束：
select * from user_constraints;
select * from mytable;
alter table mytable add num number(7,2);
select * from user_tab_columns A where A.TABLE_NAME='MYTABLE'

---企业制度
create table qyzd(
zdid number(8),
zdmc varchar2(28),
zdms varchar2(118),---制度描述
zdwj blob,--字节流---制度文件
zdwjdx number(8),--制度文件大小
zdwjmc varchar(123),---制度文件名称
constraint qyzd_pk primary key(zdid)

)
select * from qyzd;

---北京上学堂

select * from emp
select avg(sal) from emp
select empno,avg(sal) from emp group by empno
select empno,to_char(avg(sal),9999.99) from emp group by empno
---精确到小数点两位
select empno,round(avg(sal),0) from emp group by empno

select sum(sal) from emp
select count(*) from emp---一张表中有多少条记录
select count(*) from emp where 1=1 and deptno=1
---共有多少个名字
select count(ename) from emp--- count某一个字段,这个字段不是空值,那么他就算一个
select count(distinct deptno) from emp; ---有多少个部门唯一的部门
----那个部门的平均薪水高
select deptno,avg(sal) from emp group by deptno
select deptno,max(sal) from emp group by deptno having max(sal)>5000

select deptno,avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500
order by deptno desc
select * from salgrade
select ename,dname,grade from emp e join dept d on e.deptno=d.deptno
join salgrade s on e.sal between s.losal and s.hisal
where ename not like '%A'
select e1.ename,e2.deptno from emp e1 full join emp e2 on e1.mgr=e2.empno
--------部门平均薪水的等级
select * from salgrade
select e1.deptno,e1.avgsal,e3.grade from (select deptno,avg(sal) avgsal from emp group by deptno) e1
join salgrade e3 on (e1.avgsal between e3.losal and hisal)
----------每个人的薪水等级
select e1.deptno,e1.ename,e2.grade from emp e1 join salgrade e2 on (e1.sal between e2.losal and e2.hisal)

1.求部门平均的薪水等级
2.雇员中有那些人是经理人
3.不准用组函数,求薪水的最高值()
3.求平均薪水最高的部门的部门编号
4.求平均薪水最高的部门的部门名称
5.求平均薪水的等级最低的部门的部门名称
6.求部门经理人中平均薪水最低的部门名称
7.求比普通员工的最高薪水还要高的经理人名称
8.求薪水最高的前5名雇员
9,求薪水最高的第6到第10名雇员

------3.不准用组函数,求薪水的最高值()
方法一:
select e2.empno,e2.sal,e2.num from (select empno,sal,rownum num from (select empno,sal,rownum from emp order by sal desc) e1) e2 where e2.num<2 ;
方法二:
select * from emp;
select e3.* from emp e3 where e3.sal not in (select distinct e1.sal from emp e1, emp e2 where e1.sal<e2.sal);
-------2.雇员中有那些人是经理人（是他的雇员编号出现在Mgr里面,就是我们的经理人）
select * from emp where emp.empno in(select distinct mgr from emp);
-------3.求平均薪水最高的部门的部门编号
select deptno,avg(sal) from emp group by deptno
select deptno from (select avg(sal) asal,deptno from emp group by deptno) where asal=(select max(avgsal) from (select avg(sal) avgsal,deptno from emp group by deptno))
-----------5.求平均薪水的等级最低的部门的部门名称
select min(avg(sal)) from emp group by deptno;
select deptno,avg(sal) from emp group by deptno
select * from salgrade
select dname,deptno from dept where deptno =
(
select deptno from (
select e2.grade,deptno,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) e1
join salgrade e2 on e1.avg_sal
between losal and hisal

) where
grade=
(
select min(e2.grade) from
(select deptno,avg(sal) avg_sal from emp group by deptno) e1
join salgrade e2 on e1.avg_sal
between losal and hisal)
)
-------方法二
select d.dname,t1.deptno,grade,avg_sal from
(
select e.deptno,t.grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) e
join salgrade t on e.avg_sal between t.losal and t.hisal
) t1
join dept d on t1.deptno=d.deptno where t1.grade=(
select min(grade) from
(
select e.deptno,t.grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) e
join salgrade t on e.avg_sal between t.losal and t.hisal
)
)
------创建试图
create view $zhangjin as
(
select e.deptno,t.grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) e
join salgrade t on e.avg_sal between t.losal and t.hisal
)
-------------7.求比普通员工的最高薪水还要高的经理人名称
select * from (select * from emp where empno in(select distinct mgr from emp)) e
where
e.sal >(
select max(sal) from emp
where empno not in(
select empno from emp where empno in(
select distinct mgr from emp)))
select * from emp;
-----下面是复制表(包含内容)
create table aa as (select * from dept)
select * from aa
------下面是复制表(不包含内容)
create table bb as(select * from dept where 1>1)
select * from bb