Home

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 LearningPractical Learning: Introducing Sub-Queries

  1. Start Microsoft SQL Server and click Connect
  2. On the Standard toolbar, click the New Query button
  3. Open the Kolo Bank: Data Definition Language (DDL) file
  4. Select the whole content of the document and copy
  5. Right-click inside the Query Editor and click Paste
  6. To execute it, on the main menu, click Query -> Execute
  7. Click inside the Query Editor and press Ctrl + A
  8. Open the Kolo Bank: Tables file
  9. Select the whole content of the document and copy
  10. Access the Query Editor and paste the code
  11. On the main menu, click Query -> Execute
  12. Open the Kolo Bank: Views file
  13. Select the whole content of the document and copy
  14. Access the Query Editor and paste the code
  15. To execute it, on the main menu, click Query -> Execute
  16. Open the Kolo Bank: Triggers file
  17. Select the whole content of the document and copy
  18. Access the Query Editor and paste the code
  19. To execute it, on the main menu, click Query -> Execute
  20. Open the Kolo Bank: Data Manipulation Language (DML) file
  21. Select the whole content of the document and copy
  22. Access the Query Editor and paste the code
  23. To execute it, on the main menu, click Query -> Execute
  24. In the Object Explorer, right-click Databases and click Refresh
  25. Expand Databases and expand KoloBank1
  26. Click inside the Query Editor and press Ctrl + A
    USE KoloBank1;
    GO
    
    SELECT Clients.AccountNumber,
           Clients.FirstName,
           Clients.LastName
    FROM   Accounts.Customers Clients;
    GO
  27. To execute, press F5
  28. 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.

A Simple Subquery

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

    Subquery

  • 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 LearningPractical Learning: Creating a Simple Subquery

  1. 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
  2. To execute, on the SQL Editor toolbar, click the Execute button Execute

    Introducing Sub-Queries

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

A Sub-Query in a SELECT Condition

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 LearningPractical Learning: Using a Sub-Query as a Criterion

  1. 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
  2. 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)
    
  3. Click inside the top section of the Query Editor, press Ctrl + A, and press Delete
Sub-Queries and Functions

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 LearningPractical Learning: Using a Function in a Sub-Query

  1. 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
  2. To execute, right-click in the Query Editor and click Execute
     
    Introducing Subqueries
  3. 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
  4. To execute, press F5
     
    Introducing Subqueries
  5. 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 LearningPractical Learning: Combining Sub-Queries

  1. 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
  2. To execute, press F5
     
    Subquery
  3. 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.

Nesting Sub-Queries
 

Practical LearningPractical Learning: Nesting a Sub-Query

  1. 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
  2. To execute, press F5
     
    Sub-Query
  3. 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 LearningPractical Learning: Using an Expression in a Subquery

  1. 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
  2. Press F5 to execute:
    Sub-Query
  3. Click the top section of the Query Editor and press Ctrl + A
  4. 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
  5. 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)
  6. Click inside the Query Editor, press Ctrl + A, and press Delete
 
 
 

Other Operations on Sub-Queries

   

Logical Operations in 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 LearningPractical Learning: Using IN in a Subquery

  1. 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
  2. To execute, press F5
     
    Sub-Query
  3. Click inside the Query Editor and press Ctrl + A
  4. 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
  5. To execute, press F5
     
    Sub-Query
  6. Click inside the Query Editor and press Ctrl + A
  7. 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
  8. To execute, press F5
     
    Subquery
  9. 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 LearningPractical Learning: Performing Data Entry

  1. To see the list of starting salaries, type:
    USE KoloBank1;
    GO
    
    SELECT ALL *
    FROM   Management.StartingSalaries;
    GO
  2. To execute, press F5
     
    Sub-Query
  3. Click inside the Query Editor and press Ctrl + A
  4. 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
  5. To execute, press F5
  6. Click inside the Query window and press Ctrl + A
  7. 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
  8. 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)
  9. 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 LearningPractical Learning: Performing Data Entry

  1. 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
  2. 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)
  3. Notice that some employees have a salary as NULL.
    Click inside the Query window and press Ctrl + A
  4. 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
  5. 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)
  6. Click inside the Query window and press Ctrl + A
  7. 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
  8. 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)
  9. In the Object Explorer, under KoloBank1, expand Tables
  10. Under Tables, right-click Management.StartingSalaries and click Edit Top 200 Rows
     
    Starting Salaries
  11. Change the StartingSalary of Base to 12.50
  12. Change the StartingSalary of Intern to 14.05

    Starting Salaries
  13. Close the table
  14. Click the top section of the Query window and press Ctrl + A
  15. 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
  16. 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
  17. Click the top section of the Query window and press Ctrl + A
  18. 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
  19. 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)
  20. 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.

Sub-Queries and Joins

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 LearningPractical Learning: Creating a Subquery as an Alternative to a Join

  1. Click inside the Query window and press Ctrl + A to select all
  2. 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
  3. 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)
  4. Click inside the Query window and press Ctrl + A
  5. 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
  6. To execute, press F5
  7. Click inside the Query window and press Ctrl + A
  8. 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
  9. To execute, press F5
  10. Click inside the Query window and press Ctrl + A
  11. 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
  12. 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)
  13. 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:

Creating a Correlated Subquery
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 LearningPractical Learning: Creating a Correlated Sub-Query

  1. 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
  2. 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)
  3. Click inside the Query window and press Ctrl + A
  4. 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
  5. 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)
  6. Click inside the Query window and press Ctrl + A
  7. 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
  8. 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)
  9. Close Microsoft SQL Server
  10. When asked whether you want to save, click No
 
 
   
 

Previous Copyright © 2008-2013 FunctionX Next