[GTU] SQL & PL/SQL SET - 14

Definition of SET - 14

Patient (Patient_code, Patient_name, Address, City, DOB, Gender)

Implement the following:
A)

1. Create a Sequence that can be used to enter new Patient code into the Patient table.
   Add a new record into Patient table using the created sequence using the format
   ‘PT1001’.
2. Find those patients who do not belong to ‘Ahmedabad’ or ‘Mehsana’ city. (Use set
   operator)

B)

1. Create a PL/SQL block to generate the following report:
   Gender (Patients in Age group)
   
               1-20    21-40   41-60   61-80   81-100   Total
   Male
   Female
   Total
   

[GTU] SQL & PL/SQL SET - 13

Definition of SET - 13

Movie (movie_id, movie_name, date_of_release)
Screen (screen_id, location, max_capacity)
Current (movie_id,screen_id, date_of_arrival, date_of_closure)

Note:
Value of screen_id must with letter ‘S’.
Screen location can by any one of ‘FF’, ‘SF’, and ‘TF’.
Date_of_arrival must be less than Date_of_closure.
Max_capacity attribute should have a value greater than 0.

Implement the following:
A)

1. Movie ‘Star wars III‘was released in the 7th week of 2005. Find out the date of its
   release considering that a movie releases only on Friday.
2. Get the details of movie that closed on date 15-January-2010.

B)

1. Create a trigger that checks the ‘screen_id’ must start with ‘S’ whenever an insertion
   is tried to be done. Raise a user defined exception if the rule is violated.
2. Create a package for the following :
   Create a procedure to print Movie Name where Movie code is been supplied by the
   user.
   
   

[GTU] SQL & PL/SQL SET - 12

Definition of SET - 12

Sailor (sid, sname, rating (0-10), DOB)
Boat (bid, bname, color)
Reserve (sid, bid, date)

Implement the following:
A)

1. Find the name of the sailor who has not reserved the red colored boat.
2. Find the name of the sailor who is youngest among all.

B)

1. Create a trigger that checks the ‘Boat Code’ must start with ‘B’ whenever an insertion
   is tried to be done. Raise a user defined exception if the rule is violated.
2. Create a procedure that get the Sailor Code from the user and check whether that
   Sailor was born in a leap year or not. If the given sailor code does not exist, throw a
   user defined exception with appropriate message.
   
   

[GTU] SQL & PL/SQL SET - 11

Definition of SET - 11

Supplier (sid, sname, contactnum)
Parts (pid, pname, color, unit rate)
Catalog (sid, pid, qty)
Implement the following:
A)

1. Find those suppliers who haven’t ordered any Parts..
2. Create a View that displays the supplier details who have ordered any item having
   unit rate greater than Rs.500.

B)

1. Create a PL/ SQL block to prepare invoice in following format.
   Prepare this report Part information wise. Use parameterized cursor.

[GTU] SQL & PL/SQL SET - 10

Definition of SET - 10

Account (ac_no, ac_name, act_type)
Transaction (ac_no, trans_date, tran_type, tran_amount, balance)
Note: Act_type may be ‘S’ for saving or ‘C’ for current and tran_type may be ‘D’ for
deposit or ‘W’ for withdrawal.
Implement the following:
A)

1. Find out those saving transactions that took place between 10th January 2011 and 20th January 2011 and have withdrawn an amount greater than Rs. 50,000.
2. Create a View that display the account information having a balance greater than Rs. 1,00,000.

B)

1. Create a trigger not allowing insertion, deletion or updation on Saturday and before 8:00 AM & after 6:00 PM on Account table.

[GTU] SQL & PL/SQL SET - 5

Definition of SET - 5

Sailor (sid, sname, rating (0-10), DOB)
Boat (bid, bname, color)
Reserve (sid, bid, date)

Implement the following:

A)

1. Find the sailor(s) whose birthday fall in a leap year.
2. Find the name of the sailor who has reserved either the red or green colored boat.

B)

1. Create a parameterized cursor to display the sailor details who have reserved any
   boat after November 2010. If no record found, throw an user defined exception with
   appropriate message.
2. Create a function that get the Boat code from the user. Display the sailor_code who
   have reserved this boat code. Raise an exception if no information for boat/sailor
   exists.