1-10
1、查找最晚入职员工的所有信息
/* select * from employees where hire_date = (select max(hire_date) from employees); */ /* select * from employees order by hire_date desc limit 1; */
2、查找入职员工时间排名倒数第三的员工所有信息
/* select * from employees where hire_date = ( select distinct hire_date from employees order by hire_date desc limit 1 offset 2 ); */ /*该方法没有考虑到重复的日期,错误*/ select * from employees order by hire_date limit 1 offset 2;
表结构:
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`));
3、查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no
select s.*,d.dept_no from salaries as s , 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'
4、查找所有已经分配部门的员工的last_name和first_name以及dept_no
select e.last_name,e.first_name,d.dept_no from dept_emp as d left join employees as e on d.emp_no=e.emp_no;
5、查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工
select e.last_name,e.first_name,d.dept_no from employees as e left outer join dept_emp as d on d.emp_no = e.emp_no;
6、查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
select e.emp_no,s.salary from employees as e inner join salaries as s on e.emp_no = s.emp_no and e.hire_date = s.from_date order by e.emp_no desc;
7、查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
select emp_no,count(emp_no) as t from salaries group by emp_no having t>15;
8、找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
select distinct salary from salaries where to_date='9999-01-01' order by salary desc;
9、获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date='9999-01-01'
select d.dept_no,s.emp_no,s.salary from salaries as s inner join dept_manager as d on d.emp_no = s.emp_no /*方法1 and s.to_date='9999-01-01' and d.to_date='9999-01-01'; */ /*方法2*/ and d.to_date = s.to_date where s.to_date='9999-01-01'
10、获取所有非manager的员工emp_no
/* select e.emp_no from employees as e where e.emp_no not in ( select emp_no from dept_manager; ) */ SELECT emp_no FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
11-20
11、获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date='9999-01-01'。
-
选择结果为 两列包括 emp_no 以及 manager_no
-
emp_no来自dept_emp中,manager来自dept_manager
-
emp_no要去除其中的manager
-
按照emp_no对应的dept_no在dept_manager找到emp_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 not in( select emp_no from dept_manager ); */ 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;
12、获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
-
先用INNER JOIN连接两张表,限制条件是两张表的emp_no相同,即d.emp_no = s.emp_no;
-
选取每个员工当前的工资水平,用d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'作条件限制,因为此表中每条最新记录的 to_date 都用 9999-01-01 表示;
-
用GROUP BY d.dept_no将每个部门分为一组,用MAX()函数选取每组中工资最高者;
-
将salaries用s代替,dept_emp用d代替,最后将MAX(s.salary)用salary代替后输出。
SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' GROUP BY d.dept_no
13、从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
select title,count(*) as t from titles group by title having count(*)>=2;
14、从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
select title,count(distinct emp_no) as t from titles group by title having t>=2;
15、查找employees表所有emp_no为奇数,且last_name不为Mary的员工信息,并按照hire_date逆序排列
select * from employees where emp_no%2 =1 and last_name!= 'Mary' order by hire_date desc;
16、统计出当前各个title类型对应的员工当前(to_date='9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。
select t.title,avg(s.salary) as avg from salaries as s inner join titles as t on s.emp_no = t.emp_no /*where s.to_date='9999-01-01'*/ and s.to_date='9999-01-01'