Microsoft-Transition Your MCTS on SQL Server 2008 to MCSA SQL Server 2012, Part 1 Set 1

Question - 15 Maximum mark : - 26

Home
All Exams
Microsoft-Transition Your MCTS on SQL Server 2008 to MCSA SQL Server 2012, Part 1 Online Mock Tests
Microsoft-Transition Your MCTS on SQL Server 2008 to MCSA SQL Server 2012, Part 1 Set 1

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 ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2. Which Transact-SQL query should you use?

SELECT StudentCode as Code, RANK ( ) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode ✘ SELECT Id, Name, Marks, DENSE_RANK () OVER (ORDER BY Marks DESC) AS Rank FROMStudentMarks ✘ SELECT StudentCode as Code,DENSE_RANK () OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode ✘ SELECT StudentCode as Code,NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode ✔ SELECT StudentCode AS Code,Marks AS ValueFROM (SELECT StudentCode, Marks AS Marks, RANK () OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

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?

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 ✘ SELECT Id, Name, Marks, DENSE_RANK () OVER (ORDER BY Marks DESC) AS RankFROM StudentMarks ✘ SELECT StudentCode as Code, DENSE_RANK () OVER (ORDER BY AVG (Marks) DESC) AS ValueFROM StudentMarks GROUP BY StudentCode ✘ SELECT StudentCode as Code, NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS ValueFROM StudentMarks GROUP BY StudentCode ✔ 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 ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition: You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use?

CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobiieNumber) - - Create Audit Records ✘ CREATE TRIGGER TrgPhoneNumberChangeON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber from inserted) OR EXISTS (SELECT MobileNumber FROM inserted) - - Create Audit Records ✘ CREATE TRIGGER TrgPhoneNumberChangeON Customers FOR UPDATE AS IF COLUMNS_CHANGED (HomeNumber, MobileNumber) - - Create Audit Records ✘ CREATE TRIGGER TrgPhoneNumberChangeON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks: Joins 8 tables that contain up to 500,000 records each. Performs aggregations on 5 fields. The view is frequently used in several reports. You need to improve the performance of the reports. What should you do?

Convert the view into a table-valued function. ✘ Convert the view into a Common Table Expression (CTE). ✘ Convert the view into an indexed view. ✘ Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporarytable. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You administer a Microsoft SQL Server database that supports a banking transaction management application. You need to retrieve a list of account holders who live in cities that do not have a branch location. Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

SELECT AccountHolderID FROM AccouritHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster) SELECT AccountHolderIDFROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster) SELECT AccountHolderlDFROM AccouncHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster] SELECT AccountHolderIDFROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You develop a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerId . You need to create a query that meets the following requirements:

SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CuscomerlD = Orders.CustomerId ✘ SELECT CustomerName, OrderDateFROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerId ✘ SELECT CustomerName, OrderDateFROM Customers CROSS JOIN Orders ON Customers.CustomerId = Orders.CustomerId ✘ SELECT CustomerName, OrderDateFROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

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

Scalar user-defined function ✘ Inline user-defined function ✘ Table-valued user-defined function ✘ Stored procedure ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table. You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:

CREATE SEQUENCE CustomerSequence AS int START WITH 0 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALOE FOR CustomerSequence DROP SEQUENCE CustomerSequence ✘ CREATE SEQUENCE CustomerSequence AS intSTART WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence ✘ CREATE SEQUENCE CustomerSequence AS intSTART WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence ✘ CREATE SEQUENCE CustomerSequence AS intSTART WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100 CYCLE UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

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?

UPDATE TOP (10) BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0) ✘ UPDATE BlogEntrySET Summary - CAST(N' This is in a draft stage' as nvarchar(max)) WHERE Id IN (SELECT TOP (10) Id FROM B1ogEntry ORDER BY EntryDateTime DESC) ✘ UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM (SELECT TOP (10)Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID ✘ UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', 0, 0) WHERE Id IN (SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Prev Next

Congratulations

you have successfully completed the test

Back to Question View Result

Detail Form

We will send your result on your email id and phone no. please fill detail

Name

Email ID Note: Report will be send to the above Email ID

Phone No

Microsoft-Transition Your MCTS on SQL Server 2008 to MCSA SQL Server 2012, Part 1 Set 1

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner