1.查找employees表emp_no与last_name的员工信息
思路:where条件判断即可
select emp_no,birth_date,first_name,last_name,gender,hire_date
from employees
where emp_no%2!=0 and last_name!="Mary"
order by hire_date desc
补充:不相等有三种表示方式:<>、!=、IS NOT
2.统计出当前各个title类型对应的员工当前薪水对应的平均工资
思路:先分组后排序
select t.title,avg(s.salary)
from titles t
inner join salaries s
on t.emp_no=s.emp_no
group by t.title
order by avg(s.salary)
3.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
思路:因为有重复的最高salary,所以先找到最高薪水(这里可能薪水最高的有多个,通过分组的方式来去重,也可以直接distinct),然后再进一步select。这里的limit 1 offset 1也可以写成limit 1,1,表示跳过一条读一条数据。
select emp_no,salary
from salaries
where salary
= (select salary
from salaries
#这里可能薪水最高的有多个,所以先进行分组
group by salary
order by salary desc
limit 1 offset 1)
或者
4. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary(不能使用order by #)
思路:
方法1.可以找到最高工资的人,然后查出除了原表最高工资以外的最高工资,最后将第二高工资作为查询条件。(找到第二高工资之后只是作为筛选条件,还需要再用salary做一次join)
方法2.通过自连接,on条件中当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2,对s2去重之后的数量就是对应的名次。(#)
第一种:
select s.emp_no, s.salary, e.last_name, e.first_name
from salaries s join employees e
on s.emp_no = e.emp_no
where s.salary = -- 第三步: 将第二高工资作为查询条件
(
select max(salary) -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
from salaries
where salary <
(
select max(salary) -- 第一步: 查出原表最高工资
from salaries
where to_date = '9999-01-01'
)
and to_date = '9999-01-01'
)
and s.to_date = '9999-01-01'
第二种 通用型可以求任意第几高,并且可以求多个形同工资
select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join
salaries s on e.emp_no=s.emp_no
and s.to_date='9999-01-01'
and s.salary =
(
select s1.salary
from
salaries s1
join
salaries s2 on s1.salary<=s2.salary
and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
group by s1.salary
having count(distinct s2.salary)=2
)
5.查找所有员工的last_name和first_name以及对应的dept_name
思路:可以先后使用两次left join,也可以使用left join嵌套来实现。
方法1:先后left join
select e.last_name,e.first_name,m.dept_name
from employees e
left join dept_emp d
on e.emp_no=d.emp_no
left join departments m
on d.dept_no=m.dept_no
方法2:left join嵌套
SELECT last_name, first_name, dept_name
FROM (SELECT *
FROM employees
LEFT JOIN dept_emp
ON employees.emp_no=dept_emp.emp_no
) AS a
LEFT JOIN departments
ON a.dept_no=departments.dept_no;
6.查找在职员工自入职以来的薪水涨幅情况
思路:拼出入职的工资表和现在的工资表,然后两者工资相减。where s.to_date='9999-01-01’也可以写成on的连接条件。
select b.emp_no,(b.salary-a.salary) as growth
from
(select e.emp_no,s.salary
from employees e left join salaries s on e.emp_no=s.emp_no
and e.hire_date=s.from_date)a -- 入职工资表
inner join
(select e.emp_no,s.salary
from employees e left join salaries s on e.emp_no=s.emp_no
where s.to_date='9999-01-01')b -- 现在工资表
on a.emp_no=b.emp_no
order by growth
7.统计各个部门的工资记录数
思路:两次join可以连着写,另外只能根据dt.dept_no分组,不能用de.dept_no分组,有点奇怪~
select dt.dept_no,dt.dept_name,count(dt.dept_no) sum
from departments dt
left join dept_emp de on dt.dept_no=de.dept_no
left join salaries s on de.emp_no=s.emp_no
group by dt.dept_no,dt.dept_name
order by dt.dept_no ASC
8.SQL217 对所有员工的薪水按照salary降序进行1-N的排名
思路:难点在于排名的具体值怎么体现。
方法1.通过使用比较的方式找出大于某个人的工资数就是排名了,注意去重(同名并列)。
select
s1.emp_no,
s1.salary,
(select
count(distinct s2.salary)
from salaries s2
where s2.salary >= s1.salary
) as `t_rank`
from salaries s1
order by `t_rank`,s1.emp_no
方法2.这道题目考察的是SQL窗口函数(OLAP函数)中用于排序的专用窗口函数用法
但是由于关系数据库提供支持OLAP用途功能时间不长
还有一部分DBMS不支持这个新功能(比如MYSQL)
select emp_no, salary,
dense_rank() over (order by salary desc) as rank
from salaries
where to_date='9999-01-01'
order by rank asc,emp_no asc;
下面介绍三种用于进行排序的专用窗口函数:
1、RANK()
在计算排序时,若存在相同位次,会跳过之后的位次。
例如,有3条排在第1位时,排序为:1,1,1,4······
2、DENSE_RANK()
这就是题目中所用到的函数,在计算排序时,若存在相同位次,不会跳过之后的位次。
例如,有3条排在第1位时,排序为:1,1,1,2······
3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如,有3条排在第1位时,排序为:1,2,3,4······
窗口函数用法:
<窗口函数> OVER ( [PARTITION BY <列清单> ]
ORDER BY <排序用列清单> )
*其中[ ]中的内容可以忽略
9.获取所有非manager员工当前的薪水情况
思路:
方法1:使用not in
select
a.dept_no,a.emp_no,s.salary
from (select
*
from dept_emp e
where e.emp_no not in (select emp_no from dept_manager)) a
left join salaries s
on a.emp_no=s.emp_no
方法2:左连接,然后判断emp_no is null的即为非manager
10.获取员工其当前的薪水比其manager当前薪水还高的相关信息
思路:
分别获取员工和管理者的工资表,然后使用部门号连接,并且增加员工工资>经理工资的筛选条件。
**联结表emp_sal和表mag_sal,连接条件部门编号相等,要求:员工工资>经理工资**
select emp_sal.emp_no,mag_sal.manager_no,
emp_sal.emp_salary,mag_sal.manager_salary
from (
select de.emp_no,de.dept_no,s1.salary as emp_salary
from dept_emp de,salaries s1
where de.emp_no=s1.emp_no
and s1.to_date='9999-01-01'
and de.to_date='9999-01-01'
)as emp_sal
inner join(
select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
from dept_manager dm,salaries s2
where dm.emp_no=s2.emp_no
and s2.to_date='9999-01-01'
and dm.to_date='9999-01-01'
)as mag_sal
on emp_sal.dept_no=mag_sal.dept_no
where mag_sal.manager_salary<emp_sal.emp_salary;
11.汇总各个部门当前员工的title类型的分配数目
思路:总之就是一顿连接,而且要注意是对谁分组。
经验:遇到比较多的表,把连接关系列出来,把结果设计到的字段也确定好列在纸上,可以更好的理清逻辑。
select dm.dept_no,dm.dept_name,title,count(t.title) count
from departments dm
left join dept_emp de
on dm.dept_no=de.dept_no
left join titles t
on de.emp_no=t.emp_no
group by dm.dept_no,t.title //注意这里是针对dm的dept_no进行分组,对de就不行了。。。
order by dept_no,title;
12.使用join查询方式找出没有分类的电影id以及名称
思路:左连接,找出category_id为null的即为未分类。同样纸上列出关系表以及结果表,更快解决问题。
如:film–>film_id,title category–>category_id,name f_c—>f_id,c_id 结果要求:f_id title
select f.film_id,f.title
from film f
left join film_category fc
on f.film_id=fc.film_id
left join category c
on c.category_id=fc.category_id
where c.category_id is null;
13.使用子查询的方式找出属于Action分类的所有电影对应的title,description
思路:内连接inner join,可以简写成join(返回两个表中都满足条件的行,即交集。注意区分全连接),子查询
select f.title,f.description
from film f
join film_category fc
on f.film_id=fc.film_id
join (
select category_id
from category
where name="Action"
) filterCate
on fc.category_id=filterCate.category_id
#或者使用in的方式
# select title,description
# from film f
# where f.film_id in (select fc.film_id
# from category c join film_category fc on c.category_id=fc.category_id
# where name='Action')