70-761 Sample Questions & Answers
Free Querying Data with Transact-SQL practice questions with worked answers and explanations. See how the ExamJungle simulator prepares you — then jump into the full test.
Launch the full 70-761 simulator →Showing 6 of 12 free samples.
- Question 1
You have been tasked with changing the current information for a specific column in a table. You will make use of an UPDATE Transact-SQL statement to achieve your goal.
You want to specify the name of this column.
Solution: You include the SET argument.
Has the requirement been satisfied?
Show answer & explanation
Correct answer: B
Explanation: The SET argument specifies the list of column or variable names to be updated, columnjiame Is a column that contains the data to be changed, expression Is a variable, literal value, expression, or a subselect statement (enclosed with parentheses) that returns a single value. docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql">References: https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql
- Question 2
A database in your organization contains two tables, named Development and Assignment. The Development table contains the Developments, DevelopmentName, StartTime, EndTime, and userID columns. The Assignment table contains the Assignments, AssignmentName, ParentAssignmentlD, Developments, StartTime, EndTime, and userID columns.
You have been tasked with finding each assignment’s owner using a Transact-SQL statement that returns an assignment’s owner if there is one present. In the event that an assignment is not owned, but is linked with a development where an owner is present, the owner of the development should be returned. Instances that do not meet this criteria should return a value of -1.
You need to create a Transact-SQL statement that meets the requirements.
Solution: You include the COALESCE expression in your Transact-SQL statement.
Has the requirement been satisfied?
Show answer & explanation
Correct answer: A
Explanation: COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql">References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql
- Question 3
You have a Microsoft SQL Server database that contains the table shown in the exhibit below.
You need to create a report that lists the CustomerName for all customers and the OrderDate for the orders that they may have placed. The customer has not placed any orders yet, only the CustomerName must be listed.
Which Transact-SQL query should you use? To answer, drag the appropriate Transact-SQL segment to the correct locations in the answer area. You may ned to drag the splut bar between panes or scroll to view content.
Show answer & explanation
Correct answer: A
Reference: Reference: http://msdn.microsoft.com/en-us/library/ms177634.aspx
- Question 4
Your organization’s Sales department has two tables named Clients and Purchases.
The Sales department requires an inventory of all client IDs and the date of the last purchase made by each client. This info should be recorded in two columns, named ClientID and LatestPurchaseDate.
A specified date has to be returned for clients that have not made any purchases.
Your Transact-SQL statement has to include a specific type of JOIN.
Solution: You include a LEFT OUTER JOIN.
Has the requirement been satisfied?
Show answer & explanation
Correct answer: A
Explanation: The LEFT JOIN (LEFT OUTER JOIN) keyword returns all rows from the left table (tablel), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. A client might have no purchases so the right table must be allowed have a NULL value. www.w3schools.com/sql/sql Join_left.asp">References: http://www.w3schools.com/sql/sqljoin_left.asp
- Question 5
You are required to substitute three subqueries named SQ1, SQ2, and SQ3 with named result sets or temporary tables.
The result set of SQ1 has to make use of the execution scope of a SELECT statement. Other session users have to see the result set of SQ2 prior to it being removed. Other statements in the same session should have access to the result set of SQ3. However, the result set of SQ3 should be invisible to other sessions.
You have to make sure that a suitable subquery replacement is used for SQ2.
Which of the following is the option you should use?
Show answer & explanation
Correct answer: C
Explanation:
There are two types of temporary tables: local and global. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server. technet.microsoft.com/en-us/library/ms186986.aspx">References: https://technet.microsoft.com/en-us/library/ms186986.aspx - Question 6
Your company has a database that includes the Clients, Towns, and Clientclassifications tables. The database monitors purchases and shipping for American clients. All of these tables have system versioning configured.
You have been tasked with writing a query that retrieves data for analyzing the effects of discounts on credit limits. The main purpose of the analysis is to ascertain the average credit limit for every standard discount percentage, from 0% to 4%.
Which of the following should be included in your Transact-SQL statement?
Show answer & explanation
Correct answer: D
Explanation:
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. technet.microsoft.com/en-us/library/ms177410(v=sql. 105).aspx">References: https://technet.microsoft.com/en-us/library/ms177410(v=sql. 105).aspx
Ready for the real thing?
The full 70-761 simulator has every exam-style question, timed mode, and instant scoring.