T-SQLIn-Class Assignment Problem 1 – Stored Procedure – KEY
Create and test a stored procedure named ADDCREW that will (1) add a new CREW recordwith values supplied by the user, and (2) print a messageto the screen displaying the CHAR_TRIP number, the CHAR_DESTINATION, and the PIL_RATINGS if any. Your answers should go on the lines indicated below.
When invoking the procedure, use a CHAR_TRIP value of 99999.
createprocedure addcrew--create procedure that accepts 3arguments
--representing data for a new CREW record
@chartripIDinteger,@empnum integer, @crewjob varchar(10)
as
declare
@chardest varchar(3),
@pilratings varchar(30);
begin
insertinto crew (char_trip, emp_num, crew_job)--add new crew record with user-givenvalues
values (@chartripID, @empnum, @crewjob);
select@chardest=char_destination--get the correct charter destination
from charter
where char_trip =@chartripID;
select @pilratings =pil_ratings--get the correct pilot rating value
from pilot--for the pilot employee
where emp_num =@empnum;
-- print message to the screen
print'Charter trip #'+convert(varchar,@chartripID)+' has destination '
+@chardest+'. Crew member#' + @emp_num+ has pilot rating of '
+@pilratings;
end;
go
/* *********************************************************************************** */
-- invoke the procedureand supply values for the 3 arguments
executeaddcrew99999, 101,'pilot';
go
-- delete the record you just added so you can re-test with the same test data
deletefrom crew where char_trip = 99999;
go
T-SQLIn-Class Assignment Problem 2 – Trigger – KEY
Create and test a trigger named TRG_PIC_HOURS. (1) Whenever a new CREW record is added, then the trigger should execute. (2) The trigger should check IF that inserted CREW record’s CREW_JOB value is “pilot”; (3) if sothe trigger should update the PILOT table. The update should be to add ½ of the pilot’s CHAR_HOURS_FLOWN value to the pilot’s existing PIL_PIC_HRS value. Your answers should go on the lines indicated below.
When invoking the trigger, use a CHAR_TRIP value of 99999.
CREATETRIGGER trg_pic_hours
ON crew AFTERINSERTAS
DECLARE
@newcrewjob asvarchar(10),
@newchartrip asinteger,
@empnum asinteger,
@charhoursflown asnumeric(2,1);
BEGIN
-- get the values of the newly-inserted CREW record & store in program variables
Select@newcrewjob= crew_job,@newchartrip= char_trip, @empnum = emp_num
FROM INSERTED;
-- get the correct CHAR_HOURS_FLOWN value that will be needed later
Select @charhoursflown =char_hours_flown
FROM charter
WHERE char_trip =@newchartrip;
--determine if the inserted crew record was for a pilot
If @newcrewjob ='pilot'
-- update PIL_PIC_HRS value according to instructions
Begin
UPDATEpilot
SET pil_pic_hrs =pil_pic_hrs+(@charhoursflown/2)
WHERE emp_num = @empnum;
End
END;
Go
/* *********************************************************************************** */
-- invoke and test the trigger
InsertintoCREW(char_trip, emp_num, crew_job)
values (99999, 999,'pilot');
select*from pilot;
-- delete the record you just added so you can re-test with the same test data
Deletefrom CREW where char_trip = 99999;
CHARTER Table (information on chartered flights)
char_trip(PK) char_date ac_number char_desinationchar_distancechar_hours_flown char_hours_wait char_fuel_gallons char_oil_qtscus_code
------
10001 2006-02-05 2289L ATL 936 5.1 2.2 354.1 1 10011
10002 2006-02-05 2778V BNA 320 1.6 0.0 72.6 0 10016
10003 2006-02-05 4278Y GNV 1574 7.8 0.0 339.8 2 10014
10004 2006-02-06 1484P STL 472 2.9 4.9 97.2 1 10019
10005 2006-02-06 2289L ATL 1023 5.7 3.5 397.7 2 10011
10006 2006-02-06 4278Y STL 472 2.6 5.2 117.1 0 10017
10007 2006-02-06 2778V GNV 1574 7.9 0.0 348.4 2 10012
10008 2006-02-07 2289L TYS 644 4.1 0.0 140.6 1 10014
10009 2006-02-07 2289L ATL 1574 6.6 23.4 459.9 0 10017
10010 2006-02-07 4278Y ATL 998 6.2 3.2 279.7 0 10016
10011 2006-02-07 2289L BNA 352 1.9 5.3 66.4 1 10012
10012 2006-02-08 2778V ATL 884 4.8 4.2 215.1 0 10010
10013 2006-02-08 4278Y TYS 644 3.9 4.5 174.3 1 10011
10014 2006-02-09 4278Y ATL 936 6.1 2.1 302.6 0 10017
10015 2006-02-09 2289L GNV 1645 6.7 0.0 459.5 2 10016
10016 2006-02-09 2778V ATL 312 1.5 0.0 67.2 0 10011
10017 2006-02-10 2289L STL 508 3.1 0.0 105.5 0 10014
10018 2006-02-10 4278Y TYS 644 3.6 4.5 167.4 0 10017
99999 2009-02-17 1234V WAC .9
CREW Table (information on employees assigned to charter flights and their job on the flight)
char_trip(PK)(FK) emp_num(PK)(FK) crew_job
------
10001 104 Pilot
10002 101 Co-Pilot
10003 105 Pilot
10003 109 Pilot
10004 106 Pilot
10005 101 Attendant
10006 109 Pilot
10007 104 Pilot
10007 105 Pilot
10008 106 Pilot
10009 105 Pilot
10010 108 Co-Pilot
10011 101 Pilot
10011 104 Pilot
10012 101 Attendant
PILOT Table (information on pilots of Charter company)
emp_num(PK)pilot_licensepilot_ratings pil_med_typepil_med_date pil_pt135_date pil_pic_hrs
------
101 ATP SEL/MEL/Instr/CFII 1 2005-04-12 2005-06-15 0
104 ATP SEL/MEL/Instr 1 2005-06-10 2006-03-23 0
105 COM SEL/MEL/Instr/CFI 2 2006-02-25 2006-02-12 0
106 COM SEL/MEL/Instr 2 2006-04-02 2005-12-24 0
109 COM SEL/MEL/Instr/CFII 1 2006-04-14 2006-04-21 0
999 ABC SEL/MEL 9 NULL NULL 0