练习题
1.1
编写一条 CREATE TABLE 语句,用来创建一个包含表 1-A 中所列各项的表 Addressbook (地址簿),并为 regist_no (注册编号)列设置主键约束
表1-A 表 Addressbook (地址簿)中的列
CREATE TABLE Addressbook (
regist_no INTEGER PRIMARY KEY,
NAME VARCHAR ( 128 ) NOT NULL,
address VARCHAR ( 256 ) NOT NULL,
tel_no CHAR ( 10 ),
mail_address CHAR ( 20 )
);
1.2
假设在创建练习1.1中的 Addressbook 表时忘记添加如下一列 postal_code (邮政编码)了,请把此列添加到 Addressbook 表中。
列名 : postal_code
数据类型 :定长字符串类型(长度为 8)
约束 :不能为 NULL
ALTER TABLE Addressbook ADD postal_code CHAR(8) NOT NULL;
1.3
编写 SQL 语句来删除 Addressbook 表。
DROP TABLE Addressbook;
1.4
编写 SQL 语句来恢复删除掉的 Addressbook 表。
CREATE TABLE Addressbook (
regist_no INTEGER PRIMARY KEY,
NAME VARCHAR ( 128 ) NOT NULL,
address VARCHAR ( 256 ) NOT NULL,
tel_no CHAR ( 10 ),
mail_address CHAR ( 20 ),
postal_code CHAR(8) NOT NULL
);
练习题-第一部分
2.1
编写一条SQL语句,从 product
(商品)表中选取出“登记日期( regist
在2009年4月28日之后”的商品,查询结果要包含 product_name
和 regist_date
两列。
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;
返回product中purchase_price = NULL的所有商品
②
SELECT *
FROM product
WHERE purchase_price <> NULL;
返回product中product_name不为NULL的所有商品
③
SELECT *
FROM product
WHERE product_name > NULL;
返回product中product_name大于NULL的所有商品
2.3
代码清单2-22(2-2节)中的SELECT语句能够从product表中取出“销售单价(saleprice)比进货单价(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
sale_price >= purchase_price + 500;
2.4
请写出一条SELECT语句,从product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润(别名设定为profit)。
提示:销售单价打九折,可以通过saleprice列的值乘以0.9获得,利润可以通过该值减去purchase_price列的值获得。
SELECT
product_name,
product_type,
saleprice * 0.9 - purchase_price AS profit
FROM
product
WHERE
saleprice * 0.9 - purchase_price > 100
AND ( product_type = '办公用品' OR product_type = '厨房用具' );
练习题-第二部分
2.5
请指出下述SELECT语句中所有的语法错误。
SELECT product_id, SUM(product_name)
--本SELECT语句中存在错误。
FROM product
GROUP BY product_type
WHERE regist_date > '2009-09-01';
1.不可对字符型product_name进行SUM聚合
2.WHERE应该在GROUP BY之前
3.GROUP BY 字段(product_type)与 SELECT 字段不同(product_id)
2.6
请编写一条SELECT语句,求出销售单价(sale_price
列)合计值大于进货单价(purchase_price
列)合计值1.5倍的商品种类。执行结果如下所示。
product_type | sum | sum
-------------+------+------
衣服 | 5000 | 3300
办公用品 | 600 | 320
SELECT
product_type,
SUM( sale_price ) AS sum,
SUM( purchase_price ) AS sum
FROM
product
GROUP BY
product_type
HAVING
SUM( saleprice ) > SUM( purchase_price ) * 1.5;
2.7
此前我们曾经使用SELECT语句选取出了product(商品)表中的全部记录。当时我们使用了ORDERBY子句来指定排列顺序,但现在已经无法记起当时如何指定的了。请根据下列执行结果,思考ORDERBY子句的内容。
SELECT
*
FROM
product
ORDER BY
regist_date DESC,
sale_price;
3.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 AS SELECT
product_name,
sale_price,
regist_date
FROM
product
WHERE
sale_price >= 1000
AND regist_date = '2009-09-20';
3.2
向习题一中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果呢?
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
解析:插入时将会报错。
视图插入数据时,原表也会插入数据,而原表数据插入时不满足约束条件,所以会报错。(因为 ViewPractice5_1 的原表有三个带有 NOT NULL 约束的字段)
3.3
请根据如下结果编写 SELECT 语句,其中 sale_price_all 列为全部商品的平均销售单价。
product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001 | T恤衫 | 衣服 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 办公用品 | 500 | 2097.5000000000000000
0003 | 运动T恤 | 衣服 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 厨房用具 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 厨房用具 | 6800 | 2097.5000000000000000
0006 | 叉子 | 厨房用具 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 厨房用具 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 2097.5000000000000000
SELECT
product_id,
product_name,
product_type,
sale_price,
( SELECT AVG( sale_price ) FROM product ) AS sale_price_all
FROM
product;
3.4
请根据习题一中的条件编写一条 SQL 语句,创建一幅包含如下数据的视图(名称为AvgPriceByType)。
product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001 | T恤衫 | 衣服 | 1000 |2500.0000000000000000
0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000
0003 | 运动T恤 | 衣服 | 4000 |2500.0000000000000000
0004 | 菜刀 | 厨房用具 | 3000 |2795.0000000000000000
0005 | 高压锅 | 厨房用具 | 6800 |2795.0000000000000000
0006 | 叉子 | 厨房用具 | 500 |2795.0000000000000000
0007 | 擦菜板 | 厨房用具 | 880 |2795.0000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000
提示:其中的关键是 avg_sale_price 列。与习题三不同,这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说,该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。
CREATE VIEW AvgPriceByType AS
SELECT product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price)
FROM product p2
WHERE p1.product_type = p2.product_type
GROUP BY p1.product_type) AS avg_sale_price
FROM product p1;
SELECT * FROM AvgPriceByType;
3.5
运算或者函数中含有 NULL 时,结果全都会变为NULL ?(判断题)
正确
3.6
对本章中使用的 product(商品)表执行如下 2 条 SELECT 语句,能够得到什么样的结果呢?
①
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);
解析:该查询语句仅仅取出了 purchase_price 不是 500、2800、5000 的商品,而不包含 purchase_price 为 NULL 的商品,这是因为 谓词无法与 NULL 进行比较。
②
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
解析:代码执行之前,你可能会认为该语句会返回和查询 ① 同样的结果,实际上它却返回了零条记录,这是因为 NOT IN 的参数中不能包含 NULL,否则,查询结果通常为空。
3.7
按照销售单价( sale_price)对练习 3.6 中的 product(商品)表中的商品进行如下分类。
- 低档商品:销售单价在1000日元以下(T恤衫、办公用品、叉子、擦菜板、 圆珠笔)
- 中档商品:销售单价在1001日元以上3000日元以下(菜刀)
- 高档商品:销售单价在3001日元以上(运动T恤、高压锅)
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句,结果如下所示。
执行结果
low_price | mid_price | high_price
----------+-----------+------------
5 | 1 | 2
SELECT SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END) AS high_price
FROM product;
4.1
找出 product 和 product2 中售价高于 500 的商品的基本信息。
SELECT * FROM product WHERE sale_price >500
UNION ALL
SELECT * FROM product2 WHERE sale_price >500;
4.2
借助对称差的实现方式, 求product和product2的交。
-- 两个集合的交集就是两个集合的并集减去两个集合的对称差
-- 先求并集
SELECT * FROM
(SELECT * FROM product
UNION
SELECT * FROM product2) T
-- 减去对称差
WHERE product_id NOT IN
(SELECT product_id
FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION
SELECT product_id
FROM product2
WHERE product_id NOT IN (SELECT product_id FROM product));
4.3
每类商品中售价最高的商品都在哪些商店有售 ?
-- 取出想要的字段
SELECT sp.shop_id, sp.shop_name, sp.product_id ,p.product_type
FROM shopproduct sp
JOIN product p
ON sp.product_id=p.product_id
WHERE sp.product_id in
-- 过滤每个类型售价最高的商品
(SELECT product_id
FROM product p1
JOIN (SELECT product_type,
MAX(sale_price) as max_price
FROM product
GROUP BY product_type) p2
ON p1.product_type=p2.product_type AND p1.sale_price=p2.max_price);
4.4
分别使用内连结和关联子查询每一类商品中售价最高的商品。
--方法1:关联子查询
SELECT product_type, product_name, sale_price
FROM product AS P1
WHERE sale_price = (SELECT max(sale_price)
FROM product AS P2
WHERE P1.product_type = P2.product_type
--方法2:先连接,再过滤
SELECT P1.product_id,P1.product_name,P1.product_type,P1.sale_price,P2.max_price
FROM product AS P1
INNER JOIN
(SELECT product_type,max(sale_price) AS max_price
FROM product
GROUP BY product_type) AS P2
ON P1.product_type = P2.product_type
WHERE P1.sale_price= p2.max_price;
4.5
用关联子查询实现:在 product 表中,取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
SELECT product_id, product_name, sale_price,
(SELECT SUM(sale_price) FROM product AS P2
-- ①价格更低的 ②价格相等,product_id不大于的(不包括下一行)
WHERE ((P2.sale_price < P1.sale_price) OR (P2.sale_price = P1.sale_price AND P2.product_id<=P1.product_id))) AS cum_price
FROM product AS P1
ORDER BY sale_price,product_id;