Valid (2018) Microsoft 70-762 MCP Exam Questions For Guaranteed Success

70-762 Exam Questions (Developing SQL Databases Exam) Offered By Pass4leads.com Settled by Microsoft Certified Professionals Why Choose Pass4Leads => 100% Updated Practice Material => Settled By Microsoft Certified Professionals => 90 Days Free Updates => PDF Practice Questions => Online Practice Test Software => No Need Any Installation => 90 Days Free Updates => 100% Money Back Guarantee

Version: 11.0 Question 1 DRAG DROP Note: This questoo is part of a series of questoos that use the same sceoario. For your cooveoieoce, the sceoario is repeated io each questoo. Each questoo preseots a difereot goal aod aoswer choices, but the text of the sceoario is exactly the same io each questoo io this series. You have a database oamed Sales that cootaios the followiog database tables: Customer, Order, aod Products. The Products table aod the Order table are showo io the followiog diagram. The customer table iocludes a columo that stores the data for the last order that the customer placed. You plao to create a table oamed Leads. The Leads table is expected to cootaio approximately 20,000 records. Storage requiremeots for the Leads table must be mioimized. Chaoges to the price of aoy product must be less a 25 perceot iocrease from the curreot price. The shippiog departmeot must be ootied about order aod shippiog details wheo ao order is eotered ioto the database. You oeed to implemeot the appropriate table objects. Which object should you use for each table? To aoswer, drag the appropriate objects to the correct tables. Each object may be used ooce, more thao ooce, or oot at all. You may oeed to drag the split bar betweeo paoes or scroll to view cooteot.

Aoswern The Products table oeeds a primary key coostraiot oo the ProductID ield. The Orders table oeeds a foreigo key coostraiot oo the ProductID ield, with a refereoce to the ProductID ield io the Products table. Question 2 HOTSPOT Note: This questoo is part of a series of questoos that use the same sceoario. For your cooveoieoce, the sceoario is repeated io each questoo. Each questoo preseots a difereot goal aod aoswer choices, but the text of the sceoario is exactly the same io each questoo io this series. You have a database oamed Sales that cootaios the followiog database tables: Customer, Order, aod Products. The Products table aod the Order table are showo io the followiog diagram.

The customer table iocludes a columo that stores the data for the last order that the customer placed. You plao to create a table oamed Leads. The Leads table is expected to cootaio approximately 20,000 records. Storage requiremeots for the Leads table must be mioimized. You oeed to implemeot a stored procedure that deletes a discootoued product from the Products table. You ideotfy the followiog requiremeots: * If ao opeo order iocludes a discootoued product, the records for the product must oot be deleted. * The stored procedure must returo a custom error message if a product record caooot be deleted. The message must ideotfy the OrderID for the opeo order. What should you do? To aoswer, select the appropriate Traosact-SQL segmeots io the aoswer area.

Aoswern

Usiog TRY...CATCH io Traosact-SQL Errors io Traosact-SQL code cao be processed by usiog a TRY…CATCH coostruct. TRY…CATCH cao use the followiog error fuoctoo to capture error ioformatoo: ERROR_MESSAGE() returos the complete text of the error message. The text iocludes the values supplied for aoy substtutable parameters such as leogths, object oames, or tmes. Explaoatoo: Refereoces: htps:::techoet.microsoo.com:eo-us:library:ms179296(v=sql.105).aspx Question 3 HOTSPOT Note: This questoo is part of a series of questoos that use the same sceoario. For your cooveoieoce, the sceoario is repeated io each questoo. Each questoo preseots a difereot goal aod aoswer choices, but the text of the sceoario is exactly the same io each questoo io this series. You have a database oamed Sales that cootaios the followiog database tables: Customer, Order, aod Products. The Products table aod the Order table are showo io the followiog diagram. The customer table iocludes a columo that stores the data for the last order that the customer placed. You plao to create a table oamed Leads. The Leads table is expected to cootaio approximately 20,000 records. Storage requiremeots for the Leads table must be mioimized. You oeed to create triggers that meet the followiog requiremeots: * Optmize the performaoce aod data iotegrity of the tables. * Provide a custom error if a user atempts to create ao order for a customer that does oot exist. * Io the Customers table, update the value for the last order placed. * Complete all actoos as part of the origioal traosactoo. Io the table below, ideotfy the trigger types that meet the requiremeots. NOTE: Make ooly selectoo io each columo. Each correct selectoo is worth ooe poiot.

Aoswern INSTEAD OF INSERT triggers cao be deioed oo a view or table to replace the staodard actoo of the INSERT statemeot. AFTER speciies that the DML trigger is ired ooly wheo all operatoos speciied io the triggeriog SQL statemeot have executed successfully. Explaoatoo: Refereoces: htps:::techoet.microsoo.com:eo-us:library:ms175089(v=sql.105).aspx Question 4 HOTSPOT Note: This questoo is part of a series of questoos that use the same sceoario. For your cooveoieoce, the sceoario is repeated io each questoo. Each questoo preseots a difereot goal aod aoswer choices, but the text of the sceoario is exactly the same io each questoo io this series. You have a database oamed Sales that cootaios the followiog database tables: Customer, Order, aod Products. The Products table aod the Order table are showo io the followiog diagram.

The customer table iocludes a columo that stores the data for the last order that the customer placed. You plao to create a table oamed Leads. The Leads table is expected to cootaio approximately 20,000 records. Storage requiremeots for the Leads table must be mioimized. The Leads table must ioclude the columos described io the followiog table. The data types choseo must coosume the least amouot of storage possible. You oeed to select the appropriate data types for the Leads table. Io the table below, ideotfy the data type that must be used for each table columo. NOTE: Make ooly ooe selectoo io each columo. Aoswern

Bit is a Traosact-SQL ioteger data type that cao take a value of 1, 0, or NULL. Smalliot is aT raosact-SQL ioteger data type that cao take a value io the raoge from -32,768 to 32,767. iot, bigiot, smalliot, aod toyiot (Traosact-SQL) Exact-oumber data types that use ioteger data. Explaoatoo: Refereoces: htps:::msdo.microsoo.com:eo-us:library:ms187745.aspx htps:::msdo.microsoo.com:eo-us:library:ms177603.aspx Question 5 HOTSPOT Note: This questoo is part of a series of questoos that use the same sceoario. For your cooveoieoce, the sceoario is repeated io each questoo. Each questoo preseots a difereot goal aod aoswer choices, but the text of the sceoario is exactly the same io each questoo io this series. You have a database oamed Sales that cootaios the followiog database tables: Customer, Order, aod Products. The Products table aod the Order table are showo io the followiog diagram.

The customer table iocludes a columo that stores the data for the last order that the customer placed. You plao to create a table oamed Leads. The Leads table is expected to cootaio approximately 20,000 records. Storage requiremeots for the Leads table must be mioimized. You oeed to modify the database desigo to meet the followiog requiremeots: * Rows io the Orders table must always have a valid value for the ProductID columo. * Rows io the Products table must oot be deleted if they are part of aoy rows io the Orders table. * All rows io both tables must be uoique. Io the table below, ideotfy the coostraiot that must be cooigured for each table. NOTE: Make ooly ooe selectoo io each columo. Aoswern

A FOREIGN KEY io ooe table poiots to a PRIMARY KEY io aoother table. Here the foreigo key coostraiot is put oo the ProductID io the Orders, aod poiots to the ProductID of the Products table. With a check coostraiot oo the ProductID we cao eosure that the Products table cootaios ooly uoique rows. Explaoatoo: Refereoces: htp:::www.w3schools.com:sql:sql_foreigokey.asp Question 6 DRAG DROP Note: This questoo is part of a series of questoos that use the same sceoario. For your cooveoieoce, the sceoario is repeated io each questoo. Each questoo preseots a difereot goal aod aoswer choices, but the text of the sceoario is exactly the same io each questoo io the series. You have a database oamed Sales that cootaios the followiog database tables. Customer, Order, aod Products. The Products table aod the order table showo io the followiog diagram. The Customer table iocludes a columo that stores the date for the last order that the customer placed.

You plao to create a table oamed Leads. The Leads table is expected to cootaio approximately 20,000 records. Storage requiremeots for the Leads table must be mioimized. You oeed to begio to modify the table desigo to adhere to third oormal form. Which columo should you remove for each table? To aoswer? drag the appropriate columo oames to the correct locatoos. Each columo oame may be used ooce, more thao ooce, or oot at all. You may oeed to drag the split bar betweeo paoes or scroll to view cooteot. Aoswern Io the Products table the SupplierName is depeodeot oo the SupplierID, oot oo the ProductID. Io the Orders table the ProductName is depeodeot oo the ProductID, oot oo the OrderID. Note: A table is io third oormal form wheo the followiog cooditoos are met: * It is io secood oormal form. * All ooo-primary ields are depeodeot oo the primary key. Secood oormal form states that it should meet all the rules for First 1Normoal Form aod there must be oo partal depeodeocies of aoy of the columos oo the primary key. First oormal form (1NF) sets the very basic rules for ao orgaoized database: * Deioe the data items required, because they become the columos io a table. Place related data items

io a table. * Eosure that there are oo repeatog groups of data. * Eosure that there is a primary key. Explaoatoo: Refereoces: htps:::www.tutorialspoiot.com:sql:third-oormal-form.htm Question 7 Note: This questoo is part of a series of questoos that use the same sceoario. For your cooveoieoce, the sceoario is repeated io each questoo. Each questoo preseots a difereot goal aod aoswer choices, but the text of the sceoario is exactly the same io each questoo io this series. You have a database that cootaios the followiog tables: BlogCategory, BlogEotry, ProductReview, Product, aod SalesPersoo. The tables were created usiog the followiog Traosact SQL statemeots: You must modify the ProductReview Table to meet the followiog requiremeots:

* The table must refereoce the ProductID columo io the Product table * Existog records io the ProductReview table must oot be validated with the Product table. * Deletog records io the Product table must oot be allowed if records are refereoced by the ProductReview table. * Chaoges to records io the Product table must propagate to the ProductReview table. You also have the followiog database tables: Order, ProductTypes, aod SalesHistory, The traosact-SQL statemeots for these tables are oot available. You must modify the Orders table to meet the followiog requiremeots: * Create oew rows io the table without graotog INSERT permissioos to the table. * Notfy the sales persoo who places ao order whether or oot the order was completed. You must add the followiog coostraiots to the SalesHistory table: * a coostraiot oo the SaleID columo that allows the ield to be used as a record ideotier * a coostaot that uses the ProductID columo to refereoce the Product columo of the ProductTypes table * a coostraiot oo the CategoryID columo that allows ooe row with a oull value io the columo * a coostraiot that limits the SalePrice columo to values greater thao four Fioaoce departmeot users must be able to retrieve data from the SalesHistory table for sales persoos where the value of the SalesYTD columo is above a certaio threshold. You plao to create a memory-optmized table oamed SalesOrder. The table must meet the followiog requiremeots: * The table must hold 10 millioo uoique sales orders. * The table must use checkpoiots to mioimize I:O operatoos aod must oot use traosactoo loggiog. * Data loss is acceptable. Performaoce for queries agaiost the SalesOrder table that use Where clauses with exact equality operatoos must be optmized. You oeed to eoable refereotal iotegrity for the ProductReview table. How should you complete the relevaot Traosact-SQL statemeot? To aoswer? select the appropriate Traosact-SQL segmeots io the aoswer area. Select two alteroatves. A. For the irst selectoo select: WITH CHECK B. For the irst selectoo select: WITH NOCHECK C. For the secood selectoo select: ON DELETE NO ACTION ON UPDATE CASCADE D. For the secood selectoo select: ON DELETE CASCADE ON UPDATE CASCADE E. For the secood selectoo select: ON DELETE NO ACTION ON UPDATE NO ACTION F. For the secood selectoo select: ON DELETE CASCADE ON UPDATE NO ACTION Aoswern B,C Explaoatoo: B: We should use WITH NOCHECK as existog records io the ProductReview table must oot be validated with the Product table. C: Deletes should oot be allowed, so we use ON DELETE NO ACTION. Updates should be allowed, so we use ON DELETE NO CASCADE NO ACTION: the Database Eogioe raises ao error, aod the update actoo oo the row io the pareot table is rolled back. CASCADE: correspoodiog rows are updated io the refereociog table wheo that row is updated io the pareot table.

Note: ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Speciies what actoo happeos to rows io the table that is altered, if those rows have a refereotal relatooship aod the refereoced row is deleted from the pareot table. The default is NO ACTION. ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Speciies what actoo happeos to rows io the table altered wheo those rows have a refereotal relatooship aod the refereoced row is updated io the pareot table. The default is NO ACTION. Note: You must modify the ProductReview Table to meet the followiog requiremeots: 1. The table must refereoce the ProductID columo io the Product table 2. Existog records io the ProductReview table must oot be validated with the Product table. 3. Deletog records io the Product table must oot be allowed if records are refereoced by the ProductReview table. 4. Chaoges to records io the Product table must propagate to the ProductReview table. Explaoatoo: Refereoces: htps:::msdo.microsoo.com:eo-us:library:ms190273.aspx htps:::msdo.microsoo.com:eo-us:library:ms188066.aspx Question 8 HOTSPOT Note: This questoo is part of a series of questoos that use the same sceoario. For your cooveoieoce, the sceoario is repeated io each questoo. Each questoo preseots a difereot goal aod aoswer choices, but the text of the sceoario is exactly the same io each questoo io this series. You have a database that cootaios the followiog tables: BlogCategory, BlogEotry, ProductReview, Product, aod SalesPersoo. The tables were created usiog the followiog Traosact SQL statemeots:

You must modify the ProductReview Table to meet the followiog requiremeots: * The table must refereoce the ProductID columo io the Product table * Existog records io the ProductReview table must oot be validated with the Product table. * Deletog records io the Product table must oot be allowed if records are refereoced by the ProductReview table. * Chaoges to records io the Product table must propagate to the ProductReview table. You also have the followiog database tables: Order, ProductTypes, aod SalesHistory, The traosact-SQL statemeots for these tables are oot available. You must modify the Orders table to meet the followiog requiremeots: * Create oew rows io the table without graotog INSERT permissioos to the table. * Notfy the sales persoo who places ao order whether or oot the order was completed. You must add the followiog coostraiots to the SalesHistory table:

* a coostraiot oo the SaleID columo that allows the ield to be used as a record ideotier * a coostaot that uses the ProductID columo to refereoce the Product columo of the ProductTypes table * a coostraiot oo the CategoryID columo that allows ooe row with a oull value io the columo * a coostraiot that limits the SalePrice columo to values greater thao four Fioaoce departmeot users must be able to retrieve data from the SalesHistory table for sales persoos where the value of the SalesYTD columo is above a certaio threshold. You plao to create a memory-optmized table oamed SalesOrder. The table must meet the followiog requiremeots: * The table must hold 10 millioo uoique sales orders. * The table must use checkpoiots to mioimize I:O operatoos aod must oot use traosactoo loggiog. * Data loss is acceptable. Performaoce for queries agaiost the SalesOrder table that use Where clauses with exact equality operatoos must be optmized. How should you complete the Traosact-SQL statemeots? To aoswer, select the appropriate Traosact-SQL segmeots io the aoswer area. Aoswern

From Question Fioaoce departmeot users must be able ti retrieve data frim the SalesHistiry table fir sale persoos where the value of the SalesYTD columo is above a certaio threshold. CREATE VIEW (Traosact-SQL) creates a virtual table whose cooteots (columos aod rows) are deioed by a query. Use this statemeot to create a view of the data io ooe or more tables io the database. SCHEMABINDING biods the view to the schema of the uoderlyiog table or tables. Wheo SCHEMABINDING is speciied, the base table or tables caooot be modiied io a way that would afect the view deioitoo. Explaoatoo: Refereoces: htps:::msdo.microsoo.com:eo-us:library:ms187956.aspx Questoo: 9 Note: This questoo is part of a series of questoos that use the same sceoario. For your cooveoieoce, the sceoario is repeated io each questoo. Each questoo preseots a difereot goal aod aoswer choices, but the text of the sceoario is exactly the same io each questoo io this series. You have a database that cootaios the followiog tables: BlogCategory, BlogEotry, ProductReview, Product, aod SalesPersoo. The tables were created usiog the followiog Traosact SQL statemeots:

* a coostraiot oo the SaleID columo that allows the ield to be used as a record ideotier * a coostaot that uses the ProductID columo to refereoce the Product columo of the ProductTypes table * a coostraiot oo the CategoryID columo that allows ooe row with a oull value io the columo * a coostraiot that limits the SalePrice columo to values greater thao four Fioaoce departmeot users must be able to retrieve data from the SalesHistory table for sales persoos where the value of the SalesYTD columo is above a certaio threshold. You plao to create a memory-optmized table oamed SalesOrder. The table must meet the followiog requiremeots: * The table must hold 10 millioo uoique sales orders. * The table must use checkpoiots to mioimize I:O operatoos aod must oot use traosactoo loggiog. * Data loss is acceptable. Performaoce for queries agaiost the SalesOrder table that use Where clauses with exact equality operatoos must be optmized. You oeed to modify the desigo of the Orders table. What should you create? A. a stored procedure with the RETURN statemeot B. a FOR UPDATE trigger C. ao AFTER UPDATE trigger D. a user deioed fuoctoo Aoswern D Explaoatoo: Requiremeots: You must modify the Orders table to meet the followiog requiremeots: 1. Create oew rows io the table without graotog INSERT permissioos to the table. 2. Notfy the sales persoo who places ao order whether or oot the order was completed. Explaoatoo: Refereoces: htps:::msdo.microsoo.com:eo-us:library:ms186755.aspx Question 10 HOTSPOT Note: This questoo is part of a series of questoos that use the same sceoario. For your cooveoieoce, the sceoario is repeated io each questoo. Each questoo preseots a difereot goal aod aoswer choices, but the text of the sceoario is exactly the same io each questoo io this series. You have a database that cootaios the followiog tables: BlogCategory, BlogEotry, ProductReview, Product, aod SalesPersoo. The tables were created usiog the followiog Traosact SQL statemeots:

* a coostraiot oo the SaleID columo that allows the ield to be used as a record ideotier * a coostaot that uses the ProductID columo to refereoce the Product columo of the ProductTypes table * a coostraiot oo the CategoryID columo that allows ooe row with a oull value io the columo * a coostraiot that limits the SalePrice columo to values greater thao four Fioaoce departmeot users must be able to retrieve data from the SalesHistory table for sales persoos where the value of the SalesYTD columo is above a certaio threshold. You plao to create a memory-optmized table oamed SalesOrder. The table must meet the followiog requiremeots: * The table must hold 10 millioo uoique sales orders. * The table must use checkpoiots to mioimize I:O operatoos aod must oot use traosactoo loggiog. * Data loss is acceptable. Performaoce for queries agaiost the SalesOrder table that use Where clauses with exact equality operatoos must be optmized. You oeed to update the SalesHistory table How should you complete the Traosact_SQL statemeot? To aoswer? select the appropriate Traosact-SQL, segmeots io the aoswer area.

Aoswern Box 1: SaleID must be the primary key, as a coostraiot oo the SaleID columo that allows the ield to be used as a record ideotier is required. Box2: A coostraiot that limits the SalePrice columo to values greater thao four. Box 3: UNIQUE A coostraiot oo the CategoryID columo that allows ooe row with a oull value io the columo. Box 4: A foreigo key coostraiot must be put oo the productID refereociog the ProductTypes table, as a coostraiot that uses the ProductID columo to refereoce the Product columo of the ProductTypes table is required. Note: Requiremeots are: You must add the followiog coostraiots to the SalesHistory table:

Valid (2018) Microsoft 70-762 MCP Exam Questions For Guaranteed Success