[T-SQL] T-SQL_INSERT/UPDATE문 예제(sql 연습 문제4)

 

1. 각 AdventureWorks 제품은 SalesLT.Product 테이블에 저장되며 각 제품에는 고유 한 ProductID 식별자가 있으며 이는 SalesLT.Product 테이블에서 IDENTITY 열로 구현됩니다.

제품은 SalesLT.ProductCategory 테이블에 정의 된 범주로 구성됩니다. 제품 및 제품 범주 레코드는 공통ProductCategoryID 식별자 (SalesLT.ProductCategory 테이블의 IDENTITY 열)와 관련이 있습니다.

삽입 할 새 제품의 정보가 아래 표에 나와 있습니다.

Name	ProductNumber	StandardCost	ListPrice	ProductCategoryID	SellStartDate
LED Lights	LT-L123	2.56	12.99	37	<Today>

- AdventureWorks는 위 표에 나와있는 새로운 제품을 판매하기 시작했습니다. 지정되지 않은 열의 기본값 또는 NULL 값을 사용하여 SalesLT.Product 테이블에 삽입하십시오.

- 제품을 삽입 한 후 SELECT SCOPE_IDENTITY (); 삽입 된 마지막 ID 값을 가져옵니다.

- SalesLT.Product 테이블에서 제품의 행을 보려면 조회를 추가하십시오.

INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)
VALUES ('LED Lights', 'LT-L123', 2.56, 12.99, 37, GETDATE());

-- Get last identity value that was inserted
SELECT  SCOPE_IDENTITY()

SELECT * FROM SalesLT.Product
WHERE ProductID = SCOPE_IDENTITY();

 

 

 

2. 제품 범주를 삽입하기위한 이전 연습의 코드가 이미 포함되어 있습니다. 이 새로운 카테고리에는 다음 두 가지 새로운 제품이 포함됩니다.

Name	ProductName	StandardCost	ListPrice	ProductCategoryID	SellStartDate
Bicycle Bell	BB-RING	2.47	4.99	<The new ID for Bells and Horns>	<Today>
Bicycle Horn	BB-PARP	1.29	3.75	<The new ID for Bells and Horns>	<Today>

- 위의 제품 세부 사항에 따라 적절한 ProductCategoryID 값으로 두 개의 새 제품을 삽입하십시오.

- 데이터가 삽입되었는지 확인하기 위해서는 쿼리를 완료 한 후 SalesLT.Product, SalesLT.ProductCategory 테이블을 조인하십시오.

-- Insert product category
INSERT INTO SalesLT.ProductCategory (ParentProductCategoryID, Name)
VALUES (4, 'Bells and Horns');

-- Insert 2 products
INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, ProductCategoryID, SellStartDate)
VALUES
('Bicycle Bell', 'BB-RING', 2.47, 4.99, IDENT_CURRENT('SalesLT.ProductCategory'), GETDATE()),
('Bicycle Horn', 'BB-PARP', 1.29, 3.75, IDENT_CURRENT('SalesLT.ProductCategory'), GETDATE());

-- Check if products are properly inserted
SELECT c.Name As Category, p.Name AS Product
FROM SalesLT.Product AS p
JOIN SalesLT.ProductCategory as c ON p.ProductCategoryID = c.ProductCategoryID
WHERE p.ProductCategoryID = IDENT_CURRENT('SalesLT.ProductCategory');

 

 

 

 

3. 제품에 대한 데이터를 삽입했지만 가격 세부 정보가 올바르지 않습니다. 올바른 가격을 반영하기 위해 이전에 삽입 한 레코드를 업데이트해야합니다.

- AdventureWorks의 영업 관리자는 Bells and Horns 범주의 모든 제품에 대해 10 %의 가격 인상을 의무화했습니다.

- 이러한 제품의 SalesLT.Product 테이블에서 행을 업데이트하여 가격을 10 % 인상하십시오.

-- Update the SalesLT.Product table
UPDATE SalesLT.Product
SET ListPrice = ListPrice * 1.1
WHERE ProductCategoryID =
  (SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Bells and Horns');

 

​

 

4. 이전 문제에 삽입한 새로운 LED 조명으로 이전의 모든 조명 제품을 교체합니다.

- SalesLT.Product 테이블을 업데이트하여 이전에 삽입 한 LED 조명 제품 이외의 조명 범주(ProductCategoryID 37)에 있는 모든 제품의 DiscontinuedDate를 오늘 날짜로 설정하십시오.

-- Finish the UPDATE query
UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductCategoryID = 37 AND ProductNumber <> 'LT-L123';

SELECT * FROM SalesLT.Product

 

 

 

​

5. 판매 주문을 쉽게 삽입 할 수있는 재사용 가능한 스크립트를 작성하려고합니다. 주문 헤더 레코드를 삽입하는 스크립트와 지정된 주문 헤더에 대한 주문 세부 사항 레코드를 삽입하는 별도의 스크립트를 작성하려고 합니다.

두 스크립트 모두 변수를 사용하여 재사용하기 쉽도록합니다. 주문 헤더를 삽입하는 스크립트는 order date, due date, customer ID 값을 지정할 수 있도록 해야 합니다.

- 다음과 같은 판매 오더를 포함시켜야합니다.

Order Date	Due Date	Customer ID
Today's date	7 days from now	1

- 변수 이름을 입력하여 DECLARE 문을 완료하십시오. 스크립트의 INSERT 문에서 이러한 이름을 추론 할 수 있습니다.

- INSERT 쿼리를 완료하십시오. SalesOrderID는 IDENTITY 열이므로이 ID가 자동으로 생성됩니다. ShipMethod 필드에 하드 코드 된 값 'CARGO TRANSPORT 5'를 사용할 수 있습니다.

- SCOPE_IDENTITY ()를 사용하여 새 판매 주문 헤더의 ID를 인쇄하십시오.

DECLARE @OrderDate datetime = GETDATE();
DECLARE @DueDate datetime = DATEADD(dd, 7, GETDATE());
DECLARE @CustomerID int = 1;

INSERT INTO SalesLT.SalesOrderHeader (OrderDate, DueDate, CustomerID, ShipMethod)
VALUES (@OrderDate, @DueDate, @CustomerID, 'CARGO TRANSPORT 5');

PRINT SCOPE_IDENTITY();

 

 

 

​

6. 다음 단계로 주문 세부 사항을 삽입하려고합니다. 이를 위한 스크립트를 통해 사용자는 sales order ID, a product ID, a quantity, a unit price를 지정할 수 있어야합니다.

스크립트는 지정된 판매 주문 ID가 SalesLT.SalesOrderHeader 테이블에 존재하는지 확인해야합니다.

EXISTS를 사용하여 수행 할 수 있습니다. 이 경우 코드는 order details을 SalesLT.SalesOrderDetail 테이블에 삽입해야 합니다 (기본값을 지정하거나 지정되지 않은 열의 경우 NULL 사용).

판매 주문 ID가 SalesLT.SalesOrderHeader 테이블에 존재하지 않으면 코드는 'The order does not exist'라는 메시지를 인쇄해야합니다.

- 이전 연습에서 약간 수정 된 코드를 사용할 수 있습니다. SCOPE_IDENTITY ()로 OrderID를 정의합니다.

- IF-ELSE 블록을 완성하십시오

- 테스트는 SalesID.SalesOrderHeader 테이블에 OrderID와 동일한 SalesOrderID가있는 SalesOrderDetail이 있는지 확인해야합니다.

- 동일한 Order ID가 존재할 경우 SalesOrderDetail 테이블에 레코드를 삽입하도록 명령문을 완료하십시오.
- 그렇지 않은 경우 'The order does not exist'를 인쇄하십시오.

-- Code from previous exercise
DECLARE @OrderDate datetime = GETDATE();
DECLARE @DueDate datetime = DATEADD(dd, 7, GETDATE());
DECLARE @CustomerID int = 1;
INSERT INTO SalesLT.SalesOrderHeader (OrderDate, DueDate, CustomerID, ShipMethod)
VALUES (@OrderDate, @DueDate, @CustomerID, 'CARGO TRANSPORT 5');
DECLARE @OrderID int = SCOPE_IDENTITY();

-- Additional script to complete
DECLARE @ProductID int = 760;
DECLARE @Quantity int = 1;
DECLARE @UnitPrice money = 782.99;

IF EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID)
BEGIN
	INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice)
	VALUES (@OrderID, @Quantity, @ProductID, @UnitPrice)
END
ELSE
BEGIN

 

 

 

​

7. Adventure Works에 따르면 자전거의 시장 평균 가격은 $ 2,000이고 소비자 조사에 따르면 고객이 자전거를 지불 할 가능성이있는 최대 가격은 $ 5,000입니다. 자전거의 평균 정가가 시장 평균과 같거나 가장 비싼 자전거가 허용 가능한 가격보다 높을 때까지 모든 자전거 제품의 정가를 10 % 씩 증가시키는 Transact-SQL 논리를 작성해야합니다. Bikes 상위 카테고리의 제품 카테고리는 SalesLT.vGetAll Categories view에서 판별 할 수 있습니다.

- 'Bikes' 상위 카테고리에 있는 제품의 평균 정가가 시장 평균보다 낮은 경우에만 루프가 실행됩니다.

- 'Bikes'상위 카테고리에있는 모든 제품을 업데이트하여 정가를 10 % 늘립니다.

- 'Bikes' 상위 카테고리에있는 제품의 새로운 평균 및 최대 판매 가격을 결정하십시오.

- 새로운 최대 가격이 허용 가능한 최대 가격 이상인 경우 루프를 종료하십시오. 그렇지 않으면 계속하십시오..

DECLARE @MarketAverage money = 2000;
DECLARE @MarketMax money = 5000;
DECLARE @AWMax money;
DECLARE @AWAverage money;

SELECT @AWAverage =  AVG(ListPrice), @AWMax = MAX(ListPrice)
FROM SalesLT.Product
WHERE ProductCategoryID IN
	(SELECT DISTINCT ProductCategoryID
	 FROM SalesLT.vGetAllCategories
	 WHERE ParentProductCategoryName = 'Bikes');

WHILE @AWAverage < @MarketAverage
BEGIN
   UPDATE SalesLT.Product
   SET ListPrice = ListPrice * 1.1
   WHERE ProductCategoryID IN
	(SELECT DISTINCT ProductCategoryID
	 FROM SalesLT.vGetAllCategories
	 WHERE ParentProductCategoryName = 'Bikes');

 

 

 

 

8. 주문을 삭제하기 위해 Transact-SQL 스크립트를 구현하고 있으며 삭제 프로세스 중에 발생하는 모든 오류를 처리하려고합니다.

- 다음 코드를 사용하여 주문 데이터를 삭제할 수 있습니다. 그러나 지정된 순서가 존재하지 않아도 이 코드는 성공한다는 오류가 있습니다.

DECLARE @OrderID int = <the_order_ID_to_delete>;
DELETE FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @OrderID;
DELETE FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID;

- 코드를 수정하여 지정된 주문 ID가 있는지 확인한 후 삭제하십시오. 주문이 존재하지 않으면 코드에서 오류가 발생합니다. 그렇지 않으면 주문 데이터를 삭제해야합니다. 스크립트를 올바르게 코딩 한 경우 ID가 0 인 순서가 없으므로 스크립트를 실행할 때 오류가 발생합니다.

DECLARE @OrderID int = 0

-- Declare a custom error if the specified order doesn't exist
DECLARE @error VARCHAR(30) = 'Order #' + cast(@OrderID as VARCHAR) + ' does not exist';

IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID)
BEGIN
  THROW 50001, @error, 0;
END
ELSE
BEGIN
  DELETE FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @OrderID;
  DELETE FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID;
END

 

 

 

 

9. 이전 문제에 대한 솔루션은 지정된 순서가 없으면 코드에서 오류가 발생합니다. 오류가 발생하지 않도록 코드를 수정하고 PRINT 명령을 사용하여 오류 메시지를 사용자 인터페이스에 인쇄하십시오. BEGIN TRY, END TRY, BEGIN CATCH 및 END CATCH를 사용할 수 있습니다.

- 코드에 TRY ... CATCH를 추가하십시오.

- TRY 부분에 IF-ELSE 블록을 포함하십시오.

- CATCH 부분에서 ERROR_MESSAGE ()로 오류를 인쇄하십시오.

DECLARE @OrderID int = 71774
DECLARE @error VARCHAR(30) = 'Order #' + cast(@OrderID as VARCHAR) + ' does not exist';

-- Wrap IF ELSE in a TRY block
BEGIN TRY
IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID)
BEGIN
THROW 50001, @error, 0
END
ELSE
BEGIN
DELETE FROM SalesLT.SalesOrderDetail WHERE SalesOrderID = @OrderID;
DELETE FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID;
END
END TRY
-- Add a CATCH block to print out the error
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH

​

10. order details와 order headers를 삭제하는 일부 Transact-SQL 코드에서 오류 처리 논리를 구현했습니다. 그러나 프로세스의 도중에 실패하면 주문 세부 사항이 삭제 된 삭제되지 않은 주문 헤더 형식의 데이터 불일치가 발생할 수 있습니다. 두 개의 DELETE 문이 단일 트랜잭션 작업 단위로 처리되도록 이전 챌린지에서 작성한 코드를 향상시키는 것이 태스크입니다.

- BEGIN TRANSACTION 및 COMMIT TRANSACTION을 추가하여 두 개의 DELETE 문을 단일 트랜잭션 작업 단위로 취급하십시오.

- 오류 처리기에서 트랜잭션이 처리중인 경우 롤백되도록 코드를 수정하십시오. 처리중인 트랜잭션이 없으면 오류 처리기는 계속해서 오류 메시지를 인쇄해야합니다.

DECLARE @OrderID int = 0
DECLARE @error VARCHAR(30) = 'Order #' + cast(@OrderID as VARCHAR) + ' does not exist';

BEGIN TRY
  IF NOT EXISTS (SELECT * FROM SalesLT.SalesOrderHeader WHERE SalesOrderID = @OrderID)
  BEGIN
    THROW 50001, @error, 0
  END
  ELSE
  BEGIN
  BEGIN TRANSACTION
DELETE FROM SalesLT.SalesOrderDetail
WHERE SalesOrderID = @OrderID;
DELETE FROM SalesLT.SalesOrderHeader
WHERE SalesOrderID = @OrderID;
COMMIT TRANSACTION
END
END TRY

BEGIN CATCH