70 461 Querying Microsoft SQL Server 2012, Exercises of Computer Science

Download 70 461 Querying Microsoft SQL Server 2012 and more Exercises Computer Science in PDF only on Docsity!

• Microsoft 70-
  • Querying Microsoft SQL Server
    • Version: 25.

QUESTION NO: 1

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named ProductsDB. The relevant part of the ProductsDB is shown in the following database diagram:

You need to write a Transact-SQL query that display a single row in the following XML format:

<row ProductID="1001" Product="Product Name", Price="24.99" InStock="16" Supplier="Company Name" Contact="Contact Name" Phone="346 959 2215" />

Which of the following SELECT statement would you write?

A. SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS [InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID WHERE ProductID = 1001 FOR XML RAW B. SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS [InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID WHERE ProductID = 1001 FOR XML C. SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS [InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID WHERE ProductID = 1001 FOR XML AUTO D. SELECT ProductID, ProductName AS [Product], UnitPrice AS [Price], UnitsInStock AS [InStock], CompanyName AS [Supplier], ContactName AS [Contact], Phone FROM Products INNER JOIN Suppliers ON SupplierID = SupplierID WHERE ProductID = 1001

INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1001 FOR XML C. SELECT Invoices.InvoiceID, Invoices.InvoiceDate AS [Date], Invoices.InvoiceValue AS [Value], Customers.CustomerName AS [Name], Customers.CustomerCity AS [ShippedTo] FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1001 FOR XML AUTO D. SELECT InvoiceID, InvoiceDate AS [Date], InvoiceValue AS [Value], CustomerName AS [Name], CustomerCity AS [ShippedTo] FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID WHERE Customers.CustomerID = 1001 FOR XML AUTO, RAW

Answer: C

Explanation:

QUESTION NO: 3

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database named ProductsDB. The ProductsDB database is shown in the following database diagram:

You need to write a Transact-SQL query that displays all the products received by a single supplier in the following XML format:

Which of the following SELECT statement would you write?

A. SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID, p.UnitPrice, p.UnitsInStock FROM Suppliers AS s INNER JOIN Products AS p ON s.SupplierID = p.SupplierID WHERE s.SupplierID = 22 FOR XML RAW B. SELECT s.SupplierID, s.CompanyName AS [Company], s.ContactNumber, p.ProductID, p.UnitPrice, p.UnitsInStock FROM Suppliers AS s INNER JOIN Products AS p ON s.SupplierID = p.SupplierID WHERE s.SupplierID = 22 FOR XML C. SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company], Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID WHERE Suppliers.SupplierID = 22 FOR XML AUTO D. SELECT Suppliers.SupplierID, Suppliers.CompanyName AS [Company], Suppliers.ContactNumber, Products.ProductID, Products.UnitPrice, Products.UnitsInStock FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID WHERE Suppliers.SupplierID = 22 FOR XML AUTO, RAW

Answer: A

Explanation:

QUESTION NO: 4 CORRECT TEXT

You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for a database driven Web application that allows visitors to vote for the cricket player of the week. The number of votes is stored in a table named WeeklyVotes that has columns named Week, PlayerName, Votes.

You need to write a Transact-SQL query that ranks the top 30 cricket players by the average votes over the last 12 months. You want the top 10 cricket players to have a rank of 1, the next 10 to have a rank of 2, and the last 10 to have a rank of 3.

Which of the following SELECT statement would you use?

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

A. SELECT PlayerName, Votes FROM ( SELECT PlayerName, Votes, RANK () OVER (PARTITION BY PlayerName ORDER BY Votes ASC) AS Rank FROM WeeklyVotes ) AS tmp WHERE Rank = 1 B. SELECT PlayerName, Votes FROM ( SELECT PlayerName, Votes, RANK() OVER (PARTITION BY Week ORDER BY Votes DESC) AS Rank FROM WeeklyVotes) AS tmp WHERE Rank = 1 C. SELECT PlayerName, Votes FROM ( SELECT TOP 1 PlayerName, Votes, RANK () OVER (PARTITION BY PlayerName ORDER BY Votes ASC) AS Rank FROM WeeklyVotes ORDER BY Rank) AS tmp D. SELECT PlayerName, Votes FROM ( SELECT TOP 1 PlayerName, Votes, RANXO OVER (PARTITION BY PlayerName ORDER BY Votes DESC) AS Rank FROM WeeklyVotes ORDER BY Rank) AS tmp

Answer: B

Explanation:

QUESTION NO: 7

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB that has a table named Inventory.

The Inventory table has three columns named ProductID, InStore and InWarehouse. The ProductID column is the primary key and is linked to the Products table. The InStore column stores the quantity of a product that is held at ABC.com's retail shop, while the InWarehouse column stores the quantity of a product that is held at ABC.com's warehouse.

You need to add a computed column that stores the total number of a product that ABC.com has.

What Transact-SQL statements would accomplish this task?

A. ALTER TABLE Inventory ADD TotalProducts AS (InStore + InWarehouse) PERSISTED B. ALTER TABLE Inventory ADD TotalProducts int SPARSE NOT NULL

C. ALTER TABLE Inventory ADD TotalProducts AS SUM (ALL) OVER (GROUP BY InStore, InWarehouse) PERSISTED D. DROP TABLE Inventory GO CREATE TABLE Inventory ( ProductID int NOT NULL PRIMARY KEY, InStore int NOT NULL, InWarehouse int NOT NULL, TotalProducts AS SUM (InStore, InWarehouse) PERSISTED )

Answer: A

Explanation:

Ref: http://www.kodyaz.com/articles/sql-server-computed-column-calculated-column-sample.aspx

QUESTION NO: 8

ABC.com has a SQL Server 2012 database instance that hosts a database named ComDB. The ComDB database has a table named Partners that was created using the following Transact-SQL code:

CREATE TABLE [dbo].[Partners]

(

[CompanyID] [int] NOT NULL,

[CompanyName] [nvarchar] (50) NOT NULL,

[Location] [nvarchar] (50) NOT NULL,

[ContactName] [nvarchar] (50) NOT NULL,

[Email] [nvarchar] (50) NOT NULL,

[Phone] [nvarchar] (10) NOT NULL,

CONSTRAINT [PK_Partners] PRIMARY KEY CLUSTERED

(

[CompanyID] ASC

)

ON PRIMARY

CREATE TABLE [dbo].[Partners]

(

[CompanyID] [int] NOT NULL PRIMARY KEY,

[CompanyName] [varchar] (150) NOT NULL,

[Location] [varchar] (150) NOT NULL,

[ContactName] [varchar] (150) NOT NULL,

[Email] [varchar] (150) NOT NULL,

[Phone] [varchar] (10) NOT NULL

)

You develop a new table named Events using the following Transact-SQL code:

CREATE TABLE [dbo].[Events]

(

[EventID] [int] NOT NULL PRIMARY KEY,

[CompanyID] [int] NOT NULL,

[EventDescription] [varchar] (2500),

[EventCordinator] [varchar] (150) NOT NULL

)

How would you guarantee that values in the Events.CompanyID column already exist in the Partners.CompanyID column?

A. You should add a Foreign Key Constraint on the Events table. B. You should add a Check Constraint on the Events table. C. You should add a Unique Constraint on the Events table. D. You should specify Events.CompanyID as a spars column. E. You should change the Events table to a partitioned table.

Answer: A

Explanation:

Ref: http://msdn.microsoft.com/en-us/library/ms179610.aspx

QUESTION NO: 11

ABC.com has a SQL Server 2012 database infrastructure that has a database named ComDB.

You have created a view using the following Transact-SQL code:

CREATE VIEW ABCCommunications

(Type, CompanyID, CompanyName, Location, ContactName, Email, Phone)

AS

SELECT 'Clients' AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone

FROM CommList

WHERE Relation = 'Client'

SELECT 'Partners' AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone

FROM CommList

WHERE Relation = 'Partner'

SELECT 'Guests' AS Type, CompanyID, CompanyName, Location, ContactName, Email, Phone

FROM CommList

WHERE Relation = 'Guests'

GO

You want the view to be used to edit all columns except the CompanyID, CompanyName and Location columns.

What should you implement on the view?

A. You should consider implementing an AFTER UPDATE trigger. B. You should consider implementing an Index. C. You should consider implementing an INSTEAD OF UPDATE trigger. D. You should consider implementing a CHECK constraint.

Answer: C

GO

How should you alter this view to allow users to update data through the SalesV?

A. You should add a CHECK constraint to the SalesV view. B. You should add an INSTEAD OF trigger to the SalesV view. C. You should add a clustered index to the SalesV view. D. You should add an AFTER UPDATE trigger to the SalesV view. E. Create a columnstore index on all columns used in the SalesV view.

Answer: B

Explanation:

QUESTION NO: 13 CORRECT TEXT

You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a SalesDB database with a view named SalesV. The SalesV view was created using the following Transact-SQL code:

CREATE VIEW SalesDB.ProductsSalesV

AS

SELECT OrderID, ProductID, ShipDate, OrderDate, Amount

FROM SalesDB.Orders;

You want to create an inline table-valued function named fn_ABC that accepts a @ProductID parameter of the integer data type. The inline table-valued function should also allow for sales orders for each product to be listed by the latest sale.

How would you create this inline table-valued function?

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

Answer: CREATE FUNCTION SalesDB.fn_ABC ( @ProductID int )

RETURNS TABLE

AS

RETURN

(

SELECT OrderID, ProductID, ShipDate, OrderDate, Amount

FROM Sales. ProductsSalesV

WHERE ProductID = @ProductID

ORDER BY OrderDate DESC

);

QUESTION NO: 14 CORRECT TEXT

You are employed as a SQL Server 2012 database developer at ABC.com. ABC.com has a database named SalesDB with tables named Customer and Orders. The Customer and Orders tables were created using the following Transact-SQL code:

CREATE TABLE SalesDB.Customers

(

CustomerID int NOT NULL PRIMARY KEY,

FirstName varchar (150) NOT NULL,

LastName varchar (150) NOT NULL,

Address1 varchar (200) NOT NULL,

Address2 varchar (200) NULL,

City varchar (100) NOT NULL,

StateCode varchar (2) NOT NULL,

Zip varchar (5) NOT NULL,

Phone varchar (10) NOT NULL

)

GO

CREATE TABLE SalesDB.Orders

(

OrderID int NOT NULL PRIMARY KEY,

CustomerID int NOT NULL,

OrderDate datetime NOT NULL,

ShipDate datetime NOT NULL,

named OrderDate.

Which of the following WHERE clauses would be the most efficient WHERE clause to use?

A. WHERE OrderDate = CAST(datetime,@date) B. WHERE OrderDate = CONVERT(datetime,@date) C. WHERE OrderDate =@date D. WHERE OrderDate = CAST(@date AS datetime) E. WHERE OrderDate = PARSE(@date AS Date)

Answer: C

Explanation:

QUESTION NO: 16

You work as a database developer at ABC.com. You want to create a Transact-SQL query will call a table-valued function for every row the query returns.

How would you accomplish this task?

A. You should make use of a UNION. B. You should make use of a CONVERT function. C. You should make use of an INNER JOIN. D. You should make use of a Trigger. E. You should make use of a CAST function. F. You should make use of an OUTER JOIN. G. You should make use of a CROSS APPLY. H. You should make use of the FORMAT function.

Answer: G

Explanation:

QUESTION NO: 17

Which of the following datatypes has a fixed precision and a scale of six digits?

A. Double B. Money C. Int

D. Numeric E. SmallInt F. VarInt G. Float

Answer: B

Explanation:

QUESTION NO: 18

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database named SalesDB as illustrated in the following database diagram:

ABC.com has retail stores in a few major cities across the country. The company wants a list of Customers who live in a city that does not have a ABC.com store, along with the customer's City. The result set must be sorted alphabetically by City name.

Which of the following Transact-SQL statements would return the required information?

A. SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCity NOT EXISTS (SELECT StoreCity FROM Stores) ORDER BY CustomerCity B. SELECT CustomerName, CustomerCity FROM Customers WHERE CustomerCity < > ALL (SELECT StoreCity FROM Stores)

You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that has a database named ComDB with tables named Partners and Events. These tables were created using the following Transact-SQL code:

CREATE TABLE [dbo].[Partners]

(

[CompanyID] [int] NOT NULL PRIMARY KEY,

[CompanyName] [varchar] (150) NOT NULL,

[Location] [varchar] (150) NOT NULL,

[ContactName] [varchar] (150) NOT NULL,

[Email] [varchar] (150) NOT NULL,

[Phone] [varchar] (10) NOT NULL

)

CREATE TABLE [dbo].[Events]

(

[EventID] [int] NOT NULL PRIMARY KEY,

[CompanyID] [int] NOT NULL,

[EventDescription] [nvarchar] (MAX),

[EventDate] [nvarchar] (50) NOT NULL,

[EventCordinator] [nvarchar] (150) NOT NULL

)

You add a foreign key relationship between the two tables on the CompanyID column.

You need to develop a stored procedure named sp_coEvents that retrieves CompanyName for all partners and the EventDate for all events that they have coordinated.

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

Answer: A

CREATE PROCEDURE sp_coEvent

AS

SELECT CompanyName, EventDate

FROM Partners JOIN Events ON

Partners.CompanyID = Events.CompanyID

QUESTION NO: 21

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 database infrastructure with a very large database named SalesDB. You create a new table named SalesHistory that will hold historical data from the SalesDB database.

You need to perform a batch update from the SalesDB database to the SalesHistory table. You want the status information from the batch process to be logged to a SQL Server table that must be created by the batch process.

How would you accomplish this task?

A. You should make use of the FORMAT function. B. You should make use of the CONVERT function. C. You should make use of a scalar user-defined function. D. You should make use of an inline function. E. You should make use of a table-valued function. F. You should make use of a stored procedure.

Answer: C

Explanation:

QUESTION NO: 22

You are developing a SQL Server 2012 database for ABC.com. You need to create a computed column that returns the data by referencing another table using an INNER JOIN.

How would you accomplish this?

A. You should make use of the FORMAT function. B. You should make use of a scalar user-defined function. C. You should make use of an inline function. D. You should make use of a table-valued user-defined function. E. You should make use of a stored procedure.