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

TABLE CREATION

SQL> create table sailor5
  2  (
  3  sid number(3) primary key,
  4  sname varchar2(20),
  5  rating number(2),
  6  dob date
  7  );

Table created.


SQL> create table boat5
  2  (
  3  bid number(2) primary key,
  4  bname varchar2(30),
  5  color varchar2(20)
  6  );

Table created.

SQL> create table reserve5
  2  (
  3  sid number(3) REFERENCES sailor5(sid),
  4  bid number(2) REFERENCES boat5(bid),
  5  date1 date
  6  );

Table created.

INSERTION IN TABLES

SQL> insert into sailor5(sid,sname,rating,dob)values(101,'sandip',1,'15-jan-1988');

1 row created.

SQL> insert into sailor5(sid,sname,rating,dob)values(102,'jeemy',2,'15-feb-1974');

1 row created.

SQL> insert into sailor5(sid,sname,rating,dob)values(103,'jiny',3,'15-mar-1988');

1 row created.



SQL> insert into boat5(bid,bname,color)values(1,'basati','yellow');

1 row created.

SQL> insert into boat5(bid,bname,color)values(2,'renu','red');

1 row created.

SQL> insert into boat5(bid,bname,color)values(3,'moti','green');

1 row created.

SQL> insert into boat5(bid,bname,color)values(4,'motto','blue');

1 row created.



SQL> insert into reserve5(sid,bid,date1)values(101,1,'20-jun-2000');

1 row created.

SQL> insert into reserve5(sid,bid,date1)values(102,2,'23-apr-2000');

1 row created.

SQL> insert into reserve5(sid,bid,date1)values(103,3,'24-mar-2000');

1 row created.

SQL> insert into reserve5(sid,bid,date1)values(101,4,'25-feb-2000');

1 row created.

TABLES DISPLAY

SQL> select * from sailor5;

       SID SNAME                    RATING DOB
---------- -------------------- ---------- ---------
       101 sandip                        1 15-JAN-88
       102 jeemy                         2 15-FEB-74
       103 jiny                          3 15-MAR-88

SQL> select * from boat5;

       BID BNAME                    COLOR
    ------- ------------------- --------------------
         1 basati                    yellow
         2 renu                      red
         3 moti                      green
         4 motto                      blue

SQL> select * from reserve5;

       SID        BID DATE1
---------- ---------- ---------
       101          1 20-JUN-00
       102          2 23-APR-00
       103          3 24-MAR-00
       101          4 25-FEB-00

A. [1]    Find the sailor(s) whose birthday fall in a leap year.

SQL> select * from sailor5 where mod(to_char(dob,'YYYY'),4)=0 ;

       SID SNAME                    RATING DOB
---------- -------------------- ---------- ---------
       101 sandip                        1 15-JAN-88
       103 jiny                          3 15-MAR-88

A. [2]    Find the name of the sailor who has reserved either the red or green colored boat.

 SQL> select distinct(sname) from sailor5 s,boat5 b,reserve5 r where s.sid=r.sid and b.bid=r.bid and color='red' or color='green';

    SNAME
    -------------
    jeemy
    jiny
    sandip

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.

SQL> DECLARE
  2         CURSOR C1 (RDATE DATE)
  3         IS
  4         SELECT S.*
  5         FROM BOAT5 B, RESERVE5 R , SAILOR5 S WHERE DATE1 > RDATE AND S.SID = R.SID AND B.BID = R.BID;
  6  BEGIN
  7             DBMS_OUTPUT.PUT_LINE('SID   SNAME    BIRTHDATE   RATING  ');
  8             DBMS_OUTPUT.PUT_LINE('-----------------------------------');
  9 
 10             FOR C IN C1('30-NOV-1991')
 11      LOOP
 12               DBMS_OUTPUT.PUT_LINE(C.SID ||'    '||C.SNAME||'    '||C.DOB||'    '||C.RATING);
 13 
 14             END LOOP;
 15  EXCEPTION
 16         WHEN NO_DATA_FOUND THEN
 17               DBMS_OUTPUT.PUT_LINE('Record Not Found');
 18  END;
 19  /
SID   SNAME    BIRTHDATE   RATING
-----------------------------------
101    sandip    15-JAN-88    1
102    jeemy    15-FEB-74    2
103    jiny    15-MAR-88    3
101    sandip    15-JAN-88    1

PL/SQL procedure successfully completed.

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

SQL> CREATE OR REPLACE FUNCTION GETSAILOR(b BOAT5.BID%TYPE)RETURN NUMBER
  2  IS
  3         S_CODE NUMBER(3);
  4  BEGIN
  5         SELECT S.SID INTO S_CODE FROM SAILOR5 S, RESERVE5 R WHERE R.SID = S.SID AND R.BID = b;
  6 
  7   RETURN S_CODE;
  8
  9  EXCEPTION
 10         WHEN NO_DATA_FOUND THEN
 11               DBMS_OUTPUT.PUT_LINE('Boat ID not Exists');
 12               RETURN 0;
 13  END;
 14  /

Function created.

SQL> DECLARE
  2
  3   b_code number(3);
  4   s_code number(3);
  5  BEGIN
  6   b_code := &b_code;
  7
  8   s_code := getsailor(b_code);
  9          DBMS_OUTPUT.PUT_LINE('SAILOR CODE IS :'||to_char(s_Code));
 10
 11  END;
 12  /
Enter value for b_code: 2
old   6:  b_code := &b_code;
new   6:  b_code := 2;
SAILOR CODE IS :102

PL/SQL procedure successfully completed.

---

*this program is only for reference purpose, actual logic may differ from programmer to programmer