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>