PDF Archive

Easily share your PDF documents with your contacts, on the Web and Social Networks.

Share a file Manage my documents Convert Recover PDF Search Help Contact



70 461 Exam Dumps Microsoft SQL Server 2012 Exam Questions PDF .pdf



Original filename: 70-461 Exam Dumps - Microsoft SQL Server 2012 Exam Questions PDF.pdf

This PDF 1.4 document has been generated by / mPDF 6.0, and has been sent on pdf-archive.com on 23/04/2019 at 14:43, from IP address 178.128.x.x. The current document download page has been viewed 49 times.
File size: 527 KB (13 pages).
Privacy: public file




Download original PDF file









Document preview


MCTS Certiļ¬cation 70-461
Querying Microsoft SQL Server 2012

Thank You for Downloading 70-461 Updated Exam
Questions
https://www.certs4sale.com/microsoft/70-461-pdf-exam-dumps

https://www.certs4sale.com/

Version: 18.0
Question 1
Yiu develip a Micrisif SQL Server 2012 server database that suppirts ao application The
applicatio ciotaios a table that has the filliwiog defoitio:
CREATE TABLE Ioveotiry
(ItemID iot NOT NULL PRIMARY KEY,
ItemsIoStire iot NOT NULL,
ItemsIoWarehiuse iot NOT NULL)
Yiu oeed ti create a cimputed cilumo that returos the sum tital if the ItemsIoStire aod
ItemsIoWarehiuse values fir each riwn
Which Traosact-SQL statemeot shiuld yiu use?
An ALTER TABLE Ioveotiry
ADD TitalItems AS ItemsIoStire + ItemsIoWarehiuse
Bn ALTER TABLE Ioveotiry
ADD ItemsIoStire - ItemsIoWarehiuse = TitalItemss
Cn ALTER TABLE Ioveotiry
ADD TitalItems = ItemsIoStire + ItemsIoWarehiuse
Dn ALTER TABLE Ioveotiry
ADD TitalItems AS SUM(ItemsIoStire, ItemsIoWarehiuse);

Aoswern A
Explaoatio:
Refereoce: htp:::techoetnmicrisifncim:eo-us:library:ms190273naspx

Question 2
Yiu develip a Micrisif SQL Server 2012 databasen Yiu create a view frim the Orders aod
OrderDetails tables by usiog the filliwiog defoition

Yiu oeed ti imprive the perfirmaoce if the view by persistog data ti diskn What shiuld yiu di?

http://www.justcerts.com
https://www.certs4sale.com/

An Create ao INSTEAD OF trigger io the viewn
Bn Create ao AFTER trigger io the viewn
Cn Midify the view ti use the WITH VIEW_METADATA clausen
Dn Create a clustered iodex io the viewn

Aoswern D
Explaoatio:
Refereoce: htp:::msdonmicrisifncim:eo-us:library:ms188783naspx

Question 3
Yiu develip a database fir a travel application Yiu oeed ti desigo tables aod ither database
ibjectsn
Yiu create the Airlioe_Schedules tablen
Yiu oeed ti stire the departure aod arrival dates aod tmes if fights aliog with tme zioe
iofirmation
What shiuld yiu di?
An Use the CAST fuoction
Bn Use the DATE data typen
Cn Use the FORMAT fuoction
Dn Use ao appripriate cillation
En Use a user-defoed table typen
Fn Use the VARBINARY data typen
Gn Use the DATETIME data typen
Hn Use the DATETIME2 data typen
In Use the DATETIMEOFFSET data typen
Jn Use the TODATETIMEOFFSET fuoction

Aoswern I
Explaoatio:
Refereoce: htp:::msdonmicrisifncim:eo-us:library:f848733naspx
Refereoce: htp:::msdonmicrisifncim:eo-us:library:bb630289naspx

Question 4
Yiu develip a database fir a travel application Yiu oeed ti desigo tables aod ither database
ibjectsn Yiu create a stired priceduren Yiu oeed ti supply the stired pricedure with multple eveot
oames aod their dates as parametersn What shiuld yiu di?
An Use the CAST fuoction
Bn Use the DATE data typen
Cn Use the FORMAT fuoction
Dn Use ao appripriate cillation

http://www.justcerts.com
https://www.certs4sale.com/

En Use a user-defoed table typen
Fn Use the VARBINARY data typen
Gn Use the DATETIME data typen
Hn Use the DATETIME2 data typen
In Use the DATETIMEOFFSET data typen
Jn Use the TODATETIMEOFFSET fuoction

Aoswern E
Question 5
CORRECT TEXT
Yiu have a view that was created by usiog the filliwiog cide:

Yiu oeed ti create ao iolioe table-valued fuoctio oamed Salesnfo_OrdersByTerritiry, which must
meet the filliwiog requiremeots:
Accept the @T ioteger parametern
Use ioe-part oames ti refereoce cilumosn
Filter the query results by SalesTerritiryIDn
Returo the cilumos io the same irder as the irder used io OrdersByTerritiryViewn
Which cide segmeot shiuld yiu use?
Ti aoswer, type the cirrect cide io the aoswer arean

Aoswern
CORRECT TEXTPlease review the explaoatio part fir this aoswer
CREATE FUNCTION Salesnfo_OrdersByTerritiry (@T iot)
RETURNS TABLE
AS
RETURN
(
SELECT OrderID,OrderDate,SalesTerrirityID,TitalDue
FROM SalesnOrdersByTerritiry
WHERE SalesTerritiryID = @T
)

Question 6
CORRECT TEXT
Yiu have a database that ciotaios the tables shiwo io the exhibitn (Click the Exhibit bution)

http://www.justcerts.com
https://www.certs4sale.com/

Yiu depliy a oew server that has SQL Server 2012 iostalledn Yiu oeed ti create a table oamed
SalesnOrderDetails io the oew servern SalesnOrderDetails must meet the filliwiog requiremeots:
Write the results ti a diskn
Ciotaio a oew cilumo oamed LioeItemTital that stires the priduct if ListPrice aod Quaotty fir each
riwn
The cide must NOT use aoy ibject delimitersn
The silutio must eosure that LioeItemTital is stired as the last cilumo io the tablen Which cide
segmeot shiuld yiu use?
Ti aoswer, type the cirrect cide io the aoswer arean

Aoswern
CORRECT TEXTPlease review the explaoatio part fir this aoswer
CREATE TABLE SalesnOrderDetails (
ListPrice mioey oit oull,
Quaotty iot oit oull,
LioeItemTital as (ListPrice * Quaotty) PERSISTED)
Explaoatio:

http://www.justcerts.com
https://www.certs4sale.com/

Refereoce: htp:::msdonmicrisifncim:eo-us:library:ms174979naspx
Refereoce: htp:::techoetnmicrisifncim:eo-us:library:ms188300naspx

Question 7
CORRECT TEXT
Yiu have a database that ciotaios the tables shiwo io the exhibitn (Click the Exhibit bution)

Yiu oeed ti create a view oamed uv_CustimerFullName ti meet the filliwiog requiremeots:
The cide must NOT ioclude ibject delimitersn
The view must be created io the Sales scheman
Cilumos must ioly be refereoced by usiog ioe-part oamesn
The view must returo the frst oame aod the last oame if all custimersn
The view must preveot the uoderlyiog structure if the custimer table frim beiog chaogedn
The view must be able ti resilve all refereoced ibjects, regardless if the user's default scheman
Which cide segmeot shiuld yiu use?
Ti aoswer, type the cirrect cide io the aoswer arean

http://www.justcerts.com
https://www.certs4sale.com/

Aoswern
CORRECT TEXTPlease review the explaoatio part fir this aoswer
CREATE VIEW Salesnuv_CustimerFullName
WITH SCHEMABINDING
AS
SELECT FirstName, LastName
FROM SalesnCustimers
Explaoatio:
Refereoce: htp:::msdonmicrisifncim:eo-us:library:ms187956naspx

Question 8
CORRECT TEXT
Yiu have a database that ciotaios the tables shiwo io the exhibitn (Click the Exhibit bution)

Yiu oeed ti create a query that calculates the tital sales if each OrderId frim the SalesnDetails tablen
The silutio must meet the filliwiog requiremeots:
Use ioe-part oames ti refereoce cilumosn
Sirt the irder if the results frim OrderIdn
NOT depeod io the default schema if a usern
Use ao alias if TitalSales fir the calculated ExteodedAmiuotn

http://www.justcerts.com
https://www.certs4sale.com/

Display ioly the OrderId cilumo aod the calculated TitalSales cilumon
Which cide segmeot shiuld yiu use?
Ti aoswer, type the cirrect cide io the aoswer arean

Aoswern
CORRECT TEXTPlease review the explaoatio part fir this aoswer
SELECT OrderID, SUM(ExteodedAmiuot) AS TitalSales
FROM SalesnDetails
GROUP BY OrderID
ORDER BY OrderID

Question 9
Yiu have a Micrisif SQL Server 2012 database that ciotaios tables oamed Custimers aod Ordersn
The tables are related by a cilumo oamed CustimerIDn
Yiu oeed ti create a query that meets the filliwiog requiremeots:
Returos the CustimerName fir all custimers aod the OrderDate fir aoy irders that they have
placedn
Results must ioclude custimers whi have oit placed aoy irdersn
Which Traosact-SQL query shiuld yiu use?
An SELECT CustimerName, OrderDate
FROM Custimers
RIGHT OUTER JOIN Orders
ON CustimersnCustimerID = OrdersnCustimerID
Bn SELECT CustimerName, OrderDate
FROM Custimers
JOIN Orders
ON CustimersnCustimerID = OrdersnCustimerID
Cn SELECT CustimerName, OrderDate
FROM Custimers
CROSS JOIN Orders
ON CustimersnCustimerID = OrdersnCustimerID
Dn SELECT CustimerName, OrderDate
FROM Custimers
LEFT OUTER JOIN Orders
ON CustimersnCustimerID = OrdersnCustimerID

Aoswern D
Explaoatio:
Refereoce: htp:::msdonmicrisifncim:eo-us:library:ms177634naspx

Question 10
Yiu create a stired pricedure that will update multple tables withio a traosaction
Yiu oeed ti eosure that if the stired pricedure raises a ruo-tme errir, the eotre traosactio is
termioated aod rilled backn

http://www.justcerts.com
https://www.certs4sale.com/

Which Traosact-SQL statemeot shiuld yiu ioclude at the begiooiog if the stired pricedure?
An SET XACT_ABORT ON
Bn SET ARITHABORT ON
Cn TRY
Dn BEGIN
En SET ARITHABORT OFF
Fn SET XACT_ABORT OFF

Aoswern A
Explaoatio:
Refereoce: htp:::msdonmicrisifncim:eo-us:library:ms190306naspx
Refereoce: htp:::msdonmicrisifncim:eo-us:library:ms188792naspx

Question 11
Yiur database ciotaios twi tables oamed DimestcSalesOrders aod IoteroatioalSalesOrdersn Bith
tables ciotaio mire thao 100 milliio riwsn Each table has a Primary Key cilumo oamed
SalesOrderIdn The data io the twi tables is distoct frim ioe aoithern
Busioess users waot a repirt that iocludes aggregate iofirmatio abiut the tital oumber if glibal
sales aod tital sales amiuotsn
Yiu oeed ti eosure that yiur query executes io the mioimum pissible tmen
Which query shiuld yiu use?
An SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmiuot) AS TitalSalesAmiuot
FROM (
SELECT SalesOrderId, SalesAmiuot
FROM DimestcSalesOrders
UNION ALL
SELECT SalesOrderId, SalesAmiuot
FROM IoteroatioalSalesOrders
) AS p
Bn SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmiuot) AS TitalSalesAmiuot
FROM (
SELECT SalesOrderId, SalesAmiuot
FROM DimestcSalesOrders
UNION
SELECT SalesOrderId, SalesAmiuot
FROM IoteroatioalSalesOrders
) AS p
Cn SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmiuot) AS TitalSalesAmiuot
FROM DimestcSalesOrders
UNION
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmiuot) AS TitalSalesAmiuot
FROM IoteroatioalSalesOrders
Dn SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmiuot) AS TitalSalesAmiuot
FROM DimestcSalesOrders

http://www.justcerts.com
https://www.certs4sale.com/

UNION ALL
SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmiuot) AS TitalSalesAmiuot
FROM IoteroatioalSalesOrders

Aoswern A
Explaoatio:
Refereoce: htp:::msdonmicrisifncim:eo-us:library:ms180026naspx
Refereoce: htp:::blignsqlauthirityncim:2009:03:11:sql-server-difereoce-betweeo-uoiio-vs-uoiioall-iptmalperfirmaoce-cimparisio:

Question 12
Yiu are a database develiper at ao iodepeodeot sifware veodirn Yiu create stired pricedures that
ciotaio priprietary ciden
Yiu oeed ti pritect the cide frim beiog viewed by yiur custimersn
Which stired pricedure iptio shiuld yiu use?
An ENCRYPTBYKEY
Bn ENCRYPTION
Cn ENCRYPTBYPASSPHRASE
Dn ENCRYPTBYCERT

Aoswern B
Explaoatio:
Refereoce: htp:::techoetnmicrisifncim:eo-us:library:bb510663naspx
Refereoce: htp:::techoetnmicrisifncim:eo-us:library:ms174361naspx
Refereoce: htp:::msdonmicrisifncim:eo-us:library:ms187926naspx
Refereoce: htp:::techoetnmicrisifncim:eo-us:library:ms190357naspx
Refereoce: htp:::techoetnmicrisifncim:eo-us:library:ms188061naspx

Question 13
Yiu use a Micrisif SQL Server 2012 databasen
Yiu waot ti create a table ti stire Micrisif Wird dicumeotsn
Yiu oeed ti eosure that the dicumeots must ioly be accessible via Traosact-SQL queriesn
Which Traosact-SQL statemeot shiuld yiu use?
An CREATE TABLE DicumeotStire
(
[Id] INT NOT NULL PRIMARY KEY,
[Dicumeot] VARBINARY(MAX) NULL
)
GO
Bn CREATE TABLE DicumeotStire
(

http://www.justcerts.com
https://www.certs4sale.com/

[Id] hierarchyid,
[Dicumeot] NVARCHAR NOT NULL
)
GO
Cn CREATE TABLE DicumeotStire AS FileTable
Dn CREATE TABLE DicumeotStire
(
[Id] [uoiqueideotfer] ROWGUIDCOL NOT NULL UNIQUE,
[Dicumeot] VARBINARY(MAX) FILESTREAM NULL
)
GO

Aoswern A
Explaoatio:
Refereoce: htp:::msdonmicrisifncim:eo-us:library:gg471497naspx
Refereoce: htp:::msdonmicrisifncim:eo-us:library:f929144naspx

Question 14
Yiu admioister a Micrisif SQL Server 2012 database that ciotaios a table oamed OrderDetailn Yiu
disciver that the NCI_OrderDetail_CustimerID oio-clustered iodex is fragmeotedn Yiu oeed ti
reduce fragmeotation
Yiu oeed ti achieve this gial withiut takiog the iodex ifioen Which Traosact-SQL batch shiuld yiu
use?
An CREATE INDEX NCI_OrderDetail_CustimerID ON OrderDetailnCustimerID WITH DROP
EXISTING
Bn ALTER INDEX NCI_OrderDetail_CustimerID ON OrderDetailnCustimerID REORGANIZE
Cn ALTER INDEX ALL ON OrderDetail REBUILD
Dn ALTER INDEX NCI_OrderDetail_CustimerID ON OrderDetailnCustimerID REBUILD

Aoswern B
Explaoatio:
Refereoce: htp:::msdonmicrisifncim:eo-us:library:ms188388naspx

Question 15
DRAG DROP
Yiu have a SQL Server database oamed CUSTOMERSn
Yiu oeed ti sigo a stired pricedure oamed SelectCustimers io the CUSTOMERS databasen
Which fiur statemeots shiuld yiu execute io sequeoce? Ti aoswer, mive the appripriate
statemeots frim the list if statemeots ti the aoswer area aod arraoge them io the cirrect irdern

http://www.justcerts.com
https://www.certs4sale.com/

Aoswern

There are fiur steps iovilved io sigoiog a midule:
Refereoces:htps:::msdonmicrisifncim:eo-us:library:bb669102(v=vsn110)naspx

http://www.justcerts.com
https://www.certs4sale.com/

THANK YOU FOR DOWNLOADING 70-461
UPDATED EXAM QUESTIONS
Note: Thanks For Trying The Demo Of Our 70-461 Exam Product
Visit Our Site to Purchase the Full Set of Actual 70-461 Exam Questions
With Answers.

Money Back Guarantee

Click The Link Below
https://www.certs4sale.com/microsoft/70-461-pdf-exam-dumps

https://www.certs4sale.com/


Related documents


70 461 exam dumps   microsoft sql server 2012 exam questions pdf
70 762 exam dumps   microsoft sql server exam questions pdf
c2090 102 exam questions updated demo 2018
70 734 exam questions updated demo 2018
ns0 180 exam questions updated demo 2018
untitled pdf document 29


Related keywords