1、题目
统计各个部门的工资记录数,给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录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`));
输入描述:
无
输出描述:
dept_no dept_name sum
d001 Marketing 24
d002 Finance 14
d003 Human Resources 13
d004 Production 24
d005 Development 25
d006 Quality Management 25
```sql
select dept_emp.dept_no,departments.dept_name,count(*) as sum
from dept_emp inner join departments on departments.dept_no=dept_emp.dept_no
inner join salaries on dept_emp.emp_no=salaries.emp_no
group by departments.dept_no
2、题目
对所有员工的当前(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`));
解:
select emp_no,salary, dense_rank() over(order by salary DESC) as rank
from salaries
where salaries.to_date='9999-01-01'
注意:这里可以使用 dens_rank 函数 :关于dense_rank函数的使用可以借鉴:SQL四大排序
3、
题目描述
获取所有非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`));
输入描述:
无
输出描述:
dept_no emp_no salary
d001 10001 88958
d004 10003 43311
d005 10007 88070
d006 10009 95409
解:
select de.dept_no,de.emp_no,s.salary
from dept_emp de inner join employees em on de.emp_no=em.emp_no
inner join salaries s on de.emp_no=s.emp_no
where em.emp_no not in
(select emp_no from dept_manager)
and s.to_date='9999-01-01'
4、
题目描述
获取员工其当前的薪水比其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`));
输入描述:
无
输出描述:
emp_no manager_no emp_salary manager_salary
10001 10002 88958 72527
10009 10010 95409 94409
select t1.emp_no as emp_no,t2.emp_no as manager_no,
t1.salary as emp_salary,t2.salary as manager_salary
from
(select * from dept_emp inner join salaries on dept_emp.emp_no=salaries.emp_no
where salaries.to_date='9999-01-01') t1,
(select * from dept_manager inner join salaries on dept_manager.emp_no=salaries.emp_no
where salaries.to_date='9999-01-01') t2
where t1.dept_no=t2.dept_no
and t1.salary>t2.salary