数据库--实例

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