SQL学习，刷题二

xxxxrr

已于 2022-01-24 20:38:48 修改

阅读量1.1k

点赞数

文章标签： sql sqlite 数据库

于 2022-01-23 17:00:32 首次发布

本文链接：https://blog.csdn.net/xxxxrr/article/details/122652227

版权

按照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));
输出格式:
在这里插入图片描述
本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。可参考：
http://www.sqlite.org/lang_aggfunc.html#groupconcat
http://blog.csdn.net/langzxz/article/details/16807859

SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp GROUP BY dept_no

将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)
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 K
输出格式:
name
Facello’Georgi
Simmel’Bezale
在本题所用的SQLite数据库中，只支持用连接符号"||"来连接字符串，不支持用函数连接

方式一sqlite
select concat(last_name,'\'',first_name) from employees

方式二mysql
select concat(last_name,"'",first_name) from employees

有一个员工表employees简况如下
在这里插入图片描述
请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列，以上例子查询结果如下:

在这里插入图片描述

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

在这里插入图片描述
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了JS岗位并且考了9999分
请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入)
在这里插入图片描述
注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)

SELECT job,round(avg(score),3) avg FROM grade GROUP by job order by avg(score) desc

1.首先题目肯定是按工作分组了，那么语句肯定是如下的:
select xxx from grade
group by job
然后知道分组之后的分数和是
sum(score)
求平均数就是
round(sum(score)*1.0/count(id),3) as avg
联合再按avg降序排序为:
复制代码
select job,round(sum(score)*1.0/count(id),3) as avg from grade
group by job order by avg desc;

对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
在这里插入图片描述
因为题目判定系统使用的是sqlite3，所以必须按sqlite3的写法来做

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

如果是mysql，那么把or去掉，像下面这样：

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

有一个员工employees表简况如下
在这里插入图片描述
请你查找employees里最晚入职员工的所有信息，以上例子输出如下:

其他top和limit方法有牵强之处，与给定数据集有关,最晚入职的当天未必就一个人，也许有多人，使用排序并限制得只能取得指定数量的结果

select * from employees
where hire_date =
(select max(hire_date) from employees)

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00
MySQL中default后不需要加括号

alter table actor add create_date datetime not null default '2020-10-01 00:00:00'  
after last_update

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天。有一个登录(login)记录表，简况如下
在这里插入图片描述
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第4行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
请你写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序，上面的例子查询结果如下
在这里插入图片描述
先按照user_id分组，并选出每个组最大的date的情况。后面再排序

select max(date)  as d from login 
  group by user_id
order by user_id;

有一个员工表，employees简况如下
在这里插入图片描述
有一个部门表，dept_emp简况如下

请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工，以上例子如下

注意：
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据

SELECT ep.last_name, ep.first_name, dp.dept_no 
FROM employees ep 
LEFT JOIN dept_emp dp
ON ep.emp_no = dp.emp_no

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

有一个员工表，employees简况如下
在这里插入图片描述
有一个部门表，dept_emp简况如下

请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示，以上例子如下

答案是按employees表中顺序输出的，所以使用内连接查询时，必须将employees表放在前面。
select last_name,first_name,dept_no from employees,dept_emp where dept_emp.emp_no = employees.emp_no;

使用左连接查询时，employees中没有分配部门的员工（没有被记录在dept_emp表）dept_no字段被自动取NULL然后被输出，所以应当剔除（复合条件连接查询）。
select last_name,first_name,dept_no
from employees left join dept_emp
on employees.emp_no = dept_emp.emp_no
where dept_emp.dept_no<>’’;

有一个薪水表salaries简况如下
在这里插入图片描述
请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，
若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序

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

避免了2个问题：
(1) 首先这样可以解决多个人工资相同的问题；
(2) 另外，筛选出第二多的工资时要注意distinct salary，否则不能选出第二多的工资

1）首先要考虑到同一薪水有多人，假如薪水最高的有3人；薪水第二高的有2人，那么
select salary from salaries order by salary desc limit 1,1
通过上述语句limit 1,1 查出的仍然是薪水最高的第二人，所以要对salary分组查询薪水第二高的薪水（用distinct 或 group by）
select salary from salaries group by salary order by salary desc limit 1,1
（2）正确答案为：

select emp_no, salary
from salaries
where salary = (
    select salary from salaries 
    group by salary
    order by salary desc limit 1,1
) 
and to_date = '9999-01-01'

有一个薪水表，salaries简况如下
在这里插入图片描述
请你查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t，以上例子输出如下:

1.下一条salary高于本条才算涨幅，大部分答案都是只要出现了一条记录就算一次涨幅
2.个人比较严格的话，salary高于前一条才算涨幅，对于每条记录查询它上一条记录判断生成临时表，在做统计就好了

SELECT tmp.emp_no,
       Count(c) AS t
FROM   (SELECT emp_no,
               IFNULL(s1.salary > (SELECT s2.salary
                            FROM   salaries AS s2
                            WHERE  s1.from_date > s2.from_date
                            and s1.emp_no  = s2.emp_no
                            ORDER  BY s2.from_date DESC
                            LIMIT  1),1) AS c,
               s1.from_date
        FROM   salaries AS s1) tmp
GROUP  BY tmp.emp_no
HAVING Count(*) > 15;

有一个员工表employees简况如下
在这里插入图片描述
有一个部门领导表dept_manager简况如下

请你找出所有非部门领导的员工emp_no，以上例子输出:

方法一：使用NOT IN选出在employees但不在dept_manager中的emp_no记录

SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)

方法二：先使用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

针对如下表actor结构创建索引：
(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，
mysql支持ALTER TABLE创建索引
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
两种方法。语法记不住啊啊啊啊

create (unique) index 索引名 on 表名（列名

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

alter table 表名 add (unique) index 索引名（列名）

alter table actor add unique index uniq_idx_firstname(first_name);
alter table actor add index idx_lastname(last_name);

在牛客实习广场有很多公司开放职位给同学们投递，同学投递完就会把简历信息存到数据库里。
现在有简历信息表(resume_info)，部分信息简况如下
在这里插入图片描述
第1行表示，在2025年1月2号，C++岗位收到了53封简历
最后1行表示，在2026年1月4号，Java岗位收到了230封简历
请你写出SQL语句查询在2025年内投递简历的岗位和数量，并且按数量降序排序，以上例子查询结果如下:
在这里插入图片描述
筛选出2025年即可，可以使用

like '2025%'
或
year(date) = 2025
或
between '2025-01-01' and '2025-12-31'
或
date >= '2025-01-01' and date <= '2025-12-31'

select job, sum(num) cnt
from resume_info
where YEAR(date) = '2025'
group by job
order by cnt desc

现有电影信息表film，包含以下字段：
在这里插入图片描述

解题思路是运用 LEFT JOIN 连接两表，用 IS NULL 语句限定条件：
1、用 LEFT JOIN 连接 film 和 film_category，限定条件为 f.film_id = fc.film_id，即连接电影id和电影分类id，如果电影没有分类，则电影分类id显示null
2、再用 WHERE 来限定条件 fc.category_id IS NULL 选出没分类的电影
/** 注意：最后一句若写成 ON f.film_id = fc.film_id AND fc.category_id IS NULL，则意义变成左连接两表 film_id 相同的记录，且 fc.category 的值为 null，由于 fc.category的值不为null，即（f.film_id = fc.film_id AND fc.category_id IS NULL）恒为FALSE，所以左连接后只显示film表的数据，而 film_category 表的数据全为 null**/

SELECT f.film_id, f.title FROM film f LEFT JOIN film_category fc
ON f.film_id = fc.film_id WHERE fc.category_id IS NULL

select f.film_id,f.title from (film f
left join film_category fc on fc.film_id=f.film_id) as cc
where cc.category_id is null

现有员工表employees如下
在这里插入图片描述
请将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分。
(注：sqllite,字符串拼接为 || 符号，不支持concat函数，mysql支持concat函数)
MySQL、SQL Server、Oracle等数据库支持CONCAT方法，
而本题所用的SQLite数据库只支持用连接符号"||"来连接字符串

CONCAT方法：

select CONCAT(CONCAT(last_name," "),first_name) as name  from employees
或者
select CONCAT(last_name," "，first_name) as name  from employees

本题中使用：
select last_name||" "||first_name as name  from employees

有一个员工employees表简况如下
在这里插入图片描述
请你查找employees里入职员工时间排名倒数第三的员工所有信息，以上例子输出如下:

注意：可能会存在同一个日期入职的员工，所以入职员工时间排名倒数第三的员工可能不止一个。
考虑到入职日期可能会有多个重复，最合理的SQL语句应为:

SELECT * FROM employees 
WHERE hire_date = (
SELECT DISTINCT hire_date FROM employees 
ORDER BY hire_date DESC limit 2,1
);

有员工薪资简表salaries如下：
emp_no指员工编号；
salary指薪资；
from_date指该薪资的开始日期；
to_date指该薪资的结束日期
在这里插入图片描述
请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工，不考虑获取的奖金的类型)。
以上示例更新后的结果salaries为

思路：先选择符合条件的emp_no，在筛选to_date，最后将符合的字段进行更新。推荐使用内连接查询，速度会快一些。
考点：更新语句的使用，sql里可以使用基本运算（如加减乘除）
方法一：内连接

update salaries T join emp_bonus T2 on T2.emp_no = T.emp_no
set salary = salary * 1.1
where to_date = '9999-01-01'

方法二：子查询

update salaries
set salary = salary * 1.1
where emp_no in (
    select emp_no from emp_bonus
) and to_date = '9999-01-01'

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005，使用强制索引。

SELECT * FROM salaries FORCE INDEX(idx_emp_no) WHERE emp_no = 10005;

按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的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));
输出格式:
在这里插入图片描述
解题思路：
①本题关键在于把sum聚合函数作为窗口函数使用，所有聚合函数都能用做窗口函数，其语法和专用窗口函数完全相同。
sum(<汇总列>) over(<排序列>) as 别名；
②光看题目“前两个员工的salary累计和”不是很好理解，结合输出格式可以理解为running_total列是逐个员工的工资的累计和，每一行的工资都是前面所有行的工资总计。
③这有一个小bug，题目没有限定时间为当前，而按照输出格式来看和通过情况来看，只有限定时间为当前’9999-01-01’才能符合输出格式，才能通过，一开始考虑用员工分组，但是员工分组得到的结果并非题目本意，必须限定时间为当前

select emp_no,salary,
sum(salary) over(order by emp_no) as running_total
from salaries
where to_date= '9999-01-01';

使用含有关键字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));

/* 1. EXISTS */
SELECT *
FROM employees
WHERE NOT EXISTS (SELECT emp_no
FROM dept_emp
WHERE employees.emp_no = dept_emp.emp_no);

/* 2. IN */
SELECT *
FROM employees
WHERE emp_no NOT IN (SELECT emp_no
FROM dept_emp);

/*
1.什么时候用EXISTS，什么时候用IN？
主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：
当主表比从表大时，IN查询的效率较高；
当从表比主表大时，EXISTS查询的效率较高；
原因如下：
in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次
*/

构造一个触发器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
);
后台会往employees_test插入一条数据:
INSERT INTO employees_test (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, ‘Paul’, 32, ‘California’, 20000.00 );
然后从audit里面使用查询语句:
select * from audit;

初识触发器
语言运行环境：Sql(mysql 8.0)
1.创建触发器：create trigger 触发器名称
2.用after指定触发条件：在表1中每插入一行 after insert on 表1 for each row
(注意insert后用on，for each row不能少否则会报错)
3.触发的指令写在begin和end之间，分号写在指令后，end后不用加分号

create trigger audit_log after insert on employees_test
for each row
begin
insert into audit values(NEW.ID,NEW.NAME);
end

在牛客实习广场有很多公司开放职位给同学们投递，同学投递完就会把简历信息存到数据库里。
现在有简历信息表(resume_info)，部分信息简况如下
在这里插入图片描述
第1行表示，在2025年1月2号，C++岗位收到了53封简历
最后1行表示，在2026年2月6号，C++岗位收到了231封简历
请你写出SQL语句查询在2025年内投递简历的每个岗位，每一个月内收到简历的数量，并且按先按月份降序排序，再按简历数目降序排序，以上例子查询结果如下:
在这里插入图片描述 ```sql
select job,
date_format(date,"%Y-%m") as mon,
sum(num) as cnt
from resume_info
where left(date,4)=“2025”
group by job,mon
order by mon desc, cnt desc`

mysql常用函数：
EXTRACT(type FROM d) 从日期 d 中获取指定的值，type 指定返回的值。type可为year,week,moth等等
DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d. f的表达式如'%Y-%m-%d %r' 是2011-11-11 11:11:11 AM。 '%Y-%m‘是2011-11.
LEFT(s,n) 返回字符串 s 的前 n 个字符

参考资料来源：https://www.runoob.com/mysql/mysql-functions.html
删除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');

删除后titles_test表为(注：最后会select * from titles_test表来对比结果)
![在这里插入图片描述](https://img-blog.csdnimg.cn/c5fbcc1731324d20ba635517fe5feced.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAeHh4eHJy,size_20,color_FFFFFF,t_70,g_se,x_16)
delete from titles_test where id not in(
select min(id) from titles_test group by emp_no)
在mysql中这样写不行的，mysql不允许用一个表中查询出来的数据更新同一个表😂智熄的操作
改成

```sql
delete from titles_test where id not in(
    select min_id from(
select min(id) as min_id from titles_test group by emp_no) a)

在更新操作之前再查一下就好了，对mysql来说，这道题给个中等难度都不为过啊😀
有一个员工表dept_emp简况如下
在这里插入图片描述
第一行表示为员工编号为10001的部门是d001部门。

有一个部门经理表dept_manager简况如下
在这里插入图片描述
第一行表示为d001部门的经理是编号为10002的员工。

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，以上例子如下
在这里插入图片描述

本题应注意以下三点：
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 代替后输出

SELECT de.emp_no, dm.emp_no AS manager_no 
FROM dept_emp AS de INNER JOIN dept_manager AS dm
ON de.dept_no = dm.dept_no 
WHERE dm.to_date = '9999-01-01' AND de.to_date = '9999-01-01' AND de.emp_no <> dm.emp_no

在牛客刷题有一个通过题目个数的(passing_number)表，id是主键，简化如下:
在这里插入图片描述
第1行表示id为1的用户通过了4个题目;
…
第6行表示id为6的用户通过了4个题目;
请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列，数据如下

方法一.首先要通过题目降序，id升序，那么sql应该为:
select a.id,a.number
from passing_number a order by a.number desc, a.id asc;
要得到通过题目的排名，比如你通过了8分,，你同学a也通过了8分，找到大于等于你的成绩，一个9分，一个8分，一个8分，去重复，就一个9，一个8，count一下总数，第2名，如果有三个同学通过了7个呢，同理，9，8，8，7，7，7 后面比这个少的，已经死在了筛选条件，去重，9，8，7，count=3，所以sql为:

select count(distinct b.number) from passing_number b where b.number>=X
联立为:
复制代码

select a.id,a.number,
(select count(distinct b.number) from passing_number b where b.number>=a.number )
from passing_number a order by a.number desc, a.id asc;
方法二.使用mysql8.0(我强烈要求升级的mysql8.0，以便大家使用窗口函数)或者sqlite自带的dense_rank()函数，由于rank是mysql8.0的关键字，所以不能直接使用rank，要用" ` "号包裹，或者使用t_rank

 SELECT
    id,number,
    dense_rank ( ) over ( ORDER BY number DESC ) `rank` 
FROM
    passing_number 
ORDER BY
    `rank`,
    id

xxxxrr

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL学习，刷题二

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employeesCREATE 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));输出格式:本题要用到SQLite的聚合函数group_concat(
复制链接

扫一扫