SQL Server/T-SQL Tutorial/View/Filter view

Материал из SQL эксперт
Перейти к: навигация, поиск

A script that creates a stored procedure as a filter-view

   <source lang="sql">

5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> drop table Billings; 3> GO 1> 2> create table Bankers( 3> BankerID Integer, 4> BankerName VARCHAR(20), 5> BankerContactLName VARCHAR(20), 6> BankerContactFName VARCHAR(20), 7> BankerCity VARCHAR(20), 8> BankerState VARCHAR(20), 9> BankerZipCode VARCHAR(20), 10> BankerPhone VARCHAR(20) 11> ) 12> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> 4> CREATE PROC spBillingReport 5> AS 6> SELECT BankerName, BillingNumber, BillingDate, BillingTotal 7> FROM Billings JOIN Bankers 8> ON Billings.BankerID = Bankers.BankerID 9> WHERE BillingTotal - CreditTotal - PaymentTotal > 0 10> GO 1> 2> drop PROC spBillingReport; 3> GO 1> 2> drop table Bankers; 3> GO 1></source>


A SELECT statement that uses the BankersMin view with where clause

   <source lang="sql">

5> 6> 7> create table Bankers( 8> BankerID Integer, 9> BankerName VARCHAR(20), 10> BankerContactLName VARCHAR(20), 11> BankerContactFName VARCHAR(20), 12> BankerCity VARCHAR(20), 13> BankerState VARCHAR(20), 14> BankerZipCode VARCHAR(20), 15> BankerPhone VARCHAR(20) 16> ) 17> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> CREATE VIEW BankersMin 4> AS 5> SELECT BankerName, BankerState 6> FROM Bankers 7> where BankerID < 5 8> GO 1> 2> 3> SELECT * FROM BankersMin 4> WHERE BankerState = "CA" 5> ORDER BY BankerName 6> GO BankerName BankerState


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

HJI Inc. CA (1 rows affected) 1> 2> drop view BankersMin; 3> GO 1> 2> drop table Bankers; 3> GO</source>


Create a view with outer join and full join

   <source lang="sql">

5> CREATE TABLE authors( 6> au_id varchar(11), 7> au_lname varchar(40) NOT NULL, 8> au_fname varchar(20) NOT NULL, 9> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 10> address varchar(40) NULL, 11> city varchar(20) NULL, 12> state char(2) NULL, 13> zip char(5) NULL, 14> contract bit NOT NULL 15> ) 16> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 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 rows affected) 1> 2> CREATE TABLE titles( 3> title_id varchar(20), 4> title varchar(80) NOT NULL, 5> type char(12) NOT NULL, 6> pub_id char(4) NULL, 7> price money NULL, 8> advance money NULL, 9> royalty int NULL, 10> ytd_sales int NULL, 11> notes varchar(200) NULL, 12> pubdate datetime NOT NULL 13> ) 14> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> 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> 2> CREATE TABLE titleauthor( 3> au_id varchar(20), 4> title_id varchar(20), 5> au_ord tinyint NULL, 6> royaltyper int NULL 7> ) 8> GO 1> 2> insert titleauthor values("1", "2", 1, 60) 3> insert titleauthor values("2", "3", 1, 100) 4> insert titleauthor values("3", "4", 1, 100) 5> insert titleauthor values("4", "5", 1, 100) 6> insert titleauthor values("5", "6", 1, 100) 7> insert titleauthor values("6", "7", 2, 40) 8> insert titleauthor values("7", "8", 1, 100) 9> insert titleauthor values("8", "9", 1, 100) 10> 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> 2> 3> 4> 5> 6> CREATE VIEW outer_view AS 7> ( 8> SELECT 9> "Author"=RTRIM(au_lname) + ", " + au_fname, "Title"=title 10> FROM (titleauthor AS TA 11> FULL OUTER JOIN titles AS T ON (TA.title_id=T.title_id)) 12> RIGHT OUTER JOIN authors AS A ON (A.au_id=TA.au_id) 13> WHERE 14> 15> A.state <> "CA" OR A.state IS NULL 16> ) 17> GO 1> 2> SELECT * FROM outer_view WHERE Author LIKE "Ri%" ORDER BY Author 3> GO Author Title


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

(0 rows affected) 1> 2> drop VIEW outer_view; 3> GO 1> 2></source>


Create the view that combines all sales tables with union

   <source lang="sql">

3> CREATE TABLE Sales_West ( 4> Ordernum INT, 5> total money, 6> region char(5) check (region = "West"), 7> primary key (Ordernum, region) 8> ) 9> CREATE TABLE Sales_North ( 10> Ordernum INT, 11> total money, 12> region char(5) check (region = "North"), 13> primary key (Ordernum, region) 14> ) 15> CREATE TABLE Sales_East ( 16> Ordernum INT, 17> total money, 18> region char(5) check (region = "East"), 19> primary key (Ordernum, region) 20> ) 21> CREATE TABLE Sales_South ( 22> Ordernum INT, 23> total money, 24> region char(5) check (region = "South"), 25> primary key (Ordernum, region) 26> ) 27> GO 1> 2> INSERT Sales_West VALUES (16544, 2465, "West") 3> INSERT Sales_West VALUES (32123, 4309, "West") 4> INSERT Sales_North VALUES (16544, 3229, "North") 5> INSERT Sales_North VALUES (26544, 4000, "North") 6> INSERT Sales_East VALUES ( 22222, 43332, "East") 7> 8> INSERT Sales_East VALUES ( 77777, 10301, "East") 9> INSERT Sales_South VALUES (23456, 4320, "South") 10> INSERT Sales_South VALUES (16544, 9999, "South") 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> 2> -- Create the view that combines all sales tables 3> CREATE VIEW Sales_National 4> AS 5> SELECT * 6> FROM Sales_West 7> UNION ALL 8> SELECT * 9> FROM Sales_North 10> UNION ALL 11> SELECT * 12> FROM Sales_East 13> UNION ALL 14> SELECT * 15> FROM Sales_South 16> GO 1> 2> 3> SELECT * 4> FROM Sales_National 5> WHERE region = "South" 6> GO Ordernum total region


--------------------- ------
     16544             9999.0000 South
     23456             4320.0000 South

(2 rows affected) 1> 2> 3> drop TABLE Sales_West 4> drop TABLE Sales_North 5> drop TABLE Sales_East 6> drop TABLE Sales_South 7> GO 1> 2> drop VIEW Sales_National; 3> GO</source>


Use view to join four tables

   <source lang="sql">

5> CREATE TABLE Customers ( 6> CustomerID nchar (5) NOT NULL , 7> CompanyName nvarchar (40) NOT NULL , 8> ContactName nvarchar (30) NULL , 9> ContactTitle nvarchar (30) NULL , 10> Address nvarchar (60) NULL , 11> City nvarchar (15) NULL , 12> Region nvarchar (15) NULL , 13> PostalCode nvarchar (10) NULL , 14> Country nvarchar (15) NULL , 15> Phone nvarchar (24) NULL , 16> Fax nvarchar (24) NULL 17> ) 18> 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 TABLE Orders ( 4> OrderID int IDENTITY (1, 1) NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL 18> ) 19> GO 1> 2> CREATE TABLE Products ( 3> ProductID int NOT NULL , 4> ProductName nvarchar (40) NOT NULL , 5> SupplierID int NULL , 6> CategoryID int NULL , 7> QuantityPerUnit nvarchar (20) NULL , 8> UnitPrice money NULL, 9> UnitsInStock smallint NULL, 10> UnitsOnOrder smallint NULL, 11> ReorderLevel smallint NULL, 12> Discontinued bit NOT NULL 13> ) 14> GO 1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (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 TABLE OrderDetails ( 4> OrderID int NOT NULL , 5> ProductID int NOT NULL , 6> UnitPrice money NOT NULL DEFAULT (0), 7> Quantity smallint NOT NULL DEFAULT (1), 8> Discount real NOT NULL DEFAULT (0) 9> ) 10> GO 1> INSERT OrderDetails VALUES(10248,11,14,12,0) 2> INSERT OrderDetails VALUES(10248,42,9.8,10,0) 3> INSERT OrderDetails VALUES(10248,72,34.8,5,0) 4> INSERT OrderDetails VALUES(10249,14,18.6,9,0) 5> INSERT OrderDetails VALUES(10249,51,42.4,40,0) 6> INSERT OrderDetails VALUES(10250,41,7.7,10,0) 7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15) 8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15) 9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05) 10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05) 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 rows affected) 1> 2> 3> CREATE VIEW YesterdaysOrders_vw 4> AS 5> SELECT cu.rupanyName, 6> o.OrderID, 7> o.OrderDate, 8> od.ProductID, 9> p.ProductName, 10> od.Quantity, 11> od.UnitPrice, 12> od.Quantity * od.UnitPrice AS ExtendedPrice 13> FROM Customers AS cu 14> INNER JOIN Orders AS o 15> ON cu.CustomerID = o.CustomerID 16> INNER JOIN OrderDetails AS od 17> ON o.OrderID = od.OrderID 18> INNER JOIN Products AS p 19> ON od.ProductID = p.ProductID 20> WHERE CONVERT(varchar(12),o.OrderDate,101) = 21> CONVERT(varchar(12),DATEADD(day, -1,GETDATE()),101) 22> GO 1> 2> drop VIEW YesterdaysOrders_vw; 3> GO 1> drop table Customers; 2> drop table orders; 3> drop table orderdetails; 4> drop table Products; 5> GO</source>