Skip to content

Latest commit

 

History

History
124 lines (83 loc) · 3.37 KB

using-set-operators.md

File metadata and controls

124 lines (83 loc) · 3.37 KB

Back to assessment | Back to main

Using Set Operators

  1. INTERSECT is similar to what type of join?

    • A. inner
    • B. cross
    • C. outer
    • D. full

  2. The Sales.SalesOrderDetail table contains a row for every line item sold, and includes a ProductID column to identify the product that was ordered. The Production.Product table contains a row for every product, and includes a Discontinued column where the value 1 indicates that the product has been discontinued.

    What result does the following query return?

    SELECT DISTINCT ProductID
FROM Sales.SalesOrderDetail
EXCEPT
SELECT ProductID
FROM Production.Product
WHERE Discontinued = 1;
    • A. All products that have been sold and all products that have been discontinued.
    • B. All non-discontinued products that have been sold.
    • C. All non-discontinued products that have never been sold.
    • D. All discontinued products that have been sold.

    Show answer All non-discontinued products that have been sold.

  3. You write a query that returns the set of products that are available in the color 'Red' or the size 'XL' by combining the results of two queries. Any products that are available in both red and size XL should be included as a duplicate row in the resultset.

    Select the appropriate missing keyword.

    SELECT ProductID, Name, Color, Size
FROM Production.Product
WHERE Color = 'Red'
__________
SELECT ProductID, Name, Color, Size
FROM Production.Product
WHERE Size = 'XL'
ORDER BY ProductID;
    • A. UNION
    • B. UNION ALL
    • C. EXCEPT
    • D. INTERSECT

    Show answer UNION ALL

  4. You write the following Transact-SQL query that returns the CustomerName, StreetAddress, City, and PostalCode columns from the Sales.BillingAddress table:

    SELECT CustomerName, StreetAddress, City, PostalCode
FROM Sales.BillingAddress;

    You have also written the following query to retrieve the same fields from the Sales.ShippingAddress table:

    SELECT CustomerName, StreetAddress, City, PostalCode
FROM Sales.ShippingAddress;

    What set operator can you use to combine the results of these queries and return only rows for customers whose billing address is the same as their shipping address?

    • A. UNION ALL
    • B. UNION
    • C. INTERSECT
    • D. EXCEPT

    Show answer INTERSECT

  5. What does an EXCEPT query return?

    • A. all distinct rows that appear in the first result set but not in the second result set
    • B. all rows that appear in the first result set but not in the second result set
    • C. all distinct rows that appear in the second result set but not in the first result set
    • D. all rows that appear in the second result set but not in the first result set

Source: 'Transact-SQL' published on Microsoft Virtual Academy


------

Back to top | Back to assessment | Back to main