51.查找字符串'10,A,B' 中逗号','出现的次数cnt。

思路：由于 SQLite 中没有直接统计字符串中子串出现次数的函数，

因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，

即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，

最后除以子串的长度(本题中此步可省略，若子串长度大于1则不可省)。

SELECT (LENGTH('10,A,B')-LENGTH(REPLACE('10,A,B',',','')))/LENGTH(',') AS cnt;

52.获取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

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,-2) ASC;

SELECT first_name

FROM employees

ORDER BY substr(first_name,length(first_name)-1);

53.按照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)。

SELECT dept_no,group_concat(emp_no,",")

FROM dept_emp

GROUP BY dept_no;

54.查找排除当前最大、最小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`));

加 to_date = '9999-01-01' 作条件限制，导致挑选出来的是当前全表最大、最小salary，

然后对除去这两个salary再作条件限制 to_date = '9999-01-01' ，求平均薪水

SELECT AVG(salary) AS avg_salary

FROM salaries

WHERE salary NOT IN (SELECT MAX(salary) FROM salaries)

AND salary NOT IN (SELECT MIN(salary) FROM salaries)

AND to_date='9999-01-01';

严谨的逻辑：

SELECT AVG(salary) AS avg_salary FROM salaries

WHERE to_date = '9999-01-01'

AND salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

AND salary NOT IN (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01')；

55.分页查询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)，也可理解为跳过多少条记录后开始返回。

SELECT *

FROM employees

LIMIT 5 OFFSET 5;

方法二：只利用 LIMIT 关键字。

注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回(第一条记录序号为0)，切勿记反。

SELECT * FROM employees LIMIT 5,5

56.获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配具体的员工不显示

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

create table emp_bonus(

emp_no int not null,

recevied datetime not null,

btype smallint not null);

思路：先将 employees与dept_emp 用 INNER JOIN 连接，挑选出分配了部门的员工，

再用 LEFT JOIN 连接 emp_bonus，分配了奖金的员工显示奖金类型和授予时间，没分配奖金的员工则不显示。

SELECT em.emp_no, de.dept_no, eb.btype, eb.recevied

FROM employees AS em INNER JOIN dept_emp AS de

ON em.emp_no = de.emp_no

LEFT JOIN emp_bonus AS eb

ON de.emp_no = eb.emp_no；

57.使用含有关键字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 关键字的方法如下：意为在 employees 中挑选出

令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录，

也就是当 employees.emp_no=10011的时候。

反之，把NOT去掉，则输出 employees.emp_no=10001~10010时的记录。

法一：

SELECT * FROM employees WHERE NOT EXISTS

(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)；

法二：NOT IN 关键字

SELECT * FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_emp)；

58.存在如下的视图：

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 em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no

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

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'

本题主要考查 SQLite 中 CASE 表达式的用法。

即当 btype = 1 时，得到 salary * 0.1；

当 btype = 2 时，得到 salary * 0.2；

其他情况得到 salary * 0.3。

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'；

其实观察测试数据会发现 btype 只有1,2,3三种情况，即使不会 CASE 表达式，也能运用四则运算解出：

(注意要除以10.0，如果除以10的话，结果的小数位会被舍去)

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'；

60.按照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`));

本题的思路为复用 salaries 表进行子查询，最后以 s1.emp_no 排序输出求和结果。

1、输出的第三个字段，是由一个 SELECT 子查询构成。

将子查询内复用的 salaries 表记为 s2，主查询的 salaries 表记为 s1，

当主查询的 s1.emp_no 确定时，对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和

2、注意是对员工当前的薪水求和，所以在主查询和子查询内都要加限定条件 to_date = '9999-01-01'

SELECT s1.emp_no, s1.salary,

(SELECT SUM(s2.salary) FROM salaries AS s2

WHERE s2.emp_no <= s1.emp_no AND s2.to_date = '9999-01-01') AS running_total

FROM salaries AS s1

WHERE s1.to_date = '9999-01-01'

ORDER BY s1.emp_no；

61.对于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`));

正确的题目叙述：【对于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 rowid FROM employees AS e2) AS e1

WHERE e1.rowid % 2 = 1;

SQL语句是从最内层的括号开始解释，最后解释最外层的括号。所以此处语句的执行顺序是【e3层→e2层→e1层】。

SELECT e1.first_name FROM

employees e1

WHERE

(SELECT count(*) FROM employees e2

WHERE e1.first_name <=e2.first_name)%2=1;