MSSQL DB - EX08

-- 10/11
-- Northwind  컨텍스트로 이동
USE Northwind

SELECT * FROM Employees

--1. 뷰 생성 작업
CREATE VIEW EmployeesBasicInformation
AS
SELECT EmployeeID, LastName, FirstName, Title, HireDate
FROM Employees

SELECT * FROM EmployeesBasicInformation

SELECT * FROM products

--2. 뷰생 생성 작업
CREATE VIEW ProductInformation
AS
SELECT
 p.ProductID, p.ProductName, s.CompanyName,
 c.CategoryName, p.QuantityPerUnit, p.UnitPrice
FROM
 Products p
 INNER JOIN Shippers s
 ON p.SupplierID = s.ShipperID
 INNER JOIN Categories c
 ON p.CategoryID = c.CategoryID
 
SELECT * FROM ProductInformation
 
--3. 뷰를 이용한 데이터 변경
UPDATE EmployeesBasicInformation
SET FirstName = 'Nancy'
WHERE EmployeeID = 1

SELECT * FROM EmployeesBasicInformation
SELECT * FROM Employees

--4. 뷰를 이용한 데이터 변경
UPDATE ProductInformation
SET CompanyName = 'Exotic Liquids'
WHERE ProductID = 1;

SELECT * FROM ProductInformation

-- exampledb 컨텍스트로 이동
USE exampledb

--5. 인덱스 생성
CREATE TABLE indextesttable
(
 data1 nvarchar (100) not null primary key, -- clustered index
 data2 nvarchar (100) not null unique,   -- nonclustered index
 data3 nvarchar (100),
 data4 nvarchar (100),
 data5 nvarchar (100),
 data6 datetime
)

sp_help indextesttable

SELECT * FROM indextesttable

DROP INDEX indextesttable.PK__indextes__BA94ED295CD6CB2B -- 기본키라 삭제 불가 에러!!

--CREATE CLUSTERED INDEX myindex ON indextesttable (data3)   -- CLUSTERED
--CREATE NONCLUSTERED INDEX myindex ON indextesttable (data3) --NONCLUSTERED
CREATE UNIQUE INDEX  myindex ON indextesttable (data3)  -- UNIQUE NONCLUSTERED

CREATE CLUSTERED INDEX myindex2 ON indextesttable (data6) --CLUSTERED

DROP INDEX indextesttable.myindex -- tablename.indexname

ALTER INDEX myindex ON indextesttable(data3)

-- Northwind 컨텍스트로 이동
USE Northwind

--6. Transaction 처리

--A
BEGIN TRANSACTION

UPDATE Employees
SET LastName = 'Davolio 2'
WHERE EmployeeID = 1

UPDATE Employees
SET LastName = 'Fuller 2'
WHERE EmployeeID = 2

--ROLLBACK TRANSACTION
COMMIT TRANSACTION

--B  -- 이와 같은 작없시에 TRY CATCH 문을 쓰는게 좋다
BEGIN TRANSACTION
BEGIN TRY
 UPDATE Employees
 SET LastName = 'Davolio 3'
 WHERE EmployeeID = 1

 UPDATE Employees
 SET LastName = 'Fuller 3'
 WHERE EmployeeID = 2
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH

SELECT * FROM Employees

SP_HELP employees

------------------------------------------------------