oralce练习题

（CORE-E-001）综合练习
定义
CORE-F-001­——CORE-F-005的综合练习：
1.用sqlplus连接数据库时，为什么会出Oracle not available错误？
  环境变量出错
2.找出员工的姓中（last_name）第三个字母是a的员工名字
 select last_name
 from s_emp
 where last_name like'__a%';


3.找出员工名字中含有a和e的
 select first_name
 from s_emp
 where first_name like'%a%e%';


4.找出所有有提成的员工，列出名字、工资、提出，显示结果按工资从小到大，提成从小到大
  select first_name,salary,nvl(commission_pct,0)
  from s_emp
  where commission_pct is not null 
  order by salary,commission_pct;


5.42部门有哪些职位
select title from s_emp where dept_id =42;


6.哪些部门不是Sales部
select name from s_dept where name<> 'Sales';


7.显示工资不在1000到1550之间的员工信息：名字、工资，按工资从大到小排序。
select first_name,salary from s_emp where salary between 1000 and 1550 order by salary;


8.显示职位为Stock Clerk和Sales Representative，年薪在14400和17400之间的员工的信息：名字、职位、年薪.
select first_name,title,salary*12 "Anny Say" from s_emp where title in('Stock Clerk','Sales Representative') and salary between 14400 and 17400;




9.解释select id ，commission_pct from s_emp where commission_pct is null和select id ， commission_pct from s_emp where commission_pct = null的输出结果。
10.select语句的输出结果为
select * from s_dept;
select * from s_emp;
select * from s_region;
select * from s_customer;


……
当前用户有多少张表，结果集有多少条记录。
select 'select * from '||table_name||';' from user_tables where table_name like 'S\_%' escape '\';
11
1，select语句的输出结果为
  Carmen's salary is 2500.
select first_name||'''s salary is '||salary from s_emp where first_name='Carmen'; 


2,判断select first_name , dept_id from s_emp where salary > '1450'是否抱错，为什么？
不会报错，会转义










（CORE-E-002）综合练习
定义
CORE-F-006­——CORE-F-008的综合练习：
1.改变NLS_LANG的值，让select to_char(salary*12,’L99,999.99’) from s_emp 输出结果的货币单位是￥和$0




2.列出每个员工的名字，工资、涨薪后工资（涨幅为8%），元为单位进行四舍五入
select first_name,salary,round(salary*1.08,0)from s_emp;


3.找出谁是最高领导，将名字按大写形式显示
select upper(b.first_name) from s_emp a,s_emp b where a.id(+)=b.manager_id and b.manager_id is null;


4.Ben的领导是谁（Ben向谁报告）。
select a.first_name from s_emp a,s_emp b where a.id=b.manager_id and b.first_name='Ben';


5.Ben领导谁。（谁向Ben报告）。
select a.first_name from s_emp a,s_emp b where a.manager_id =b.id and b.first_name='Ben';


6.哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资
select a.first_name,a.salary,b.first_name,b.salary from s_emp a,s_emp b where a.manager_id=b.id and a.salary>b.salary;


7.哪些员工和Biri(last_name)同部门
select a.last_name from s_emp a,s_emp b where a.dept_id=b.dept_id and b.last_name='Biri',and a.last_name<>'Biri';


8.哪些员工跟Smith(last_name)做一样职位
select a.first_name from s_emp a,s_emp b where a.title=b.title and b.last_name='Smith',and a.last_name<>'Smith';


9.哪些员工跟Biri(last_name)不在同一个部门
select a.last_name from s_emp a,s_emp b where a.dept_id<> b.dept_id and b.last_name='Biri';
select b.last_name from s_emp a,s_emp b where a.dept_id(+)=b.dept_id and a.last_name(+)='Biri' and a.dept_id is null;


10.哪些员工跟Smith(last_name)做不一样的职位
select a.first_name from s_emp a,s_emp b where a.title<>b.title and b.last_name='Smith';


11.显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称
select e.first_name,e.commission_pct,d.name,r.name from s_emp e,s_dept d,s_region r where e.dept_id=d.id and d.region_id=r.id and e.commission_pct is not null;


12.显示Operations部门有哪些职位
select distinct e.title from s_emp e,s_dept d where e.dept_id=d.id and d.name='Operations';


13.整个公司中，最高工资和最低工资相差多少
14.提成大于0的人数
15.显示整个公司的最高工资、最低工资、工资总和、平均工资，保留到整数位。
16.整个公司有多少个领导
17.列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期
select a.first_name,b.salary,b.start_date from s_emp a,s_emp b where a.start_date >b.start_date and a.salary>b.salary and a.dept_id=b.dept_id;
（CORE-E-003）综合练习
定义
CORE-F-009­——CORE-F-011的综合练习：
1.各个部门平均、最大、最小工资、人数，按照部门号升序排列  
  1  select dept_id,avg(salary),max(salary),min(salary),count(first_name)
  2  from s_emp
  3  group by dept_id
  4* order by dept_id
select avg(salary),max(salary),count(first_name) from s_emp group by dept_id order by dept_id;


2.各个部门中工资大于1500的员工人数
 select dept_id,count(first_name)
  2  from s_emp
  3  where salary>1500
  4  group by dept_id;
select dept_id,count(first_name) from s_emp where salary>1500 group by dept_id;
3.各个部门平均工资和人数，按照部门名字升序排列
  1  select max(d.name),avg(e.salary),count(e.first_name)
  2  from s_emp e,s_dept d
  3  where e.dept_id=d.id
  4  group by e.dept_id
  5* order by max(d.name)
select count(first_name),avg(salary) from s_emp e,s_dept d where e.dept_id= d.id group by dept_id order by max(d.name); 
;




4.列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数
 select max(dept_id),max(salary),count(first_name)from s_emp o where exists(select 1 from s_emp i where i.salary=o.salary and i.dept_id=o.dept_id and i.first_name<>o.first_name)


5.该部门中工资高于1000的员工数量超过2人，列出符合条件的部门：显示部门名字、地区名称
  1  select d.name,r.name
  from s_dept d,s_region r,s_emp e
  where d.region_id=r.id and d.id=e.dept_id
  and e.salary>1000
  group by d.name,r.name
  having count(e.salary)>2
select d.name,r.name from s_dept d,s_region r,s_emp e where d.region_id=r.id and e.dept_id=d.id and count(select first_name from s_emp where salary>1000 group by dept_id)>2;




6.哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）
  1  select e.first_name,e.salary
  2  from s_emp e
  3  where e.salary>(select avg(i.salary)
  4  from s_emp i);
select salary,first_name from s_emp where salary>(select avg(salary) from s_emp ) order by salary;




7.哪些员工的工资，介于32和33部门（33高些）平均工资之间
  1  select e.salary from s_emp e
  2  where e.salary between
  3  (select avg(e1.salary) from s_emp e1 where dept_id=32)
  4  and (select avg(e1.salary) from s_emp e1 where dept_id=33)
select first_name,salary from s_emp where salary between (select avg(salary) from s_emp where dept_id=32) and (select avg(salary) from s_emp where dept_id=33);


8.所在部门平均工资高于1500的员工名字
  1  select e.first_name
  2  from s_emp e
  3  where e.dept_id in(select i.dept_id
  4  from s_emp i
  5  group by i.dept_id
  6* having avg(i.salary)>1500)
select first_name from s_emp e1 where salary >(select avg(salary) from s_emp e2 where e1.dept_id=e2.dept_id);
select first_name from s_emp  where dept_id in(select dept_id from s_emp group by dept_id having avg(salary)>1500);


9.列出各个部门中工资最高的员工的信息：名字、部门号、工资
  1  select e.first_name,e.dept_id,e.salary
  2  from s_emp e
  3  where e.salary in(select max(i.salary)
  4  from s_emp i
  5  group by i.dept_id)
select first_name,dept_id,salary from s_emp where salary in(select max(salary) from s_emp group by dept_id);


10.最高的部门平均工资值的是多少
  1  select max(avg(salary))
  2  from s_emp
  3  group by dept_id;
select avg(salary),dept_id from s_emp group by dept_id having avg(salary)=(select max(avg(salary))from s_emp group by dept_id );
11.平均公子最高部门的ID


11.哪些部门的人数比32号部门的人数多
select dept_id from s_emp group by dept_id having count(first_name)>(select count(first_name) from s_emp where dept_id =32);
12.Ben的领导是谁（非关联子查询）
  1  select e.first_name
  2  from s_emp e,s_emp i
  3  where i.manager_id=e.id
  4  and i.first_name='Ben'
select first_name from s_emp where id=(select manager_id from s_emp where first_name='Ben');


13.Ben领导谁（非关联子查询）
  1  select e.first_name
  2  from s_emp e,s_emp i
  3  where e.manager_id=i.id
  4  and i.first_name='Ben'
select first_name from s_emp where manager_id =(select id from s_emp where first_name='Ben');


14.Ben的领导是谁（关联子查询）
  1 select first_name
  2  from s_emp outer
  3  where exists (select 1     
  4  from s_emp
  5  where outer.id=manager_id
  6  and first_name='Ben')
select first_name from s_emp e1 where Exists (select first_name from s_emp e2 where e1.id=e2.manager_id and e2.first_name ='Ben');


15.Ben领导谁（关联子查询）
  1  select first_name
  2  from s_emp outer
  3  where exists (select 1
  4  from s_emp
  5  where outer.manager_id=id
  6  and first_name='Ben');
select first_name from s_emp e1 where exists (select first_name from s_emp e2 where e2.id=e1.manager_id and e2.first_name='Ben');
16.列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期（关联子查询）
  1  select first_name,salary,start_date
  2  from s_emp outer
  3  where exists(select 1
  4  from s_emp
  5  where outer.start_date>start_date
  6  and outer.salary>salary
  7  group by dept_id)；
select first_name,salary,start_date from s_emp e1 where exists (select first_name from s_emp e2 where e1.salary>e2.salary and e1.start_date>e2.start_date and e1.dept_id=e2.dept_id);


17.哪些员工跟Biri(last_name)不在同一个部门（非关联子查询）
  1  select e.last_name
  2  from s_emp e
  3  where e.dept_id<>(select i.dept_id
  4  from s_emp i
  5  where i.last_name='Biri')


select first_name from s_emp e1 where dept_id not in (select dept_id from s_emp e2 where e2.last_name='Biri');


18.哪些员工跟Biri(last_name)不在同一个部门（关联子查询）
  1  select last_name
  2  from s_emp outer
  3  where not exists(select 1
  4  from s_emp
  5  where last_name='Biri'
  6* and outer.last_name='Biri')
select first_name from s_emp e1 where exists(select first_name from s_emp e2 where e1.dept_id <> e2.dept_id and e2.last_name='Biri');


19.Operations部门有哪些职位（非关联子查询）
  1  select distinct title
  2  from s_emp
  3  where dept_id in (select id
  4  from s_dept
  5  where name='Operations')
select distinct title from s_emp where dept_id in(select id from s_dept where name='Operations');


20.Operations部门有哪些职位（关联子查询）
  1  select distinct title
  2  from s_emp outer
  3  where exists(select 1 
  4  from s_dept
  5  where outer.dept_id=id
  6  and name='Operations');
select distinct title from s_emp e where exists (select  1 from s_dept d where d.id=e.dept_id and d.name='Operations');


CORE-E-004）综合练习
定义
CORE-F-012­——CORE-F-017的综合练习：
1.工资在1500至3000之间的员工，各自工作的年限（四舍五入，降序）。
select first_name,round(months_between(sysdate,start_date)/12,0) from s_emp where salary between 1500 and 3000 ;




2.公司规定，每个员工在工作25年的第一个周五，可以申请退休，查询每个员工的这天显示格式：2010-01-01
select first_name||' '||last_name,to_char((next_day(add_months(start_date,300),'friday')),'yyyy-mm-dd') retire_day from s_emp;
3.显示员工的名字、入职日期、周几入职（用英文全拼），显示顺序从周一至周日。星期
select first_name,start_date,to_char(start_date,'day') week from s_emp order by to_char(start_date-1,'d'); 




4.写一个sql脚本，实现多对多关系（暂时不实现约束）：学生表、课程表、学生选课表，学生表包含如下信息：学号、姓名、性别、出生日期、政治面貌，课程表包含如下信息：课程号、课程名称、学分、学时、学期，学生选课表包含如下信息：学号、课程号、成绩并插入数据。
insert into student26 values(01,'zhangsan',to_date('1986-09-06','yyyy-mm-dd'),'w');
insert into student26 values(02,'lisi',to_date('1986-03-01','yyyy-mm-dd'),'m');
insert into student26 values(03,'wangwu',to_date('1986-07-25','yyyy-mm-dd'),'w');
insert into student26 values(04,'adou',to_date('1986-06-03','yyyy-mm-dd'),'m');
insert into student26 values(05,'shudu',to_date('1986-01-23','yyyy-mm-dd'),w');
insert into student26 values(06,'mohuang',to_date('1986-05-26','yyyy-mm-dd'),'m');
insert into student26 values(07,'wumimng',to_date('1986-08-13','yyyy-mm-dd'),'w');
insert into student26 values(08,'niefeng',to_date('1986-03-11','yyyy-mm-dd'),'m');
insert into student26 values(09,'lieren',to_date('1986-06-05','yyyy-mm-dd'),'w');
insert into student26 values(10,'yuzou',to_date('1986-06-19','yyyy-mm-dd'),'m');
insert into student26 values(11,'wuji',to_date('1986-04-10','yyyy-mm-dd'),'w');


insert into count26 values(01,'java',32,5,'1');
insert into count26 values(02,'oracle',23,5,'1');
insert into count26 values(03,'hibernate',64,5,'1');
insert into count26 values(04,'ajax',16,5,'1');
insert into count26 values(05,'jdbc',34,5,'1');
insert into count26 values(06,'spring',30,5,'1');
insert into count26 values(07,'servlet',63,5,'1');
insert into count26 values(08,'struts',21,5,'1');
insert into count26 values(09,'jsp',38,5,'1');
insert into count26 values(10,'javascrip',21,5,'1');


insert into grade values(01,01,'zhangsan','java',90,'1');
insert into grade values(01,02,'zhangsan','oracle',80,'1');
insert into grade values(01,03,'zhangsan','hibernate',86,'1');
insert into grade values(01,04,'zhangsan','ajax',76,'1');
insert into grade values(01,05,'zhangsan','jdbc',96,'1');
insert into grade values(01,06,'zhangsan','spring',72,'1');
insert into grade values(01,07,'zhangsan','servlet',81,'1');
insert into grade values(01,08,'zhangsan','struts',90,'1');
insert into grade values(01,09,'zhangsan','jsp',68,'1');
insert into grade values(01,10,'zhangsan','javascrip',85,'1');


insert into grade values(02,01,'lisi','java',70,'1');
insert into grade values(02,02,'lisi','oracle',80,'1');
insert into grade values(02,03,'lisi','hibernate',86,'1');
insert into grade values(02,04,'lisi','ajax',65,'1');
insert into grade values(02,05,'lisi','jdbc',100,'1');
insert into grade values(02,06,'lisi','spring',60,'1');
insert into grade values(02,07,'lisi','servlet',81,'1');
insert into grade values(02,08,'lisi','struts',90,'1');
insert into grade values(02,09,'lisi','jsp',50,'1');
insert into grade values(02,10,'lisi','javascrip',85,'1');


insert into grade values(03,01,'wangwu','java',70,'1');
insert into grade values(03,02,'wangwu','oracle',80,'1');
insert into grade values(03,03,'wangwu','hibernate',86,'1');
insert into grade values(03,04,'wangwu','ajax',20,'1');
insert into grade values(03,05,'wangwu','jdbc',100,'1');
insert into grade values(03,06,'wangwu','spring',50,'1');
insert into grade values(03,07,'wangwu','servlet',81,'1');
insert into grade values(03,08,'wangwu','struts',90,'1');
insert into grade values(03,09,'wangwu','jsp',50,'1');
insert into grade values(03,10,'wangwu','javascrip',85,'1');




5.在建好表的基础上完成查询：某个学生选了哪些课程。
select cname from grade where sname='';
6.哪些学生没有选某门课程。


（CORE-E-005）综合练习
定义
CORE-F-018——CORE-F-020的综合练习：
1.完善sql脚本中的建表语句，在三张表上增加约束：学生表、课程表、学生选课表，在数据处理上体现出事务的概念。


2.将学生表上的id和课程表上的id用sequence实现。


3.修改某个学生的出生日期。
update student26 set birthday=to_date('','yyyy-mm-dd')where name='';




4.修改某门课程的学时。
update count26 set time='' where name='';
5.删除某名学生。
delete from grade where sname='';
delete from student26 where name='';
6.在学生选课表上添加两个字段学生名字和课程名称并填上值。
alter table student26 add(sname,number);




7.若有两门课程的成绩低于60分将其删除。
delete from grade where sid=(select sid from grade  where grade<60 group by sid having count(cname)>=2);
delete from student26 where id=(select sid from grade  where grade<60 group by sid having count(cname)>=2);


8.哪个学生选了所有的课程。
select distinct sname from grade where exists(select 1 from grade g,count26 c group by sid having count(*)=count(c.name));
select id,name from student26 s where not exists(select 1 from count26 c where not exists(select 1 from grade g where s.id=g.sid and c.id=g.cid));


（CORE-E-006）综合练习
定义
CORE-E-006的综合练习：
1.补充前一天的sql脚本，在学生表、课程表、学生选课表上添加索引。学生表的姓名字段、出生日期字段，课程表上的学分字段，学生选课表的成绩字段。


2.哪些地区没有员工。
select name from s_region r where not exists(select 1 from s_emp e,s_dept d where e.dept_id=d.id and d.region_id=r.id);
select s_region r,(select r.region_id,d.id did,e.id eid from s_emp e,s_dept d,s_region r where e.dept_id=d.id
and d.region_id=r.id)a
where a.rid(+)=r.id and a.eid is null;


3.42部门员工的纳税情况，税率如下：


0.00 ——1999.99 0.00
2000.00——3999.99 0.09
4000.00——5999.99 0.20
6000.00——7999.99 0.30
8000.00——9999.99 0.40
10000.00——11999.99 0.42
12000.00——139999.99 0.44
140000.00 or greater 0.45
select first_name,salary 
case when salary  >=  0.00 and salary<= 1999.99 then salary
when salary >= 2000.00 and salary<= 3999.99 then salary*0.91
when salary  >=  4000.00 and salary<= 5999.99 then salary*0.8
when salary >=  6000.00 and salary<= 7999.99 then salary*0.7
when salary  >=  8000.00 and salary<= 9999.99 then salary*0.6
when salary  >=  10000.00 and salary<= 11999.99 then salary*0.58
when salary  >=  12000.00 and salary<=13999.99 then salary*0.56
else salary *0.55
end aft_sal from s_emp;
decode(


4.从emp表和dept表中完成如下查询：各个部门不同职位的工资总和？输出如下：
select job,max(decode(deptno,10,cnt))d10,
max(decode(deptno,20,cnt))d20,
max(decode(deptno,30,cnt))d30
from(select job,deptno,count(*) cnt from emp group by job,deptno)
group by job ;


select sum(decode(deptno,10,sal)) dept_10,
sum(decode(deptno,20,sal)) dept_20,
sum(decode(deptno,30,sal)) dept_30,
job from emp    group by job;
 
JOB          DEPT_10    DEPT_20    DEPT_30   
--------- ---------- ---------- ----------
CLERK              1         2          1
SALESMAN                                4
PRESIDENT          1
MANAGER            1         1          1
ANALYST     2
5.有一个权限表XT_QXMX:(权限序号,权限名称)




QXXH   QXMC
1 发文填写
2 发文审批
3 收文填写
4 收文审批
权限-人员对应表XT_QXRY,如果某人有某个权限,则在此表有条记录(序号,权限序号,用户名称)
XH QXXH YHMC
1 2 zhangsan
2 3 zhangsan
(zhangsan有两个权限,发文审批和收文填写)
写一个查询:列出zhangsan有哪些权限,同时列出所有的权限.查询结果如下:
QXXH QXMC  是否有此权限
1 发文填写  false
2 发文审批  true
3 收文填写  true
4 收文审批  false
6.数据如下：
日期                事务所                标章NO.(varchar2) 
12/12                北京                 1
12/12                北京                 2
12/12                北京                 3
12/13                河北                 51
12/13                河北                 52
12/13                河北                 53
12/13                河北                 60
12/13                河北                 70
 输出结果如下: 只要日期和事务所一样,标章号连续就把记录合并
 日期                事务所                标章NO.(varchar2) 
12/12                北京                 1-3
12/13                河北                 51-53
12/13                河北                 60
12/13                河北                 70
（CORE-E-007）项目实战
定义
CORE-E-007的项目实战：
实验室管理系统需要说明
该系统提供如下功能模块：管理员管理、资费管理、用户管理、帐务查询、账单查询、用户自服务。有两类人群使用该系统：管理员和用户，管理员可以使用管理员管理、资费管理、用户管理、帐务查询、账单查询功能模块，用户可以使用用户自服务模块。管理员包含的信息有：管理员编码、管理员姓名、登录名、登录密码、联系电话、E-Mail、注册日期、注销日期。管理员有多人，每人可以使用多个功能模块。每个功能模块可以被多名管理员使用。
有用户管理权限的管理员可以管理用户，用户（即为帐务帐号）包含如下信息：主键编码、用户姓名（真实姓名）、用户账务帐号（用户登录名）、用户账务帐号密码（用户登录密码）、账务帐号状态（0：开通；1：暂停；2：删除）、联系电话、email、开通日期、暂停日期、付款方式(0：现金；1：银行转帐；2：邮局汇款；3：其他)、职业、省份、性别、公司名称、地址、邮编。一个用户可以在多台Solaris机器上开通帐户（即为业务帐号），实现远程登录。根据使用的时间，进行收费服务。资费政策为开通服务需要缴纳月租费10元，其它标准如下：
资费标准 
超出部分使用费 
2．45元／20小时 
0.01元/分钟 
4．95元／40小时 
9．95元／100小时 
19元／200小时 
业务帐号包含的信息为：主键编码、用户业务帐号（OS系统帐号）、用户业务帐号密码（操作系统密码）、服务器编码（机器IP地址）、业务帐号状态（0：正常；1：暂停；2：删除）、资费编码、开通日期、暂停日期。


1.根据以上说明画出E-R图并设计出表结构，并设计索引。
2.用DDL语句实现表结构、索引，其中主键用sequence实现、
3.完成数据编辑。
4.每个管理员有哪些权限？
5.哪些管理员有所有权限？
6.没有某项权限的管理员有哪些？
7.用户选择了哪些业务？
8.用户选择的业务的资费情况如何？