Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 47 discussion

Actual exam question from Microsoft's 70-762

Question #: 47
Topic #: 1

DRAG DROP -
You are creating a stored procedure which will insert data into the table shown in the Database schema exhibit. (Click the exhibit button.)

You need to insert a new customer record into the tables as a single unit of work.
Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange the, in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
Select and Place:

Show Suggested Answer

Suggested Answer:

The entities on the many side, of the 1-many relations, must be added before we add the entities on the 1-side.
We must insert new rows into BusinessEntityContact and BusinessEntityAddress tables, before we insert the corresponding rows into the BusinessEntity and
AddressType tables.

by RavenVStar at Dec. 18, 2019, 11:53 p.m.

Comments

Submit Cancel

Bartek

Highly Voted 5 years, 4 months ago

1. BussinessEntity 2. BussinessEntityContact 3.AddressType 4. BussinessEntityAddress

upvoted 27 times

NickMane

5 years, 3 months ago

Also works: 1. BussinessEntity 2. AddressType 3. BussinessEntityContact 4. BussinessEntityAddress

upvoted 16 times

...

SzalonyZielonyRobak

Highly Voted 5 years, 3 months ago

The only way to do it with 5 items is to skip one. Which we can? In my opinion - AddressType. I think, that we can assume, that it is already there. So that way: 1) Begin trans 2) Person.BusinessEntity 3) Person.BusinessEntityContact 4) Person.BusinessEntityAddress 5) Commit 3 & 4 are interchangable if it is not the case, then before the insert into Person.BusinessEntityAddress we need to insert into Person.AddressType

upvoted 22 times

AJ_96

5 years, 3 months ago

You're right. In my exam AddressType option wasn't even an available option.

upvoted 9 times

...

Andy7622

4 years, 8 months ago

But what about about Name field that is presented only in Person.BusinessEntityAddressType? Can we skip the person name?

upvoted 1 times

...

kiri2020

4 years, 5 months ago

Passed exam and had this question, the AddressType wasn't even there, only 3 tables given.

upvoted 2 times

...

smartrammy

Most Recent 4 years, 9 months ago

One BussinessEntity can have multiple BussinessEntityContact (1:Many). Each BussinessEntityContact can have multiple BussinessEntityAddress (1:Many) . Based on Addresstype in BussinessEntityAddress Address name can be retrieved from AddressType. BussinessEntity BussinessEntityContact BussinessEntityAddress AddressType

upvoted 1 times

...

stm22

4 years, 11 months ago

5 lines: begin transaction insert into Person.BusinessEntity insert into Person.BusinessEntityAddress insert into Person.BusinessEntityContact commit transaction no need to insert into Person.AddressType; that is parent to Person.BusinessEntityAddress.AddressTypeId

upvoted 3 times

eggzamtaker

4 years, 5 months ago

Correct.

upvoted 1 times

...

Anette

5 years ago

It must be like this: begin transaction insert into Person.BusinessEntity insert into Person.AddressType insert into Person.BusinessEntityAddress insert into Person.BusinessEntityContact commit transaction But these are 6 statements not 5:/

upvoted 1 times

Anette

4 years, 11 months ago

Since it is required to insert a new customer, we can avoid insert into Person.AddressType, assuming that there are data for Address Type. begin transaction insert into Person.BusinessEntity insert into Person.BusinessEntityContact insert into Person.BusinessEntityAddress commit transaction And these lines are interchangeable: insert into Person.BusinessEntityContact insert into Person.BusinessEntityAddress

upvoted 2 times

...

binbashir22

4 years, 5 months ago

Exactly you are right....

upvoted 1 times

...

Gocsan

5 years, 5 months ago

I think the answer provided is correct. The table that holds the foreign key (i.e. the table that has the yellow key on it) must refer to an existing primary key from the other table. This means that the data in the primary key table must exist before before the foreign key record. In this scenario, all the tables with the infinity symbol are parent tables. The tables with the foreign key are child tables.

upvoted 2 times

databasejamdown

4 years, 8 months ago

Gocsan, you are incorrect. You are correct that data must exist in the primary table first. In this case the primary table is the one on the one side of the relationship. The explanation given below the answer is the opposite of what it really should be

upvoted 2 times

...

Jiacheng

5 years, 3 months ago

wrong, table with yellow key is parent table

upvoted 10 times

...

Cageman

5 years, 5 months ago

Agree, think the order must be the other way around.

upvoted 2 times

...

RavenVStar

5 years, 6 months ago

This answer doesn't seem right. First the answer should be 5 of the 6 statements. You need to add the parent record before any children can be added, otherwise you won't have the parentID to add the children if you don't have the parent ID.

upvoted 21 times

...