Be patient, Everything is coming Together.: September 2019

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_18 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_18 r,boats_18 b

2 where

3 s.sid=r.sid and

4 r.bid=b.bid and

5 b.color='red';

SID

----------

5.Find the names of sailors who have reserved a red boat.

SQL> select s.sname from

2 sailors s,reserves_18 r,boats_18 b

3 where

4 s.sid=r.sid and

5 r.bid=b.bid and

6 b.color='red';

SNAME

----------

Dustin

Lubber

Horatio

6.Find the colors of boats reserved by Lubber.

SQL>select b.color from

1 sailors s,reserves_18 r,boats_18 b

2 where

3 s.sid=r.sid and

4 r.bid=b.bid and

5 s.sname='Lubber'

COLOR

-----

red

green

red

7.Find the names of sailors who have reserved atleast one boat.

SQL> select s.sname from

2 sailors s,reserves_18 r,boats_18 b

3 where

4 s.sid=r.sid

5 and r.bid=b.bid

6 and s.sid in

7 (select sid from reserves_18 group by sid having count(s.sid)>1)

SNAME

----------

Dustin

Lubber

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

2 sid in

3 (select sid from reserves_18 group by sid

4 having count(day)>+2);

RATING+1

----------

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>

1 ( select s.sname from

2 sailors s ,reserves_18 r,boats_18 b

3 where s.sid=r.sid and

4 r.bid=b.bid and

5 b.color='red')

6 intersect

7 (select s.sname from

8 sailors s ,reserves_18 r,boats_18 b

9 where s.sid=r.sid and

10 r.bid=b.bid and

11 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

2 from sailors s

3 where s.sid in((select r.sid from boats18 b,reserves_18 r

4 where r.bid=b.bid and b.color='red')

5 UNION

6 (select r2.sid

7 from boats_18 b2,reserves_18 r2

8 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_18 r,boats_18 b

2 where

3 s.sid=r.sid

4 and

5 r.bid=b.bid and b.color='red'

6 and

7 b.color!='green';

SID

----------

13.Find all sids of sailors who have amrating of 10 or have reserved boat 104

SQL>(select s.sid from sailors s ,reserves_18 r,boats_18 b

1 where

2 s.sid=r.sid

3 and

4 s.rating=10)

5 union

6 (select s.sid from sailors s ,reserves_18 r,boats_18 b

7 where

8 s.sid=r.sid

9 and r.bid=104);

SID

----------

14.Find the names of sailors who have not reserved a red boat.

SQL> select s.sid from sailors s ,reserves_18 r,boats_18 b

2 where

3 s.sid=r.sid

4 and

5 r.bid=b.bid

6 and

7 b.color!='red';

SID

----------

15.Find sailors whose rating is better than some sailor called Horatio.

SQL> select * from sailors where rating > all

2 (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>

1 select * from sailors where rating > any

2 (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

2 from sailors s

3 where s.sid in((select r.sid from boats18 b,reserves_18 r

4 where r.bid=b.bid and b.color='red')

5 UNION

6 (select r2.sid

7 from boats_18 b2,reserves_18 r2

8 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

2 not exists

3 (( select bid from boats_18)

4 minus

5 (select bid from sailors s,reserves_18 r

6 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

1 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)

----------

24.Count the number of different sailor names.

SQL> select distinct count(distinct(sname)) from sailors ;

COUNT(DISTNCT(SNAME))

----------

25.Find the names of sailors who are older than the oldest sailor with a rating of 10.

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

28.For each red boat, find the number of reservations for this boat.

SQL> select count(r.sid) from reserves_18 r,boats_18 b

where r.bid=b.bid and b.color='red'group by b.bid;

COUNT(R.SID)

___________________

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

2 group by rating

3 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

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

32. Find those ratings for which the average age of sailors is the minimum over all ratings.

SQL> select rating

2 from (select avg(age) age,rating from sailors

3 group by rating)

4 where age=(select min(avg(age)) from sailors

5 group by rating);

RATING

----------

Be patient, Everything is coming Together.

Monday, September 9, 2019

Report Abuse