File Info
| Exam | Querying Data with Transact-SQL |
| Number | 70-761 |
| File Name | Microsoft.70-761.Train4Sure.2019-02-01.112q.tqb |
| Size | 14 MB |
| Posted | Feb 01, 2019 |
| Download | Microsoft.70-761.Train4Sure.2019-02-01.112q.tqb |
How to open VCEX & EXAM Files?
Files with VCEX & EXAM extensions can be opened by ProfExam Simulator.
Coupon: MASTEREXAM
With discount: 20%
Demo Questions
Question 1
You need to create a database object that meets the following requirements:
- accepts a product identified as input
- calculates the total quantity of a specific product, including quantity on hand and quantity on order
- caches and reuses execution plan
- returns a value
- can be called from within a SELECT statement
- can be used in a JOIN clause
What should you create?
- an extended stored procedure
- a user-defined scalar function
- a user-defined stored procedure that has an OUTPUT parameter
- a temporary table that has a columnstore index
Correct answer: B
Explanation:
User-defined scalar functions are execution plans that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a result set. Furthermore the execution plan is cached and reusable. User-defined scalar functions can also be called from within a SELECT statement and can be used in a JOIN clause. Incorrect Answers:A: Using extended stored procedures is not recommended as they has been deprecated. CLR Integration should be used instead of extended stored procedures.C: Stored procedures cannot be used in a SELECT statement or in a JOIN clause.D: A temporary table is a result set and not a value.References:https://www.c-sharpcorner.com/UploadFile/996353/difference-between-stored-procedure-and-user-defined-functio/ User-defined scalar functions are execution plans that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a result set. Furthermore the execution plan is cached and reusable.
User-defined scalar functions can also be called from within a SELECT statement and can be used in a JOIN clause.
Incorrect Answers:
A: Using extended stored procedures is not recommended as they has been deprecated. CLR Integration should be used instead of extended stored procedures.
C: Stored procedures cannot be used in a SELECT statement or in a JOIN clause.
D: A temporary table is a result set and not a value.
References:
https://www.c-sharpcorner.com/UploadFile/996353/difference-between-stored-procedure-and-user-defined-functio/
Question 2
You need to create an indexed view that requires logic statements to manipulate the data that the view displays.
Which two database objects should you use? Each correct answer presents a complete solution.
- a user-defined table-valued function
- a CRL function
- a stored procedure
- a user-defined scalar function
Correct answer: AC
Explanation:
You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types. You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.
Question 3
You have a database that includes the tables shown in the exhibit.
You need to create a list of all customers, the order ID for the last order that the customer placed, and the date that the order was placed. For customers who have not placed orders, you must substitute a zero for the order ID and 01/01/1990 for the date.
Which Transact-SQL statement should you run?
Correct answer: A
Explanation:
ISNULL Syntax: ISNULL ( check_expression , replacement_value ) author:"Luxemburg, Rosa"The ISNULL function replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression. References: https://msdn.microsoft.com/en-us/library/ms184325.aspx ISNULL Syntax: ISNULL ( check_expression , replacement_value ) author:"Luxemburg, Rosa"
The ISNULL function replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression.
References: https://msdn.microsoft.com/en-us/library/ms184325.aspx
