Experiment Sailors

Lab-4
Experiment 
  Sailors


       SID SNAME       AGE    RATING
      -- ---------- --------- ---------
       22 dustin       7        45
       29 brutus       1        33
       31 lubber       8        55
       32 andy         8      25.5
       58 rusty       10        35
       64 horatio      7        35
       71 zorba       10        40
       74 horatio      9        40
       85 art          3      25.5
       95 bob          3      63.5



Boats

     BID BNAME                COLOR
--------- -------------------- ----------
      101 interlake            blue
      102 interlake            red
      103 clipper              green
      104 marine               red




Reserves


     SID        BID     DAY
--------- --------- ------
       22       101     10-OCT-98
       22       102     10-OCT-98
       22       103     10-AUG-98
       22       104     10-JUL-98
       31       102     11-OCT-98
       31       103     11-JUN-98
       31       104     11-DEC-98
       64       101     09-MAY-98
       64       102     09-AUG-98
       74       104     09-AUG-98


Selecting data from sailors table
Selecting data from reserves table
Selecting data from boat table
1)      find the names of sailors who have reserved boat 103
2)      find the names of sailors who have reserved a red boat
3)      Find the name and age of the oldest sailors
4)      Count the number of sailors.
5)      Count the number of different sailors names
6)      find the names of sailors who have not reserved a red boat
7)      find the names of sailors who have not reserved boat 103
8)      find the names of sailors who have reserved at least one boat
9)      Compute  increments for the ratings of persons who have sailed two different boats on the same day.
10)  Find the names of sailors who have reserved a red or a green boat
11)  find the all sids of sailors who have rating 10 or have reserved boat 104..
12)  Find the number of reservations for each red boat.
13)  Find the minimum age of the sailor.
14)  Find the sum of the rating of sailors
15)  find the id and names of sailors who have reserved id=22 or age<25.



 











Full join

SQL> select s.sid,s.sname,o.orderdate
   from suppliers s
    full join
    orders o
    on s.sid=o.sid;

       SID SNAME           ORDERDATE
---------- --------------- ---------
        11 ibm             12-AUG-10
        33 microsoft       09-NOV-11
        11 ibm             07-SEP-09
        44 sony
        22 hewlett packard
                           13-DEC-12






type of join operation to perform

Display names of the employees and their loc.(cartisian product)
Display names of the employees along their manager names.(self join)
Display names of the employees who are  working in NEW YORK.(equi join)
Display names of the employee,salary of each and grade of each .(non equi join)

:p><= n < �v� ��� class=MsoPlainText> 

SQL> select * from student;

no rows selected

SQL> rollback;

Rollback complete.

SQL> select * from student;

    ROLLNO NAME                       AGGR ADDR                                
---------- -------------------- ---------- ----------                          
         1 akshay                     98.5                                     
         2 shruthi                    97.4                                      
         3 venkat                     87.6                                     


SQL> drop table student;

Table dropped.

SQL>  create table student(rollno number,name varchar2(20));

Table created.

SQL> insert into student values(10,'askhay');

1 row created.

SQL> insert into student values(20,'ravi');

1 row created.

SQL> select * from student;

    ROLLNO NAME                                                                
---------- --------------------                                                 
        10 askhay                                                              
        20 ravi                                                                

SQL> commit;

Commit complete.

SQL> savepoint p;

Savepoint created.

SQL> update student set name='madhu' where rollno=1;

0 rows updated.

SQL> update student set name='madhu' where rollno=10;

1 row updated.

SQL> select * from student;

    ROLLNO NAME                                                                 
---------- --------------------                                                
        10 madhu                                                               
        20 ravi                                                                 

SQL> rollback to p;

Rollback complete.

SQL> select * from student;

    ROLLNO NAME                                                                
---------- --------------------                                                
        10 askhay                                                               
        20 ravi