Be patient, Everything is coming Together.: Working on Sailors Table

1.Find the names and ages of all sailors.
SQL> select sname,age from sailors;
SNAME             AGE
----------           ----------
Dustin                 45.0
Brutus                 33.0
Lubber                55.5
Andy                   25.5
Rusty                  35.0
Horatio               35.0
Zorba                 16.0
Horatio              35.0
Art                     25.5
Bob                   63.5
10 rows selected.

2.Find all sailors with a rating above 7.
SQL> select * from sailors where rating>7;
       SID SNAME          RATING        AGE
       ------ ---------- ---------- ----------
        31 Lubber              8       55.5
        32 Andy                8       25.5
        58 Rusty              10         35
        71 Zorba              10         16
        74 Horatio             9         35

3.Find the names of sailors who have reserved boat number 103.
SQL> select * from sailors s,reserves r where s.sid=r.sid and r.bid=103;
       SID SNAME          RATING        AGE    BID    DAY
---------- ---------- ---------- ---------- -------- ------
        22 Dustin              7         45     103   10-AUG-98
        31 Lubber              8       55.5     103   11-JUN-98
        74 Horatio             9         35     103   09-AUG-98

4.Find the sids of sailors who have reserved a red boat.
SQL> select s.sid from sailors s,reserves r,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red';
       SID
----------
        22
        22
        31
        31
        64

5.Find the names of sailors who have reserved a red boat.
SQL> select s.sname from sailors s,reserves r,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red';
SNAME
----------
Dustin
Dustin
Lubber
Lubber
Horatio

6.Find the colors of boats reserved by Lubber.
SQL>select b.color from sailors s,reserves r,boats b where s.sid=r.sid and r.bid=b.bid and s.sname='Lubber';

COLOR
-----
red
green
red

7.Find the names of sailors who have reserved atleast one boat.
SQL> select s.sname from sailors s,reserves r,boats b where s.sid=r.sid   and r.bid=b.bid and s.sid in (select sid from reserves group by sid having count(s.sid)>1)
SNAME
----------
Dustin
Dustin
Dustin
Dustin
Lubber
Lubber
Lubber
Horatio
Horatio
9 rows selected.

8.Compute increments for the ratings of persons who have sailed two different boats on the same day.
SQL> select rating+1 from sailors where sid in (select sid from reserves group by sid having count(day)>+2);
RATING+1
----------
         8
         9

9.Find the ages of sailors whose name begins and ends with B and has at least three characters.
SQL>select age from sailors s where s.sname like 'B_%b';
       AGE
----------
      63.5

10.Find the names of sailors who have reserved a red or a green boat.
SQL>( select s.sname from sailors s ,reserves r,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red')intersect (select s.sname from sailors s ,reserves r,boats b   where s.sid=r.sid and   r.bid=b.bid and b.color='green')
SNAME
----------
Dustin
Horatio
Lubber

11.Find the names of sailors who have reserved aboth a red and a green boat.
SQL>select s.sname from sailors s where s.sid in((select r.sid from boats18 b,reserves r where r.bid=b.bid and b.color='red') UNION (select r2.sid from boats b2,reserves r2 where r2.bid=b2.bid and b2.color='green'));
SNAME
----------
Dustin
Lubber

12.Find the sids of all sailors who have reserved red boats but not green boats.
SQL> select s.sid from sailors s ,reserves r,boats b   where   s.sid=r.sid and r.bid=b.bid and b.color='red' and b.color!='green';
       SID
----------
        22
        22
        31
        31
        64

13.Find all sids of sailors who have amrating of 10 or have reserved boat 104
SQL>(select s.sid from sailors s ,reserves r,boats b where   s.sid=r.sid and s.rating=10) union (select s.sid from sailors s ,reserves r,boats b where s.sid=r.sid and r.bid=104);
       SID
----------
        22
        31

14.Find the names of sailors who have not reserved a red boat.
SQL> select s.sid from sailors s ,reserves r,boats b where   s.sid=r.sid   and r.bid=b.bid and b.color!='red';
       SID
----------
        22
        22
        31
        64
        74


15.Find sailors whose rating is better than some sailor called Horatio.
SQL> select * from sailors where rating > all(select rating from sailors where sname='Horatio');
       SID SNAME          RATING        AGE
---------- ---------- ---------- ----------
        58 Rusty              10         35
        71 Zorba              10         16


16.Find sailors whose rating is better than every sailor called Horatio.
SQL>select * from sailors where rating > any (select rating from sailors where sname='Horatio');
       SID SNAME          RATING        AGE
---------- ---------- ---------- ----------
        31 Lubber              8       55.5
        32 Andy                8       25.5
        58 Rusty              10         35
        71 Zorba              10         16
        74 Horatio             9         35


17.Find the sailors with the highest rating.
SQL> select sname from sailors where rating=(select max(rating) from sailors );
SNAME
----------
Rusty
Zorba

18.Find the names of sailors who have reserved aboth a red and a green boat.
SQL>select s.sname from sailors s where s.sid in((select r.sid from boats18 b,reserves r where r.bid=b.bid and b.color='red') UNION (select r2.sid from boats b2,reserves r2 where r2.bid=b2.bid and b2.color='green'));
SNAME
----------
Dustin
Lubber


19.Find the names of sailors who have reserved all boats.
SQL> select * from sailors where not exists (( select bid from boats) minus (select bid from sailors s,reserves r where s.sid=r.sid));
       SID SNAME          RATING        AGE
---------- ---------- ---------- ----------
        22 Dustin              7         45
        29 Brutus              1         33
        31 Lubber              8       55.5
        32 Andy                8       25.5
        58 Rusty              10         35
        64 Horatio             7         35
        71 Zorba              10         16
        74 Horatio             9         35
        85 Art                 3       25.5
        95 Bob                 3       63.5
10 rows selected.

20.Find the average age of all sailors.
SQL> select avg(age) from sailors;
AVG(AGE)
----------
      36.9


21.Find the average of sailors with a rating of 10.
SQL> select avg(age) from sailors where rating=10;
AVG(AGE)
----------
      25.5


22.Find the name and age of oldest sailor.
SQL> select sname , age from sailors where age=(select max(age) from sailors);
SNAME             AGE
---------- ----------
Bob              63.5


23.Count the number of sailors.
SQL> select count(sid) from sailors;
COUNT(SID)
----------
        10


24.Count the number of different sailor names.
SQL> select distinct count(distinct(sname)) from sailors;
COUNT(DISTNCT(SNAME))
----------
        9

25.Find the names of sailors who are older than the oldest sailor with a rating of 10.
SQL>select * from sailors where age>all(select age from sailors where rating=10);
SID   SNAME           RATING       AGE
-----   -----------      -------------     --------
22   dustin           7       45
31   lubber           8       55.5
95   bob           3       63.5

26.Find the age of the youngest sailor for each rating level.
SQL> select rating,min(age) from sailors group by rating
    RATING   MIN(AGE)
---------- ----------
         1         33
         3       25.5
         7         35
         8       25.5
         9         35
        10         16
6 rows selected.

27.Find the age of the youngest sailor who is eligible to vote (i.e., is atleast 18 years old) for each rating level with atleast two such sailors.
SQL>select min(age) from sailors where age>=18 group by rating having count(age)>1;
MIN(AGE)
________________
25.5
35
25.5

28.For each red boat, find the number of reservations for this boat.
SQL> select count(r.sid) from reserves r,boats b where r.bid=b.bid and b.color='red'group by b.bid;
COUNT(R.SID)
___________________
3
2

29. Find the average age of sailors for each rating level that has atleast two sailors.
SQL> select avg(age),rating,count(sid) from sailors group by rating   having count(sid)>=2;
   AVG(AGE)     RATING COUNT(SID)
    ----------           ----------        ----------
          44.5                  3          2
          40                    7          2
          40.5                  8          2
          25.5                  10         2

30.Find the average age of sailors who are of voting age (i.e., is atleast 18 years old) for each rating level that has atleast two sailors.
SQL> select avg(age) from sailors group by rating having count(sid)>1;
AVG(AGE)
_______________
40.5
40
44.5
25.5

31.Find the average age of sailors who are of voting age (i.e., is atleast 18 years old) for each rating level that has atleast two such sailors.
SQL> select min(age) from sailors where age>=18 group by rating having count(age)>1;
MIN(AGE)
________________
25.5
35
25.5

32. Find those ratings for which the average age of sailors is the minimum over all ratings.
SQL> select rating from (select avg(age) age,rating from sailors group by rating) where age=(select min(avg(age)) from sailors group by rating);
    RATING
----------
        10

Be patient, Everything is coming Together.

Friday, August 17, 2018

Working on Sailors Table

No comments:

Report Abuse