附录C 练习题答案

# 附录C 练习题答案

# 第3章

# 3-1

检索所有银行员工的员工ID、名字和姓氏，并按姓氏和名字进行排序。

mysql> SELECT emp_id, fname, lname
    -> FROM employee
    -> ORDER BY lname, fname;
+--------+----------+-----------+
| emp_id | fname    | lname     |
+--------+----------+-----------+
|      2 | Susan    | Barker    |
|     13 | John     | Blake     |
|      6 | Helen    | Fleming   |
|     17 | Beth     | Fowler    |
|      5 | John     | Gooding   |
|      9 | Jane     | Grossman  |
|      4 | Susan    | Hawthorne |
|     12 | Samantha | Jameson   |
|     16 | Theresa  | Markham   |
|     14 | Cindy    | Mason     |
|      8 | Sarah    | Parker    |
|     15 | Frank    | Portman   |
|     10 | Paula    | Roberts   |
|      1 | Michael  | Smith     |
|      7 | Chris    | Tucker    |
|     18 | Rick     | Tulman    |
|      3 | Robert   | Tyler     |
|     11 | Thomas   | Ziegler   |
+--------+----------+-----------+
18 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 3-2

检索状态为“ACTIVE”且可用余额大于2500美元的所有账户的账户ID、客户ID和可用余额。

mysql> SELECT account_id, cust_id, avail_balance
    -> FROM account
    -> WHERE status = 'ACTIVE'
    ->   AND avail_balance > 2500;
+------------+---------+----------------+
| account_id | cust_id | avail_balance  |
+------------+---------+----------------+
|          3 |       1 | 3000.00        |
|         10 |       4 | 5487.09        |
|         13 |       6 | 10000.00       |
|         14 |       7 | 5000.00        |
|         15 |       8 | 3487.19        |
|         18 |       9 | 9345.55        |
|         20 |      10 | 23575.12       |
|         22 |      11 | 9345.55        |
|         23 |      12 | 38552.05       |
|         24 |      13 | 50000.00       |
+------------+---------+----------------+
10 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 3-3

编写一个针对account表的查询，返回开设账户的员工的ID（使用account.open_emp_id列），每个不同的员工只包含一行记录。

mysql> SELECT DISTINCT open_emp_id
    -> FROM account;
+-------------+
| open_emp_id |
+-------------+
|           1 |
|          10 |
|          13 |
|          16 |
+-------------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

# 3-4

填写这个多数据集查询中的空白处（用<#>表示），以实现如下结果：

mysql> SELECT p.product_cd, a.cust_id, a.avail_balance
    -> FROM product p INNER JOIN account <1>
    ->      ON p.product_cd = <2>
    -> WHERE p.<3> = 'ACCOUNT';
+------------+---------+----------------+
| product_cd | cust_id | avail_balance  |
+------------+---------+----------------+
| CD         |       1 | 3000.00        |
| CD         |       6 | 10000.00       |
| CD         |       7 | 5000.00        |
| CD         |       9 | 1500.00        |
| CHK        |       1 | 1057.75        |
| CHK        |       2 | 2258.02        |
| CHK        |       3 | 1057.75        |
| CHK        |       4 | 534.12         |
| CHK        |       5 | 2237.97        |
| CHK        |       6 | 122.37         |
| CHK        |       8 | 3487.19        |
| CHK        |       9 | 125.67         |
| CHK        |      10 | 23575.12       |
| CHK        |      12 | 38552.05       |
| MM         |       3 | 2212.50        |
| MM         |       4 | 5487.09        |
| MM         |       9 | 9345.55        |
| SAV        |       1 | 500.00         |
| SAV        |       2 | 200.00         |
| SAV        |       4 | 767.77         |
| SAV        |       8 | 387.99         |
+------------+---------+----------------+
21 rows in set (0.02 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

<1>、<2>和<3>的正确值为：

a
a.product_cd
product_type_cd

# 第4章

# 4-1

以下过滤条件会返回哪些交易ID？ txn_date < '2005-02-26' AND (txn_type_cd = 'DBT' OR amount > 100) 交易ID 1、2、3、5、6和7。

# 4-2

以下过滤条件会返回哪些交易ID？ account_id IN (101,103) AND NOT (txn_type_cd = 'DBT' OR amount > 100) 交易ID 4和9。

# 4-3

构造一个查询，检索所有在2002年开设的账户。

mysql> SELECT account_id, open_date
    -> FROM account
    -> WHERE open_date BETWEEN '2002-01-01' AND '2002-12-31';
+------------+------------+
| account_id | open_date  |
+------------+------------+
|          6 | 2002-11-23 |
|          7 | 2002-12-15 |
|         12 | 2002-08-24 |
|         20 | 2002-09-30 |
|         21 | 2002-10-01 |
+------------+------------+
5 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

# 4-4

构造一个查询，查找所有姓氏中第二个位置包含字母“a”且在“a”之后任意位置包含字母“e”的非企业客户。

mysql> SELECT cust_id, lname, fname
    -> FROM individual
    -> WHERE lname LIKE '_a%e%';
+---------+----------+--------+
| cust_id | lname    | fname  |
+---------+----------+--------+
|       1 | Hadley   | James  |
|       9 | Farley   | Richard|
+---------+----------+--------+
2 rows in set (0.02 sec)

1
2
3
4
5
6
7
8
9
10

# 第5章

# 5-1

填写以下查询中的空白处（用<#>表示），以获得如下结果：

mysql> SELECT e.emp_id, e.fname, e.lname, b.name
    -> FROM employee e INNER JOIN <1> b
    ->      ON e.assigned_branch_id = b.<2>;
+--------+----------+-----------+----------------+
| emp_id | fname    | lname     | name           |
+--------+----------+-----------+----------------+
|      1 | Michael  | Smith     | Headquarters   |
|      2 | Susan    | Barker    | Headquarters   |
|      3 | Robert   | Tyler     | Headquarters   |
|      4 | Susan    | Hawthorne | Headquarters   |
|      5 | John     | Gooding   | Headquarters   |
|      6 | Helen    | Fleming   | Headquarters   |
|      7 | Chris    | Tucker    | Headquarters   |
|      8 | Sarah    | Parker    | Headquarters   |
|      9 | Jane     | Grossman  | Headquarters   |
|     10 | Paula    | Roberts   | Woburn Branch  |
|     11 | Thomas   | Ziegler   | Woburn Branch  |
|     12 | Samantha | Jameson   | Woburn Branch  |
|     13 | John     | Blake     | Quincy Branch  |
|     14 | Cindy    | Mason     | Quincy Branch  |
|     15 | Frank    | Portman   | Quincy Branch  |
|     16 | Theresa  | Markham   | So. NH Branch  |
|     17 | Beth     | Fowler    | So. NH Branch  |
|     18 | Rick     | Tulman    | So. NH Branch  |
+--------+----------+-----------+----------------+
18 rows in set (0.03 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<1>和<2>的正确值为：

branch
branch_id

# 5-2

编写一个查询，返回每个非企业客户（customer.cust_type_cd = 'I'）的账户ID，以及客户的联邦ID（customer.fed_id）和账户所基于的产品名称（product.name）。

mysql> SELECT a.account_id, c.fed_id, p.name
    -> FROM account a INNER JOIN customer c
    ->      ON a.cust_id = c.cust_id
    ->      INNER JOIN product p
    ->      ON a.product_cd = p.product_cd
    -> WHERE c.cust_type_cd = 'I';
+------------+----------------+----------------------------------+
| account_id | fed_id         | name                             |
+------------+----------------+----------------------------------+
|          1 | 111-11-1111   | checking account                 |
|          2 | 111-11-1111   | savings account                   |
|          3 | 111-11-1111   | certificate of deposit           |
|          4 | 222-22-2222   | checking account                 |
|          5 | 222-22-2222   | savings account                   |
|          6 | 333-33-3333   | checking account                 |
|          7 | 333-33-3333   | money market account             |
|          8 | 444-44-4444   | checking account                 |
|          9 | 444-44-4444   | savings account                   |
|         10 | 444-44-4444   | money market account             |
|         11 | 555-55-5555   | checking account                 |
|         12 | 666-66-6666   | checking account                 |
|         13 | 666-66-6666   | certificate of deposit           |
|         14 | 777-77-7777   | certificate of deposit           |
|         15 | 888-88-8888   | checking account                 |
|         16 | 888-88-8888   | savings account                   |
|         17 | 999-99-9999   | checking account                 |
|         18 | 999-99-9999   | money market account             |
|         19 | 999-99-9999   | certificate of deposit           |
+------------+----------------+----------------------------------+
19 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

# 5-3

构造一个查询，查找所有直属上级被分配到不同部门的员工，检索这些员工的ID、名字和姓氏。

mysql> SELECT e.emp_id, e.fname, e.lname
    -> FROM employee e INNER JOIN employee mgr
    ->      ON e.superior_emp_id = mgr.emp_id
    -> WHERE e.dept_id != mgr.dept_id;
+--------+----------+-----------+
| emp_id | fname    | lname     |
+--------+----------+-----------+
|      4 | Susan    | Hawthorne |
|      5 | John     | Gooding   |
+--------+----------+-----------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11

# 第6章

# 6-1

如果集合A = {L M N O P}，集合B = {P Q R S T}，那么以下操作会生成哪些集合？

A union B
A union all B
A intersect B
A except B

A union B = {L M N O P Q R S T}
A union all B = {L M N O P P Q R S T}
A intersect B = {P}
A except B = {L M N O}

# 6-2

编写一个复合查询，查找所有个人客户的名字和姓氏，以及所有员工的名字和姓氏。

mysql> SELECT fname, lname
    -> FROM individual
    -> UNION
    -> SELECT fname, lname
    -> FROM employee;
+----------+-----------+
| fname    | lname     |
+----------+-----------+
| James    | Hadley    |
| Susan    | Tingley   |
| Frank    | Tucker    |
| John     | Hayward   |
| Charles  | Frasier   |
| John     | Spencer   |
| Margaret | Young     |
| Louis    | Blake     |
| Richard  | Farley    |
| Michael  | Smith     |
| Susan    | Barker    |
| Robert   | Tyler     |
| Susan    | Hawthorne |
| John     | Gooding   |
| Helen    | Fleming   |
| Chris    | Tucker    |
| Sarah    | Parker    |
| Jane     | Grossman  |
| Paula    | Roberts   |
| Thomas   | Ziegler   |
| Samantha | Jameson   |
| John     | Blake     |
| Cindy    | Mason     |
| Frank    | Portman   |
| Theresa  | Markham   |
| Beth     | Fowler    |
| Rick     | Tulman    |
+----------+-----------+
27 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

# 6-3

按照lname列对练习6-2的结果进行排序。

mysql> SELECT fname,
    ->        lname
    -> FROM individual
    -> UNION
    -> SELECT fname,
    ->        lname
    -> FROM employee;
+----------+-----------+
| fname    | lname     |
+----------+-----------+
| James    | Hadley    |
| Susan    | Tingley   |
| Frank    | Tucker    |
| John     | Hayward   |
| Charles  | Frasier   |
| John     | Spencer   |
| Margaret | Young     |
| Louis    | Blake     |
| Richard  | Farley    |
| Michael  | Smith     |
| Susan    | Barker    |
| Robert   | Tyler     |
| Susan    | Hawthorne |
| John     | Gooding   |
| Helen    | Fleming   |
| Chris    | Tucker    |
| Sarah    | Parker    |
| Jane     | Grossman  |
| Paula    | Roberts   |
| Thomas   | Ziegler   |
| Samantha | Jameson   |
| John     | Blake     |
| Cindy    | Mason     |
| Frank    | Portman   |
| Theresa  | Markham   |
| Beth     | Fowler    |
| Rick     | Tulman    |
+----------+-----------+
27 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

# 第7章

# 7-1

编写一个查询，返回字符串“Please find the substring in this string”中第17到25个字符。

mysql> SELECT SUBSTRING('Please find the substring in this string', 17, 9);
+------------------------------------------------------------------+
| SUBSTRING('Please find the substring in this string', 17, 9)     |
+------------------------------------------------------------------+
|                                                                  |
| substring                                                        |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

# 7-2

编写一个查询，返回数字-25.76823的绝对值和符号（-1、0或1），同时返回该数字四舍五入到百分位的值。

mysql> SELECT ABS(-25.76823), SIGN(-25.76823), ROUND(-25.76823, 2);
+-------------------+---------------------+-----------------------+
| ABS(-25.76823)     | SIGN(-25.76823)      | ROUND(-25.76823, 2)    |
+-------------------+---------------------+-----------------------+
| 25.76823           | -1                  | -25.77                |
+-------------------+---------------------+-----------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

# 7-3

编写一个查询，仅返回当前日期的月份部分。

mysql> SELECT EXTRACT(MONTH FROM CURRENT_DATE());
+--------------------------------+
| EXTRACT(MONTH FROM CURRENT_DATE) |
+--------------------------------+
|                                5 |
+--------------------------------+
1 row in set (0.02 sec)

1
2
3
4
5
6
7

（除非你做这个练习时正好是5月，否则你的结果很可能会有所不同。）

# 第8章

# 8-1

构造一个查询，统计account表中的行数。

mysql> SELECT COUNT(*)
-> FROM account;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.32 sec)

1
2
3
4
5
6
7
8

# 8-2

修改练习8-1中的查询，统计每个客户持有的账户数量。显示客户ID和每个客户的账户数量。

mysql> SELECT cust_id, COUNT(*)
-> FROM account
-> GROUP BY cust_id;
+---------+----------+
| cust_id | count(*) |
+---------+----------+
|       1 |        3 |
|       2 |        2 |
|       3 |        2 |
|       4 |        3 |
|       5 |        1 |
|       6 |        2 |
|       7 |        1 |
|       8 |        2 |
|       9 |        3 |
|      10 |        2 |
|      11 |        1 |
|      12 |        1 |
|      13 |        1 |
+---------+----------+
13 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 8-3

修改练习8-2中的查询，仅包含那些至少有两个账户的客户。

mysql> SELECT cust_id, COUNT(*)
-> FROM account
-> GROUP BY cust_id
-> HAVING COUNT(*) >= 2;
+---------+----------+
| cust_id | COUNT(*) |
+---------+----------+
|       1 |        3 |
|       2 |        2 |
|       3 |        2 |
|       4 |        3 |
|       6 |        2 |
|       8 |        2 |
|       9 |        3 |
|      10 |        2 |
+---------+----------+
8 rows in set (0.04 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 8-4（附加题）

按产品和分支机构查找总可用余额，条件是每个产品和分支机构的账户数量超过一个。按总余额（从高到低）对结果进行排序。

mysql> SELECT product_cd, open_branch_id, SUM(avail_balance)
-> FROM account
-> GROUP BY product_cd, open_branch_id
-> HAVING COUNT(*) > 1
-> ORDER BY 3 DESC;
+------------+------------------+---------------------+
| product_cd | open_branch_id    | SUM(avail_balance)   |
+------------+------------------+---------------------+
| CHK        | 4                | 67852.33             |
| MM         | 1                | 14832.64             |
| CD         | 1                | 11500.00             |
| CD         | 2                | 8000.00              |
| CHK        | 2                | 3315.77              |
| CHK        | 1                | 782.16               |
| SAV        | 2                | 700.00               |
+------------+------------------+---------------------+
7 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

注意，MySQL不接受ORDER BY SUM(avail_balance) DESC，所以我只能按位置指定排序列。

# 第9章

# 9-1

对account表构造一个查询，使用一个过滤条件和一个针对product表的非关联子查询，查找所有贷款账户（product.product_type_cd = 'LOAN'）。检索账户ID、产品代码、客户ID和可用余额。

mysql> SELECT account_id, product_cd, cust_id, avail_balance
-> FROM account
-> WHERE product_cd IN (SELECT product_cd
->       FROM product
->      WHERE product_type_cd = 'LOAN');
+------------+------------+---------+-----------------+
| account_id | product_cd | cust_id | avail_balance   |
+------------+------------+---------+-----------------+
|         21 | BUS        |      10 | 0.00            |
|         22 | BUS        |      11 | 9345.55         |
|         24 | SBL        |      13 | 50000.00        |
+------------+------------+---------+-----------------+
3 rows in set (0.07 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

# 9-2

使用针对product表的关联子查询重新编写练习9-1中的查询，以获得相同的结果。

mysql> SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance
-> FROM account a
-> WHERE EXISTS (SELECT 1
->       FROM product p
->      WHERE p.product_cd = a.product_cd
->          AND p.product_type_cd = 'LOAN');
+------------+------------+---------+-----------------+
| account_id | product_cd | cust_id | avail_balance   |
+------------+------------+---------+-----------------+
|         21 | BUS        |      10 | 0.00            |
|         22 | BUS        |      11 | 9345.55         |
|         24 | SBL        |      13 | 50000.00        |
+------------+------------+---------+-----------------+
3 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 9-3

将以下查询与employee表进行连接，以显示每个员工的经验水平：

SELECT 'trainee' name, '2004-01-01' start_dt, '2005-12-31' end_dt
UNION ALL
SELECT 'worker' name, '2002-01-01' start_dt, '2003-12-31' end_dt
UNION ALL
SELECT 'mentor' name, '2000-01-01' start_dt, '2001-12-31' end_dt

1
2
3
4
5

给子查询取别名levels，并包含员工的ID、名字、姓氏和经验水平（levels.name）。（提示：使用不等式条件构建连接条件，以确定员工的start_date列属于哪个级别。）

mysql> SELECT e.emp_id, e.fname, e.lname, levels.name
-> FROM employee e INNER JOIN
->    (SELECT 'trainee' name, '2004-01-01' start_dt, '2005-12-31' end_dt
->      UNION ALL
->      SELECT 'worker' name, '2002-01-01' start_dt, '2003-12-31' end_dt
->      UNION ALL
->     SELECT 'mentor' name, '2000-01-01' start_dt, '2001-12-31' end_dt) levels
->      ON e.start_date BETWEEN levels.start_dt AND levels.end_dt;
+--------+----------+-----------+----------+
| emp_id | fname    | lname     | name     |
+--------+----------+-----------+----------+
|      6 | Helen    | Fleming   | trainee  |
|      7 | Chris    | Tucker    | trainee  |
|      2 | Susan    | Barker    | worker   |
|      4 | Susan    | Hawthorne | worker   |
|      5 | John     | Gooding   | worker   |
|      8 | Sarah    | Parker    | worker   |
|      9 | Jane     | Grossman  | worker   |
|     10 | Paula    | Roberts   | worker   |
|     12 | Samantha | Jameson   | worker   |
|     14 | Cindy    | Mason     | worker   |
|     15 | Frank    | Portman   | worker   |
|     17 | Beth     | Fowler    | worker   |
|     18 | Rick     | Tulman    | worker   |
|      1 | Michael  | Smith     | mentor   |
|      3 | Robert   | Tyler     | mentor   |
|     11 | Thomas   | Ziegler   | mentor   |
|     13 | John     | Blake     | mentor   |
|     16 | Theresa  | Markham   | mentor   |
+--------+----------+-----------+----------+
18 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

# 9-4

对员工（employee）表构建一个查询，检索员工ID、名字、姓氏，以及员工所属部门和分支机构的名称。该查询不能使用任何表连接操作。

mysql> SELECT e.emp_id, e.fname, e.lname,
->     (SELECT d.name FROM department d
->      WHERE d.dept_id = e.dept_id) dept_name,
->     (SELECT b.name FROM branch b
->      WHERE b.branch_id = e.assigned_branch_id) branch_name
->  FROM employee e;
+--------+----------+-----------+------------------+------------------+
| emp_id | fname    | lname     | dept_name        | branch_name      |
+--------+----------+-----------+------------------+------------------+
|      1 | Michael  | Smith     | Administration   | Headquarters     |
|      2 | Susan    | Barker    | Administration   | Headquarters     |
|      3 | Robert   | Tyler     | Administration   | Headquarters     |
|      4 | Susan    | Hawthorne | Operations       | Headquarters     |
|      5 | John     | Gooding   | Loans            | Headquarters     |
|      6 | Helen    | Fleming   | Operations       | Headquarters     |
|      7 | Chris    | Tucker    | Operations       | Headquarters     |
|      8 | Sarah    | Parker    | Operations       | Headquarters     |
|      9 | Jane     | Grossman  | Operations       | Headquarters     |
|     10 | Paula    | Roberts   | Operations       | Woburn Branch    |
|     11 | Thomas   | Ziegler   | Operations       | Woburn Branch    |
|     12 | Samantha | Jameson   | Operations       | Woburn Branch    |
|     13 | John     | Blake     | Operations       | Quincy Branch    |
|     14 | Cindy    | Mason     | Operations       | Quincy Branch    |
|     15 | Frank    | Portman   | Operations       | Quincy Branch    |
|     16 | Theresa  | Markham   | Operations       | So. NH Branch    |
|     17 | Beth     | Fowler    | Operations       | So. NH Branch    |
|     18 | Rick     | Tulman    | Operations       | So. NH Branch    |
+--------+----------+-----------+------------------+------------------+
18 rows in set (0.12 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# 第10章

# 10-1

编写一个查询，返回所有产品名称以及基于该产品的账户信息（使用账户（account）表中的product_cd列与产品（product）表进行关联）。包括所有产品，即使没有为该产品开设任何账户。

mysql> SELECT p.product_cd, a.account_id, a.cust_id, a.avail_balance
->  FROM product p LEFT OUTER JOIN account a
->      ON p.product_cd = a.product_cd;
+------------+------------+---------+-----------------+
| product_cd | account_id | cust_id | avail_balance   |
+------------+------------+---------+-----------------+
| AUT        |        NULL|    NULL | NULL            |
| BUS        |         21 |      10 | 0.00            |
| BUS        |         22 |      11 | 9345.55         |
| CD         |          3 |       1 | 3000.00         |
| CD         |         13 |       6 | 10000.00        |
| CD         |         14 |       7 | 5000.00         |
| CD         |         19 |       9 | 1500.00         |
| CHK        |          1 |       1 | 1057.75         |
| CHK        |          4 |       2 | 2258.02         |
| CHK        |          6 |       3 | 1057.75         |
| CHK        |          8 |       4 | 534.12          |
| CHK        |         11 |       5 | 2237.97         |
| CHK        |         12 |       6 | 122.37          |
| CHK        |         15 |       8 | 3487.19         |
| CHK        |         17 |       9 | 125.67          |
| CHK        |         20 |      10 | 23575.12        |
| CHK        |         23 |      12 | 38552.05        |
| MM         |          7 |       3 | 2212.50         |
| MM         |         10 |       4 | 5487.09         |
| MM         |         18 |       9 | 9345.55         |
| MRT        |        NULL|    NULL | NULL            |
| SAV        |          2 |       1 | 500.00          |
| SAV        |          5 |       2 | 200.00          |
| SAV        |          9 |       4 | 767.77          |
| SAV        |         16 |       8 | 387.99          |
| SBL        |         24 |      13 | 50000.00        |
+------------+------------+---------+-----------------+
26 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# 10-2

重新编写练习10-1中的查询，使用另一种外连接类型（例如，如果你在练习10-1中使用了左外连接，这次就使用右外连接），使得结果与练习10-1相同。

mysql> SELECT p.product_cd, a.account_id, a.cust_id, a.avail_balance
->  FROM account a RIGHT OUTER JOIN product p
->      ON p.product_cd = a.product_cd;
+------------+------------+---------+-----------------+
| product_cd | account_id | cust_id | avail_balance   |
+------------+------------+---------+-----------------+
| AUT        |       NULL |    NULL | NULL            |
| BUS        |         21 |      10 | 0.00            |
| BUS        |         22 |      11 | 9345.55         |
| CD         |          3 |       1 | 3000.00         |
| CD         |         13 |       6 | 10000.00        |
| CD         |         14 |       7 | 5000.00         |
| CD         |         19 |       9 | 1500.00         |
| CHK        |          1 |       1 | 1057.75         |
| CHK        |          4 |       2 | 2258.02         |
| CHK        |          6 |       3 | 1057.75         |
| CHK        |          8 |       4 | 534.12          |
| CHK        |         11 |       5 | 2237.97         |
| CHK        |         12 |       6 | 122.37          |
| CHK        |         15 |       8 | 3487.19         |
| CHK        |         17 |       9 | 125.67          |
| CHK        |         20 |      10 | 23575.12        |
| CHK        |         23 |      12 | 38552.05        |
| MM         |          7 |       3 | 2212.50         |
| MM         |         10 |       4 | 5487.09         |
| MM         |         18 |       9 | 9345.55         |
| MRT        |       NULL |    NULL | NULL            |
| SAV        |          2 |       1 | 500.00          |
| SAV        |          5 |       2 | 200.00          |
| SAV        |          9 |       4 | 767.77          |
| SAV        |         16 |       8 | 387.99          |
| SBL        |         24 |      13 | 50000.00        |
+------------+------------+---------+-----------------+
26 rows in set (0.02 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# 10-3

将账户（account）表通过account.cust_id列与个人（individual）表和企业（business）表进行外连接，使得结果集中每个账户对应一行数据。需要包含的列有account.account_id、account.product_cd、individual.fname、individual.lname和business.name。

mysql> SELECT a.account_id, a.product_cd,
->      i.fname, i.lname, b.name
->  FROM account a LEFT OUTER JOIN business b
->      ON a.cust_id = b.cust_id
->      LEFT OUTER JOIN individual i
->      ON a.cust_id = i.cust_id;
+------------+------------+----------+-----------+----------------------------------+
| account_id | product_cd | fname    | lname     | name                             |
+------------+------------+----------+-----------+----------------------------------+
|          1 | CHK        | James    | Hadley    | NULL                             |
|          2 | SAV        | James    | Hadley    | NULL                             |
|          3 | CD         | James    | Hadley    | NULL                             |
|          4 | CHK        | Susan    | Tingley   | NULL                             |
|          5 | SAV        | Susan    | Tingley   | NULL                             |
|          6 | CHK        | Frank    | Tucker    | NULL                             |
|          7 | MM         | Frank    | Tucker    | NULL                             |
|          8 | CHK        | John     | Hayward   | NULL                             |
|          9 | SAV        | John     | Hayward   | NULL                             |
|         10 | MM         | John     | Hayward   | NULL                             |
|         11 | CHK        | Charles  | Frasier   | NULL                             |
|         12 | CHK        | John     | Spencer   | NULL                             |
|         13 | CD         | John     | Spencer   | NULL                             |
|         14 | CD         | Margaret | Young     | NULL                             |
|         15 | CHK        | Louis    | Blake     | NULL                             |
|         16 | SAV        | Louis    | Blake     | NULL                             |
|         17 | CHK        | Richard  | Farley    | NULL                             |
|         18 | MM         | Richard  | Farley    | NULL                             |
|         19 | CD         | Richard  | Farley    | NULL                             |
|         20 | CHK        | NULL     | NULL      | Chilton Engineering             |
|         21 | BUS        | NULL     | NULL      | Chilton Engineering             |
|         22 | BUS        | NULL     | NULL      | Northeast Cooling Inc.          |
|         23 | CHK        | NULL     | NULL      | Superior Auto Body               |
|         24 | SBL        | NULL     | NULL      | AAA Insurance Inc.               |
+------------+------------+----------+-----------+----------------------------------+
24 rows in set (0.05 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 10 - 4（附加分题）

设计一个查询，生成集合{1, 2, 3, ..., 99, 100}。（提示：使用交叉连接（cross join），至少包含两个from子句子查询。）

SELECT ones.x + tens.x + 1
FROM
(SELECT 0 x UNION ALL SELECT 1 x UNION ALL SELECT 2 x UNION ALL SELECT 3 x UNION ALL SELECT 4 x UNION ALL SELECT 5 x UNION ALL SELECT 6 x UNION ALL SELECT 7 x UNION ALL SELECT 8 x UNION ALL SELECT 9 x) ones
CROSS JOIN
(SELECT 0 x UNION ALL SELECT 10 x UNION ALL SELECT 20 x UNION ALL SELECT 30 x UNION ALL SELECT 40 x UNION ALL SELECT 50 x UNION ALL SELECT 60 x UNION ALL SELECT 70 x UNION ALL SELECT 80 x UNION ALL SELECT 90 x) tens;

1
2
3
4
5

# 第11章

# 11 - 1

重写以下使用简单case表达式的查询，使其使用搜索型case表达式实现相同的结果。尽量少使用when子句。

SELECT emp_id,
       CASE title
           WHEN 'President' THEN 'Management'
           WHEN 'Vice President' THEN 'Management'
           WHEN 'Treasurer' THEN 'Management'
           WHEN 'Loan Manager' THEN 'Management'
           WHEN 'Operations Manager' THEN 'Operations'
           WHEN 'Head Teller' THEN 'Operations'
           WHEN 'Teller' THEN 'Operations'
           ELSE 'Unknown'
           END
FROM employee;
SELECT emp_id,
       CASE
           WHEN title LIKE '%President' OR title = 'Loan Manager' OR title = 'Treasurer' THEN 'Management'
           WHEN title LIKE '%Teller' OR title = 'Operations Manager' THEN 'Operations'
           ELSE 'Unknown'
           END
FROM employee;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 11 - 2

重写以下查询，使结果集包含一行四列（每列对应一个分行）。将这四列命名为branch_1到branch_4。

mysql> SELECT open_branch_id, COUNT(*)
    -> FROM account
    -> GROUP BY open_branch_id;
+----------------+----------+
| open_branch_id | COUNT(*) |
+----------------+----------+
|              1 |        8 |
|              2 |        7 |
|              3 |        3 |
|              4 |        6 |
+----------------+----------+
4 rows in set (0.00 sec)
mysql> SELECT
    -> SUM(CASE WHEN open_branch_id = 1 THEN 1 ELSE 0 END) branch_1,
    -> SUM(CASE WHEN open_branch_id = 2 THEN 1 ELSE 0 END) branch_2,
    -> SUM(CASE WHEN open_branch_id = 3 THEN 1 ELSE 0 END) branch_3,
    -> SUM(CASE WHEN open_branch_id = 4 THEN 1 ELSE 0 END) branch_4
    -> FROM account;
+-----------+-----------+-----------+-----------+
| branch_1  | branch_2  | branch_3  | branch_4  |
+-----------+-----------+-----------+-----------+
|         8 |         7 |         3 |         6 |
+-----------+-----------+-----------+-----------+
1 row in set (0.02 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 第12章

# 12 - 1

生成一个事务，将50美元从弗兰克·塔克（Frank Tucker）的货币市场账户转到他的支票账户。你需要在transaction表中插入两行数据，并在account表中更新两行数据。

START TRANSACTION;
SELECT i.cust_id,
       (SELECT a.account_id
        FROM account a
        WHERE a.cust_id = i.cust_id
          AND a.product_cd = 'MM') mm_id,
       (SELECT a.account_id
        FROM account a
        WHERE a.cust_id = i.cust_id
          AND a.product_cd = 'chk') chk_id
INTO @cst_id, @mm_id, @chk_id
FROM individual i
WHERE i.fname = 'Frank'
  AND i.lname = 'Tucker';
INSERT INTO transaction (txn_id, txn_date, account_id, txn_type_cd, amount)
VALUES (NULL, now(), @mm_id, 'CDT', 50);
INSERT INTO transaction (txn_id, txn_date, account_id, txn_type_cd, amount)
VALUES (NULL, now(), @chk_id, 'DBT', 50);
UPDATE account
SET last_activity_date = now(),
    avail_balance = avail_balance - 50
WHERE account_id = @mm_id;
UPDATE account
SET last_activity_date = now(),
    avail_balance = avail_balance + 50
WHERE account_id = @chk_id;
COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 第13章

# 13 - 1

修改account表，使客户每种产品不能拥有超过一个账户。

ALTER TABLE account
    ADD CONSTRAINT account_unq1 UNIQUE (cust_id, product_cd);

1
2

# 13 - 2

在transaction表上生成一个多列索引，该索引可用于以下两个查询：

SELECT txn_date, account_id, txn_type_cd, amount
FROM transaction
WHERE txn_date > cast('2008-12-31 23:59:59' as datetime);
SELECT txn_date, account_id, txn_type_cd, amount
FROM transaction
WHERE txn_date > cast('2008-12-31 23:59:59' as datetime)
  AND amount < 1000;
CREATE INDEX txn_idx01
    ON transaction (txn_date, amount);

1
2
3
4
5
6
7
8
9

# 第14章

# 14 - 1

创建一个视图，查询employee表，并在不使用where子句进行查询时生成以下输出：

+-------------------+-------------------+
| supervisor_name   | employee_name     |
+-------------------+-------------------+
| NULL              | Michael Smith     |
| Michael Smith     | Susan Barker      |
| Michael Smith     | Robert Tyler      |
| Robert Tyler      | Susan Hawthorne   |
| Susan Hawthorne   | John Gooding      |
| Susan Hawthorne   | Helen Fleming     |
| Helen Fleming     | Chris Tucker      |
| Helen Fleming     | Sarah Parker      |
| Helen Fleming     | Jane Grossman     |
| Susan Hawthorne   | Paula Roberts     |
| Paula Roberts     | Thomas Ziegler    |
| Paula Roberts     | Samantha Jameson  |
| Susan Hawthorne   | John Blake        |
| John Blake        | Cindy Mason       |
| John Blake        | Frank Portman     |
| Susan Hawthorne   | Theresa Markham   |
| Theresa Markham   | Beth Fowler       |
| Theresa Markham   | Rick Tulman       |
+-------------------+-------------------+
18 rows in set (1.47 sec)
mysql> CREATE VIEW supervisor_vw
    -> (supervisor_name,
    ->  employee_name
    -> )
    -> AS
    -> SELECT concat(spr.fname, ' ', spr.lname),
    ->        concat(emp.fname, ' ', emp.lname)
    -> FROM employee emp
    ->          LEFT OUTER JOIN employee spr
    ->                          ON emp.superior_emp_id = spr.emp_id;
Query OK, 0 rows affected (0.12 sec)
mysql> SELECT *
    -> FROM supervisor_vw;
+-------------------+-------------------+
| supervisor_name   | employee_name     |
+-------------------+-------------------+
| NULL              | Michael Smith     |
| Michael Smith     | Susan Barker      |
| Michael Smith     | Robert Tyler      |
| Robert Tyler      | Susan Hawthorne   |
| Susan Hawthorne   | John Gooding      |
| Helen Fleming     | Chris Tucker      |
| Helen Fleming     | Sarah Parker      |
| Helen Fleming     | Jane Grossman     |
| Susan Hawthorne   | Paula Roberts     |
| Paula Roberts     | Thomas Ziegler    |
| Paula Roberts     | Samantha Jameson  |
| Susan Hawthorne   | John Blake        |
| John Blake        | Cindy Mason       |
| John Blake        | Frank Portman     |
| Susan Hawthorne   | Theresa Markham   |
| Theresa Markham   | Beth Fowler       |
| Theresa Markham   | Rick Tulman       |
+-------------------+-------------------+
18 rows in set (0.17 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

# 14 - 2

银行行长希望有一份报告，显示每个分行的名称和所在城市，以及在该分行开设的所有账户的总余额。创建一个视图来生成这些数据。

mysql> CREATE VIEW branch_summary_vw
    -> (branch_name,
    ->  branch_city,
    ->  total_balance
    -> )
    -> AS
    -> SELECT b.name, b.city, sum(a.avail_balance)
    -> FROM branch b
    ->          INNER JOIN account a
    ->                     ON b.branch_id = a.open_branch_id
    -> GROUP BY b.name, b.city;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT *
    -> FROM branch_summary_vw;
+----------------+-------------+------------------+
| branch_name    | branch_city | total_balance    |
+----------------+-------------+------------------+
| Headquarters   | Waltham     | 27882.57         |
| Quincy Branch  | Quincy      | 53270.25         |
| So. NH Branch  | Salem       | 68240.32         |
| Woburn Branch  | Woburn      | 21361.32         |
+----------------+-------------+------------------+
4 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 第15章

# 15 - 1

编写一个查询，列出银行模式（bank schema）中的所有索引，并包含表名。

mysql> SELECT DISTINCT table_name, index_name
    -> FROM information_schema.statistics
    -> WHERE table_schema = 'bank';
+----------------+-------------------+
| table_name     | index_name        |
+----------------+-------------------+
| account        | PRIMARY           |
| account        | account_unq1      |
| account        | fk_product_cd     |
| account        | fk_a_branch_id    |
| account        | fk_a_emp_id       |
| account        | acc_bal_idx       |
| branch         | PRIMARY           |
| business       | PRIMARY           |
| customer       | PRIMARY           |
| department     | PRIMARY           |
| department     | dept_name_idx     |
| employee       | PRIMARY           |
| employee       | fk_dept_id        |
| employee       | fk_e_branch_id    |
| employee       | fk_e_emp_id       |
| individual     | PRIMARY           |
| officer        | PRIMARY           |
| officer        | fk_o_cust_id      |
| product        | PRIMARY           |
| product        | fk_product_type_cd |
| product_type   | PRIMARY           |
| transaction    | PRIMARY           |
| transaction    | fk_t_account_id   |
| transaction    | fk_teller_emp_id  |
| transaction    | fk_exec_branch_id |
| transaction    | txn_idx01         |
+----------------+-------------------+
26 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# 15 - 2

编写一个查询，生成可用于在bank.employee表上创建所有索引的输出。输出格式应为： "ALTER TABLE <表名> ADD INDEX <索引名> (<列列表>)"

mysql> SELECT concat(
    ->      CASE
    ->           WHEN st.seq_in_index = 1 THEN
    ->              concat('ALTER TABLE ', st.table_name, ' ADD',
    ->                   CASE
    ->                         WHEN st.non_unique = 0 THEN ' UNIQUE '
    ->                       ELSE ' '
    ->                     END,
    ->                   'INDEX ',
    ->                 st.index_name, ' (', st.column_name)
    ->          ELSE concat('    ', st.column_name)
    ->      END,
    ->      CASE
    ->          WHEN st.seq_in_index =
    ->             (SELECT max(st2.seq_in_index)
    ->               FROM information_schema.statistics st2
    ->              WHERE st2.table_schema = st.table_schema
    ->                  AND st2.table_name = st.table_name
    ->                  AND st2.index_name = st.index_name)
    ->               THEN ');'
    ->          ELSE ''
    ->      END
    ->    ) index_creation_statement
    -> FROM information_schema.statistics st
    -> WHERE st.table_schema = 'bank'
    ->      AND st.table_name = 'employee'
    -> ORDER BY st.index_name, st.seq_in_index;
+-------------------------------------------------------------------+
| index_creation_statement                                            |
+-------------------------------------------------------------------+
| ALTER TABLE employee ADD INDEX fk_dept_id (dept_id);                 |
| ALTER TABLE employee ADD INDEX fk_e_branch_id (assigned_branch_id); |
| ALTER TABLE employee ADD INDEX fk_e_emp_id (superior_emp_id);        |
| ALTER TABLE employee ADD UNIQUE INDEX PRIMARY (emp_id);              |
+-------------------------------------------------------------------+
4 rows in set (0.20 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

← 附录B MySQL对SQL语言的扩展