SQL基础教程练习题

最新推荐文章于 2022-04-18 23:59:56 发布

owl_hub

最新推荐文章于 2022-04-18 23:59:56 发布

阅读量1.4k

点赞数

本文链接：https://blog.csdn.net/py_xiaoguaishou/article/details/121315218

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

SQL基础教程练习题

记录下学习过程，陆续更新
测试环境：CentOS 7.9 +mysql5.7
官网答案资料：
https://pan.baidu.com/s/1rYHNXXYdu4qrj0w1Q8CGrA
提取码：18vg

准备数据表

提前准备Product数据表，以及插入数据

--创建数据表
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));

--插入数据
INSERT INTO Product VALUES ('001','TT血衫','衣服',1000,500,'2009-09-20');
INSERT INTO Product VALUES ('002','打孔器','办公用品',500,320,'2009-09-11');
INSERT INTO Product VALUES ('003','运动T血','衣服',4000,2800,NULL);
INSERT INTO Product VALUES ('004','菜刀','厨房用具',3000,2800,'2009-09-20');
INSERT INTO Product VALUES ('005','高压锅','厨房用具',6800,5000,'2009-01-15');
INSERT INTO Product VALUES ('006','叉子','厨房用具',500,NULL,'2009-08-20');
INSERT INTO Product VALUES ('007','擦菜板','厨房用具',880,790,'2008-04-28');
INSERT INTO Product VALUES ('008','圆珠笔','办公用品',100,NULL,'2009-11-11');

--查看表数据
select * from Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 001        | TT血衫       | 衣服         |       1000 |            500 | 2009-09-20  |
| 002        | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 003        | 运动T血      | 衣服         |       4000 |           2800 | NULL        |
| 004        | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20  |
| 005        | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15  |
| 006        | 叉子         | 厨房用具     |        500 |           NULL | 2009-08-20  |
| 007        | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28  |
| 008        | 圆珠笔       | 办公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

第一章练习题：

1、编写一条CREATE TABLE语句，用来创建一个包含表1-A中所列各项的表Addressbook (地址簿),并为regist_no(注册编号）列设置主键约束。

解答：

mysql> CREATE TABLE Addressbook(
    -> regist_no INTEGER NOT NULL,
    -> name VARCHAR(128) NOT NULL,
    -> addresss VARCHAR(256) NOT NULL,
    -> tel_no CHAR(10),
    -> mail_address CHAR(20),
    -> PRIMARY KEY (regist_no)
    ->);

2、假设在创建练习1.1中的Addressbook表时忘记添加如下一列postal_code（邮政编码）了，请把此列添加到Addressbook表中。

列名: postal_code
数据类型：定长字符串类型（长度为8）
约束：不能为NULL

mysql> ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;
Query OK, 0 rows affected (0.02 sec)

3、编写SQL语句来删除Addressbook表。

mysql> DROP TABLE Addressbook;

4、编写SQL语句来恢复删除掉的Addressbook表。

耍我完？

第二章练习题

1、编写一条SQL语句，从Product（商品）表中选取出“登记日期（regist_date）在2009年4月28日之后”的商品。查询结果要包含product_name 和regist_date两列。

mysql> SELECT product_name,regist_date FROM Product WHERE regist_date>'2009-4-8';
+--------------+-------------+
| product_name | regist_date |
+--------------+-------------+
| TT血衫       | 2009-09-20  |
| 打孔器       | 2009-09-11  |
| 菜刀         | 2009-09-20  |
| 叉子         | 2009-08-20  |
| 圆珠笔       | 2009-11-11  |
+--------------+-------------+
5 rows in set (0.00 sec)

2、请说出对Product表执行如下3条SELECT语句时的返回结果。

--①
SELECT *
FROM Product
WHERE purchase_price = NULL;

--②
SELECT *
FROM Product
WHERE purchase_price <> NULL;

--③
SELECT *
FROM Product
WHERE product_name > NULL;

不会有结果，不能对NULL使用比较运算符，可以通过IS NULL和IS NOT NULL来取出NULL 和非NULL的数据

3、代码清单2-22（2-2节）中的SELECT语句能够从Product表中取出“销售单价（sale_price)比进货单价（purchase_price)高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示。

mysql> SELECT product_name,sale_price,purchase_price
    ->   FROM Product
    ->   WHERE sale_price - purchase_price >= 500;


mysql> SELECT product_name,sale_price,purchase_price  
    -> FROM Product   
    -> WHERE NOT sale_price - purchase_price < 500;
    
mysql> SELECT product_name,sale_price,purchase_price  
    -> FROM Product   
    -> WHERE NOT sale_price+500 >= purchase_price;

4、请写出一条SELECT语句，从Product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name 列、product_type列以及销售单价打九折之后的利润（别名设定为profit）。

提示：销售单价打九折，可以通过sale_price列的值乘以0.9获得,利润可以通过该值减去purchase_price列的值获得。

mysql> SELECT product_name,product_type,sale_price*0.9 - purchase_price as profit
    -> FROM Product
    -> WHERE (sale_price*0.9 - purchase_price > 100 )and (product_type='办公用品' or product_type='厨房用具');
     
+--------------+--------------+--------+
| product_name | product_type | profit |
+--------------+--------------+--------+
| 打孔器       | 办公用品     |    500 |
+--------------+--------------+--------+
1 row in set (0.00 sec)

SQL基础教程练习题

测试环境：CentOS 7.9 +mysql5.7

准备数据表

提前准备Product数据表，以及插入数据

--创建数据表
mysql> 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> INSERT INTO Product VALUES ('001','TT血衫','衣服',1000,500,'2009-09-20');
mysql> INSERT INTO Product VALUES ('002','打孔器','办公用品',500,320,'2009-09-11');
mysql> INSERT INTO Product VALUES ('003','运动T血','衣服',4000,2800,NULL);
mysql> INSERT INTO Product VALUES ('004','菜刀','厨房用具',3000,2800,'2009-09-20');
mysql> INSERT INTO Product VALUES ('005','高压锅','厨房用具',6800,5000,'2009-01-15');
mysql> INSERT INTO Product VALUES ('006','叉子','厨房用具',500,NULL,'2009-08-20');
mysql> INSERT INTO Product VALUES ('007','擦菜板','厨房用具',880,790,'2008-04-28');
mysql> INSERT INTO Product VALUES ('008','圆珠笔','办公用品',100,NULL,'2009-11-11');

--查看表数据
select * from Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 001        | TT血衫       | 衣服         |       1000 |            500 | 2009-09-20  |
| 002        | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 003        | 运动T血      | 衣服         |       4000 |           2800 | NULL        |
| 004        | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20  |
| 005        | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15  |
| 006        | 叉子         | 厨房用具     |        500 |           NULL | 2009-08-20  |
| 007        | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28  |
| 008        | 圆珠笔       | 办公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

第一章练习题：

1、编写一条CREATE TABLE语句，用来创建一个包含表1-A中所列各项的表Addressbook (地址簿),并为regist_no(注册编号）列设置主键约束。

解答：

mysql> CREATE TABLE Addressbook(
    -> regist_no INTEGER NOT NULL,
    -> name VARCHAR(128) NOT NULL,
    -> addresss VARCHAR(256) NOT NULL,
    -> tel_no CHAR(10),
    -> mail_address CHAR(20),
    -> PRIMARY KEY (regist_no)
    ->);

2、假设在创建练习1.1中的Addressbook表时忘记添加如下一列postal_code（邮政编码）了，请把此列添加到Addressbook表中。

列名: postal_code
数据类型：定长字符串类型（长度为8）
约束：不能为NULL

mysql> ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;
Query OK, 0 rows affected (0.02 sec)

3、编写SQL语句来删除Addressbook表。

mysql> DROP TABLE Addressbook;

4、编写SQL语句来恢复删除掉的Addressbook表。

耍我完？

第二章练习题

1、编写一条SQL语句，从Product（商品）表中选取出“登记日期（regist_date）在2009年4月28日之后”的商品。查询结果要包含product_name 和regist_date两列。

mysql> SELECT product_name,regist_date FROM Product WHERE regist_date>'2009-4-8';
+--------------+-------------+
| product_name | regist_date |
+--------------+-------------+
| TT血衫       | 2009-09-20  |
| 打孔器       | 2009-09-11  |
| 菜刀         | 2009-09-20  |
| 叉子         | 2009-08-20  |
| 圆珠笔       | 2009-11-11  |
+--------------+-------------+
5 rows in set (0.00 sec)

2、请说出对Product表执行如下3条SELECT语句时的返回结果。

--①
SELECT *
FROM Product
WHERE purchase_price = NULL;

--②
SELECT *
FROM Product
WHERE purchase_price <> NULL;

--③
SELECT *
FROM Product
WHERE product_name > NULL;

不会有结果，不能对NULL使用比较运算符，可以通过IS NULL和IS NOT NULL来取出NULL 和非NULL的数据

mysql> SELECT product_name,sale_price,purchase_price
    ->   FROM Product
    ->   WHERE sale_price - purchase_price >= 500;


mysql> SELECT product_name,sale_price,purchase_price  
    -> FROM Product   
    -> WHERE NOT sale_price - purchase_price < 500;
    
mysql> SELECT product_name,sale_price,purchase_price  
    -> FROM Product   
    -> WHERE NOT sale_price+500 >= purchase_price;

提示：销售单价打九折，可以通过sale_price列的值乘以0.9获得,利润可以通过该值减去purchase_price列的值获得。

mysql> SELECT product_name,product_type,sale_price as profit
    -> FROM Product
    -> WHERE (sale_price*0.9 - purchase_price > 100 )and (product_type='办公用品' or product_type='厨房用品');
     
+--------------+--------------+--------+
| product_name | product_type | profit |
+--------------+--------------+--------+
| 打孔器       | 办公用品     |    500 |
+--------------+--------------+--------+
1 row in set (0.00 sec)

第三章练习题

1、请指出下述SELECT语句中所有语法错误。

mysql> SELECT product_id, SUM(product_name)
    -> FROM Product 
    -> GROUP BY product_type;
    -> WHERE regist_date>'2009-09-01'

①SELECT执行顺序

FROM->WHERE-.>GROUP BY -> HAVING -> SELECT -> ORDER BY

②使用GROUP BY语句不能出现聚合建之外的列名

③SUM、AVG只能适用于数值类型的列

2、请编写一条SELECT语句，求出销售单价（sale_Price列）合计值大于进货单价（purchase_price列）合计值的1.5倍的商品种类。执行结果如下所示。

解：

mysql> SELECT product_type,SUM(sale_price),SUM(purchase_price)
    -> FROM Product
    -> GROUP BY product_type
    -> HAVING SUM(sale_price)>SUM(purchase_price)*1.5;

3、此前我们曾经使用SELECT语句选取出了Product（商品）表中的全部记录。当时我们使用了ORDER BY子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考ORDERBY子句的内容。

mysql> SELECT * 
    -> FROM Product
    -> ORDER BY regist_date desc, sale_price;;

第四章

1、代码中有开始事务语句BEGIN TRANSACTION但是缺少事务结束的语句，原表内容不会发生改变

2、会出现报错，主键重复导致的报错

3、

--创建表格
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));


--插入数据
--这里根据insert复制其他表的方法，将Product表中所需的字段选中插入到新的表中ProductMargin
INSERT INTO ProductMargin 
SELECT product_id,product_name,sale_price,purchase_price,sale_price-purchase_price 
From Product;

4、

--这里涉及到了多个操作，需要用到事务
--我这里是基于mysql来操作的
START TRANSACTION;

UPDATE ProductMargin
	SET sale_price=3000
	WHERE product_name='运动T恤';
	
UPDATE ProductMargin
	SET margin = sale_price-purchase_price
	where product_name='运动T恤';
	
commit;

第五章

1、创建出满足下述三个条件的视图（视图名称为ViewPractice5_1）。使用Product（商品）表作为参照表，假设表中包含初始状态的8行数据。
条件1：销售单价大于等于1000日元。
条件2：登记日期是2009年9月20日。
条件3：包含商品名称、销售单价和登记日期三列。

对该视图执行SELECT 语句的结果如下所示。

select * from ViewPractice5_1

+--------------+------------+-------------+
| product_name | sale_price | regist_date |
+--------------+------------+-------------+
| TT血衫       |       1000 | 2009-09-20  |
| 菜刀         |       3000 | 2009-09-20  |
+--------------+------------+-------------+

解答：

create view ViewPeactice5_1
    AS 
    select product_name,sale_price,regist_date
    from Product
    where sale_price>=1000 and regist_date='2009-9-20';
+--------------+------------+-------------+
| product_name | sale_price | regist_date |
+--------------+------------+-------------+
| TT血衫       |       1000 | 2009-09-20  |
| 菜刀         |       3000 | 2009-09-20  |
+--------------+------------+-------------+

2、向以上视图ViewPractice5_1视图中插入一下语句，会得到什么结果？

insert into ViewPeactice5_1 value('刀子',300,'2009-11-02')

报错：

3、请根据如下结果编写select语句，其中sale_price_all列为全部商品的平均销售价

+------------+--------------+--------------+------------+----------------+
| product_id | product_name | product_type | sale_price | sale_price_all |
+------------+--------------+--------------+------------+----------------+
| 001        | TT血衫       | 衣服         |       1000 |      2097.5000 |
| 002        | 打孔器       | 办公用品     |        500 |      2097.5000 |
| 003        | 运动T血      | 衣服         |       4000 |      2097.5000 |
| 004        | 菜刀         | 厨房用具     |       3000 |      2097.5000 |
| 005        | 高压锅       | 厨房用具     |       6800 |      2097.5000 |
| 006        | 叉子         | 厨房用具     |        500 |      2097.5000 |
| 007        | 擦菜板       | 厨房用具     |        880 |      2097.5000 |
| 008        | 圆珠笔       | 办公用品     |        100 |      2097.5000 |
+------------+--------------+--------------+------------+----------------+

select product_id,product_name,product_type,sale_price,(
    select avg(sale_price) 
    from Product) as sale_price_all
from Product;

5、请根据习题5.1中的条件编写一条SQL语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。

+------------+--------------+--------------+------------+----------------+
| product_id | product_name | product_type | sale_price | avg_sale_price |
+------------+--------------+--------------+------------+----------------+
| 001        | TT血衫       | 衣服         |       1000 |      2500.0000 |
| 002        | 打孔器       | 办公用品     |        500 |       300.0000 |
| 003        | 运动T血      | 衣服         |       4000 |      2500.0000 |
| 004        | 菜刀         | 厨房用具     |       3000 |      2795.0000 |
| 005        | 高压锅       | 厨房用具     |       6800 |      2795.0000 |
| 006        | 叉子         | 厨房用具     |        500 |      2795.0000 |
| 007        | 擦菜板       | 厨房用具     |        880 |      2795.0000 |
| 008        | 圆珠笔       | 办公用品     |        100 |       300.0000 |
+------------+--------------+--------------+------------+----------------+

提示:其中的关键是avg_sale_price列。与习题5.3不同,这里需要计算出的是各商品种类的平均销售单价。这与5-3节中使用关联子查询所得到的结果相同。也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

解答：

CREATE VIEW AvgPriceByType 
AS
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 p1.product_type
    ) as avg_sale_price
from Product as p1;

-- 确认视图内容
SELECT * FROM AvgPriceByType;

owl_hub

关注

0
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
SQL基础教程练习题

SQL基础教程练习题记录下学习过程，陆续更新测试环境：CentOS 7.9 +mysql5.7准备数据表提前准备Product数据表，以及插入数据--创建数据表mysql> CREATE TABLE Product ( -> product_id CHAR(4) NOT NULL, -> product_name VARCHAR(100) NOT NULL, -> product_type VARCHAR(32) NOT NULL, -&g
复制链接

扫一扫