Скачать с ютуб SQL Tutorial - Views в хорошем качестве

SQL Tutorial - Views

This video will teach you how to create, alter and drop views with working examples, within SQL Server, we also look at the benefits and limitations of views and creating views with schemabinding and encryption. CREATE VIEW ‘name’ (optional column list) (optional) WITH (SCHEMABINDING / ENCRYPTION) AS SELECT STATEMENT SQL Statements used below: IF OBJECT_ID(N'vwSummary', N'V') IS NOT NULL DROP VIEW vwSummary; GO CREATE VIEW vwSummary (Customer_Id, Customer_Name, Order_Id, Order_Date, Title, [Paperback/Hardback]) WITH ENCRYPTION AS SELECT C_Id AS Customer_Id, C_First_Name + ' ' + C_Last_Name AS Customer_Name, O_Id AS Order_Id, O_Date AS Order_Date, P_Title AS Title, P_Type AS [Paperback/Hardback] FROM dbo.Customers INNER JOIN dbo.Orders ON C_Id = O_C_Id INNER JOIN dbo.Order_Details ON O_Id = OD_O_Id INNER JOIN dbo.Books ON OD_P_Id = P_Id; SELECT Customer_Id, Customer_Name, Order_Id, Order_Date, Title, [Paperback/Hardback] FROM vwSummary WHERE Customer_Id = 1 ALTER TABLE dbo.Customers ALTER COLUMN C_First_Name NVARCHAR(100) NOT NULL ALTER TABLE dbo.Customers ALTER COLUMN C_Middle_Name VARCHAR(50) NULL Find out View text: SELECT b.[definition] FROM sys.objects AS A INNER JOIN sys.sql_modules AS B ON A.object_id = B.object_Id WHERE a.[type] = N'V': CREATE VIEW vwLowStock (Title, Stock) AS SELECT P_Title, P_Stock FROM dbo.Books WHERE P_Stock ‘less than’= 5 DROP VIEW vwLowStock CREATE VIEW vwTotals AS SELECT O_C_Id AS Customer_Id, SUM(O_Total) AS Total FROM Orders GROUP BY O_C_Id Views are covered in the Microsoft Exam – Querying SQL Server (70-461) 2012 / 2014.