View on GitHub

DP-080: Querying Data with Microsoft Transact-SQL

Companion material and resources for the course 'Endpoint Administrator' delivery by Ricardo Cabral

Back to assessment Back to main

3 - Querying Multiple Tables with Joins

  1. The HumanResources.Employee table contains the following columns:
    • EmployeeID (a unique identifier for each employee)
    • FirstName (the employee’s first name)
    • LastName (the employee’s last name)
    • ManagerID (the EmployeeID of the employee’s manager)

You want to write a query that returns FirstName and LastName for each employee as well as FirstName and LastName for the employee’s manager.

Which type of join should you use?

  1. What are the two standards used to define the JOIN syntax?
  1. What does a Cartesian product consist of?
    • A. all unmatched values in the first table
    • B. every possible combination of rows
    • C. all rows in the first table that match a second table
    • D. all rows from both tables in one result
  2. You write a query to generate test data. You want to generate a list of sales orders in which every customer has ordered every product.

What kind of join should you use?

  1. You write a query that returns a list of all sales employees that have taken sales orders. Employees who have not taken sales orders should not be included in the results.
  Select the appropriate join type to complete the following query.
  SELECT e.FirstName, e.LastName, s.Amount
  FROM HumanResources.Employee AS e
  ________________ Sales.SalesOrder AS s ON s.SalesPersonID = e.EmployeeID;

Show answer INNER JOIN

  1. When a column is selected in a join where the value cannot be determined from the source table, what value is returned?

Source: ‘Transact-SQL’ published on Microsoft Virtual Academy

Back to top Back to assessment Back to main