070-457 試験問題を無料オンラインアクセス

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

You create a view named VwEmployee as shown in the following Transact-SQL statement.

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 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
• 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
• C.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
• 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

You administer a Microsoft SQL Server 2012 database. You provide temporary securityadmin access to User1 to the database server. You need to know if User1 adds logins to securityadmin. Which server-level audit action group should you use?

• A.SERVER_STATE_CHANGE_GROUP
• B.SUCCESSFUL_LOGIN_GROUP
• C.SERVER_PRINCIPAL_IMPERSONATION_GROUP
• D.SERVER_ROLE_MEMBER_CHANGE_GROUP

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

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.MiddleName
• B.EmployeeNum
• C.JobTitle
• D.FirstName
• E.DepartmentID
• F.ReportsToID
• G.LastName
• H.EmployeeID
• I.DateHired

You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores. You discover performance issues when large amounts of data are written to tables under heavy system load. You need to limit the number of cores that handle I/O. What should you configure?

• A.Processor affinity
• B.I/O affinity
• C.Lightweight pooling
• D.Max worker threads

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. You need to retrieve the students who scored the highest marks for each subject along with the marks. Which Transact-SQL query should you use?

• A.SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks
• B.SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
• C.SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
• D.SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• E.SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
• F.SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• G.SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• H.SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1