《SQL 实战》题解（33-48题）

SQL33

题目

创建一个actor表，包含如下列信息(注：sqlite获取系统默认时间是datetime(‘now’,‘localtime’))

列表	类型	是否为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,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    last_update timestamp NOT NULL DEFAULT(datetime('now','localtime')),
    PRIMARY KEY(actor_id)
)

SQL34

题目

对于表actor批量插入如下数据(不能有2条insert语句哦!)

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

SQL35

题目

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

题解

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

SQL36

题目

对于如下表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表结构如下：

列表	类型	是否为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;

SQL37

题目

针对如下表actor结构创建索引：

(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作)

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

SQL38

题目

针对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(first_name_v, last_name_v) AS
SELECT first_name, last_name
FROM actor;

SQL39

题目

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

题解

-- sqlite
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005;
-- mysql
SELECT * FROM salaries FORCE INDEX (idx_emp_no) WHERE emp_no = 10005;

SQL40

题目

存在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 create_date datetime NOT NULL DEFAULT('0000-00-00 00:00:00');

SQL41

题目

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

SQL42

题目

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

题解

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

SQL43

题目

将所有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);

题解

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

SQL44

题目

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

题解

UPDATE titles_test 
SET emp_no = REPLACE(emp_no, 10001, 10005)
WHERE id = 5;

SQL45

题目

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

题目

-- sqlite
ALTER TABLE titles_test RENAME TO titles_2017;
-- mysql
RENAME TABLE titles_test TO titles_2017;

SQL46

题目

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。

(audit已经创建，需要先drop)

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)
);
-- mysql
DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL REFERENCES employees_test(ID),
    create_date datetime NOT NULL,
);
-- mysql
ALTER TABLE audit
ADD FOREIGN KEY(EMP_no) REFERENCES employees_test(ID);

SQL47

题目

存在如下的视图：

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

如何获取emp_v和employees有相同的数据？

题解

SELECT * FROM emp_v;

SQL48

题目

请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

create table emp_bonus(
emp_no int 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 = 1.1*salarya
WHERE to_date='9999-01-01'
AND emp_no IN (
    SELECT emp_no FROM emp_bonus GROUP BY emp_no 
)