实验八 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后面的语句

步骤

1. 在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

1. 查询供应商表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;