SQL exam 70-461 questions and answers part 5

MS-SQL exam 70-461 questions and answers dumps

QUESTION 41

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:

Ÿ Future modifications to the table definition will not affect the applications' ability to access data.

Ÿ The new object can accommodate data retrieval and data modification.

Ÿ You need to achieve this goal by using the minimum amount of changes to the applications. What should you create for each application?

A. Synonyms

B. Common table expressions

C. Views

D. Temporary tables

 

 

QUESTION 42

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:

Ÿ Future modifications to the table definition will not affect the applications' ability to access data.

Ÿ The new object can accommodate data retrieval and data modification.

Ÿ You need to achieve this goal by using the minimum amount of changes to the existing applications.

What should you create for each application?

A. views

B. table partitions

C. table-valued functions

D. stored procedures
 

QUESTION 43

You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:

1.       Returns a result set based on supplied parameters.

2.       Enables the returned result set to perform a join with a table.

Which object should you use?

A. Inline user-defined function

B. Stored procedure

C. Table-valued user-defined function

D. Scalar user-defined function

Correct Answer: Below

QUESTION 44

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?

A. Scalar user-defined function

B. Inline user-defined function

C. Table-valued user-defined function

D. Stored procedure

Correct Answer: Below

QUESTION 45

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:

Ÿ Takes an input variable

Ÿ Returns a table of values

Ÿ Cannot be referenced within a view

Which object should you use?

A. Scalar-valued function

B. Inline function

C. User-defined data type

D. Stored procedure

Correct Answer: Below

QUESTION 46

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 a create a unique constraint?

A. DateHired

B. DepartmentID

C. EmployeelD

D. EmployeeNum

E. FirstName

F. JobTitle

G. LastName

H. MiddleName

I. ReportsToID

Correct Answer: Below

QUESTION 47

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 use an identity specification to include a seed of 1,000

and an increment of 1?

A. DateHired

B. DepartmentID

C. EmployeelD

D. EmployeeNum

E. FirstName

F. JobTitle

G. LastName

H. MiddleName

I. ReportsToID

Correct Answer: Below

QUESTION 48

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 Primary Key constraint for this table?

A. DateHired

B. DepartmentID

C. EmployeelD

D. EmployeeNum

E. FirstName

F. JobTitle

G. LastName

H. MiddleName

I. ReportsToID

Correct Answer: Below

QUESTION 49

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. DateHired

B. DepartmentID

C. EmployeelD

D. EmployeeNum

E. FirstName

F. JobTitle

G. LastName

H. MiddleName

I. ReportsToID

Correct Answer: Below

QUESTION 50

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 a create a self-reference foreign key constraint?

A. DateHired

B. DepartmentID

C. EmployeelD

D. EmployeeNum

E. FirstName

F. JobTitle

G. LastName

H. MiddleName

I. ReportsToID

Correct Answer:  

41	42	43	44	45	46	47	48	49	50
C	A	C	D	D	D	C	C	C	I

Hints:

Unique constraint
                EmployeeNum

self-reference foreign key constraint

ReportsToID

Primary Key constraint for this table?
Or
a seed of 1,000 and an increment of 1
or
Foreign Key constraint that references a different table

                EmployeelD