实验八 T_SQL编程
题目
以电子商务系统数据库ecommerce为例
1、在ecommerce数据库，针对会员表member首先创建一个“呼和浩特地区”会员的视图view_hohhot，然后通过该视图查询来自“呼和浩特”地区的会员信息，用批处理命令语句将问题进行分割，并分别以注释进行语句作用的说明描述
2、创建一个保存类别编号值的局部变量，并以set语句进行赋值，然后在product表中查询产品类别编号为局部变量值的产品名称proname、单价price、成本价cost、库存量stock。
3、查询供应商表supplier，如果存在供应商编号14001的记录，则显示“编号为14001的供应商记录已经存在”的消息，并输出此供应商的详细信息；否则插入一条供应商记录（14001，‘京顺通讯有限公司’，‘刘燕’，‘北京市朝阳区’，‘1111111’）
4、查询商品表product的商品编号、商品名称、单价、库存量、商品类别名称信息，其中商品类别使用CASE语句，当类别编号为101时，则类别名称为手机；当类别编号为201时，则类别名称为激光打印机；当类别编号为202时，则类别名称为喷墨打印机，当类别编号为301时，则类别名称为平板电脑∙∙∙∙∙∙（具体参照商品表）
5、在product表中，对产品编号为10102的商品单价进行加价，每次加价50，加大单价等于或高于3000即可，并统计加价的次数，打印输出产品名称、新单价和加价次数
6、自行设计一个多条件查询问题，并使用CASE语句去解决，要求CASE后面没有任何参数表达式，在WHEN子句后接有比较表达式，更加比较表达式的值，执行相对应的THEN后面的语句
步骤
- 在ecommerce数据库，针对会员表member首先创建一个“呼和浩特地区”会员的视图view_hohhot，然后通过该视图查询来自“呼和浩特”地区的会员信息，用批处理命令语句将问题进行分割，并分别以注释进行语句作用的说明描述
首先创造一个视图
--创建一个名为view_hohhot的视图，该视图只包含呼和浩特地区的会员信息。
create view view_hohhot as select * from member where address='呼和浩特';
--使用刚刚创建的视图view_hohhot来查询呼和浩特地区的所有会员信息
select* from view_hohhot;
2.创建一个保存类别编号值的局部变量，并以set语句进行赋值，然后在product表中查询产品类别编号为局部变量值的产品名称proname、单价price、成本价cost、库存量stock。
首先自定义一个保存类别编号值得局部变量@prono，定义类型为int类型。
declare @prono int
然后使用set语句将数据进行赋值
set @prono=10;
select proname,price,cost,stock from product where prono=@prono
- 查询供应商表supplier，如果存在供应商编号14001的记录，则显示“编号为14001的供应商记录已经存在”的消息，并输出此供应商的详细信息；否则插入一条供应商记录（14001，‘京顺通讯有限公司’，‘刘燕’，‘北京市朝阳区’，‘1111111’）
-- 检查供应商编号14001是否存在
IF EXISTS (SELECT 1 FROM supplier WHERE supno = 14001)
BEGIN
-- 如果存在，则打印消息并查询该供应商的详细信息
PRINT '编号为14001的供应商记录已经存在';
SELECT * FROM supplier WHERE supno = 14001;
END
ELSE
BEGIN
-- 如果不存在，则插入新的供应商记录
INSERT INTO supplier
VALUES(14001, '京顺通讯有限公司', '刘燕', '北京市朝阳区', '1111111');
PRINT '已成功插入编号为14001的供应商记录';
END
4.查询商品表product的商品编号、商品名称、单价、库存量、商品类别名称信息，其中商品类别使用CASE语句，当类别编号为101时，则类别名称为手机；当类别编号为201时，则类别名称为激光打印机；当类别编号为202时，则类别名称为喷墨打印机，当类别编号为301时，则类别名称为平板电脑∙∙∙∙∙∙（具体参照商品表）
SELECT
prono AS 商品编号,
proname AS 商品名称,
price AS 单价,
stock AS 库存量,
CASE
WHEN prono = 101 THEN '手机'
WHEN prono = 201 THEN '激光打印机'
WHEN prono = 202 THEN '喷墨打印机'
WHEN prono = 301 THEN '平板电脑'
END AS 商品类别名称
FROM
product;
5、在product表中，对产品编号为10102的商品单价进行加价，每次加价50，加大单价等于或高于3000即可，并统计加价的次数，打印输出产品名称、新单价和加价次数
DECLARE @currentPrice DECIMAL(10, 2);
DECLARE @increaseCount INT = 0;
-- 设置初始价格为商品10102的当前单价
SET @currentPrice = (SELECT price FROM product WHERE prono = 10102);
-- 循环直到单价达到或超过3000
WHILE @currentPrice < 3000
BEGIN
SET @currentPrice = @currentPrice + 50; -- 每次加价50
SET @increaseCount = @increaseCount + 1; -- 加价次数增加
END
-- 更新商品单价（这一步在实际中需要确保只更新一次，这里仅为示意）
UPDATE product
SET price = @currentPrice
WHERE prono = 10102;
-- 输出或返回结果（这通常需要在应用程序层面处理，SQL直接输出有局限）
SELECT
proname,
@currentPrice AS 新单价,
@increaseCount AS 加价次数
FROM
product
WHERE
prono = 10102;
6、自行设计一个多条件查询问题，并使用CASE语句去解决，要求CASE后面没有任何参数表达式，在WHEN子句后接有比较表达式，更加比较表达式的值，执行相对应的THEN后面的语句
CREATE TABLE Sales (
ProductID INT PRIMARY KEY,
Category VARCHAR(50),
SalesAmount DECIMAL(10,2)
);
-- 假设插入一些示例数据
INSERT INTO Sales (ProductID, Category, SalesAmount) VALUES
(1, 'Electronics', 5000.00),
(2, 'Clothing', 1500.00),
(3, 'Electronics', 8000.00),
(4, 'Home & Kitchen', 3000.00),
(5, 'Clothing', 10000.00);
SELECT
ProductID,
Category,
SalesAmount,
CASE
WHEN Category = 'Electronics' THEN
CASE
WHEN SalesAmount <= 3000 THEN 'Low'
WHEN SalesAmount > 3000 AND SalesAmount <= 7000 THEN 'Medium'
ELSE 'High'
END
WHEN Category = 'Clothing' THEN
CASE
WHEN SalesAmount <= 2000 THEN 'Low'
WHEN SalesAmount > 2000 AND SalesAmount <= 6000 THEN 'Medium'
ELSE 'High'
END
ELSE
CASE
WHEN SalesAmount <= 900 THEN 'Low'
WHEN SalesAmount > 900 AND SalesAmount <= 2500 THEN 'Medium'
ELSE 'High'
END
END AS SalesGrade
FROM
Sales;
