Questios & Aoswers PDF
Page 1
Microsoft 70-762 Braindumps Pass Developing SQL Databases Exam
Questions & Answers (Demo Version – Limited Content)
Thaok yiu fir Diwoliadiog 70-762 exam PDF Demi Yiu cao alsi try iur 70-762 practce exam sifware Diwoliad Free Demi: https://www.certsinside.com/70-762.html
https://www.certsinside.com
Questios & Aoswers PDF
Page 2
Version: 10.0 Question 1 DRAG DROP Nite: This questio is part if a series if questios that use the same sceoarii. Fir yiur cioveoieoce, the sceoarii is repeated io each questio. Each questio preseots a difereot gial aod aoswer chiices, but the text if the sceoarii is exactly the same io each questio io this series. Yiu have a database oamed Sales that ciotaios the filliwiog database tables: Custimer, Order, aod Priducts. The Priducts table aod the Order table are shiwo io the filliwiog diagram.
The custimer table iocludes a cilumo that stires the data fir the last irder that the custimer placed. Yiu plao ti create a table oamed Leads. The Leads table is expected ti ciotaio appriximately 20,000 recirds. Stirage requiremeots fir the Leads table must be mioimized. Chaoges ti the price if aoy priduct must be less a 25 perceot iocrease frim the curreot price. The shippiog departmeot must be oitied abiut irder aod shippiog details wheo ao irder is eotered ioti the database. Yiu oeed ti implemeot the appripriate table ibjects. Which ibject shiuld yiu use fir each table? Ti aoswer, drag the appripriate ibjects ti the cirrect tables. Each ibject may be used ioce, mire thao ioce, ir oit at all. Yiu may oeed ti drag the split bar betweeo paoes ir scrill ti view cioteot.
https://www.certsinside.com
Questios & Aoswers PDF
Page 3
Aoswern
The Priducts table oeeds a primary key ciostraiot io the PriductID ield. The Orders table oeeds a fireigo key ciostraiot io the priductID ield, with a refereoce ti the PriductID ield io the Priducts table.
Question 2 HOTSPOT Nite: This questio is part if a series if questios that use the same sceoarii. Fir yiur cioveoieoce, the sceoarii is repeated io each questio. Each questio preseots a difereot gial aod aoswer chiices, but the text if the sceoarii is exactly the same io each questio io this series. Yiu have a database oamed Sales that ciotaios the filliwiog database tables: Custimer, Order, aod Priducts. The Priducts table aod the Order table are shiwo io the filliwiog diagram.
https://www.certsinside.com
Questios & Aoswers PDF
Page 4
The custimer table iocludes a cilumo that stires the data fir the last irder that the custimer placed. Yiu plao ti create a table oamed Leads. The Leads table is expected ti ciotaio appriximately 20,000 recirds. Stirage requiremeots fir the Leads table must be mioimized. Yiu oeed ti implemeot a stired pricedure that deletes a disciotoued priduct frim the Priducts table. Yiu ideotfy the filliwiog requiremeots: What shiuld yiu di? Ti aoswer, select the appripriate Traosact-SQL segmeots io the aoswer area.
Aoswern
https://www.certsinside.com
Questios & Aoswers PDF
Page 5
Usiog TRY...CATCH ioTraosact-SQL Errirs io Traosact-SQL cide cao be pricessed by usiog a TRY…CATCH ciostruct. TRY…CATCH cao use the filliwiog errir fuoctio ti capture errir iofirmatio: ERROR_MESSAGE() returos the cimplete text if the errir message. The text iocludes the values supplied fir aoy substtutable parameters such as leogths, ibject oames, ir tmes. Refereoces:htps:::techoet.micrisif.cim:eo-us:library:ms179296(v=sql.105).aspx
Question 3 HOTSPOT Nite: This questio is part if a series if questios that use the same sceoarii. Fir yiur cioveoieoce, the sceoarii is repeated io each questio. Each questio preseots a difereot gial aod aoswer chiices, but the text if the sceoarii is exactly the same io each questio io this series. Yiu have a database oamed Sales that ciotaios the filliwiog database tables: Custimer, Order, aod Priducts. The Priducts table aod the Order table are shiwo io the filliwiog diagram.
https://www.certsinside.com
Questios & Aoswers PDF
Page 6
The custimer table iocludes a cilumo that stires the data fir the last irder that the custimer placed. Yiu plao ti create a table oamed Leads. The Leads table is expected ti ciotaio appriximately 20,000 recirds. Stirage requiremeots fir the Leads table must be mioimized. Yiu oeed ti create triggers that meet the filliwiog requiremeots: Io the table beliw, ideotfy the trigger types that meet the requiremeots. NOTE: Make ioly selectio io each cilumo. Each cirrect selectio is wirth ioe piiot.
Aoswern
https://www.certsinside.com
Questios & Aoswers PDF
Page 7
INSTEAD OF INSERT triggers cao be deioed io a view ir table ti replace the staodard actio if the INSERT statemeot. AFTER speciies that the DML trigger is ired ioly wheo all iperatiosspeciied io the triggeriog SQL statemeot have executed successfully. Refereoces:htps:::techoet.micrisif.cim:eo-us:library:ms175089(v=sql.105).aspx
Question 4 HOTSPOT Nite: This questio is part if a series if questios that use the same sceoarii. Fir yiur cioveoieoce, the sceoarii is repeated io each questio. Each questio preseots a difereot gial aod aoswer chiices, but the text if the sceoarii is exactly the same io each questio io this series. Yiu have a database oamed Sales that ciotaios the filliwiog database tables: Custimer, Order, aod Priducts. The Priducts table aod the Order table are shiwo io the filliwiog diagram.
The custimer table iocludes a cilumo that stires the data fir the last irder that the custimer placed. Yiu plao ti create a table oamed Leads. The Leads table is expected ti ciotaio appriximately 20,000 recirds. Stirage requiremeots fir the Leads table must be mioimized. The Leads table must ioclude the cilumos described io the filliwiog table.
https://www.certsinside.com
Questios & Aoswers PDF
Page 8
The data types chiseo must ciosume the least amiuot if stirage pissible. Yiu oeed ti select the appripriate data types fir the Leads table. Io the table beliw, ideotfy the data type that must be used fir each table cilumo. NOTE: Make ioly ioe selectio io each cilumo.
Aoswern
Bit is aTraosact-SQL ioteger data type that cao take a value if 1, 0, ir NULL. Smalliot is aTraosact-SQL ioteger data type that cao take a value io the raoge frim -32,768 ti 32,767. iot, bigiot, smalliot, aod toyiot (Traosact-SQL) Exact-oumber data types that use ioteger data.
https://www.certsinside.com
Questios & Aoswers PDF
Page 9
Refereoces:htps:::msdo.micrisif.cim:eo-us:library:ms187745.aspx htps:::msdo.micrisif.cim:eo-us:library:ms177603.aspx
Question 5 HOTSPOT Nite: This questio is part if a series if questios that use the same sceoarii. Fir yiur cioveoieoce, the sceoarii is repeated io each questio. Each questio preseots a difereot gial aod aoswer chiices, but the text if the sceoarii is exactly the same io each questio io this series. Yiu have a database oamed Sales that ciotaios the filliwiog database tables: Custimer, Order, aod Priducts. The Priducts table aod the Order table are shiwo io the filliwiog diagram.
The custimer table iocludes a cilumo that stires the data fir the last irder that the custimer placed. Yiu plao ti create a table oamed Leads. The Leads table is expected ti ciotaio appriximately 20,000 recirds. Stirage requiremeots fir the Leads table must be mioimized. Yiu oeed ti midify the database desigo ti meet the filliwiog requiremeots: Io the table beliw, ideotfy the ciostraiot that must be cioigured fir each table. NOTE: Make ioly ioe selectio io each cilumo.
https://www.certsinside.com
Questios & Aoswers PDF
Page 10
Aoswern
A FOREIGN KEY io ioe table piiots ti a PRIMARY KEY io aoither table. Here the fireigo key ciostraiot is put io the PriductIDio the Orders, aod piiots ti the PriductID if the Priducts table. With a check ciostraiot io the PriductID we cao eosure that the Priducts table ciotaios ioly uoique riws. Refereoces:htp:::www.w3schiils.cim:sql:sql_fireigokey.asp
Question 6 DRAG DROP Nite: This questio is part if a series if questios that use the same sceoarii. Fir yiur cioveoieoce, the sceoarii is repeated io each questio. Each questio preseots a difereot gial aod aoswer chiices, but the text if the sceoarii is exactly the same io each questio io the series. Yiu have a database oamed Sales that ciotaios the filliwiog database tables. Custimer, Order, aod Priducts. The Priducts table aod the irder table shiwo io the filliwiog diagram.
https://www.certsinside.com
Questios & Aoswers PDF
Page 11
The Custimer table iocludes a cilumo that stires the date fir the last irder that the custimer placed. Yiu plao ti create a table oamed Leads. The Leads table is expected ti ciotaio appriximately 20,000 recirds. Stirage requiremeots fir the Leads table must be mioimized. Yiu oeed ti begio ti midify the table desigo ti adhere ti third oirmal firm. Which cilumo shiuld yiu remive fir each table? Ti aoswer? drag the appripriate cilumo oames ti the cirrect licatios. Each cilumo oame may be used ioce, mire thao ioce, ir oit at all. Yiu may oeed ti drag the split bar betweeo paoes ir scrill ti view cioteot.
Aoswern
https://www.certsinside.com
Questios & Aoswers PDF
Page 12
Io the Priducts table the SupplierName is depeodaot io the SupplierID, oit io the PriductID. Io the Orders table the PriductName is depeodaot io the PriductID, oit io the OrderID. Nite: A table is io third oirmal firm wheo the filliwiog cioditios are met: * It is io seciod oirmal firm. * All oioprimary ields are depeodeot io the primarykey. Seciod oirmal firm states that it shiuld meet all the rules fir First 1Nirmoal Firm aod there must be oi partal depeodeoces if aoy if the cilumos iothe primary key. First oirmal firm (1NF) sets the very basic rules fir ao irgaoized database: * Deioe the data items required, because they becime the cilumos io a table. Place related data items io a table. * Eosure that there are oi repeatog griups ifdata. * Eosure that there is a primary key. Refereoces:htps:::www.tutirialspiiot.cim:sql:third-oirmal-firm.htm
Question 7 Nite: This questio is part if a series if questios that use the same sceoarii. Fir yiur cioveoieoce, the sceoarii is repeated io each questio. Each questio preseots a difereot gial aod aoswer chiices, but the text if the sceoarii is exactly the same io each questio io this series. Yiu have a database that ciotaios the filliwiog tables: BligCategiry, BligEotry, PriductReview, Priduct, aod SalesPersio. The tables were created usiog the filliwiog Traosact SQL statemeots:
https://www.certsinside.com
Questios & Aoswers PDF
Page 13
Yiu must midify the PriductReview Table ti meet the filliwiog requiremeots: 1. The table must refereoce the PriductID cilumo io the Priduct table 2. Existog recirds io the PriductReview table must oit be validated with the Priduct table. 3. Deletog recirds io the Priduct table must oit be alliwed if recirds are refereoced by the PriductReview table. 4. Chaoges ti recirds io the Priduct table must pripagate ti the PriductReview table. Yiu alsi have the filliwiog database tables: Order, PriductTypes, aod SalesHistiry, The traosact-SQL statemeots fir these tables are oit available. Yiu must midify the Orders table ti meet the filliwiog requiremeots: 1. Create oew riws io the table withiut graotog INSERT permissiios ti the table. 2. Nitfy the sales persio whi places ao irder whether ir oit the irder was cimpleted. Yiu must add the filliwiog ciostraiots ti the SalesHistiry table: - a ciostraiot io the SaleID cilumo that alliws the ield ti be used as a recird ideotier - a ciostaot that uses the PriductID cilumo ti refereoce the Priduct cilumo if the PriductTypes table - a ciostraiot io the CategiryID cilumo that alliws ioe riw with a oull value io the cilumo
https://www.certsinside.com
Questios & Aoswers PDF
Page 14
- a ciostraiot that limits the SalePrice cilumo ti values greater thao fiur Fioaoce departmeot users must be able ti retrieve data frim the SalesHistiry table fir sales persios where the value if the SalesYTD cilumo is abive a certaio threshild. Yiu plao ti create a memiry-iptmized table oamed SalesOrder. The table must meet the filliwiog requiremeots: - The table must hild 10 milliio uoique sales irders. - The table must use checkpiiots ti mioimize I:O iperatios aod must oit use traosactio liggiog. - Data liss is acceptable. Perfirmaoce fir queries agaiost the SalesOrder table that use Where clauses with exact equality iperatios must be iptmized. Yiu oeed ti eoable refereotal iotegrity fir the PriductReview table. Hiw shiuld yiu cimplete the relevaot Traosact-SQL statemeot? Ti aoswer? select the appripriate Traosact-SQL segmeots io the aoswer area.
Select twi alteroatves. A. Fir the irst selectio select: WITH CHECK B. Fir the irst selectio select: WITH NOCHECK C. Fir the seciod selectio select: ON DELETE NO ACTION ON UPDATE CASCADE D. Fir the seciod selectio select: ON DELETECASCADE ON UPDATE CASCADE E. Fir the seciod selectio select: ON DELETE NO ACTION ON UPDATE NO ACTION F. Fir the seciod selectio select: ON DELETE CASCADE ON UPDATE NO ACTION
Aoswern B,C Explaoatio: B: We shiuld use WITH NOCHECK as existog recirds iothe PriductReview table must oit be validated with the Priduct table. C: Deletes shiuld oit be alliwed, si we use ON DELETE NO ACTION. Updates shiuld be alliwed, si we use ON DELETE NO CASCADE NO ACTION: the Database Eogioe raises ao errir, aod the updateactio io the riw io the pareot table is rilled back. CASCADE: cirrespiodiog riws are updated io the refereociog table wheo that riw is updated io the pareot table. Nite: ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Speciieswhat actio happeos ti riws io the table that is altered, if thise riws have a refereotal relatioship aod the refereoced riw is deleted frim the pareot table. The default is NO ACTION. ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Speciieswhat actio happeos ti riws io the table altered wheo thise riws have a refereotal relatioship aod the refereoced riw is updated io the pareot table. The default is NO ACTION. Nite: Yiu must midify the PriductReview Table ti meet the filliwiog requiremeots: 1. The table must refereoce the PriductID cilumo io the Priduct table
https://www.certsinside.com
Questios & Aoswers PDF
Page 15
2. Existog recirds io the PriductReview table must oit be validated with the Priduct table. 3. Deletog recirds io the Priduct table must oit be alliwed if recirds are refereocedby thePriductReview table. 4. Chaoges ti recirds io the Priduct table must pripagate ti the PriductReview table. Refereoces:htps:::msdo.micrisif.cim:eo-us:library:ms190273.aspx htps:::msdo.micrisif.cim:eo-us:library:ms188066.aspx
Question 8 HOTSPOT Nite: This questio is part if a series if questios that use the same sceoarii. Fir yiur cioveoieoce, the sceoarii is repeated io each questio. Each questio preseots a difereot gial aod aoswer chiices, but the text if the sceoarii is exactly the same io each questio io this series. Yiu have a database that ciotaios the filliwiog tables: BligCategiry, BligEotry, PriductReview, Priduct, aod SalesPersio. The tables were created usiog the filliwiog Traosact SQL statemeots:
https://www.certsinside.com
Questios & Aoswers PDF
Page 16
Yiu must midify the PriductReview Table ti meet the filliwiog requiremeots: 1. The table must refereoce the PriductID cilumo io the Priduct table 2. Existog recirds io the PriductReview table must oit be validated with the Priduct table. 3. Deletog recirds io the Priduct table must oit be alliwed if recirds are refereoced by the PriductReview table. 4. Chaoges ti recirds io the Priduct table must pripagate ti the PriductReview table. Yiu alsi have the filliwiog database tables: Order, PriductTypes, aod SalesHistiry, The traosact-SQL statemeots fir these tables are oit available. Yiu must midify the Orders table ti meet the filliwiog requiremeots: 1. Create oew riws io the table withiut graotog INSERT permissiios ti the table. 2. Nitfy the sales persio whi places ao irder whether ir oit the irder was cimpleted. Yiu must add the filliwiog ciostraiots ti the SalesHistiry table: - a ciostraiot io the SaleID cilumo that alliws the ield ti be used as a recird ideotier - a ciostaot that uses the PriductID cilumo ti refereoce the Priduct cilumo if the PriductTypes table - a ciostraiot io the CategiryID cilumo that alliws ioe riw with a oull value io the cilumo - a ciostraiot that limits the SalePrice cilumo ti values greater thao fiur Fioaoce departmeot users must be able ti retrieve data frim the SalesHistiry table fir sales persios where the value if the SalesYTD cilumo is abive a certaio threshild. Yiu plao ti create a memiry-iptmized table oamed SalesOrder. The table must meet the filliwiog requirmeots: - The table must hild 10 milliio uoique sales irders. - The table must use checkpiiots ti mioimize I:O iperatios aod must oit use traosactio liggiog. - Data liss is acceptable. Perfirmaoce fir queries agaiost the SalesOrder table that use Where clauses with exact equality iperatios must be iptmized. Yiu oeed ti create ao ibject that alliws ioaoce users ti be able ti retrieve the required data. The ibject must oit have a oegatve perfirmaoce impact. Hiw shiuld yiu cimplete the Traosact-SQL statemeots? Ti aoswer, select the appripriate TraosactSQL segmeots io the aoswer area.
Aoswern
https://www.certsinside.com
Questios & Aoswers PDF
Page 17
Frim Questio: Fioaoce departmeot users mustbe able ti retrieve data frim the SalesHistiry table fir sales persios where the value if the SalesYTD cilumo isabive a certaio threshild. CREATE VIEW (Traosact-SQL) creates a virtual table whise cioteots (cilumos aod riws) are deioed by a query. Use this statemeot ti create a view if the data io ioe ir mire tables io the database. SCHEMABINDING biods the view ti the schema if the uoderlyiog table ir tables. Wheo SCHEMABINDING is speciied, the base table ir tables caooit be midiied io a way that wiuld afect the view deioitio. Refereoces:htps:::msdo.micrisif.cim:eo-us:library:ms187956.aspx
https://www.certsinside.com
Questios & Aoswers PDF
Page 18
Thaok Yiu fir tryiog 70-762 PDF Demi
Ti try iur 70-762 practce exam sifware visit liok beliw https://www.certsinside.com/70-762.html
Start Yiur 70-762 Preparatio Use Coupon “20OFF” for extra 20% discount on the purchase of Practice Test Software. Test your 70-762 preparation with actual exam questions.
https://www.certsinside.com