UUS Practice Tests⌕ Search
IT Certifications · 70-761

Exam 70-761: Querying Data with Transact-SQL Practice Test

Exam 70-761: Querying Data with Transact-SQL concerns retrieving and shaping relational data with SQL Server queries. The available practice material samples row filtering and ordering, distinct values, computed expressions, aggregate filtering, set operations, and data-type conversion. It also touches on date-range predicates and scalar operators used to produce precise results from tables.

Exam overview

Exam providerMicrosoft
Exam code70-761
CategoryIT Certifications

About the Exam 70-761: Querying Data with Transact-SQL Exam

Working with Transact-SQL requires understanding how query clauses interact and how their placement affects results. You may need to choose between WHERE, which filters input rows, and HAVING, which filters groups after aggregation; use ORDER BY with a computed alias; or distinguish UNION from UNION ALL when combining result sets. Other useful skills include limiting rows after sorting, excluding NULL values, writing inclusive date-range conditions, and converting text safely with TRY_CONVERT. These decisions support routine database work such as retrieving recent events, preparing department summaries, and returning only records that meet application parameters. Pay attention to result-set behavior: sorting determines which rows qualify as the first or most recent, while duplicate handling and NULL treatment can change what a query returns.

Sample Practice Questions

Review a selection from this exam's published US Practice Tests practice bank.

Question 1

A query must return the first 20 rows from dbo.Products after sorting by ProductID in ascending order. Which syntax is appropriate in SQL Server?

  1. ASELECT FIRST 20 ProductID, ProductName FROM dbo.Products ORDER BY ProductID;
  2. BSELECT TOP (20) ProductID, ProductName FROM dbo.Products ORDER BY ProductID;
  3. CSELECT ProductID, ProductName FROM dbo.Products LIMIT 20 ORDER BY ProductID;
  4. DSELECT ProductID, ProductName FROM dbo.Products FETCH 20 ORDER BY ProductID;
Show answerCorrect answer: B

SQL Server uses TOP to limit the number of returned rows. ORDER BY defines which rows are first; without an ordering, the selected subset is not predictably ordered.

Question 2

Which query returns each distinct Country value from dbo.Customers, excluding NULL values?

  1. ASELECT DISTINCT Country FROM dbo.Customers WHERE Country IS NOT NULL;
  2. BSELECT Country FROM dbo.Customers WHERE Country IS DISTINCT;
  3. CSELECT UNIQUE Country FROM dbo.Customers WHERE Country <> NULL;
  4. DSELECT Country FROM dbo.Customers GROUP BY *;
Show answerCorrect answer: A

DISTINCT removes duplicate Country values, while IS NOT NULL excludes rows with no Country value. The other choices use unsupported or incorrect syntax, or do not express the required filtering.

Question 3

A SELECT statement computes a value for each row and assigns it the alias ExtendedCost. Which clause should refer to ExtendedCost to sort the final result by that computed value?

  1. AFROM
  2. BWHERE
  3. CORDER BY
  4. DGROUP BY
Show answerCorrect answer: C

ORDER BY can refer to a SELECT-list alias and controls the ordering of the final result. The alias is not available to the earlier logical processing stages such as WHERE.

Question 4

A table contains an integer column Quantity. Which expression returns 1 when Quantity is at least 10 and 0 otherwise?

  1. AIIF(Quantity >= 10, 1, 0)
  2. BCOALESCE(Quantity >= 10, 1, 0)
  3. CNULLIF(Quantity, 10)
  4. DTRY_CONVERT(int, Quantity >= 10)
Show answerCorrect answer: A

IIF evaluates a Boolean condition and returns its second argument when true or its third argument when false. Here those values are 1 and 0.

Question 5

A query returns one row per department with its average salary. Which clause filters out departments whose average salary is below 60000?

  1. AWHERE AVG(Salary) >= 60000
  2. BHAVING AVG(Salary) >= 60000
  3. CORDER BY AVG(Salary) >= 60000
  4. DON AVG(Salary) >= 60000
Show answerCorrect answer: B

HAVING filters groups after aggregation, so it can test AVG(Salary). WHERE filters individual input rows and cannot directly use an aggregate function in this way.

Question 6

A query uses UNION ALL to combine two compatible result sets. What happens to rows that appear in both result sets?

  1. AThey are returned once after duplicate removal
  2. BThey are returned as many times as they occur in the inputs
  3. CThey cause a syntax error
  4. DThey are returned only if every column is non-NULL
Show answerCorrect answer: B

UNION ALL concatenates the result sets without removing duplicates, so repeated rows remain repeated. Duplicate elimination is performed by UNION, not UNION ALL.

Question 7

An application receives a date as text and needs to convert it to the date type, returning NULL if the text is not a valid date. Which expression is designed for this conversion?

  1. ACAST(DateText AS date)
  2. BTRY_CONVERT(date, DateText)
  3. CCONVERT_IMPLICIT(date, DateText)
  4. DDATEPART(date, DateText)
Show answerCorrect answer: B

TRY_CONVERT attempts the requested conversion and returns NULL when the conversion fails. CAST and CONVERT ordinarily raise an error for an invalid conversion.

How to use this practice test

The free US Practice Tests practice session draws up to 20 random questions from the 50-question bank. Answer them to check your understanding of the sampled Transact-SQL query patterns; results appear after the session. Use missed or uncertain items to identify concepts to revisit, such as aggregate filters, ordering, conversion, or duplicate handling.

Preparation tips

  • Write paired examples using WHERE and HAVING, and note whether each condition filters source rows or grouped results.
  • Practice TOP with ORDER BY, including ascending and descending sorts, so row limits return the intended records.
  • Compare UNION with UNION ALL on sample data containing duplicate rows; verify how each affects the combined result.
  • Test NULL-sensitive predicates and distinct-value queries, and distinguish ordinary conversion from TRY_CONVERT when invalid text should produce NULL.
  • Use inclusive date ranges with supplied boundary values and confirm that both endpoints are returned.
Official sourceOfficial exam page ↗