querying-tables-with-select.md

Back to assessment | Back to main

Querying Tables with SELECT

1. You write a query to return the Name and Price of all products that have a Category value of 2 or 4. You have used the following SELECT clause:

   SELECT Name, Price
   FROM Production.Product

   Which two of the following WHERE clauses can you use to complete the query correctly?

   • A. WHERE Category IN (2, 4);
   • B. WHERE CATEGORY = 2 AND Category = 4;
   • C. WHERE Category BETWEEN 2 AND 4;
   • D. WHERE Category = 2 OR Category = 4;
   • E. WHERE Category = 2 OR 4;

2. Which character acts as a wildcard when used in the LIKE predicate?

   • A. &
   • B. +
   • C. *
   • D. _
   
   
   Show answer

   _ (underscore)
   
   

3. Which character in a LIKE statement matches exactly one alpha-numeric character?

   • A. _
   • B. &
   • C. +
   • D. *
   
   
   Show answer

   _ (underscore) Any single character
   
   

4. Which T-SQL keyword should you use to remove duplicate rows?

   • A. DISTINCT
   • B. UNION ALL
   • C. WHERE
   • D. JOIN
   
   
   Show answer

   DISTINCT
   
   

5. Which SQL keyword matches a value in a collection when used in a predicate?

   • A. IN
   • B. LIKE
   • C. OR
   • D. BETWEEN
   
   
   Show answer

   IN (Transact-SQL) determines whether a specified value matches any value in a subquery or a list.
   
   

6. You write a Transact-SQL query to list the available sizes for products. Each individual size should be listed only once.

   Which query should you use?

   • A. SELECT TOP 1 Size FROM Production.Product ORDER BY Size;
   • B. SELECT DISTINCT Size FROM Production.Product;
   • C. SELECT ALL Size FROM Production.Product;
   • D. SELECT Size FROM Production.Product;
   
   
   Show answer

   SELECT DISTINCT Size FROM Production.Product;
   
   

7. You write a Transact-SQL query to return the SalesOrderID, OrderDate, and Amount fields for the first 10 sales orders in order of SalesOrderID. Which three of the following queries returns the required results?

   • A.

     SELECT SalesOrderID, OrderDate, Amount
     FROM Sales.SalesOrder
     ORDER BY SalesOrderID
     OFFSET 0 ROW FETCH NEXT 10 ROW ONLY;

   • B.

     SELECT TOP 10 SalesOrderID, OrderDate, Amount
     FROM Sales.SalesOrder
     ORDER BY SalesOrderID;

   • C.

     SELECT TOP 10 PERCENT SalesOrderID, OrderDate, Amount
     FROM Sales.SalesOrder
     ORDER BY SalesOrderID;

   • D.

     SELECT SalesOrderID, OrderDate, Amount
     FROM Sales.SalesOrder
     ORDER BY SalesOrderID ASC;

   • E.

     FROM Sales.SalesOrder
     ORDER BY SalesOrderID
     OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

8. What are two ways that the results of a sorted query can be limited?

   • A. BOTTOM N PERCENT
   • B. TOP N PERCENT
   • C. TOP N
   • D. BOTTOM N

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

---

Back to top | Back to assessment | Back to main