Home

Intersections and Unions of Records

         

Intersections and Differences in Sets of Records

 

Introduction

Two of the intermediate operations used in SQL consists of finding out whether two tables or lists have common values or records in certain fields, or what records are in one table but not in the other.

Practical LearningPractical Learning: Introducing Intersection of Records

  1. Open the LPM2 file. Select and copy its whole content
  2. Start Microsoft SQL Server and connect
  3. Right-click the name of the server and click New Query
  4. Paste the LPM2 code in the Query Editor
  5. To execute, right-click inside the Query Editor and click Execute
  6. Click inside the Query Editor and press Ctrl + A
  7. To see the list of tenants from the Rockville apartments and the list of tenants from Alexandria apartments, type the following code:
    USE LPM2;
    GO
    SELECT *
    FROM Rentals.Tenants t;
    GO
    SELECT *
    FROM Rentals.Customers c;
    GO
  8. To execute, right-click inside the Query Editor and click Execute. Notice that, although both tables have columns with similar values, the orders of the columns with like-values are not the same on both tables:

Finding Records Intersections

Intersection in Sets of Records

If you have two tables with the same categories of information, you may want to know what records are common in the lists. For example, if you have two list of customers, you may want to know whether some customers are in both tables. That operation is called an intersection. The SQL performs it as done in algebra. That SQL operator is called INTERSECT. Its formula is:

{ <query_specification> | ( <query_expression> ) } 
INTERSECT
{ <query_specification> | ( <query_expression> ) }
Intersection in Sets of Records

The INTERSECT keyword is written between two SQL expressions. The first expression is also referred to as the left expression. The second expression is also referred to as the right expression.

There are rules the expressions must follow:

  • Both expressions must have the same number of columns
  • The data types of the columns in the same positions must be compatible. This means that the data type of the column ColA in position Pn in table A must be compatible with the colum ColB in position Pn of table B. In the same way, the data type of the column ColX in position Pn+1 in table A must be compatible with the colum ColY in position Pn+1 of table B. Compatibilty means that both columns can be integer-based, or decimal-based, or string-based, or date-based

The columns in both expressions don't have to have the same name.

Practical LearningPractical Learning: Finding Records Intersections

  1. If you specify only one column in each expression, if a record in the left expression and another record in the right table have the same values, the value would appear in the result.
    Click inside the Query Editor and press Ctrl + A
  2. To find the intersection of records using one column, type the following:
    USE LPM2;
    GO
    SELECT t.TenantCode
    FROM Rentals.Tenants t
    INTERSECT
    SELECT c.AccountNumber
    FROM Rentals.Customers c;
    GO
  3. To execute, right-click inside the Query Editor and click Execute

    Finding Records Intersections

  4. The result of the INTERSECT operation consists of the names of the columns of the first expression. Therefore, if you want to specify an alias for a column, use the field of the first expression. As an example, change the statement as follows:
    USE LPM2;
    GO
    SELECT t.TenantCode [Tenant Account #]
    FROM Rentals.Tenants t
    INTERSECT
    SELECT c.AccountNumber
    FROM Rentals.Customers c;
    GO
  5. If you specify only one column in each expression, if no record in the left and right expression is the same, the result would be empty.
    Click inside the Query Editor and press Ctrl + A
  6. Type the following:
    USE LPM2;
    GO
    SELECT t.PhoneNumber
    FROM Rentals.Tenants t
    INTERSECT
    SELECT c.Phone
    FROM Rentals.Customers c;
    GO
  7. To execute, right-click inside the Query Editor and click Execute

    Finding Records Intersections

  8. If you specify more than one column in each expression, if all columns in a certain position Pn of both expressions have the same value, the value would appear in the result. The database engine would check the values of columns in position Pn+1 of both expressions and those value must be the same.
    Click inside the Query Editor and press Ctrl + A
  9. Type the following:
    USE LPM2;
    GO
    SELECT t.TenantCode, t.FirstName, t.MaritalStatus
    FROM Rentals.Tenants t
    INTERSECT
    SELECT c.AccountNumber, c.FirstName, c.Status
    FROM Rentals.Customers c;
    GO
  10. To execute, right-click inside the Query Editor and click Execute

    Finding Records Intersections

  11. If you specify more than one column in each expression, if a column in position Pn of the left expression is different from the column in the same position Pn of the right expression, the whole result would be empty.
    Click inside the Query Editor and press Ctrl + A
  12. Type the following:
    USE LPM2;
    GO
    SELECT t.TenantCode, t.FirstName, t.MaritalStatus
    FROM Rentals.Tenants t
    INTERSECT
    SELECT c.AccountNumber, c.FirstName, c.Status
    FROM Rentals.Customers c;
    GO
  13. To execute, right-click inside the Query Editor and click Execute

    Finding Records Intersections

  14. Click inside the Query Editor and press Ctrl + A
  15. You can use functions in the expressions. If you decide to do this To see an example, type the following:
    USE LPM2;
    GO
    SELECT tens.TenantCode [Tenant Account #],
           CONCAT(tens.LastName, N', ', tens.FirstName) [Tenant Name]
    FROM Rentals.Tenants tens
    INTERSECT
    SELECT custs.AccountNumber,
           CONCAT(custs.LastName, N', ', custs.FirstName)
    FROM Rentals.Customers custs;
    GO
  16. To execute, press F5

    Finding Records Intersections

  17. To see more examples, type the following:
    USE LPM2;
    GO
    SELECT t.TenantCode, t.LastName
    FROM Rentals.Tenants t
    INTERSECT
    SELECT c.AccountNumber, c.LastName
    FROM Rentals.Customers c;
    GO
  18. To execute, right-click inside the Query Editor and click Execute
  19. Click inside the Query Editor and press Ctrl + A
  20. To try another example, type the following:
    USE LPM2;
    GO
    SELECT regs.RegistrationNumber, regs.RentStartDate
    FROM Rentals.Registrations regs
    INTERSECT
    SELECT allocs.AllocationID, allocs.DateOccupiedFrom
    FROM Rentals.Allocations allocs;
    GO
  21. To execute, right-click inside the Query Editor and click Execute
  22. Click inside the Query Editor and press Ctrl + A
  23. To see another example, type the following:
    USE LPM2;
    GO
    SELECT regs.RegistrationNumber,
           regs.TenantCode,
           regs.PropertyNumber,
           regs.RentStartDate
    FROM Rentals.Registrations regs
    INTERSECT
    SELECT allocs.AllocationID,
           allocs.AccountNumber,
           allocs.ApartmentNumber,
           allocs.DateOccupiedFrom
    FROM Rentals.Allocations allocs;
    GO
  24. To execute, right-click inside the Query Editor and click Execute

    Finding Records Intersections

  25. Click inside the Query Editor and press Ctrl + A
  26. Instead of using the columns directly, if the expressions include foreign keys whose values are not very clear (such as the case of integers), you can use a join to present more meaningful values. If you decide t doo that To see an example, type the following:
    USE LPM2;
    GO
    SELECT regs.RegistrationNumber,
           CONCAT(tens.LastName, N', ', tens.FirstName) Tenant,
           regs.PropertyNumber,
           regs.RentStartDate
    FROM Rentals.Registrations regs INNER JOIN Rentals.Tenants tens
    ON regs.TenantCode = tens.TenantCode
    INTERSECT
    SELECT allocs.AllocationID,
           CONCAT(custs.LastName, N', ', custs.FirstName),
           allocs.ApartmentNumber,
           allocs.DateOccupiedFrom
    FROM Rentals.Allocations allocs INNER JOIN Rentals.Customers custs
    ON allocs.AccountNumber = custs.AccountNumber;
    GO
  27. To execute, right-click inside the Query Editor and click Execute

    Finding Records Intersections

  28. Click inside the Query Editor and press Ctrl + A
  29. To see an example, type the following:
    USE LPM2;
    GO
    SELECT regs.RegistrationNumber,
           tens.FirstName,
           regs.PropertyNumber,
           regs.RentStartDate
    FROM Rentals.Registrations regs INNER JOIN Rentals.Tenants tens
    ON regs.TenantCode = tens.TenantCode
    INTERSECT
    SELECT allocs.AllocationID,
           custs.FirstName,
           allocs.ApartmentNumber,
           allocs.DateOccupiedFrom
    FROM Rentals.Allocations allocs INNER JOIN Rentals.Customers custs
    ON allocs.AccountNumber = custs.AccountNumber;
    GO
  30. To execute, right-click inside the Query Editor and click Execute

    Finding Records Intersections

  31. Click inside the Query Editor and press Ctrl + A
  32. To view another example, type the following:
    USE LPM2;
    GO
    SELECT regs.RegistrationNumber,
           regs.RegistrationDate,
           regs.PropertyNumber,
           regs.RentStartDate
    FROM Rentals.Registrations regs
    INTERSECT
    SELECT allocs.AllocationID,
           allocs.DateAllocated,
           allocs.ApartmentNumber,
           allocs.DateOccupiedFrom
    FROM Rentals.Allocations allocs;
    GO
  33. To execute, press F5
  34. To get a more meaning display, change the SQL statement as follows:
    USE LPM2;
    GO
    SELECT regs.RegistrationNumber [Registration #],
           Format(regs.RegistrationDate, N'dddd, MMMM dd, yyyy')   [Allocated On],
           N'Prop #: ' + props.PropertyNumber + N', Monthly Rent: ' + FORMAT(props.MonthlyRate, N'C') [Unit Info],
           Format(regs.RentStartDate, N'y') [Property Occupied From]
    FROM Rentals.Registrations regs INNER JOIN Listing.Properties props
    ON regs.PropertyNumber = props.PropertyNumber
    INTERSECT
    SELECT allocs.AllocationID,
           Format(allocs.DateAllocated, N'dddd, MMMM dd, yyyy'),
           N'Prop #: ' + props.PropertyNumber + N', Monthly Rent: ' + FORMAT(props.MonthlyRate, N'C'),
           Format(allocs.DateOccupiedFrom, N'y')
    FROM Rentals.Allocations allocs INNER JOIN Listing.Properties props
    ON allocs.ApartmentNumber = props.PropertyNumber;
    GO
  35. To execute, press F5

    Finding Records Intersections

  36. Click inside the Query window and press Ctrl + A
  37. To see more examples, type the following:
    USE LPM2;
    GO
    SELECT t.TenantCode, t.LastName
    FROM Rentals.Tenants t
    INTERSECT
    SELECT c.AccountNumber, c.LastName
    FROM Rentals.Customers c;
    GO
  38. To execute, right-click inside the Query Editor and click Execute
  39. To see one more example, type the following:
    USE LPM2;
    GO
    SELECT pmts.PaymentNumber, pmts.PaymentDate,
           pmts.RegistrationNumber, pmts.AmountPaid
    FROM Rentals.Payments pmts
    INTERSECT
    SELECT recs.ReceiptID, recs.DateReceiptMade,
           recs.PaymentForAllocationNumber, recs.PaymentAmt
    FROM Rentals.Receipts recs
    ORDER BY pmts.RegistrationNumber;
    GO
  40. To execute, right-click inside the Query Editor and click Execute

    Finding Records Intersections

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

Difference in Sets of Records

Once again, imagine you have two tables that have the same categories of records, such as two lists of customers. You may want to know what records belong to one list and not to the other list. To assist you with this operation, Transact-SQL provides the EXCEPT operator. Its formula is the same as that of INTERSECT:

{ <query_specification> | ( <query_expression> ) } 
EXCEPT
{ <query_specification> | ( <query_expression> ) }

In this case also, the EXCEPT keyword is surrounded by two SQL expressions. The rules are the same for the intersection:

This time also, the names of the columns of the expressions can be different but columns of the first expression would be used for the final result. When creating the resulting list, the database engine compares the records of the left expression to those of the right expression:

Practical LearningPractical Learning: Finding the Difference in Records

  1. To find an intersection of records, type the following:
    USE LPM2;
    GO
    SELECT t.TenantCode
    FROM Rentals.Tenants t
    INTERSECT
    SELECT c.AccountNumber
    FROM Rentals.Customers c;
    GO
  2. To execute, right-click inside the Query Editor and click Execute

    Finding Records Intersections

  3. To apply the exception, change the statement as follows:
    USE LPM2;
    GO
    SELECT t.TenantCode
    FROM Rentals.Tenants t
    EXCEPT
    SELECT c.AccountNumber
    FROM Rentals.Customers c;
    GO
  4. To execute, right-click inside the Query Editor and click Execute

    Finding Records Exceptions

  5. Notice that the resulting values are different.
    To reverse the source and the target expressions, change the statement as follows:
    USE LPM2;
    GO
    SELECT c.AccountNumber
    FROM Rentals.Customers c
    EXCEPT
    SELECT t.TenantCode
    FROM Rentals.Tenants t;
    GO
  6. To execute, press F5

    Finding Records Exceptions

  7. Click inside the Query Editor and press Ctrl + A
  8. Type the following:
    USE LPM2;
    GO
    SELECT t.TenantCode [Account #], t.FirstName [First Name]
    FROM Rentals.Tenants t
    INTERSECT
    SELECT c.AccountNumber, c.FirstName
    FROM Rentals.Customers c;
    GO
  9. To execute, right-click inside the Query Editor and click Execute

    Finding Records Exceptions

  10. Change the statement as follows:
    USE LPM2;
    GO
    SELECT t.TenantCode [Account #], t.FirstName [First Name]
    FROM Rentals.Tenants t
    EXCEPT
    SELECT c.AccountNumber, c.FirstName
    FROM Rentals.Customers c;
    GO
  11. To execute, right-click inside the Query Editor and click Execute

    Finding Records Exceptions

  12. Reverse the source and the target as follows:
    USE LPM2;
    GO
    SELECT c.AccountNumber [Account #], c.FirstName [First Name]
    FROM Rentals.Customers c
    EXCEPT
    SELECT t.TenantCode, t.FirstName
    FROM Rentals.Tenants t;
    GO
  13. To execute, right-click inside the Query Editor and click Execute

    Finding Records Exceptions

  14. Click inside the Query Editor and press Ctrl + A
  15. To view another example, type the following:
    USE LPM2;
    GO
    SELECT regs.RegistrationNumber,
           regs.RegistrationDate,
           regs.PropertyNumber,
           regs.RentStartDate
    FROM Rentals.Registrations regs
    INTERSECT
    SELECT allocs.AllocationID,
           allocs.DateAllocated,
           allocs.ApartmentNumber,
           allocs.DateOccupiedFrom
    FROM Rentals.Allocations allocs;
    GO
  16. To execute, press F5

    Finding Records Intersections

  17. To get the except result, change the statement as follows:
    USE LPM2;
    GO
    SELECT regs.RegistrationNumber [Registration #],
           regs.RegistrationDate   [Allocated On],
           regs.PropertyNumber     [Unit Info],
           regs.RentStartDate      [Property Occupied From]
    FROM Rentals.Registrations regs
    EXCEPT
    SELECT allocs.AllocationID,
           allocs.DateAllocated,
           allocs.ApartmentNumber,
           allocs.DateOccupiedFrom
    FROM Rentals.Allocations allocs;
    GO
  18. To get a more meaning display, change the SQL statement as follows:
    USE LPM2;
    GO
    SELECT regs.RegistrationNumber [Registration #],
           Format(regs.RegistrationDate, N'D')   [Allocated On],
           N'Prop #' + props.PropertyNumber + N', Rate: ' +
    		FORMAT(props.MonthlyRate, N'C') + N'/Month' [Unit Info],
           Format(regs.RentStartDate, N'MMMM yyyy') [Property Occupied From]
    FROM Rentals.Registrations regs INNER JOIN Listing.Properties props
    ON regs.PropertyNumber = props.PropertyNumber
    EXCEPT
    SELECT allocs.AllocationID,
           Format(allocs.DateAllocated, N'D'),
           N'Prop #' + props.PropertyNumber + N', Rate: ' + 
    		FORMAT(props.MonthlyRate, N'C') + N'/Month' [Unit Info],
           Format(allocs.DateOccupiedFrom, N'MMMM yyyy')
    FROM Rentals.Allocations allocs INNER JOIN Listing.Properties props
    ON allocs.ApartmentNumber = props.PropertyNumber;
    GO
  19. To execute, press F5

    Finding Records Exceptions

  20. Click inside the Query window, press Ctrl + A, and press Delete

Uniting Some Records

 

Introduction

Consider the following tables:

USE Exercise1;
GO

DROP TABLE HumanResources.Employees;
GO
DROP TABLE HumanResources.Contractors;
GO

CREATE TABLE HumanResources.Employees
(
    EmployeeNumber nchar(9),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    [Status] nvarchar(20) default N'Employee'
);
GO
CREATE TABLE HumanResources.Contractors
(
    ContractorCode nchar(7),
    Name1 nvarchar(20),
    Name2 nvarchar(20),
    Wage decimal(6, 2),
    [Type] nvarchar(20) default N'Contractor'
);
GO

INSERT INTO HumanResources.Employees(EmployeeNumber,
		FirstName, LastName, HourlySalary)
VALUES(N'2930-4708', N'John', N'Franks', 20.05),
      (N'8274-9571', N'Peter', N'Sonnens', 10.65),
      (N'6359-8079', N'Leslie', N'Aronson', 15.88);
GO
INSERT INTO HumanResources.Contractors(ContractorCode, Name1, Name2, Wage)
VALUES(N'350-809', N'Mary', N'Shamberg', 14.20),
      (N'286-606', N'Chryssa', N'Lurie', 20.26);
GO

Sometimes, either for the sake of comparing records or for preparing them for any operation, you may want to display the records of more than one table. To support the ability to select records of various tables and show them together, you use the UNION operator. The basic formula to follow is:

SELECT WhatField(s) FROM OneTable
UNION
SELECT WhatField(s) FROM AnotherTable;

 There are rules you must follow:

The columns don't have to have the same name. Here is an example of getting a union of all records from both tables:

SELECT * FROM HumanResources.Employees
UNION
SELECT * FROM HumanResources.Contractors;
GO

This would produce:

Uniting ALL Records

If you use the above formula, the records of all SELECTed tables would be included in the result. As an alternative, you can set a condition for the table(s) to exclude some records. You can set a condition on only one table, some tables, or all tables, but each table must have its own condition. Here is an exmple that unites some records of the above employees whose name end with s and the contractors who earn more than 20.00:

SELECT * FROM HumanResources.Employees WHERE LastName LIKE N'%s'
UNION
SELECT * FROM HumanResources.Contractors WHERE Wage >= 20.00;
GO

This would produce:

Uniting ALL Records

Practical LearningPractical Learning: Uniting the Records

  1. To see the records of tenants from Rockville and Alexandria previous databases, type the following code:
    USE LPM2;
    GO
    SELECT * FROM Rentals.Tenants;
    GO
    SELECT * FROM Rentals.Customers;
    GO
  2. To execute, press F5
    UNION
    
    
    
  3. Click inside the Query Editor and press Ctrl + A
  4. To unite the records of the tenants, type the following code:
    USE LPM2;
    GO
    SELECT t.TenantCode, t.FirstName, t.LastName, 
           t.MaritalStatus, t.PhoneNumber, t.EmailAddress
    FROM Rentals.Tenants t
    UNION
    SELECT c.AccountNumber, c.FirstName, c.LastName, c.[Status], c.Phone, c.Email
    FROM Rentals.Customers c;
    GO
  5. Press F5 to execute. This would produce:

    UNION
  6. Click inside the Query Editor and press Ctrl + A
  7. If you want, you can specify the captions you want. Change the code as follows:
    USE LPM2;
    GO
    SELECT r.RegistrationNumber [Regist #],
           r.RegistrationDate   [Regist Date],
           r.TenantCode         [For Account #],
           r.PropertyNumber     [For Prop #],
           r.RentStartDate      [Rent Start On]
    FROM Rentals.Registrations r
    UNION
    SELECT a.AllocationID, a.DateAllocated, a.AccountNumber,
           a.ApartmentNumber, a.DateOccupiedFrom
    FROM Rentals.Allocations a;
    GO
  8. Press F5 to execute

    Uniting Records

  9. Notice that you get 14 records.
    Click inside the Query Editor
  10. Press Ctrl + A and press Delete

Uniting ALL Records

By default, when UNION is used, the database engine arranges the records based on the first column. This means that if the first column is number-based, the records would be sorted in increment based on that column. If the first column is character-based, the list would be given with that column in alphabetical order. As an alternative, you can ask the database engine to include the records as they are made available, that is, the records of the first table, followed by those of the second table, and so on. To give this instruction, add the ALL keyword after UNION. The formula to follow is:

SELECT WhatField(s) FROM OneTable
UNION ALL
SELECT WhatField(s) FROM AnotherTable;

 Here is an example:

SELECT * FROM HumanResources.Employees
UNION ALL
SELECT * FROM HumanResources.Contractors;
GO

This would produce:

Uniting ALL Records

Practical LearningPractical Learning: Uniting ALL Records

  1. To unite all records, add the ALL keyword to the right of UNION:
    USE LPM2;
    GO
    SELECT r.RegistrationNumber [Regist #],
           r.RegistrationDate   [Regist Date],
           r.TenantCode         [For Account #],
           r.PropertyNumber     [For Prop #],
           r.RentStartDate      [Rent Start On]
    FROM Rentals.Registrations r
    UNION ALL
    SELECT a.AllocationID, a.DateAllocated, a.AccountNumber,
           a.ApartmentNumber, a.DateOccupiedFrom
    FROM Rentals.Allocations a;
    GO
  2. Press F5 to execute

    Uniting Records

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

Copying From Many Tables

You can use the ability to copy records in order to get records from two or more tables and add them to a another table.

The formula to follow is:

INSERT INTO TableName
SELECT WhatField(s) FROM OneTable
UNION [ALL]
SELECT WhatField(s) FROM AnotherTable;

Here is an example:

USE LPM1;
GO

INSERT INTO Listing.Apartments
SELECT props.PropertyNumber,
       props.[Address],
       props.UnitNumber,
       props.City,
       props.[State],
       props.ZIPCode,
       props.Bedrooms,
       props.Bathrooms,
       props.MonthlyRate,
       props.SecurityDeposit
FROM Listing.Properties props
UNION
SELECT condos.CondoCode,
       condos.[Address],
       condos.CondoNbr,
       condos.City,
       condos.[State],
       condos.PostalCode,
       condos.Beds,
       condos.Baths,
       condos.Rent,
       condos.SecDepot
FROM Listing.Condominiums condos
UNION
SELECT alex.ApartmentCode,
       alex.[Address],
       alex.ApartNbr,
       alex.City,
       alex.[State],
       alex.ZIP,
       alex.Bedrooms,
       alex.Bathrooms,
       alex.MonthRent,
       alex.InitialDeposit
FROM Listing.AlexandriaApartments alex;
GO

Here is an example that displays the results

USE LPM1;
GO

SELECT aparts.PropertyNumber  N'Prop #',
       aparts.[Address],
       aparts.UnitNumber      N'Apart #',
       aparts.City,
       aparts.[State],
       aparts.ZIPCode         N'ZIP Code',
       aparts.Bedrooms        Beds,
       aparts.Bathrooms       Baths,
       aparts.MonthlyRate     Rent,
       aparts.SecurityDeposit Deposit
FROM Listing.Apartments aparts;
GO
ss F5
UNION


Practical LearningPractical Learning: Updating the Database

  1. Type the following code:
    USE LPM2;
    GO
    /* Change the property number where a record from Alexandria has
       the name property number as an apartment from Rockville */
    UPDATE Listing.Properties
    SET    PropertyNumber = N'3840-0002'
    WHERE (PropertyNumber = N'3840-8262') AND (City = N'Alexandria');
    GO
    UPDATE Listing.Properties
    SET    PropertyNumber = N'6200-0008'
    WHERE (PropertyNumber = N'6200-8048') AND (City = N'Alexandria');
    GO
    UPDATE Listing.Properties
    SET    PropertyNumber = N'3840-0003'
    WHERE (PropertyNumber = N'3840-0683') AND (City = N'Alexandria');
    GO
    UPDATE Listing.Properties
    SET    PropertyNumber = N'6286-0008'
    WHERE (PropertyNumber = N'6286-6408') AND (City = N'Alexandria');
    GO
    -- Harmonize the security deposits based on the rate of each house
    UPDATE Listing.Properties
    SET    SecurityDeposit =  500 WHERE MonthlyRate >= 800;
    GO
    UPDATE Listing.Properties
    SET    SecurityDeposit =  600 WHERE MonthlyRate >= 900;
    GO
    UPDATE Listing.Properties
    SET    SecurityDeposit =  700 WHERE MonthlyRate >= 1000;
    GO
    UPDATE Listing.Properties
    SET    SecurityDeposit =  800 WHERE MonthlyRate >= 1200;
    GO
    UPDATE Listing.Properties
    SET    SecurityDeposit = 1000 WHERE MonthlyRate >= 1500;
    GO
    -- Add to the Rockville table of tenants the missing columns from the Alexandria table of customers
    ALTER TABLE Rentals.Tenants
    ADD DateCreated date;
    GO
    ALTER TABLE Rentals.Tenants
    ADD DateCreated date;
    GO
    ALTER TABLE Rentals.Tenants
    ADD EmergencyName nvarchar(40);
    GO
    ALTER TABLE Rentals.Tenants
    ADD EmergencyPhone nvarchar(20);
    GO
    /* Change the tenants account numbers from Alexandria
       where they are the same as tenants in Rockville */
    UPDATE Rentals.Customers
    SET    AccountNumber = N'292000'
    WHERE  AccountNumber = N'292475';
    GO
    UPDATE Rentals.Customers
    SET    AccountNumber = N'293000'
    WHERE  AccountNumber = N'293759';
    GO
    UPDATE Rentals.Customers
    SET    AccountNumber = N'295000'
    WHERE  AccountNumber = N'295804';
    GO
    UPDATE Rentals.Customers
    SET    AccountNumber = N'524000'
    WHERE  AccountNumber = N'524794';
    GO
    UPDATE Rentals.Customers
    SET    AccountNumber = N'824000'
    WHERE  AccountNumber = N'824857';
    GO
    -- Copy the Alexandria tenants to those in Rockville
    INSERT INTO Rentals(TenantCode, DateCreated, FirstName, LastName,
                        MaritalStatus, NumberOfChildren, PhoneNumber,
    					EmailAddress, EmergencyName, EmergencyPhone)
    SELECT AccountNumber, DateCreated, FirstName, LastName, [Status],
           Children, Phone, Email, EmergencyName, EmergencyPhone
    FROM Rentals.Customers;
    GO
    /* Change the records of the Alexandria registrations
       based on the properties numbers and the accounts numbers
       that were changed earlier */
    UPDATE Rentals.Allocations
    SET    ApartmentNumber = N'3840-0002', AccountNumber = N'295000'
    WHERE  AllocationID = 2;
    GO
    UPDATE Rentals.Allocations
    SET    ApartmentNumber = N'6200-0008', AccountNumber = N'824000'
    WHERE  AllocationID = 5;
    GO
    UPDATE Rentals.Allocations
    SET    ApartmentNumber = N'3840-0003', AccountNumber = N'292000'
    WHERE  AllocationID = 7;
    GO
    UPDATE Rentals.Allocations
    SET    ApartmentNumber = N'6286-0008', AccountNumber = N'293000'
    WHERE  AllocationID = 9;
    GO
    -- Create a new Registrations table to use an identity column
    CREATE TABLE Rentals.Agreements
    (
    	AgreementID      int identity(1, 1) not null,
    	RegistrationDate Date,
    	EmployeeNumber   int, -- Processed By
    	TenantCode       nvarchar(10), -- Processed For
    	PropertyNumber   nvarchar(10) not null,
    	RentStartDate    date
    );
    GO
    -- Add the Rockville regustrations to this table
    INSERT INTO  Rentals.Agreements(RegistrationDate, EmployeeNumber, 
                                    TenantCode, PropertyNumber, RentStartDate)
    SELECT RegistrationDate, EmployeeNumber, TenantCode, PropertyNumber, RentStartDate
    FROM Rentals.Registrations
    GO
    -- Then add the Alxandria registrations to the new table
    INSERT INTO Rentals.Agreements(RegistrationDate, TenantCode, PropertyNumber, RentStartDate)
    SELECT DateAllocated, AccountNumber, ApartmentNumber, DateOccupiedFrom)
    FROM Rentals.Allocations;
    GO
    /* Change the registrations numbers of the Alexandria tenants based on
       the new identity numbers of the Agreements table */
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 11
    WHERE  PaymentForAllocationNumber =  2;
    GO
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 12
    WHERE  PaymentForAllocationNumber =  5;
    GO
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 13
    WHERE  PaymentForAllocationNumber =  7;
    GO
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 14
    WHERE  PaymentForAllocationNumber =  9;
    GO
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 15
    WHERE  PaymentForAllocationNumber = 12;
    GO
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 16
    WHERE  PaymentForAllocationNumber = 15;
    GO
    -- Add the Alexandria payments to the Roclville's
    INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationNumber, AmountPaid, Notes)
    SELECT PaymentAmt, PaymentForAllocationNumber, DateReceiptMade, ReceiptDescription
    FROM Rentals.Receipts;
    GO
    
    
  2. To execute, on the main menu, click Query -> Execute
  3. Close Microsoft SQL Server
  4. When asked whether you want to save, click No

Exercises

   

Lesson Summary Questions

  1. What are the two rules to create a union of tables (Select 2)?
    1. There must be a maximum of 6 tables
    2. The number and the order of the columns must be the same
    3. The names of the tables must be the same
    4. The data types must be compatible
    5. The database must have a clustered index
  2. What is the basic formula to create a union of two tables?
    1. SELECT WhatField(s) FROM OneTable
      UNION
      SELECT WhatField(s) FROM AnotherTable;
    2. WITH UNION
      SELECT WhatField(s) FROM OneTable
      ADD
      SELECT WhatField(s) FROM AnotherTable;
    3. SET UNION ON
      SELECT WhatField(s) FROM OneTable
      JOIN
      SELECT WhatField(s) FROM AnotherTable;
    4. SELECT WhatField(s) FROM OneTable
      UNION WITH
      SELECT WhatField(s) FROM AnotherTable;
    5. ON SELECT WhatField(s) FROM OneTable
      JOIN SELECT WhatField(s) FROM AnotherTable;
      UNION ALL
      
  3. What types of DML operations can be performed in a MERGE statement (Select 3)?
    1. CREATE
    2. SELECT
    3. INSERT
    4. UPDATE
    5. DELETE

Answers

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

Practice Questions

  1. Create

Previous Copyright © 2012-2013 FunctionX Next