70-761 Exam
Top Exact 70-761 faq Tips!
Exam Code: 70-761 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Querying Data with Transact-SQL (beta)
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-761 Exam.
Q1. DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You query a database that includes two tables: Project and Task. The Project table includes the following columns:
Task level is defined using the following rules:
You need to determine the task level for each task in the hierarchy.
Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
Answer:
Explanation:
Box 1: SELECT CAST (NULL AS INT) AS ParentTaskID, etc.
This statement selects all tasks with task level 0.
The ParentTaskID could be null so we should use CAST (NULL AS INT) AS ParentTaskID.
Box 2: UNION
We should use UNION and not UNION ALL as we do not went duplicate rows. UNION specifies that multiple result sets are to be combined and returned as a single result set.
Incorrect: Not UNION ALL: ALL incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.
Box 3, Box 4, Box 5:
These statements select all tasks with task level >0.
References:
https://msdn.microsoft.com/en-us/library/ms180026.aspx
Q2. You have a database that stored information about servers and application errors. The database contains the following tables.
Servers
Errors
You need to return all error log messages and the server where the error occurs most often.
Which Transact-SQL statement should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q3. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You create a table named Customer by running the following Transact-SQL statement:
You must insert the following data into the Customer table:
You need to ensure that both records are inserted or neither record is inserted. Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Answer: A
Explanation:
With the INSERT INTO..VALUES statement we can insert both values with just one statement. This ensures that both records or neither is inserted.
References: https://msdn.microsoft.com/en-us/library/ms174335.aspx
Q4. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You create a table named Customer by running the following Transact-SQL statement:
You must insert the following data into the Customer table:
You need to ensure that both records are inserted or neither record is inserted. Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation:
As there are two separate INSERT INTO statements we cannot ensure that both or neither records is inserted.
Q5. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that tracks orders and deliveries for customers in North America. The database contains the following tables:
Sales.Customers
Application.Cities
Sales.CustomerCategories
The company’s development team is designing a customer directory application. The application must list customers by the area code of their phone number. The area code is defined as the first three characters of the phone number.
The main page of the application will be based on an indexed view that contains the area and phone number for all customers.
You need to return the area code from the PhoneNumber field. Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Answer: A
Explanation:
As the result of the function will be used in an indexed view we should use schemabinding. References: https://sqlstudies.com/2014/08/06/schemabinding-what-why/
Q6. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that tracks orders and deliveries for customers in North America. The database contains the following tables:
Sales.Customers
Application.Cities
Sales.CustomerCategories
The company’s development team is designing a customer directory application. The
application must list customers by the area code of their phone number. The area code is defined as the first three characters of the phone number.
The main page of the application will be based on an indexed view that contains the area and phone number for all customers.
You need to return the area code from the PhoneNumber field. Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation:
The variable max, in the line DECLARE @areaCode nvarchar(max), is not defined.
Q7. DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You query a database that includes two tables: Project and Task. The Project table includes the following columns:
Task level is defined using the following rules:
You need to determine the task level for each task in the hierarchy.
Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
Answer:
Explanation:
Box 1: SELECT CAST (NULL AS INT) AS ParentTaskID, etc.
This statement selects all tasks with task level 0.
The ParentTaskID could be null so we should use CAST (NULL AS INT) AS ParentTaskID.
Box 2: UNION
We should use UNION and not UNION ALL as we do not went duplicate rows. UNION specifies that multiple result sets are to be combined and returned as a single result set.
Incorrect: Not UNION ALL: ALL incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.
Box 3, Box 4, Box 5:
These statements select all tasks with task level >0.
References:
https://msdn.microsoft.com/en-us/library/ms180026.aspx
Q8. Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You create a table named Products by running the following Transact-SQL statement:
You have the following stored procedure:
You need to modify the stored procedure to meet the following new requirements:
- Insert product records as a single unit of work.
- Return error number 51000 when a product fails to insert into the database.
- If a product record insert operation fails, the product information must not be permanently written to the database.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation:
A transaction is correctly defined for the INSERT INTO ..VALUES statement, and if there is an error in the transaction it will be caught ant he transaction will be rolled back. However, error number 51000 will not be returned, as it is only used in an IF @ERROR = 51000 statement.
Note: @@TRANCOUNT returns the number of BEGIN TRANSACTION statements that
have occurred on the current connection.
References: https://msdn.microsoft.com/en-us/library/ms187967.aspx
Q9. DRAG DROP
You have a database that includes the following tables:
You need to create a list of all customer IDs and the date of the last order that each customer placed. If the customer has not placed any orders, you must return the date January 1, 1900. The column names must be CustomerID and LastOrderDate.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
Answer:
Explanation:
Box 1: SELECT..COALESCE…
The COALESCE function evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
Box 2: ..LEFT OUTER JOIN..
The LEFT JOIN (LEFT OUTER JOIN) keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. A customer might have no orders so the right table must be allowed have a NULL value.
Box 3: ON c.custid = o.custid
We JOIN on the custID column, which is available in both tables. Box 4: GROUP BY c.custid
References:
https://technet.microsoft.com/en-us/library/ms189499(v=sql.110).aspx http://www.w3schools.com/sql/sql_join_left.asp
Q10. DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You are developing a database to track customer orders. The database contains the following tables: Sales.Customers, Sales.Orders, and Sales.OrderLines. The following table describes the columns in Sales.Customers.
The following table describes the columns in Sales.Orders.
The following table describes the columns in Sales.OrderLines.
You need to create a function that accepts a CustomerID as a parameter and returns the following information:
- all customer information for the customer
- the total number of orders for the customer
- the total price of all orders for the customer
- the average quantity of items per order
How should you complete the function definition? To answer, drag the appropriate Transact-SQL segment to the correct locations. Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Answer:
Explanation:
Box1: RETURNS TABLE
The function should return the following information:
- all customer information for the customer
- the total number of orders for the customer
- the total price of all orders for the customer
- the average quantity of items per order
Box 2: COUNT
The function should return the total number of orders for the customer.
Box 3: SUM
The function should return the total price of all orders for the customer.
Box 3. AVG
The function should return the average quantity of items per order.
Box 4: GROUP BY
Need to use GROUP BY for the aggregate functions.
References: https://msdn.microsoft.com/en-us/library/ms186755.aspx