牛客数据库SQL实战题(11-20题)
11、获取所有员工当前的manager
如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
答案
select emp.emp_no, ma.emp_no as manager_no
from dept_emp as emp
join dept_manager as ma
on ma.dept_no=emp.dept_no
where ma.to_date='9999-01-01' and emp.to_date='9999-01-01'
and emp.emp_no <> ma.emp_no;
如果当前的manager是自己的话不显示该条，这个条件用不等号来表示就可以。
12、获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
答案
select de.dept_no, de.emp_no, max(sa.salary) as salary
from dept_emp as de
join salaries as sa
on de.emp_no=sa.emp_no
where de.to_date='9999-01-01' and sa.to_date='9999-01-01'
group by de.dept_no;
根据de.dept_no分组，选择sa.salary最大的行，de.emp_no也会选择相应的行。
13、从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t
CREATE TABLE IF NOT EXISTS titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
答案
select title, count(title) as t
from titles
group by title
having t>=2;
select title之后还可以count(title)。
14、从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
CREATE TABLE IF NOT EXISTS titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
答案
select title, count(distinct emp_no) as t
from titles
group by title
having t>=2;
要去除重复的emp_no可以在count中对distinct emp_no进行计数。
15、查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
答案
select *
from employees
where emp_no%2=1 and last_name<>'Mary'
order by hire_date desc;
mysql中取余用%除数，在oracle中取余是mod(被除数, 除数)。
16、统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
CREATE TABLE IF NOT EXISTS titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
答案
select ti.title as title, avg(sa.salary) as avg
from titles as ti
join salaries as sa
on ti.emp_no=sa.emp_no
where ti.to_date='9999-01-01' and sa.to_date='9999-01-01'
group by ti.title;
17、获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
答案
select emp_no, salary
from salaries
where to_date='9999-01-01' and salary=
(select distinct salary
from salaries
order by salary desc
limit 1,1);
薪水第二多的人可能有多个
18、查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
答案
select em.emp_no, max(sa.salary) as salary, em.last_name, em.first_name
from employees as em
join salaries as sa
on em.emp_no=sa.emp_no
where sa.to_date='9999-01-01' and sa.salary<
(select max(salary) from salaries where to_date='9999-01-01');
不用order by选择次高，则先选出最高，然后排出最高，选出剩下数据中的最高。
19、查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (dept_no));
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
答案
select em.last_name, em.first_name, dename.dept_name
from (employees as em
left join dept_emp as de
on em.emp_no=de.emp_no)
left join departments as dename
on de.dept_no=dename.dept_no;
连接三张表，先将employees表和dept_emp表左连接，employees中的有的员工没有分配部门，所以要用left join。也会有员工有多个部门的记录，这题并没有要求筛选当前。
20、查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
答案
select (select salary
from salaries
where emp_no='10001'
order by from_date desc
limit 1) -
(select salary
from salaries
where emp_no='10001'
order by from_date
limit 1) as growth;
另外，在MS Access中支持first()函数和last()函数，分别可以获取列的第一个值和最后一个值。但是其他数据库不支持，一般用limit来选择。
结尾
如果您发现我的文章有任何错误，或对我的文章有什么好的建议，请联系我！如果您喜欢我的文章，请点喜欢~*我是蓝白绛，感谢你的阅读！
