Microsoft 70-461 Exam Questions Instant Access

Question 1

You have a database that contains a table named Customer. The customer table contains a column named LastName that has a column definition of varchar(50).

An application named App1 reads from the table frequently.

You need to change the column definition to nvarchar(100). The solution must minimize the amount of time it takes for App1 to read the data.

Which statement should you execute?

AOption A

BOption B

COption C

DOption D

Answer : C

To change the data type of a column in a SQL Server (or Microsoft access) table , usethe following syntax:

ALTER TABLE table_name

ALTER COLUMN column_name datatype

References: https://www.w3schools.com/SQl/sql_alter.asp

Question 2

You develop a Microsoft SQL Server 2012 database.

You need to create and call a stored procedure that meets the following requirements:

* Accepts a single input parameter for CustomerID.

* Returns a single integer to the calling application.

Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

ACREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @CustomerRating = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN
GO

BEXECUTE dbo.GetCustomerRating 1745

CDECLARE @CustomerRatingByCustomer INT
DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745,
@CustomerRatingByCustomer

DCREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN @Result
GO

EDECLARE @CustomerRatingByCustomer INT
EXECUTE dbo.GetCustomerRating
@CustomerID = 1745,
@CustomerRating = @CustomerRatingByCustomer OUTPUT

FCREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT
AS
DECLARE @Result INT
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURNS @Result
GO

Answer : A, E

Question 3

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?

ASELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode

BSELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank
FROM StudentMarks

CSELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS
Value
FROM StudentMarks
GROUP BY StudentCode

DSELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode

ESELECT 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

FSELECT 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

GSELECT 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

HSELECT 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

Answer : F

Question 4

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?

Aviews

Btable partitions

Ctable-valued functions

Dstored procedures

Answer : A

Question 5

You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button.)

You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure.

You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specified by the @Count parameter.

The solution must NOT use BEGIN, END, or DECLARE statements.

Part of the correct Transact-SQL statement has been provided in the answer area. Complete the Transact-SQL statement

ACREATE PROCEDURE usp_Customers @Count int
SELECT TOP(@Count)
Customers.LastName
FROM Customers
ORDER BY Customers.LastName

BCREATE PROCEDURE usp_Customers #Count int
SELECT TOP(Count)
Customers.LastName
FROM Customers
ORDER BY Customers.LastName

Answer : A

Question 6

Answer : B

The size of a column with the DATE format is 3 bytes fixed.

Incorrect Answers:

G: The DATETIME data type storage size is 8 bytes.

H: datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

I: The 26 DATETIMEOFFSET data type storage size is 10 bytes.

References: https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server-2017

Question 7

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button).

You need to create a query for a report. The query must meet the following requirements:

* NOT use object delimiters.

* Return the most recent orders first.

* Use the first initial of the table as an alias.

* Return the most recent order date for each customer.

* Retrieve the last name of the person who placed the order.

* Return the order date in a column named MostRecentOrderDate that appears as the last column in the report.

The solution must support the ANSI SQL-99 standard.

Which code segment should you use?

To answer, type the correct code in the answer area.

ASELECT C.LastName, MAX(O.OrderDate) AS MostRecentOrderDate
FROM Customers AS C INNER JOIN Orders AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.LastName
ORDER BY MostRecentOrderDate DESC

BSELECT C.LastName, MAX(O.OrderDate) AS MostRecentOrderDate
FROM Customers AS C INNER JOIN Orders AS O
ON C.CustomerID
GROUP BY C.LastName
ORDER BY MostRecentOrderDate DESC

Answer : A

Microsoft Querying Microsoft SQL Server 2012/2014 70-461 Exam Questions