HWA2_P3_2.sql

/* -------------------------------------------------------------------------
   Yu Cheng
   ICS 321 Assignment 2
   October 23, 2008

   PART 3.2
   Provide the script that will create a stored procedure that will select
   the Skill Title, name, assessment date and required review date
   associated with all Persons assigned to a skill. Use the attribute names
   'Name', 'Skill Title',  'Assessed' and 'next review'.  Name the stored
   procedure PersonnelSkills.
   ------------------------------------------------------------------------- */

USE DB9975
GO

CREATE PROCEDURE PersonnelSkills
AS
	SELECT
		dbo.PersonName(PERSON.Id)    AS [Name],
		SKILL.Title                  AS [Skill Title],
		PERSON_SKILL.AssessmentDate  AS [Assessed],
		PERSON_SKILL.ReviewDate      AS [Next Review]

	FROM SKILL

	JOIN PERSON_SKILL ON SKILL.Id  = PERSON_SKILL.SkillId
	JOIN PERSON       ON PERSON.Id = PERSON_SKILL.PersonId

	ORDER BY Name
GO

EXECUTE PersonnelSkills
GO