SQL12题目描述如下:
思路:本题要查询的是每个部门薪水最高的员工号,部门号,薪水
先根据emp_no连接两个表,按照部门分组group by
找出每个组薪水最高的
代码如下:
select d.dept_no, s.emp_no,s.salary
from salaries s left join dept_emp d on s.emp_no = d.emp_no/*连接表*/
group by d.dept_no having s.salary=max(s.salary)/*分组,再找到薪水最高的那个*/
order by dept_no/*排序*/
SQL15题目描述如下:
思路:emp_no为奇数: emp_no%2=1
last_name不为Marry的员工: last_name is not ‘Marry’
按照hire_date逆序排序 order by hire_date desc
代码:
select *
from employees
where emp_no%2=1 and last_name is not 'Mary'
order by hire_date desc