SQL Server/T-SQL Tutorial/Table/Table alias
Содержание
Ambiguous column name during table join
4>
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>
2>
3> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
4> GO
(1 rows affected)
1>
2>
3>
4> UPDATE Billings
5> SET CreditTotal = CreditTotal + 100
6> FROM(SELECT TOP 5 * FROM Billings) AS TopBillings
7>
8> GO
Msg 209, Level 16, State 1, Server J\SQLEXPRESS, Line 5
Ambiguous column name "CreditTotal".
1>
2>
3> drop table Billings;
4> GO
Column name with or without a table alias
2>
3> CREATE TABLE stores(
4> stor_id char(4) NOT NULL,
5> stor_name varchar(40) NULL,
6> stor_address varchar(40) NULL,
7> city varchar(20) NULL,
8> state char(2) NULL,
9> zip char(5) NULL
10> )
11> GO
1> insert stores values("1","B","567 Ave.","Tustin", "CA","92789")
2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745")
3> insert stores values("3","T","679 St.", "Portland", "OR","89076")
4> insert stores values("4","F","89 St.", "Fremont", "CA","90019")
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE discounts(
4> discounttype varchar(40) NOT NULL,
5> stor_id char(4) NULL ,
6> lowqty smallint NULL,
7> highqty smallint NULL,
8> discount dec(4,2) NOT NULL
9> )
10> GO
1>
2> insert discounts values("Initial Customer", NULL, NULL, NULL, 10.5)
3> insert discounts values("Volume Discount", NULL, 100, 1000, 6.7)
4> insert discounts values("Customer Discount", "8042", NULL, NULL, 5.0)
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT discounttype, discount, s.stor_name
3> FROM discounts d
4> JOIN stores s
5> ON d.stor_id = s.stor_id
6> GO
discounttype discount stor_name
---------------------------------------- -------- ----------------------------------------
(0 rows affected)
1>
2> drop table stores;
3> drop table discounts;
4> GO
Query two tables with column in common
4> CREATE TABLE Suppliers (
5> SupplierID int NOT NULL ,
6> CompanyName nvarchar (40) NOT NULL ,
7> ContactName nvarchar (30) NULL ,
8> ContactTitle nvarchar (30) NULL ,
9> Address nvarchar (60) NULL ,
10> City nvarchar (15) NULL ,
11> Region nvarchar (15) NULL ,
12> PostalCode nvarchar (10) NULL ,
13> Country nvarchar (15) NULL ,
14> Phone nvarchar (24) NULL ,
15> Fax nvarchar (24) NULL ,
16> HomePage ntext NULL
17> )
18> 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> CREATE TABLE Products (
4> ProductID int NOT NULL ,
5> ProductName nvarchar (40) NOT NULL ,
6> SupplierID int NULL ,
7> CategoryID int NULL ,
8> QuantityPerUnit nvarchar (20) NULL ,
9> UnitPrice money NULL,
10> UnitsInStock smallint NULL,
11> UnitsOnOrder smallint NULL,
12> ReorderLevel smallint NULL,
13> Discontinued bit NOT NULL
14> )
15> 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> SELECT *
2> FROM Products, Suppliers
3> WHERE Products.SupplierID = Suppliers.SupplierID
4> GO
ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued SupplierID CompanyName
ContactName ContactTitle Address City Region PostalCode Country
Phone Fax HomePage
----------- ---------------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------ ----------- ----------------
------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------ --------------- --------------- ---------- ---------
------ ------------------------ ------------------------ -----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
4 L 4 7 5 kg pkg. 10.0000 4 20 5 0 4 G
W Tester 1 Hill Sydney NSW 2042 Australia
(02) 555-5914 (021) 555-2222 NULL
(1 rows affected)
1>
2> drop table Products;
3> drop table Suppliers;
4> GO
Reference column with table name
6> CREATE TABLE Products (
7> ProductID int NOT NULL ,
8> ProductName nvarchar (40) NOT NULL ,
9> SupplierID int NULL ,
10> CategoryID int NULL ,
11> QuantityPerUnit nvarchar (20) NULL ,
12> UnitPrice money NULL,
13> UnitsInStock smallint NULL,
14> UnitsOnOrder smallint NULL,
15> ReorderLevel smallint NULL,
16> Discontinued bit NOT NULL
17> )
18> 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> CREATE TABLE Suppliers (
3> SupplierID int 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> HomePage ntext NULL
15> )
16> 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> SELECT Products.*, Suppliers.SupplierID
3> FROM Products
4> INNER JOIN Suppliers
5> ON Products.SupplierID = Suppliers.SupplierID
6>
7> GO
ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued SupplierID
----------- ---------------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------ -----------
4 L 4 7 5 kg pkg. 10.0000 4 20 5 0 4
(1 rows affected)
1>
2> drop table Products;
3> GO
1>
2> drop table Suppliers;
3> GO
Table alias during table join
4> CREATE TABLE Customers (
5> CustomerID nchar (5) NOT NULL ,
6> CompanyName nvarchar (40) NOT NULL ,
7> ContactName nvarchar (30) NULL ,
8> ContactTitle nvarchar (30) NULL ,
9> Address nvarchar (60) NULL ,
10> City nvarchar (15) NULL ,
11> Region nvarchar (15) NULL ,
12> PostalCode nvarchar (10) NULL ,
13> Country nvarchar (15) NULL ,
14> Phone nvarchar (24) NULL ,
15> Fax nvarchar (24) NULL
16> )
17> 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 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>
3> INSERT INTO Orders VALUES (10248,"1",5,"7/4/1996","8/1/2001","7/16/2001",3,32.38,"V","A","R", NULL,N"51100","France")
7> go
1>
2>
3>
4>
5> SELECT c.CustomerID, CompanyName
6> FROM Customers c
7> LEFT OUTER JOIN Orders o
8> ON c.CustomerID = o.CustomerID
9> WHERE o.CustomerID IS NULL
10>
11> GO
CustomerID CompanyName
---------- ----------------------------------------
1 A
2 M
3 H
4 B
5 S
6 F
7 C
8 P
9 D
(9 rows affected)
1>
2> drop table orders;
3> drop table customers;
4> GO
1>
2>