SQL

Sailors Table
create table sailors(sid number(5) primary key,
sname varchar(20) NOTNULL,
rating number(5) CHECK Rating≥1 and Rating≤10,
age number(4,2));

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
95 Bob 3 63.5
85 Art 3 25.5
71 Zorba 10 16
74 Horatio 9 35 2
Boats Table
create table boats(bid number(4) primary key,
bname varchar(20),
color varchar(10));

BID BNAME COLOR

104 Marine Red
103 Clipper Green
102 Interlake Red
101 Interlake Blue

3
Reserves Table
create table reserves( sid number(5), bid number(5) , day date,
foreign key(sid) references sailors(sid),
foreign key(bid) references boats(bid) )

SID BID DAY

22 101 10/10/98
74 103 9/8/98
64 102 9/8/98
64 101 9/5/98
31 104 11/12/98
31 103 11/6/98
31 102 11/10/98
22 104 10/7/98
22 103 10/8/98
22 102 10/10/98
4
Find the names and ages of all sailors

5
Find the names and ages of all sailors

Select sname,age
from sailors;
SNAME AGE
Dustin 45
Brutus 33
Lubber 55.5
Andy 25.5
Rusty 35
Horatio 35
Bob 63.5
Art 25.5
Zorba 16
Horatio 35 6
Find the details of sailors whose rating is more than 7

7
Find the details of sailors whose rating is more than 7

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

8
Find the distinct names of Sailors whose rating is better
than 7

9
Find the distinct names of Sailors whose rating is better
than 7

select distinct sname as sailorsname

from sailors
where rating>7
SailorsNAME
Lubber
Andy
Rusty
Zorba
Horatio

10
Find the average age of sailors

11
Find the average age of sailors
AVG(AGE)
36.9
select avg(age) from sailors

12
Find the average age of sailors
AVG(AGE)
36.9
select avg(age) from sailors

Find the sum of ages of sailors whose rating is 10

13
Find the average age of sailors
AVG(AGE)
36.9
select avg(age) from sailors

Find the sum of ages of sailors whose rating is 10

select sum(age)
SUM(AGE)
from sailors
where rating=10; 51

14
Find the average age of sailors
AVG(AGE)
36.9
select avg(age) from sailors

Find the sum of ages of sailors whose rating is 10

select sum(age)
SUM(AGE)
from sailors
where rating=10; 51

find the average age of sailors whose rating is 10

15
Find the average age of sailors
AVG(AGE)
36.9
select avg(age) from sailors

Find the sum of ages of sailors whose rating is 10

select sum(age)
SUM(AGE)
from sailors
where rating=10; 51

find the average age of sailors whose rating is 10

select avg(age)
from sailors AVG(AGE)
where rating=10; 25.5

16
Find the name and age of oldest sailor

17
Find the name and age of oldest sailor

select max([Link])
from sailors s2);

18
 SID SNAME RATING AGE
22 Dustin 7 45
29 Brutus 1 33
31 Lubber 8 55.5 63.5
32 Andy 8 25.5
58 Rusty 10 35
64 Horatio 7 35
95 Bob 3 63.5
85 Art 3 25.5
71 Zorba 10 16
74 Horatio 9 35

19
Find the name and age of oldest sailor

select [Link],[Link] SNAME AGE

from sailors s Bob 63.5

where [Link]=( select max([Link])

from sailors s2);

20
Find the name and age of oldest sailor

select [Link],[Link] SNAME AGE

from sailors s Bob 63.5

where [Link]=( select max([Link])

from sailors s2);

Count the no of different sailors names

21
Find the name and age of oldest sailor

select [Link],[Link] SNAME AGE

from sailors s Bob 63.5

where [Link]=( select max([Link])

from sailors s2);

Count the no of different sailors names

COUNT([Link])
Select count(distinct [Link]) 9

from sailors s;

22
Find the sids of sailors who reserved red color boat

23
 SID BID DAY
22 101 10/10/98 BID BNAME COLOR
74 103 9/8/98 104 Marine Red
64 102 9/8/98 103 Clipper Green
64 101 9/5/98
102 Interlake Red
31 104 11/12/98
31 103 11/6/98 101 Interlake Blue
31 102 11/10/98
22 104 10/7/98
22 103 10/8/98
22 102 10/10/98

24
Find the sids of sailors who reserved red color boat

select [Link]
from boats b, reserves r
where [Link]='red' and [Link]=[Link];

25
Find the sids of sailors who reserved red color boat

select [Link]
from boats b, reserves r
where [Link]='red' and [Link]=[Link];

Find the details of sailors who reserved boat 104

26
Find the sids of sailors who reserved red color boat

select [Link]
from boats b, reserves r
where [Link]='red' and [Link]=[Link];

Find the details of sailors who reserved boat 104

select sid, name, rating, age

from sailors s, reserves r
where [Link]=104 and [Link]=[Link]

27
Find the details of sailors who reserved at least one boat

28
Find the details of sailors who reserved at least one boat

select [Link], [Link], [Link], [Link]

from sailors s, reserves r
where [Link]=[Link]

29
Find the details of sailors who reserved at least one boat

select [Link], [Link], [Link], [Link]

from sailors s, reserves r
where [Link]=[Link]

Find the names of sailors who reserved red color boat

30
Find the details of sailors who reserved at least one boat

select [Link], [Link], [Link], [Link]

from sailors s, reserves r
where [Link]=[Link]

Find the names of sailors who reserved red color boat

select [Link]
from sailors s, boats b, reserves r
where ([Link]='red' and [Link]=[Link] and [Link]=[Link]);

31
Find the sids of sailors who reserved red or green color boats

32
Find the sids of sailors who reserved red or green color boats

select [Link]
from boats b, reserves r
where ([Link]='red' or [Link]='green') and [Link]=[Link];

33
Find the sids of sailors who reserved red or green color boats

select [Link]
from boats b, reserves r
where ([Link]='red' or [Link]='green') and [Link]=[Link];

Find all sids of sailors who are having rating of 10 or

reserved boat 104

34
Find the sids of sailors who reserved red or green color boats

select [Link]
from boats b, reserves r
where ([Link]='red' or [Link]='green') and [Link]=[Link];

Find all sids of sailors who are having rating of 10 or

reserved boat 104

select [Link]
from sailors s, reserves r
where ([Link]=10 or [Link]=104) and [Link]=[Link];

35
Find the names of sailors who reserved both red and green
color boats

36
Find the names of sailors who reserved both red and green
color boats

select [Link]
from boats b, reserves r
where [Link]='red' and [Link]=[Link]

intersect

select [Link]
from boats b, reserves r
where [Link]='green' and [Link]=[Link]

37
Find all sids of sailors who are having rating of 10 or
reserved boat 104

38
Find all sids of sailors who are having rating of 10 or
reserved boat 104

select [Link] from sailors s where [Link]=10

union
select [Link] from reserves r where [Link]=104

39
Find the age of the youngest sailor for each rating level

40
SELECT Command

select field list

from table name
[where condition
groupby field name
having condition]

41
Find the age of the youngest sailor for each rating level

select [Link], min([Link])

from sailors s
group by [Link];

RATING MIN([Link])
1 33
8 25.5
7 35
3 25.5
10 16
9 35

42
Find the age of youngest sailor who is eligible to vote(18
years age)for each rating level with at least 2 sailors

43
Find the age of youngest sailor who is eligible to vote(18
years age)for each rating level with atleast 2 sailors

select [Link], min([Link])

RATING MIN([Link])
from sailors s 3 25.5

where [Link]>18
7 35
8 25.5
group by [Link]
having count(*)>1

44
Find the sailors whose rating is better than some sailor
called Horatio

45
Find the sailors whose rating is better than some sailor
called Horatio

select [Link]
from sailors s
where [Link] > any (select [Link]
from sailors s2
SID
where [Link]='Horatio');
58
71
74
31
32

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

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

SID SNAME RATING AGE

22 Dustin 7 45 SID BID DAY
29 Brutus 1 33 22 101 10/10/98
31 Lubber 8 55.5 74 103 9/8/98
32 Andy 8 25.5 64 102 9/8/98
58 Rusty 10 35 64 101 9/5/98
64 Horatio 7 35 31 104 11/12/98
95 Bob 3 63.5 31 103 11/6/98
85 Art 3 25.5 31 102 11/10/98
71 Zorba 10 16 22 104 10/7/98
74 Horatio 9 35 22 103 10/8/98

22 102 10/10/98

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

select [Link]
from sailors s
where [Link] NOT IN (select [Link]
from reserves r
where [Link] IN ( select [Link]
from boats
SNAME
where [Link]='red‘);
Dustin
Lubber
Horatio

49
Find the sailors with highest rating

50
Find the sailors with highest rating

select [Link]
from sailors s1
where [Link] > = all(select [Link]
from sailors s2);

SID
58
71

51
Find the details of sailors with Second highest rating

52
Find the details of sailors with Second highest rating

select max([Link])
from sailors s1
where [Link]<(select max([Link])
from sailors s2)

53
Find the names of sailors with second highest age

54
Find the names of sailors with second highest age

select sname, max(age)

from sailors
where age<(select max(age)
from sailors )

55
Find the names of sailors with second highest age

select [Link], max([Link]) as A

from sailors s1
where [Link]<( select max([Link])
from sailors s2)

56
Find the details of sailors with third highest rating

57
Find the details of sailors with third highest rating

SELECT s1. name, [Link]

FROM sailors s1
WHERE 3 = (SELECT count (distinct [Link])
FROM sailors s2
WHERE [Link]<=[Link])

58
S1
SID SNAME RATING AGE
22 Dustin 7 45
29 Brutus 1 33
31 Lubber 8 55.5 S2
32 Andy 8 25.5
58 Rusty 10 35 SID SNAME RATING AGE
64 Horatio 7 35 22 Dustin 7 45
95 Bob 3 63.5 29 Brutus 1 33
85 Art 3 25.5 31 Lubber 8 55.5
71 Zorba 10 16 32 Andy 8 25.5
74 Horatio 9 35 58 Rusty 10 35
64 Horatio 7 35
95 Bob 3 63.5
85 Art 3 25.5
71 Zorba 10 16
74 Horatio 9 35

59
Correlated Nested Queries

SELECT s1. name, [Link]

FROM sailors s1
WHERE 3 = (SELECT count( distinct [Link])
FROM sailors s2
WHERE [Link]<=[Link])

60
Get a list of customers who placed at least one order

61
EXISTS

SELECT column-names
FROM table-name
WHERE EXISTS (SELECT column-name
FROM table-name
WHERE condition)

62
Get a list of customers who placed at least one order

SELECT *
FROM Customer c
WHERE EXISTS (SELECT *
FROM Order o
WHERE [Link] = [Link])

63
Get the list of customers who haven’t placed any orders yet

SELECT *
FROM Customer c
WHERE NOT EXISTS (SELECT *
FROM Order o
WHERE [Link] = [Link])

64
Find the names of sailors who have reserved boat number 103

65
Find the names of sailors who have reserved boat number 103

select [Link]
from sailors s
where [Link] IN (select [Link]
from reserves r
where [Link]=103);

SNAME
Dustin
Lubber
Horatio

66
Find the names of sailors with age over 20 who have not reserved a red
boat

67
Find the names of sailors with age over 20 who have not reserved a red
boat
select [Link]
from sailors s
where [Link]>20 and [Link] not in (select [Link]
from reserves r
where [Link] in(select [Link]
from boats b
SNAME where [Link]='Red'));
Brutus
Andy
Rusty
Bob
Art
Horatio

68
Find the names of sailors who have reserved boat 103.

69
Find the names of sailors who have reserved boat 103.

SELECT [Link]
FROM Sailors S
WHERE EXISTS ( SELECT *
FROM Reserves R
WHERE [Link] = 103 AND [Link] = [Link] )

70
SELECT [Link]
FROM Sailors S
WHERE [Link] IN ( SELECT [Link]
FROM Reserves R
WHERE [Link] = 103 )

71
Find the ids and names of sailors who have reserved two
different boats on the same day.

SID BID DAY

22 101 10/10/98
74 103 9/8/98
64 102 9/8/98
64 101 9/5/98
31 104 11/12/98
31 103 11/6/98
31 102 11/10/98
22 104 10/7/98
22 103 10/8/98
22 102 10/10/98

72
Find the ids and names of sailors who have reserved two
different boats on the same day.

SELECT DISTINCT [Link], [Link]

FROM Sailors S, Reserves R1, Reserves R2
WHERE [Link] = [Link] AND [Link] = [Link] AND
[Link] = [Link] AND [Link] <> [Link]

73
Find the ids and names of sailors who have reserved two
different boats on the same day.

select [Link], count(DISTINCT [Link]) as NumberOfBoats

from sailors s INNER JOIN reserves r ON [Link] = [Link]
group by [Link]
having count(DISTINCT [Link]) > = 2

74
Find the names of sailors who have reserved all boats.

75
Find the names of sailors who have reserved all boats.

SELECT [Link]
FROM Sailors S
WHERE NOT EXISTS ( ( SELECT [Link] FROM Boats B)
EXCEPT
( SELECT distinct [Link] FROM Reserves R
WHERE [Link] = [Link] )
);

76