51* 获取Employees中的first_name

51.1 题目描述

获取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
Georgi
Kyoichi
Anneke
Sumant
Mary
Parto
Saniya

51.2 题解 substr

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

52 按照dept_no进行汇总

52.1 题目描述

按照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_noemployees
d00110001,10002
d00210006
d00310005
d00410003,10004
d00510007,10008,10010
d00610009,10010

52.2 题解 group_concat(字段名,分隔符)

select 
    dept_no
    ,group_concat(emp_no, ",")
from 
    dept_emp
group by
    dept_no
  • 本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数==必须与 GROUP BY 配合使用==。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。

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

53.1 题目描述

查找排除当前最大、最小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

53.2 题解

  • 正常逻辑，OJ过不了
select
    avg(salary) as avg_salary
from 
    salaries
where
    salary not in(
        -- 1 找到最高工资和最小工资的salary
        (select    
            max(salary)
        from 
            salaries
        where 
            to_date = '9999-01-01') -- OJ中需要把时间条件删除，才能过
        ,(select
            min(salary)
        from 
            salaries
        where 
            to_date = '9999-01-01') -- OJ中需要把时间条件删除，才能过
    )
and
    to_date = '9999-01-01'
  • OJ通过题解
select
    avg(salary) as avg_salary
from 
    salaries
where
    salary not in(
        -- 1 找到最高工资和最小工资的salary
        (select    
            max(salary)
        from 
            salaries
        )
        ,(select
            min(salary)
        from 
            salaries
        )
    )
and
    to_date = '9999-01-01'

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

54.1 题目描述

分页查询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`));

54.2 题解 limit offset

  • 方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回==（第一条记录序号为0）==，也可理解为跳过多少条记录后开始返回。
select 
    *
from 
    employees
limit 
    5 -- 返回5条数据
offset
    5 -- 从第6条数据开始返回
  • 只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。
select 
    *
from 
    employees
limit 
    5,5 -- 从第6条记录开始，返回4条数据（第1条数据是第0条数据）

55 获取所有员工的emp_no

55.1 题目描述

获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示

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

create table emp_bonus(
emp_no int not null,
received datetime not null,
btype smallint not null);

输出格式:

e.emp_nodept_nobtypereceived
10001d00112010-01-01
10002d00122010-10-01
10003d00432011-12-03
10004d00412010-01-01
10005d003
10006d002
10007d005
10008d005
10009d006
10010d005
10010d006

55.2 题解

select
    d.emp_no
    ,d.dept_no
    ,e.btype
    ,e.recevied -- 这个字段是题目打错的，我日，建议击毙
from 
    dept_emp d
left join
    emp_bonus e
on 
    d.emp_no = e.emp_no
  • 正常题解：本题严谨的思路为，先将 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

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

56.1 题目描述

使用含有关键字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_nobirth_datefirst_namelast_namegenderhire_date
100111953-11-07MarySluisF1990-01-22

56.2 题解 exists

  • 不使用exists
select 
    * 
from 
    employees
where 
    emp_no not in (
        select 
            emp_no
        from 
            dept_emp d
    )
  • 使用exists
select 
    * 
from 
    employees
where not exists (
    select 
        emp_no
    from 
        dept_emp 
    where 
           emp_no = employees.emp_no -- 外面的表一定要和存在条件的子表差生关联
)
SELECT 
    * 
FROM 
    employees 
WHERE emp_no NOT exists (
    SELECT 
        emp_no 
       FROM 
        dept_emp)  -- 过不了，在 employees 中没有一条记录能使 (SELECT emp_no FROM dept_emp) 不成立。(SELECT emp_no FROM dept_emp) 没有跟 emplotees 产生联系，因为无论选中 employees 中的哪条记录，(SELECT emp_no FROM dept_emp) 都成立

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

57.1 题目描述

存在如下的视图：

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_nobirth_datefirst_namelast_namegenderhire_date
100061953-04-20AnnekePreusigF1989-06-02
100071957-05-23TzvetanZielinskiF1989-02-10
100081958-02-19SaniyaKalloufiM1994-09-15
100091952-04-19SumantPeacF1985-02-18
100101963-06-01DuangkaewPiveteauF1989-08-24
100111953-11-07MarySluisF1990-01-22

57.2 题解

-- 这题真的智障
select 
    * 
from
    emp_v

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

58.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`));
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_nofirst_namelast_namebtypesalarybonus
10001GeorgiFacello1889588895.8
10002BezalelSimmel27252714505.4
10003PartoBamford34331112993.3
10004ChirstianKoblick1740577405.7

58.2 题解 case..when..then..else..end

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

59* 统计salary的累计和running_total

59.1 题目描述

按照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_nosalaryrunning_total
100018895888958
1000272527161485
1000343311204796
1000474057278853
1000594692373545
1000643311416856
1000788070504926
1000995409600335
1001094409694744
1001125828720572

59.2 题解

本题的思路为复用 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 
    s2.emp_no
    ,s2.salary
    ,(
        select 
            sum(salary)
        from 
            salaries s1
        where 
            s1.emp_no <= s2.emp_no -- 复用两张表
        and 
            s1.to_date = '9999-01-01'
    ) as running_total
from 
    salaries s2
where 
    s2.to_date = '9999-01-01'

60* 对于employees表中，给出奇数行的first_name

60.1 题目描述

对于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
Anneke
Tzvetan
Saniya
Mary

60.2 题解

SELECT 
    E1.first_name 
FROM 
    employees E1
WHERE (
    SELECT 
        COUNT(*)  -- 序号 复用2张表，先对first_name进行排序，count(*)就是序号
    FROM 
        employees E2 
    WHERE 
        E1.first_name >= E2.first_name) % 2 = 1;
Last modification：May 14th, 2020 at 10:59 am
© 允许规范转载