Microsoft 70-461 Exam Practice Questions (P. 5)

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You develop a database for a travel application. You need to design tables and other database objects.
You need to store media files in several tables.
Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently.
What should you do?

Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You develop a database for a travel application. You need to design tables and other database objects.
You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats.
What should you do?

You are a database developer of a Microsoft SQL 2012 Server database.
You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID.
A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?

You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.
The view has the following definition:

You need to ensure that users can update only the phone numbers by using this view.
What should you do?

You develop a Microsoft SQL Server database that contains tables named Employee and Person.
The tables have the following definitions:

Users are able to use single INSERT statements or INSERT...SELECT statements into this view.
You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.
Which Transact-SQL statement should you use?
A.


CREATE TRIGGER TrgVwEmployee -

ON VwEmployee -

FOR INSERT -

AS -

BEGIN -
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber)
SELECT Id, EmployeeNumber FROM inserted

END -
B.


CREATE TRIGGER TrgVwEmployee -

ON VwEmployee -

INSTEAD OF INSERT -

AS -

BEGIN -
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber)
SELECT Id, EmployeeNumber FROM inserted

END -


CREATE TRIGGER TrgVwEmployee -
C.

ON VwEmployee -

INSTEAD OF INSERT -

AS -

BEGIN -
DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25),
@PersonID
INT, @EmployeeNumber NVARCHAR(15)
SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName,
@EmployeeNumber = EmployeeNumber

FROM inserted -
INSERT INTO Person(Id, FirstName, LastName)
VALUES(@ID, @FirstName, @LastName)
INSERT INTO Employee(PersonID, EmployeeNumber)
VALUES(@PersonID, @EmployeeNumber

END -
D.


CREATE TRIGGER TrgVwEmployee -

ON VwEmployee -

INSTEAD OF INSERT -

AS -

BEGIN -
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName FROM VwEmployee
INSERT INTO Employee(PersonID, EmployeeNumber)
SELECT Id, EmployeeNumber FROM VwEmployee

END -