SQL练习-牛客网

我会带着你远行 2022-10-23 15:23 164阅读 0赞

## 牛客网中SQL练习题： ##

[牛客网SQL练习][SQL]

### **1.找出Employees中最晚入职的员工的所有信息：** ###

-- 题解：
    -- 方式1： 此种情况当同一天有多人入职时，只能查出一条数据。
    select * from employees order by hire_date desc limit 0 ,1;
    -- 方式2：当同一天有多人同时入职能查回多条。
    select * 
    from 
    employees 
    where hire_date = 
    (select max(hire_date) from employees);

### **2.查找出employees中入职时间倒数第三的员工的信息。** ###

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

### **3.查找各个部门领导薪水详情以及其对应部门编号dept\_no，输出结果以salaries.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`));
    
    CREATE TABLE `dept_manager` (
    `dept_no` char(4) NOT NULL,
    `emp_no` int(11) NOT NULL,
    `to_date` date NOT NULL,
    PRIMARY KEY (`emp_no`,`dept_no`));
    -- 解题
    select  
    s.emp_no,s.salary,s.from_date,s.to_date,d.dept_no
    from salaries s , dept_manager d
    where s.emp_no =d.emp_no
    and s.to_date='9999-01-01'
    and d.to_date='9999-01-01'
    order by s.emp_no;

### **4.查找所有已经分配部门的员工的last\_name和first\_name以及dept\_no，未分配的部门的员工不显示** ###

**员工表**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU1OTI4Mg_size_16_color_FFFFFF_t_70]  
**部门表**  
![部门表][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU1OTI4Mg_size_16_color_FFFFFF_t_70 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`));
    
    -- 方式1：
    select 
    e.last_name,e.first_name, d.dept_no
    from employees e , dept_emp d
    where e.emp_no =d.emp_no;
    
    -- 方式2：
    select 
    e.last_name,e.first_name, d.dept_no
    from employees e inner join  dept_emp d
    on e.emp_no = d.emp_no;

### **5.查找所有已经分配部门的员工的last\_name和first\_name以及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 `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`));
    
    --左连接：查出左表中所有数据，包括某字段为空的情况。
    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;

### **7.查找薪水记录超过15次的员工号emp\_no以及其对应的记录次数t** ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU1OTI4Mg_size_16_color_FFFFFF_t_70 2]

-- 分组和聚合
    select emp_no, count(emp_no) t
    from salaries
    group by emp_no
    having t>15;

### **10.找出所有非部门领导的员工emp\_no** ###

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

### **11.获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示** ###

select e.emp_no, m.emp_no manager
    from dept_emp e, dept_manager m
    where e.dept_no=m.dept_no
    and e.emp_no!=m.emp_no

## 不错的题： ##

### **查询语文成绩和平均成绩都大于60分的所有姓名，** ###

SELECT
    	a. NAME
    FROM
    	TB3,
    	(   SELECT
    			avg(score) avgscore,
    			NAME
    		FROM
    			TB3
    		GROUP BY
    			NAME
    		HAVING
    			AVG(score) > 60
    	) a
    WHERE
    	a. NAME = TB3. NAME
    AND TB3. SUBJECT = '语文'
    AND TB3.score > 60;

> 记录下：此方法不知是否为最优，  
> 思路：读题、拆解，将一个问题拆解为多个小的问题去分别处理，然后找出各个小问题之间的联系，再串起来。问题就搞定了。

[SQL]: https://www.nowcoder.com/ta/sql
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU1OTI4Mg_size_16_color_FFFFFF_t_70]: /images/20221023/940dd0f1ce424bc2b9c6a6ceadbd6280.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU1OTI4Mg_size_16_color_FFFFFF_t_70 1]: /images/20221023/b9f29c34702549fda49e76797e5d777b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU1OTI4Mg_size_16_color_FFFFFF_t_70 2]: /images/20221023/0b61c2ffad014e00b0b56560845700e7.png