SQL Server/T-SQL Tutorial/Subquery/Subquery
Содержание
- 1 <> ALL with subquery
- 2 A query that uses three subqueries
- 3 A subquery can be used with other comparison operators:
- 4 Building a Nested Subquery
- 5 Delete with subquery
- 6 Doing calculation with subquery
- 7 ID NOT IN (SELECT ID FROM Title)
- 8 Join and sub query
- 9 Subqueries and Comparison Operators: =
- 10 Subqueries and IN Operator
- 11 Subqueries are SELECT statements that are nested within another T-SQL statement.
- 12 Subqueries can be nested.
- 13 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
- 14 subquery with >=
- 15 Using Subqueries to Check for the Existence of Matches
- 16 Using Subqueries to Check for the Existence of Matches between two tables
<> ALL with subquery
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>
A query that uses three subqueries
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>
A subquery can be used with other comparison operators:
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>
Building a Nested Subquery
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>])
Delete with subquery
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>
Doing calculation with subquery
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
ID NOT IN (SELECT ID FROM Title)
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>
Join and sub query
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>
Subqueries and Comparison Operators: =
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>
Subqueries and IN Operator
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>
Subqueries are SELECT statements that are nested within another T-SQL statement.
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>
Subqueries can be nested.
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>
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
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>
subquery with >=
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>
Using Subqueries to Check for the Existence of Matches
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)
Using Subqueries to Check for the Existence of Matches between two tables
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>