SQL在线编程练习(08-04)【2】

最新推荐文章于 2023-08-05 23:37:23 发布

Jasminexjf

最新推荐文章于 2023-08-05 23:37:23 发布

阅读量261

点赞数

分类专栏：找工作大全 SQL学习

本文链接：https://blog.csdn.net/Jasminexjf/article/details/98473333

版权

找工作大全同时被 2 个专栏收录

60 篇文章 14 订阅

订阅专栏

SQL学习

11 篇文章 0 订阅

订阅专栏

1. 分页查询employees表，每5行一页，返回第2页的数据

题目描述

分页查询employees表，每5行一页，返回第2页的数据
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`));

解析：

根据题意，每行5页，返回第2页的数据，即返回第6~10条记录，以下有两种方法可以解决：

方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。

1	`SELECT * FROM employees LIMIT` `5` `OFFSET` `5`

方法二：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。

2	`SELECT * FROM employees LIMIT` `5,5`

2.获取所有员工的emp_no

题目描述

获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示
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`));
输出格式:

e.emp_no	dept_no	btype	recevied
10001	d001	1	2010-01-01
10002	d001	2	2010-10-01
10003	d004	3	2011-12-03
10004	d004	1	2010-01-01
10005	d003
10006	d002
10007	d005
10008	d005
10009	d006
10010	d005
10010	d006

SELECT de.emp_no, de.dept_no, eb.btype, eb.recevied 
FROM dept_emp AS de 
LEFT JOIN emp_bonus AS eb 
ON de.emp_no = eb.emp_no

3. 使用含有关键字exists查找未分配具体部门的员工的所有信息。

题目描述

使用含有关键字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`));
输出格式:

emp_no	birth_date	first_name	last_name	gender	hire_date

本题用 EXISTS 关键字的方法如下：意为在 employees 中挑选出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录，也就是当 employees.emp_no=10011的时候。反之，把NOT去掉，则输出 employees.emp_no=10001~10010时的记录。

1 2	`SELECT * FROM employees WHERE NOT EXISTS` `(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)`

由于 OJ系统没有限制我们只能使用 EXISTS 关键字，因此还能用 NOT IN 关键字替换，即在employees 中选出 dept_emp 中没有的 emp_no。

1	`SELECT * FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_emp)`

SELECT *
FROM employees
WHERE emp_no NOT IN (
    SELECT emp_no
    FROM dept_emp 
    )
# 用exist的方法：
SELECT * 
FROM employees
WHERE NOT EXISTS (
    SELECT emp_no
    FROM dept_emp
    WHERE emp_no = employees.emp_no)

4. 获取employees中的行数据，且这些行也存在于emp_v中

题目描述

存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
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_v中。注意不能使用intersect关键字。

SELECT * 
FROM employees
WHERE emp_no > 10005

因为视图emp_v就是按照这样的条件从employees中获取的数据。

5. 获取有奖金的员工相关信息。

题目描述

获取有奖金的员工相关信息。
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`));
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not 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`));
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。当前薪水表示to_date='9999-01-01'
输出格式:

emp_no	first_name	last_name	btype	salary	bonus
10001	Georgi	Facello	1	88958	8895.8
10002	Bezalel	Simmel	2	72527	14505.4
10003	Parto	Bamford	3	43311	12993.3

注意的是：最后的一列bonus是派生列，即通过btype和salary得到的bonus.另外一点就是：我们在JOIN salary表时，需要进行时间的筛选。

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(s.salary * b.btype / 10.0) AS bonus
FROM employees AS e 
INNER JOIN emp_bonus AS b 
ON e.emp_no = b.emp_no
INNER JOIN salaries AS s
ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

6. 统计salary的累计和running_total

题目描述

按照salary的累计和running_total，其中running_total为前面员工的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`));
输出格式:

emp_no	salary	running_total
10001	88958	88958
10002	72527	161485
10003	43311	204796
10004	74057	278853
10005	94692	373545
10006	43311	416856
10007	88070	504926
10009	95409	600335
10010	94409	694744
10011	25828	720572

分析：输出格式首先是按照额mp_no进行降序排列，然后running_total是emp_no小于等于当前emp_no的所有salary的累加

SELECT a.emp_no,a.salary,
(SELECT SUM(salary) FROM salaries AS s WHERE s.emp_no <= a.emp_no AND s.to_date='9999-01-01')AS running_total
FROM salaries AS a
WHERE a.to_date='9999-01-01'
ORDER BY a.emp_no;

7. 对于employees表中，给出奇数行的first_name (仅仅返回奇数行的first_name)

题目描述

对于employees表中，给出奇数行的first_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`));
输出格式:

first_name
Georgi
Chirstian
Anneke
Tzvetan
Saniya

本题目叙述有问题，应该为：【对于employees表，在对first_name进行排名后，选出奇数排名对应的first_name】。

分析：

1、本题用到了三层 SELECT 查询，为了便于理解，采用缩进方式分层，且最外层对应e1，最内层对应e3；

2、在e3层中，采用 COUNT() 函数对 e2.first_name 进行排名标号，即在给定 e2.first_name的情况下，不大于 e2.first_name 的 e3.first_name 的个数有多少，该个数刚好与 e2.first_name 的排名标号匹配，且将该值命名为 rowid；

/*注意：排名标号后并未排序，即[Bob, Carter, Amy]的排名是[2,3,1]，选取奇数排名后输出[Carter, Amy]，所以可见参考答案中的first_name并未按字母大小排序*/

3、在e1层中，直接在限定条件 e1.rowid % 2 = 1 下，代表奇数行的 rowid，选取对应的 e1.first_name；

4、e2层则相当于连接e1层（选取表示层）与e3层（标号层）的桥梁。

SELECT e1.first_name FROM 
  (SELECT e2.first_name, 
    (SELECT COUNT(*) FROM employees AS e3
     WHERE e3.first_name <= e2.first_name)
   AS reivod FROM employees AS e2)AS e1
WHERE e1.reivod % 2 = 1

8. 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

题目描述

查找排除当前最大、最小salary之后的员工的平均工资avg_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`));
输出格式:

avg_salary
69462.5555555556

SELECT AVG(s.salary) AS avg_salary
FROM salaries AS s
WHERE s.to_date = '9999-01-01'
AND s.salary NOT IN (SELECT MAX(s1.salary) FROM salaries AS s1)
AND s.salary NOT IN (SELECT MIN(s2.salary) FROM salaries AS s2)

注意：min()和max()都是聚合函数，是对结果集中的列进行操作而不是对单个记录进行操作；得到最大值和最小值的筛选用法。

9. 按照dept_no进行汇总

算法知识视频讲解

题目描述

按照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`));
输出格式:

dept_no	employees
d001	10001,10002
d002	10006
d003	10005
d004	10003,10004
d005	10007,10008,10010

SELECT de.dept_no, GROUP_CONCAT(de.emp_no)AS employees
FROM dept_emp AS de
GROUP BY de.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

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

题目描述

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

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

SELECT title, description
FROM film
WHERE film_id IN 
  (SELECT film_id
   FROM film_category
   WHERE category_id = 
    (SELECT DISTINCT category_id 
     FROM category
     WHERE name = 'Action'))

注意	本题的解答的点： 1） SELECT DISTINCT的使用，用作得到不重复的 category_id; 2）镶嵌了三个查询语句，最内层查询用于获取Action类别电影的category_id，中间查询获得了在film_category表中的所有符合Action的电影id;最外层则直接根据上一层查询得到的film_id筛选film表中的电影的title,description，并进行show.

Jasminexjf

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL在线编程练习(08-04)【2】

1. 分页查询employees表，每5行一页，返回第2页的数据题目描述分页查询employees表，每5行一页，返回第2页的数据CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` ...
复制链接

扫一扫