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.
SQL> update
student set aggr=aggr+5 where rollno=3;
1 row updated.
SQL> update
student set name='calvin' ,addr='goa' where rollno=1 or aggr>95;
2 rows updated.
SQL> select *
from student;
ROLLNO NAME AGGR ADDR
----------
-------------------- ---------- ----------
1 calvin 99.9 goa
2 calvin 97.4 goa
3 venkat 92.6 bangalore
SQL> select *
from student;
ROLLNO NAME AGGR ADDR
----------
-------------------- ---------- ----------
1 calvin 99.9 goa
2 calvin 97.4 goa
3 venkat 92.6 bangalore
SQL> delete
from student where rollno=1;
1 row deleted.
SQL> delete
from student;
2 rows deleted.
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