第一题
根据需求说明找到对应的SQL完成2—11题
A. select max(salary) , min(salary) , sum(salary) , round(avg(salary)) from s_emp;
B. select max(salary) , min(salary) , sum(salary) , avg(salary) from s_emp;
C. select e1.first_name , e2.first_name from s_emp e1 , s_emp e2 where
e1.title(+) = e2.title and e2.last_name(+) = ‘Smith’ and e2.last_name is null;
D. select first_name,start_date,round(months_between(sysdate,start_date)/12)
years from s_emp where salary between 2000 and 3000 order by years desc;
E. select e1.dept_id , e1.salary , count(*) cnt from s_emp e1 , s_emp
e2 where e1.salary = e2.salary and e1.dept_id = e2.dept_id and e1.id
<> e2.id group by e1.id , e1.salary ;
F. select d.name dname , r.name rname , first_name , commission_pct
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;
G. select max(d.name) dname , max(r.name) rname , avg(e.salary) avgsal
from s_emp e , s_dept d , s_region r where e.dept_id = d.id and
d.region_id = r.id group by dname order by dept_id , rname;
H. select d.name , r.name , avg(e.salary) from s_emp e , s_dept d ,
s_region r where e.dept_id = d.id and d.region_id = r.id and r.name =
‘Europe’ group by d.name , r.name;
I. select d.name dname , r.name rname , first_name , commission_pct
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 null;
J. select dept_id , avg(salary) avgsal from s_emp group by dept_id
having avg(salary) > (select avg(salary) from s_emp where dept_id =32);
K. select e1.first_name , e2.first_name from s_emp e1 , s_emp e2 where
e1.title(+) = e2.title and e2.last_name= ‘Smith’ and e2.last_name is null;
L. select e1.dept_id , e1.salary , count(*) cnt from s_emp e1 , s_emp
e2 where e1.salary = e2.salary and e1.dept_id = e2.dept_id and e1.id
<> e2.id group by e1.dept_id , e1.salary ;
M. select dept_id , avg(salary) from s_emp group by dept_id where avg(salary) > 2000;
N. select dept_id , avg(salary) from s_emp group by dept_id having avg(salary) > 2000;
O. select max(d.name) , max(r.name) , avg(e.salary) from s_emp e ,
s_dept d , s_region r where e.dept_id = d.id and r.name = ‘Europe’ group by e.dept_id;
P. select dept_id , avg(salary) avgsal from (select avg(salary) from s_emp where dept_id = 32);
Q. select max(d.name) dname , max(r.name) rname , avg(e.salary) avgsal
from s_emp e , s_dept d , s_region r where e.dept_id = d.id and
d.region_id = r.id group by dept_id order by dname , rname;
R. select dept_id , salary from s_emp where (dept_id , salary) not in (select dept_id , avg(salary) from s_emp group by dept_id);
S. select dept_id , salary from s_emp where (dept_id , salary) in
(select dept_id , avg(salary) from s_emp group by dept_id);
T. select first_name,start_date,round(between(sysdate,start_date)/12)
years from s_emp where salary between 2000 and 3000 order by years desc;
2显示整个公司的最高工资、最低工资、工资总和、平均工资,保留到整数位( )。
3列出每个部门中有同样工资的员工的统计信息,列出他们的部门号,工资,人数( )。
4各个部门平均工资和人数,按照部门名字升序排列( )。
5显示有提成的员工的信息:名字、提成、所在部门名称、所在地区的名称( )。
6哪些员工跟Smith(last_name)做不一样的职位( )。
7平均工资大于2000元的各个部门的平均工资:部门号、平均工资( )。
8Europe地区的各个部门的平均工资:显示部门名称、地区名称、平均工资( )。
9哪些部门的平均工资比32部门的平均工资高( )。
10哪些员工的工资等于本部门的平均工资( )。
11工资在2000至3000之间的员工,各自工作的年限(四舍五入,降序)( )。
第二题
根据需求说明找到对应的SQL完成13—22题
A. select dept_id , count(*) cnt from s_emp group by dept_id having
count(*) > (select count(*) from s_emp where dept_id = 32);
B. select e.ename , e.sal from emp e , salgrade s where e.sal between s.losal and s.hisal;
C. select e1.dept_id , e1.salary , count(*) cnt from s_emp e1 , s_emp e2 where e1.salary = e2.salary and e1.dept_id = e2.dept_id and e1.id<> e2.id group by e1.dept_id , e1.salary;
D. select e.first_name, e.salary , m.first_name , m.salary from s_emp e , s_emp m where e.manager_id = m.id and e.salary < m.salary;
E. select e.ename , e.sal from emp e , salgrade s where e.sal between s.losal(+) and s.hisal(+);
F. select first_name from s_emp where id not in (select manager_id from s_emp where manager_id is not null);
G. select max(d.name) , max(r.name) , avg(e.salary) from s_emp e ,s_dept d , s_region r where e.dept_id = d.id and d.region_id = r.id;
H. select first_name from s_emp where id not in (select manager_id from s_emp where manager_id is null);
I. select first_name,to_char(start_date,'yyyy-mm-dd'),
to_char(next_day(add_months(start_date,12*25),'FRIDAY'),'yyyy-mm-dd') from s_emp;
J. select dept_id , count(*) cnt from s_emp group by dept_id having
count(*) > (select * from s_emp where dept_id = 32);
K. select e1.first_name , e1.start_date , e1.salary from s_emp e1 ,
s_emp e2 where e1.dept_id = e2.dept_id and e1.start_date >
e2.start_date and e1.salary > e2.salary;
L. select d.name , r.name ,avg(e.salary) from s_emp e , s_dept d , s_region r where e.dept_id =
d.id and d.region_id = r.id group by d.name , r.name;
M. select first_name , salary , dept_id from s_emp where (dept_id ,
salary) in(select dept_id , max(salary) from s_emp group by salary);
N. select first_name , salary , dept_id from s_emp where (dept_id ,
salary) in(select dept_id , max(salary) from s_emp group by dept_id);
O. select first_name,to_char(start_date,’yyyy-mm-dd’),
to_char(‘yyyy-mm-dd’,next_day(add_months(start_date,12*25),’FRIDAY’)) from s_emp;
P. select first_name from s_emp o where (select 1 from s_emp i where
i.dept_id = o.dept_id and o.start_date > i.start_date and o.salary> i.salary);
Q. select max(d.name) dname , max(r.name) rname , count(*) cnt from
s_emp e , s_dept d , s_region r where e.dept_id = d.id and d.region_id
= r.id and e.salary > 2000 group by dept_id having count(*) > 3;
R. select max(d.name) dname , max(r.name) rname , count(*) cnt from
s_emp e , s_dept d , s_region r having e.dept_id = d.id and d.region_id
= r.id and e.salary > 2000 group by dept_id where count(*) > 3;
S. select e1.dept_id , e1.salary , count(*) cnt from s_emp e1 , s_emp
e2 where e1.salary = e2.salary and e1.dept_id = e2.dept_id and e1.id
<> e2.id group by e1.salary; T. select e.first_name , e.salary ,
m.first_name , m.salary from s_emp e , s_emp m where e.manager_id =
m.id and e.salary > m.salary;
13列出每个部门中有同样工资的员工的统计信息,列出他们的部门号,工资,人数( )。
14该部门中工资高于2000的员工数量超过3人,列出符合条件的部门:显示部门名字、地区名称( )。
15列出在同一部门共事,入职日期晚但工资高于其他同事的员工:名字、工资、入职日期( )。
16公司规定,每个员工在工作25年的第一个周五,可以申请退休,查询每个员工的这天.显示格式:2000-10-31( )。
17列出各个部门中工资最高的员工的信息:名字、部门号、工资( )。
18哪些部门的人数比32号部门的人数多( )。
19哪些人是员工( )。
20各个部门的平均工资:显示部门名称、地区名称、平均工资( )。
21哪些员工的工资高于他直接上司的工资,列出员工的名字和工资,上司的名字和工资( )。
22哪些员工的工资不在工资级别范围之内( )。
联系客服