SQL查询练习一

SQL查询语句练习一

题目来源：https://www.nowcoder.com/ta/sql

所涉及到的表及其结构

#员工信息表
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`));

#部门经理表
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 `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 `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`));

1、查找最晚入职员工的所有信息
解题思路：最晚入职，意味着入职时间最大，用分组函数max()解决问题

select * from employees
    where hire_date = (select max(hire_date) from employees);

2、查找入职员工时间排名倒数第三的员工所有信息
解题思路：取结果集的第n行，利用mysql的limit关键字即可得到想要的行数

select * from employees
    order by hire_date desc
    limit 2,1;

3、查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no
解题思路：先进行条件筛选出当前的有效行，再进行表连接

select salaries.*, dept_manager.dept_no
    from salaries,dept_manager
    where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01'
            and salaries.emp_no=dept_manager.emp_no;

4、查找所有已经分配部门的员工的last_name和first_name及对应的部门号
解题思路：表连接

select last_name,first_name,dept_no
    from employees,dept_emp
    where employees.emp_no=dept_emp.emp_no;

5、查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工
解题思路：利用表的左外连接

select last_name,first_name,dept_no
    from employees left outer join dept_emp
    on dept_emp.emp_no=employees.emp_no;

6、查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序
解题思路：当入职日期与薪水开始时间一致说明对应薪水是入职薪水，再进行表连接取相应的字段，最后再对结果进行逆序

select salaries.emp_no,salary from salaries,employees
    where from_date=hire_date and salaries.emp_no=employees.emp_no 
    order by salaries.emp_no desc;

7、查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
解题思路：薪水涨幅对应salaries表里的同一emo_no的记录数目，先利用group by进行分组，再利用count函数计算行数，利用having对结果进行限定

select emp_no,count(*) t from salaries
    group by emp_no
    having count(*)>15;

8、找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
解题思路：利用关键字distinct消除重复行即可

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’
解题思路：条件限定筛选出当前的manager和薪水情况，再进行表连接取相应字段

select dept_no,dept_manager.emp_no,salary from dept_manager,salaries
    where dept_manager.to_date='9999-01-01' and salaries.to_date='9999-01-01'
        and dept_manager.emp_no=salaries.emp_no;

10、获取所有非manager的员工emp_no
解题思路：当employees表里的emp_no没有与dept_manager表里的emp_no对应，就说明该员工为非manager，利用not exists提高查询效率，只要子查询中两表的emp_no相同，外层的当前行即可舍弃，取下一行进行判断

select emp_no from employees
    where not exists 
        (select 'x' from dept_manager 
             where employees.emp_no=dept_manager.emp_no);

11、获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=‘9999-01-01’。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
解题思路：先条件筛选出当前的manager，再进行表连接，最后将连接表里的两个emp_no字段中值相同的行剔除掉

select dept_emp.emp_no,dept_manager.emp_no manager_no
    from dept_emp,dept_manager
    where dept_emp.to_date='9999-01-01' and dept_manager.to_date='9999-01-01'
        and dept_emp.dept_no=dept_manager.dept_no
        and dept_emp.emp_no!=dept_manager.emp_no;

12、获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
解题思路：连接两表形成临时表，按照部门进行分组，再在每个部门中取得最大薪水的相应行即可

select dept_no,emp_no,max(salary)
    from (select dept_no,dept_emp.emp_no,salary
        from dept_emp,salaries
        where dept_emp.to_date='9999-01-01' and salaries.to_date='9999-01-01'
         and dept_emp.emp_no=salaries.emp_no)
    group by dept_no;