REAL QUESTIONS SUBMIT MATERIAL ADVERTISE
Braindumps

Microsoft

Cisco

Citrix

CIW

CompTia

CWNA

Apple

Adobe

HP

Legato

Exin

Filemaker

Brocade

Ericsson

TIA

Veritas

ISEB

SCP

IISFA

ISM

OMG

Apc

Mile2

Foundry

Huawei

McData

Symantec

TeraData

RedHat

Solar Winds

Blue Coat

Riverbed

 

 
 
Click on name of dumper to view the dump
 
Worth
 
 

 

Braindumps of 70-460
Querying Microsoft SQL Server 2012

 

Exam Questions, Answers, Braindumps (70-460)

I passed it with good score, but you need to study and remember a lot of the information. I used www.exams.ws for my test. Here are the questions what I remember.

 

QUESTION NO: 1

You work 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,

CustomerName nvarchar (250) NOT NULL,

Address1 nvarchar (100) NOT NULL,

Address2 nvarchar (100) NULL,

City nvarchar (50) NOT NULL,

State nvarchar (50) 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,

CustomerID int NOT NULL,

SalesRepID int NOT NULL

)

GO

You are developing a stored procedure named OrdersByDate that returns the OrderID,

CustomerID, CustomerName and OrderDate. The stored procedure will take a parameter named

@date that uses the int datatype. The @date parameter will be used to filter the result set based on the OrderDate column in the Orders table.

How would you create the stored procedure?

A. CREATE PROCEDURE OrdersByDate
@date int
AS
SELECT OrderID, CustomerID, CustormerName, OrderDate
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE OrderDate = CONVERT(datetime,@date)

B. CREATE PROCEDURE OrdersByDate
@date int
AS
SELECT OrderID, CustomerID, CustormerName, OrderDate
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE OrderDate =@date

C. CREATE PROCEDURE OrdersByDate
@date int
AS
SELECT OrderID, CustomerID, CustormerName, OrderDate
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE OrderDate = CAST(@date AS datetime)

D. CREATE PROCEDURE OrdersByDate
@date int
AS
SELECT OrderID, CustomerID, CustormerName, OrderDate
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE OrderDate = PARSE(@date AS datetime)

Answer: C

QUESTION NO: 2

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that contains a database named ABCDB. The ABCDB database is used by an in-house application named ABCApp3 that queries a readonly table with a clustered index. ABC.com users report that ABCApp3 is functioning sluggishly. You suspect query the application uses is causing the problem. You analyze the query and discover that column referenced in the WHERE clause is not part of the clustered index. You also notice that the query returns five columns, as well as a COUNT (*) clause grouped on the five columns. How would you improve the efficiency of this query?

A. You should replace the query with recursive stored procedure.

B. You should replace the COUNT (*) clause with a persisted computed column.

C. You should create nonclustered indexes on all columns used in the query.

D. You should create a filtered index on the column used in the WHERE clause.

E. You should add an INCLUDE clause to the clustered index.

F. You should create a columnstore index on all columns used in the query.

G. You should create a unique clustered index on the column used in the WHERE clause.

Answer: F

QUESTION NO: 3

You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that contains a database named SalesDB. The SalesDB database is used by an in-house application named ABCApp3. ABC.com users report that ABCApp3 is functioning sluggishly. You discover that application consumes considerable memory when it runs single-use dynamic queries against the SalesDB database. You suspect that these queries are making excessive use of procedure cache. How would you reduce procedure cache if you cannot create new indexes on the SalesDB database?

A. You should replace the queries with recursive stored procedures.

B. You should add make use of the INCLUDE clause in the index.

C. You should make use of the READPAST hint in the queries.

D. You should make use of the READCOMMITTED hint in the queries.

E. You should make use of the optimize for ad hoc workloads option.

Answer: E

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.

Answer:

SELECT TOP 50 PlayerName,

NTILE (3) OVER (ORDER BY AVG (Votes) DESC) AS AveVotes

FROM WeeklyVotes

GROUP BY PlayerName

QUESTION NO: 5

You work as a database developer at ABC.com. ABC.com has a SQL Server 2012 databasenamed SalesDB that has a table named WeeklySales. The WeeklySales table records the sales amount for each of ABC.com's 20 sales representitives. You need to write a Transact-SQL query that ranks the sales representatives by the average sales amount for the past year. You want the sales representatives with the same average sales amount to have the same rank with the subsequent rank being skipped. Which ranking function should you use?

A. The RANK( ) OVER function.

B. The NTILE( ) OVER function

C. The DENSE_RANK( ) OVER function

D. The ROW_NUMBER( ) OVER function

E. The FORMAT function.

Answer: C

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

QUESTION NO: 6

You work as a SQL Server 2012 database developer at ABC.com. You are developing a query fora 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 returns the cricket player that received the mostvotes for each week, as well as the number of votes they received. Which of the following SELECT statement would accomplish this task?

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

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

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. TheComDB 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

)

You want to create a FOR UPDATE trigger that will track changes to the ContactName and Phone columns. Which of the following statements should you use in the trigger definition?

A. IF COLUMNS_UPDATED (ContactName, Phone)

B. IF COLUMNS_UPDATED (ContactName) OR COLUMNS_UPDATED (Phone)

C. IF UPDATED (ContactName, Phone).

D. IF UPDATED (ContactName) OR UPDATED (Phone)

Answer: D

QUESTION NO: 9

You are the database administrator of a SQL Server 2012 database infrastructure at ABC.com. You need to optimize a very large database table that contains several million rows of data by designing a view based on the table. The view must allow users to perform aggregations on several columns.How should you configure the view to ensure optimal performance?

A. You should create the view as an indexed view.

B. You should create a clustered index on the view.

C. You should make use of a stored procedure to return that data.

D. You should make use of a table-valued function.

Answer: A

 

 

70-460

 


 

 

Braindumps Real exam questions and verified answers - 100% passing guarantee - cheap prices.

 

Free brain dumps Braindumps, notes, books for free

 

Braindumps and Exams - Instant download real exam questions - Passing guarantee.

Follow us on FaceBook
Braindumps on Facebook
 
 
 
 
 

CheckPoint

Linux

Novell

DB/2

Network Appliance

EC-Council

Nortel

McAfee

Juniper

ISACA

PMI

Sybase

EMC

HDI

SNIA

ISC

Sair

IBM

Lotus

Exam Express

3COM

BICSI

DeLL

Enterasys

Extreme Networks

Guidance Software

Computer Associates

Network General

SAS Institute

Alcatel Lucent

SeeBeyond

TruSecure

Polycom

Hyperion

Hitachi

Nokia

Fortinet

Vmware

Fujitsu

Tibco

Intel

PostgreSQLCE

BusinessObjects

RESSoftware

BlackBerry

AccessData

ICDL

Isilon

SAP

The Open Group

ACSM

Altiris

Avaya

Cognos

F5

Genesys

SDI

ACI

ASQ

Google

H3C

HIPAA

HRCI

SOA

IIBA

Zend