牛客网SQL实战练习——6~10

声明：练习牛客网SQL实战题目，整理笔记。
6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_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`));
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`));

分析：本题涉及两个表，employees和salaries。首先由emp_no连接两个表，入职时候的薪水情况即e.hire_date = s.from_date，按照emp_no进行逆序即order by e.emp_no desc;
答案：

select e.emp_no,s.salary
from employees as e ,salaries as s
where e.emp_no=s.emp_no and e.hire_date = s.from_date
order by e.emp_no desc;

7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

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`));

分析：用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数,根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t,由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件.
答案：

select emp_no,count(salary) as t
from salaries
group by emp_no
having t>15

8.找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示(问题代码同上)
分析：DISTINCT语法用于仅返回不同的（different）值，所以使用distinct关键字返回薪水。
答案：

select distinct(salary)
from salaries
where to_date='9999-01-01'
order by salary desc;

9.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

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`));
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`));

分析：先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no，并且将salaries用别名s代替，dept_manager用别名d代替，再加上限制条件to_date='9999-01-01'*即可
答案：

select d.dept_no,d.emp_no,s.salary
from dept_manager as d inner join salaries as s
on d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'

10.获取所有非manager的员工emp_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`));
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`));

分析：本题可以使用嵌套查询，查询employees中的emp_no,然后使用not in去除dept_manager中的emp_no.
答案：

select emp_no
from employees
where emp_no not in 
(
select emp_no
from dept_manager
)

欢迎关注微信公众号：蛋炒番茄
同步更新！！！