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

[GTU] SQL & PL/SQL SET - 4

Definition of SET - 4

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

1. Find the top three Parts been ordered and have the highest sale till date.

B)

1. Create a PL/ SQL block to prepare invoice in following format.
   Display the invoice in the following format. Use parameterized cursor.
   
   Invoice ::: <Supplier’s Name>
   Part Id Part Name Quantity Unit Price Total Price
   ----------------------------------------------------------------------------------------
   ----------------------------------------------------------------------------------------
   Total: _______
   ----------------------------------------------------------------------------------------
   
   

[GTU] SQL & PL/SQL SET - 3

Definition of SET - 3

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 Sequence that can be used to enter new account number into the account
   table. Add a new record into Account table using the created sequence.

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.
2. Create a package for the following :
   Create a function to return the current balance for a given account number.
   
   

[GTU] SQL & PL/SQL SET - 2

Definition of SET - 2

Customer (Cust_Id, Cust_Name, Cust_Addr, Cust_City, EmailID,Contact_No)
Magazine (Mag_Id, Mag_Name, Unit_Rate, Type_of_subsciption[weekly, monthly, etc.])
Subscription (Cust_Id, Mag_Id, start_date, end_date)

A)

1. Create a View that displays Customer name, Magazine name along with its rate
   which was subscribed during 01-Sept-2010 to 01-Feb-2011.
2. Find top three magazines having the highest sale during last one month of time.

B)

1. Create a function to return No. of customers in city Gandhinagar who have subscribed
   the magazine ‘Outlook’ after August 2010. If no such customer exists, throw a user
   defined exception with appropriate message.
2. Create a trigger that is fired after an INSERT statement is executed for the Customer
   table. The trigger writes the new customer’s code, name and the sysdate in a table
   called Customer_Log.(create the table Customer_Log)
   
   
   

[GTU] SQL & PL/SQL SET - 1

Definition of SET - 1

Competition (Comp_code, Comp_name (Dancing, Painting, GK, etc.) )
Participants (Part_no, Part_name, DOB, Address, EmailID, Contact_number )
Scorecard (Part_no, Comp_code, Judge_no , Marks)Implement the following:
A)

1. Find those participants who have registered both for ‘Dancing’ and ‘Painting’ (Note:
   Use set operator).
2. Find the average score, scored in each competition event.

B)

1. Create a PL/ SQL block to prepare report in following format.
   Display the score card in the following format, for the Participant whose
   ID/ Name should be provided by the user.
   
   
   Talent Winner 2011 ::: <Participant’s Name>
   Competition name Judge1 Judge2 Judge3
   --------------------------------------------------------------------------------------------
   1. Painting
   2. Dancing
   --------------------------------------------------------------------------------------------
   Total Marks: _______
   --------------------------------------------------------------------------------------------