Microsoft SQL Server - Lesson 34: Subqueries

	Subqueries

Subqueries Fundamentals

Introduction

A subquery, also called an inner query, is a query created inside of another query. A subquery is created in an existing SELECT, INSERT, UPDATE, or DELETE statement. The parent query that contains the inner statement is also called an outer query.

Practical Learning: Introducing Subqueries

Start the computer and log in
Launch Microsoft SQL Server and click Connect
Open the codes/KoloBank2.sql file (codes/KoloBank2.txt)
To execute it, on the main menu, click Query -> Execute
Close the KoloBank3 window
In the Object Explorer, expand Databases and expand KoloBank3
Right-click KoloBank3 and click Start PowerShell
Type SQLCMD and press Enter
Type USE KoloBank3; and press Enter
Type GO and press Enter
Type SELECT Clients.AccountNumber, Clients.CustomerName and press Enter
Type FROM Management.Customers AS Clients; and press Enter
Type GO and press Enter
Return to Microsoft SQL Server Management Studio
In the Object Explorer, right-click KoloBank3 and click New Query
On the SQL Editor toolbar, click the Results to Text button

A Simple Subquery

Transact-SQL support various techniques of creating subqueries. A subquery can be created as a SELECT expression inside a regular SELECT statement. Such a subquery is also called an inner select. The parent query is also called an outer select.

Before creating a subquery, start a normal SELECT statement that includes a placeholder for the column(s) of a table (or tables) or view(s). The subquery can use one of the placeholders but it (the subquery) must be included in parentheses.

When creating a subquery as a member of a SELECT statement, the statement must have a condition, which is usually a WHERE condition. Therefore, the primary formula to create a subquery is:

SELECT Statement (SELECT WhatColumn FROM WhatObject WHERE Condition) FROM WhatObject

You start with a normal SELECT statement that either uses * or contains a list of columns. Here is an example:

SELECT ItemName, Size, UnitPrice,
(SELECT WhatColumn FROM What WHERE Condition)
FROM StoreItems;

The condition must specify a relationship by which the field(s) of the inside SELECT expression(s) relate(s) to the parent or outer table or view. The condition can use one of the logical operators. Here is an example:

SELECT CheckAmount,
      (SELECT CustomerName FROM Management.Customers
       WHERE  CheckCashing.CustomerID = Customers.CustomerID)
FROM CheckCashing;
GO

Among the rules you must observe when creating a subquery:

The nested SELECT statement must represent only one column. It cannot be in the form SELECT * FROM WhatObject, or SELECT Column1, Column2, Column_n FROM WhatObject. If you try using more than one column, you would receive an error as: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
The column involved in the subquery cannot be of type varchar(max), nvarchar(max), or varbinary(max)
The nested SELECT statement must produce only one value. This implies that the WHERE condition must state how the values would be restricted to produce only one value

As mentioned already, a subquery produces a result; but, because that statement is included in parentheses, its scope is limited, which means the (name of the) column of the subquery cannot be referenced outside.

If you simply execute the SQL statement, the header of the subquery would display nothing. For example, the above statement would produce:

Subquery

Notice that the column header of the subquery is empty. As an alternative, you should add an alias for the subquery. This can be done as follows:

SELECT CheckAmount,
      (SELECT CustomerName FROM Management.Customers
       WHERE  CheckCashing.CustomerID = Customers.CustomerID) AS [Paid To]
FROM CheckCashing;
GO

Subquery

On the other hand, since you are likely to use the same name of a column in the subquery as well as in the outside expression, you should create an alias for each table and use it to qualify the name of each column. Here are examples:

SELECT Cashed.CheckAmount,
      (SELECT Clients.CustomerName FROM Management.Customers AS Clients
       WHERE  Clients.CustomerID = Cashed.CustomerID) AS [Paid To]
FROM CheckCashing AS Cashed;
GO

In fact, in some cases, you will use the same table both in the parent statement and in the subquery. In this case, you should (must) use a different alias for each part, one for the table in the parent statement and the other for the same table used in the subquery.

Practical Learning: Creating a Simple Subquery

To get introduced to subqueries, type the following code:

SELECT Clients.AccountNumber AS [Account #],
       Clients.CustomerName AS Customer,
      (SELECT ActType.AccountType FROM Management.AccountTypes AS ActType
       WHERE  Clients.AccountTypeID = ActType.AccountTypeID) AS [Type]
FROM Management.Customers AS Clients;
GO

To execute, on the SQL Editor toolbar, click the Execute button

Introducing Subqueries

A Subquery in a SELECT Condition

We have already seen various ways of using a WHERE condition to restrict the values produced by a SELECT statement. The statements we have used so far supposed that we knew the value on which to apply the condition. If not, you can create a subquery in such a WHERE condition. The main rule you must follow is that the subquery must produce the type of value of the column of the external condition. Of course, the subquery must return only one value.

Practical Learning: Creating a Simple Subquery

Return to the PowerShell window

To see the deposits that each customer made per date, type the following code and press Enter after each line:

>1 SELECT Depots.DepositDate AS [Deposit Date],
>2 	  Depots.DepositAmount AS Amount
>3 FROM   Transactions.Deposits AS Depots
>4 WHERE  Depots.CustomerID = 
>5	 (SELECT Clients.CustomerID
>6	  FROM Management.Customers AS Clients
>7 	  WHERE Clients.CustomerID = Depots.CustomerID);
>8 GO

Return to the Query window of SQL Server Management Studio

Subqueries and Functions

A function is a prime object that can be used to combine values or to perform calculations. We already know that Transact-SQL provides a vast collection of built-in aggregate functions that act on all records of a table or a view. You can use one of those (aggregate) functions to get a summary of values such as the number of records of a child table associated with a parent table. In the same way, you can use the other appropriate built-in functions or you can create your own.

Practical Learning: Using a Function in a Subquery

Click inside the top section of the Query window and press Ctrl + A

To use a function in a subquery, type the code as follows:

SELECT Clients.AccountNumber, Clients.CustomerName,
      (SELECT SUM(Depots.DepositAmount)
       FROM Transactions.Deposits AS Depots
       WHERE Clients.CustomerID = Depots.CustomerID) AS [Client Total Deposits]
FROM Management.Customers AS Clients;
GO

To execute, right-click in the Query window and click Execute

Notice that some customers don't show a deposit. To hide those records, edit the code as follows:

SELECT Clients.AccountNumber, Clients.CustomerName,
      (SELECT SUM(Depots.DepositAmount)
       FROM Transactions.Deposits AS Depots
       WHERE  Clients.CustomerID = Depots.CustomerID) AS [Client Total Deposits]
FROM Management.Customers AS Clients
WHERE (SELECT SUM(Depots.DepositAmount) FROM Transactions.Deposits AS Depots
       WHERE  Clients.CustomerID = Depots.CustomerID) IS NOT NULL;
GO

To execute, press F5
Click in the top section of the Query window and press Ctrl + A

Combining Subqueries

Remember that a subquery uses a placeholder of the main SELECT statement. Based on this, you can include as many subqueries as you want inside an outer SELECT statement as long as each uses its own placeholder and produces one value.

Practical Learning: Combining Subqueries

To create more than one subquery, type the following:

SELECT Clients.AccountNumber AS [Account #],
       Clients.CustomerName AS Customer,
      (SELECT SUM(Depots.DepositAmount) FROM Transactions.Deposits AS Depots
       WHERE  Clients.CustomerID = Depots.CustomerID) AS Deposits,
      (SELECT SUM(Paid.WithdrawalAmount) FROM Transactions.Withdrawals AS Paid
       WHERE  Clients.CustomerID = Paid.CustomerID) AS Withdrawn
FROM Management.Customers AS Clients;
GO

To execute, press F5
Return to the PowerShell window

Nesting Subqueries

As stated already, a subquery can be a SQL statement created inside an outer SELECT statement. In the same way, a subquery can be created inside another subquery. In fact, one subquery can be created in a subquery that itself is inside another subquery. The primary formula to follow is:

SELECT WhatColumn FROM WhatObject
    WHERE Condition Operator
	(SELECT WhatColumn
	     FROM WhatObject
	     WHERE Condition Operator
		(SELECT WhatColumn
	     	 FROM WhatObject
	     	 WHERE Condition Operator
		       (SELECT WhatColumn
	     	 	FROM WhatObject
	     	 	WHERE Condition)))

Transact-SQL allows up to 32 levels of subqueries. The primary rule to follow is that, since a subquery must produce only one value, it you use a multi-level expression, each nested subquery must produce one value that its parent subquery can use.

Practical Learning: Nesting a Subquery

To view the total deposits of a customer whose primary key has the same value as a certain employee's primary key (2), type the statement as follows and press Enter after each line:

>1 SELECT Depots.DepositAmount
>2 FROM Transactions.Deposits AS Depots
>3 WHERE Depots.CustomerID = 
>4	 (SELECT Clients.CustomerID
>5	  FROM Management.Customers AS Clients
>6	  WHERE Clients.CustomerID = 
>7		(SELECT EmployeeID
>8	         FROM Personnel.Employees
>9	         WHERE EmployeeID = 2));
>10 GO

Return to the Query window of the SQL Server Management Studio

Subqueries and Expressions

Remember that a subquery is meant to produce a value. Once you have that value, you can involve it in a calculated or string-based operation.

Practical Learning: Using an Expression in a Subquery

Click inside the top section of the Query window and press Ctrl + A

To get a list of managers whose salary is greater than or equal to the base salary of managers + the standard deviation of the salaries of all current managers, type the following (sorry for the long code; the only important part is the bottom (red) section, in fact, you can skip the (blue) parts previous to it):

SELECT Category, StartingSalary AS [Starting Salary of Managers]
FROM   Personnel.StartingSalaries
WHERE  Category = N'Manager';
GO
SELECT Managers.EmployeeNumber AS [Empl #],
       Managers.LastName + N', ' + Managers.FirstName AS [Employee Name],
       Managers.Title,
       Managers.HourlySalary AS [Salary/Hr]
FROM   Personnel.Employees AS Managers
WHERE (Title LIKE N'%Manager%');
GO
SELECT STDEV(HourlySalary) AS [Standard Deviation of Managers Salaries]
       FROM Personnel.Employees
       WHERE HourlySalary IS NOT NULL;
GO

SELECT N'Managers whose salary is greater than or equal ' 
       N'to managers base salary + managers standard deviation';
GO

SELECT Managers.EmployeeNumber AS [Empl #],
       Managers.LastName + N', ' + Managers.FirstName AS [Manager Name],
       Managers.Title,
       Managers.HourlySalary AS [Salary/Hr]
FROM   Personnel.Employees AS Managers
WHERE  Managers.HourlySalary >=
     ((SELECT StartingSalary
       FROM   Personnel.StartingSalaries
       WHERE Category LIKE N'Manager') +
      (SELECT STDEV(HourlySalary)
       FROM Personnel.Employees
       WHERE Title LIKE N'%Manager%'));
GO

Press F5 to execute:
Click the top section of the Query window and press Ctrl + A

To create an expression, type following:

SELECT Clients.AccountNumber AS [Account #],
       Clients.CustomerName AS Customer,
      (SELECT SUM(Depots.DepositAmount) FROM Transactions.Deposits AS Depots
       WHERE  Clients.CustomerID = Depots.CustomerID) AS Deposits,
      (SELECT SUM(Paid.WithdrawalAmount) FROM Transactions.Withdrawals AS Paid
       WHERE  Clients.CustomerID = Paid.CustomerID) AS Withdrawn,
      (SELECT SUM(Depots.DepositAmount) FROM Transactions.Deposits AS Depots
       WHERE  Clients.CustomerID = Depots.CustomerID) -
      (SELECT SUM(Paid.WithdrawalAmount) FROM Transactions.Withdrawals AS Paid
       WHERE  Clients.CustomerID = Paid.CustomerID) As Balance
FROM Management.Customers AS Clients;
GO

To execute, press F5

Account #            Customer                                           Deposits              Withdrawn             Balance
-------------------- -------------------------------------------------- --------------------- --------------------- ---------------------
28-3782-84           James Carlton Brokeridge                           3170.58               380.00                2790.58
92-3782-43           Chrissy Arlene McMahon                             740.00                100.00                640.00
38-4227-52           James Norris                                       1375.00               100.00                1275.00
68-6434-56           Eldridge Powers                                    750.00                260.00                490.00
83-4654-77           Hobert Umbro Spampinato                            4505.00               200.00                4305.00
47-4783-25           Gloria Aline Wright                                1275.35               100.00                1175.35
82-3763-24           Liliana Wellie Ortez                               250.00                NULL                  NULL
72-3474-24           Ornella Maiwand                                    2250.00               300.00                1950.00
34-5458-49           Leonel James Harbor                                3200.00               NULL                  NULL
29-4586-42           Albert Sonny Odonnell                              NULL                  NULL                  NULL
68-3465-86           Howie Horace Fallace                               NULL                  NULL                  NULL
40-4658-63           Mellinda Bridges                                   NULL                  NULL                  NULL
56-8468-59           Barry Parrang                                      NULL                  NULL                  NULL
94-7785-34           Ismail Zorbah                                      NULL                  NULL                  NULL
37-5764-86           Xavier Lenny Hereford                              NULL                  NULL                  NULL
34-9754-71           Marthe Helene Bradley                              NULL                  NULL                  NULL
72-9375-48           Jabouni Cabasco Toussey                            NULL                  NULL                  NULL
37-5490-64           Cherrine Leonie Horvath                            NULL                  NULL                  NULL
20-3454-96           Ophellie Wyman                                     NULL                  NULL                  NULL
76-5475-43           Joseph Patrick Honey                               NULL                  NULL                  NULL
27-3457-49           Robert Daniel Luner                                NULL                  NULL                  NULL

(21 row(s) affected)

Return to the PowerShell window

Other Operations on Subqueries

Logical Operations in Subqueries

So far, we have seen that you can use one or more Boolean operators (=, <, <=, >, >=, and <>) in a WHERE condition of a subquery. Besides these, the SQL also allows you to use regular logical operators (LIKE, IN, AND, OR, and BETWEEN) to specify how to restrict the values produced by the subquery. In the IN statement, you must make sure the database interpreter is considering one or more values of its records.

Remember that, to negate an operation, you can precede its expression with NOT.

Practical Learning: Using IN in a Subquery

To use IN to see the list of deposits that were made in the Silver Spring branch if some withdrawals were made in the same branch (the primary key of a the deposit must be the same as the primary key of the withdrawal), type the following code and press Enter after each line:
```
>1 SELECT Depots.DepositDate AS [Date],
>2 	  Depots.DepositAmount AS [Deposit Amount]
>3 FROM Transactions.Deposits AS Depots
>4 WHERE Depots.LocationID IN 
>5	 (SELECT Withdrawn.LocationID
>6	  FROM Transactions.Withdrawals AS Withdrawn
>7	  WHERE Withdrawn.LocationID = 3);
>8 GO
```
To use more than one value for IN (this will show all deposits that were made in the MD branches when the primary key of the deposit is the same as the primary of a withdrawal)), edit the code as follows and press Enter after each line (to reduce yping and recall previously typed lines of code, you probably know already how to use the up and down keys (of the keywoard), and also pressing Enter to accept a line after you have found it):
```
>1 SELECT Depots.DepositDate AS [Date],
>2	  Depots.DepositAmount AS [Deposit Amount]
>3 FROM Transactions.Deposits AS Depots
>4 WHERE Depots.LocationID IN 
>5	 (SELECT Withdrawn.LocationID
>6	  FROM Transactions.Withdrawals AS Withdrawn
>7	  WHERE (Withdrawn.LocationID = 3) OR (Withdrawn.LocationID = 4));
>8 GO
```

To negate the IN and see all deposits not made in the MD branches, edit the code as follows (again, use the arrow keys on the keyboard to locate the lines and press Enter when you find the line of code) (after locating the necessary line of code, simply insert the NOT keyword and continue):

>1 SELECT Depots.DepositDate AS [Date],
>2	  Depots.DepositAmount AS [Deposit Amount]
>3 FROM Transactions.Deposits AS Depots
>4 WHERE Depots.LocationID NOT IN 
>5	 (SELECT Withdrawn.LocationID
>6	  FROM Transactions.Withdrawals AS Withdrawn
>7	  WHERE (Withdrawn.LocationID = 3) OR (Withdrawn.LocationID = 4));
>8 GO

Data Entry and Subqueries

When performing data entry, you may not want the user to provide a value for a certain field, in which case you would provide that value yourself. On the other hand, to assist a user for data entry, you can provide a value for the field. Of course, we already know how to assign a default value to a column, except that, to do this, we must know the value we want to assign. In some cases, either we don't know exactly what value to assign or the value is not yet available (we may have to wait for the boss to decide or we may have to wait for another person to create the table that holds the value(s) we want). The solution is to use a subquery that would select a value from a table or view. In this case, you can use a WHERE condition that would specify how and where to get the necessary value.

Practical Learning: Performing Data Entry

To see the list of starting salaries, type SELECT * FROM Personnel.StartingSalaries; and press Enter
Type GO and press Enter

Notice the starting salaries per category.
To create a subquery, type the following code (this code creates a new employee with an employee number and a name; to assign a salary to the new employee, the code gets the base salary specified in the StartingSalaries table) and press Enter after each line:

>1 INSERT INTO Personnel.Employees(EmployeeNumber, HourlySalary,
>2 				   LastName, FirstName)
>3 VALUES(N'295-420',
>4       (SELECT StartingSalary
>5        FROM   Personnel.StartingSalaries
>6        WHERE StartingSalaries.Category = N'Base'),
>7        N'Margareth',
>8        N'Schubert');
>9 GO

Return to the Query window in the SQL Server Management Studio
Click inside the Query window and press Ctrl + A

To see a list of all employees whose salary is less than or equal to the base salary of the company, type the code as follows:

SELECT * FROM Personnel.Employees
WHERE HourlySalary <= (SELECT StartingSalary
 		       FROM   Personnel.StartingSalaries
		       WHERE Category LIKE N'Base');
GO

Press F5 to execute

EmployeeID  EmployeeNumber FirstName   LastName       Title       CanCreateNewAccount EmailAddress   WorkPhone            Extension EmployeeAddress              EmployeeCity      EmployeeState    EmployeeZIPCode Country    HomePhone    HourlySalary    Notes
----------- -------------- ----------- -------------- ----------- ------------------- -------------- -------------------- --------- ---------------------------- ----------------- ---------------- --------------- ---------- ------------ --------------- ----------
3           195-028        Calvin      Khone          Cashier     NULL                NULL           (301) 839-4253       NULL      516 Linden Street Apt D2     Silver Spring     NULL             NULL            USA        NULL         6.85            NULL
6           274-284        Herbert     Jerremies      Intern      1                   NULL           (410) 653-1309       106       8254 12th St. N.E.           Washington        DC               NULL            USA        NULL         4.15            NULL
12          464-808        Mark        Georges        Intern      NULL                NULL           (202) 719-7335       NULL      1101 Elon Rd                 Takoma Park       NULL             NULL            USA        NULL         7.12            NULL
13          119-814        Samuel      McCain         Cashier     NULL                NULL           (410) 653-1309       142       9337 Cachet St               Baltimore         MD               NULL            USA        NULL         8.25            NULL
14          924-993        Kirsten     Roberts        Intern      NULL                NULL           (410) 653-1309       164       1336 Philadelphia St.        Baltimore         MD               NULL            USA        NULL         8.05            NULL

(5 row(s) affected)

Updating Records Using a Subquery

When updating a record, the main challenge is usually to locate that record. This can be done using a condition that specifies where and how to identify the record. As mention for data entry, when updating a record, it would be easy to know the value we want to assign to a column. In some cases, we may not have that value yet or we may not know it, maybe because it is located in another table, which means the value may have been changed (updated by the supervisor, another developer, or by some other means) and is supposed to change. An alternative to solving this type of problem is to get the value using a subquery. In this case, you can use a WHERE condition that would specify how and where to get the necessary value.

Practical Learning: Performing Data Entry

Click inside the Query window and press Ctrl + A to select all

To see a list of all employees, type the following code:

SELECT EmployeeNumber AS [Empl #],
       LastName + N', ' + FirstName AS [Employee Name],
       Title,
       HourlySalary AS [Salary/Hr]
FROM Personnel.Employees
GO

To execute, press F5

Empl #     Employee Name              Title                          Salary/Hr
---------- -------------------------- ------------------------------ ---------------
220-682    Nguyen, Matt               Head Cashier                   22.82
462-088    Hannagan, Catherine        Customer Account Manager       28.55
195-028    Khone, Calvin              Cashier                        6.85
271-799    Vaughs, Leonie             Cashier                        NULL
195-804    Stafford, Sylvie           Regional Manager               36.22
274-284    Jerremies, Herbert         Intern                         4.15
662-286    Zbrnitz, Lienev            Cashier                        15.75
487-525    Guerrero, Paulin           Intern                         16.85
395-138    Waste, Plant               Head Teller                    16.75
822-730    Chang, Steven              Accountant                     24.15
930-717    Kombo, Abedi               Shift Programmer               10.02
464-808    Georges, Mark              Intern                         7.12
119-814    McCain, Samuel             Cashier                        8.25
924-993    Roberts, Kirsten           Intern                         8.05
220-826    Fake-Eye, William          Manager of Public Relations    17.32
900-026    Lhoads, Roger              Cashier                        10.24
270-707    Zeran, Ada                 Administrative Assistant       15.48
272-883    Drudge, Milicien           Cashier                        15.34
559-528    Rhoades, Jeffrey           Cashier                        NULL
385-225    Kast, Aaron                Accounts Manager               20.34
717-028    Lourde, Antoine            Regional Assistant Manager     15.62
405-850    Kirkland, Lorraine         Assistant Manager              22.86
295-420    Margareth, Schubert        NULL                           NULL

(23 row(s) affected)

Notice that some employees have a salary as NULL.
To see the list of interns and their salaries, change the code as follows:

SELECT * FROM Personnel.StartingSalaries;
GO
SELECT EmployeeNumber AS [Empl #],
       LastName + N', ' + FirstName AS [Intern Name],
       HourlySalary AS [Salary/Hr]
FROM Personnel.Employees
WHERE Title LIKE N'Intern';
GO

Press F5 to execute

Category                       StartingSalary
------------------------------ ---------------------
Base                           10.00
Intern                         12.35
Regular                        14.50
Manager                        20.00

(4 row(s) affected)

Empl #     Intern Name                      Salary/Hr
---------- -------------------------------- -------------
274-284    Jerremies, Herbert               4.15
487-525    Guerrero, Paulin                 16.85
464-808    Georges, Mark                    7.12
924-993    Roberts, Kirsten                 8.05

(4 row(s) affected)

To see the list of employees who make less than the company's minimum wage, change the code as follows:

SELECT * FROM Personnel.StartingSalaries;
GO
SELECT EmployeeNumber AS [Empl #],
       LastName + N', ' + FirstName AS [Employee Name],
       Title,
       HourlySalary AS [Salary/Hr]
FROM Personnel.Employees
WHERE HourlySalary < (SELECT StartingSalary
                      FROM   Personnel.StartingSalaries
                      WHERE Category LIKE N'Base');
GO

Press F5 to execute

Category                       StartingSalary
------------------------------ ---------------------
Base                           10.00
Intern                         12.35
Regular                        14.50
Manager                        20.00

(4 row(s) affected)

Empl #     Employee Name                       Title           Salary/Hr
---------- ----------------------------------- --------------- ---------------------
195-028    Khone, Calvin                       Cashier         6.85
274-284    Jerremies, Herbert                  Intern          4.15
464-808    Georges, Mark                       Intern          7.12
119-814    McCain, Samuel                      Cashier         8.25
924-993    Roberts, Kirsten                    Intern          8.05

(5 row(s) affected)

In the Object Explorer, expand KoloBank3 and expand Tables
Under Tables, right-click Personnel.StartingSalaries and click Edit Top 200 Rows
Change the StartingSalary of Base to 12.50
Change the StartingSalary of Intern to 14.05
Close the table
Click the top section of the Query window and press Ctrl + A

To create a subquery that has a condition to specify the default salary of employees who did not receive a salary when their initial records were created, type the followig code:

SELECT 	EmployeeNumber AS [Empl #],
	LastName + N', ' + FirstName AS [Employee Name],
	Title,
	HourlySalary AS [Salary/Hr]
FROM Personnel.Employees
GO

UPDATE Personnel.Employees
SET HourlySalary = (SELECT StartingSalary
		    FROM   Personnel.StartingSalaries
		    WHERE Category = N'Base')
WHERE HourlySalary IS NULL;
GO

SELECT 	EmployeeNumber AS [Empl #],
	LastName + N', ' + FirstName AS [Employee Name],
	Title,
	HourlySalary AS [Salary/Hr]
FROM Personnel.Employees
GO

To execute, press F5

Empl #     Employee Name            Title                           Salary/Hr
---------- ------------------------ ------------------------------- -------------
220-682    Nguyen, Matt             Head Cashier                    22.82
462-088    Hannagan, Catherine	    Customer Account Manager        28.55
195-028    Khone, Calvin            Cashier                         6.85
271-799    Vaughs, Leonie           Cashier                         NULL
195-804    Stafford, Sylvie         Regional Manager                36.22
274-284    Jerremies, Herbert       Intern                          4.15
662-286    Zbrnitz, Lienev          Cashier                         15.75
487-525    Guerrero, Paulin         Intern                          16.85
395-138    Waste, Plant             Head Teller                     16.75
822-730    Chang, Steven            Accountant                      24.15
930-717    Kombo, Abedi             Shift Programmer                10.02
464-808    Georges, Mark            Intern                          7.12
119-814    McCain, Samuel           Cashier                         8.25
924-993    Roberts, Kirsten         Intern                          8.05
220-826    Fake-Eye, William        Manager of Public Relations	    17.32
900-026    Lhoads, Roger            Cashier                         10.24
270-707    Zeran, Ada               Administrative Assistant        15.48
272-883    Drudge, Milicien         Cashier                         15.34
559-528    Rhoades, Jeffrey         Cashier                         NULL
385-225    Kast, Aaron              Accounts Manager                20.34
717-028    Lourde, Antoine          Regional Assistant Manager      15.62
405-850    Kirkland, Lorraine       Assistant Manager               22.86
295-420    Margareth, Schubert      NULL			    10.00

(23 row(s) affected)


(2 row(s) affected)
Empl #     Employee Name            Title                            Salary/Hr
---------- ------------------------ -------------------------------- ------------
220-682    Nguyen, Matt             Head Cashier                     22.82
462-088    Hannagan, Catherine      Customer Account Manager         28.55
195-028    Khone, Calvin            Cashier                          6.85
271-799    Vaughs, Leonie           Cashier                          12.50
195-804    Stafford, Sylvie         Regional Manager                 36.22
274-284    Jerremies, Herbert       Intern                           4.15
662-286    Zbrnitz, Lienev          Cashier                          15.75
487-525    Guerrero, Paulin         Intern                           16.85
395-138    Waste, Plant             Head Teller                      16.75
822-730    Chang, Steven            Accountant                       24.15
930-717    Kombo, Abedi             Shift Programmer                 10.02
464-808    Georges, Mark            Intern                           7.12
119-814    McCain, Samuel           Cashier                          8.25
924-993    Roberts, Kirsten         Intern                           8.05
220-826    Fake-Eye, William        Manager of Public Relations      17.32
900-026    Lhoads, Roger            Cashier                          10.24
270-707    Zeran, Ada               Administrative Assistant         15.48
272-883    Drudge, Milicien         Cashier                          15.34
559-528    Rhoades, Jeffrey         Cashier                          12.50
385-225    Kast, Aaron              Accounts Manager                 20.34
717-028    Lourde, Antoine          Regional Assistant Manager       15.62
405-850    Kirkland, Lorraine       Assistant Manager                22.86
295-420    Margareth, Schubert      NULL                             10.00

(23 row(s) affected)

Notice that the employees whose salaries were set as NULL have received a default salary from the StartingSalaries table

Click the top section of the Query window and press Ctrl + A

To create a subquery that specifies a condition to update the minimum salary of interns, type code as follows:

-- First show the list of interns
SELECT EmployeeNumber AS [Empl #],
       LastName + N', ' + FirstName AS [Intern Name],
       HourlySalary AS [Salary/Hr]
FROM Personnel.Employees
WHERE Title LIKE N'Intern';
GO

-- Check and, if necessary, update the salary of the intern
/*
This code is meant to update the salary of each intern,
only if the hourly salary of that intern is less than the salary
set for the Intern category in the StartingSalaries table
*/
UPDATE Personnel.Employees
SET HourlySalary = (SELECT StartingSalary
		    FROM   Personnel.StartingSalaries
		    WHERE Category = N'Intern')
WHERE (Title LIKE N'Intern') AND (HourlySalary < (SELECT StartingSalary
						  FROM   Personnel.StartingSalaries
						  WHERE Category = N'Intern'));
GO
-- Show the list of interns again
SELECT EmployeeNumber AS [Empl #],
       LastName + N', ' + FirstName AS [Intern Name],
       HourlySalary AS [Salary/Hr]
FROM Personnel.Employees
WHERE Title LIKE N'Intern';
GO

To execute, press F5

Deleting Records Using a Subquery

Just as done for updating records, you can delete records using a subquery. The formulas are primarily the same as for updating a record and the rules for the subquery are the same as seen for updating.

Subqueries and Joins

In many cases, a subquery plays the same role as a join. That is, a subquery is primarily an alternative to a join. A subquery can be used to get values of fields from two or more tables or views. Of course, the tables or views must have a relationship, which would be based on a primary key-foreign key scenario. To create such a subquery, use its WHERE condition to specify how the tables or views are related or joined.

The main difference between a join and a subquery is in the way the join is structured. Normally, a regular join doesn't require a condition. For example, a left join simply says "Find the records from Table A and Table B that are related with the ON statement." This implies that a left join would include records with NULL (unspecified/empty) values. Because a subquery has a WHERE condition, its result would include only records that follow the stated condition. On the other hand, if you create an inner join, you would get the same results as a subquery.

Practical Learning: Creating a Subquery as an Alternative to a Join

Click inside the Query window and press Ctrl + A to select all

To create a subquery, type the following code:

SELECT Clients.AccountNumber AS [Account #],
      (SELECT Category.AccountType
       FROM Management.AccountTypes AS Category
       WHERE Clients.AccountTypeID = Category.AccountTypeID) AS [Type],
       Clients.CustomerName AS Customer
FROM Management.Customers AS Clients;
GO

To execute, press F5

Account #            Type                   Customer
-------------------- ---------------------- ------------------------------
28-3782-84           Checking               James Carlton Brokeridge
92-3782-43           Checking               Chrissy Arlene McMahon
38-4227-52           NULL                   James Norris
68-6434-56           NULL                   Eldridge Powers
83-4654-77           Saving                 Hobert Umbro Spampinato
47-4783-25           NULL                   Gloria Aline Wright
82-3763-24           Checking               Liliana Wellie Ortez
72-3474-24           CD                     Ornella Maiwand
34-5458-49           NULL                   Leonel James Harbor
29-4586-42           Checking               Albert Sonny Odonnell
68-3465-86           Checking               Howie Horace Fallace
40-4658-63           Saving                 Mellinda Bridges
56-8468-59           CD                     Barry Parrang
94-7785-34           NULL                   Ismail Zorbah
37-5764-86           Checking               Xavier Lenny Hereford
34-9754-71           CD                     Marthe Helene Bradley
72-9375-48           Checking               Jabouni Cabasco Toussey
37-5490-64           Saving                 Cherrine Leonie Horvath
20-3454-96           Checking               Ophellie Wyman
76-5475-43           Checking               Joseph Patrick Honey
27-3457-49           Checking               Robert Daniel Luner

(21 row(s) affected)

Notice that this produces 21 records.
Click inside the Query window and press Ctrl + A

To create an inner join relationship, type the following code:

SELECT Clients.AccountNumber As [Account #],
       Category.AccountType As [Type],
       Clients.CustomerName  As Customer
FROM   Management.Customers AS Clients INNER JOIN
       Management.AccountTypes AS Category 
       ON Clients.AccountTypeID = Category.AccountTypeID;
GO

To execute, press F5

Account #            Type                Customer
-------------------- ------------------- --------------------------------------------------
28-3782-84           Checking            James Carlton Brokeridge
92-3782-43           Checking            Chrissy Arlene McMahon
83-4654-77           Saving              Hobert Umbro Spampinato
82-3763-24           Checking            Liliana Wellie Ortez
72-3474-24           CD                  Ornella Maiwand
29-4586-42           Checking            Albert Sonny Odonnell
68-3465-86           Checking            Howie Horace Fallace
40-4658-63           Saving              Mellinda Bridges
56-8468-59           CD                  Barry Parrang
37-5764-86           Checking            Xavier Lenny Hereford
34-9754-71           CD                  Marthe Helene Bradley
72-9375-48           Checking            Jabouni Cabasco Toussey
37-5490-64           Saving              Cherrine Leonie Horvath
20-3454-96           Checking            Ophellie Wyman
76-5475-43           Checking            Joseph Patrick Honey
27-3457-49           Checking            Robert Daniel Luner

(16 row(s) affected)

Notice that this produces 16 records.
Click inside the Query window and press Ctrl + A

To create a left join, change the statement as follows:

SELECT Clients.AccountNumber AS [Account #],
       Category.AccountType AS [Type],
       Clients.CustomerName  AS Customer
FROM   Management.Customers AS Clients LEFT JOIN
       Management.AccountTypes AS Category 
       ON Clients.AccountTypeID = Category.AccountTypeID;
GO

To execute, press F5

Account #            Type                 Customer
-------------------- -------------------- ----------------------------
28-3782-84           Checking             James Carlton Brokeridge
92-3782-43           Checking             Chrissy Arlene McMahon
38-4227-52           NULL                 James Norris
68-6434-56           NULL                 Eldridge Powers
83-4654-77           Saving               Hobert Umbro Spampinato
47-4783-25           NULL                 Gloria Aline Wright
82-3763-24           Checking             Liliana Wellie Ortez
72-3474-24           CD                   Ornella Maiwand
34-5458-49           NULL                 Leonel James Harbor
29-4586-42           Checking             Albert Sonny Odonnell
68-3465-86           Checking             Howie Horace Fallace
40-4658-63           Saving               Mellinda Bridges
56-8468-59           CD                   Barry Parrang
94-7785-34           NULL                 Ismail Zorbah
37-5764-86           Checking             Xavier Lenny Hereford
34-9754-71           CD                   Marthe Helene Bradley
72-9375-48           Checking             Jabouni Cabasco Toussey
37-5490-64           Saving               Cherrine Leonie Horvath
20-3454-96           Checking             Ophellie Wyman
76-5475-43           Checking             Joseph Patrick Honey
27-3457-49           Checking             Robert Daniel Luner

(21 row(s) affected)

Notice that we get the same records as the subquery.
To create a right join, change the LEFT keyword to RIGHT

SELECT Clients.AccountNumber AS [Account #],
       Category.AccountType AS [Type],
       Clients.CustomerName  AS Customer
FROM   Management.Customers AS Clients RIGHT JOIN
       Management.AccountTypes AS Category 
       ON Clients.AccountTypeID = Category.AccountTypeID;
GO

To execute, press F5

Account #            Type                  Customer
-------------------- --------------------- --------------------------------------------------
28-3782-84           Checking              James Carlton Brokeridge
92-3782-43           Checking              Chrissy Arlene McMahon
82-3763-24           Checking              Liliana Wellie Ortez
29-4586-42           Checking              Albert Sonny Odonnell
68-3465-86           Checking              Howie Horace Fallace
37-5764-86           Checking              Xavier Lenny Hereford
72-9375-48           Checking              Jabouni Cabasco Toussey
20-3454-96           Checking              Ophellie Wyman
76-5475-43           Checking              Joseph Patrick Honey
27-3457-49           Checking              Robert Daniel Luner
83-4654-77           Saving                Hobert Umbro Spampinato
40-4658-63           Saving                Mellinda Bridges
37-5490-64           Saving                Cherrine Leonie Horvath
72-3474-24           CD                    Ornella Maiwand
56-8468-59           CD                    Barry Parrang
34-9754-71           CD                    Marthe Helene Bradley

(16 row(s) affected)

We are back to 16 records. Therefore, know the differences among the subquery, the inner join, the left join, and the right join.
Click inside the Query window and press Ctrl + A

Correlated Subqueries

Introduction

A subquery is referred to as correlated if the subquery's operation relies on the parent's statement to produce a value. That is, the subquery is processed based on a condition from the parent statement. For these reasons, when a correlated subquery is a member of a SQL statement, for each record of the parent SQL statement, the SQL interpreter starts with the parent statement, gets into the subquery, compares its value with the parent's statement, and juges if/how it must produce a result.

Creating a Correlated Subquery

There are two primary types of correlated subqueries. In our introduction, we saw that a subquery could be created using the following formula:

SELECT WhatColumn(s),
    (SELECT WhatColumn FROM What WHERE Condition)
FROM WhatObject(s)

We used an example as follows:

SELECT CheckAmount,
      (SELECT CustomerName FROM Management.Customers
       WHERE  CheckCashing.CustomerID = Customers.CustomerID)
FROM CheckCashing;
GO

In this case, there may not be any relationship between the parent SELECT statement and the subquery. With a correlated subquery, that relationship must exist. To apply it, the primary type of a correlated subquery requires a WHERE condition that would tie both statements. The formula to use would be:

SELECT WhatColumn(s) FROM WhatObject(s)
WHERE Condition Operator (Subquery)

You start with a normal SQL statement that specifies where its column(s) would come from. Then you add a WHERE condition that would hold a subquery. The Condition and the Operator must announce how the statements (the parent and the subquery) would be related.

Practical Learning: Creating a Correlated Subquery

To see a summary of all deposits, type the following:

SELECT DepositID,
       LocationID, 
       CustomerID, 
       DepositDate, 
       DepositAmount
FROM   Transactions.Deposits;
GO

Press F5 to execute.
Notice that some deposits were made on the same day, such as on 01/12/2010 or on 01/14/2010

DepositID   LocationID  CustomerID  DepositDate 	DepositAmount
----------- ----------- ----------- ------------------- ---------------------
1           1           1           2011-01-12  	250.00
2           1           6           2011-01-14  	500.00
3           3           8           2011-01-12  	50.00
4           5           2           2011-01-15  	740.00
5           1           5           2011-01-18  	1350.00
6           3           8           2011-01-12  	350.00
7           1           3           2011-01-13  	125.00
8           2           1           2011-01-14  	2500.00
9           3           1           2011-01-22  	200.00
10          2           4           2011-01-18  	750.00
11          2           7           2011-01-10  	250.00
12          4           9           2011-01-12  	3200.00
13          3           8           2011-01-22  	1850.00
14          1           1           2011-01-24  	220.58
15          2           3           2011-01-24  	1250.00
16          4           6           2011-01-25  	775.35
17          1           5           2011-01-25  	3155.00

(17 row(s) affected)

To create a correlated subquery that shows a list of the maximum payments made on certain dates and the IDs of the customers who made them, change the statement as follows:

SELECT Depots.CustomerID AS [Made By],
       Depots.DepositDate AS [Made On],
       Depots.DepositAmount AS Amount
FROM   Transactions.Deposits AS Depots
WHERE  Depots.DepositAmount = (SELECT MAX(Put.DepositAmount)
       	   		       FROM   Transactions.Deposits AS Put
       	   		       WHERE  Depots.CustomerID = Put.CustomerID);
GO

To execute, press F5

Made By     Made On    Amount
----------- ---------- ---------------------
9           2011-01-12 3200.00
8           2011-01-22 1850.00
7           2011-01-10 250.00
6           2011-01-25 775.35
5           2011-01-25 3155.00
4           2011-01-18 750.00
3           2011-01-24 1250.00
2           2011-01-15 740.00
1           2011-01-14 2500.00

(9 row(s) affected)

To show the names of the customers holding the accounts, change the statement as follows:

SELECT (SELECT Clients.CustomerName
	FROM Management.Customers AS Clients
	WHERE Depots.CustomerID = Clients.CustomerID) AS [Made By],
        Depots.DepositDate AS [Made On],
        Depots.DepositAmount AS Amount
FROM    Transactions.Deposits AS Depots
WHERE   Depots.DepositAmount = (SELECT MAX(Put.DepositAmount)
       	   			FROM   Transactions.Deposits AS Put
           			WHERE  Depots.CustomerID = Put.CustomerID);
GO

Press F5 to execute

Made By                            Made On       	Amount
---------------------------------- -------------------- -------------
Leonel James Harbor                2011-01-12		3200.00
Ornella Maiwand                    2011-01-22 		1850.00
Liliana Wellie Ortez               2011-01-10 		250.00
Gloria Aline Wright                2011-01-25 		775.35
Hobert Umbro Spampinato            2011-01-25 		3155.00
Eldridge Powers                    2011-01-18 		750.00
James Norris                       2011-01-24 		1250.00
Chrissy Arlene McMahon             2011-01-15 		740.00
James Carlton Brokeridge           2011-01-14 		2500.00

(9 row(s) affected)

Return to the PowerShell window

To create a correlated subquery that shows a list of the sum of payments made on certain dates by some customers from their IDs, type the following code and press Enter after each line:

>1 SELECT Depots.CustomerID AS [Customer ID],
>2        Depots.DepositDate AS [Date],
>3        Depots.DepositAmount AS [Amount]
>4 FROM   Transactions.Deposits AS Depots
>5 WHERE  Depots.DepositAmount = 
>6	  	(SELECT SUM(Put.DepositAmount)
>7           	 FROM   Transactions.Deposits AS Put
>8           	 WHERE  Depots.CustomerID = Put.CustomerID);
>9 GO

Type Quit and press Enter
Type Exit and press Enter to return to Microsoft SQL Server Management Studio
Close the Query window
When asked whether you want to save, click No
Close Microsoft SQL Server

Exercises

Lesson Summary Questions

What types of DML operations can be performed in a subquery (Select 2)?
1. CREATE
2. INSERT
3. ALTER
4. DROP
5. SELECT
What types of DML operations can be performed in a subquery (Select 2)?
1. CREATE
2. ALTER
3. UPDATE
4. DELETE
5. EXECUTE
What is the maximum level of nesting in a subquery?
1. 3
2. 9
3. 24
4. 32
5. 256
How many values must a subquery produce?
1. 1
2. 2
3. 4
4. 6
5. 9

Answers

Answers
1. Wrong Answer
2. Right Answer
3. Wrong Answer
4. Wrong Answer
5. Right Answer
Answers
1. Wrong Answer
2. Wrong Answer
3. Right Answer
4. Right Answer
5. Wrong Answer
Answers
1. Wrong Answer
2. Wrong Answer
3. Wrong Answer
4. Right Answer
5. Wrong Answer
Answers
1. Right Answer
2. Wrong Answer
3. Wrong Answer
4. Wrong Answer
5. Wrong Answer