查找所有员工入职时候的薪水情况
查找所有员工入职时候的薪水情况,给出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
));
select e.emp_no,s.salary from employees e,salaries s
where e.emp_no=s.emp_no and e.hire_date=s.from_date
order by e.emp_no desc;
查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
查找薪水变动超过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
));
select emp_no, count(to_date) as t
from salaries
group by emp_no
# group by 结果按员工号码统计
having t > 15;
找出所有员工当前具体的薪水salary情况
找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
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 distinct salary
from salaries
where to_date='9999-01-01'
order by salary desc;
获取所有部门当前manager的当前薪水情况
获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况,给出dept_no, emp_no以及salary,输出结果按照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
));
select d.dept_no,s.emp_no,s.salary
from dept_manager d,salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'
order by d.dept_no ;
获取所有非manager的员工emp_no
获取所有非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
));
如插入为:
INSERT INTO dept_manager VALUES(‘d001’,10002,‘1996-08-03’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d002’,10006,‘1990-08-05’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d003’,10005,‘1989-09-12’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d004’,10004,‘1986-12-01’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d005’,10010,‘1996-11-24’,‘2000-06-26’);
INSERT INTO dept_manager VALUES(‘d006’,10010,‘2000-06-26’,‘9999-01-01’);
INSERT INTO employees VALUES(10001,‘1953-09-02’,‘Georgi’,‘Facello’,‘M’,‘1986-06-26’);
INSERT INTO employees VALUES(10002,‘1964-06-02’,‘Bezalel’,‘Simmel’,‘F’,‘1985-11-21’);
INSERT INTO employees VALUES(10003,‘1959-12-03’,‘Parto’,‘Bamford’,‘M’,‘1986-08-28’);
INSERT INTO employees VALUES(10004,‘1954-05-01’,‘Chirstian’,‘Koblick’,‘M’,‘1986-12-01’);
INSERT INTO employees VALUES(10005,‘1955-01-21’,‘Kyoichi’,‘Maliniak’,‘M’,‘1989-09-12’);
INSERT INTO employees VALUES(10006,‘1953-04-20’,‘Anneke’,‘Preusig’,‘F’,‘1989-06-02’);
INSERT INTO employees VALUES(10007,‘1957-05-23’,‘Tzvetan’,‘Zielinski’,‘F’,‘1989-02-10’);
INSERT INTO employees VALUES(10008,‘1958-02-19’,‘Saniya’,‘Kalloufi’,‘M’,‘1994-09-15’);
INSERT INTO employees VALUES(10009,‘1952-04-19’,‘Sumant’,‘Peac’,‘F’,‘1985-02-18’);
INSERT INTO employees VALUES(10010,‘1963-06-01’,‘Duangkaew’,‘Piveteau’,‘F’,‘1989-08-24’);
INSERT INTO employees VALUES(10011,‘1953-11-07’,‘Mary’,‘Sluis’,‘F’,‘1990-01-22’);
select e.emp_no
from employees e
left join dept_manager d
on e.emp_no=d.emp_no
where d.dept_no is null;
# 左连接,右边dept_no是空的话代表没有主管
select emp_no
from employees
where emp_no not in (select emp_no from dept_manager)