Create the view that combines all sales tables with union : Filter view « View « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. View
  3. Filter view
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








16.6.Filter view
16.6.1.A script that creates a stored procedure as a filter-view
16.6.2.A SELECT statement that uses the BankersMin view with where clause
16.6.3.Use view to join four tables
16.6.4.Create the view that combines all sales tables with union
16.6.5.Create a view with outer join and full join