Old Papers

1
Old Papers

• Lab Week 12

2
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.

3
Parcel delivery system (S2002)
4
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 OByrne 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)

5
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.

6
Dog Kennel system (S2003)
7
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)

8
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)