SQL Server/T-SQL Tutorial/Query/Query Select

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

add a WHERE clause and group by

   <source lang="sql">

4> 5> 6> CREATE TABLE Employees ( 7> EmployeeID int NOT NULL , 8> LastName nvarchar (20) NOT NULL , 9> FirstName nvarchar (10) NOT NULL , 10> Title nvarchar (30) NULL , 11> TitleOfCourtesy nvarchar (25) NULL , 12> BirthDate datetime NULL , 13> HireDate datetime NULL , 14> Address nvarchar (60) NULL , 15> City nvarchar (15) NULL , 16> Region nvarchar (15) NULL , 17> PostalCode nvarchar (10) NULL , 18> Country nvarchar (15) NULL , 19> HomePhone nvarchar (24) NULL , 20> Extension nvarchar (4) NULL , 21> Photo image NULL , 22> Notes ntext NULL , 23> ReportsTo int NULL , 24> PhotoPath nvarchar (255) NULL 25> 26> ) 27> GO 1> 2> 3> SELECT ReportsTo AS Manager, COUNT(*) AS Reports 4> FROM Employees 5> WHERE EmployeeID != 5 6> GROUP BY ReportsTo 7> 8> GO Manager Reports


-----------

(0 rows affected) 1> 2> drop table Employees; 3> GO 1></source>


A SELECT statement that retrieves three columns from each row, sorted in descending sequence by Billing total

   <source lang="sql">

8> create table Billings ( 9> BankerID INTEGER, 10> BillingNumber INTEGER, 11> BillingDate datetime, 12> BillingTotal INTEGER, 13> TermsID INTEGER, 14> BillingDueDate datetime , 15> PaymentTotal INTEGER, 16> CreditTotal INTEGER 17> 18> ); 19> 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> SELECT BillingNumber, BillingDate, BillingTotal 4> FROM Billings 5> ORDER BY BillingTotal DESC 6> GO BillingNumber BillingDate BillingTotal


----------------------- ------------
           1 2005-01-22 00:00:00.000          165
           2 2001-02-21 00:00:00.000          165
           3 2003-05-02 00:00:00.000          165
           4 1999-03-12 00:00:00.000          165
           5 2000-04-23 00:00:00.000          165
           6 2001-06-14 00:00:00.000          165
           7 2002-07-15 00:00:00.000          165
           8 2003-08-16 00:00:00.000          165
           9 2004-09-17 00:00:00.000          165
           0 2005-10-18 00:00:00.000          165

(10 rows affected) 1> 2> drop table Billings; 3> GO 1> 2></source>


A SELECT statement that retrieves two columns and a calculated value for a specific Billing

   <source lang="sql">

3> 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> SELECT BankerID, BillingTotal, CreditTotal + PaymentTotal AS TotalCredits 3> FROM Billings 4> WHERE BankerID = 1 5> GO BankerID BillingTotal TotalCredits


------------ ------------
         1          165          444

(1 rows affected) 1> drop table Billings; 2> GO 1></source>


A SELECT statement that returns an empty result set

   <source lang="sql">

4> 5> 6> 7> create table Billings ( 8> BankerID INTEGER, 9> BillingNumber INTEGER, 10> BillingDate datetime, 11> BillingTotal INTEGER, 12> TermsID INTEGER, 13> BillingDueDate datetime , 14> PaymentTotal INTEGER, 15> CreditTotal INTEGER 16> 17> ); 18> 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> SELECT BillingNumber, BillingDate, BillingTotal 5> FROM Billings 6> WHERE BillingTotal > 50000 7> GO BillingNumber BillingDate BillingTotal


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

(0 rows affected) 1> 2> drop table Billings; 3> GO 1></source>


A SELECT statement that uses the CHAR function to format output

   <source lang="sql">

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> SELECT BankerName + CHAR(13) + CHAR(10) 4> + BankerCity + ", " + BankerState + " " + BankerZipCode 5> FROM Bankers 6> WHERE BankerID = 1 7> GO


ABC Inc. Vancouver, BC 11111 (1 rows affected) 1> 2> 3> drop table Bankers; 4> GO</source>


asterisk (*) specifies all columns of the named tables in the FROM clause

   <source lang="sql">

5> 6> CREATE TABLE employee( 7> id INTEGER NOT NULL PRIMARY KEY, 8> first_name VARCHAR(10), 9> last_name VARCHAR(10), 10> salary DECIMAL(10,2), 11> start_Date DATETIME, 12> region VARCHAR(10), 13> city VARCHAR(20), 14> managerid INTEGER 15> ); 16> 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> 4> 5> drop table employee; 6> GO 1></source>


Both uppercase and lowercase statements are acceptable, a query could be written as follows:

   <source lang="sql">

14> 15> CREATE TABLE employee( 16> id INTEGER NOT NULL PRIMARY KEY, 17> first_name VARCHAR(10), 18> last_name VARCHAR(10), 19> salary DECIMAL(10,2), 20> start_Date DATETIME, 21> region VARCHAR(10), 22> city VARCHAR(20), 23> managerid INTEGER 24> ); 25> 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 from employee; 4> GO first_name


Jason Alison James Celia Robert Linda David James Joan (9 rows affected) 1> 2> 3> SELECT FIRST_Name FROM EMPLOYEE; 4> GO FIRST_Name


Jason Alison James Celia Robert Linda David James Joan (9 rows affected) 1> 2> 3> drop table employee; 4> GO</source>


Operator Precedence

   <source lang="sql">

Precedence Operator(s) 1 0(parentheses) 2 + (Positive), - (Negative), ~ (Bitwise NOT) 3 * (Multiply), / (Divide), % (Modulo) 4 + (Add), (+ Concatenate), - (Subtract) 5 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators) 6 ^ (Bitwise XOR), & (Bitwise AND), | (Bitwise OR) 7 NOT 8 AND 9 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME 10 = (Assignment)</source>


retrieve specific columns

   <source lang="sql">

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> 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> SELECT BankerID, BillingNumber, TermsID from Billings 4> GO BankerID BillingNumber TermsID


------------- -----------
         1             1           1
         2             2           1
         3             3           1
         4             4           1
         5             5           1
         6             6           1
         7             7           1
         8             8           1
         9             9           1
         0             0           1

(10 rows affected) 1> 2> 3> drop table Billings; 4> GO 1></source>


SELECT (0 -- Returns 1.00

   <source lang="sql">

3> SELECT (1.0 + 3.0) / 4.0 -- Returns 1.00 4> GO


  1.000000

(1 rows affected)</source>


SELECT0 /- Returns 1.75

   <source lang="sql">

4> SELECT 1.0 + 3.0 / 4.0 -- Returns 1.75 5> GO


  1.750000

(1 rows affected)</source>


Table alias

   <source lang="sql">

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> 4> SELECT v.BankerName 5> FROM Bankers v 6> GO BankerName


ABC Inc. DEF Inc. HJI Inc. QWE Inc. RTY Inc. YUI Inc. OIP Inc. SAD Inc. DFG Inc. GHJ Inc. (10 rows affected) 1> 2> drop table Bankers; 3> GO</source>


The basic syntax of the SELECT statement

   <source lang="sql">

SELECT select_list FROM table_source [WHERE search_condition] [ORDER BY order_by_list] The four clauses of the SELECT statement Clause Description SELECT Describes the columns that will be included in the result set. FROM Names the table from which the query will retrieve the data. WHERE Specifies the conditions that must be met for a row to be included in the result set. This clause is optional. ORDER BY Specifies how the rows in the result set will be sorted. This clause is optional.


20> 21> create table Billings ( 22> BankerID INTEGER, 23> BillingNumber INTEGER, 24> BillingDate datetime, 25> BillingTotal INTEGER, 26> TermsID INTEGER, 27> BillingDueDate datetime , 28> PaymentTotal INTEGER, 29> CreditTotal INTEGER 30> 31> ); 32> 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> SELECT * FROM Billings 3> GO BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal


------------- ----------------------- ------------ ----------- ----------------------- ------------ -----------
         1             1 2005-01-22 00:00:00.000          165           1 2005-04-22 00:00:00.000          123         321
         2             2 2001-02-21 00:00:00.000          165           1 2002-02-22 00:00:00.000          123         321
         3             3 2003-05-02 00:00:00.000          165           1 2005-04-12 00:00:00.000          123         321
         4             4 1999-03-12 00:00:00.000          165           1 2005-04-18 00:00:00.000          123         321
         5             5 2000-04-23 00:00:00.000          165           1 2005-04-17 00:00:00.000          123         321
         6             6 2001-06-14 00:00:00.000          165           1 2005-04-18 00:00:00.000          123         321
         7             7 2002-07-15 00:00:00.000          165           1 2005-04-19 00:00:00.000          123         321
         8             8 2003-08-16 00:00:00.000          165           1 2005-04-20 00:00:00.000          123         321
         9             9 2004-09-17 00:00:00.000          165           1 2005-04-21 00:00:00.000          123         321
         0             0 2005-10-18 00:00:00.000          165           1 2005-04-22 00:00:00.000          123         321

(10 rows affected) 1> 2> drop table Billings; 3> GO 1> 2></source>


The operators you can use in a search condition

   <source lang="sql">

Operator Description != not equal !> not greater than !< not less than < less than <= less than or equal <> not being equal = equality between two expressions > greater than >= greater than or equal to ALL In subquery, if all retrieved values satisfy the search condition, the rows will be retrieved. ANY In subquery, if any retrieved values satisfy the search condition, the rows will be retrieved. BETWEEN Designates an inclusive range of values. Used with the AND clause between the beginning and ending values. CONTAINS Does a search for words and phrases. ESCAPE Escape from the pattern. EXISTS When used with a subquery, EXISTS tests for the existence of rows in the subquery. FREETEXT Searches character-based data for words using meaning, rather than literal values. IN Provides an inclusive list of values for the search condition. IS NOT NULL if the value is NOT null. IS NULL whether the value is null. LIKE pattern matching. NOT BETWEEN Specifies a range of values NOT to include. NOT IN Provides a list of values for which NOT to return rows for. NOT LIKE Tests character string, excluding those with pattern matches. SOME If any retrieved values satisfy the search condition, the rows will be retrieved.</source>


The specification column_name [as] column_heading

   <source lang="sql">

6> CREATE TABLE employee( 7> id INTEGER NOT NULL PRIMARY KEY, 8> first_name VARCHAR(10), 9> last_name VARCHAR(10), 10> salary DECIMAL(10,2), 11> start_Date DATETIME, 12> region VARCHAR(10), 13> city VARCHAR(20), 14> managerid INTEGER 15> ); 16> 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> SELECT ID FROM employee 3> GO ID


         1
         2
         3
         4
         5
         6
         7
         8
         9

(9 rows affected) 1> 2> 3> 4> drop table employee; 5> GO 1> 2></source>


Use local variable in a select statement

   <source lang="sql">

3> create table Billings ( 4> BankerID INTEGER, 5> BillingNumber INTEGER, 6> BillingDate datetime, 7> BillingTotal INTEGER, 8> TermsID INTEGER, 9> BillingDueDate datetime , 10> PaymentTotal INTEGER, 11> CreditTotal INTEGER 12> 13> ); 14> 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> DECLARE @intvar int 3> SET @intvar = 1 4> 5> SELECT TermsID, BillingNumber "Category name", 6> @intvar "Local variable" 7> FROM Billings 8> GO TermsID Category name Local variable


------------- --------------
         1             1              1
         1             2              1
         1             3              1
         1             4              1
         1             5              1
         1             6              1
         1             7              1
         1             8              1
         1             9              1
         1             0              1

(10 rows affected) 1> 2> 3> drop table Billings; 4> GO 1> 2></source>


Using SELECT to Create a Script

   <source lang="sql">

4> 5> 6> CREATE TABLE employee( 7> id INTEGER NOT NULL PRIMARY KEY, 8> first_name VARCHAR(10), 9> last_name VARCHAR(10), 10> salary DECIMAL(10,2), 11> start_Date DATETIME, 12> region VARCHAR(10), 13> city VARCHAR(20) 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city


---------- ---------- ------------ ----------------------- ---------- --------------------
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto

(9 rows affected) 1> 2> 3> 4> SELECT column_name + " IS NULL AND " 5> FROM INFORMATION_SCHEMA.columns 6> WHERE table_name = "Employee" 7> ORDER BY ORDINAL_POSITION 8> GO



id IS NULL AND first_name IS NULL AND last_name IS NULL AND salary IS NULL AND start_Date IS NULL AND region IS NULL AND city IS NULL AND

(7 rows affected) 1> 2> 3> drop table employee; 4> GO</source>