最新公告
  • 新注册用户请前往个人中心绑定邮箱以便接收相关凭证邮件!!!点击前往个人中心
  • 牛客网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`));

    输入描述:

    输出描述:

    emp_no
    birth_date
    first_name
    last_name
    gender
    hire_date
    10008
    1958-02-19
    Saniya
    Kalloufi
    M
    1994-09-15
    select * from employees
    where hire_date =
    (select max(hire_date) from employees)
    SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;  # 不推荐
    top和limit方法有牵强之处,与给定数据集有关
    最晚入职的当天未必就一个人,也许有多人,使用排序并限制得只能取得指定数量的结果

    题目描述

    查找入职员工时间排名倒数第三的员工所有信息
    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
    10005
    1955-01-21
    Kyoichi
    Maliniak
    M
    1989-09-12
    SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1;
    LIMIT m,n : 表示从第m+1条开始,取n条数据;
    LIMIT n : 表示从第0条开始,取n条数据,是limit(0,n)的缩写。
    本题limit 2,1 表示从第(2+1)条数据开始,取一条数据,即入职员工时间排名倒数第三的员工。

    题目描述

    查找各个部门当前(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`));

    输入描述:

    输出描述:

    emp_no
    salary
    from_date
    to_date
    dept_no
    10002
    72527
    2001-08-02
    9999-01-01
    d001
    10004
    74057
    2001-11-27
    9999-01-01
    d004
    10005
    94692
    2001-09-09
    9999-01-01
    d003
    10006
    43311
    2001-08-02
    9999-01-01
    d002
    10010
    94409
    2001-11-23
    9999-01-01
    d006
    select s.*,d.dept_no
    from salaries s, dept_manager d
    where s.to_date = ‘9999-01-01’
    and d.to_date = ‘9999-01-01’
    and s.emp_no = d.emp_no;
    这里的坑主要在于两个表的逻辑关系,题目要求是薪水情况以及部门编号,再结合输出情况dept_no 被放到了最后一列,可见是主表是“salaries”。这里顺序错了就会提示:您的代码无法通过所有用例。。。

    题目描述

    查找所有已经分配部门的员工的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`));

    输入描述:

    输出描述:

    last_name
    first_name
    dept_no
    Facello
    Georgi
    d001
    省略
    省略
    省略
    Piveteau
    Duangkaew
    d006
    SELECT e.last_name, e.first_name, d.dept_no
    FROM dept_emp d NATURAL JOIN employees e;
    只有一列是公有的,用自然连接呀亲们。

    题目描述

    查找所有员工的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`));

    输入描述:

    输出描述:

    last_name
    first_name
    dept_no
    Facello
    Georgi
    d001
    省略
    省略
    省略
    Sluis
    Mary
    NULL(在sqlite中此处为空,MySQL为NULL)
    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;
    注意:
    INNER JOIN 两边表同时有对应的数据,即任何一边缺失数据就不显示。
    LEFT JOIN 会读取左边数据表的全部数据,即便右边表无对应数据。
    RIGHT JOIN 会读取右边数据表的全部数据,即便左边表无对应数据。
    注意
    on与where有什么区别,两个表连接时用on,在使用left  jion时,on和where条件的区别如下:
    1、  on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
    2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left  join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

    题目描述

    查找所有员工入职时候的薪水情况,给出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`));

    输入描述:

    输出描述:

    emp_no
    salary
    10011
    25828
    省略
    省略
    10001
    60117
    select e.emp_no,s.salary
    from employees e,salaries s
    where  e.emp_no = s.emp_no
    and e.hire_date = s.from_date
    order by e.emp_no desc;
    SELECT e.emp_no, s.salary FROM employees AS e INNER JOIN salaries AS s
    ON e.emp_no = s.emp_no AND e.hire_date = s.from_date
    ORDER BY e.emp_no DESC
    此题应注意以下四个知识点:
    1、由于测试数据中,salaries.emp_no不唯一(因为号码为emp_no的员工会有多次涨薪的可能,所以在salaries中对应的记录不止一条),employees.emp_no唯一,即salaries的数据会多于employees,因此需先找到employees.emp_no在salaries表中对应的记录salaries.emp_no,则有限制条件e.emp_no = s.emp_no
    2、根据题意注意到salaries.from_date 和employees.hire_date的值应该要相等,因此有限制条件 e.hire_date = s.from_date
    3、根据题意要按照emp_no值逆序排列,因此最后要加上 ORDER BY e.emp_no DESC
    4、为了代码良好的可读性,运用了Alias别名语句,将employees简化为e,salaries简化为s,即 employees AS e与salaries AS s,其中AS可以省略

    题目描述

    查找薪水涨幅超过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`));

    输入描述:

    输出描述:

    emp_no
    t
    10001
    17
    10004
    16
    10009
    18
    select emp_no,count(emp_no) as t
    from salaries
    group by emp_no
    having t > 15;
    此题应注意以下四点:
    1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
    2、根据题意,输出的涨幅次数为t,故用AS语句将COUNT(emp_no)的值转换为t
    3、由于COUNT()函数不可用于WHERE语句中,故使用HAVING语句来限定t>15的条件
    4、最后存在一个理解误区,涨幅超过15次,salaries中相应的记录数应该超过16(从第2条记录开始算作第1次涨幅),不过题目为了简单起见,将第1条记录当作第1次涨幅,所以令t>15即可
    /**  注意: 严格来说,下一条salary高于本条才算涨幅,但本题只要出现了一条记录就算一次涨幅,salary相同可以理解为涨幅为0,salary变少理解为涨幅为负 **/

    题目描述

    找出所有员工当前(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`));

    输入描述:

    输出描述:

    salary
    94692
    94409
    88958
    88070
    74057
    72527
    59755
    43311
    25828
    select salary from salaries  where to_date=’9999-01-01′ group by salary order by salary desc;
    SELECT DISTINCT salary FROM salaries WHERE to_date = ‘9999-01-01’ ORDER BY salary DESC

    对于distinct,groupby的性能。

    数据量非常巨大时候,比如1000万中有300W重复数据,这时候的distinct的效率略好于group by;
    对于相对重复量较小的数据量比如1000万中1万的重复量,用groupby的性能会远优于distnct。
    简书上的一篇博客说的不错,大家可以穿送过去看一看传送门
    重复量小用group by,重复量大用dstinct

    题目描述

    获取所有部门当前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`));

    输入描述:

    输出描述:

    dept_no
    emp_no
    salary
    d001
    10002
    72527
    d004
    10004
    74057
    d003
    10005
    94692
    d002
    10006
    43311
    d006
    10010
    94409
    SELECT d.dept_no, d.emp_no, s.salary 
    FROM salaries AS s INNER JOIN dept_manager AS d 
    ON d.emp_no = s.emp_no
    AND d.to_date = ‘9999-01-01’
    AND s.to_date = ‘9999-01-01’
    1、先用INNER JOIN连接两张表,限制条件是两张表的emp_no相同,即d.emp_no = s.emp_no,并且将salaries用别名s代替,dept_manager用别名d代替
    2、根据题意,要获取当前manager的当前salary情况,再加上限制条件d.to_date = ‘9999-01-01’ AND s.to_date = ‘9999-01-01’即可(因为同一emp_no在salaries表中对应多条涨薪记录,而当s.to_date = ‘9999-01-01’时是该员工当前的薪水记录)

    题目描述

    获取所有非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`));

    输入描述:

    输出描述:

    emp_no
    10001
    10003
    10007
    10008
    10009
    10011
    方法一:使用NOT IN选出在employees但不在dept_manager中的emp_no记录
    SELECT emp_no FROM employees
    WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
    not in在实际使用中,因为not in会转化成多表连接,而且不使用索引,在这里,觉得还是用left_join代替会好一点
    方法二:先使用LEFT JOIN连接两张表,再从此表中选出dept_no值为NULL对应的emp_no记录
    SELECT emp_no FROM (SELECT * FROM employees LEFT JOIN dept_manager
    ON employees.emp_no = dept_manager.emp_no)
    WHERE dept_no IS NULL
    方法三:方法二的简版,使用单层SELECT语句即可
    SELECT employees.emp_no FROM employees LEFT JOIN dept_manager
    ON employees.emp_no = dept_manager.emp_no
    WHERE dept_no IS NULL

    题目描述

    获取所有员工当前的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`));

    输入描述:

    输出描述:

    emp_no
    manager_no
    10001
    10002
    10003
    10004
    10009
    10010
    select de.emp_no,dm.emp_no as manager_no
    from dept_emp de,dept_manager dm
    where de.dept_no = dm.dept_no
    and de.to_date=’9999-01-01′
    and dm.to_date=’9999-01-01′
    and de.emp_no <> dm.emp_no;
    应注意以下三点:
    1、用INNER JOIN连接两张表,因为要输出自己的经理,得知自己与经理的部门要相同,故有限制条件 de.dept_no = dm.dept_no
    2、再用WHERE限制当前员工与当前经理的条件,即 dm.to_date 等于 ‘9999-01-01’ 、de.to_date 等于 ‘9999-01-01’ 、 de.emp_no 不等于dm.emp_no
    3、为了增强代码可读性,将dept_emp用别名de代替,dept_manager用dm代替,最后根据题意将de.emp_no用别名manager_no代替后输出

    题目描述

    获取所有部门中当前员工薪水最高的相关信息,给出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`));

    输入描述:

    输出描述:

    dept_no
    emp_no
    salary
    d001
    10001
    88958
    d002
    10006
    43311
    d003
    10005
    94692
    d004
    10004
    74057
    d005
    10007
    88070
    d006
    10009
    95409
    此题思路如下:
    1、先用INNER JOIN连接两张表,限制条件是两张表的emp_no相同,即d.emp_no = s.emp_no;
    2、选取每个员工当前的工资水平,用d.to_date = ‘9999-01-01’ AND s.to_date = ‘9999-01-01’作条件限制,因为此表中每条最新记录的 to_date 都用 9999-01-01 表示;
    3、用GROUP BY d.dept_no将每个部门分为一组,用MAX()函数选取每组中工资最高者;
    4、将salaries用s代替,dept_emp用d代替,最后将MAX(s.salary)用salary代替后输出。
    select d.dept_no,d.emp_no,max(s.salary) as salary
    from dept_emp d inner join salaries s
    on d.emp_no = s.emp_no
    and d.to_date = ‘9999-01-01’
    and s.to_date = ‘9999-01-01’
    group by d.dept_no;
    ——————————————分割线:若存在多条最大记录—————————————-
    有同学提出疑问,如果存在多条最大记录怎么办?而 MAX 函数根据不同数据库只选择最前一条或最后一条最大记录,其余记录均被忽略。此时解法如下:
    1、创建两张表,一张为maxsalary,用于存放当前每个部门薪水的最大值;另一张为currentsalary,用于存放当前每个部门所有员工的编号和薪水;
    2、限定条件为两张表的 dept_no 和 salary 相等,这样就可以找出当前每个部门所有薪水等于最大值的员工的相关信息了;
    3、最后记得根据 currentsalary.dept_no 升序排列,输出与参考答案相同的记录表。
    4、以下代码虽然很长,仔细一看都是基于上面的基础解法变化而来的,中心思想就是绕开 MAX 的特性限制,运用比较的方法选出多个相同的最大值。
    SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary
    FROM
    //创建maxsalary表用于存放当前每个部门薪水的最大值
    (SELECT d.dept_no, MAX(s.salary) AS salary
    FROM salaries AS s INNER JOIN dept_emp As d
    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) AS maxsalary,
    //创建currentsalary表用于存放当前每个部门所有员工的编号和薪水
    (SELECT d.dept_no, s.emp_no, s.salary
    FROM salaries AS s INNER JOIN dept_emp As d
    ON d.emp_no = s.emp_no
    WHERE d.to_date = ‘9999-01-01’ AND s.to_date = ‘9999-01-01’
    ) AS currentsalary
    //限定条件为两表的dept_no和salary均相等
    WHERE currentsalary.dept_no = maxsalary.dept_no
    AND currentsalary.salary = maxsalary.salary
    //最后以currentsalary.dept_no排序输出符合要求的记录表
    ORDER BY currentsalary.dept_no

    题目描述

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

    输入描述:

    输出描述:

    title
    t
    Assistant Engineer
    2
    Engineer
    4
    省略
    省略
    Staff
    3
    select title,count(title) as t
    from titles
    group by title
    having t >=2;
    此题应注意以下三点:
    1、用COUNT()函数和GROUP BY语句可以统计同一title值的记录条数
    2、根据题意,输出每个title的个数为t,故用AS语句将COUNT(title)的值转换为t
    3、由于WHERE后不可跟COUNT()函数,故用HAVING语句来限定t>=2的条件

    题目描述

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

    输入描述:

    输出描述:

    title
    t
    Assistant Engineer
    2
    Engineer
    3
    省略
    省略
    Staff
    3
    select title,count(distinct emp_no) as t
    from titles
    group by title
    having t >=2;
    此题应注意以下三点:
    1、先用GROUP BY title将表格以title分组,再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数
    2、根据题意,输出每个title的个数为t,故用AS语句将COUNT(DISTINCT emp_no)的值转换为t
    3、由于WHERE后不可跟COUNT()函数,故用HAVING语句来限定t>=2的条件

    题目描述

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

    输入描述:

    输出描述:

    emp_no
    birth_date
    first_name
    last_name
    gender
    hire_date
    10011
    1953-11-07
    Mary
    Sluis
    F
    1990-01-22
    10005
    1955-01-21
    Kyoichi
    Maliniak
    M
    1989-09-12
    10007
    1957-05-23
    Tzvetan
    Zielinski
    F
    1989-02-10
    10003
    1959-12-03
    Parto
    Bamford
    M
    1986-08-28
    10001
    1953-09-02
    Georgi
    Facello
    M
    1986-06-26
    10009
    1952-04-19
    Sumant
    Peac
    F
    1985-02-18
    select * from employees
    where emp_no % 2 =1
    and last_name != ‘Mary’
    order by hire_date desc;
    三点需要注意:
    1、员工号为奇数,则emp_no取余应为1
    2、last_name不为Mary,用‘!=’表示
    3.根据hire_date逆序排列,用desc
     

    题目描述

    统计出当前各个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);

    输入描述:

    输出描述:

    title
    avg
    Engineer
    94409.0
    Senior Engineer
    69009.2
    Senior Staff
    91381.0
    Staff
    72527.0
    SELECT t.title AS title, AVG(s.salary) AS avg
    FROM titles AS t
    INNER JOIN (SELECT * FROM salaries GROUP BY emp_no HAVING to_date = MAX(to_date)) AS s
    ON s.emp_no = t.emp_no AND s.to_date = t.to_date AND s.to_date = ‘9999-01-01’
    GROUP BY t.title
    1、先算出当前员工的当前工资表,即由每个emp_no的to_date为最大时的记录构成的表
    2、再将此表与titles连接,限定条件为emp_no相等且to_date相等,即得当前员工的工资表
    3、最后以title分组,利用AVG()函数计算每个title下的平均工资

    题目描述

    获取当前(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`));

    输入描述:

    输出描述:

    emp_no
    salary
    10009
    94409
    select emp_no, salary
    from salaries
    where to_date = ‘9999-01-01’
    and salary =
    (select salary
    from salaries
    group by salary
    order by salary
    desc limit 1,1)
    避免了2个问题:
    (1) 首先这样可以解决多个人工资相同的问题;
    (2) 另外,筛选出第二多的工资时要注意distinct salary,否则不能选出第二多的工资。
     

    题目描述

    查找当前薪水(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`));

    输入描述:

    输出描述:

    emp_no
    salary
    last_name
    first_name
    10009
    94409
    Peac
    Sumant
    SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
    FROM employees AS e INNER JOIN salaries AS s
    ON e.emp_no = s.emp_no
    WHERE s.to_date = ‘9999-01-01’
    AND s.salary NOT IN
    (SELECT MAX(salary)
    FROM salaries
    WHERE to_date = ‘9999-01-01’)
     
    本题做法很多,主要思想为多层SELECT嵌套与MAX()函数结合
    1、先利用MAX()函数找出salaries中当前薪水最高者,即SELECT MAX(salary) FROM salaries WHERE to_date = ‘9999-01-01’
    2、再利用INNER JOIN连接employees与salaries表,限定条件为【同一员工】e.emp_no = s.emp_no、【当前】s.to_date = ‘9999-01-01’与【非薪水最高】s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = ‘9999-01-01’)
    3、在以上限制条件下找薪水最高者,即为所有员工薪水的次高者
     

    题目描述

    查找所有员工的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`));

    输入描述:

    输出描述:

    last_name
    first_name
    dept_name
    Facello
    Georgi
    Marketing
    省略
    省略
    省略
    Sluis
    Mary
    NULL
    SELECT em.last_name, em.first_name, dp.dept_name
    FROM (employees AS em LEFT JOIN dept_emp AS de ON em.emp_no = de.emp_no)
    LEFT JOIN departments AS dp ON de.dept_no = dp.dept_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,也包括暂时没有分配部门的员工
     

     

    题目描述

    查找员工编号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`));

    输入描述:

    输出描述:

    growth
    28841
    本题严谨的思路如下:
    1、先分别找到emp_no=10001的员工的第一次工资记录与最后一次工资记录
    2、再将最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅,最后用别名growth代替
    SELECT (
    (SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY from_date DESC LIMIT 1) –
    (SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY from_date ASC LIMIT 1)
    ) AS growth
     
    本题的另一种解法也能通过测试,但实际上不严谨,只有在员工最后一条工资记录为最大值时成立,如果最后一次的工资调整为降薪,则此思路通不过。具体思路如下:直接找到emp_no=10001的员工的工资记录,将其最大工资减去最小工资得到涨幅
     
    SELECT (MAX(salary)-MIN(salary)) AS growth 
    FROM salaries WHERE emp_no = ‘10001’
     

    本站所有文章均由网友分享,仅用于参考学习用,请勿直接转载,如有侵权,请联系网站客服删除相关文章。若由于商用引起版权纠纷,一切责任均由使用者承担
    极客文库 » 牛客网SQL练习题总结(一)

    常见问题FAQ

    如果资源链接失效了怎么办?
    本站用户分享的所有资源都有自动备份机制,如果资源链接失效,请联系本站客服QQ:2580505920更新资源地址。
    如果用户分享的资源与描述不符怎么办?
    可以联系客服QQ:2580505920,如果要求合理可以安排退款或者退赞助积分。
    如何分享个人资源获取赞助积分或其他奖励?
    本站用户可以分享自己的资源,但是必须保证资源没有侵权行为。点击个人中心,根据操作填写并上传即可。资源所获收益完全归属上传者,每周可申请提现一次。
    如果您发现了本资源有侵权行为怎么办?
    及时联系客服QQ:2580505920,核实予以删除。

    参与讨论

    • 211会员总数(位)
    • 3737资源总数(个)
    • 0本周发布(个)
    • 0 今日发布(个)
    • 869稳定运行(天)

    欢迎加入「极客文库」,成为原创作者从这里开始!

    立即加入 了解更多
    成为赞助用户享有更多特权立即升级