SQL语句练习

本文内容来源于牛课网题库。

1.查找最晚入职员工的所有信息

返回目录

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`));

解答

select * from employees 
    order by hire_date desc limit 1;

2.查找入职员工时间排名倒数第三的员工所有信息

返回目录

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`));

使用子查询

select * from  employees where hire_date =  (
    select hire_date
  from employees 
   order by hire_date  desc 
       limit 2,1 );

不使用子查询

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

3.查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号dept_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 `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`));

使用内连接查询

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

4.查找所有已经分配部门的员工的last_name和first_name

返回目录

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 `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`));

select e.last_name ,e.first_name,d.dept_no
    from  employees e inner join dept_emp d
      where e.emp_no = d.emp_no;

5.查找所有员工的last_name和first_name以及对应部门编号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 `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`));

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

6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

返回目录

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 `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`));

from_date 是涨薪的起始日期，
因为是入职时的薪水，因此需要s.from_date = e.hire_date

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

7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

返回目录

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、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2、根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t
3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>15的条件

SELECT emp_no, COUNT(emp_no) AS t
  FROM salaries
    GROUP BY emp_no HAVING t > 15;

8.找出所有员工当前(to_date=’9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

返回目录

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`));

GROUP BY :值相同的为一组
ORDER BY　salary desc：降序排序

SELECT salary 
    FROM   salaries
      WHERE to_date='9999-01-01'
        GROUP BY salary
        ORDER BY salary desc;

9.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=’9999-01-01’

返回目录

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`));

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

10.获取所有非manager的员工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 `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`));

使用not in 和子查询来解决．

select emp_no 
    from employees 
      where emp_no not in 
        (
            select emp_no
              from dept_manager
        );

返回目录

11.获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=’9999-01-01’。

结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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 `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`));

dept_emp是员工表，dept_manager是部门表
员工不一有manager，所以要通过e.dept_no = m.dept_no过滤掉没有manager的员工

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

返回目录

12.获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

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`));

求解的是每个部门工资最高的员工
所以group by　使用d.dept_no，按照部门分组．
但是分组后

select d.dept_no, d.emp_no ,max(s.salary)
    from  dept_emp d inner join salaries s
       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。

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

使用group by 分组，使用count统计个数

select  title,count(title)
    from titles
        group by title;

返回目录

14.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

注意对于重复的emp_no进行忽略。

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

使用distinct对结果去重

select  title,count(distinct emp_no) t
    from  titles
        group by title
        having t >= 2;

返回目录

15.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

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`));

select * 
    from employees 
        where (emp_no%2 = 1)
        and last_name != 'Mary'
        order by hire_date desc;

返回目录

16.统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

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`));

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

select t.title title ,avg(s.salary) avg
    from salaries s  inner join titles t
        where  s.emp_no = t.emp_no
        and s.to_date='9999-01-01'  and t.to_date='9999-01-01' 
        group by t.title;

返回目录

17.获取当前（to_date=’9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

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`));

因为是第二大，使用　desc降序排序
limit 1,1限制第二条数据

select emp_no ,salary
    from salaries 
        where to_date='9999-01-01'
        order  by salary desc 
        limit 1,1;

返回目录

18.查找当前薪水(to_date=’9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

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 `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`));

先去除第一大，再获取第二大

select s.emp_no,s.salary,e.last_name,e.first_name 
    from employees e inner join salaries s
        on e.emp_no = s.emp_no
            where  s.to_date='9999-01-01'
                and s.salary = 
                    (
                        //获取第二大工资
                        select  max(salary)
                        from salaries
                        //去除第一大
                            where  salary not in 
                                (
                                    select  max(salary)
                                        from salaries
                                        where  to_date='9999-01-01'
                                )
                             and   to_date='9999-01-01'
                    );

返回目录

19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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 `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`));

本题思路为运用两次LEFT JOIN连接嵌套
1、第一次LEFT JOIN连接employees表与dept_emp表，得到所有员工的last_name和first_name以及对应的dept_no，也包括暂时没有分配部门的员工
2、第二次LEFT JOIN连接上表与departments表，即连接dept_no与dept_name，得到所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

select  e.last_name,e.first_name,dp.dept_name
     from (employees e left join dept_emp de on e.emp_no = de.emp_no )
           left join  departments dp on de.dept_no = dp.dept_no;

返回目录

20.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

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`));

to_date 是每一次薪资变化的记录时间
所以查找to_date最大和最小时的薪资差值即可

select 
    (select salary
           from salaries
           where  emp_no = 10001 
           order  by to_date desc limit 1
    )
     - 
     (select salary
           from salaries
           where  emp_no = 10001 
           order  by to_date asc limit 1
    )
    as growth;

返回目录

21.**查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

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 `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`));
**

select a.emp_no, (b.salary - c.salary) as growth
from
    employees as a
    inner join salaries as b
    on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
    inner join salaries as c
    on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc

返回目录

22.统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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`));

先dept_emp de inner join salaries s　去除没有部门的员工
再inner join departments 获得部门表信息　
再对dept_no进行分组

select dp.dept_no,dp.dept_name,count(s.salary)
    from (dept_emp  de  inner join  salaries s
            on de.emp_no = s.emp_no) 
        inner join  departments  dp  on  dp.dept_no = de.dept_no
        group by dp.dept_no;

返回目录

23.对所有员工的当前(to_date=’9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_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`));

s

返回目录

24.获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=’9999-01-01’

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 `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 `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 `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`));

s

返回目录

25.获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=’9999-01-01’,

结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

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 `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`));

s

返回目录

26.汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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 IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

s

返回目录

27.给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime(‘%Y’, to_date)

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`));

s

返回目录

28.查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

film表
字段  说明
film_id 电影id
title   电影名称
description 电影描述信息

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
category表
字段  说明
category_id 电影分类id
name    电影分类名称
last_update 电影分类最后更新时间

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段  说明
film_id 电影id
category_id 电影分类id
last_update 电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

s

返回目录

29.使用join查询方式找出没有分类的电影id以及名称

film表
字段  说明
film_id 电影id
title   电影名称
description 电影描述信息

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
category表
字段  说明
category_id 电影分类id
name    电影分类名称
last_update 电影分类最后更新时间

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段  说明
film_id 电影id
category_id 电影分类id
last_update 电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

s

返回目录

30.使用子查询的方式找出属于Action分类的所有电影对应的title,description

lm表
字段  说明
film_id 电影id
title   电影名称
description 电影描述信息

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
category表
字段  说明
category_id 电影分类id
name    电影分类名称
last_update 电影分类最后更新时间

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段  说明
film_id 电影id
category_id 电影分类id
last_update 电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

子查询解法：

select f.title,f.description from film as f
where f.film_id in (select fc.film_id from film_category as fc
               where fc.category_id in (select c.category_id from category as c
                                        where c.name = 'Action'));

非子查询解法：

select f.title,f.description
from film as f inner join film_category as fc on f.film_id = fc.film_id
               inner join category as c on c.category_id = fc.category_id
where c.name = 'Action';

返回目录

31.获取select

获取select * from employees对应的执行计划

EXPLAIN SELECT * FROM employees

返回目录

32.将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

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`));

select last_name||" "||first_name as name from employees
select concat（last_name，‘ ’，first_name）as name from employees

返回目录

33.创建一个actor表，包含如下列信息

列表  类型  是否为NULL 含义
actor_id    smallint(5) not null    主键id
first_name  varchar(45) not null    名字
last_name   varchar(45) not null    姓氏
last_update timestamp   not null    最后更新时间，默认是系统的当前时间

根据题意，本题关键点是actor_id的主键设置与last_update的默认获取系统时间：
1、在actor_id字段末尾加上PRIMARY KEY是将该字段设置为主键，
或者在表的最后一行加上PRIMARY KEY(actor_id)
2、在last_update末尾加上DEFAULT是为该字段设置默认值，
且默认值为(datetime('now','localtime'))，即获得系统时间，注意最外层的括号不可省略
CREATE TABLE actor(
  actor_id smallint(5) PRIMARY KEY NOT NULL,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  last_update timestamp NOT NULL default (datetime('now','localtime'))
);

返回目录

34.批量插入数据

对于表actor批量插入如下数据
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

方法一：利用VALUES(value1, value2, ...), (value1, value2, ...), ...(value1, value2, ...),
INSERT INTO actor
VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')

方法二：利用 UNION SELECT 批量插入
INSERT INTO actor
SELECT 1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'
UNION SELECT 2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33'

返回目录

35.批量插入数据，不使用replace操作

对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

insert IGNORE into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');

返回目录

36.创建一个actor_name表

对于如下表actor，其对应的数据为:
actor_id    first_name  last_name   last_update
1   PENELOPE    GUINESS 2006-02-15 12:34:33
2   NICK    WAHLBERG    2006-02-15 12:34:33

创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：
列表  类型  是否为NULL 含义
first_name  varchar(45) not null    名字
last_name   varchar(45) not null    姓氏

create table actor_name
select first_name,last_name from actor;

返回目录

37.对first_name创建唯一索引uniq_idx_firstname

针对如下表actor结构创建索引：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

先用 CREATE UNIQUE INDEX ... ON ... 对first_name创建唯一索引值，
再用 CREATE INDEX ... ON ... 对last_name创建普通索引值
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

返回目录

38.针对actor表创建视图actor_name_view

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

方法一：注意 CREATE VIEW ... AS ... 的 AS 是创建视图语法中的一部分，而后面的两个 AS 只是为字段创建别名

CREATE VIEW actor_name_view AS
SELECT first_name AS fist_name_v, last_name AS last_name_v
FROM actor 
方法二：直接在视图名的后面用小括号创建视图中的字段名
CREATE VIEW actor_name_view (fist_name_v, last_name_v) AS
SELECT first_name, last_name FROM actor 

返回目录

39.针对上面的salaries表emp_no字段创建索引idx_emp_no

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
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`));

create index idx_emp_no on salaries(emp_no);

SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005

返回目录

40.在last_update后面新增加一列名字为create_date

存在actor表，包含如下列信息：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')));
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'

ALTER TABLE actor ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
其中 ADD 后的 COLUMN 可省略，NOT NULL 和 DEFAULT '0000-00-00 00:00:00' 可交换：
ALTER TABLE actor ADD create_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL ;

返回目录

41.构造一个触发器audit_log

构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

链接：https://www.nowcoder.com/questionTerminal/7e920bb2e1e74c4e83750f5c16033e2e
来源：牛客网

1.创建触发器使用语句：CREATE TRIGGER trigname;
2.指定触发器触发的事件在执行某操作之前还是之后，使用语句：
BEFORE/AFTER [INSERT/UPDATE/ADD] ON tablename
3.触发器触发的事件写在BEGIN和END之间；
4.触发器中可以通过NEW获得触发事件之后2对应的tablename的相关列的值，
OLD获得触发事件之前的2对应的tablename的相关列的值

CREATE TRIGGER audit_log AFTER INSERT ON employees_test
BEGIN
    INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
END;

返回目录

42.删除emp_no重复的记录，只保留最小的id对应的记录。

```
删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

返回目录

43.将所有to_date为9999-01-01的全部更新为NULL

将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

另外要注意若干列 to_date = NULL 和 from_date = '2001-01-01' 之间只能用逗号连接，切勿用 AND 连接。

UPDATE titles_test SET to_date = NULL, from_date = '2001-01-01'
WHERE to_date = '9999-01-01';

返回目录

44.将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

方法一：全字段更新替换。由于 REPLACE 的新记录中 id=5，与表中的主键 id=5 冲突，故会替换掉表中 id=5 的记录，否则会插入一条新记录（例如新插入的记录 id = 10）。并且要将所有字段的值写出，否则将置为空。可参考：
http://blog.csdn.net/zhangjg_blog/article/details/23267761


REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')
方法二：运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。以下语句用 UPDATE和REPLACE 配合完成，用REPLACE函数替换后的新值复制给 id=5 的 emp_no。REPLACE的参数为整型时也可通过。可参考：
http://www.cnblogs.com/huangtailang/p/5cfbd242cae2bcc929c81c266d0c875b.html
http://sqlite.org/lang_corefunc.html#replace


UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005) WHERE id = 5
/** 另外可以利用OJ系统的漏洞，不用 REPLACE 实现  **/

UPDATE titles_test SET emp_no = 10005 WHERE id = 5

返回目录

45.将titles_test表名修改为titles_2017

将titles_test表名修改为titles_2017。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

ALTER TABLE titles_test RENAME TO titles_2017

返回目录

46.在audit表上创建外键约束，其emp_no对应employees_test表的主键id

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

drop table audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    foreign key(EMP_no) references employees_test(ID));

返回目录

47.如何获取emp_v和employees有相同的数据no

存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据？
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`));
输出格式:
emp_no  birth_date  first_name  last_name   gender  hire_date
10006   1953-04-20  Anneke  Preusig F   1989-06-02
10007   1957-05-23  Tzvetan Zielinski   F   1989-02-10
10008   1958-02-19  Saniya  Kalloufi    M   1994-09-15
10009   1952-04-19  Sumant  Peac    F   1985-02-18
10010   1963-06-01  Duangkaew   Piveteau    F   1989-08-24
10011   1953-11-07  Mary    Sluis   F   1990-01-22

由于视图 emp_v 的记录是从 employees 中导出的，所以要判断两者中相等的数据，只需要判断emp_no相等即可。
方法一：用 WHERE 选取二者 emp_no 相等的记录

SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no
方法二：用 INTERSECT 关键字求 employees 和 emp_v 的交集
可参考：http://www.sqlite.org/lang_select.html


SELECT * FROM employees INTERSECT SELECT * FROM emp_v
方法三：仔细一想，emp_v的全部记录均由 employees 导出，因此可以投机取巧，直接输出 emp_v 所有记录

SELECT * FROM emp_v
【错误方法：】用以下方法直接输出 *，会得到两张表中符合条件的重复记录，因此不合题意，必须在 * 前加表名作限定

SELECT * FROM employees, emp_v WHERE employees.emp_no = emp_v.emp_no

返回目录

48.将所有获取奖金的员工当前的薪水增加10%

将所有获取奖金的员工当前的薪水增加10%。
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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`));

按照正常的逻辑以及之前题目的尿性，要先选出符合条件的 emp_no，即用 INNER JOIN 连接 salaries 和 emp_bonus，且用 s.to_date = '9999-01-01' 表示当前薪水，然后再用 UPDATE ... SET ... WHERE ... IN ... 语句来更新表中数据。

UPDATE salaries SET salary = salary * 1.1 WHERE emp_no IN
(SELECT s.emp_no FROM salaries AS s INNER JOIN emp_bonus AS eb 
ON s.emp_no = eb.emp_no AND s.to_date = '9999-01-01')
但又发现题目测试用例没设置好，emp_bonus里面的全部 emp_no 都是当前获奖的所有员工，于是就有了以下简易答案：

UPDATE salaries SET salary = salary * 1.1 WHERE emp_no IN 
(SELECT emp_no FROM emp_bonus)
谁知道还能继续投机取巧，估计是OJ系统的问题，将所有 salary 都上涨10%也能通过，于是又有了以下终极版最短答案：

UPDATE salaries SET salary = salary * 1.1 

返回目录

49.针对库中的所有表生成select count(*)对应的SQL语句

针对库中的所有表生成select count(*)对应的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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

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 `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`));
输出格式:
cnts
select count(*) from employees;
select count(*) from departments;
select count(*) from dept_emp;
select count(*) from dept_manager;
select count(*) from salaries;
select count(*) from titles;
select count(*) from emp_bonus;

select concat('select count(*) from', ' ', TABLE_NAME, ';') as cnts
 from (select table_name from information_schema.tables where table_schema='shop') as hi;

返回目录

50.将employees表中的所有员工的last_name和first_name通过(‘)连接起来。

将employees表中的所有员工的last_name和first_name通过(')连接起来。
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`));
输出格式:
name
Facello'Georgi
Simmel'Bezalel
Bamford'Parto
Koblick'Chirstian
Maliniak'Kyoichi
Preusig'Anneke

select concat(last_name,"‘",first_name) as name 
from employees;

返回目录

51.查找字符串’10,A,B’ 中逗号’,’出现的次数cnt

select length('10,A,B') -length(replace('10,A,B',",",""))

返回目录

52.获取Employees中的first_name

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
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`));
输出格式：
first_name
Chirstian
Tzvetan
Bezalel
Duangkaew

本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

SELECT first_name FROM employees ORDER BY substr(first_name,length(first_name)-1) 

SELECT first_name FROM employees ORDER BY substr(first_name,-2) 

返回目录

53.按照dept_no进行汇总

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
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`));
输出格式:
dept_no employees
d001    10001,10002
d002    10006
d003    10005

select dept_no,group_concat(emp_no SEPARATOR ',') from dept_emp group by dept_no;

返回目录

54.查找排除当前最大、最小salary之后的员工的平均工资avg_salary

查找排除当前最大、最小salary之后的员工的平均工资avg_salary。
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`));
输出格式:
avg_salary
69462.5555555556

select avg(salary) as avg_salary from salaries
where
salary not in (select min(salary )from salaries  ) and
salary not in  (select max(salary )from salaries  ) and
to_date='9999-01-01'

返回目录

55.分页查询employees表，每5行一页，返回第2页的数据

分页查询employees表，每5行一页，返回第2页的数据
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`));

根据题意，每行5页，返回第2页的数据，即返回第6~10条记录，以下有两种方法可以解决：
方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。

SELECT * FROM employees LIMIT 5 OFFSET 5
方法二：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。

SELECT * FROM employees LIMIT 5,5

返回目录

56.获取所有员工的emp_no

获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示
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 `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 `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 `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`));
输出格式:
e.emp_no    dept_no btype   recevied
10001   d001    1   2010-01-01
10002   d001    2   2010-10-01
10003   d004    3   2011-12-03

本题严谨的思路为，先将 employees与dept_emp 用 INNER JOIN 连接，挑选出分配了部门的员工，再用 LEFT JOIN 连接 emp_bonus（在前面的题中可看到此表），分配了奖金的员工显示奖金类型和授予时间，没分配奖金的员工则不显示。

SELECT em.emp_no, de.dept_no, eb.btype, eb.recevied
FROM employees AS em INNER JOIN dept_emp AS de
ON em.emp_no = de.emp_no
LEFT JOIN emp_bonus AS eb 
ON de.emp_no = eb.emp_no
由于dept_emp表中都是已分配部门的员工，因此只用 dept_emp表与emp_bonus表就可以解决问题：

SELECT de.emp_no, de.dept_no, eb.btype, eb.recevied
FROM dept_emp AS de LEFT JOIN emp_bonus AS eb 
ON de.emp_no = eb.emp_no

返回目录

57.使用含有关键字exists查找未分配具体部门的员工的所有信息。

使用含有关键字exists查找未分配具体部门的员工的所有信息。
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_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`));
输出格式:
emp_no  birth_date  first_name  last_name   gender  hire_date
10011   1953-11-07  Mary    Sluis   F   1990-01-22

本题用 EXISTS 关键字的方法如下：意为在 employees 中挑选出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录，也就是当 employees.emp_no=10011的时候。反之，把NOT去掉，则输出 employees.emp_no=10001~10010时的记录。

SELECT * FROM employees WHERE NOT EXISTS 
(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)
由于 OJ系统没有限制我们只能使用 EXISTS 关键字，因此还能用 NOT IN 关键字替换，即在employees 中选出 dept_emp 中没有的 emp_no。

SELECT * FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_emp)

返回目录

58.获取employees中的行数据，且这些行也存在于emp_v中

存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
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`));
获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。
输出格式:
emp_no  birth_date  first_name  last_name   gender  hire_date
10006   1953-04-20  Anneke  Preusig F   1989-06-02
10007   1957-05-23  Tzvetan Zielinski   F   1989-02-10
10008   1958-02-19  Saniya  Kalloufi    M   1994-09-15

链接：https://www.nowcoder.com/questionTerminal/2556c1fcc92c490d9bf331ab07dfb7dc
来源：牛客网

根据题意，不能使用 INTERSECT 关键字，但由于视图 emp_v 的记录是从 employees 中导出的，
因此要判断两者中相等的数据，只需要判断emp_no相等即可。
方法一：用 WHERE 选取二者 emp_no 相等的记录

SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no
方法二：由于emp_v的全部记录均由 employees 导出，因此可以投机取巧，直接输出 emp_v 所有记录

SELECT * FROM emp_v

返回目录

59.获取有奖金的员工相关信息。

获取有奖金的员工相关信息。
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_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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

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`));
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
输出格式:
emp_no  first_name  last_name   btype   salary  bonus
10001   Georgi  Facello 1   88958   8895.8
10002   Bezalel Simmel  2   72527   14505.4
10003   Parto   Bamford 3   43311   12993.3

select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,case
when eb.btype='1' then s.salary*0.1 
when eb.btype='2' then s.salary*0.2
else salary*0.3 end as bonus
from employees e right join emp_bonus eb
on e.emp_no = eb.emp_no left join salaries s
on eb.emp_no = s.emp_no and s.to_date='9999-01-01';

返回目录

60.统计salary的累计和running_total

按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。
 具体结果如下Demo展示。。
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`));
输出格式:
emp_no  salary  running_total
10001   88958   88958
10002   72527   161485
10003   43311   204796

题的思路为复用 salaries 表进行子查询，最后以 s1.emp_no 排序输出求和结果。
1、输出的第三个字段，是由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2，
主查询的 salaries 表记为 s1，当主查询的 s1.emp_no 确定时，对子查询中不大于
 s1.emp_no 的 s2.emp_no 所对应的薪水求和
2、注意是对员工当前的薪水求和，所以在主查询和子查询内都要加限定条件 to_date = '9999-01-01'

SELECT s1.emp_no, s1.salary, 
(SELECT SUM(s2.salary) FROM salaries AS s2 
 WHERE s2.emp_no <= s1.emp_no AND s2.to_date = '9999-01-01') AS running_total 
FROM salaries AS s1 WHERE s1.to_date = '9999-01-01' ORDER BY s1.emp_no

返回目录

61.对于employees表中，给出奇数行的first_name

对于employees表中，给出奇数行的first_name
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`));
输出格式:
first_name
Georgi
Chirstian

SELECT e1.first_name FROM
employees e1
WHERE
(SELECT count(*) FROM employees e2
WHERE e1.first_name <=e2.first_name)%2=1;