This page was exported from All The Latest MCTS Exam Questions And Answers For Free Share [ https://www.mctsdump.com ] Export date:Fri Apr 26 0:17:15 2024 / +0000 GMT ___________________________________________________ Title: [New Exam Dumps] Do Not Miss 70-461 Exam Dumps Shared By PassLeader -- 100% Exam Pass Guarantee --------------------------------------------------- New Updated 70-461 Exam Questions from PassLeader 70-461 PDF dumps! Welcome to download the newest PassLeader 70-461 VCE dumps: http://www.passleader.com/70-461.html (168 Q&As) Keywords: 70-461 exam dumps, 70-461 exam questions, 70-461 VCE dumps, 70-461 PDF dumps, 70-461 practice tests, 70-461 study guide, 70-461 braindumps, Querying Microsoft SQL Server 2012 Exam NEW QUESTION 158You have a Microsoft SQL Server database that includes two tables named EmployeeBonus and BonusParameters. The tables are defined by using the following Transact-SQL statements:The tables are used to compute a bonus for each employee. The EmployeeBonus table has a non- null value in either the Quarterly, HalfYearly or Yearly column. This value indicates which type of bonus an employee receives. The BonusParameters table contains one row for each calendar year that stores the amount of bonus money available and a company performance indicator for that year. You need to calculate a bonus for each employee at the end of a calendar year. Which Transact-SQL statement should you use? A.    SELECTCAST(CHOOSE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus * CompanyPerformance)/20, (Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS `Bonus' FROMEmployeeBonus, BonusParametersB.    SELECT "Bonus" =CASE EmployeeBonusWHEN Quarterly=1 THEN (Quarterly * AvailableBonus * CompanyPerformance)/40 WHEN HalfYearly=1 THEN (HalfYearly * AvailableBonus * CompanyPerformance)/20 WHEN Yearly=1 THEN (Yearly * AvailableBonus * CompanyPerformance)/10 ENDFROM EmployeeBonus,BonusParametersC.    SELECTCAST(COALESCE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus * CompanyPerformance)/20, (Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS `Bonus' FROMEmployeeBonus, BonusParametersD.    SELECTNULLIF(NULLIF((Quarterly * AvailableBonus * CompanyPerformance)/40,(HalfYearly * AvailableBonus * CompanyPerformance)/20),(Yearly * AvailableBonus * CompanyPerformance)/10) AS `Bonus' FROMEmployeeBonus, BonusParameters Answer: B NEW QUESTION 159You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements: - Takes an input parameter- Returns a table of values- Can be referenced within a view Which object should you use? A.    inline table-valued functionB.    user-defined data typeC.    stored procedureD.    scalar-valued function Answer: AExplanation:Incorrect answers:Not B: A user-defined data type would not be able to take an input parameter.Not C: A stored procedure cannot be used within a view.Not D: A scalar-valued would only be able to return a single simple value, not a table. NEW QUESTION 160...... Download the newest PassLeader 70-461 dumps from passleader.com now! 100% Pass Guarantee! — http://www.passleader.com/70-461.html NEW QUESTION 162You are developing a Microsoft SQL Server 2012 database for a company. The database contains a table that is defined by the following Transact-SQL statement:You use the following Transact-SQL script to insert new employee data into the table. Line numbers are included for reference only.If an error occurs, you must report the error message and line number at which the error occurred and continue processing errors. You need to complete the Transact-SQL script. Which Transact-SQL segment should you insert at line 06? A.    SELECT ERROR_LINE(), ERROR_MESSAGE()B.    DECLARE @message NVARCHAR(1000),@severity INT, @state INT; SELECT @message = ERROR_MESSAGE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE();RAISERROR (@message, @severity, @state);C.    DECLARE @message NVARCHAR(1000),@severity INT, @state INT; SELECT @message = ERROR_MESSAGE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE();THROW (@message, @severity, @state);D.    THROW; Answer: BExplanation:When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.Reference: TRY...CATCH (Transact-SQL)https://msdn.microsoft.com/en-us/library/ms175976.aspx NEW QUESTION 164You are maintaining a Microsoft SQL Server database. You run the following query:You observe performance issues when you run the query. You capture the following query execution plan:You need to ensure that the query performs returns the results as quickly as possible. Which action should you perform? A.    Add a new index to the ID column of the Person table.B.    Add a new index to the EndDate column of the History table.C.    Create a materialized view that is based on joining data from the ActiveEmployee and History tables.D.    Create a computed column that concatenates the GivenName and SurName columns. Answer: AExplanation:Cost is 53% for the Table Scan on the Person (p) table. This table scan is on the ID column, so we should put an index on it. NEW QUESTION 165...... Download the newest PassLeader 70-461 dumps from passleader.com now! 100% Pass Guarantee! — http://www.passleader.com/70-461.html NEW QUESTION 167You are developing a database in SQL Server 2012 to store information about current employee project assignments. You are creating a view that uses data from the project assignment table. You need to ensure that the view does not become invalid if the schema of the project assignment table changes. What should you do? A.    Create the view by using an account in the sysadmin role.B.    Add a DDL trigger to the project assignment table to re-create the view after any schema change.C.    Create the view in a new schema.D.    Add a DDL trigger to the view to block any changes. Answer: BExplanation:DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.Reference: DDL Triggershttps://technet.microsoft.com/en-us/library/ms190989(v=sql.105).aspx NEW QUESTION 168You are maintaining a Microsoft SQL Server database that stores order information for an online store website. The database contains a table that is defined by the following Transact-SQL statement:You need to ensure that purchase order numbers are used only for a single order. What should you do? A.    Create a new CLUSTERED constraint on the PurchaseOrderNumber column.B.    Create a new UNIQUE constraint on the PurchaseOrderNumber column.C.    Create a new PRIMARY constraint on the PurchaseOrderNumber column.D.    Create a new FOREIGN KEY constraint on the PurchaseOrderNumber column. Answer: BExplanation:You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.Reference: UNIQUE Constraintshttps://technet.microsoft.com/en-us/library/ms191166(v=sql.105).aspx Download the newest PassLeader 70-461 dumps from passleader.com now! 100% Pass Guarantee! 70-461 PDF dumps & 70-461 VCE dumps: http://www.passleader.com/70-461.html (168 Q&As) --------------------------------------------------- Images: --------------------------------------------------- --------------------------------------------------- Post date: 2015-12-14 09:17:15 Post date GMT: 2015-12-14 09:17:15 Post modified date: 2015-12-14 09:17:15 Post modified date GMT: 2015-12-14 09:17:15 ____________________________________________________________________________________________ Export of Post and Page as text file has been powered by [ Universal Post Manager ] plugin from www.gconverters.com