1.SQL2 查找入职员工时间排名倒数第三的员工所有信息
select *
from employees
ORDER by hire_date desc
LIMIT 2,1
注意:limit n,m :含义是大于n的第一个整数,一共m个
2.SQL5 请你查找所有已经分配部门的员工的last_name和first_name以及dept_no,也包括暂时没有分配具体部门的员工
select e.last_name
,e.first_name
,IFNULL(NULL,d.dept_no)
from employees e
left JOIN dept_emp d on e.emp_no=d.emp_nov
注意:ifnull(m,n):代表的含义是如果是空值null,则传入参数m,否则传入n
3.SQL15 查找employees表emp_no与last_name的员工信息
select *
from employees
where last_name !='Mary'
and mod(emp_no,2)!=0
# in (
# select emp_no
# from employees
# where mod(emp_no,2)!=0
# )
ORDER by hire_date desc
注意:mod(m,n)含义m除n取余
4.查找在职员工自入职以来的薪水涨幅情况
select v.emp_no,v.salary-b.salary
from (
select emp_no,salary
from salaries
where to_date='9999-01-01'
)v
left join
# (select a.*
# from (
# select emp_no
# ,salary
# ,to_date
# ,rank() over(partition by emp_no order by from_date) as rk
# from salaries
# )a
# where a.rk=1 )b
(
select emp_no
,salary
,min(from_date)
from salaries
group by emp_no
)b
on b.emp_no=v.emp_no
order by v.salary-b.salary
解题思路:求涨幅问题。利用两表连接,一个计算pre值,另一个计算now值
5.SQL33 创建一个actor表,包含如下列信息
drop table if exists `actor` ;
CREATE TABLE actor(
actor smallint(5) NOT null ,
first_name varchar(45) NOT null ,
last_name varchar(45) NOT null ,
last_update date NOT null
)