SQL Server/T-SQL Tutorial/Subquery/Inline view
Содержание
- 1 A query that required a derived table.
- 2 A query that uses a correlated subquery in its SELECT clause to retrieve the most recent Billing for each Banker
- 3 Creating a Derived Table
- 4 Finding First Employee Using a Derived Table
- 5 In-Line Views (Derived Tables)
- 6 Join with (inline)Table Expressions
- 7 Queries in the FROM Clause
- 8 Select from nested select statements
- 9 SELECT <list> FROM (query that returns a regular result set) AS <alias name> JOIN <some other base or derived table>
- 10 Subqueries and Derived Tables
A query that required a derived table.
6> CREATE TABLE MyTempTable
7> (
8> ThisCol varchar(10)
9> )
10> GO
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> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> INSERT MyTempTable
4> SELECT Col1
5> FROM (SELECT DISTINCT Col1 = CONVERT(varchar(10), qty),
6> Col2 = qty
7> FROM sales) xyz
8> ORDER BY Col2
9> GO
(5 rows affected)
1>
2>
3>
4> SELECT * FROM MyTempTable
5> GO
ThisCol
----------
10
15
20
25
75
(5 rows affected)
1>
2> drop table sales
3> DROP TABLE MyTempTable
4> GO
1>
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>
4> create table Bankers(
5> BankerID Integer,
6> BankerName VARCHAR(20),
7> BankerContactLName VARCHAR(20),
8> BankerContactFName VARCHAR(20),
9> BankerCity VARCHAR(20),
10> BankerState VARCHAR(20),
11> BankerZipCode VARCHAR(20),
12> BankerPhone VARCHAR(20)
13> )
14> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3> SELECT DISTINCT BankerName,
4> (SELECT MAX(BillingDate) FROM Billings
5> WHERE Billings.BankerID = Bankers.BankerID) AS LatestInv
6> FROM Bankers
7> ORDER BY LatestInv DESC
8>
9>
10> drop table Billings;
11> drop table Bankers;
12> GO
BankerName LatestInv
-------------------- -----------------------
GHJ Inc. 2005-10-18 00:00:00.000
ABC Inc. 2005-01-22 00:00:00.000
DFG Inc. 2004-09-17 00:00:00.000
SAD Inc. 2003-08-16 00:00:00.000
HJI Inc. 2003-05-02 00:00:00.000
OIP Inc. 2002-07-15 00:00:00.000
YUI Inc. 2001-06-14 00:00:00.000
DEF Inc. 2001-02-21 00:00:00.000
RTY Inc. 2000-04-23 00:00:00.000
QWE Inc. 1999-03-12 00:00:00.000
(10 rows affected)
Creating a Derived Table
7> SELECT * FROM (SELECT "Fred" As FirstName, "Flintstone" As LastName)
8> AS CartoonCharacter
9>
FirstName LastName
--------- ----------
Fred Flintstone
Finding First Employee Using a Derived Table
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> managerid INTEGER
13> );
14> 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> SELECT
6> O.*
7> FROM
8> Employee AS O
9> JOIN
10> (
11> SELECT
12> MIN (ID) AS ID
13> FROM
14> Employee
15> GROUP BY
16> ID
17> ) AS X ON X.ID = O.ID
18>
19>
20>
21> drop table employee;
22> 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>
In-Line Views (Derived Tables)
4>
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 authors(
3> au_id varchar(11),
4> au_lname varchar(40) NOT NULL,
5> au_fname varchar(20) NOT NULL,
6> phone char(12) NOT NULL DEFAULT ("UNKNOWN"),
7> address varchar(40) NULL,
8> city varchar(20) NULL,
9> state char(2) NULL,
10> zip char(5) NULL,
11> contract bit NOT NULL
12> )
13> 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> CREATE TABLE titleauthor(
3> au_id varchar(20),
4> title_id varchar(20),
5> au_ord tinyint NULL,
6> royaltyper int NULL
7> )
8> 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> SELECT a.au_lname, tt.title
3> FROM authors a JOIN
4> (SELECT title, au_id FROM titleauthor ta
5> JOIN titles t ON t.title_id = ta.title_id) tt
6> ON a.au_id = tt.au_id
7> GO
au_lname title
---------------------------------------- --------------------------------------------------------------------------------
Joe The
Jack Emotional
Pink Prolonged
Blue With
Red Valley
Black Any?
White Fifty
(7 rows affected)
1>
2>
3> drop table titleauthor;
4> drop table authors;
5> drop table titles;
6> GO
1>
Join with (inline)Table Expressions
5> CREATE TABLE Orders (
6> OrderID int IDENTITY (1, 1) NOT NULL ,
7> CustomerID nchar (5) NULL ,
8> EmployeeID int NULL ,
9> OrderDate datetime NULL ,
10> RequiredDate datetime NULL ,
11> ShippedDate datetime NULL ,
12> ShipVia int NULL ,
13> Freight money NULL DEFAULT (0),
14> ShipName nvarchar (40) NULL ,
15> ShipAddress nvarchar (60) NULL ,
16> ShipCity nvarchar (15) NULL ,
17> ShipRegion nvarchar (15) NULL ,
18> ShipPostalCode nvarchar (10) NULL ,
19> ShipCountry nvarchar (15) NULL
20> )
21> GO
1>
2>
3>
4> SELECT O.OrderID, O.EmployeeID, O.CustomerID, O.OrderDate
5> FROM dbo.Orders AS O
6> JOIN (SELECT EmployeeID, MAX(OrderID) AS MaxOid
7> FROM dbo.Orders
8> GROUP BY EmployeeID) AS D
9> ON O.OrderID = D.MaxOid;
10> GO
OrderID EmployeeID CustomerID OrderDate
----------- ----------- ---------- -----------------------
(0 rows affected)
1>
2> drop table Orders;
3> GO
1>
Queries in the FROM Clause
3>
4>
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> SELECT first_name
4> FROM (SELECT *
5> FROM employee
6> WHERE id >= 2) AS empno_10000
7> GO
first_name
----------
Alison
James
Celia
Robert
Linda
David
James
Joan
(8 rows affected)
1>
2>
3> drop table employee;
4> GO
1>
2>
Select from nested select statements
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>
2>
3>
4> SELECT rank, title_id, ytd_sales, title
5> FROM (SELECT
6> T1.title_id,
7> ytd_sales,
8> T1.title,
9> (SELECT COUNT(DISTINCT T2.ytd_sales) FROM titles AS T2
10> WHERE T1.ytd_sales <= T2.ytd_sales) AS rank
11> FROM titles AS T1) AS X
12> WHERE (ytd_sales IS NOT NULL) AND (rank <= 10)
13> ORDER BY rank
14> GO
rank title_id ytd_sales title
----------- -------------------- ----------- --------------------------------------------------------------------------------
1 1 4095 Secrets
1 2 4095 The
1 7 4095 Any?
2 4 4072 Prolonged
3 5 3876 With
4 3 3336 Emotional
5 6 2032 Valley
6 8 1509 Fifty
1>
2>
3> drop table titles;
4> GO
SELECT <list> FROM (query that returns a regular result set) AS <alias name> JOIN <some other base or derived table>
7>
8>
9>
10> CREATE TABLE Customers (
11> CustomerID nchar (5) NOT NULL ,
12> CompanyName nvarchar (40) NOT NULL ,
13> ContactName nvarchar (30) NULL ,
14> ContactTitle nvarchar (30) NULL ,
15> Address nvarchar (60) NULL ,
16> City nvarchar (15) NULL ,
17> Region nvarchar (15) NULL ,
18> PostalCode nvarchar (10) NULL ,
19> Country nvarchar (15) NULL ,
20> Phone nvarchar (24) NULL ,
21> Fax nvarchar (24) NULL
22> )
23> GO
1>
2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111")
3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL)
4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444")
5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55")
6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777")
7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32")
8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99")
9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41")
10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745")
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE TABLE Orders (
2> OrderID int NOT NULL ,
3> CustomerID nchar (5) NULL ,
4> EmployeeID int NULL ,
5> OrderDate datetime NULL ,
6> RequiredDate datetime NULL ,
7> ShippedDate datetime NULL ,
8> ShipVia int NULL ,
9> Freight money NULL DEFAULT (0),
10> ShipName nvarchar (40) NULL ,
11> ShipAddress nvarchar (60) NULL ,
12> ShipCity nvarchar (15) NULL ,
13> ShipRegion nvarchar (15) NULL ,
14> ShipPostalCode nvarchar (10) NULL ,
15> ShipCountry nvarchar (15) NULL
16> )
17> GO
1>
2>
3> CREATE TABLE Products (
4> ProductID int NOT NULL ,
5> ProductName nvarchar (40) NOT NULL ,
6> SupplierID int NULL ,
7> CategoryID int NULL ,
8> QuantityPerUnit nvarchar (20) NULL ,
9> UnitPrice money NULL,
10> UnitsInStock smallint NULL,
11> UnitsOnOrder smallint NULL,
12> ReorderLevel smallint NULL,
13> Discontinued bit NOT NULL
14> )
15> GO
1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6)
2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7)
3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0)
4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0)
5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0)
6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0)
7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0)
8> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
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>
3> SELECT DISTINCT c.rupanyName
4> FROM Customers AS c
5> JOIN
6> (SELECT CustomerID
7> FROM Orders o
8> JOIN OrderDetails od
9> ON o.OrderID = od.OrderID
10> JOIN Products p
11> ON od.ProductID = p.ProductID
12> WHERE p.ProductName = "Chocolade") AS spen
13> ON c.CustomerID = spen.CustomerID
14> JOIN
15> (SELECT CustomerID
16> FROM Orders o
17> JOIN OrderDetails od
18> ON o.OrderID = od.OrderID
19> JOIN Products p
20> ON od.ProductID = p.ProductID
21> WHERE ProductName = "Vegie-spread") AS spap
22> ON c.CustomerID = spap.CustomerID
23> GO
CompanyName
----------------------------------------
(0 rows affected)
1>
2> drop table Products;
3> drop table OrderDetails;
4> drop table Orders;
5> drop table Customers;
6> GO
Subqueries and Derived Tables
Understanding Subqueries
Subqueries are the means by which you can do two or more SELECTs at the same time but return only one result set
Nested scalar subqueries are those subqueries that return a single row and column in their result set.
8>
9> CREATE TABLE employee(
10> id INTEGER NOT NULL PRIMARY KEY,
11> first_name VARCHAR(10),
12> last_name VARCHAR(10),
13> salary DECIMAL(10,2),
14> start_Date DATETIME,
15> region VARCHAR(10),
16> city VARCHAR(20)
17> );
18> 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> --Finding the Date of the Last Order
4> SELECT MAX (Start_Date) FROM Employee
5> --Finding the Most-Recent Employee
6>
7> SELECT * FROM Employee WHERE Start_Date =(SELECT MAX (Start_Date) FROM Employee)
8>
9>
10>
11> drop table employee;
12> drop table title;
13> GO
-----------------------
2008-03-18 00:00:00.000
(1 rows affected)
id first_name last_name salary start_Date region city
----------- ---------- ---------- ------------ ----------------------- ---------- --------------------
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
(1 rows affected)
1>