Create a view based on correlated subquery and then query it : Create View « View « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. View
  3. Create View
4> CREATE TABLE Orders (
5>      OrderID int IDENTITY (1, 1) NOT NULL ,
6>      CustomerID nchar (5) NULL ,
7>      EmployeeID int NULL ,
8>      OrderDate datetime NULL ,
9>      RequiredDate datetime NULL ,
10>     ShippedDate datetime NULL ,
11>     ShipVia int NULL ,
12>     Freight money NULL DEFAULT (0),
13>     ShipName nvarchar (40) NULL ,
14>     ShipAddress nvarchar (60) NULL ,
15>     ShipCity nvarchar (15) NULL ,
16>     ShipRegion nvarchar (15) NULL ,
17>     ShipPostalCode nvarchar (10) NULL ,
18>     ShipCountry nvarchar (15) NULL
19>
20>
21> )
22> GO
1>
2> CREATE TABLE Customers (
3>      CustomerID nchar (5) NOT NULL ,
4>      CompanyName nvarchar (40) NOT NULL ,
5>      ContactName nvarchar (30) NULL ,
6>      ContactTitle nvarchar (30) NULL ,
7>      Address nvarchar (60) NULL ,
8>      City nvarchar (15) NULL ,
9>      Region nvarchar (15) NULL ,
10>     PostalCode nvarchar (10) NULL ,
11>     Country nvarchar (15) NULL ,
12>     Phone nvarchar (24) NULL ,
13>     Fax nvarchar (24) NULL
14> )
15> GO
1>
2> INSERT Customers VALUES('1','A','Maria',    'Sales',  'Str. 57', 'Berlin'    ,NULL,'12209', 'Germany','111-1111111','111-1111111')
3> INSERT Customers VALUES('2','M','Joe',      'Owner',  'Ave. 231','Vancouver' ,NULL,'05023', 'Mexico', '(222) 222-3332',NULL)
4> INSERT Customers VALUES('3','H','Thomas',   'Sales',  'Sq.  111','London'    ,NULL,'1D00P', 'UK',     '(444) 444-4444','(444) 444-4444')
5> INSERT Customers VALUES('4','B','Berg',     'Order',  'Blv    8','Toronto'   ,NULL,'00222', 'Sweden', '4444-55 55 65','5555-55 55 55')
6> INSERT Customers VALUES('5','S','Moos',     'Sales',  'Fort  57','New York'  ,NULL,'68306', 'Germany','6666-66666','6666-77777')
7> INSERT Customers VALUES('6','F','Cite',     'Manager','24      ','Dalles'    ,NULL,'67000', 'France', '88.60.15.31','88.60.15.32')
8> INSERT Customers VALUES('7','C','Sommer',   'Owner',  'Araq, 67','Paris'     ,NULL,'28023', 'Spain',  '(91) 555 22 82','(91) 555 91 99')
9> INSERT Customers VALUES('8','P','Leb',      'Owner',  '12      ','Beijing'   ,NULL,'13008', 'France', '91.24.45.40','91.24.45.41')
10> INSERT Customers VALUES('9','D','Elizabeth','Manager','23 Blvd.','Tsawassen','BC', 'T2F8M4','Canada', '(604) 555-4729','(604) 555-3745')
11> go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3> Create VIEW dbo.VCustsWithOrders
4> AS
5> SELECT TOP (100) PERCENT
6>   Country, CustomerID, CompanyName, ContactName, ContactTitle,
7>   Address, City, Region, PostalCode, Phone, Fax
8> FROM Customers AS C
9> WHERE EXISTS
10>   (SELECT * FROM dbo.Orders AS O
11>    WHERE O.CustomerID = C.CustomerID)
12> ORDER BY Country;
13> GO
1>
2> SELECT Country, CustomerID, CompanyName
3> FROM dbo.VCustsWithOrders;
4> GO
Country         CustomerID CompanyName
--------------- ---------- ----------------------------------------

(0 rows affected)
1>
2>
3> drop VIEW dbo.VCustsWithOrders;
4> GO
1>
2> drop table orders;
3> drop table customers;
4> GO
1>








16.2.Create View
16.2.1.The syntax of the CREATE VIEW statement
16.2.2.A view is a SELECT statement that's stored with the database.
16.2.3.WITH SCHEMABINDING clause protects a view by binding it to the database structure, or schema.
16.2.4.Views have a couple of limitations.
16.2.5.Create a view based on correlated subquery and then query it
16.2.6.Encrypting aView
16.2.7.A CREATE VIEW statement that uses TOP and ORDER BY clauses
16.2.8.Creating an Indexed View (CLUSTERED, NONCLUSTERED)
16.2.9.Using the ORDER BY Clause in a View with TOP 100 PERCENT
16.2.10.Supplying Column Names in the Header of the View