70-461 Free Practice Test

- (Exam Topic 2)
You 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:
<>>

1. A. SELECTCAST(CHOOSE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus * CompanyPerformance)/20,(Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS ‘Bonus’ FROMEmployeeBonus, BonusParameters
2. B. 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)/10ENDFROM EmployeeBonus,BonusParameters
3. C. SELECTCAST(COALESCE((Quarterly * AvailableBonus * CompanyPerformance)/40, (HalfYearly * AvailableBonus * CompanyPerformance)/20,(Yearly * AvailableBonus * CompanyPerformance)/10) AS money) AS ‘Bonus’ FROMEmployeeBonus, BonusParameters
4. D. SELECTNULLIF(NULLIF((Quarterly * AvailableBonus * CompanyPerformance)/40,(HalfYearly * AvailableBonus * CompanyPerformance)/20),(Yearly * AvailableBonus * CompanyPerformance)/10) AS ‘Bonus’ FROMEmployeeBonus, BonusParameters

Correct Answer: B

- (Exam Topic 2)
You create a view based on the following statement:
< ><>>>>< >

1. A. Option A
2. B. Option B
3. C. Option C
4. D. Option D

Correct Answer: C

- (Exam Topic 1)
You use Microsoft SQL Server to develop a database application.
Your application sends data to a VARCHAR(50) variable named @var.
You need to write a Transact-SQL statement that will return information on a successful or unsuccessful cast to an integer in a table.
Which Transact-SQL statement should you run?
< ><>< >

1. A. Option A
2. B. Option B
3. C. Option C
4. D. Option D

Correct Answer: B
TRY_PARSE returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server. Use TRY_PARSE only for converting from string to date/time and number types.
References: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-parse-transact-sql?view=sql-server-2017

- (Exam Topic 1)
You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers.
The view has the following definition:
<>

1. A. Alter the vie
2. B. Use the EXPAND VIEWS query hint along with each SELECT statement.
3. C. Create an INSTEAD OF UPDATE trigger on the view.
4. D. Drop the vie
5. E. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
6. F. Create an AFTER UPDATE trigger on the view.

Correct Answer: B
Reference: http://msdn.microsoft.com/en-us/library/ms187956.aspx

- (Exam Topic 2)
You generate a daily report according to the following query:
<>>< ><>< >

1. A. Option A
2. B. Option B
3. C. Option C
4. D. Option D

Correct Answer: A