牛客网数据库SQL实战(21-25)

21、查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
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`));

牛客网数据库SQL实战(21-25)

select a.emp_no, (b.salary - c.salary) as growth
from
employees as a
inner join salaries as b
on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
inner join salaries as c
on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc;

讨论:https://www.nowcoder.com/questionTerminal/fc7344ece7294b9e98401826b94c6ea5

22、统计各个部门对应员工涨幅的次数总和,给出部门编码dept_no、部门名称dept_name以及次数sum
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 `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`));

牛客网数据库SQL实战(21-25)

select departments.dept_no,departments.dept_name,count(*) sum
from departments,dept_emp,salaries
where departments.dept_no = dept_emp.dept_no
and dept_emp.emp_no = salaries.emp_no
group by departments.dept_no;

讨论:https://www.nowcoder.com/questionTerminal/6a62b6c0a7324350a6d9959fa7c21db3

23、对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名,相同salary并列且按照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`));

牛客网数据库SQL实战(21-25)

select s1.emp_no,s1.salary,count(distinct s2.salary)
from salaries s1,salaries s2
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.salary<= s2.salary/*给定s1.salary求有多少个s2.salary大于它*/
group by s1.emp_no
order by s1.salary desc,s1.emp_no asc;

讨论:https://www.nowcoder.com/questionTerminal/b9068bfe5df74276bd015b9729eec4bf

24、获取所有非manager员工当前的薪水情况,给出dept_no、emp_no以及salary ,当前表示to_date='9999-01-01'
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`));
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`));

牛客网数据库SQL实战(21-25)

SELECT de.dept_no, s.emp_no, s.salary
FROM dept_emp AS de
INNER JOIN salaries AS s
ON s.emp_no = de.emp_no
AND s.to_date = '9999-01-01'
WHERE de.emp_no NOT IN
(SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01');

1、先用INNER JOIN连接employees和salaries,找出当前所有员工的工资情况

2、再用INNER JOIN连接dept_emp表,找到所有员工所在的部门
3、最后用限制条件de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')选出当前所有非manager员工,再依次输出dept_no、emp_no、salary
25、获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_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 `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`));
牛客网数据库SQL实战(21-25)
SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM
(SELECT s.salary, s.emp_no, de.dept_no
FROM salaries s
INNER JOIN dept_emp de
ON s.emp_no = de.emp_no
AND s.to_date = '9999-01-01' ) AS sem,
(SELECT s.salary, s.emp_no, dm.dept_no
FROM salaries s
INNER JOIN dept_manager dm
ON s.emp_no = dm.emp_no
AND s.to_date = '9999-01-01' ) AS sdm
WHERE sem.dept_no = sdm.dept_no
AND sem.salary > sdm.salary;

1、先用INNER JOIN连接salaries和demp_emp,建立当前所有员工的工资记录sem

2、再用INNER JOIN连接salaries和demp_manager,建立当前所有员工的工资记录sdm
3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工,并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary
上一篇:[Android] Android GreenDao 保存 JavaBean 或者List 类型数据


下一篇:java_常用数据类型转换基础篇