image.png
作业在Windows 10 + MySQL8.0上测试过。
第一章 数据库和SQL
1.1 编写一条CREATE TABLE语句，用来创建一个包含表1-A中所列各项的表Addressbook（地址薄），并为regist_no（注册编号）列设置主键约束。
表1-A 表Addressbook（地址薄）中的列
| 列的含义 | 列的名称 | 数据类型 | 约束 |
|---|---|---|---|
| 注册编号 | regist_no | 整数型 | 不能为NULL、主键 |
| 姓名 | name | 可变长字符串类型（长度为128） | 不能为NULL |
| 住址 | address | 可变长字符串类型（长度为128） | 不能为NULL |
| 电话号码 | tel_no | 定长字符串类型（长度为10） | |
| 邮箱地址 | mail_address | 定长字符串类型（长度为20） |
答案：
CREATE TABLE Addressbook (
regist_no int not null,
name varchar(128) not null,
address varchar(128) not null,
tel_no char(10),
mail_address char(20),
PRIMARY KEY(regist_no));
1.2 假设在创建练习1.1中的Addressbook表时忘记添加如下一列postal_code（邮政编码）了，请把此列添加到Addressbook表中。
列明： postal_code
数据类型： 定长字符串类型（长度为8）
约束： 不能为NULL
答案：
ALTER TABLE Addressbook ADD column (postal_code char(8) not null);
1.3 编写SQL语句来删除Addressbook表。
答案：
DROP TABLE Addressbook;
1.4 编写SQL语句来恢复删除掉的Addressbook表。
答案：
使用drop删除的表不能恢复，只能重建。
CREATE TABLE Addressbook (
regist_no int not null,
name varchar(128) not null,
address varchar(128) not null,
tel_no char(10),
mail_address char(20),
PRIMARY KEY(regist_no));
第二章查询基础
2.1 编写一条SQL语句，从Product（商品）表中选取“登记日期（regist_date）在2009年4月28日之后”的商品。查询结果要包含product_name和regist_date两列。
答案：
首先创建表格。
CREATE TABLE Product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
然后插入数据：
--MySQL
-- DML:插入数据
START TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;
SQL语句如下：
SELECT product_name, regist_date FROM Product WHERE regist_date > 2009-04-28;
2.2 请说出对Product表执行如下3条SELECT语句时的返回结果。
SELECT * FROM Product WHERE purchase_price = NULL;
SELECT * FROM Product WHERE purchase_price <> NULL;
SELECT * FROM Product WHERE purchase_price > NULL;
答案：都是返回空集，即: Empty set. 因为NULL是不确定的。不确定的值是不能参与比较的。
2.3 代码清单2-22（2-2节）中的SELECT语句能够从Product表中取出“销售单价（sale_price）比进货单价（purchase_price）高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示。
执行结果：
| product_name | sale_price | purchase_price |
|---|---|---|
| T恤衫 | 1000 | 500 |
| 运动T恤 | 4000 | 2800 |
| 高压锅 | 6800 | 5000 |
答案：
SELECT product_name, sale_price, purchase_price FROM Product WHERE 500 <= sale_price - purchase_price;
SELECT product_name, sale_price, purchase_price FROM Product WHERE sale_price-500 >= purchase_price;
2.4 请写出一条SELECT语句，从Product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润（别名设为profit）。【提示：销售单价打九折，可以通过sale_price列的值乘以0.9获得，利润可以通过该值减去purchase_price列的值获得。】
答案：
SELECT
product_name, product_type, 0.9*sale_price-purchase_price AS profit
FROM
Product
WHERE (0.9*sale_price-purchase_price > 100 and (product_type='办公用品' or product_type = '厨房用具'))；
第三章聚合与排序
3.1 请指出下述SELECT语句中所有语法错误。
SELECT product_id, SUM(product_name)
FROM product
GROUP BY product_type
WHERE regist_date > '2009-09-01';
答案：
- SUM()函数只能对数值操作，不能对字符串操作。
- WHERE语句不能在GROUP BY之后
- SELECT子句中存在GROUP BY子句中未指定的列（product_id）。
3.2 请编写一条SELECT语句，求出销售单价（sale_price列）合计值大于进货单价（purchase_price列）合计值的1.5倍的商品种类。执行结果如下所示：
| product_type | sum | sum |
|---|---|---|
| 衣服 | 5000 | 3300 |
| 办公用品 | 600 | 320 |
说明：第一个sum列，SUM（sale_price)的结果；第二个sum列，SUM(purchase_price)的结果。
答案：
SELECT product_type, sum(sale_price), sum(purchase_price)
FROM product
GROUP BY product_type
HAVING sum(sale_price) > 1.5*sum(purchase_price)
说明：HAVING子句和WHERE子句有些相似。 人为细分的话，可以认为：WHERE子句 = 指定行所对应的条件；HAVING子句 = 指定组所对应的条件。
3.3 此前我们曾经使用SELECT语句选取出了Product（商品）表中的全部记录。当时我们使用了ORDER BY子句来指定排列顺序，但现在已经无法记起当时是如何指定的了。请根据下列执行结果，思考ORDER BY子句的内容。
执行结果：
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
|---|---|---|---|---|---|
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | |
| 0008 | 圆珠笔 | 办公用品 | 100 | 2009-11-11 | |
| 0006 | 叉子 | 厨房用具 | 500 | 2009-09-20 | |
| 0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
答案：
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM Product
GROUP BY regist_date DESC, sale_price;
第四章数据更新
4.1 A先生在自己的计算机上，使用CREATE TABLE语句创建了一张空的Product表，并执行了如下的SQL语句向其中插入数据。
BEGIN TRANSACTION;
INSERT INTO Product VALUES('0001','T恤衫','衣服', 1000, 500, '2008-09-20');
INSERT INTO Product VALUES('0002','打孔器','办公用品', 500, 320, '2008-09-11');
INSERT INTO Product VALUES('0003','运动T恤','衣服', 4000, 2800, NULL);
紧接着，B先生使用其他计算机连接上该数据库，执行了如下SELECT语句。这是B先生得到怎样的查询结果呢？
SELECT * FROM Product;
答案：因为A先生最后没有输入COMMIT，所以，B先生，什么也查不到。
4.2 如下所示，有一张包含3条记录的Product表。
| 商品编号 | 商品名称 | 商品种类 | 销售单价 | 进货单价 | 登记日期 |
|---|---|---|---|---|---|
| 0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 |
使用如下的INSERT语句复制这三行数据，应该就能够将表中的数据增加为6行。请说出该语句执行的结果。
INSERT INTO Product SELECT * FROM Product;
答案：违反主键约束，所以，一行也插不进去。
4.3 以练习4.2中的Product表为基础，再创建另外一张包含利润列的新表ProductMargin（商品利润）。
CREATE TABLE ProductMargin(
product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
sale_price INTEGER,
purchase_price INTEGER,
margin INTEGER,
PRIMARY KEY(product_id)
);
请写出向上述表中插入如下数据的SQL语句，其中的利润可以简单地通过对Product表中的数据进行计算（销售单价-进货单价）得出。
| product_id | product_name | sale_price | purchase_price | margin |
|---|---|---|---|---|
| 0001 | T恤衫 | 1000 | 500 | 500 |
| 0002 | 打孔器 | 500 | 320 | 180 |
| 0003 | 运动T恤 | 4000 | 2800 | 1200 |
答案：
INSERT INTO ProductMargin (product_id, product_name, sale_price, purchase_price, margin)
SELECT product_id, product_name, sale_price, purchase_price, sale_price - purchase AS margin
FROM Product;
4.4 对练习4.3中的ProductMargin表的数据进行如下更改。
1、将运动T恤衫的销售单价从4000日元下降至3000日元。
2、根据上述结果再次计算运动T恤衫的利润。
更改后的ProductMargin表如下所示。请写出能够实现该变更的SQL语句。
| product_id | product_name | sale_price | purchase_price | margin |
|---|---|---|---|---|
| 0001 | T恤衫 | 1000 | 500 | 500 |
| 0002 | 打孔器 | 500 | 320 | 180 |
| 0003 | 运动T恤 | 3000 | 2800 | 200 |
答案：
UPDATE ProductMargin
SET sale_price = 3000
WHERE product_id = '0003';
UPDATE ProductMargin
SET margin = sale_price - purchase_price
WHERE product_id = '0003';
第五章复杂查询
5.1 创建出满足下述三个条件的视图（视图名称为ViewPractice5_1）。使用Product（商品）表作为参照表，假设表中包含初始状态的8行数据。
条件1：销售单价大于等于1000日元。
条件2：登记日期是2009年9月20日。
条件3：包含商品名称、销售单价和登记日期三列。
对该视图执行SELECT语句的结果如下所示。
SELECT * FROM ViewPractice5_1;
执行结果：
| product_name | sale_price | regist_date |
|---|---|---|
| T恤衫 | 1000 | 2009-09-20 |
| 菜刀 | 3000 | 2009-09-20 |
答案：
CREATE VIEW Viewpractice5_1 (product_name,sale_price,regist_date)
AS
SELECT product_name, sale_price, regist_date
FROM product
WHERE sale_price>=1000 AND regist_date = '2009-09-20';
5.2 向习题5.1中创建的视图ViewPractice5_1中插入如下数据，会得到什么样的结果呢？
INSERT INTO ViewProctice5_1 VALUES ('刀子', 300, '2009-11-02');
答案：错误。错误信息：Field of view 'shop.viewpractice5_1' underlying table doesn't have a default value。原因是，向视图中插入数据就是向视图对应的表中插入数据。但是表中的很多列都设成了NOT NULL，比如product_id，因为没有对应的输入值，所以插入失败。
5.3 请根据如下结果编写SELECT语句，其中sale_price_all列为全部商品的平均销售单价。
执行结果：
| product_id | product_name | product_type | sale_price | sale_price_all |
|---|---|---|---|---|
| 0001 | T恤衫 | 衣服 | 1000 | 2097.5 |
| 0002 | 打孔器 | 办公用品 | 500 | 2097.5 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2097.5 |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2097.5 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 2097.5 |
| 0006 | 叉子 | 厨房用具 | 500 | 2097.5 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 2097.5 |
| 0008 | 圆珠笔 | 办公用品 | 100 | 2097.5 |
答案：
子查询：
SELECT
product_id, product_name, product_type, sale_price,
(SELECT AVG(sale_price) from product)
AS avg_sale_price
FROM product;
5.4 请根据习题5.1中的条件编写一条SQL语句，创建一副包含如下数据的视图（名称为AvgPriceByType）。
执行结果：
| product_id | product_name | product_type | sale_price | avg_sale_price |
|---|---|---|---|---|
| 0001 | T恤衫 | 衣服 | 1000 | 2500 |
| 0002 | 打孔器 | 办公用品 | 500 | 300 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2500 |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2795 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 2795 |
| 0006 | 叉子 | 厨房用具 | 500 | 2795 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 2795 |
| 0008 | 圆珠笔 | 办公用品 | 100 | 300 |
答案：
SELECT product_id, product_name, product_type, sale_price,
(select avg(sale_price)
FROM product as P2
WHERE P1.product_type = P2.product_type
GROUP BY P2.product_type)
AS avg_sale_price
FROM product
AS P1;
第六章函数、谓词、CASE表达式
6.1 对本章中使用的Product表执行如下2条SELECT语句，能够得到什么样的结果呢？
第一条：
SELECT product_name, purchase_price FROM Product WHERE purchase NOT IN (500,2800,5000);
第二条：
SELECT product_name, purchase_price FROM Product WHERE purchase NOT IN (500,2800,5000, NULL);
6.2 按照销售单价（sale_price）对练习6.1中的Product表中的商品进行如下分类。
- 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
- 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
- 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的SELECT语句，结果如下所示。
执行结果：
| low_price | mid_price | high_price |
|---|---|---|
| 5 | 1 | 2 |
第七章集合运算
7.1 请说出下述SELECT语句的结果。
SELECT * FROM Product
UNION
SELECT * FROM Product
INTERSECT
SELECT * FROM Product ORDER BY product_id;
7.2 7-2节的代码清单7-11中列举的外联结的结果中，高压锅和圆珠笔2条记录的商店编号（shop_id）和商店名称（shop_name）都是NULL。请使用字符串“不确定”替换其中的NULL。期望结果如下所示。
执行结果：
| shop_id | shop_name | product_id | product_name | sale_price |
|---|---|---|---|---|
| 000A | 东京 | 0002 | 打孔器 | 500 |
| 000A | 东京 | 0003 | 运动T恤 | 4000 |
| 000A | 东京 | 0001 | T恤衫 | 1000 |
| 000B | 名古屋 | 0006 | 叉子 | 500 |
| 000B | 名古屋 | 0002 | 打孔器 | 500 |
| 000B | 名古屋 | 0003 | 运动T恤 | 4000 |
| 000B | 名古屋 | 0004 | 菜刀 | 3000 |
| 000B | 名古屋 | 0007 | 擦菜板 | 880 |
| 000C | 大阪 | 0006 | 叉子 | 500 |
| 000C | 大阪 | 0007 | 擦菜板 | 880 |
| 000C | 大阪 | 0006 | 叉子 | 500 |
| 000C | 大阪 | 0004 | 菜刀 | 3000 |
| 000D | 福冈 | 0001 | T恤衫 | 1000 |
| 不确定 | 不确定 | 0005 | 高压锅 | 6800 |
| 不确定 | 不确定 | 0008 | 圆珠笔 | 100 |
第八章SQL高级处理
8.1 请说出针对本章中使用的Product表执行如下SELECT语句所能得到的结果。
SELECT product_id, product_name, sale_price, MAX( sale_price) OVER (ORDER BY product_id) AS current_max_price FROM Product;
8.2 继续使用Product表，计算出按照登记日期（regist_date）升序进行排列的各日期单价（sale_price）的总额。排序是需要登记日期为NULL的“运动T恤”记录排在第1位（也就是将其看作比其他日期都早）。
第九章 通过应用程序连接数据库（略）
主要是不懂Java。(/汗)
网友评论