ＳＱＬ面试题

第一题
根据需求说明找到对应的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哪些员工的工资不在工资级别范围之内（ ）。

同类其他面试题 点击新一篇或旧一篇可浏览全部同类面试题

新一篇：数据库笔试题

旧一篇：大量数据并发的情况下 ，不用自增列 怎样设置主键