Sqlserve2019实验汇总

实验所基于的销售管理数据库中涉及以下表：

指定列或全部列查询（Select）

1. 查询商品表（Product）中所有商品的信息

SELECT * FROM Product;

1. 检索客户表（Customer）中前5位客户的公司名称、联系人姓名和地址。

SELECT TOP 5 CompanyName，ContactName，Address

FROMCustomer;

3.从员工表（Employee）中查询所有员工的部门信息。

SELECT Employee.EmployeeID, EmployeeName, Department.DepartmentName

FROM Employee

JOIN Department ON Employee.DepartmentID = Department.DepartmentID;

4.查询将员工表中（Employee）所有员工的工资提高10%后的信息，输出字段为：员工姓名，原工资，提高后工资。

SELECT EmployeeName, Salary, Salary * 1.1 AS NewSalary

FROM Employee;

5.统计员工表（Employee）中的职工数。

SELECT COUNT(*) AS EmployeeCount FROM Employee;

按条件查询（Where）

1.检索员工表所有姓李和姓章的员工信息。

SELECT * FROM Employee WHERE EmployeeName LIKE '李%' OR EmployeeName LIKE '章%';

2.检索员工表姓李的名字只有一个汉字的员工信息。

SELECT * FROM Employee WHERE EmployeeName LIKE '李_';

1. 查询员工表中工资在3400以下的女性员工姓名和工资信息。

SELECT EmployeeName, Salary FROM Employee WHERE Salary < 3400 AND Sex = 'Female';

1. 查询员工表中工资在5000到7000之间的员工信息。（Between）

SELECT * FROM Employee WHERE Salary BETWEEN 5000 AND 7000;

1. 检索销售订单表（Sell_Order）中，员工编号为1、5、7的员工接收订单的信息。（IN）

SELECT * FROM Sell_Order WHERE EmployeeID IN (1, 5, 7);

1. 检索部门表（Department）中主管位置不为空的部门信息。

SELECT * FROM Department WHERE Manager IS NOT NULL;

分组统计查询

1. 查询员工表中男女员工的平均工资。输出字段性别，平均工资。

SELECT Sex, AVG(Salary) AS AverageSalary FROM Employee GROUP BY Sex;

1. 查询销售订单表中各种商品的订货总数，输出字段为商品编号，订货总数。

SELECT ProductID, COUNT(*) AS OrderTotal FROM Sell_Order GROUP BY ProductID;

1. 查询销售订单表中，订购两种以上商品的客户编号，订购商品种类。

SELECT CustomerID, COUNT(DISTINCT ProductID) AS ProductTypes

FROM Sell_Order

GROUP BY CustomerID

HAVING COUNT(DISTINCT ProductID) >= 2;

对查询结果排序

1. 查询员工表中男女员工的平均工资。输出字段性别，平均工资，按照平均工资升序排列。

SELECT Sex, AVG(Salary) AS AverageSalary FROM Employee GROUP BY Sex ORDER BY AverageSalary ASC;

2.查询销售订单表中各种商品的订货总数，输出字段为商品编号，订货总数，按照订货总数降序排列。

SELECT ProductID, COUNT(*) AS OrderTotal FROM Sell_Order GROUP BY ProductID ORDER BY OrderTotal DESC;

 

 

.查询和“章宏”同一部门的员工号，员工姓名。

SELECT EmployeeID,EmployeeName

FROM Employee

WHERE DepartmentID IN

                            (SELECT DepartmentID

                             FROM Employee

                             WHERE EmployeeName='章宏')

2.查询年龄最小的员工姓名、性别和工资。

SELECT EmployeeName,Sex,Salary

FROM Employee

WHERE BirthDate IN

                            ( SELECT MAX(BirthDate)

                              FROM Employee

                            )

3.查询比平均工资高的员工姓名和工资。

SELECT EmployeeName,Salary

FROM Employee

WHERE Salary >(SELECT AVG(Salary)

                            FROM Employee

                        )

4.查询没被下订单的产品编号、产品名称。

1)SELECT p.ProductID, p.ProductName

FROM Product p

LEFT JOIN Sell_Order so ON p.ProductID = so.ProductID

WHERE so.SellOrderID IS NULL;（连接查询）

2)SELECT p.ProductID, p.ProductName

FROM Product p

WHERE p.ProductID NOT IN (

    SELECT so.ProductID

    FROM Sell_Order so

);(嵌套查询)

5.查询订购牛奶的客户名称和联系地址。

1）SELECT DISTINCT c.Contactname, c.Address

FROM Customer c

JOIN Sell_Order so ON c.CustomerID = so.CustomerID

JOIN Product p ON so.ProductID = p.ProductID

WHERE p.ProductName = '牛奶';（连接查询）

2）SELECT DISTINCT c.ContactName, c.Address

FROM Customer c

WHERE c.CustomerID IN (

    SELECT so.CustomerID

    FROM Sell_Order so

    JOIN Product p ON so.ProductID = p.ProductID

    WHERE p.ProductName = '牛奶'

);（嵌套查询）

6.查询每个客户订购各产品总数量，输出总数量在400以上的客户名称，商品名称。

1）select CompanyName,ProductName
from Customer,Product
where CustomerID in
    (select CustomerID
     from Sell_Order
     where ProductID=Product.ProductID
     group by CustomerID,ProductID
     having sum(SellOrderNumber)>400
    )（）连接查询

2）SELECT c.CompanyName, p.ProductName

FROM Customer c

JOIN Sell_Order so ON c.CustomerID = so.CustomerID

JOIN Product p ON so.ProductID = p.ProductID

WHERE so.CustomerID IN (

    SELECT so.CustomerID

    FROM Sell_Order so

    GROUP BY so.CustomerID, so.ProductID

    HAVING SUM(so.SellOrderNumber) > 400

);（嵌套查询）

连接查询实现：（4）-（6）

.向客户表中插入一行：客户编号为34，公司名称为“人民低电压”，联系人为“南辉”。

INSERT

INTO Customer(CustomerID,CompanyName,ContactName)

VALUES('34','人民低电压','南辉')

2.先定义一张表day_total，该表中包含两个属性列（销售日期 smalldatetime,销售订单数 int），在Sell_order中统计每天的销售订单数，并将结果插入到day_total表中。

CREATE TABLE day_total

       ( 销售日期 smalldatetime,

         销售订单数 int

       )

INSERT

INTO day_total

SELECT SellOrderDate,COUNT(SellOrderID)

FROM Sell_Order

GROUP BY SellOrderDate

3.将商品表中所有商品的价格上浮20%。

UPDATE Product

SET Price=Price*1.2

4.将商品表中库存量小于10的商品库存量置零。

UPDATE Product

SET ProductStockNumber=0

WHERE ProductStockNumber<10

SELECT name

FROM sys.triggers

WHERE parent_id = OBJECT_ID('Product');

DISABLE TRIGGER product_update_stock ON Product;

UPDATE Product

SET ProductStockNumber=0

WHERE ProductStockNumber<10

5.删除采购订单表Purchase_Order中商品库存量小于0的订单。

DELETE

FROM Purchase_order

WHERE ProductID IN

                            (SELECT ProductID

                             FROM Product

                             WHERE ProductStockNumber<0

                             )

6.删除员工EmployeeName为“李立三”的所有销售订单。（自己任意指定一个员工名）

DELETE

FROM Sell_Order

WHERE EmployeeID IN (

                                    SELECT EmployeeID

                                    FROM Employee

                                    WHERE EmployeeName='李立三'

                                    )

7.创建视图View_employee，输出员工号，姓名，性别，部门号

CREATE VIEW View_employee(员工号,姓名,性别,部门号)

AS

SELECT EmployeeID,EmployeeName,Sex,DepartmentID

FROM Employee

8.基于视图View_employee和部门表，查询各部门名称、各部门员工人数。

SELECT DepartmentName,COUNT(员工号) 部门员工人数

FROM Department,View_employee

WHERE DepartmentID=View_employee.部门号

GROUP BY DepartmentName

9.创建客户订单信息视图View_customerorder，包括客户公司名称，订购商品名称，单价，订购日期。

CREATE VIEW View_customerorder(客户公司名称,订购商品名称,单价,订购日期)

AS

SELECT CompanyName,ProductName,Price,SellOrderDate

FROM Customer,Sell_Order,Product

WHERE Customer.CustomerID=Sell_Order.CustomerID AND Product.ProductID=Sell_Order.ProductID

10.基于视图View_customerorder，查询“三川实业有限公司”订购商品情况。

SELECT *

FROM View_customerorder

WHERE 客户公司名称='三川实业有限公司'

 

编程计算S=2+4+6+……+100

DECLARE @sum int,@i int;

SELECT @sum = 0,@i = 2;

WHILE(@i<=100)

BEGIN

SET @sum+=@i;

set @i+=2;

END

PRINT @sum

2.编程计算S=1+（1+3）+（1+3+5）+……+（1+3+5……+51）

DECLARE @sum int,@i int;

SELECT @sum = 0,@i = 1;

WHILE(@i<=26)

BEGIN

SET @sum+=@i*@i;

SET @i+=1;

END

PRINT @sum

3.查询是否有“打印机”的订单，如果有，输出“目前有打印机的订单”，如果没有，输出“目前没有打印机的订单”。

IF EXISTS

(SELECT SellOrderID

 FROM Sell_Order,Product

 WHERE Sell_Order.ProductID=Product.ProductID AND ProductName='打印机')

 PRINT 'THERE ARE ORDERS FOR PRINTER!'

 ELSE 

 PRINT 'THERE ARE NOT ORDERS FOR PRINTER'

4.编程实现删除员工“姜玲娜”，将数据库中与“姜玲娜”相关信息一并除。

1.BEGIN TRANSACTION;

DECLARE @employeeId INT;

-- 查找员工ID

SELECT @employeeId = EmployeeID

FROM Employee

WHERE EmployeeName = '姜玲娜';

-- 删除与该员工相关的销售订单

DELETE FROM Sell_Order

WHERE EmployeeID = @employeeId;

-- 删除与该员工相关的采购订单

DELETE FROM Purchase_Order

WHERE EmployeeID = @employeeId;

-- 删除员工信息

DELETE FROM Employee

WHERE EmployeeID = @employeeId;

COMMIT;

2. create trigger employee_delete

on Employee

instead of delete

as

begin

delete from Sell_Order

where EmployeeID in (select EmployeeID from deleted)delete from Purchase_order

where EmployeeID in (select EmployeeID from deleted)delete from Employee

where EmployeeID in (select EmployeeID from deleted)end

5.供应商“文成软件有限公司”给员工“章宏”供货2000个优盘。编程实现将采购订单涉及到的相关信息写入到数据库中。

declare @EmployeeID int

set @EmployeeID = (select EmployeeID from Employee where EmployeeName = '章宏')

declare @ProductID int

set @ProductID = (select ProductID from Product where ProductName = '优盘')

declare @PurchaseID int

set @PurchaseID = (select ISNULL(MAX(PurchaseOrderID), 0) + 1 from Purchase_order)

declare @ProviderID int -- Adding missing variable declaration

insert into Purchase_order(PurchaseOrderID, ProviderID, EmployeeID, ProductID, PurchaseOrderNumber)

values(@PurchaseID, @ProviderID, @EmployeeID, @ProductID, 2000)

6.在销售管理数据库中创建存储过程proc_employee_order，要求实现如下功能：根据员工的姓名查询该员工的奖金情况，奖金根据该员工接收订单的总金额计算得到（奖金=总金额*5%），调用存储过程，查询员工王孔若和蔡慧敏的奖金。

CREATE PROCEDURE proc_employee_order1

    @EmployeeName NVARCHAR(100)

AS

BEGIN

    IF NOT EXISTS(SELECT 1 FROM Employee WHERE EmployeeName = @EmployeeName)

    BEGIN

        PRINT @EmployeeName + '不存在'

        RETURN

    END

   

    SELECT

        e.EmployeeName,

        ISNULL(SUM(so.SellOrderNumber * p.Price) * 0.05, 0) AS 奖金

    FROM Employee e

    LEFT JOIN Sell_Order so ON e.EmployeeID = so.EmployeeID

    LEFT JOIN Product p ON so.ProductID = p.ProductID

    WHERE e.EmployeeName = @EmployeeName

    GROUP BY e.EmployeeName

END

7.在销售管理数据库中创建存储过程proc_customer_order, 要求实现如下功能：根据客户的公司名称查询该客户的订单情况，如果该公司没有订购商品，则输出“某某公司没有订购商品”，否则输出订购商品的相关信息，包括公司名称，联系人姓名，订购商品名称，订购数量，单价。调用存储过程，查询“通恒机械有限公司”订购商品情况。

CREATE PROCEDURE proc_customer_order

    @CompanyName NVARCHAR(100)

AS

BEGIN

    -- 先检查公司是否存在且是否有订单

    IF NOT EXISTS(

        SELECT 1

        FROM Customer c

        JOIN Sell_Order so ON c.CustomerID = so.CustomerID

        WHERE c.CompanyName = @CompanyName

    )

    BEGIN

        PRINT @CompanyName + '没有订购商品'

        RETURN

    END

    -- 输出订单详情

    SELECT

        c.CompanyName,

        c.ContactName,

        p.ProductName,

        so.SellOrderNumber,

        p.Price

    FROM Customer c

    JOIN Sell_Order so ON c.CustomerID = so.CustomerID

    JOIN Product p ON so.ProductID = p.ProductID

    WHERE c.CompanyName = @CompanyName

END

-- 调用存储过程

EXEC proc_customer_order '通恒机械有限公司'

创建触发器trigger_delete，实现以下功能：当订单表的数据被删除时，显示提示信息“订单表记录被修改了”。

CREATE TRIGGER trigger_delete

ON Sell_Order

AFTER DELETE

AS

PRINT '订单表记录被修改了';

2对Sell_Order表创建名为reminder的触发器，当用户向Sell_Order表中插入或修改记录时，自动显示Sell_Order表中的记录。

CREATE TRIGGER Trigger_reminder

ON Sell_Order

AFTER INSERT,UPDATE

AS

SELECT *

FROM Sell_Order

3对Employee表中创建名为emp_updtri的触发器，实现如下功能：当修改姓名时，自动检查

订单表，确定是否有该员工的订单，如果存在该员工，则撤销操作。

CREATE TRIGGER emp_upd

ON Employee

AFTER UPDATE

AS

IF UPDATE(EmployeeName)

DECLARE @ID INT

SELECT @ID=EmployeeID

FROM deleted

IF EXISTS(SELECT *

                     FROM Sell_Order

                     WHERE Sell_Order.SellOrderID=@ID)

BEGIN

PRINT '员工已存在订单'

ROLLBACK

END

4创建一个INSERT触发器，当在Employee表中插入一条新员工记录时，如果是“人事部”的员工，则撤销该插入操作，并返回出错消息。

CREATE TRIGGER Empoyee_Insert

ON Employee

AFTER INSERT

AS

DECLARE @DID CHAR(50)

SELECT @DID=DepartmentID FROM inserted

IF (SELECT DepartmentName

       FROM Department

       WHERE DepartmentID=@DID

       )='人事部'

BEGIN

PRINT '禁止插入人事部成员'

ROLLBACK

END

5创建一个product_order_delete的触发器，其功能是：当删除商品表中的商品记录时，同时删除订单表中相应的订单，并显示提示信息“有关商品已被删除”。

CREATE TRIGGER product_order_delete

ON Product

INSTEAD OF DELETE

AS

DECLARE @Pid int

SELECT @Pid=ProductID

FROM deleted

DELETE

FROM Purchase_order

WHERE Purchase_order.ProductID=@Pid

DELETE

FROM Product

WHERE ProductID=@Pid

PRINT '有关商品已被删除'

6创建一个名为employee_deleted的触发器，其功能是：当对Employee表进行删除操作时，首先检查订单表，如果删除的员工没有接收订单，可以删除该员工的消息，否则撤销删除，显示“无法修改”的信息。

ALTER TRIGGER employee_delete

ON Employee

INSTEAD OF DELETE

AS

DECLARE @EID INT

SELECT @EID=EmployeeID

FROM deleted

IF EXISTS(SELECT SellOrderID

                FROM Sell_Order

                WHERE EmployeeID=@EID

                )

BEGIN

PRINT '无法修改'

END

ELSE

DELETE

FROM Employee

WHERE EmployeeID=@EID