六、MYSQL数据练习题（三）

mysql练习在牛客网（https://www.nowcoder.com/）上进行

2、获取每个部门中当前员工薪水最高的相关信息

描述

有一个员工表dept_emp简况如下:

有一个薪水表salaries简况如下:

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列，以上例子输出如下:

（注意: Mysql与Sqlite select 非聚合列的结果可能不一样)

解答：

2021-06-16 周三打卡

select * from (SELECT dept_no, dept_emp.emp_no,salary FROM  dept_emp left JOIN salaries ON salaries.emp_no = dept_emp.emp_no ORDER BY salary desc LIMIT 9999999)as a GROUP BY a.dept_no ORDER BY a.dept_no asc;

3、查找employees表

有一个员工表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`));

请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列，以上例子查询结果如下:

2021-06-17 10:03:07 周四打卡

解答：

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

4、统计出当前各个title类型对应的员工当前薪水对应的平均工资

描述

有一个员工职称表titles简况如下:

有一个薪水表salaries简况如下:

建表语句如下:

CREATE TABLE titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT 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`));

请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序，以上例子输出如下:

解答：

2021-06-18　　09:13:32　　周五　　打卡

select a.title,avg(a.salary) from (select titles.emp_no,title,salary from titles join salaries on salaries.emp_no = titles.emp_no) as a group by a.title;

5、获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

描述

有一个薪水表salaries简况如下:

请你获取薪水第二多的员工的emp_no以及其对应的薪水salary

示例：

drop table if exists  `salaries` ; 
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`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');

解答：

2021-06-21　　09:59:18　　周一　　打卡

select emp_no,salary from salaries where (select max(salary) as 'maxsalary' from salaries) != salary order by salary DESC limit 0,1;

6、获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

描述

有一个员工表employees简况如下:

有一个薪水表salaries简况如下:

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:

（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)

示例1

drop table if exists  `employees` ; 
drop table if exists  `salaries` ; 
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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

解答：

2021-06-22　　11:07:24　　周二　　打卡

-- 思路：
-- （1）先两表联查得到一个具有员工号emp_no和salary的信息；
-- （2）然后查出最大的salary；
-- （3）将查出的最大salary作为限定条件在（1）中排除；
-- （4）在结果（3）中查找最大的salary，此时这个最大的salary在表1中就是第二大的salary；
-- （5）将（4）结果作为限定条件在（1）中进行查询即可得到结果。

SELECT
    *
FROM
    (
        SELECT
            employees.emp_no,
            salary,
            employees.last_name,
            employees.first_name
        FROM
            employees
        LEFT JOIN salaries ON salaries.emp_no = employees.emp_no
    ) AS c
WHERE
    (
        SELECT
            MAX(a.salary)
        FROM
            (
                SELECT
                    salary
                FROM
                    employees
                LEFT JOIN salaries ON salaries.emp_no = employees.emp_no
            ) AS a
        JOIN (
            SELECT
                max(salary) AS 'maxsalary'
            FROM
                employees
            LEFT JOIN salaries ON salaries.emp_no = employees.emp_no
        ) AS b ON b.maxsalary != a.salary
    ) = c.salary;

7、SQL19 查找所有员工的last_name和first_name以及对应的dept_name

描述

有一个员工表employees简况如下:

有一个部门表departments表简况如下:

有一个，部门员工关系表dept_emp简况如下:

请你查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工，以上例子输出如下:

示例1：

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `employees` ; 
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`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO departments VALUES('d003','Human Resources');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');

解答：

2021-06-23　　10:23:17　　周三　　打卡

select last_name,first_name,dept_name from employees left join (select departments.dept_no,dept_name,dept_emp.emp_no from departments join dept_emp on dept_emp.dept_no = departments.dept_no) as a on a.emp_no = employees.emp_no;

8、SQL22 统计各个部门的工资记录数

描述

有一个部门表departments简况如下:

有一个，部门员工关系表dept_emp简况如下:

有一个薪水表salaries简况如下:

请你统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序，以上例子输出如下:

示例1

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
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`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','2001-06-22','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10003,32323,'1996-08-03','9999-01-01');

解答：

2021-06-24　　09:04:29　　周四　　打卡

select b.dept_no ,b.dept_name,count(b.salary) from (select a.*,salaries.salary from salaries right join (select departments.dept_no,departments.dept_name, dept_emp.emp_no from departments join dept_emp on dept_emp.dept_no = departments.dept_no) as a on a.emp_no = salaries.emp_no)as b group by b.dept_no order by b.dept_no ASC

9、对所有员工的薪水按照salary降序进行1-N的排名

描述

有一个薪水表salaries简况如下:

对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列且按照emp_no升序排列：

示例1

drop table if exists  `salaries` ; 
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`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,72527,'2001-12-01','9999-01-01');

解答：

2021-06-25　　09:47:32　　周五　　打卡

DENSE_RANK()函数的作用：
DENSE_RANK()是一个窗口函数，它为分区或结果集中的每一行分配排名，而排名值没有间隙。

行的等级从行前的不同等级值的数量增加1。

DENSE_RANK() 函数的语法如下：

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
在这个语法中：

首先，PARTITION BY子句将FROM子句生成的结果集划分为分区。DENSE_RANK()函数应用于每个分区。
其次，ORDER BY 子句指定DENSE_RANK()函数操作的每个分区中的行顺序。
如果分区具有两个或更多具有相同排名值的行，则将为这些行中的每一行分配相同的排名。

OVER(PARTITION BY)函数介绍：
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。 其参数：over（partition by columnname1 order by columnname2） 含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。 例如：employees表中，有两个部门的记录：department_id ＝10和20 select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。

select emp_no,salary,DENSE_RANK() OVER(order by salary DESC)  from salaries;

10、获取所有非manager员工当前的薪水情况

描述

有一个员工表employees简况如下:

有一个，部门员工关系表dept_emp简况如下:

有一个部门经理表dept_manager简况如下:

有一个薪水表salaries简况如下:

获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary，以上例子输出:

示例1

drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
drop table if exists  `employees` ; 
drop table if exists  `salaries` ; 
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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1996-08-03');
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');

解答：

2021-06-28　　13:07:26　　周一　　打卡

select  dept_no,b.emp_no,salary from dept_emp join (select emp_no from employees where emp_no not in  (select a.emp_no from dept_manager join (select dept_emp.emp_no,dept_emp.dept_no from employees join dept_emp on dept_emp.emp_no = employees.emp_no) as a on a.emp_no = dept_manager.emp_no and a.dept_no =dept_manager.dept_no))as b on b.emp_no = dept_emp.emp_no join salaries on salaries.emp_no = dept_emp.emp_no;

posted @ 2021-06-17 10:07 惜阙阅读(353) 评论(0) 收藏举报

刷新页面返回顶部

惜阙

六、MYSQL数据练习题（三）

描述

描述

描述

描述

示例1

描述

描述

示例1

描述

示例1

描述

示例1

公告