Microsoft 70-461 Querying Microsoft SQL Server 2012 Online Training

Question #21

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
AS
SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1
WITH CHECK OPTION
B . CREATE VIEW VwPriorityCustomers
WITH VIEW_METADATA
AS
SELECT Id, Name FROM dbo.Customers WHERE IsPriority=1
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

Reveal Solution Hide Solution

Question #22

DRAG DROP

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.

Reveal Solution Hide Solution

Question #23

You administer a Microsoft SQL Server instance that will support several databases.

You need to ensure that every new database created has a data type named postalcode that contains the

same attributes.

What should you do?

A . Create a user-defined type on the model database.
B . Create a user-defined type on the master database.
C . Create a user-defined data type on the master database.
D . Create a user-defined data type on the model database.

Reveal Solution Hide Solution

Question #24

DRAG DROP

You administer a SQL Server database that tracks sales that are made by sales persons.

The database contains a table that is defined by the following Transact-SQL statements:

You have the following requirements:

– accept a datetime value for the query month

– return a list of salespeople IDs who have sales in the query month or before the query month

– compare sales with sales quota for salespeople who have a sales quota

– display year-to-date sales for salespeople that do not have a sales quota

How should you complete the stored procedure? 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.

Reveal Solution Hide Solution

Question #25

DRAG DROP

You are creating queries for a shopping cart application.

You have the following requirements:

– Query1 must list products where less than 100 units have been sold in total.

– Query2 must list products where more than 10 units have been sold in a single order.

You need to identify the correct sub-queries to complete the outer queries that you have written.

Which Transact-SQL statement or statements should you use? To answer, drag the appropriate SQL statements to the correct locations. Each SQL statement 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.

Reveal Solution Hide Solution

Question #26

You are working with a table that has an XML column that contains information about books. Each book may have an associated price. You need to write a query that returns each author on a separate row in XML format.

Which XML method should you use?

A . Value()
B . Nodes()
C . Query()
D . Exist()

Reveal Solution Hide Solution

Question #27

DRAG DROP

A database contains tables as shown in the exhibit. (Click the Exhibit button.)

Customer who are inactive are moved from the Customers table to the InactiveCustomers table. Any orders for inactive customers are removed from the Orders table.

You write the following SELECT statement to return all the inactive customers:

SELECT CustomerId FROM InactiveCustomers

You need to extend the SELECT statement to include customers who do not have any orders.

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.

Reveal Solution Hide Solution

Question #28

You develop a Microsoft SQL Server database.

The database contains a table defined by the following Transact-SQL statement:

All regions have more than one employee.

You need to write a query to find the employee with the highest SalesYTD in each region with the following result set:

– First Name

– Last Name

– Region

– Sales YTD

Which Transact-SQL query should you run?

A)

B)

C)

D)

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

Reveal Solution Hide Solution

Question #29

You administer a Microsoft SQL Server database that contains a table named Products.

In a bit column named Discontinued, a value of 1 indicates a product is inactive, and a value of 0

indicates the product is active.

You need to create a view that returns a column with a full product status description.

Which expression should you use to define the column?

A . IF(Discontinued = 0, ‘Active’, ‘Inactive’)
B . CASE Discontinued
WHEN 0 THEN ‘Active’
WHEN 1 THEN ‘Inactive’
END
C . IF Discontinued = 0
‘ Active’
ELSE
‘ Inactive’
D . DECODE (Discontinued, 0, ‘Active’, 1, ‘Inactive’, ‘Unknown’)

Reveal Solution Hide Solution

Question #30

DRAG DROP

You develop a Microsoft SQL Server database that supports an e-commerce website.

You create a table defined by the following Transact-SQL statement:

You need to create a reusable function that will remove all carriage return characters from all the text values.

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.