Question 1 You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the ehibit. !Clic" the #hibit button.$ You need to dis%la& rows from the 'rders table for the Customers row havin( the Customer)d value set to 1 in the followin( *ML format+ ,hich Transact-
SQL uer& should &ou use/
1. SELECT SELECT OrderId, OrderId, OrderDat OrderDate, e, Amount, Amount, Name, Name, Country Country FROM Orders Orders INNER INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML A#TO, ELEMENTS $. SELECT SELECT OrderId, OrderId, OrderDat OrderDate, e, Amount, Amount, Name, Name, Country Country FROM Orders Orders INNER INNER JOIN Customers ON Orders.CustomerId ! Customers.CustomerId WERE Customers.CustomerId ! 1 FOR "ML RAW, ELEMENTS %. SELEC SELECT T OrderId, OrderId, OrderDat OrderDate, e, Amount, Amount, Name, Name, Country Country FROM Orders Orders INNER INNER JOIN Customers ON Orders.CustomerId ! Customers.CustomerId WERE Customers.CustomerId ! 1 FOR "ML RAW &. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId ! Customers.CustomerId WERE Customers.CustomerId ! 1 FOR "ML A#TO A#TO - '()en *. SELECT SELECT Name, Country Country,, Order+d, Order+d, OrderDate, OrderDate, Amount Amount FROM Orders Orders INNER INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML A#TO, ELEMENTS . SELECT SELECT Name, Country Country,, OrderId, OrderId, OrderDate, OrderDate, Amount Amount FROM FROM Orders INNER INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId- 1 FOR "ML A#TO A#TO . SELECT SELECT Name AS AS Customers/ Customers/Name, Name, Country Country AS Custome Customers/C rs/Country ountry,, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML 0AT Customers2 3. SELECT SELECT Name AS 4Name 4Name,, Country AS 4Countr 4Country, y, OrderId, OrderId, OrderDa OrderDate, te, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML 0AT Customers2
Question 2 You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the ehibit. !Clic" the #hibit button.$ You need to dis%la& rows from the 'rders table for the Customers row havin( the
Customer)d value set to 1 in the followin( *ML format. ,hich Transact-
SQL uer& should &ou use/
1. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML A#TO, ELEMENTS $. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId ! Customers.CustomerId WERE Customers.CustomerId ! 1 FOR "ML RAW, ELEMENTS %. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId ! Customers.CustomerId WERE Customers.CustomerId ! 1 FOR "ML A#TO - '()en &. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId ! Customers.CustomerId WERE Customers.CustomerId ! 1 FOR "ML RAW *. SELECT Name, Country, Order+d, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML A#TO, ELEMENTS . SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML A#TO . SELECT Name AS 4Name, Country AS 4Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML 0AT Customers2 3. SELECT Name AS Customers/Name, Country AS Customers/Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML 0AT Customers2
Question You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the ehibit. !Clic" the #hibit button.$ You need to dis%la& rows from the 'rders table for the Customers row havin( the Customer)d value set to 1 in the followin( *ML format. ,hich Transact-
SQL uer& should &ou use/
1. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId ! Customers.CustomerId WERE Customers.CustomerId ! 1 FOR "ML RAW, ELEMENTS $. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId ! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML A#TO, ELEMENTS %. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId ! Customers.CustomerId WERE Customers.CustomerId ! 1 FOR "ML RAW &. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId ! Customers.CustomerId WERE Customers.CustomerId ! 1 FOR "ML A#TO *. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId- 1 FOR "ML A#TO . SELECT Name, Country, Order+d, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML A#TO, ELEMENTS - '()en . SELECT Name AS Customers/Name, Country AS Customers/Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML 0AT Customers2 3. SELECT Name AS 4Name, Country AS 4Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId! Customers.CustomerId WERE Customers.CustomerId! 1 FOR "ML 0AT Customers2
Question You develo% a Microsoft SQL Server 2012 server database that su%%orts an a%%lication. The a%%lication contains a table that has the followin( definition+ C#3T# T34L# )nventor& ! )tem)D int 5'T 56LL 7)M3Y 8#Y9 )tems)nStore int 5'T 56LL9 )tems)n,arehouse int 5'T 56LL$ You need to create a com%uted column that returns the sum total of the )tems)nStore and )tems)n,arehouse values for each row. The new column is e%ected to be ueried heavil&9 and &ou need to be able to inde the column. ,hich Transact-SQL statement should &ou use/
1. ALTER TA5LE In)entory ADD Tota+Items AS ItemsInStore 6 ItemsInWare7ouse 0ERSISTED - '()en $. ALTER TA5LE In)entory ADD Tota+Items AS S#MItemsInStore, ItemsInWare7ouse2 0ERSISTED
%. ALTER TA5LE In)entory ADD Tota+Items AS Items+nStore 6 ItemsInWare7ouse &. ALTER TA5LE In)entory ADD Tota+Items AS S#MItemsInStore, ItemsInWare7ouse2
Question : You develo% a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the followin( definition+ You need to create an audit record onl& when either the Mobile5umber or ;ome5umber column is u%dated. ,hich Transact-SQL uer& should &ou
use/
1. CREATE TRI''ER Tr807oneNum9erC7an8e ON Customers FOR #0DATE AS IF #0DATE omeNum9er2 OR #0DATE Mo9(+eNum9er2 - - Create Aud(t Re:ords - '()en $. CREATE TRI''ER Tr807oneNum9erC7an8e ON Customers FOR #0DATE AS IF E"ISTS SELECT omeNum9er FROM (nserted2 OR E"ISTS SELECT Mo9(+eNum9er FROM (nserted2 - - Create Aud(t Re:ords %. CREATE TRI''ER Tr807oneNum9erC7an8e ON Customers FOR #0DATE AS IF COL#MNS;CAN'ED omeNum9er, Mo9(+eNum9er2 - - Create Aud(t Re:ords &. CREATE TRI''ER Tr807oneNum9erC7an8e ON Customers FOR #0DATE AS IF COL#MNS;#0DATED omeNum9er, Mo9(+eNum9er2 - - Create Aud(t Re:ords
Question < You develo% a Microsoft SQL Server 2012 database. You create a view that %erforms the followin( tas"s+ =oins > tables that contain u% to :009000 records each. 7erforms a((re(ations on : fields. The view is freuentl& used in several re%orts. You need to im%rove the %erformance of the re%orts. ,hat should
&ou do/
1. $. %. &.
Con)ert t7e )(e< (nto an (nde=ed )(e<. - '()en Con)ert t7e )(e< (nto a Common Ta9+e E=>ress(on CTE2. Con)ert t7e )(e< (nto a ta9+e-)a+ued ?un:t(on. Con)ert t7e )(e< (nto a stored >ro:edure and retr(e)e t7e resu+t ?rom t7e stored >ro:edure (nto a tem>orary ta9+e.
Question ?
You have three tables that contain data for dentists9 %s&chiatrists9 and %h&sicians. You create a view that is used to loo" u% their email addresses and %hone numbers. The view has the followin( definition+ You need to ensure that users can u%date onl& the %hone numbers and email addresses b& usin( this
view. ,hat should &ou do/
1. Create an INSTEAD OF #0DATE tr(88er on t7e )(e<. - '()en $. A+ter t7e )(e<. #se t7e E"0AND @IEWS uery 7(nt a+on8 <(t7 ea:7 SELECT statement. %. Create an AFTER #0DATE tr(88er on t7e )(e<. &. Dro> t7e )(e<. Re-:reate t7e )(e< 9y us(n8 t7e SCEMA5INDIN' :+ause, and t7en :reate an (nde= on t7e )(e<.
Question > )ntroduced in SQL Server 20129 a S#Q6#5C# is a user-defined schema-bound ob@ect that (enerates a seuence of numeric values accordin( to the s%ecification with which the seuence was created. The seuence of numeric values is (enerated in an ascendin( or descendin( order at a defined interval and ma& c&cle !re%eat$ as reuested. Aiven the followin( seuence definition+ C#3T# S#Q6#5C# Bdbo.BSQLSeuence 3S T)5Y)5T M3*3L6#
CYCL#
1. $. %. &. *. .
1BB $BB %BB N#LL De>ends on t7e out>ut o? t7e NE"T @AL#E FOR ?un:t(on. None o? t7e a9o)e. - '()en
Question E You use Microsoft SQL Server 2012 database to develo% a sho%%in( cart a%%lication. You need to invo"e a table-valued function for each row returned b& a
uer&. ,hich Transact-SQL o%erator should &ou use/
1. $. %. &.
CROSS A00L - '()en #N0I@OT 0I@OT CROSS JOIN
Question 10 You su%%ort a database structure shown in the ehibit. !Clic" the #hibit button.$ You need to write a uer& that dis%la&s the followin( details+ Total sales made b& sales %eo%le9 &ear9 cit&9 and countr& Sub totals onl& at the cit& level and countr& level 3 (rand total of the sales amount ,hich Transact-SQL
uer& should &ou use/
1. SELECT Sa+es0erson.Name, Country, C(ty, Date0artyyyy, Sa+eDate2 AS ear, SumAmount2 AS Tota+ FROM Sa+e INNER JOIN Sa+es0erson ON Sa+e.Sa+es0ersonID ! Sa+es0erson.Sa+es0ersonID 'RO#0 5 C#5ESa+es0erson.Name, Country, C(ty, Date0artyyyy, Sa+eDate22 $. SELECT Sa+es0erson.Name, Country, C(ty, Date0artyyyy, Sa+eDate2 AS ear, SumAmount2 AS Tota+ FROM Sa+e INNER JOIN Sa+es0erson ON Sa+e.Sa+es0ersonID ! Sa+es0erson.Sa+es0ersonID 'RO#0 5 ROLL#0Sa+es0erson.Name, Date0artyyyy, Sa+eDate2, C(ty, Country2 %. SELECT Sa+es0erson.Name, Country, C(ty, Date0artyyyy, Sa+eDate2 AS ear, SumAmount2 AS Tota+ FROM Sa+e INNER JOIN Sa+es0erson ON Sa+e.Sa+es0ersonID ! Sa+es0erson.Sa+es0ersonID 'RO#0 5 C#5ESa+es0erson.Name, Date0artyyyy, Sa+eDate2, C(ty, Country2 &. SELECT Sa+es0erson.Name, Country, C(ty, Date0artyyyy, Sa+eDate2 AS ear, SumAmount2 AS Tota+ FROM Sa+e INNER JOIN Sa+es0erson ON Sa+e.Sa+es0ersonID ! Sa+es0erson.Sa+es0ersonID 'RO#0 5 'RO#0IN' SETSSa+es0erson.Name, Country, C(ty, Date0artyyyy, Sa+eDate22, C(ty2, Country2, 22 - '()en
Question 11 You administer a Microsoft SQL Server database that su%%orts a ban"in( transaction mana(ement a%%lication. You need to retrieve a list of account holders who live in cities that do not have a branch location. ,hich Transact-SQL uer& or ueries should &ou use/ !#ach correct answer
%resents a com%lete solution. Choose all that a%%l&.$
1. SELECT A::ounto+derID FROM A::ounto+der WERE C(tyID ALL SELECT C(tyID FROM 5ran:7Master2 - '()en $. SELECT A::ounto+der+D FROM A::ounto+der WERE C(tyID SOME SELECT C(tyID FROM 5ran:7Master2 %. SELECT A::ounto+derID FROM A::ounto+der WERE C(tyID NOT IN SELECT C(tyID FROM 5ran:7Master2 - '()en &. SELECT A::ounto+derID FROM A::ounto+der WERE C(tyID AN SELECT C(tyID FROM 5ran:7Master2
Question 12 You administer a Microsoft SQL Server 2012 database. The database contains a table named #m%lo&ee. 7art of the #m%lo&ee table is shown in the ehibit. !Clic" the #hibit button.$ Confidential information about the em%lo&ees is stored in a se%arate table named #m%lo&eeData. 'ne record eists within #m%lo&eeData for each record in the #m%lo&ee table. You need to assi(n the a%%ro%riate constraints and table %ro%erties to ensure data inte(rit& and visibilit&. 'n which column in the #m%lo&ee table should &ou use an identit& s%ecification to include a seed of 19000 and an increment of 1/
1. $. %. &. *. . . 3. .
Jo9T(t+e F(rstName Em>+oyeeID - '()en Date(red Em>+oyeeNum De>artmentID M(dd+eName LastName Re>ortsToID
Question 1 You develo% three Microsoft SQL Server 2012 databases named Database19 Database29 and Database. You have %ermissions on both Database1 and Database2. You %lan to write and de%lo& a stored %rocedure named dbo.us%F)nsert#vent in Database. dbo.us%F)nsert#vent must eecute other stored %rocedures in the other databases. You need to ensure that callers that do not have %ermissions on Database1 or Database2 can eecute
the stored %rocedure. ,hich Transact-SQL statement should &ou use/
1. $. %. &.
#SE Data9ase1 E"EC#TE AS OWNER - '()en E"EC#TE AS CALLER #SE Data9ase$
Question 1 You administer a database that includes a table named Customers that contains more than ?:0 rows. You create a new column named 7artition5umber of the int t&%e in the table. You need to assi(n a 7artition5umber for each record in the Customers table. You also need to ensure that the 7artition5umber satisfies the followin( conditions+ 3lwa&s starts with 1. Starts a(ain from 1 after it reaches 100. ,hich Transact-SQL statement
should &ou use/
1. CREATE SEG#ENCE CustomerSeuen:e AS (nt START WIT 1 INCREMENT 5 1 MIN@AL#E B MA"@AL#E 1BB CCLE #0TATE Customers SET 0art(t(onNum9er ! NE"T @AL#E FOR CustomerSeuen:e DRO0 SEG#ENCE CustomerSeuen:e $. CREATE SEG#ENCE CustomerSeuen:e AS (nt START WIT 1 INCREMENT 5 1 MIN@AL#E 1 MA"@AL#E 1BB CCLE #0DATE Customers SET 0art(t(onNum9er ! NE"T @AL#E FOR CustomerSeuen:e DRO0 SEG#ENCE CustomerSeuen:e - '()en %. CREATE SEG#ENCE CustomerSeuen:e AS (nt START WIT 1 INCREMENT 5 1 MIN@AL#E 1 MA"@AL#E 1BB #0DATE Customers SET 0art(t(onNum9er ! NE"T @AL#E FOR CustomerSeuen:e 6 1 DRO0 SEG#ENCE CustomerSeuen:e &. CREATE SEG#ENCE CustomerSeuen:e AS (nt START WIT B INCREMENT 5 1 MIN@AL#E 1 MA"@AL#E 1BB #0DATE Customers SET 0art(t(onNum9er ! NE"T @AL#E FOR CustomerSeuen:e DRO0 SEG#ENCE CustomerSeuen:e
Question 1: You use Microsoft SQL Server 2012 to develo% a database a%%lication. You create a stored %rocedure named Delete=obCandidate. You need to ensure that if Delete=obCandidate encounters an error9 the eecution of the stored
%rocedure re%orts the error number. ,hich Transact-SQL statement should
&ou use/
1. E"EC De+eteJo9Cand(date 0RINT NError ! 6 CAST44ERROR AS N@ARCAR322 6 N, Ro
Question 1< 3 table named 7rofits stores the total %rofit made each &ear within a territor&. The 7rofits table has columns named Territor&9 Year9 and 7rofit. You need to create a re%ort that dis%la&s the %rofits made b& each territor& for each &ear and its %recedin( &ear. ,hich Transact-SQL uer& should &ou use/
1. SELECT Terr(tory, ear, 0ro?(t, LEAD0ro?(t, 1, B2 O@ER0ARTITION 5 Terr(tory ORDER 5 ear2 AS Ne=t0ro?(t FROM 0ro?(ts $. SELECT Terr(tory, ear, 0ro?(t, LA'0ro?(t, 1, B2 O@ER0ARTITION 5 ear ORDER 5 Terr(tory2 AS Ne=t0ro?(t FROM 0ro?(ts %. SELECT Terr(tory, ear, 0ro?(t, LEAD0ro?(t, 1, B2 O@ER0ARTITION 5 ear ORDER 5 Terr(tory2 AS Ne=t0ro?(t FROM 0ro?(ts &. SELECT Terr(tory, ear, 0ro?(t, LA'0ro?(t, 1, B2 O@ER0ARTITION 5 Terr(tory ORDER 5 ear2 AS Ne=t0ro?(t FROM 0ro?(ts - '()en
Question 1?
You use Microsoft SQL Server 2012 to develo% a database a%%lication. Your a%%lication sends data to an 53C;3!M3*$ variable named Gvar. You need to write a Transact-SQL statement that will find out the success of a cast to a decimal !<9E$. ,hich code se(ment should &ou use/select
1. SELECT IIFTR;0ARSE4)ar AS de:(ma+%,22 IS N#LL, True, Fa+se2 AS 5adCast - '()en $. TR SELECT :on)ertde:(ma+%,2, 4)ar2 SELECT True AS 5adCast 2 CATC SELECT Fa+se AS 5adCast 2 %. SELECT CASE WEN :on)ertde:(ma+%,2, 4)ar2 IS N#LL TEN True ELSE Fa+se END AS 5adCast &. 5E'IN TR SELECT :on)ertde:(ma+%,2, 4)ar2 AS @a+ue, True AS 5adCast END TR 5E'IN CATC SELECT :on)ertde:(ma+%,2, 4)ar2 AS @a+ue, Fa+se AS 5adCast END CATC
Question 1> You are writin( a set of ueries a(ainst a H)L#ST#3M-enabled database. You create a stored %rocedure that will u%date multi%le tables within a transaction. You need to ensure that if the stored %rocedure raises a runtime error9 the entire transaction is terminated and rolled bac". ,hich Transact-SQL statement should &ou include at the be(innin( of the stored
%rocedure/
1. $. %. &. *. .
SET IM0LICIT;TRANSACTIONS ON SET TRANSACTION ISOLATION LE@EL SNA0SOT SET TRANSACTION ISOLATION LE@EL SERIALIA5LE SET "ACT;A5ORT OFF SET "ACT;A5ORT ON - '()en SET IM0LICIT TRANSACTIONS OFF
Question 1E You create a stored %rocedure that will u%date multi%le tables within a transaction. You need to ensure that if the stored %rocedure raises a run-time error9 the entire transaction is terminated and rolled bac". ,hich Transact-SQL
statement should &ou include at the be(innin( of the stored %rocedure/
1. $. %. &. *. .
SET ARITA5ORT OFF SET ARITA5ORT ON TR SET "ACT;A5ORT ON - '()en 5E'IN SET "ACT;A5ORT OFF
Question 20 You are a database develo%er at an inde%endent software vendor. You create stored %rocedures that contain %ro%rietar& code. You need to %rotect the code from bein( viewed b& &our customers. ,hich stored %rocedure o%tion
should &ou use/
1. $. %. &.
ENCR0T50ASS0RASE ENCR0T5CERT ENCR0TION - '()en ENCR0T5KE
