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

TABLE CREATION

SQL> create table sailor
  2  (
  3  sid varchar2(10) primary key,
  4  sname varchar2(10),
  5  rating number(10) CHECK (rating between 0 and 10),
  6  DOB date
  7  );

Table created.

SQL> create table boat
  2  (
  3  bid varchar2(10) primary key,
  4  bname varchar2(10),
  5  color varchar2(10)
  6  );

Table created.


SQL> create table reserve
  2  (
  3  sid varchar2(10) REFERENCES sailor(sid),
  4  bid varchar2(10) REFERENCES boat(bid),
  5  rdate date
  6  );

Table created

INSERTION IN TABLES

SQL> insert into sailor
  2  values('s1','rajnikant',9,'29-FEB-1988');

1 row created.

SQL> insert into sailor
  2  values('s2','irfaan',8,'29-FEB-1988');

1 row created.

SQL> insert into sailor
  2  values('s3','saktimaan',7,'29-MAR-1989');

1 row created.

SQL> insert into boat
  2  values('b1','basanti','red');

1 row created.

SQL> insert into boat
  2  values('b2','titanic','yellow');

1 row created.

SQL> insert into boat
  2  values('b3','airbus','black');

1 row created.


SQL> insert into reserve
  2  values('s1','b2','13-MAR-12');

1 row created.

SQL> insert into reserve
  2  values('s3','b1','13-FEB-12');

1 row created.

SQL> insert into reserve
  2  values('s2','b1','13-APR-12');

1 row created.

TABLES DISPLAY

SQL> select * from boat;

BID        BNAME      COLOR
---------- ---------- ----------
b1         basanti    red
b2         titanic    yellow
b3         airbus     black

SQL> select * from sailor;

SID        SNAME          RATING DOB
---------- ---------- ---------- ---------
s1         rajnikant           9 29-FEB-88
s2         irfaan              8 29-FEB-88
s3         saktimaan           7 29-MAR-89

SQL> select * from reserve;

SID        BID        RDATE
---------- ---------- ---------
s1         b2         13-MAR-12
s3         b1         13-FEB-12
s2         b1         13-APR-12

A. [1]    Find the name of the sailor who has not reserved the red colored boat.

SQL> select * from sailor where sid NOT IN (select sid from reserve where bid IN(select bid from boat where color = 'red'));

SID        SNAME          RATING DOB
---------- ---------- ---------- ---------
s1         rajnikant           9 29-FEB-88

A. [2]    Find the name of the sailor who is youngest among all.

SQL> select * from sailor  where dob IN (select max(dob) from sailor);

SID        SNAME          RATING DOB
---------- ---------- ---------- ---------
s3         saktimaan           7 29-MAR-89

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.

SQL> create trigger trigger12
  2  BEFORE insert
  3  ON boat
  4  for each row
  5  DECLARE
  6   first_char varchar2(1);
  7
  8  BEGIN
  9   first_char := substr(:new.bid , 1, 1);
 10   if (first_char='b') then
 11    dbms_output.put_line('Data inserted');
 12   else
 13    RAISE_APPLICATION_ERROR(-20005,'BoatCode must start with b');
 14   end if;
 15
 16  END;
 17  /

Trigger created.

SQL> insert into boat
  2  values('5','filo','white');
insert into boat
            *
ERROR at line 1:
ORA-20005: BoatCode must start with b
ORA-06512: at "SCOTT.TRIGGER12", line 9
ORA-04088: error during execution of trigger 'SCOTT.TRIGGER12'

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

SQL> create or replace procedure procedure12(xsid IN varchar2) IS
  2
  3  inLeap number(10);
  4
  5
  6  BEGIN
  7   select mod(to_char(dob,'YYYY'),4) into inLeap from sailor where sid = xsid;
  8 
  9   if (inLeap = 0) then
 10    dbms_output.put_line('yes this sailor born in leap year');
 11   else
 12    dbms_output.put_line('NO this sailor is not born in leap year');
 13
 14   end if;
 15
 16   EXCEPTION
 17    WHEN NO_DATA_FOUND then
 18      raise_application_error(-20001,'Data Does not Exists');
 19  END;
 20  /

Procedure created.

SQL> execute  procedure12('s2');
yes this sailor born in leap year

PL/SQL procedure successfully completed.

SQL> execute  procedure12('s3');
NO this sailor is not born in leap year

PL/SQL procedure successfully completed.

---

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