Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 - 070-457 Exam Practice Test
You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
Status information must be logged to a status table.
If the status table does not exist at the beginning of the batch, it must be created.
Which object should you use?
Status information must be logged to a status table.
If the status table does not exist at the beginning of the batch, it must be created.
Which object should you use?
Correct Answer: C
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:
Id is the Primary Key.
You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries
based on the values in EntryDateTime. Which Transact-SQL statement should you use?
Id is the Primary Key.
You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries
based on the values in EntryDateTime. Which Transact-SQL statement should you use?
Correct Answer: A
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance. The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?
Correct Answer: D
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You administer a Microsoft SQL Server 2012 database. You need to convert the database to a contained database. You also need to ensure that all users are converted to contained users. Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Build List and Reorder:
Build List and Reorder:
Correct Answer:
Explanation:
--Burgos - YES
Reference: http://msdn.microsoft.com/en-us/library/ff929071.aspx
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its preceding year. Which Transact-SQL query should you use?
Correct Answer: B
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You use Microsoft SQL Server 2012 to develop a database application. You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9) )
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. Which three Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9) )
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. Which three Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:
Correct Answer:
You develop a database application for a university. You need to create a view that will be indexed that
meets the following requirements:
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:
meets the following requirements:
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:
Correct Answer:
Explanation:
Verified answer as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms187956.aspx
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button).
You need to create a query for a report. The query must meet the following requirements:
NOT use object delimiters.
Return the most recent orders first.
Use the first initial of the table as an alias.
Return the most recent order date for each customer.
Retrieve the last name of the person who placed the order.
Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.
The solution must support the ANSI SQL-99 standard.
Which code segment should you use?
To answer, type the correct code in the answer area.
You need to create a query for a report. The query must meet the following requirements:
NOT use object delimiters.
Return the most recent orders first.
Use the first initial of the table as an alias.
Return the most recent order date for each customer.
Retrieve the last name of the person who placed the order.
Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.
The solution must support the ANSI SQL-99 standard.
Which code segment should you use?
To answer, type the correct code in the answer area.
Correct Answer: B
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages. You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile. You need to ensure that AgentPortalUser can send email messages. What should you do?
Correct Answer: A
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012. You
attempt to add the [Corpnet\User1] login to the database. However, you receive the following error
message:
"User already exists in current database."
You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the
original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?
attempt to add the [Corpnet\User1] login to the database. However, you receive the following error
message:
"User already exists in current database."
You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the
original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?
Correct Answer: D
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You administer three Microsoft SQL Server 2008 R2 instances. Database mirroring is configured in High-Safety mode with Automatic Failover between the following three servers:
SQL1 is the Principal server.
SQL2 is the mirror server.
SQL3 is the witness server.
You need to upgrade SQL1 and SQL2 to SQL Server 2012. You need to ensure that downtime is minimized during the upgrade. Which six actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Build List and Reorder:
SQL1 is the Principal server.
SQL2 is the mirror server.
SQL3 is the witness server.
You need to upgrade SQL1 and SQL2 to SQL Server 2012. You need to ensure that downtime is minimized during the upgrade. Which six actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Build List and Reorder:
Correct Answer:
Explanation:
--Burgos - NO
Verified answer as correct.
Reference: http://msdn.microsoft.com/en-us/library/bb677181.aspx
You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
Correct Answer: B,C
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).