Certification Practice Test Sample Questions For
Querying Data with Microsoft Transact-SQL (MS-20761)

QuickStart is now offering you sample questions for Querying Data with Microsoft Transact-SQL (MS-20761). Whether you are deciding which exam to sign up for, or simply want to practice the materials necessary to complete certification for this course, we have provided a practice test to better aid in certification. 100% of the questions are real test questions from a recent version of the Querying Data with Microsoft Transact-SQL (MS-20761) exam.


Arrow

SQL: Advanced Querying (SQL-QueryAdv)

Enroll now today and get 30% off using discount code PRACTICE30 at checkout.

 

Querying Data with Microsoft Transact-SQL (MS-20761) Sample Exam Questions

proceed to the next question. Correct answers will be displayed when you complete exam.
Module 1
Module 1

Introduction to MS SQL Server

1

SQL Server includes 5 System Databases. Which one of the following is used only for Reporting Services?

A. Master
Incorrect.
B. Model
Incorrect.
C. Msdb
Incorrect.
D. None of the above
Correct!
2

Among 4 different editions of SQL Server, which of the following has support for Always on High Availability?

A. Enterprise,
Correct!
B. Standard,
Incorrect.
C. Business Intelligence
Incorrect.
D. None of the above
Incorrect.
3

Which authentication method can be used to log on to SQL Server?

A. Windows Authentication
Incorrect.
B. SQL Server Authentication
Incorrect.
C. Both A And B
Correct!
D. None of the above
Incorrect.


Back To Top
 
 
Module 2
Module 2

Introduction to T-SQL Querying

1

SQL Server Database Engine Processes Select Statement in a logical manner. Choose the best answer that describes the correct Logical Query Processing Order.

2

From the following T-SQL Elements select the one that can include a Predicate:

A. Where Clause
Incorrect.
B. Having Clause
Incorrect.
C. Join Conditions
Incorrect.
D. All the Above
Correct!
3

Which of the following Statements are True with regards to Categories of T-SQL?



Back To Top
 
 
Module 3
Module 3

Writing Select Queries

1

Consider the following Query: Select unit price, qty, (unit price*qty) From Sales. OrderDetails;
The Results appear as follows:

example

Choose the best answer which describes the error in the above Results:

2

You have company departments in five countries. You have the following query for the Human Resource Database:
SELECT DISTINCT DeptName, Country from HumanResources.Departments;
The Query Returns the following:

example

How many rows would be returned?

A. 7
Incorrect.
B. 6
Correct!
C. 4
Incorrect.
D. 3
Incorrect.
3

Which of the following statements are correct regarding T-SQL Case Expressions?



Back To Top
 
 
Module 3
Module 4

Querying Multiple Tables

1

Choose which of the following join types description is true?

2

What is the purpose of using Self Joins? Chose the best answer/s below:

3

Consider the following Query:

“From T1 Left Outer Join t2 ON t1.col=t2.col”

Choose the correct answer that best describes the above query:



Back To Top
 
 
Module 3
Module 5

Sorting and Filtering Data

1

The main purpose of order by clause is to?

2

Let’s assume you are filtering data in the where clause? Which of the following statement/s is/are true?

3

Chose the answer below which states the error in the following Query:

SELECT TOP (5) ordereid, custid, orderdate From Sales. Orders



Back To Top
 
 
Module 3
Module 6

Working with SQL Server Data Types

1

In Certain scenarios we must convert from one data type to another But, there are important reasons for data type conversion. Listed below are some options, Chose the answer/s which you think is correct:

2

Collation is a collection of properties that determine several aspects of character data. Which property is/are not a property of Collation?

A. Character Length
Correct!
B. Character Set
Incorrect.
C. Sort Order
Incorrect.
D. None of the above
Incorrect.
3

This question comprises of three parts. First part is about date functions, second part about Date and Time Parts Functions, and third part will focus on date and time difference Functions. For each part, one question with 4 options will be given. You to have to choose the option which best answers the question:


Part 1: Date Functions:


*Following Function is used to get system dates:

A. DateName
Incorrect.
B. GetDate/GetUTCDate
Correct!
C. All the Above
Correct!
D. None of the above.
Incorrect.

Part 2: Date and Time Parts Function:


*Following function is used to get Date and Time Parts:

A. DATEPART
Incorrect.
B. DateName
Correct!
C. All the Above
Correct!
D. None of the above.
Incorrect.

Part 3: Date and time difference functions:


*Following function is used to get date and time difference:

A. DATEFROMPARTS
Incorrect.
B. DATEDIFF/DATEDIFF_BIG
Correct!
C. All the Above
Incorrect.
D. None of the above.
Incorrect.


Back To Top
 
 
Module 3
Module 7

Using DML To Modify Data

1

Which of the following statements are true about Select Into clause:

2

Choose all the statements which are correct about Merge clause:

3

This question comprises of three parts. First part is about date functions, second part about Date and Time Parts Functions, and third part will focus on date and time difference Functions. For each part, one question with 4 options will be given. You to have to choose the option which best answers the question:


Part 1: Date Functions:


*Following Function is used to get system dates:

A. DateName
Incorrect.
B. GetDate/GetUTCDate
Correct!
C. All the Above
Correct!
D. None of the above.
Incorrect.

Part 2: Date and Time Parts Function:


*Following function is used to get Date and Time Parts:

A. DATEPART
Incorrect.
B. DateName
Correct!
C. All the Above
Correct!
D. None of the above.
Incorrect.

Part 3: Date and time difference functions:


*Following function is used to get date and time difference:

A. DATEFROMPARTS
Incorrect.
B. DATEDIFF/DATEDIFF_BIG
Correct!
C. All the Above
Incorrect.
D. None of the above.
Incorrect.


Back To Top
 
 
Module 3
Module 8

Using Built-In Functions

1

Scalar functions in SQL Server can be defined as:

2

In addition to data type conversions, SQL Server provides functions for conversion or replacement of NULL. Which function is used to replace NULL input with another value?

A. ISNULL
Incorrect.
B. COALESCE
Incorrect.
C. Both A and B
Correct!
D. None of the above
Incorrect.


Back To Top
 
 
Module 3
Module 9

Grouping and Aggregating Data

1

Which clause/s is/are processed after Group By clause?

A. From
Incorrect.
B. Where
Incorrect.
C. Having
Correct!
D. None of the above
Incorrect.
2

Which of the following statement/s is/are true about Aggregate functions?

3

Answer the following two questions:


1) Which clause only operates on Groups and not detail rows?


A. Having
Incorrect.
B. Group By
Correct!
C. All the Above
Incorrect.
D. None of the above.
Incorrect.

2) Which clause controls which rows are available for the next phase of the query?


A. Having
Incorrect.
B. DateName
Correct!
C. All the Above
Correct!
D. None of the above.
Incorrect.

Part 3: Date and time difference functions:


*Following function is used to get date and time difference:

A. DATEFROMPARTS
Incorrect.
B. DATEDIFF/DATEDIFF_BIG
Correct!
C. All the Above
Incorrect.
D. None of the above.
Incorrect.
-->


Back To Top
 
 
Module 2
Module 10

Using Subqueries

1

Subqueries can either be:

A. Scalar.
Incorrect.
B. Multi-valued
Incorrect.
C. Table-valued
Incorrect.
D. All the above
Correct!
2

Choose all the correct statements regarding Inner and Sub queries:

3

Correlated subqueries cannot be executed separately from the outer query because of:

A. Performance Issues
Incorrect.
B. Testing Complexity
Incorrect.
C. Debugging Complexity
Incorrect.
D. B and C
Correct!


Back To Top
 
 
Module 3
Module 11

Using Table Expressions:

1

Which of the following statements best describe views?

2

When flexibility of input is preferred, Table Valued Functions are more useful then views Because:

3

Choose all statements below which are correct about CTE (Common Table Expressions)?



Back To Top
 
Module 3
Module 12

Using Set Operators:

1

Your company have two databases: HR. Employees and Sales. Customers. You want to return Country, Region and city from both tables with the help of following Query:
SELECT country, region, city FROM HR. Employees

Answer

SELECT country, region, city FROM Sales. Customers;

Your main objective is to get city, region and country from both tables but, making sure Duplicate rows are eliminated. From the options below select the option that will eliminate duplicate rows and can be written between above two queries:

A. UNION ALL
Incorrect.
B. UNION
Correct!
C. SUM () Function
Incorrect.
D. None of the above
Incorrect.
2

Your company have two databases: Sales. Customers and Production. Suppliers. You want to get data for country and city, from both tables, with the help of following query:
SELECT country, city FROM SALES.CUSTOMERS

ANSWER

SELECT country, city FROM PRODUCTION.SUPPLIERS;

Your main objective is to get country and city from both tables which contains distinct records. Which of the following options from below can cater the requirements and can be written between the two queries:

A. UNION ALL
Correct!
B. UNION
Incorrect.
C. INTERSECT
Correct!
D. None of the Above
Incorrect.
3

Following statement contains queries about Joins and Apply Operators. Choose all those which are correct:



Back To Top
 
Module 3
Module 13

Using Window Ranking, Offset and Aggregate Functions:

1

In SQL windowing operations, which of the following statements are false?

2

Choose all the answers that best describes SQL windowing:

3

Which of the following queries are example of Window Offset function:



Back To Top
 
Module 3
Module 14

Using Window Ranking, Offset and Aggregate Functions:

1

In the elements of Pivoting, there are 3 phases: Select the one which is not a phase:

2

We can use Group by with ROLLUP, CUBE and GROUPING SETS. Select the answer which best describes both.



Back To Top
 
 
Module 3
Module 15

Executing Stored Procedures

1

This question contains three parts. Choose all answers which are correct



Back To Top
 
 
Module 3
Module 16

Programming with T-SQL

1

Which of the following Batch statements are valid:

2

Select all the answer that describes the error in the following T-SQL variables declaration and procedure execution :
Line 1:DECLARE Numrows INT=3, catid INT

Line 2:EXEC Production.ProdsByCategory

Line 3:@Numrows= numrows, @catid=catid

3

You want to populate a table by creating 27 new rows. Before you create the rows, you need to check that the table exists. From the following T-SQL keywords, choose the one that you will NOT need to use.

A. While
Correct!
B. IF
Incorrect.
C. BEGIN
Incorrect.
D. All of the above.
Incorrect.


Back To Top
 
Module 3
Module 17

Implementing Error Handling

1

Following statements describes Error Handling in T-SQL . Choose all those which are not correct

2

You have the following T-SQL script:
Line 1:BEGIN TRY
Line 2:INSERT INTO HumanResources.PossibleSkills(Skill Name, Category)
Line 3:VALUES ('Associate Consultant', 'IT Professional');
Line 4:END TRY
Line 5:DECLARE @prefix AS NVARCHAR(50) = 'There has been an error: ';
Line 6:BEGIN CATCH
Line 7:PRINT @prefix + ERROR_MESSAGE();
Line 8:THROW;
Line 9:END CATCH;
Line 10:GO
This code will not compile and execute. Select all the options that will execute and compile this code.

3

You are working on a project in which you are writing some error handling in a T-SQL script. If a problem arises you want to raise and error with a severity of 20. What option below will you choose to raise and error with 20 Severity.

A. Throw
Incorrect.
B. Catch
Incorrect.
C. Raise Error
Correct!
D. None of the above.
Incorrect.


Back To Top
 
Module 2
Module 18

Implementing Transactions

1

Part 1: Which of the following statement/s about BEGIN TRANSACTION is/are not true?

Part 2: Choose all the correct options which best describe COMMIT TRANSACTIONS.

Part 3: Consider the following Transaction:

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.SimpleOrders(custid, empid, orderdate)
VALUES (68,9,'2006-07-12');
INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty)
VALUES (1, 2,15.20,20);
COMMIT TRANSACTION
END TRY
Should the above transaction be committed or rolled back ?

Part 4:ROLLBACK statement undoes all modifications made in the transaction by reverting the data to the state it was in at the beginning of the transaction. Which of the following is not true about ROLLBACK statement?

Part 5:Following statements are about Implementing Transactions in T-SQL. You have to decide which of them is correct. You can choose all, none, one or two correct answers:

2

Consider the following Transaction below and select all those options that are correct.

LINE1:BEGIN TRY

Line 2: BEGIN TRANSACTION

Line 3: INSERT INTO SALES.ORDERS

Line 4: INSET INTO SALES.ORDERDETAILS

Line 5: COMMIT TRANSACTION

Line 6:END TRY

LINE 7:BEGIN CATCH

LINE 8: SELECT ERROR_NUMBER()

LINE 9: ROLLBACK TRANSACTION

LINE 8: END CATCH

Which of the following options are not correct about this transaction? :

3

Select from the description below, what category of transaction does it falls into:

Individual data modification statements (for example, INSERT, UPDATE, and DELETE) submitted separately from other commands are automatically wrapped in a transaction by SQL Server. These single-statement transactions are automatically committed when the statement succeeds or are automatically rolled back when the statement encounters a runtime error.

A. Auto commit Transactions
Correct!
B. Explicit Transactions
Incorrect.
C. Implicit Transactions
Incorrect.
D. None of the above
Incorrect.


Back To Top
 

Practice Exam - Querying Data with Microsoft Transact-SQL

$99.00

More Information:

  • Learning Style: On Demand
  • Learning Style: Practice Exam
  • Difficulty: Beginner
  • Course Duration: 1 Hour
  • Course Info: Download PDF
  • Certificate: See Sample

Need Training for 5 or More People?

Customized to your team's need:

  • Annual Subscriptions
  • Private Training
  • Flexible Pricing
  • Enterprise LMS
  • Dedicated Customer Success Manager

Outline

Reviews

Write Your Own Review
Only registered users can write reviews. Please Sign in or create an account

Hit button to validate captcha