Mega Code Archive
Create the view that combines all sales tables with union
<![CDATA[
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
]]>