Microsoft Querying Microsoft SQL Server 2012/2014 - 70-461 FREE EXAM DUMPS QUESTIONS & ANSWERS]

Question 1

You use a Microsoft Azure SQL Database instance named ContosoDb. ContosoDb contains a table named Customers that has existing records.
The Customers table has columns named Id and Name.
You need to create a new column in the Customer table named Status that allows null values and sets the value of the Status column to Silver for all existing records.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the anwer area and arrange them in the correct order.

Discussion 0

Correct Answer:

Explanation:
NULL or NOT NULL specifies whether the column can accept null values. Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified or if the table is empty. NOT NULL can be specified for computed columns only if PERSISTED is also specified. If the new column allows null values and no default is specified, the new column contains a null value for each row in the table. If the new column allows null values and a default definition is added with the new column, WITH VALUES can be used to store the default value in the new column for each existing row in the table.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017

Question 2

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.

A. SELECT 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 B. SELECT 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

Discussion 0

Correct Answer: B Vote an answer

Question 3

You use a Microsoft Azure SQL DataBase instance. The instance contains a table named Customers that has columns named Id, Name, and IsPriority.
You need to create a view named VwPriorityCustomers that:
* returns rows from Customer that have a value of True in the IsPriority column, and
* does not allow columns to be altered or dropped in the underlying table.
Which Transact-SQL statement shoul you run?

A. CREATE VIEW VwPriorityCustomers
WITH VIEW_METADATA
AS
SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1 B. CREATE VIEW VwPriorityCustomers
AS
SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1
WITH CHECK OPTION C. CREATE VIEW VwPriorityCustomers
WITH ENCRYPTION
AS
SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1 D. CREATE VIEW VwPriorityCustomers
WITH SCHEMABINDING
AS
SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 4

You are developing an SQL Server database. The database contains two tables and a function that are defined by the following Transact-SQL statements.

You need to create a query to determine the total number of products that are sold each day for the live top-selling products on that particular day.
How should you complete the relevant Transact-SQL script? To answer, select the appropriate Transact-SQL statements from each list in the answer area.

Discussion 0

Correct Answer:

Explanation:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. T There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
Reference:
https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

Question 5

A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history.
The Production.Product table contains the following two columns:
The Sales.SalesOrderDetail table contains the following three columns:
You need to create a query listing all of the products that were never ordered.
Which statements should you execute?

A. Option B B. Option A C. Option C D. Option D

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 6

The following is a series of questions in which you are required to input one or more lines of code.
To input your response
Type your response into the text entry field in the Answer Area. You may input one or more lines of code. More than one solution may be correct. You will receive credit if your solution matches any of the correct solutions.
To validate code syntax
After entering your code, click the Check Syntax button. This validates code syntax (such as SQL commands) and values (such as table names and variable names) used in your solution. If there are any errors, they will appear in the window next to the Check Syntax button. You may change your code and re-validate the syntax as many times as you want.
Note that Check Syntax does NOT validate whether you have answered the question correctly. It simply validates the accuracy of your syntax.
To view available command keywords
Click the Keywords button to view a list of command keywords. This is a general list provided for reference and is not limited to commands used in the question.

The Sales schema of a database contains the tables shown in the exhibit.

You need to create a stored procedure named RecentOrders that meets the following requirements:
* Declares an input variable named @EndDate of type varchar(255).
* Compare the value of @EndDate with the OrderDate for records from the Sales.Orders table.
* Return only the OrderID column for rows where the OrderDate is more recent than @EndDate.
Construct the Transact-SQL for the stored procedure by using the following guidelines:
* Do not use aliases.
* Do not use object delimiters.
* Ensure that any objects called in RecentOrders can be resolved by all users.
* Convert @endDate to a datetime data type in the WHERE clause of the SELECT statement before comparing the value to an OrderDate.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-SQL segment that has been provided as well as below it.

A. Line 1: CREATE PROCECURE RecentOrders @EndDate varchar(255)
Line 2: AS
Line 3: SELECT OrderID
Line 4: FROM Sales.Orders
Line 5: WHERE Sales.Orders B. Line 1: CREATE PROCECURE RecentOrders @EndDate varchar(255)
Line 2: AS
Line 3: SELECT OrderID
Line 4: FROM Sales.Orders
Line 5: WHERE Sales.Orders.OrderDate > @Endate
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql

Discussion 0

Correct Answer: B Vote an answer

Question 7

You want to add a new GUID column named ProductGUID to a table named dbo.Product that already contains data.
ProductGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Product.
You need to ensure that the new column is assigned a GUID for existing rows.
Which four Transact-SQL statements should you use to develop the solution? (To answer, move the appropriate Transact-SQL segments from the list of SQL-segments to the answer area and arrange them in the correct order.)

Discussion 0

Correct Answer:

Explanation:
Box 1:
Existing BookGuid can be null.
Box 2:
Box 3: new()
Generate a new global value
Box 4:
Actually, in the real world, you don't have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it.

Question 8

You develop a Microsoft SQL Server 2012 database.
You need to create a batch process that meets the following requirements:
* Returns a result set based on supplied parameters.
* Enables the returned result set to perform a join with a table.
Which object should you use?

A. Table-valued user-defined function B. Inline user-defined function C. Stored procedure D. Scalar user-defined function

Discussion 0

Correct Answer: A Vote an answer

Question 9

You develop a Microsoft SQL Server database for an educational institution. The database contains tables named ExamEvaluation and ProjectEvaluation, which are used to track student scores. The tables are created by using the following code:

Students are evaluated based on the following rules:
* Each student will complete a maximum of one exam and one project.
* A student must complete at least one exam.
* A single exam consists of multiple sections, where the student is evaluated based on the average score of all sections.
* A single project consists of Section A and Section B, where the student is evaluated by adding 60 percent of Section A and 40 percent of Section B.
* At any given time, a student may have completed only an exam, or both an exam and a project.
You want to create a summary report of student evaluations.
You need to write a query that will return evaluation results for students who have completed any form of evaluation, grouped by the year of undertaling. A sample result set is shown in the following table.

Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Discussion 0

Correct Answer:

Explanation:
Box 1: SELECT..
The outer select statement
Box 2: (SELECT ..AVG(SectionScore)
Need the average score: A single exam consists of multiple sections, where the student is evaluated based on the average score of all sections.
Box 3: LEFT OUTER JOIN
There might be no finished Project
Box 4: ON..
Box 5: (SELECT ..SUM
Need the SUM function to handle addition of NULL values.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql

Question 10

You are developer for a Microsoft Azure SQL Database instance.
You are creating a new stored procedure. The procedure must perform the following tasks in this order:
* 1. Update a table named OrderHistory.
* 2. Delete rows from a table named Orders.
* 3. Delete rows from a table named Customers.
* 4. Insert rows into a table named ProcessHistory.
You need to ensure that the procedure meets the following requirements:
* If either DELETE operation fails, the rest of operation must continue.
* If either the UPDATE operation or the INSERT operation fails, the whole procedure should fail and no changes should be retained.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Discussion 0

Correct Answer:

Explanation:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

Question 11

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO C. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS E. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers') F. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS G. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers') H. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO

Discussion 0

Correct Answer: F Vote an answer

Question 12

You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?

A. Decimal B. Money C. Float D. Smallmoney

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 13

You create a table by using the following Transact-SQL Statement:

You need to return a result set that has a single column named DisplayInformation. The result set must contain the Name value if the Name value is NOT NULL, otherwise the result set must contain the SubName value.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.

Discussion 0

Correct Answer:

SELECT IIF (Name IS NOT NULL, Name, SubName)
Explanation:
Update line 1 to get the following:
SELECT IIF (Name IS NOT NULL, Name, SubName)
FROM Products;
IIF returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.
Syntax: IIF ( boolean_expression, true_value, false_value )
If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE.
If the value of expression is NULL,IS NOT NULL returns FALSE; otherwise, it returns TRUE.
To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL References:https://msdn.microsoft.com/en-us/library/hh213574.aspx
https://msdn.microsoft.com/en-us/library/ms188795.aspx

Question 14

Your database contains tables that were created by running the following Transact-SQL statements:

Data from the Ticket and Action production tables needs to be archived to the TicketArchive and ActionArchive tables, respectively. When a ticket is archived, data is copied from the production tables and then deleted from the production tables.
You need to create statements to archive all the tickets that:
were opened one year ago or earlier,
were closed six months ago or earlier, and
have a TicketStatusID of 10.
Which Transact-SQL segment or segments should you use to complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Discussion 0

Correct Answer:

Explanation:
Box 1: DECLARE..
Declare a table variable
Box 2: OUTPUT..
Box 3: DELETE FROM dbo.Ticket
Remove from the Ticket table.
Box 4: DELETE FROM dbo.Action
Remove from the Action table.
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

Question 15

You use a Microsoft SQL Server 2012 database.
You want to create a table to store Microsoft Word documents.
You need to ensure that the documents must only be accessible via Transact-SQL queries.
Which Transact-SQL statement should you use?

A. CREATE TABLE DocumentStore AS FileTable B. CREATE TABLE DocumentStore
(
[Id] INT NOT NULL PRIMARY KEY,
[Document] VARBINARY(MAX) NULL
)
GO C. CREATE TABLE DocumentStore
(
[Id] hierarchyid,
[Document] NVARCHAR NOT NULL
)
GO D. CREATE TABLE DocumentStore
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[Document] VARBINARY(MAX) FILESTREAM NULL
)
GO

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).