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 from salaries where to_date='9999-01-01' and
salary =
(select distinct salary from salaries
where to_date='9999-01-01'
order by salary desc
limit 1,1)
2. 查找最晚入职员工的所有信息
问题
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`));
答案
select * from employees where hire_date = (select max(hire_date) from employees)
3. 查找入职员工时间排名倒数第三的员工所有信息
问题
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`));
答案
select * from employees order by hire_date desc limit 2,1;
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`));
4. 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
问题 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
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((select salary from salaries where emp_no = 10001 order by to_date desc limit 1) -
(select salary from salaries where emp_no = 10001 order by to_date asc limit 1)) as growth
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`));
答案
select de.emp_no, dm.emp_no as manager_no
from dept_emp as de inner join dept_manager as dm
on de.dept_no = dm.dept_no
where dm.to_date='9999-01-01' and
de.to_date='9999-01-01' and
de.emp_no <> dm.emp_no
select d.dept_no,d.emp_no,s.salary from dept_manager as d join salaries as s
on d.emp_no = s.emp_no where s.to_date='9999-01-01' and d.to_date='9999-01-01';
select s.* ,d.dept_no
from salaries as s
join dept_manager as d
on s.emp_no=d.emp_no
where s.to_date = '9999-01-01'
and d.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 `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`));
查找所有已经分配部门的员工的last_name和first_name
select e.last_name,e.first_name,d.dept_no from dept_emp as d join employees as e on e.emp_no = d.emp_no;
select e.last_name,e.first_name,d.dept_no from employees as e left join dept_emp as d on e.emp_no=d.emp_no;
查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
select e.emp_no,s.salary
from employees as e join salaries as s
on e.emp_no = s.emp_no where s.from_date = e.hire_date
order by e.emp_no desc;
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`));
获取所有非manager的员工emp_no
select emp_no from employees as e where e.emp_no not in (select emp_no from dept_manager);
select * from employees where emp_no%2!=0 and last_name != 'Mary' order by hire_date desc;
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);
从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
select title,count(title) as t from titles group by title having t>=2;
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 N...