SQL实战题目（三）

此题目全部来源于牛客网

45.题目描述
在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
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,
create_date datetime NOT NULL
);
答案：
由于SQLite中不能通过 ALTER TABLE … ADD FOREIGN KEY … REFERENCES … 语句来对已创建好的字段创建外键，因此只能先删除表，再重新建表的过程中创建外键。可参考：
http://www.sqlite.org/foreignkeys.html
https://zhidao.baidu.com/question/350164703.html
DROP TABLE audit;
CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL,
FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

46.题目描述
存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和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));
答案：
由于视图 emp_v 的记录是从 employees 中导出的，所以要判断两者中相等的数据，只需要判断emp_no相等即可。
方法一：用 WHERE 选取二者 emp_no 相等的记录
1
SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no
方法二：用 INTERSECT 关键字求 employees 和 emp_v 的交集
可参考：http://www.sqlite.org/lang_select.html
1
SELECT * FROM employees INTERSECT SELECT * FROM emp_v
方法三：仔细一想，emp_v的全部记录均由 employees 导出，因此可以投机取巧，直接输出 emp_v 所有记录
1
SELECT * FROM emp_v
【错误方法：】用以下方法直接输出 ，会得到两张表中符合条件的重复记录，因此不合题意，必须在 前加表名作限定
1
SELECT * FROM employees, emp_v WHERE employees.emp_no = emp_v.emp_no

47.题目描述
将所有获取奖金的员工当前的薪水增加10%。
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));
答案：
update salaries
set salary=salary * 1.1
where salary in(
select salary
from emp_bonus eb,salaries s
where eb.emp_no = s.emp_no

48.题目描述
针对库中的所有表生成select count(*)对应的SQL语句
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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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));
答案：

1、在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 ‘table’，详情可参考：
http://blog.csdn.net/xingfeng0501/article/details/7804378
2、在 SQLite 中用 “||” 符号连接字符串
1
2
SELECT “select count(*) from ” || name || “;” AS cnts
FROM sqlite_master WHERE type = ‘table’

49.题目描述
将employees表中的所有员工的last_name和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));
答案：
select (last_name||”’”||first_name) as name
from employees

50.查找字符串’10,A,B’ 中逗号’,’出现的次数cnt。
答案：
理解下：select as的含义
select length (‘10,A,B’) - length(replace(‘10,A,B’,”,”,”“)) as cnt

/*
select 2 as cnt
*/

51.题目描述
获取Employees中的first_name，查询按照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));
答案：

本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。
select first_name
from employees
order by substr(first_name,length(first_name)-1,2)

52.题目描述
按照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

53.题目描述
查找排除当前最大、最小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));
答案：
where字句用and连接多个条件；
select avg(salary) as avg_salary
from salaries
where salary not in
(select max(salary) from salaries where to_date = ‘9999-01-01’)
and (select min(salary) from salaries where to_date = ‘9999-01-01’)
and to_date = ‘9999-01-01’

54.分页查询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));
答案：
select * from employees limit 5,5;

55.题目描述
获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示
答案：
select e.emp_no,de.dept_no,eb.btype,eb.recevied
from (employees e inner join dept_emp de
on e.emp_no = de.emp_no)
left join emp_bonus eb
on e.emp_no = eb.emp_no

56.题目描述
使用含有关键字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));
答案：
exists关键字:强调的是是否返回结果集，不要求知道返回什么。只要返回了字段，就是真。
select *
from employees em
where not exists
( select emp_no
from dept_emp de
where de.emp_no = em.emp_no )

57.存在如下的视图：
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关键字。
答案：
2种：
select em.*
from employees em,emp_v ev
where em.emp_no = ev.emp_no

SELECT * FROM emp_v

58.题目描述

获取有奖金的员工相关信息
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=’9999-01-01’
答案：
本题主要考查 SQLite 中 CASE 表达式的用法。即当 btype = 1 时，得到 salary * 0.1；当 btype = 2 时，得到 salary * 0.2；其他情况得到 salary * 0.3。详细用法请参考：
http://www.sqlite.org/lang_expr.html 中的【The CASE expression】
http://www.2cto.com/database/201202/120267.html 中的【条件表达式】
SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(CASE b.btype
WHEN 1 THEN s.salary * 0.1
WHEN 2 THEN s.salary * 0.2
ELSE s.salary * 0.3 END) 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’

59.题目描述
按照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))
答案：
这题目很好：灵活复用子表，
解法1：是将查询出的作为一个select中，想法奇异；
select s1.emp_no,s1.salary,
(select sum(s2.salary)
from salaries s2
where s2.emp_no <= s1.emp_no
and s2.to_date = ‘9999-01-01’
) as running_total
from salaries s1
where s1.to_date = ‘9999-01-01’
解法2：关联2表，依据emp_no唯一，再根据group分组，select挑选出；
把所有小于等于当前编号的表s1和当前编号表s2联立起来，然后按照当前编号分组，计算出所有小于等于
当前标号的工资总数
SELECT s2.emp_no,s2.salary,SUM(s1.salary) AS running_total
FROM salaries AS s1 INNER JOIN salaries AS s2
ON s1.emp_no <= s2.emp_no
WHERE
s1.to_date = “9999-01-01”
AND s2.to_date = “9999-01-01”
GROUP BY s2.emp_no

60.题目描述
【对于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 employees as e1
where
(select count(*) from employees as e2 where e2.first_name <=e1.first_name ) %2 =1