SQL练习

最新推荐文章于 2022-09-12 02:30:00 发布

qq_36226141

最新推荐文章于 2022-09-12 02:30:00 发布

阅读量607

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_36226141/article/details/119743182

版权

练习题

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;

qq_36226141

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL练习

练习题1.1编写一条 CREATE TABLE 语句，用来创建一个包含表 1-A 中所列各项的表 Addressbook （地址簿），并为 regist_no （注册编号）列设置主键约束表1-A 表 Addressbook （地址簿）中的列1.2假设在创建练习1.1中的 Addressbook 表时忘记添加如下一列 postal_code （邮政编码）了，请把此列添加到 Addressbook 表中。列名： postal_code数据类型：定长字符串类型（长度为 8）约.
复制链接

扫一扫