Microsoft SQL Server - Lesson 42: Sub-Queries

Sub-Queries Fundamentals

Introduction

A sub-query, also called an inner query, is a query created inside of another query. A sub-query 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 Sub-Queries

Start Microsoft SQL Server and click Connect
On the Standard toolbar, click the New Query button
Open the Kolo Bank: Data Definition Language (DDL) file
Select the whole content of the document and copy
Right-click inside the Query Editor and click Paste
To execute it, on the main menu, click Query -> Execute
Click inside the Query Editor and press Ctrl + A
Open the Kolo Bank: Tables file
Select the whole content of the document and copy
Access the Query Editor and paste the code
On the main menu, click Query -> Execute
Open the Kolo Bank: Views file
Select the whole content of the document and copy
Access the Query Editor and paste the code
To execute it, on the main menu, click Query -> Execute
Open the Kolo Bank: Triggers file
Select the whole content of the document and copy
Access the Query Editor and paste the code
To execute it, on the main menu, click Query -> Execute
Open the Kolo Bank: Data Manipulation Language (DML) file
Select the whole content of the document and copy
Access the Query Editor and paste the code
To execute it, on the main menu, click Query -> Execute
In the Object Explorer, right-click Databases and click Refresh
Expand Databases and expand KoloBank1

Click inside the Query Editor and press Ctrl + A

USE KoloBank1;
GO

SELECT Clients.AccountNumber,
       Clients.FirstName,
       Clients.LastName
FROM   Accounts.Customers Clients;
GO

To execute, press F5
Click inside the Query Editor, press Ctrl + A, and press Delete

A Simple Subquery

Transact-SQL supports various techniques of creating subqueries. A sub-query can be created as an 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 sub-query, start a normal SELECT statement that includes a placeholder for the column(s) of a table (or tables) or view(s). The sub-query can use one of the placeholders but it (the subquery) must be included in parentheses.

When creating a sub-query as a member of a SELECT statement, the statement must have a condition, which is usually a WHERE expression. 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 uses a column. 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 a logical operator. Here is an example:

USE KoloBank1;
GO

SELECT CheckAmount,
       (SELECT LastName
	FROM Accounts.Customers
        WHERE Customers.AccountNumber = Transactions.AccountNumber)
FROM Accounts.Transactions;
GO

This would produce:

Subquery

Among the rules you must observe when creating a sub-query:

The nested SELECT statement must represent only one expression. 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. Here is an example:
```
USE KoloBank1;
GO

SELECT CheckAmount,
       (SELECT FirstName, LastName
	FROM Accounts.Customers
        WHERE Customers.AccountNumber = Transactions.AccountNumber)
FROM Accounts.Transactions;
GO
```

If you want to use more than one column, you can include them in an expression. For example, you can concatenate the columns. Here is an example:

SELECT CheckAmount,
       (SELECT LastName + N', ' + FirstName
	FROM Accounts.Customers
        WHERE Customers.AccountNumber = Transactions.AccountNumber)
FROM Accounts.Transactions;
GO

The column involved in the sub-query 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 sub-query produces a result; but, because that statement is included in parentheses, its scope is limited, which means the (name of the) column of the sub-query cannot be referenced outside.

By default, if you execute the SQL statement as we have done above, the header of the subquery would display nothing. As an alternative, you should add an alias for the sub-query. This can be done as follows:

SELECT CheckAmount As [Check Amount],
       (SELECT CONCAT(Customers.FirstName, N' ', Customers.LastName)
	FROM Accounts.Customers
        WHERE Customers.AccountNumber = Transactions.AccountNumber) As [Issued by]
FROM Accounts.Transactions;
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:

USE KoloBank1;
GO

SELECT Checks.CheckAmount As [Check Amount],
       (SELECT CONCAT(Clients.FirstName, N' ', Clients.LastName)
	FROM Accounts.Customers Clients
        WHERE Checks.AccountNumber = Clients.AccountNumber) As [Issued by]
FROM Accounts.Transactions Checks;
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:

USE KoloBank1;
GO

SELECT Clients.AccountNumber AS [Account #],
       Clients.FirstName,
       Clients.LastName,
      (SELECT ActType.AccountType FROM Accounts.Types AS ActType
       WHERE  Clients.AccountType = ActType.AccountType) AS [Type]
FROM Accounts.Customers AS Clients;
GO

To execute, on the SQL Editor toolbar, click the Execute button
Click inside the Query Editor, press Ctrl + A, and press Delete

A Sub-Query 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, we would create a sub-query in such a WHERE condition. The main rule to follow is that the sub-query must produce the type of value of the column of the external condition. Of course, the sub-query must return only one value.

Practical Learning: Using a Sub-Query as a Criterion

To see the deposits that each customer made per date, type the following code:

USE KoloBank1;
GO

SELECT d.TransactionDate AS [Deposit Date],
       d.DepositAmount AS Amount
FROM   Accounts.Deposits AS d
WHERE  d.AccountNumber = 
       (SELECT c.AccountNumber
        FROM Accounts.Customers AS c
	WHERE c.AccountNumber = d.AccountNumber);
GO

To execute, press F5

Deposit Date Amount
------------ --------------
2013-01-04   325.00
2013-01-04   1128.64
2013-01-05   220.00
2013-01-12   500.00
2013-01-12   2500.00
2013-01-12   975.35
2013-01-12   885.27
2013-01-16   338.92
2013-01-16   842.46
2013-01-18   750.00
2013-01-24   100.00
2013-01-24   186.24
2013-01-25   40.00
2013-01-25   448.62
2013-01-25   2000.00
2013-01-25   400.00
2013-01-26   765.00
2013-01-30   4418.64
2013-01-30   2000.00
2013-01-31   500.00
2013-02-01   1500.00
2013-02-02   328.74
2013-02-02   2500.00
2013-02-05   725.50
2013-02-06   200.00
2013-02-06   200.00
2013-02-06   40.00
2013-02-06   200.00


2013-02-07   320.00
2013-02-08   3644.88
2013-02-08   225.85
2013-02-08   1424.84
2013-02-10   2225.00
2013-02-10   20.00
2013-02-14   200.00
2013-02-14   500.00
2013-02-14   3825.55
2013-02-14   500.00
2013-02-15   428.55
2013-02-15   148.82
2013-02-17   845.68
2013-02-22   2448.52
2013-02-22   500.00
2013-02-24   1258.72
2013-02-27   1622.54
2013-02-28   1322.64
2013-02-28   245.53
2013-02-28   120.44
2013-03-01   808.12
2013-03-01   328.45
2013-03-01   1000.00
2013-03-06   20.00
2013-03-06   1248.64
2013-03-08   1426.94
2013-03-12   2428.48

(55 row(s) affected)

Click inside the top section of the Query Editor, press Ctrl + A, and press Delete

Sub-Queries 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 Sub-Query

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

USE KoloBank1;
GO

SELECT Clients.AccountNumber, CONCAT(Clients.FirstName, N' ', Clients.LastName),
      (SELECT SUM(Depots.DepositAmount)
       FROM Accounts.Transactions AS Depots
       WHERE Clients.AccountNumber = Depots.AccountNumber) AS [Client Total Deposits]
FROM Accounts.Customers AS Clients;
GO

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

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

USE KoloBank1;
GO

SELECT Clients.AccountNumber, CONCAT(Clients.FirstName, N' ', Clients.LastName),
      (SELECT SUM(Depots.DepositAmount)
       FROM Accounts.Transactions AS Depots
       WHERE Clients.AccountNumber = Depots.AccountNumber) AS [Client Total Deposits]
FROM Accounts.Customers AS Clients
WHERE (SELECT SUM(Depots.DepositAmount) FROM Accounts.Transactions AS Depots
       WHERE  Clients.AccountNumber = Depots.AccountNumber) IS NOT NULL;
GO

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

Combining Sub-Queries

Remember that a sub-query 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 Sub-Queries

To create more than one subquery, type the following:

USE KoloBank1;
GO

SELECT Clients.AccountNumber AS [Account #],
       CONCAT(Clients.FirstName, N' ', Clients.LastName) AS Customer,
      (SELECT SUM(Depots.DepositAmount) FROM Accounts.Transactions AS Depots
       WHERE  Clients.AccountNumber = Depots.AccountNumber) AS Deposits,
      (SELECT SUM(Paid.WithdrawalAmount) FROM Accounts.Transactions AS Paid
       WHERE  Clients.AccountNumber = Paid.AccountNumber) AS Withdrawn
FROM Accounts.Customers AS Clients;
GO

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

Nesting Sub-Queries

As stated already, a sub-query can be a SQL statement created inside an outer SELECT statement. In the same way, a sub-query can be created inside another sub-query. In fact, one sub-query can be created in a sub-query that itself is inside another sub-query. 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 sub-query must produce only one value, it you use a multi-level expression, each nested sub-query must produce one value that its parent sub-query can use.

Practical Learning: Nesting a Sub-Query

To use an example of nesting a sub-query, type the following statement:

USE KoloBank1;
GO

SELECT d.AccountNumber N'Account #',
       d.DepositAmount N'Amount'
FROM Accounts.Deposits AS d
WHERE d.AccountNumber = 
      (SELECT c.AccountNumber
       FROM Accounts.Customers AS c
       WHERE c.EmployeeNumber = 
	     (SELECT EmployeeNumber
              FROM Management.Employees
              WHERE EmployeeNumber = N'552-884'));
GO

To execute, press F5
Click inside the Query Editor, press Ctrl + A, and press Delete

Sub-Queries and Expressions

Remember that a sub-query 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

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):

USE KoloBank1;
GO

SELECT Category, StartingSalary AS [Starting Salary for Managers]
FROM   Management.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   Management.Employees AS Managers
WHERE (Title LIKE N'%Manager%');
GO
SELECT STDEV(HourlySalary) AS [Standard Deviation of Managers Salaries]
       FROM Management.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   Management.Employees AS Managers
WHERE  Managers.HourlySalary >=
     ((SELECT StartingSalary
       FROM   Management.StartingSalaries
       WHERE Category LIKE N'Manager') +
      (SELECT STDEV(HourlySalary)
       FROM Management.Employees
       WHERE Title LIKE N'%Manager%'));
GO

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

To create an expression, type following:

USE KoloBank1;
GO

SELECT c.AccountNumber AS [Account #],
       CONCAT(c.FirstName, N' ', c.LastName) AS Customer,
      (SELECT SUM(d1.DepositAmount)
       FROM Accounts.Deposits AS d1
       WHERE  c.AccountNumber = d1.AccountNumber) AS Deposited,
      (SELECT SUM(w1.WithdrawalAmount)
       FROM Accounts.Withdrawals AS w1
       WHERE  w1.AccountNumber = c.AccountNumber) AS Withdrawn,
      (SELECT SUM(d2.DepositAmount)
       FROM Accounts.Deposits AS d2
       WHERE  d2.AccountNumber = c.AccountNumber) -
      (SELECT SUM(w2.WithdrawalAmount)
       FROM Accounts.Withdrawals AS w2
       WHERE  w2.AccountNumber = c.AccountNumber) As Balance
FROM Accounts.Customers AS c;
GO

To execute, press F5

Account #            Customer                                            Deposited             Withdrawn             Balance
-------------------- --------------------------------------------------- --------------------- --------------------- ---------------------
13-850069-28         Jane Jeffries                                       NULL                  NULL                  NULL
20-240705-64         Jeffrey Andrews                                     NULL                  NULL                  NULL
20-304042-49         Ophellie Wyman                                      3802.26               897.10                2905.16
27-314257-84         Robert Luner                                        5028.40               2205.86               2822.54
28-370082-80         Gloria Wright                                       7153.48               NULL                  NULL
29-425806-46         Albert Odonnell                                     8284.19               NULL                  NULL
30-514090-26         Cherrine Horvath                                    2092.97               1331.53               761.44
38-402217-59         James Schneider                                     2335.71               400.00                1935.71
40-460582-63         Mellinda Bridges                                    4500.00               NULL                  NULL
44-504058-04         Leonel Harbor                                       2500.00               NULL                  NULL
47-474083-29         Annette Benson                                      528.82                352.00                176.82
51-842068-25         Barry Parrang                                       1948.64               NULL                  NULL
68-304605-84         Jonathan Myler                                      3636.45               300.00                3336.45
68-640304-15         Eldridge Powers                                     1242.46               NULL                  NULL
72-304724-26         Ornella Maiwand                                     1426.94               NULL                  NULL
72-903175-44         Jabouni Toussey                                     3644.88               NULL                  NULL
76-504275-24         Joseph Honey                                        1071.53               NULL                  NULL
80-240840-51         Robert Chen                                         NULL                  NULL                  NULL
82-370863-62         Liliana Ortez                                       765.00                NULL                  NULL
83-462584-73         Hobert Spampinato                                   500.00                NULL                  NULL
84-697064-28         Xavier Hereford                                     2000.00               NULL                  NULL
84-975004-57         Marthe Bradley                                      1424.84               NULL                  NULL
92-037082-42         Chrissy McMahon                                     NULL                  NULL                  NULL
94-477085-03         Ismail Zorbah                                       220.00                210.00                10.00

(24 row(s) affected)

Click inside the Query Editor, press Ctrl + A, and press Delete

Other Operations on Sub-Queries

Logical Operations in Sub-Queries

So far, we have seen that you can use one or more Boolean operators (=, <, <=, >, >=, and <>) in a WHERE condition of a sub-query. 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 sub-query. 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, type the following code:

USE KoloBank1;
GO

SELECT d.LocationCode Location,
       d.TransactionDate AS [Date],
       d.DepositAmount AS [Deposit Amount]
FROM Accounts.Deposits AS d
WHERE d.LocationCode IN 
     (SELECT w.LocationCode
      FROM Accounts.Withdrawals As w
      WHERE w.LocationCode = N'SLVSSL');
GO

To execute, press F5
Click inside the Query Editor and press Ctrl + A

To use more than one value for IN, type the following code:

USE KoloBank1;
GO

SELECT d.AccountNumber N'Account #',
       d.TransactionDate AS [Date],
       d.DepositAmount AS [Deposit Amount]
FROM   Accounts.Deposits AS d
WHERE  d.LocationCode IN 
       (SELECT w.LocationCode
        FROM Accounts.Withdrawals AS w
        WHERE (w.LocationCode = N'SLVSSL') OR (d.LocationCode = N'ALXJPZ'));
GO

To execute, press F5
Click inside the Query Editor and press Ctrl + A

To negate the IN statement to 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):

USE KoloBank1;
GO

SELECT d.AccountNumber N'Account #',
       d.TransactionDate AS [Date],
       d.DepositAmount AS [Deposit Amount]
FROM   Accounts.Deposits AS d
WHERE  d.LocationCode NOT IN 
       (SELECT w.LocationCode
        FROM Accounts.Withdrawals AS w
        WHERE (w.LocationCode = N'SLVSSL') OR (d.LocationCode = N'ALXJPZ'));
GO

To execute, press F5
Click inside the Query Editor, press Ctrl + A, and press Delete

Data Entry and Sub-Queries

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 sub-query 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:

USE KoloBank1;
GO

SELECT ALL *
FROM   Management.StartingSalaries;
GO

To execute, press F5
Click inside the Query Editor and press Ctrl + A

To use a sub-query in data entry, 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):

USE KoloBank1;
GO

INSERT INTO Management.Employees(EmployeeNumber,
                                 HourlySalary,
				 FirstName, MiddleName, LastName)
VALUES(N'295-420',
      (SELECT StartingSalary
       FROM   Management.StartingSalaries
       WHERE  Category = N'Base'),
       N'Margareth', N'Elizabeth', N'Schubert');
GO

To execute, press F5
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:

USE KoloBank1;
GO

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

Press F5 to execute

EmployeeNumber FirstName       MiddleName      LastName        LocationCode Title      CanCreateAccount EmailAddress     WorkPhone   Extension Address                     City             State   ZIPCode      Country    PhoneNumber          HourlySalary          Notes
-------------- --------------- --------------- --------------- ------------ ---------- ---------------- ---------------- ----------- --------- --------------------------- ---------------- ------- ------------ ---------- -------------------- --------------------- --------
295-420        Margareth       Elizabeth       Schubert        NULL         NULL       NULL             NULL             NULL        NULL      NULL                        NULL             NULL    NULL         USA        NULL                 10.00                 NULL
595-028        Calvin          Alfred          Khone           DCK10S       Cashier    NULL             NULL             NULL        NULL      516 Linden Street Apt D2    Silver Spring    MD      20902        USA        (301) 839-4253       6.85                  NULL
624-993        Kirsten         Pennie          Roberts         WHTFLT       Intern     NULL             NULL             NULL        NULL      1336 Philadelphia St        Baltimore        MD      21207        USA        (410) 653-1309       8.05                  NULL
639-814        Samuel          Howard          Jones           WHTFLT       Cashier    NULL             NULL             NULL        NULL      9337 Cachet St              Baltimore        MD      21205        USA        (410) 653-1309       8.25                  NULL
774-284        Herbert         Marc            Jerremies       GTWMST       Intern     1                NULL             NULL        NULL      8254 12th St. N.E.          Washington       DC      20004        USA        (410) 653-1309       4.15                  NULL
864-808        Mark            Richard         Georges         CPKUMD       Intern     NULL             NULL             NULL        NULL      1101 Elon Rd                Takoma Park      MD      20912        USA        (202) 719-7335       7.12                  NULL

(6 row(s) affected)

Click inside the Query Editor, press Ctrl + A, and press Delete

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 sub-query. 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 a list of all employees, type the following code:

USE KoloBank1;
GO

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

To execute, press F5

Empl #     Employee Name                                        Title                                              Salary/Hr
---------- ---------------------------------------------------- -------------------------------------------------- ---------------------
000-100    Teller Machine, ATM                                  NULL                                               NULL
000-200    Transaction, Automatic                               Automatic Computer Transaction                     NULL
111-111    Web, Online                                          Online Transaction                                 NULL
209-400    Cole, Krista                                         Branch Manager                                     28.55
248-552    Olney, Michael                                       Cashier                                            17.52
279-377    Dobmeyer, Michael                                    Shift Programmer                                   20.56
280-082    Coleman, Geoffrey                                    Head Cashier                                       22.82
284-005    Wine, Anne                                           Cashier                                            16.68
284-725    Dundon, Wanda                                        Cashier                                            15.95
294-075    Velker, Luis                                         Branch Manager                                     28.75
295-420    Schubert, Margareth                                  NULL                                               10.00
308-406    Roland, Jessica                                      Intern                                             12.72
428-947    Njawe, Krystal                                       Cashier                                            14.85
461-842    Vive, Dorrin                                         Branch Manager                                     NULL
462-777    Zeran, Ada                                           Administrative Assistant                           15.48
481-114    Lansing, Samuel                                      Cashier                                            15.25
482-799    Roberts, Annette                                     Cashier                                            14.88
484-050    Oslin, Marianne                                      Assistant Manager                                  26.86
492-081    Kilborne, Jeffrey                                    Accounts Manager                                   26.15
500-284    Charles, Alexa                                       Cashier                                            17.75
503-938    Stephenson, Michelle                                 Head Teller                                        21.75
507-728    Wray, Timothy                                        Branch Manager                                     35.62
533-825    Kast, Aaron                                          Accounts Manager                                   24.34
552-884    Donovan, Joy                                         Customer Accounts Manager                          29.55
595-028    Khone, Calvin                                        Cashier                                            6.85
624-825    Parkinson, Luke                                      Intern                                             14.35
624-993    Roberts, Kirsten                                     Intern                                             8.05
639-814    Jones, Samuel                                        Cashier                                            8.25
660-026    Frieddle, Lucas                                      Cashier                                            16.24
712-083    Huntsmann, Wendy                                     Cashier                                            18.34
715-204    Ramirez, Adam                                        Branch Manager                                     NULL
722-286    Fisher, Donald                                       Public Relations Manager                           25.32
736-626    Coen, Annabelle                                      Cashier                                            15.75
774-284    Jerremies, Herbert                                   Intern                                             4.15
829-313    Michaels, Simon                                      Cashier                                            18.05
864-808    Georges, Mark                                        Intern                                             7.12
927-395    Vanecek, Luisa                                       Cashier                                            16.75
928-495    Duck, Ryan                                           General Manager                                    42.74
952-846    Possemato, John                                      Assistant Manager                                  24.12

(39 row(s) affected)

Notice that some employees have a salary as NULL.
Click inside the Query window and press Ctrl + A

To see the list of interns and their salaries, change the code as follows:

USE KoloBank1;
GO

SELECT * FROM Management.StartingSalaries;
GO
SELECT EmployeeNumber AS [Empl #],
       LastName + N', ' + FirstName AS [Intern Name],
       HourlySalary AS [Salary/Hr]
FROM Management.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
---------- ---------------------------------------------------- ---------------------
308-406    Roland, Jessica                                      12.72
624-825    Parkinson, Luke                                      14.35
624-993    Roberts, Kirsten                                     8.05
774-284    Jerremies, Herbert                                   4.15
864-808    Georges, Mark                                        7.12

(5 row(s) affected)

Click inside the Query window and press Ctrl + A

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

USE KoloBank1;
GO

SELECT * FROM Management.StartingSalaries;
GO
SELECT EmployeeNumber AS [Empl #],
       LastName + N', ' + FirstName AS [Employee Name],
       Title,
       HourlySalary AS [Salary/Hr]
FROM Management.Employees
WHERE HourlySalary < (SELECT StartingSalary
                      FROM   Management.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
---------- ------------------------ ---------- ----------
595-028    Khone, Calvin            Cashier    6.85
624-993    Roberts, Kirsten         Intern     8.05
639-814    Jones, Samuel            Cashier    8.25
774-284    Jerremies, Herbert       Intern     4.15
864-808    Georges, Mark            Intern     7.12

(5 row(s) affected)

In the Object Explorer, under KoloBank1, expand Tables
Under Tables, right-click Management.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 sub-query 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:

USE KoloBank1;
GO

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

UPDATE Management.Employees
SET HourlySalary = (SELECT StartingSalary
		    FROM   Management.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 Management.Employees
GO

To execute, press F5

Empl #     Employee Name                                        Title                                              Salary/Hr
---------- ---------------------------------------------------- -------------------------------------------------- ---------------------
000-100    Teller Machine, ATM                                  NULL                                               NULL
000-200    Transaction, Automatic                               Automatic Computer Transaction                     NULL
111-111    Web, Online                                          Online Transaction                                 NULL
209-400    Cole, Krista                                         Branch Manager                                     28.55
248-552    Olney, Michael                                       Cashier                                            17.52
279-377    Dobmeyer, Michael                                    Shift Programmer                                   20.56
280-082    Coleman, Geoffrey                                    Head Cashier                                       22.82
284-005    Wine, Anne                                           Cashier                                            16.68
284-725    Dundon, Wanda                                        Cashier                                            15.95
294-075    Velker, Luis                                         Branch Manager                                     28.75
295-420    Schubert, Margareth                                  NULL                                               10.00
308-406    Roland, Jessica                                      Intern                                             12.72
428-947    Njawe, Krystal                                       Cashier                                            14.85
461-842    Vive, Dorrin                                         Branch Manager                                     NULL
462-777    Zeran, Ada                                           Administrative Assistant                           15.48
481-114    Lansing, Samuel                                      Cashier                                            15.25
482-799    Roberts, Annette                                     Cashier                                            14.88
484-050    Oslin, Marianne                                      Assistant Manager                                  26.86
492-081    Kilborne, Jeffrey                                    Accounts Manager                                   26.15
500-284    Charles, Alexa                                       Cashier                                            17.75
503-938    Stephenson, Michelle                                 Head Teller                                        21.75
507-728    Wray, Timothy                                        Branch Manager                                     35.62
533-825    Kast, Aaron                                          Accounts Manager                                   24.34
552-884    Donovan, Joy                                         Customer Accounts Manager                          29.55
595-028    Khone, Calvin                                        Cashier                                            6.85
624-825    Parkinson, Luke                                      Intern                                             14.35
624-993    Roberts, Kirsten                                     Intern                                             8.05
639-814    Jones, Samuel                                        Cashier                                            8.25
660-026    Frieddle, Lucas                                      Cashier                                            16.24
712-083    Huntsmann, Wendy                                     Cashier                                            18.34
715-204    Ramirez, Adam                                        Branch Manager                                     NULL
722-286    Fisher, Donald                                       Public Relations Manager                           25.32
736-626    Coen, Annabelle                                      Cashier                                            15.75
774-284    Jerremies, Herbert                                   Intern                                             4.15
829-313    Michaels, Simon                                      Cashier                                            18.05
864-808    Georges, Mark                                        Intern                                             7.12
927-395    Vanecek, Luisa                                       Cashier                                            16.75
928-495    Duck, Ryan                                           General Manager                                    42.74
952-846    Possemato, John                                      Assistant Manager                                  24.12

(39 row(s) affected)


(5 row(s) affected)
Empl #     Employee Name                                        Title                                              Salary/Hr
---------- ---------------------------------------------------- -------------------------------------------------- ---------------------
000-100    Teller Machine, ATM                                  NULL                                               12.50
000-200    Transaction, Automatic                               Automatic Computer Transaction                     12.50
111-111    Web, Online                                          Online Transaction                                 12.50
209-400    Cole, Krista                                         Branch Manager                                     28.55
248-552    Olney, Michael                                       Cashier                                            17.52
279-377    Dobmeyer, Michael                                    Shift Programmer                                   20.56
280-082    Coleman, Geoffrey                                    Head Cashier                                       22.82
284-005    Wine, Anne                                           Cashier                                            16.68
284-725    Dundon, Wanda                                        Cashier                                            15.95
294-075    Velker, Luis                                         Branch Manager                                     28.75
295-420    Schubert, Margareth                                  NULL                                               10.00
308-406    Roland, Jessica                                      Intern                                             12.72
428-947    Njawe, Krystal                                       Cashier                                            14.85
461-842    Vive, Dorrin                                         Branch Manager                                     12.50
462-777    Zeran, Ada                                           Administrative Assistant                           15.48
481-114    Lansing, Samuel                                      Cashier                                            15.25
482-799    Roberts, Annette                                     Cashier                                            14.88
484-050    Oslin, Marianne                                      Assistant Manager                                  26.86
492-081    Kilborne, Jeffrey                                    Accounts Manager                                   26.15
500-284    Charles, Alexa                                       Cashier                                            17.75
503-938    Stephenson, Michelle                                 Head Teller                                        21.75
507-728    Wray, Timothy                                        Branch Manager                                     35.62
533-825    Kast, Aaron                                          Accounts Manager                                   24.34
552-884    Donovan, Joy                                         Customer Accounts Manager                          29.55
595-028    Khone, Calvin                                        Cashier                                            6.85
624-825    Parkinson, Luke                                      Intern                                             14.35
624-993    Roberts, Kirsten                                     Intern                                             8.05
639-814    Jones, Samuel                                        Cashier                                            8.25
660-026    Frieddle, Lucas                                      Cashier                                            16.24
712-083    Huntsmann, Wendy                                     Cashier                                            18.34
715-204    Ramirez, Adam                                        Branch Manager                                     12.50
722-286    Fisher, Donald                                       Public Relations Manager                           25.32
736-626    Coen, Annabelle                                      Cashier                                            15.75
774-284    Jerremies, Herbert                                   Intern                                             4.15
829-313    Michaels, Simon                                      Cashier                                            18.05
864-808    Georges, Mark                                        Intern                                             7.12
927-395    Vanecek, Luisa                                       Cashier                                            16.75
928-495    Duck, Ryan                                           General Manager                                    42.74
952-846    Possemato, John                                      Assistant Manager                                  24.12

(39 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 sub-query that specifies a condition to update the minimum salary of interns, type code as follows:

USE KoloBank1;
GO

-- First show the list of interns
SELECT EmployeeNumber AS [Empl #],
       LastName + N', ' + FirstName AS [Intern Name],
       HourlySalary AS [Salary/Hr]
FROM Management.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 Management.Employees
SET HourlySalary = (SELECT StartingSalary
		    FROM   Management.StartingSalaries
		    WHERE Category = N'Intern')
WHERE (Title LIKE N'Intern') AND (HourlySalary < (SELECT StartingSalary
						  FROM   Management.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 Management.Employees
WHERE Title LIKE N'Intern';
GO

To execute, press F5

Empl #     Intern Name                                          Salary/Hr
---------- ---------------------------------------------------- ---------------------
308-406    Roland, Jessica                                      12.72
624-825    Parkinson, Luke                                      14.35
624-993    Roberts, Kirsten                                     8.05
774-284    Jerremies, Herbert                                   4.15
864-808    Georges, Mark                                        7.12

(5 row(s) affected)


(4 row(s) affected)
Empl #     Intern Name                                          Salary/Hr
---------- ---------------------------------------------------- ---------------------
308-406    Roland, Jessica                                      14.05
624-825    Parkinson, Luke                                      14.35
624-993    Roberts, Kirsten                                     14.05
774-284    Jerremies, Herbert                                   14.05
864-808    Georges, Mark                                        14.05

(5 row(s) affected)

Click inside the Query Editor, press Ctrl + A, and press Delete

Deleting Records Using a Sub-Query

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

Sub-Queries and Joins

In many cases, a sub-query plays the same role as a join. That is, a sub-query is primarily an alternative to a join. A sub-query 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 sub-query, use its WHERE condition to specify how the tables or views are related or joined.

The main difference between a join and a sub-query 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 sub-query 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 sub-query.

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 sub-query, type the following code:

USE KoloBank1;
GO

SELECT Clients.AccountNumber AS [Account #],
      (SELECT Category.AccountType
       FROM Accounts.Types AS Category
       WHERE Clients.AccountType = Category.AccountType) AS [Type],
      (LastName + N', ' + FirstName) AS Customer
FROM Accounts.Customers AS Clients;
GO

To execute, press F5

Account #            Type                      Customer
-------------------- ------------------------- ------------------------
13-850069-28         Checking                  Jeffries, Jane
20-240705-64         Checking                  Andrews, Jeffrey
20-304042-49         Checking                  Wyman, Ophellie
27-314257-84         Checking                  Luner, Robert
28-370082-80         Saving                    Wright, Gloria
29-425806-46         Checking                  Odonnell, Albert
30-514090-26         Checking                  Horvath, Cherrine
38-402217-59         Checking                  Schneider, James
40-460582-63         CD                        Bridges, Mellinda
44-504058-04         Saving                    Harbor, Leonel
47-474083-29         Checking                  Benson, Annette
51-842068-25         Checking                  Parrang, Barry
68-304605-84         Checking                  Myler, Jonathan
68-640304-15         Checking                  Powers, Eldridge
72-304724-26         Saving                    Maiwand, Ornella
72-903175-44         Saving                    Toussey, Jabouni
76-504275-24         Checking                  Honey, Joseph
80-240840-51         Checking                  Chen, Robert
82-370863-62         Checking                  Ortez, Liliana
83-462584-73         Checking                  Spampinato, Hobert
84-697064-28         CD                        Hereford, Xavier
84-975004-57         Saving                    Bradley, Marthe
92-037082-42         Checking                  McMahon, Chrissy
94-477085-03         Saving                    Zorbah, Ismail

(24 row(s) affected)

Click inside the Query window and press Ctrl + A

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

USE KoloBank1;
GO

SELECT Clients.AccountNumber As [Account #],
       Category.AccountType As [Type],
       (LastName + N', ' + FirstName) As Customer
FROM   Accounts.Customers AS Clients INNER JOIN Accounts.Types AS Category 
       ON Clients.AccountType = Category.AccountType;
GO

To execute, press F5
Click inside the Query window and press Ctrl + A

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

USE KoloBank1;
GO

SELECT Clients.AccountNumber AS [Account #],
       Category.AccountType AS [Type],
       (LastName + N', ' + FirstName)  AS Customer
FROM   Accounts.Customers AS Clients LEFT JOIN
       Accounts.Types AS Category 
       ON Clients.AccountType = Category.AccountType;
GO

To execute, press F5
Click inside the Query window and press Ctrl + A

To create a right join, change the LEFT keyword to RIGHT

USE KoloBank1;
GO

SELECT Clients.AccountNumber AS [Account #],
       Category.AccountType AS [Type],
       (LastName + N', ' + FirstName) AS Customer
FROM   Accounts.Customers AS Clients RIGHT JOIN
       Management.AccountTypes AS Category 
       ON Clients.AccountType = Category.AccountType;
GO

To execute, press F5

Account #            Type                      Customer
-------------------- ------------------------- -------------------
40-460582-63         CD                        Bridges, Mellinda
84-697064-28         CD                        Hereford, Xavier
13-850069-28         Checking                  Jeffries, Jane
20-240705-64         Checking                  Andrews, Jeffrey
20-304042-49         Checking                  Wyman, Ophellie
27-314257-84         Checking                  Luner, Robert
29-425806-46         Checking                  Odonnell, Albert
30-514090-26         Checking                  Horvath, Cherrine
38-402217-59         Checking                  Schneider, James
47-474083-29         Checking                  Benson, Annette
51-842068-25         Checking                  Parrang, Barry
68-304605-84         Checking                  Myler, Jonathan
68-640304-15         Checking                  Powers, Eldridge
76-504275-24         Checking                  Honey, Joseph
80-240840-51         Checking                  Chen, Robert
82-370863-62         Checking                  Ortez, Liliana
83-462584-73         Checking                  Spampinato, Hobert
92-037082-42         Checking                  McMahon, Chrissy
28-370082-80         Saving                    Wright, Gloria
44-504058-04         Saving                    Harbor, Leonel
72-304724-26         Saving                    Maiwand, Ornella
72-903175-44         Saving                    Toussey, Jabouni
84-975004-57         Saving                    Bradley, Marthe
94-477085-03         Saving                    Zorbah, Ismail

(24 row(s) affected)

Click inside the Query Editor, press Ctrl + A, and press Delete

Correlated Sub-Queries

Introduction

A sub-query is referred to as correlated if the sub-query's operation relies on the parent's statement to produce a value. That is, the sub-query is processed based on a condition from the parent statement. For these reasons, when a correlated sub-query 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 sub-query, 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 sub-query 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 Accounts.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 sub-query. With a correlated sub-query, that relationship must exist. To apply it, the primary type of a correlated sub-query 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 sub-query. The Condition and the Operator must announce how the statements (the parent and the sub-query) would be related.

Practical Learning: Creating a Correlated Sub-Query

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

USE KoloBank1;
GO

SELECT LocationCode,
       EmployeeNumber,
       AccountNumber,
       TransactionDate,
       CurrencyType,
       DepositAmount,
       Balance
FROM Accounts.Transactions
WHERE TransactionType = N'Deposit';
GO

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

LocationCode EmployeeNumber AccountNumber        TransactionDate CurrencyType         DepositAmount         Balance
------------ -------------- -------------------- --------------- -------------------- --------------------- ---------------------
SLVSSL       952-846        20-304042-49         2013-01-04      Cash                 325.00                325.00
ALXJPZ       294-075        68-304605-84         2013-01-04      Check                1128.64               1128.64
CPKUMD       280-082        94-477085-03         2013-01-05      Check                220.00                220.00
SLVSSL       952-846        27-314257-84         2013-01-12      Check                500.00                500.00
ALXJPZ       552-884        28-370082-80         2013-01-12      Check                2500.00               2500.00
WHTFLT       533-825        38-402217-59         2013-01-12      Check                975.35                975.35
GTWMST       533-825        68-304605-84         2013-01-12      Check                885.27                2013.91
SLVSSL       284-005        27-314257-84         2013-01-16      Check                338.92                838.92
GTWMST       492-081        68-640304-15         2013-01-16      Check                842.46                842.46
CPKUMD       461-842        30-514090-26         2013-01-18      Cash                 750.00                750.00
WHTFLT       712-083        27-314257-84         2013-01-24      Cash                 100.00                98.92
WHTFLT       829-313        38-402217-59         2013-01-24      Check                186.24                961.59
CPKUMD       461-842        29-425806-46         2013-01-25      Cash                 40.00                 40.00
0SSCTPL      000-100        38-402217-59         2013-01-25      Check                448.62                1410.21
WHTFLT       484-050        40-460582-63         2013-01-25      Cash                 2000.00               2000.00
SLVSSL       284-005        68-640304-15         2013-01-25      Cash                 400.00                1242.46
ALXJPZ       294-075        82-370863-62         2013-01-26      Check                765.00                765.00
CPKUMD       481-114        29-425806-46         2013-01-30      Check                4418.64               4458.64
SLVSSL       503-938        84-697064-28         2013-01-30      Cash                 2000.00               2000.00
SLVSSL       660-026        20-304042-49         2013-01-31      Cash                 500.00                455.16
WHTFLT       552-884        40-460582-63         2013-02-01      Cash                 1500.00               3500.00
0SSCTPL      000-100        20-304042-49         2013-02-02      Check                328.74                783.90
CPKUMD       461-842        44-504058-04         2013-02-02      Check                2500.00               2500.00
0WPLZM       000-100        38-402217-59         2013-02-05      Check                725.50                2129.71
0UNSDC       000-100        27-314257-84         2013-02-06      Cash                 200.00                152.06
WHTFLT       484-050        30-514090-26         2013-02-06      Cash                 200.00                191.52
SLVSSL       952-846        47-474083-29         2013-02-06      Cash                 40.00                 40.00
WHTFLT       533-825        51-842068-25         2013-02-06      Cash                 200.00                200.00
0WTFML       000-100        47-474083-29         2013-02-07      Cash                 320.00                360.00
SLVSSL       209-400        72-903175-44         2013-02-08      Direct Deposit       3644.88               3644.88
WHTFLT       507-728        76-504275-24         2013-02-08      Check                225.85                225.85
CPKUMD       248-552        84-975004-57         2013-02-08      Check                1424.84               1424.84
0WTFML       000-100        28-370082-80         2013-02-10      Cash                 2225.00               4725.00
GTWMST       484-050        30-514090-26         2013-02-10      Check                20.00                 15.67
SLVSSL       284-725        20-304042-49         2013-02-14      Check                200.00                603.90
0UNSDC       000-100        27-314257-84         2013-02-14      Cash                 500.00                492.06
CPKUMD       461-842        29-425806-46         2013-02-14      Check                3825.55               8278.19
SLVSSL       952-846        83-462584-73         2013-02-14      Cash                 500.00                500.00
WHTFLT       507-728        30-514090-26         2013-02-15      Check                428.55                419.27
GTWMST       492-081        47-474083-29         2013-02-15      Check                148.82                268.82
SLVSSL       209-400        76-504275-24         2013-02-17      Check                845.68                1071.53
SLVSSL       660-026        20-304042-49         2013-02-22      Cash                 2448.52               2864.16
WHTFLT       280-082        51-842068-25         2013-02-22      Cash                 500.00                700.00
SLVSSL       000-200        27-314257-84         2013-02-24      Check                1258.72               1650.78
GTWMST       533-825        68-304605-84         2013-02-27      Check                1622.54               3330.45
CPKUMD       829-313        27-314257-84         2013-02-28      Check                1322.64               2973.42
CPKUMD       492-081        30-514090-26         2013-02-28      Check                245.53                306.55
GTWMST       481-114        30-514090-26         2013-02-28      Check                120.44                426.99
CPKUMD       481-114        27-314257-84         2013-03-01      Check                808.12                3775.54
SLVSSL       462-777        30-514090-26         2013-03-01      Cash                 328.45                749.44
WHTFLT       484-050        40-460582-63         2013-03-01      Cash                 1000.00               4500.00
SLVSSL       294-075        47-474083-29         2013-03-06      Cash                 20.00                 176.82
GTWMST       492-081        51-842068-25         2013-03-06      Check                1248.64               1942.64
SLVSSL       952-846        72-304724-26         2013-03-08      Check                1426.94               1426.94
0SSMTR       000-100        28-370082-80         2013-03-12      Check                2428.48               7153.48

(55 row(s) affected)

Click inside the Query window and press Ctrl + A

To create a correlated sub-query 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:

USE KoloBank1;
GO

SELECT d.AccountNumber   AS [Made By],
       d.TransactionDate AS [Made On],
       d.DepositAmount   AS Amount
FROM   Accounts.Deposits AS d
WHERE  d.DepositAmount = (SELECT MAX(Put.DepositAmount)
       	   	 	  FROM   Accounts.Transactions AS Put
       	   		  WHERE  Put.AccountNumber = d.AccountNumber);
GO

To execute, press F5

Made By              Made On    Amount
-------------------- ---------- ---------------------
94-477085-03         2013-01-05 220.00
28-370082-80         2013-01-12 2500.00
38-402217-59         2013-01-12 975.35
68-640304-15         2013-01-16 842.46
30-514090-26         2013-01-18 750.00
40-460582-63         2013-01-25 2000.00
82-370863-62         2013-01-26 765.00
29-425806-46         2013-01-30 4418.64
84-697064-28         2013-01-30 2000.00
44-504058-04         2013-02-02 2500.00
47-474083-29         2013-02-07 320.00
72-903175-44         2013-02-08 3644.88
84-975004-57         2013-02-08 1424.84
83-462584-73         2013-02-14 500.00
76-504275-24         2013-02-17 845.68
20-304042-49         2013-02-22 2448.52
68-304605-84         2013-02-27 1622.54
27-314257-84         2013-02-28 1322.64
51-842068-25         2013-03-06 1248.64
72-304724-26         2013-03-08 1426.94
Warning: Null value is eliminated by an aggregate or other SET operation.

(20 row(s) affected)

Click inside the Query window and press Ctrl + A

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

USE KoloBank1;
GO

SELECT (SELECT CONCAT(LastName, N', ', FirstName)
    	FROM Accounts.Customers AS Clients
	WHERE Clients.AccountNumber = Depots.AccountNumber) AS [Made By],
        Depots.TransactionDate AS [Made On],
        Depots.DepositAmount AS Amount
FROM    Accounts.Deposits AS Depots
WHERE   Depots.DepositAmount = (SELECT MAX(Put.DepositAmount)
                	   	FROM   Accounts.Deposits AS Put
                    		WHERE  Depots.AccountNumber = Put.AccountNumber);
GO

Press F5 to execute

Made By                                              Made On    Amount
---------------------------------------------------- ---------- -----------------
Zorbah, Ismail                                       2013-01-05 220.00
Bradley, Marthe                                      2013-02-08 1424.84
Hereford, Xavier                                     2013-01-30 2000.00
Spampinato, Hobert                                   2013-02-14 500.00
Ortez, Liliana                                       2013-01-26 765.00
Honey, Joseph                                        2013-02-17 845.68
Toussey, Jabouni                                     2013-02-08 3644.88
Maiwand, Ornella                                     2013-03-08 1426.94
Powers, Eldridge                                     2013-01-16 842.46
Myler, Jonathan                                      2013-02-27 1622.54
Parrang, Barry                                       2013-03-06 1248.64
Benson, Annette                                      2013-02-07 320.00
Harbor, Leonel                                       2013-02-02 2500.00
Bridges, Mellinda                                    2013-01-25 2000.00
Schneider, James                                     2013-01-12 975.35
Horvath, Cherrine                                    2013-01-18 750.00
Odonnell, Albert                                     2013-01-30 4418.64
Wright, Gloria                                       2013-01-12 2500.00
Luner, Robert                                        2013-02-28 1322.64
Wyman, Ophellie                                      2013-02-22 2448.52

(20 row(s) affected)

Close Microsoft SQL Server
When asked whether you want to save, click No


Previous	Copyright © 2008-2022, FunctionX, Inc.	Next