首页 > 代码库 > 最近常考的sql题目
最近常考的sql题目
1.将dbo.Orders中的数据合并至Sales.Orders表中，匹配行进行更新操作，未匹配行进行插入操作，目标表中存在，源表中不存在，则删除。
MERGE INTO Sales.Orders as S
USING dbo.Orders AS O
on S.orderid=O.orderid
when matched
then update set S.custid=O.custid,....
when not matched
then insert values(S.cusrid,....)
when not matched by source
then DELETE;
注：详细内容见http://www.cnblogs.com/zq281660880/archive/2013/03/07/2947852.html
2.定义一个事务，向产品表中添加记录，如果添加成功，则接着给供应商表也添加一条数据，如果产品表记录添加失败，供应商表不做任何操作。
BEGIN TRANSACTION
INSERT INTO Product(ProductID,Name,Price,Supplierid)
VALUES(1,‘AA‘,34,34 );
IF @@ERROR=0
BEGIN
INSERT INTO Suppliers(Supplierid,SupName,SupAddress)
VALUES(2,‘AA‘,‘AAA‘);
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
3.定义一个游标（例子）
DECLARE @id AS INT,@name AS VARCHAR
DECLARE c CURSOR FOR
SELECT ID,NAME FROM table1
ORDER BY id;
OPEN c
FETCH NEXT FROM c INTO @id,@name
WHILE @@fetch_status=0
BEGIN
SELECT id,NAME
FROM table1
WHERE id=@id
FETCH NEXT FROM c INTO @id,@name
END
CLOSE c
DEALLOCATE c
4.创建一个触发器，要求在产品表中插入一条记录时，向供应商表也插入一条记录
CREATE TRIGGER SuppliersAdd
ON Product
AFTER INSERT
AS
INSERT INTO Suppliers
SELECT INSERTED.Supplierid,INSERTED.Name,INSERTED.Price
FROM inserted
5.创建一个内联表值函数
CREATE FUNCTION fnProduct(@id as INT,@n as INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) Productid,Name
FROM Product
WHERE Productid=@id;
GO
6.创建存储过程
create proc sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
.........
end
CREATE PROCEDURE prProductUpd
@ProductID AS INT,
@Supplierid AS INT
AS
BEGIN
DECLARE @i AS NVARCHAR(50)
BEGIN TRY
UPDATE Product
SET Supplierid = @Supplierid
WHERE ProductID=@ProductID;
SET @i=1;
END TRY
BEGIN CATCH
SET @i=0;
END CATCH
END
执行：
EXEC prProductUpd 1,33;
7.逻辑处理顺序：
FROM 、 WHERE 、GROUP BY 、HAVING 、SELECT、 OVER 、DISTINCT 、TOP 、ORDER BY
最近常考的sql题目