Microsoft Querying Microsoft SQL Server 2012/2014 - 070-461무료 덤프문제 풀어보기
You have a SQL Server database that contains all of the sales data for your company.
You need to create a query that returns the customers who represent the top five percent of the total actual sales.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
You need to create a query that returns the customers who represent the top five percent of the total actual sales.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
정답: A
설명: (Fast2test 회원만 볼 수 있음)
You use Microsoft SQL Server 2012 database to develop a shopping cart application.
You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output.
Which Transact-SQL operator should you use?
You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output.
Which Transact-SQL operator should you use?
정답: B
설명: (Fast2test 회원만 볼 수 있음)
You are developing an SQL Server database for an automobile manufacturer. The manufacturer maintains the list of vehicles sold and vehicles that have been recalled. The tables are shown below:
You have the following Transact-SQL code. Line numbers are included for reference only.
You must update the VehiclesToRecall table with the list of vehicles that were recalled in 2014. You must maximize the performance of the operation.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
You have the following Transact-SQL code. Line numbers are included for reference only.
You must update the VehiclesToRecall table with the list of vehicles that were recalled in 2014. You must maximize the performance of the operation.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
정답:
Reference:
MERGE (Transact-SQL)
https://msdn.microsoft.com/en-us/library/bb510625.aspx
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee.
Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.
On which column in the Employee table should you create a Foreign Key constraint that references a different table in the database?
Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)
Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.
On which column in the Employee table should you create a Foreign Key constraint that references a different table in the database?
정답: A
설명: (Fast2test 회원만 볼 수 있음)
You develop a SQL Server database for an online theater sales company. The database includes the following four tables:
The following diagram shows the tables and their relationships:
The following diagram shows the tables and their relationships:
정답:
You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.
You need to improve the performance of the view by persisting data to disk. What should you do?
You need to improve the performance of the view by persisting data to disk. What should you do?
정답: D
설명: (Fast2test 회원만 볼 수 있음)
You administer a Microsoft SQL Server instance that will support several databases.
You need to ensure that the organization serial numbers can be stored in every new database created. You also need to ensure that the serial numbers are six alphanumeric characters and have the same structure across every database.
What should you do?
You need to ensure that the organization serial numbers can be stored in every new database created. You also need to ensure that the serial numbers are six alphanumeric characters and have the same structure across every database.
What should you do?
정답: D
설명: (Fast2test 회원만 볼 수 있음)
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 previous year.
Which Transact-SQL query should you use?
You need to create a report that displays the profits made by each territory for each year and its previous year.
Which Transact-SQL query should you use?
정답: C
설명: (Fast2test 회원만 볼 수 있음)
You are a database developer of a Microsoft SQL Server database.
The database contains a table named Instructor that has the following definition:
You are designing a new table named Course that has the following definition:
You need to ensure that the InstructorId column in the Course table contains only values that exist in the InstructorID column of the Instructor table.
Which Transact-SQL statement should you use?
The database contains a table named Instructor that has the following definition:
You are designing a new table named Course that has the following definition:
You need to ensure that the InstructorId column in the Course table contains only values that exist in the InstructorID column of the Instructor table.
Which Transact-SQL statement should you use?
정답: A
설명: (Fast2test 회원만 볼 수 있음)
You develop a Microsoft SQL Server Database. The database contains a table named Status that is defined by the following Transact-SQL statement:
There are thousands of rows in the Status table, with significant duplication of data in the Color column. Ninety percent of the rows in the table have Color="Red", and the remaining 10 percent have Color="Green".
You want to normalize the Color information in this table. You create a table named Colors that is defined by the following DDL:
You populate the new Colors table by using the following Transact-SQL statement:
INSERT Colors (ColorName) SELECT DISTINCT Color FROM Status
You need to ensure that the following requirements are met:
* The Status table uses only colors that exist in the Colors table.
* Data redundancy in the Status table is reduced.
* Data integrity is enforced during the normalization process.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segment from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
There are thousands of rows in the Status table, with significant duplication of data in the Color column. Ninety percent of the rows in the table have Color="Red", and the remaining 10 percent have Color="Green".
You want to normalize the Color information in this table. You create a table named Colors that is defined by the following DDL:
You populate the new Colors table by using the following Transact-SQL statement:
INSERT Colors (ColorName) SELECT DISTINCT Color FROM Status
You need to ensure that the following requirements are met:
* The Status table uses only colors that exist in the Colors table.
* Data redundancy in the Status table is reduced.
* Data integrity is enforced during the normalization process.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segment from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
정답:
Explanation:
First update the new column ColorID, and drop the old Column Color.
Add a check constraint on the new ColorID column, and finally add a foreign key constraint.
You use Microsoft SQL Server to develop a database application.
You create a stored procedure named usp_calculategrowth. The stored procedure modifies rows and can result in several different exceptions.
You need to ensure that when the stored procedure is executed, the following requirements are met:
Which six 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.)
You create a stored procedure named usp_calculategrowth. The stored procedure modifies rows and can result in several different exceptions.
You need to ensure that when the stored procedure is executed, the following requirements are met:
Which six 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.)
정답:
Explanation:
Box 1: BEGIN TRY
A TRY...CATCH construct consists of two parts: a TRY block and a CATCH block. When an error condition is detected in a Transact-SQL statement that is inside a TRY block, control is passed to a CATCH block where the error can be processed.
A TRY blockstarts with the BEGIN TRY statement and ends with the END TRY statement.
Box 2: Exec usp_calculategrowth
Box 3: END TRY
Box 4: BEGIN CATCH
A TRY block must be followed immediately by a CATCH block. A CATCH block starts with the BEGIN CATCH statement andends with the END CATCH statement.
Box 5: EXEC xp_logevent ...
xp_logevent logs a user-defined message in the SQL Server log file and in the Windows Event Viewer. xp_logevent can be used to send an alert without sending a message to the client.
Incorrect:
Not RAISERROR: RAISERROR generates an error message and initiates error processing for the session.
The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct. New applicationsshould use THROW instead.
Box 6: END CATCH
References:
https://msdn.microsoft.com/en-us/library/ms186244.aspx
https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx
You 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?
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
설명: (Fast2test 회원만 볼 수 있음)