SQL Server/T-SQL Tutorial/Set Operations/Union — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:46, 26 мая 2010
Содержание
- 1 A union that combines information from the Billings table
- 2 A union that combines payment data from the same joined tables
- 3 Listing the output from two identical tables.
- 4 OR operator can be used instead of the UNION operator, as the two equivalent examples.
- 5 UNION same type of columns from different tables
- 6 Using Unions to Display Data from Multiple Queries
A union that combines information from the Billings table
4>
5> create table Billings (
6> BankerID INTEGER,
7> BillingNumber INTEGER,
8> BillingDate datetime,
9> BillingTotal INTEGER,
10> TermsID INTEGER,
11> BillingDueDate datetime ,
12> PaymentTotal INTEGER,
13> CreditTotal INTEGER
14>
15> );
16> 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>
3> create table Bankers(
4> BankerID Integer,
5> BankerName VARCHAR(20),
6> BankerContactLName VARCHAR(20),
7> BankerContactFName VARCHAR(20),
8> BankerCity VARCHAR(20),
9> BankerState VARCHAR(20),
10> BankerZipCode VARCHAR(20),
11> BankerPhone VARCHAR(20)
12> )
13> 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>
5> SELECT "Active" AS Source, BillingNumber, BillingDate, BillingTotal
6> FROM Billings
7> WHERE BillingTotal - PaymentTotal - CreditTotal > 0
8> UNION
9> SELECT "Paid" AS Source, BillingNumber, BillingDate, BillingTotal
10> FROM Billings
11> WHERE BillingTotal - PaymentTotal - CreditTotal <= 0
12> ORDER BY BillingTotal DESC
13> GO
Source BillingNumber BillingDate BillingTotal
------ ------------- ----------------------- ------------
Paid 0 2005-10-18 00:00:00.000 165
Paid 1 2005-01-22 00:00:00.000 165
Paid 2 2001-02-21 00:00:00.000 165
Paid 3 2003-05-02 00:00:00.000 165
Paid 4 1999-03-12 00:00:00.000 165
Paid 5 2000-04-23 00:00:00.000 165
Paid 6 2001-06-14 00:00:00.000 165
Paid 7 2002-07-15 00:00:00.000 165
Paid 8 2003-08-16 00:00:00.000 165
Paid 9 2004-09-17 00:00:00.000 165
(10 rows affected)
1>
2> drop table Billings;
3> drop table Bankers;
4> GO
A union that combines payment data from the same joined tables
4>
5> create table Billings (
6> BankerID INTEGER,
7> BillingNumber INTEGER,
8> BillingDate datetime,
9> BillingTotal INTEGER,
10> TermsID INTEGER,
11> BillingDueDate datetime ,
12> PaymentTotal INTEGER,
13> CreditTotal INTEGER
14>
15> );
16> 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>
3>
4> create table Bankers(
5> BankerID Integer,
6> BankerName VARCHAR(20),
7> BankerContactLName VARCHAR(20),
8> BankerContactFName VARCHAR(20),
9> BankerCity VARCHAR(20),
10> BankerState VARCHAR(20),
11> BankerZipCode VARCHAR(20),
12> BankerPhone VARCHAR(20)
13> )
14> 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> SELECT BillingNumber, BankerName, "33% Payment" AS PaymentType,
4> BillingTotal AS Total, (BillingTotal * 0.333) AS Payment
5> FROM Billings JOIN Bankers
6> ON Billings.BankerID = Bankers.BankerID
7> WHERE BillingTotal > 10000
8> UNION
9> SELECT BillingNumber, BankerName, "50% Payment" AS PaymentType,
10> BillingTotal AS Total, (BillingTotal * 0.5) AS Payment
11> FROM Billings JOIN Bankers
12> ON Billings.BankerID = Bankers.BankerID
13> WHERE BillingTotal BETWEEN 500 AND 10000
14> UNION
15> SELECT BillingNumber, BankerName, "Full amount" AS PaymentType,
16> BillingTotal AS Total, BillingTotal AS Payment
17> FROM Billings JOIN Bankers
18> ON Billings.BankerID = Bankers.BankerID
19> WHERE BillingTotal < 500
20> ORDER BY PaymentType, BankerName, BillingNumber
21> GO
BillingNumber BankerName PaymentType Total Payment
------------- -------------------- ----------- ----------- ----------------
1 ABC Inc. Full amount 165 165.000
2 DEF Inc. Full amount 165 165.000
9 DFG Inc. Full amount 165 165.000
0 GHJ Inc. Full amount 165 165.000
3 HJI Inc. Full amount 165 165.000
7 OIP Inc. Full amount 165 165.000
4 QWE Inc. Full amount 165 165.000
5 RTY Inc. Full amount 165 165.000
8 SAD Inc. Full amount 165 165.000
6 YUI Inc. Full amount 165 165.000
(10 rows affected)
1>
2> drop table Billings
3> drop table Bankers;
4> GO
1>
2>
Listing the output from two identical tables.
3> CREATE TABLE SalesMw (
4> StoreID integer NOT NULL,
5> CD_ID integer NOT NULL,
6> QtySold integer,
7> SalesDate datetime)
8> GO
1> INSERT into SalesMw VALUES(1300,2001,10,"Oct 31,1997")
2> INSERT into SalesMw VALUES(1300,2002,15,"Oct 31,1997")
3> INSERT into SalesMw VALUES(1300,2001,5,"Nov 30,1997")
4> INSERT into SalesMw VALUES(1300,2003,16,"Nov 30,1997")
5> INSERT into SalesMw VALUES(1300,2017,9,"Nov 30,1997")
6> INSERT into SalesMw VALUES(1330,2000,2,"Oct 31,1997")
7> INSERT into SalesMw VALUES(1330,2000,109,"Nov 30,1997")
8> INSERT into SalesMw VALUES(1330,2029,5,"Nov 30,1997")
9> INSERT into SalesMw VALUES(1330,2030,5,"Nov 30,1997")
10> INSERT into SalesMw VALUES(1330,2015,20,"Nov 30,1997")
11> INSERT into SalesMw VALUES(1330,2016,66,"Nov 30,1997")
12> INSERT into SalesMw VALUES(1310,2005,11,"Nov 30,1997")
13> INSERT into SalesMw VALUES(1320,2022,14,"Nov 30,1997")
14> 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 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TABLE SalesNe (
3> StoreID integer NOT NULL,
4> CD_ID integer NOT NULL,
5> QtySold integer,
6> SalesDate datetime)
7>
8> INSERT into SalesNe VALUES(1200,2006,10,"Oct 31,1997")
9> INSERT into SalesNe VALUES(1200,2007,15,"Oct 31,1997")
10> INSERT into SalesNe VALUES(1200,2007,8,"Nov 30,1997")
11> INSERT into SalesNe VALUES(1200,2008,12,"Nov 30,1997")
12> INSERT into SalesNe VALUES(1200,2009,19,"Nov 30,1997")
13> INSERT into SalesNe VALUES(1210,2008,7,"Oct 31,1997")
14> INSERT into SalesNe VALUES(1210,2009,55,"Nov 30,1997")
15> INSERT into SalesNe VALUES(1210,2010,18,"Nov 30,1997")
16> INSERT into SalesNe VALUES(1210,2022,11,"Nov 30,1997")
17> INSERT into SalesNe VALUES(1210,2023,20,"Nov 30,1997")
18> INSERT into SalesNe VALUES(1210,2020,15,"Nov 30,1997")
19> INSERT into SalesNe VALUES(1220,2018,6,"Nov 30,1997")
20> INSERT into SalesNe VALUES(1220,2005,3,"Nov 30,1997")
21>
22>
23>
24>
25>
26>
27>
28>
29>
30> SELECT * from SalesMw
31> UNION
32> SELECT * from SalesNe
33>
34> 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 rows affected)
(1 rows affected)
(1 rows affected)
StoreID CD_ID QtySold SalesDate
----------- ----------- ----------- -----------------------
1200 2006 10 1997-10-31 00:00:00.000
1200 2007 8 1997-11-30 00:00:00.000
1200 2007 15 1997-10-31 00:00:00.000
1200 2008 12 1997-11-30 00:00:00.000
1200 2009 19 1997-11-30 00:00:00.000
1210 2008 7 1997-10-31 00:00:00.000
1210 2009 55 1997-11-30 00:00:00.000
1210 2010 18 1997-11-30 00:00:00.000
1210 2020 15 1997-11-30 00:00:00.000
1210 2022 11 1997-11-30 00:00:00.000
1210 2023 20 1997-11-30 00:00:00.000
1220 2005 3 1997-11-30 00:00:00.000
1220 2018 6 1997-11-30 00:00:00.000
1300 2001 5 1997-11-30 00:00:00.000
1300 2001 10 1997-10-31 00:00:00.000
1300 2002 15 1997-10-31 00:00:00.000
1300 2003 16 1997-11-30 00:00:00.000
1300 2017 9 1997-11-30 00:00:00.000
1310 2005 11 1997-11-30 00:00:00.000
1320 2022 14 1997-11-30 00:00:00.000
1330 2000 2 1997-10-31 00:00:00.000
1330 2000 109 1997-11-30 00:00:00.000
1330 2015 20 1997-11-30 00:00:00.000
1330 2016 66 1997-11-30 00:00:00.000
1330 2029 5 1997-11-30 00:00:00.000
1330 2030 5 1997-11-30 00:00:00.000
(26 rows affected)
1>
2> drop table SalesNe;
3> GO
OR operator can be used instead of the UNION operator, as the two equivalent examples.
3>
4>
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> SELECT first_name
4> FROM employee
5> WHERE id = "1"
6> UNION
7> SELECT first_name
8> FROM employee
9> WHERE id = "2"
10> GO
first_name
----------
Alison
Jason
(2 rows affected)
1>
2>
3> SELECT first_name
4> FROM employee
5> WHERE id ="1" OR id = "2"
6> GO
first_name
----------
Jason
Alison
(2 rows affected)
1>
2>
3> drop table employee;
4> GO
UNION same type of columns from different tables
7> CREATE TABLE Employees (
8> EmployeeID int NOT NULL ,
9> LastName nvarchar (20) NOT NULL ,
10> FirstName nvarchar (10) NOT NULL ,
11> Title nvarchar (30) NULL ,
12> TitleOfCourtesy nvarchar (25) NULL ,
13> BirthDate datetime NULL ,
14> HireDate datetime NULL ,
15> Address nvarchar (60) NULL ,
16> City nvarchar (15) NULL ,
17> Region nvarchar (15) NULL ,
18> PostalCode nvarchar (10) NULL ,
19> Country nvarchar (15) NULL ,
20> HomePhone nvarchar (24) NULL ,
21> Extension nvarchar (4) NULL ,
22> Photo image NULL ,
23> Notes ntext NULL ,
24> ReportsTo int NULL ,
25> PhotoPath nvarchar (255) NULL
26>
27> )
28> 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 TABLE Suppliers (
4> SupplierID int NOT NULL ,
5> CompanyName nvarchar (40) NOT NULL ,
6> ContactName nvarchar (30) NULL ,
7> ContactTitle nvarchar (30) NULL ,
8> Address nvarchar (60) NULL ,
9> City nvarchar (15) NULL ,
10> Region nvarchar (15) NULL ,
11> PostalCode nvarchar (10) NULL ,
12> Country nvarchar (15) NULL ,
13> Phone nvarchar (24) NULL ,
14> Fax nvarchar (24) NULL ,
15> HomePage ntext NULL
16> )
17> GO
1>
2>
3> INSERT Suppliers VALUES(1,"L","N","Manager","L 10","Van",NULL,"2800","Paris","1114108","43844115",NULL)
4> INSERT Suppliers VALUES(2,"Z","D","Manager","V 22","Zaa",NULL,"9999 ZZ","USA"," 1212","(12345) 1210",NULL)
5> INSERT Suppliers VALUES(3,"K","A","Manager","V 12","Lap",NULL,"53120","Finland"," 10956",NULL,NULL)
6> INSERT Suppliers VALUES(4,"G","W","Tester", "1 Hill","Sydney","NSW","2042","Australia","(02) 555-5914","(021) 555-2222",null)
7> INSERT Suppliers VALUES(5,"M","J","Manager","2 St.","Mon","BC","H1J 1C3","Canada","(514) 555-9022",NULL,NULL)
8> INSERT Suppliers VALUES(6,"P","G","Administrator","V 153","Sal",NULL,"84100","Italy","(089) 6547665","(089) 1111111",NULL)
9> INSERT Suppliers VALUES(7,"E","M","Sales","22 Str","Mont",NULL,"71300","France","85.57.00.07",NULL,NULL)
10> INSERT Suppliers VALUES(8,"G","E","Sales","B Ave","Ann",NULL,"74000","France","38.76.98.06","38.76.98.58",NULL)
11> INSERT Suppliers VALUES(9,"F","C","Manager","1 Str","Ste","Calgary","J2S 7S8","Canada","(514) 555-2955","(514) 555-2921",NULL)
12> 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> SELECT CompanyName AS Name,
4> Address,
5> City,
6> Region,
7> PostalCode,
8> Country
9> FROM Customers
10> UNION
11> SELECT CompanyName,
12> Address,
13> City,
14> Region,
15> PostalCode,
16> Country
17> FROM Suppliers
18> UNION
19> SELECT FirstName + " " + LastName,
20> Address,
21> City,
22> Region,
23> PostalCode,
24> Country
25> FROM Employees
26> go
Name Address City Region PostalCode Country
---------------------------------------- ------------------------------------------------------------ --------------- --------------- ---------- ---------------
A Str. 57 Berlin NULL 12209 Germany
B Blv 8 Toronto NULL 00222 Sweden
C Araq, 67 Paris NULL 28023 Spain
D 23 Blvd. Tsawassen BC T2F8M4 Canada
E 22 Str Mont NULL 71300 France
F 1 Str Ste Calgary J2S 7S8 Canada
F 24 Dalles NULL 67000 France
G 1 Hill Sydney NSW 2042 Australia
G B Ave Ann NULL 74000 France
H Sq. 111 London NULL 1D00P UK
K V 12 Lap NULL 53120 Finland
L L 10 Van NULL 2800 Paris
M 2 St. Mon BC H1J 1C3 Canada
M Ave. 231 Vancouver NULL 05023 Mexico
P 12 Beijing NULL 13008 France
P V 153 Sal NULL 84100 Italy
S Fort 57 New York NULL 68306 Germany
Z V 22 Zaa NULL 9999 ZZ USA
(18 rows affected)
1>
2> drop table Customers;
3> drop table Employees;
4> drop table Suppliers;
5> GO
Using Unions to Display Data from Multiple Queries
5> CREATE TABLE Classification (
6> Classif_ID integer NOT NULL PRIMARY KEY,
7> Classification varchar(25))
8> GO
1>
2> INSERT into Classification VALUES( 1,"Pop")
3> INSERT into Classification VALUES( 2,"Country")
4> INSERT into Classification VALUES( 3,"Alternative")
5> INSERT into Classification VALUES( 4,"Metal")
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE CD (
4> CD_ID integer NOT NULL PRIMARY KEY,
5> CD_Title varchar(40),
6> Composer_ID integer NOT NULL,
7> Classif_ID integer NOT NULL,
8> SalesPrice money,
9> AverageCost money)
10> GO
1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99)
2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99)
3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99)
4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99)
5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99)
6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99)
7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99)
8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99)
9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99)
10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99)
11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99)
12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99)
13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99)
14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99)
15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99)
16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99)
17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99)
18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99)
19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99)
20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99)
21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99)
22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99)
23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99)
24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99)
25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99)
26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99)
27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99)
28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99)
29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99)
30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99)
31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99)
32> 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 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 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 rows affected)
1>
2>
3> SELECT CONVERT(varchar(30),SalesPrice) from CD
4> UNION
5> Select Classification from Classification
6> GO
------------------------------
11.99
12.99
13.99
14.99
15.99
16.99
19.99
25.99
9.99
Alternative
Country
Metal
Pop
(13 rows affected)
1>
2> drop table Classification;
3>
4> drop table CD;
5> GO