SQL在线编程练习(08-02)【1】

Date: 2019-08-02

1. 创建一个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	最后更新时间，默认是系统的当前时间

注意： 

根据题意，本题关键点是actor_id的主键设置与last_update的默认获取系统时间：

1、在actor_id字段末尾加上PRIMARY KEY是将该字段设置为主键，或者在表的最后一行加上PRIMARY KEY(actor_id)

2、在last_update末尾加上DEFAULT是为该字段设置默认值，且默认值为(datetime('now','localtime'))，即获得系统时间，注意最外层的括号不可省略

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

2.  对于表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语句进行多行数据到的插入。以及数据类型的插入。

INSERT INTO actor(actor_id, first_name, last_name,last_update)
VALUES(
    '1',
    'PENELOPE',
    'GUINESS',
    '2006-02-15 12:34:33'),
    ('2',
     'NICK',
     'WAHLBERG',
     '2006-02-15 12:34:33');

3.  批量插入数据，不使用replace操作

对于表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'

注意：：

1）  在 SQLite 中，用 INSERT OR IGNORE 来插入记录，或忽略插入与表内UNIQUE字段都相同的记录

1	INSERT OR IGNORE INTO actor VALUES (3, 'ED', 'CHASE', '2006-02-15 12:34:33')

2）用 INSERT OR REPLACE 来插入记录，或更新替代与表内UNIQUE字段都相同的记录

2	INSERT OR REPLACE INTO actor VALUES (3, 'ED', 'CHASE', '2006-02-15 12:34:33')2）

INSERT or IGNORE INTO actor 
VALUES ('3','ED','CHASE','2006-02-15 12:34:33');

4.  创建一个actor_name表  并从现有的表中导入相应的数据

对于如下表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语句建立一个新表，然后将actor表中的数据进行导入。
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

5.  对first_name创建唯一索引uniq_idx_firstname  考察的是建立唯一索引和普通索引

针对如下表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);

6.  针对actor表创建视图actor_name_view

针对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

7.  针对上面的salaries表emp_no字段创建索引idx_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 index idx_emp_no on salaries(emp_no);  # 原始题目中已经建立好了索引

select * from salaries
indexed by idx_emp_no
where emp_no = '10005'
# 满足条件时，强制索引。

8.  在last_update后面新增加一列名字为create_date  （新增列）

存在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:00:00'

ALTER TABLE actor ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

9. 构造一个触发器audit_log

构造一个触发器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
);

CREATE TRIGGER audit_log AFTER INSERT ON employees_test
begin 
    INSERT INTO audit VALUES(new.id, new.name);
end;

10.  删除emp_no重复的记录，只保留最小的id对应的记录。 （删除在某列上重复的值记录）

删除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');

思路;先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id，然后用 DELETE FROM ... WHERE ... NOT IN ... 语句删除 “非每个分组最小id对应的所有记录”

DELETE FROM titles_test WHERE id NOT IN
(SELECT MIN(id) FROM titles_test GROUP BY emp_no

11. 将所有to_date为9999-01-01的全部更新为NULL  

将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
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');

UPDATE titles_test 
SET to_date = NULL,from_date = '2001-01-01'
WHERE to_date = '9999-01-01'

12.  将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。
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');

UPDATE titles_test 
SET emp_no = REPLACE(emp_no,10001,10005) 
WHERE id = 5
# 或者
replace into titles_test values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');

13.  将titles_test表名修改为titles_2017   （修改表名：重命名表）

将titles_test表名修改为titles_2017。
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');

ALTER TABLE titles_test rename to titles_2017

14.  在audit表上创建外键约束，其emp_no对应employees_test表的主键id （删除已有的表，然后基于之前的内容新建并构建外键）

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

DROP TABLE audit;
CREATE TABLE audit(
    emp_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(emp_no) REFERENCES employees_test(ID));

15.  如何获取emp_v和employees有相同的数据no

存在如下的视图：
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_no	birth_date	first_name	last_name	gender	hire_date
10006	1953-04-20	Anneke	Preusig	F	1989-06-02
10007	1957-05-23	Tzvetan	Zielinski	F	1989-02-10
10008	1958-02-19	Saniya	Kalloufi	M	1994-09-15
10009	1952-04-19	Sumant	Peac	F	1985-02-18
10010	1963-06-01	Duangkaew	Piveteau	F	1989-08-24

由于视图 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

【错误方法：】用以下方法直接输出 *，会得到两张表中符合条件的重复记录，因此不合题意，必须在 * 前加表名作限定

 

SELECT * FROM employees, emp_v WHERE employees.emp_no = emp_v.emp_no

16. 将所有获取奖金的员工当前的薪水增加10%   （借助领取奖学金的表信息，修改薪水表 中某一列的值）

将所有获取奖金的员工当前的薪水增加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 emp_no in (select emp_no from emp_bonus)

17. 针对库中的所有表生成select count(*)对应的SQL语句

针对库中的所有表生成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`));
输出格式:

cnts
select count(*) from employees;
select count(*) from departments;
select count(*) from dept_emp;
select count(*) from dept_manager;
select count(*) from salaries;
select count(*) from titles;

思路： 

本题主要有以下两个关键点：

1、在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 'table'，详情可参考：

http://blog.csdn.net/xingfeng0501/article/details/7804378

2、在 SQLite 中用 “||” 符号连接字符串

SELECT "select count(*) from " || name || ";" AS cnts
FROM sqlite_master WHERE type = 'table'

18. 将employees表中的所有员工的last_name和first_name通过(')连接起来。

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

name
Facello'Georgi
Simmel'Bezalel
Bamford'Parto
Koblick'Chirstian
Maliniak'Kyoichi
Preusig'Anneke
Zielinski'Tzvetan
Kalloufi'Saniya
Peac'Sumant
Piveteau'Duangkaew

SELECT last_name ||"'"|| first_name as name
FROM employees

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

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

思路：由于 SQLite 中没有直接统计字符串中子串出现次数的函数，因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，最后除以子串的长度（本题中此步可省略，若子串长度大于1则不可省）

select (length('10,A,B')-length(replace('10,A,B',',',''))) as cnt

20. 获取Employees中的first_name

获取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`));
输出格式：

first_name
Chirstian
Tzvetan
Bezalel
Duangkaew
Georgi
Kyoichi
Anneke
Sumant
Mary
Parto

思路： 本题考查 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)