SQL Server/T-SQL Tutorial/Subquery/Subquery

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

<> ALL with subquery

   <source lang="sql">

5> CREATE TABLE authors( 6> au_id varchar(11), 7> au_lname varchar(40) NOT NULL, 8> au_fname varchar(20) NOT NULL, 9> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 10> address varchar(40) NULL, 11> city varchar(20) NULL, 12> state char(2) NULL, 13> zip char(5) NULL, 14> contract bit NOT NULL 15> ) 16> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 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 rows affected) 1> 2> 3> CREATE TABLE titleauthor( 4> au_id varchar(20), 5> title_id varchar(20), 6> au_ord tinyint NULL, 7> royaltyper int NULL 8> ) 9> GO 1> 2> insert titleauthor values("1", "2", 1, 60) 3> insert titleauthor values("2", "3", 1, 100) 4> insert titleauthor values("3", "4", 1, 100) 5> insert titleauthor values("4", "5", 1, 100) 6> insert titleauthor values("5", "6", 1, 100) 7> insert titleauthor values("6", "7", 2, 40) 8> insert titleauthor values("7", "8", 1, 100) 9> insert titleauthor values("8", "9", 1, 100) 10> 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> 3> 4> SELECT "Author ID"=A.au_id, 5> "Author"=CONVERT(varchar(20), RTRIM(au_lname) + ", " 6> + RTRIM(au_fname)), state 7> FROM authors A 8> WHERE A.au_id <> ALL 9> (SELECT B.au_id FROM titleauthor B) 10> GO Author ID Author state


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

9 Gold, Dep CA 10 Siler, Dean CA (2 rows affected) 1> 2> 3> SELECT "Author ID"=A.au_id, 4> "Author"=CONVERT(varchar(20), RTRIM(au_lname) + ", " 5> + RTRIM(au_fname)), state 6> FROM authors A 7> WHERE A.au_id NOT IN 8> (SELECT B.au_id FROM titleauthor B) 9> GO Author ID Author state


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

9 Gold, Dep CA 10 Siler, Dean CA (2 rows affected) 1> 2> 3> drop table authors; 4> drop table titleauthor; 5> GO 1></source>


A query that uses three subqueries

   <source lang="sql">

5> 6> 7> 8> create table Bankers( 9> BankerID Integer, 10> BankerName VARCHAR(20), 11> BankerContactLName VARCHAR(20), 12> BankerContactFName VARCHAR(20), 13> BankerCity VARCHAR(20), 14> BankerState VARCHAR(20), 15> BankerZipCode VARCHAR(20), 16> BankerPhone VARCHAR(20) 17> ) 18> 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> 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> SELECT Summary1.BankerState, Summary1.BankerName, TopInState.SumOfBillings 3> FROM 4> (SELECT V_Sub.BankerState, V_Sub.BankerName, 5> SUM(I_Sub.BillingTotal) AS SumOfBillings 6> FROM Billings AS I_Sub JOIN Bankers AS V_Sub 7> ON I_Sub.BankerID = V_Sub.BankerID 8> GROUP BY V_Sub.BankerState, V_Sub.BankerName) AS Summary1 9> JOIN 10> (SELECT Summary2.BankerState, 11> MAX(Summary2.SumOfBillings) AS SumOfBillings 12> FROM 13> (SELECT V_Sub.BankerState, V_Sub.BankerName, 14> SUM(I_Sub.BillingTotal) AS SumOfBillings 15> FROM Billings AS I_Sub JOIN Bankers AS V_Sub 16> ON I_Sub.BankerID = V_Sub.BankerID 17> GROUP BY V_Sub.BankerState, V_Sub.BankerName) AS Summary2 18> GROUP BY Summary2.BankerState) AS TopInState 19> ON Summary1.BankerState = TopInState.BankerState AND 20> Summary1.SumOfBillings = TopInState.SumOfBillings 21> ORDER BY Summary1.BankerState 22> GO BankerState BankerName SumOfBillings


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

BC ABC Inc. 165 CA HJI Inc. 165 DE DEF Inc. 165 ER QWE Inc. 165 FG OIP Inc. 165 MN GHJ Inc. 165 PL DFG Inc. 165 PO SAD Inc. 165 TY YUI Inc. 165 YU RTY Inc. 165 (10 rows affected) 1> 2> drop table Bankers; 3> drop table Billings; 4> GO 1></source>


A subquery can be used with other comparison operators:

   <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> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 4> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 5> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 6> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 7> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 8> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 9> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 10> 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> select * from employee; 2> 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> CREATE TABLE title( 3> id INTEGER, 4> job_title VARCHAR(20) 5> ); 6> GO 1> INSERT INTO title VALUES (1, "developer"); 2> INSERT INTO title VALUES (2, "manager"); 3> 4> GO (1 rows affected) (1 rows affected) 1> 2> SELECT DISTINCT id 3> FROM employee 4> WHERE id < 5> (SELECT id 6> FROM title 7> WHERE job_title = "developer") 8> 9> 10> 11> drop table employee; 12> drop table title; 13> GO id


(0 rows affected) 1> --Any comparison operator can be used, provided the inner query returns exactly one row. 2></source>


Building a Nested Subquery

   <source lang="sql">

A subquery is a SELECT query that is nested within another SELECT, INSERT, UPDATE, or DELETE statement. A subquery can also be nested inside another subquery. Subqueries can often be re-written into regular JOINs. Sometimes an existence subquery can perform better than equivalent non-subquery methods. A correlated subquery is a subquery whose results depend on the values of the outer query.

SELECT <SELECT list> FROM <SomeTable> WHERE <SomeColumn> = (

      SELECT <single column>
      FROM <SomeTable>
      WHERE <condition that results in only one row returned>)

Or: SELECT <SELECT list> FROM <SomeTable> WHERE <SomeColumn> IN (

          SELECT <single column>
          FROM <SomeTable>
          [WHERE <condition>])</source>
   
  

Delete with subquery

   <source lang="sql">

4> CREATE TABLE employee( 5> id INTEGER NOT NULL PRIMARY KEY, 6> first_name VARCHAR(10), 7> last_name VARCHAR(10), 8> salary DECIMAL(10,2), 9> start_Date DATETIME, 10> region VARCHAR(10), 11> city VARCHAR(20) 12> ); 13> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 4> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 5> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 6> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 7> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 8> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 9> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 10> 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> select * from employee; 2> 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> CREATE TABLE title( 3> id INTEGER, 4> job_title VARCHAR(20) 5> ); 6> GO 1> INSERT INTO title VALUES (1, "developer"); 2> INSERT INTO title VALUES (1, "manager"); 3> INSERT INTO title VALUES (2, "tester"); 4> INSERT INTO title VALUES (2, "programmer"); 5> INSERT INTO title VALUES (3, "boss"); 6> INSERT INTO title VALUES (4, "sales"); 7> INSERT INTO title VALUES (5, "market"); 8> INSERT INTO title VALUES (6, "coder"); 9> INSERT INTO title VALUES (7, "tester"); 10> INSERT INTO title VALUES (8, "developer"); 11> INSERT INTO title VALUES (9, "manager"); 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 rows affected) (1 rows affected) 1> 2> 3> DELETE FROM Employee 4> WHERE ID NOT IN 5> (SELECT ID FROM Title) 6> 7> drop table employee; 8> drop table title; 9> GO (0 rows affected) 1> 2></source>


Doing calculation with subquery

   <source lang="sql">

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 ID, Salary, Salary - (SELECT Avg(Salary) FROM Employee) 4> As AvgSalaryDifference 5> FROM Employee 6> GO ID Salary AvgSalaryDifference


------------ ----------------------------------------
         1      5890.00                               384.555556
         2      4789.00                              -716.444444
         3      6678.00                              1172.555556
         4      5567.00                                61.555556
         5      4467.00                             -1038.444444
         6      6456.00                               950.555556
         7      5345.00                              -160.444444
         8      4234.00                             -1271.444444
         9      6123.00                               617.555556

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


ID NOT IN (SELECT ID FROM Title)

   <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> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 4> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 5> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 6> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 7> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 8> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 9> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 10> 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> select * from employee; 2> 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> CREATE TABLE title( 3> id INTEGER, 4> job_title VARCHAR(20) 5> ); 6> GO 1> INSERT INTO title VALUES (1, "developer"); 2> INSERT INTO title VALUES (1, "manager"); 3> INSERT INTO title VALUES (2, "tester"); 4> INSERT INTO title VALUES (2, "programmer"); 5> INSERT INTO title VALUES (3, "boss"); 6> INSERT INTO title VALUES (4, "sales"); 7> INSERT INTO title VALUES (5, "market"); 8> INSERT INTO title VALUES (6, "coder"); 9> INSERT INTO title VALUES (7, "tester"); 10> INSERT INTO title VALUES (8, "developer"); 11> INSERT INTO title VALUES (9, "manager"); 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 rows affected) (1 rows affected) 1> 2> 3> SELECT ID, First_Name 4> FROM Employee 5> WHERE ID NOT IN 6> (SELECT ID FROM Title) 7> GO ID First_Name


----------

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


Join and sub query

   <source lang="sql">

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> CREATE TABLE OrderDetails ( 4> OrderID int NOT NULL , 5> ProductID int NOT NULL , 6> UnitPrice money NOT NULL DEFAULT (0), 7> Quantity smallint NOT NULL DEFAULT (1), 8> Discount real NOT NULL DEFAULT (0) 9> ) 10> GO 1> INSERT OrderDetails VALUES(10248,11,14,12,0) 2> INSERT OrderDetails VALUES(10248,42,9.8,10,0) 3> INSERT OrderDetails VALUES(10248,72,34.8,5,0) 4> INSERT OrderDetails VALUES(10249,14,18.6,9,0) 5> INSERT OrderDetails VALUES(10249,51,42.4,40,0) 6> INSERT OrderDetails VALUES(10250,41,7.7,10,0) 7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15) 8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15) 9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05) 10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05) 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 rows affected) 1> 2> DECLARE @FirstDate smalldatetime 3> 4> SELECT @FirstDate = MIN(OrderDate) FROM Orders 5> 6> SELECT DISTINCT o.OrderDate, od.ProductID 7> FROM Orders o 8> JOIN OrderDetails od 9> ON o.OrderID = od.OrderID 10> WHERE o.OrderDate = @FirstDate 11> GO OrderDate ProductID


-----------

(0 rows affected) 1> 2> SELECT DISTINCT o.OrderDate, od.ProductID 3> FROM Orders o 4> JOIN OrderDetails od 5> ON o.OrderID = od.OrderID 6> WHERE o.OrderDate = (SELECT MIN(OrderDate) FROM Orders) 7> GO OrderDate ProductID


-----------

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


Subqueries and Comparison Operators: =

   <source lang="sql">

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> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 4> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 5> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 6> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 7> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 8> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 9> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 10> 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> select * from employee; 2> 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> CREATE TABLE title( 3> id INTEGER, 4> job_title VARCHAR(20) 5> ); 6> GO 1> INSERT INTO title VALUES (1, "developer"); 2> INSERT INTO title VALUES (2, "tester"); 3> GO (1 rows affected) (1 rows affected) 1> 2> 3> SELECT * FROM employee 4> WHERE id = 5> (SELECT id 6> FROM title 7> WHERE job_title = "developer") 8> 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

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


Subqueries and IN Operator

   <source lang="sql">

The IN operator allows the specification of a set of expressions (or constants) that are subsequently used for the query search. 8> 9> 10> CREATE TABLE employee( 11> id INTEGER NOT NULL PRIMARY KEY, 12> first_name VARCHAR(10), 13> last_name VARCHAR(10), 14> salary DECIMAL(10,2), 15> start_Date DATETIME, 16> region VARCHAR(10), 17> city VARCHAR(20) 18> ); 19> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 4> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 5> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 6> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 7> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 8> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 9> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 10> 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> select * from employee; 2> 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> CREATE TABLE title( 3> id INTEGER, 4> job_title VARCHAR(20) 5> ); 6> GO 1> INSERT INTO title VALUES (1, "developer"); 2> INSERT INTO title VALUES (1, "manager"); 3> INSERT INTO title VALUES (2, "tester"); 4> INSERT INTO title VALUES (2, "programmer"); 5> INSERT INTO title VALUES (3, "boss"); 6> INSERT INTO title VALUES (4, "sales"); 7> INSERT INTO title VALUES (5, "market"); 8> INSERT INTO title VALUES (6, "coder"); 9> INSERT INTO title VALUES (7, "tester"); 10> INSERT INTO title VALUES (8, "developer"); 11> INSERT INTO title VALUES (9, "manager"); 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 rows affected) (1 rows affected) 1> 2> 3> SELECT * FROM employee WHERE id IN (SELECT id FROM title) 4> 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> drop table employee; 4> drop table title; 5> GO 1></source>


Subqueries are SELECT statements that are nested within another T-SQL statement.

   <source lang="sql">

5> 6> CREATE TABLE titles( 7> title_id varchar(20), 8> title varchar(80) NOT NULL, 9> type char(12) NOT NULL, 10> pub_id char(4) NULL, 11> price money NULL, 12> advance money NULL, 13> royalty int NULL, 14> ytd_sales int NULL, 15> notes varchar(200) NULL, 16> pubdate datetime NOT NULL 17> ) 18> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> 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 TABLE sales( 3> stor_id char(4) NOT NULL, 4> ord_num varchar(20) NOT NULL, 5> ord_date datetime NOT NULL, 6> qty smallint NOT NULL, 7> payterms varchar(12) NOT NULL, 8> title_id varchar(80) 9> ) 10> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> 8> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT title, price 3> FROM titles 4> WHERE title_id IN 5> (SELECT title_id 6> FROM sales 7> WHERE qty > 30) 8> GO title price


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

Secrets 20.0000 (1 rows affected) 1> 2> drop table sales; 3> drop table titles; 4> GO 1></source>


Subqueries can be nested.

   <source lang="sql">

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


----------
         1 Jason
         2 Alison
         4 Celia
         5 Robert
         7 David
         8 James

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


Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

   <source lang="sql">

5> CREATE TABLE titles( 6> title_id varchar(20), 7> title varchar(80) NOT NULL, 8> type char(12) NOT NULL, 9> pub_id char(4) NULL, 10> price money NULL, 11> advance money NULL, 12> royalty int NULL, 13> ytd_sales int NULL, 14> notes varchar(200) NULL, 15> pubdate datetime NOT NULL 16> ) 17> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> 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> 3> CREATE TABLE roysched( 4> title_id varchar(20), 5> lorange int NULL, 6> hirange int NULL, 7> royalty int NULL 8> ) 9> GO 1> 2> 3> insert roysched values("1", 0, 10000, 10) 4> insert roysched values("2", 10001, 20000, 12) 5> insert roysched values("3", 20001, 30000, 14) 6> insert roysched values("4", 30001, 40000, 16) 7> insert roysched values("5", 40001, 50000, 18) 8> 9> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> 4> SELECT titles.title_id, title, royalty 5> FROM titles 6> WHERE titles.royalty >= 7> (SELECT 1.25 * roysched.royalty FROM roysched) 8> 9> GO Msg 512, Level 16, State 1, Server J\SQLEXPRESS, Line 4 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 1> 2> drop table titles; 3> drop table roysched; 4> GO 1> 2></source>


subquery with >=

   <source lang="sql">

3> CREATE TABLE titles( 4> title_id varchar(20), 5> title varchar(80) NOT NULL, 6> type char(12) NOT NULL, 7> pub_id char(4) NULL, 8> price money NULL, 9> advance money NULL, 10> royalty int NULL, 11> ytd_sales int NULL, 12> notes varchar(200) NULL, 13> pubdate datetime NOT NULL 14> ) 15> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> 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> 3> CREATE TABLE roysched( 4> title_id varchar(20), 5> lorange int NULL, 6> hirange int NULL, 7> royalty int NULL 8> ) 9> GO 1> 2> 3> insert roysched values("1", 0, 10000, 10) 4> insert roysched values("2", 10001, 20000, 12) 5> insert roysched values("3", 20001, 30000, 14) 6> insert roysched values("4", 30001, 40000, 16) 7> insert roysched values("5", 40001, 50000, 18) 8> 9> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT titles.title_id, title, royalty 4> FROM titles 5> WHERE titles.royalty >= 6> (SELECT 1.25 * AVG(roysched.royalty) FROM roysched) 7> GO title_id title royalty


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

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


Using Subqueries to Check for the Existence of Matches

   <source lang="sql">

13> 14> CREATE TABLE employee( 15> id INTEGER NOT NULL PRIMARY KEY, 16> first_name VARCHAR(10), 17> last_name VARCHAR(10), 18> salary DECIMAL(10,2), 19> start_Date DATETIME, 20> region VARCHAR(10), 21> city VARCHAR(20) 22> ); 23> 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> 5> SELECT DISTINCT s.region 6> FROM employee s 7> WHERE EXISTS ( SELECT ID 8> FROM employee 9> WHERE salary BETWEEN 5000 AND 6000) 10> 11> 12> 13> 14> drop table employee; 15> GO region


East North South West (4 rows affected)</source>


Using Subqueries to Check for the Existence of Matches between two tables

   <source lang="sql">

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


----------
         1 Jason
         2 Alison
         3 James
         4 Celia
         5 Robert
         6 Linda
         7 David
         8 James
         9 Joan

(9 rows affected) 1> 2></source>