Transcript Stored Procedures

Old Papers
Lab Week 12
Try out old papers
• You are nearly at the end of the first
semester of your course.
• The questions following are questions
from previous papers in FT211 and DT266
• Use the database DT2112Examples to try
out the queries.
Parcel delivery system (S2002)
delivery
mechanism
sender
staffNo
deptno
grade
staffname
Mechanismcode
VehicleType
DriverType
can deliver
send
parcel
ParcelNo
*staffNo
*Typecode
DateSent
Urgency
is a type
of
parcel type
Typecode
Type description
*Mechanismcode
Summer 2002
• 6. Given the ERD displayed in the previous
slide, write Select statements that will: •
(a) Display the number of parcels sent out
on 12/05/2002 and that had an urgency value of
‘high’.
(5 marks)
•
(b) The number of parcels sent out by staff
member ‘Patricia O’Byrne’ ordered by type,
giving the description of the parcel type.
(10
marks)
•
(c) A list of all senders that incurred the
use of a vehicle type ‘Refrigerated truck’.
(10
marks)
Write the following queries
• Following on from the database tables you
created from the ‘Create, Alter, Drop’
presentation (See Week 4):
• Write a stored procedure to:
– Accept a guest no and display any bookings for that
guest.
• Store the stored procedure.
• Write a query to execute the stored procedure.
• Execute the stored procedure.
Dog Kennel system (S2003)
Dog Breed
Breed Id
Breed description
Weight range
Dog
DogId
DogName
DogWeight
DogAge
DogDiet
DogExerciseNeeds
*Breed Id
Kennel
KennelNo
KennelPosition
KennelSize
KennelConditions
KennelHeat
Stay
*DogId
StartDate
*KennelNo
EndDate
CostIncurred
DogComments
S2003 Q5
Given the ERD displayed on Slide 6 write Select
statements that will: (a) Display a list of all dogs that have a breed
description of ‘Golden Retriever’. (5 marks)
(b) Display DogId, DogName and BreedId for all dogs
that have stayed in heated kennels, grouped by BreedId.
(10 marks)
(c) Given a Breed Description, display all kennels in
which dogs of that breed have previously stayed,
ensuring that each kennel displays only once. Assume
that the breed description is held in a local variable. (10
marks)
S2003 Q6
(a) Write a procedure BookDogStay that will take as input
parameters DogID, StartDate, EndDate and KennelNo,
and will attempt to book a dog stay in the system, by
adding a new row to the STAY table, leaving
CostIncurred and DogComments as nulls. Return the
following status values (with reference to Figure 2 Dog
Kennel Database diagram):
0. The stay has been booked successfully.
1. The dog does not exist in the dog table. No rows
have been added to STAY.
2. The kennel does not exist in the kennel table. No
rows have been added to STAY.
(20 marks)
(b) Write and enquiry to run the stored procedure in SQL
Server Query Analyser. (5 marks)