UUS Practice Tests⌕ Search
IT Certifications · 70-461

Exam 70-461: Querying Microsoft SQL Server 2012/2014 Practice Test

Exam 70-461 focuses on writing and modifying Transact-SQL queries for SQL Server 2012 and 2014. Practice topics include filtering and sorting rows, combining result sets, joining tables, defining columns, and using built-in functions. Other questions exercise stored-procedure logic, aggregate calculations, window functions, and choosing indexes for selective searches.

Exam overview

Exam providerMicrosoft
Exam code70-461
CategoryIT Certifications

About the Exam 70-461: Querying Microsoft SQL Server 2012/2014 Exam

Querying SQL Server data requires matching each task to the right T-SQL construct. Joins connect related rows, including self-joins when a table contains relationships such as employee-to-manager. UNION ALL preserves duplicate rows when combining results, while WHERE and HAVING apply filters at different stages of a query. Window functions support calculations across related rows without collapsing detail: ranking functions handle ties, and ordered aggregate windows can produce running totals. Query authors also need to define predictable output with ORDER BY, alter table structures with appropriate data types and defaults, and select built-in functions for values such as the current system date and time. In stored procedures, conditional branching supports actions based on whether a condition is met. Index decisions depend on the search predicate, key columns, and selectivity; an index can help locate matching rows but is not automatically beneficial for every query.

Sample Practice Questions

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

Question 1

A stored procedure should return the current date and time from the SQL Server system clock. Which built-in function is appropriate?

  1. AGETDATE()
  2. BCOUNT(*)
  3. CNEWID()
  4. DSUSER_SNAME()
Show answerCorrect answer: A

GETDATE() returns the current database system timestamp as a datetime value. COUNT counts rows, NEWID generates a unique identifier, and SUSER_SNAME returns a login name.

Question 2

A query needs to combine rows from dbo.CurrentCustomers and dbo.ArchivedCustomers, retaining duplicate rows that appear in both query results. Which set operator has that behavior by default?

  1. AUNION
  2. BUNION ALL
  3. CINTERSECT
  4. DEXCEPT
Show answerCorrect answer: B

UNION ALL appends the results while retaining duplicates. UNION removes duplicate result rows, while INTERSECT and EXCEPT return rows based on set comparisons.

Question 3

Which statement adds a new column named IsActive to an existing table named dbo.Accounts, using bit as its data type and a default value of 1?

  1. AALTER TABLE dbo.Accounts ADD IsActive bit DEFAULT (1);
  2. BUPDATE TABLE dbo.Accounts ADD IsActive bit = 1;
  3. CCREATE COLUMN dbo.Accounts IsActive bit DEFAULT (1);
  4. DALTER COLUMN dbo.Accounts ADD IsActive bit = 1;
Show answerCorrect answer: A

ALTER TABLE ... ADD adds a column to an existing table. A DEFAULT constraint can specify the value used when an insert omits the column; the other statements do not use valid SQL Server syntax for this operation.

Question 4

A query needs to return each row from dbo.Employees together with the name of that employee's manager, who is also stored in dbo.Employees. Which join pattern is appropriate?

  1. AJoin dbo.Employees to itself using separate employee and manager aliases
  2. BUse a cross join between dbo.Employees and dbo.Departments
  3. CUse a full outer join from dbo.Employees to itself without a matching condition
  4. DUse UNION to combine employee names and manager names
Show answerCorrect answer: A

A self-join uses two aliases for the same table, matching an employee's ManagerID to the manager's EmployeeID. This allows a query to retrieve employee and manager attributes from the same table.

Question 5

A query needs the total value of each invoice, calculated as Quantity multiplied by UnitPrice, but the result should include only invoices whose calculated total exceeds 500. Which clause can filter using that calculated expression in the query?

  1. AWHERE Quantity * UnitPrice > 500
  2. BHAVING Quantity * UnitPrice > 500
  3. CORDER BY Quantity * UnitPrice > 500
  4. DGROUP BY Quantity * UnitPrice > 500
Show answerCorrect answer: A

A row-level expression can be tested in WHERE before grouping. HAVING is used to filter groups, while ORDER BY and GROUP BY do not serve as row filters.

Question 6

A query must return each row from dbo.Employees together with a cumulative salary total for that employee's department, ordered by HireDate and EmployeeID. Which expression is suitable for the cumulative total?

  1. ASUM(Salary) OVER (PARTITION BY DepartmentID ORDER BY HireDate, EmployeeID ROWS UNBOUNDED PRECEDING)
  2. BSUM(Salary) GROUP BY DepartmentID ORDER BY HireDate
  3. CSUM(Salary) OVER (GROUP BY DepartmentID)
  4. DSUM(Salary) OVER (PARTITION BY DepartmentID)
Show answerCorrect answer: A

An aggregate used with an OVER clause can calculate a windowed value while retaining individual rows. PARTITION BY restarts the calculation per department, and the ordered frame from the beginning through the current row produces the running total.

Question 7

A query needs to number each employee within their department by salary from highest to lowest, assigning the same rank to employees with equal salaries and leaving gaps after ties. Which function should be used?

  1. AROW_NUMBER()
  2. BRANK()
  3. CDENSE_RANK()
  4. DNTILE(2)
Show answerCorrect answer: B

RANK() assigns equal values the same rank and leaves gaps after ties. DENSE_RANK() does not leave gaps, while ROW_NUMBER() assigns a different sequence number to every row.

How to use this practice test

The free practice bank contains 50 questions, with up to 20 random questions in a session. Work through each query scenario by identifying the required result shape and the T-SQL feature that produces it. Results appear after the session, giving you a chance to revisit missed concepts such as joins, window functions, or filtering.

Preparation tips

  • Write small examples that contrast UNION with UNION ALL, and verify whether duplicate rows should be retained.
  • Practice self-joins using aliases, then test how join type affects employees whose manager row is missing.
  • Compare WHERE and HAVING with grouped queries, and check when an expression can be filtered through a derived table or another query layer.
  • Build window-function examples with tied salaries and ordered dates; inspect how RANK and cumulative SUM behave within partitions.
  • For index scenarios, identify the predicate columns and likely selectivity before deciding whether a nonclustered index could reduce row lookups.
Official sourceOfficial exam page ↗