数据表如下
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 `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`));
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 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);
1、查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no
输出描述:
emp_no | salary | from_date | to_date | dept_no |
---|---|---|---|---|
10002 | 72527 | 2001-08-02 | 9999-01-01 | d001 |
10003 | 72537 | 2001-10-02 | 9999-01-01 | d002 |
select salaries.*,dept_manager.dept_no from salaries, dept_manager
where salaries.emp_no = dept_manager.emp_no
and salaries.to_date = "9999-01-01"
and dept_manager.to_date = "9999-01-01";
2、查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
select employees.emp_no, salaries.salary from employees, salaries
where employees.hire_date = salaries.from_date
and
employees.emp_no = salaries.emp_no
order by employees.emp_no desc;
3、查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
输出描述:
emp_no | t |
---|---|
10010 | 21 |
10021 | 18 |
利用Group by 的having进行条件筛选
select emp_no, count(*) as t from salaries
group by emp_no having t>15;
4、找出所有员工当前(to_date=’9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
输出描述:
salary |
---|
94692 |
93221 |
select distinct(salary) from salaries
where to_date = '9999-01-01'
order by salary desc;
5、获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date=’9999-01-01’
输出描述:
dept_no | emp_no | salary |
---|---|---|
d001 | 10002 | 72527 |
d004 | 10004 | 74057 |
d003 | 10005 | 94692 |
d002 | 10006 | 43311 |
d006 | 10010 | 94409 |
select d.dept_no , d.emp_no ,s.salary
from salaries s join dept_manager d on d.emp_no = s.emp_no
where d.to_date = '9999-01-01'
and s.to_date = '9999-01-01';
6、获取所有非manager的员工emp_no
select emp_no from employees
where emp_no not in (select emp_no from dept_manager);
7、获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
输出描述:
dept_no | emp_no | salary |
---|---|---|
d001 | 10002 | 72527 |
d004 | 10004 | 74057 |
d003 | 10005 | 94692 |
select d.dept_no, d.emp_no, max(s.salary) from dept_emp d, salaries s
where d.emp_no = s.emp_no
and d.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
group by dept_no;
8、统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资average。
select title, avg(salary) as average from titles t, salaries s
where t.emp_no = s.emp_no
and t.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
group by title
9、 获取当前(to_date=’9999-01-01’)薪水第二多的员工的emp_no以及其对应的薪水salary
select emp_no, salary from salaries
where to_date = '9999-01-01'
order by salary desc
limit 1,1;
10、查找当前薪水(to_date=’9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by
select e.emp_no, max(s.salary) salary, last_name, first_name
from employees e, salaries s
where e.emp_no = s.emp_no
and to_date = '9999-01-01'
and s.salary not in (select max(salary) from salaries where to_date = '9999-01-01');