oracle作业二,oracle基础作业2

最疯狂的就是第四题,其他的还比较容易编写的

1、 用户表tb_user(user_id,name,delpt_id,emt),

2、 部门表tb_dept(dept_id,dept_desc),

3、 功夫表tb_gongfu(user_id,gf_id,main_gf_flag)

4、 功夫明细表tb_gf(gf_id,gf_desc)

user_id  name     dept_id  emt

1001   令狐冲1    100

1002   岳不群1    1000

1003   师太2    800

1004   真人4    700

1005   松山道长6    900

1007   太乙3    200

1008   品议5    200

1009   悟空5    2000

dept_id  dept_desc

1     华山派

2     恒山派

3     嵩山派

4     武当派

5     少林派

6     衡山派

User_id   gf_id   main_gf_flag

1001    1        1

1001    2        0

1001    3        0

1002    2        1

1003    3        1

1004    4        1

1005    5        0

1006    5        1

1007     2       0

1008    3        0

1009    5        1

1009    2        0

gf_id   gf_desc

1    华山剑法

2    武当剑法

3    吸星大法

4    辟邪大法

5    太极

资产

5、 用户表tb_user(user_id,name,delpt_id,emt),

6、 部门表tb_dept(dept_id,dept_desc),

7、 功夫表tb_gongfu(user_id,gf_id,main_gf_flag)

8、 功夫明细表tb_gf(gf_id,gf_desc)

(1) 查询会两种功夫或两种功夫以上的人员的名字，部门，以及会的主要功夫名称

select distinct b.name,a.dept_desc,b.gf_desc from

(select  a.NAME as name ,b.dept_desc as dept_desc,d.gf_desc as gf_desc

from tb_user a ,tb_dept b ,tb_gongfu c,tb_gf d

where a.dept_id =b.dept_id and a.user_id =c.user_id and c.gf_id=d.gf_id and main_gf_flag =0

group by   a.NAME ,b.dept_desc,d.gf_desc) a

, (select  a.NAME  as name ,b.dept_desc,d.gf_desc ,main_gf_flag

from tb_user a ,tb_dept b ,tb_gongfu c,tb_gf d

where a.dept_id =b.dept_id and a.user_id =c.user_id and c.gf_id=d.gf_id and main_gf_flag =1

group by   a.NAME ,b.dept_desc,d.gf_desc,main_gf_flag) b

where a.name =b.name

(2)查询部门名称以及各部门的人员数量，按人员数量降序排列

Select dept_desc ,count(user_id)

From tb_user a,tb_dept b

Where a.dept_id =b.dept_id

group by dept_desc

Order by  count(user_id) desc

(3)查询资产排前3的用户的姓名，资产，以及部门名称，按资产升序排列

select a.emt ,a.name,a.dept_desc

from

(Select  a.emt  , a.name  ,b.dept_desc

From tb_user a, tb_dept b

Where  a.dept_id =b.dept_id

Order by a.emt desc ) a

where  Rownum  <=3

(4)查询资产低于平均资产的用户的名字及部门名称、资产、所会的主功夫名称(如果没有主功夫为次功夫最小ID的功夫名称)

select a.name ,c.dept_desc ,a.emt ,g.gf_desc

from tb_user a ,(select avg(emt) as emt from tb_user)b ,tb_dept c  ,

(select    b.gf_id ,b.user_id

from (

select sum (main_gf_flag)  as main_gf_flag ,user_id

from tb_gongfu

group by user_id )a ,tb_gongfu b

where a.main_gf_flag = b.main_gf_flag  and a.user_id =b.user_id) d  ,tb_gf g

where a.emt 

(5)查询每个部门的总资产

select dept_Desc, sum(emt)  from tb_user ,tb_dept

where tb_user.DEPT_ID=tb_dept.DEPT_ID  group by dept_Desc