CORRECT TEXT
You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that has a database named ComDB with tables named Partners and Events. These
tables were created using the following Transact-SQL code:
CREATE TABLE [dbo].[Partners]
(
[CompanyID] [int] NOT NULL PRIMARY KEY,
[CompanyName] [varchar] (150) NOT NULL,
[Location] [varchar] (150) NOT NULL,
[ContactName] [varchar] (150) NOT NULL,
[Email] [varchar] (150) NOT NULL,
[Phone] [varchar] (10) NOT NULL
)
CREATE TABLE [dbo].[Events]
(
[EventID] [int] NOT NULL PRIMARY KEY,
[CompanyID] [int] NOT NULL,
[EventDescription] [nvarchar] (MAX),
[EventDate] [nvarchar] (50) NOT NULL,
[EventCordinator] [nvarchar] (150) NOT NULL
)
You add a foreign key relationship between the two tables on the CompanyID column.
You need to develop a stored procedure named sp_coEvents that retrieves CompanyName for all
partners and the EventDate for all events that they have coordinated.
To answer, type the correct code in the answer area.
Answer:
Explanation:
CREATE PROCEDURE sp_coEvent
AS
SELECT CompanyName, EventDate
FROM Partners JOIN Events ON
Partners.CompanyID = Events.CompanyID
I think the answer is wrong because it uses an INNER JOIN.
This would not qualify the ask of “CompanyName for all partners” as well as “EventDate for all events that they have coordinated”
I would use FULL JOIN instead.
0
0
Each records in Events must have one correlated record in Partners, so the FULL is not required.
I think the left join is more appropriate.
0
0
Left Join will also bring Partners that have no event against them, so the event date will be NULL. I don’t think this is the purpose of this stored proc.
If you look at the stored proc name, sp_coEvent it also shows that the requirement is to get the company events. What will be the purpose of bringing partners with no events? So I think INNER JOIN is appropriate, i.e. just return the partners who have coordinated an event.
0
0
The answer is right. The statement say “partners AND their events”, so there’s no point in bringing partner without events, like in a left join.
0
0
It should be Left Join
0
0
I don’t know but a view should be a more sensible thing to create given the requirements
0
0
No, the requirement is to create a stored procedure.
0
0
The answer is right.
0
0
create procedure sp_coEvents
@CompanyName varchar (150)
as
select partners.CompanyName,events.EventDate from Partners inner join Events
on Partners.CompanyID=events.CompanyID
where partners.CompanyName = @CompanyName
0
0
alter table Events
add Constraint FKConstraint Foreign Key (CompanyID) references dbo.Partners(CompanyID)
create procedure sp_coEvents
as
select P.CompanyName, E.EventDate
FROM dbo.Partners as P
inner join dbo.Events as E
on P.CompanyID=E.CompanyID
0
0
BTW, part of the new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0