数据库SQL实战题目详解（全61题）---（21-40）部分

最新推荐文章于 2023-05-09 10:32:45 发布

浮豹

最新推荐文章于 2023-05-09 10:32:45 发布

阅读量464

点赞数 1

分类专栏： sql 文章标签： sql 数据库 sqlite

本文链接：https://blog.csdn.net/weixin_41744624/article/details/104412955

版权

sql 专栏收录该内容

22 篇文章 3 订阅

订阅专栏

题目来源：牛客网–《数据库SQL实战》
https://www.nowcoder.com/ta/sql?page=0
题目答案为博主自写已通过运行，题目难度近似于阶梯上升，可根据自身情况分部分作答。

其他题目链接：
1-20题链接：https://blog.csdn.net/weixin_41744624/article/details/104226505
41-60题链接：
https://blog.csdn.net/weixin_41744624/article/details/104413757
………………………………………………………………………………
另附可以使用的在线编程工具
sqlfiddle在线工具
网页版方便个人在闲暇时间的小题目练习
也可以从牛客网参考本答案直接作答
………………………………………………………………………………

21、

查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
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));

select a.emp_no, (b.salary - c.salary) as growth
from
    employees as a
    inner join salaries as b
    on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
    inner join salaries as c
    on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc

22、

统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum
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));

SELECT de.dept_no, dp.dept_name, COUNT(s.salary) AS sum 
FROM dept_emp AS de 
INNER JOIN salaries AS s ON de.emp_no = s.emp_no
INNER JOIN departments AS dp ON de.dept_no = dp.dept_no 
GROUP BY de.dept_no

23、

对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照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));

select  a.emp_no ,a.salary,
 
count(distinct b.salary) as rank
 
from salaries as a,salaries as b
 
where a.to_date = '9999-01-01'
 
and b.to_date ='9999-01-01'
and a.salary<= b.salary
 
group by a.emp_no
order by a.salary desc,a.emp_no asc

24、

获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’
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));

SELECT de.dept_no, s.emp_no, s.salary 
FROM employees AS e INNER JOIN salaries AS s 
ON s.emp_no = e.emp_no AND s.to_date = '9999-01-01'
INNER JOIN dept_emp AS de ON e.emp_no = de.emp_no
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date='9999-01-01')

25、

获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_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 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));

select A.emp_no ,B.emp_no as manager_no,
C.salary as emp_salary,D.salary as manager_salary 
from dept_emp A
left join dept_manager B on  A.dept_no =B.dept_no
left join salaries C on A.emp_no = C.emp_no and C.to_date = '9999-01-01'
left join salaries D on B.emp_no = D.emp_no and D.to_date = '9999-01-01'
where emp_salary > manager_salary

26、

汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
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 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);

SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM titles AS t INNER JOIN dept_emp AS de 
ON t.emp_no = de.emp_no AND de.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
INNER JOIN departments AS dp 
ON de.dept_no = dp.dept_no
GROUP BY de.dept_no, t.title

27、

给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)

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

SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no 
AND salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 
     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC

28、

film表
字段说明
film_id 电影id
title 电影名称
description 电影描述信息

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT ‘0’,
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));

category表
字段说明
category_id 电影分类id
name 电影分类名称
last_update 电影分类最后更新时间

CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, last_update timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段说明
film_id 电影id
category_id 电影分类id
last_update 电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, last_update timestamp);

查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

select c.name,count(f.film_id)as f_count from film as f  
    inner join film_category as fc  
    on f.film_id=fc.film_id  
    inner join category as c  
    on fc.category_id=c.category_id  
    where f.description like '%robot%'  
    group by c.category_id 
    having f_count>=2;

29、

film表
字段说明
film_id 电影id
title 电影名称
description 电影描述信息

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT ‘0’,
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
category表
字段说明
category_id 电影分类id
name 电影分类名称
last_update 电影分类最后更新时间

CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, last_update timestamp);

使用join查询方式找出没有分类的电影id以及名称

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

30、

film表
字段说明
film_id 电影id
title 电影名称
description 电影描述信息

CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, last_update timestamp);

使用子查询的方式找出属于Action分类的所有电影对应的title,description

select title ,description from film 
where film_id in (select film_id from film_category 
                 where category_id in (select category_id from category
                                      where name ='Action'))

31、

获取select * from employees对应的执行计划

EXPLAIN SELECT * FROM employees

32、

将employees表的所有员工的last_name和first_name拼接起来作为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));

SELECT last_name||" "||first_name AS Name FROM employees

33、

创建一个actor表，包含如下列信息
列表类型是否为NULL 含义
actor_id smallint(5) not null 主键id
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏
last_update timestamp not null 最后更新时间，默认是系统的当前时间

CREATE TABLE actor
(
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime'))
)

34、

对于表actor批量插入如下数据
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,‘localtime’)))

actor_id first_name last_name last_update
1 PENELOPE GUINESS 2006-02-15 12:34:33
2 NICK WAHLBERG 2006-02-15 12:34:33

insert into  actor
values
('1', 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
('2', 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')

35、

对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,‘localtime’)))

actor_id first_name last_name last_update
‘3’ ‘ED’ ‘CHASE’ ‘2006-02-15 12:34:33’

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

36、

对于如下表actor，其对应的数据为:
actor_id first_name last_name last_update

1 PENELOPE GUINESS 2006-02-15 12:34:33
2 NICK WAHLBERG 2006-02-15 12:34:33

创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：
列表类型是否为NULL 含义
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏

create table actor_name(
first_name varchar(45) not null ,
last_name varchar(45) not null);
insert into actor_name
select first_name,last_name from actor

37、

针对如下表actor结构创建索引：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,‘localtime’)))
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

38、

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,‘localtime’)))

create view actor_name_view as
select first_name as first_name_v, last_name as last_name_v from actor

39、

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
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 index idx_emp_no on salaries(emp_no);

SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = '10005'

40、

存在actor表，包含如下列信息：
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,‘localtime’)));
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000 00:00:00’

alter table actor add
create_date datetime not null default'0000-00-00 00:00:00'

浮豹

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
数据库SQL实战题目详解（全61题）---（21-40）部分

题目来源：牛客网–《数据库SQL实战》https://www.nowcoder.com/ta/sql?page=0题目答案为博主自写已通过运行，题目难度近似于阶梯上升，可根据自身情况分部分作答。其他题目链接：1-20题链接：https://blog.csdn.net/weixin_41744624/article/details/104226505……………………………………………………...
复制链接

扫一扫

专栏目录