opg1
----------------------------------------------------------------------


--1

SELECT s.sname
FROM sailors as s, (
	SELECT s.sid
	FROM sailors as s, boats as b, reserves as r
	WHERE s.sid=r.sid AND b.bid=r.bid AND b.bid=103
	) AS sok
WHERE sok.sid=s.sid;


  sname  
---------
 Dustin
 Lubber
 Horatio
(3 rows)





opg2
----------------------------------------------------------------------


--2

SELECT s.sname
FROM sailors as s, (
	SELECT DISTINCT( s.sid )
	FROM sailors as s, boats as b, reserves as r
	WHERE s.sid=r.sid AND b.bid=r.bid AND b.color='red'
	) AS sok
WHERE sok.sid=s.sid;


  sname  
---------
 Dustin
 Lubber
 Horatio
(3 rows)





opg3
----------------------------------------------------------------------


--3

SELECT DISTINCT( b.color )
FROM boats as b, (
	SELECT DISTINCT( b.bid )
	FROM sailors as s, boats as b, reserves as r
	WHERE s.sid=r.sid AND b.bid=r.bid AND s.sname='Lubber'
	) AS sok
WHERE sok.bid=b.bid;


 color 
-------
 green
 red
(2 rows)





opg4
----------------------------------------------------------------------



--4

SELECT s.sname
FROM sailors as s, (
	SELECT s.sid 
	FROM sailors as s, reserves as r
	WHERE s.sid=r.sid 
	GROUP BY s.sid 
	HAVING COUNT( r.bid ) > 1 
	) as sok
WHERE sok.sid=s.sid;


  sname  
---------
 Horatio
 Lubber
 Dustin
(3 rows)





opg5
----------------------------------------------------------------------


--5

SELECT  DISTINCT( s.sid ), s.sname
FROM sailors as s, (
	SELECT s.sid 
	FROM sailors as s, boats as b, reserves as r
	WHERE s.sid=r.sid AND b.bid=r.bid AND ( b.color='green' OR b.color='red' )
	) AS sok
WHERE sok.sid=s.sid;


 sid |  sname  
-----+---------
  22 | Dustin
  31 | Lubber
  64 | Horatio
  74 | Horatio
(4 rows)





opg6
----------------------------------------------------------------------


--6

SELECT s.sid 
FROM sailors as s, ( 
SELECT s.sid 
FROM sailors as s, boats as b, reserves as r 
WHERE s.sid=r.sid AND b.bid=r.bid AND b.color='red' 
INTERSECT (
SELECT s.sid 
FROM sailors as s, boats as b, reserves as r 
WHERE s.sid=r.sid AND b.bid=r.bid AND b.color='green'
)
) as sok
WHERE sok.sid=s.sid
;


 sid 
-----
  22
  31
(2 rows)





opg7
----------------------------------------------------------------------


--7

SELECT s.sname 
FROM sailors as s, (
	SELECT s.sid 
	FROM sailors as s, boats as b, reserves as r 
	WHERE s.sid=r.sid AND b.bid=r.bid
	GROUP BY s.sid 
	HAVING COUNT( r.bid ) >= 2
) as sok 
WHERE sok.sid=s.sid;


  sname  
---------
 Horatio
 Lubber
 Dustin
(3 rows)





opg8
----------------------------------------------------------------------


--8

SELECT s.sid 
FROM sailors as s, ( 
SELECT s.sid 
FROM sailors as s, boats as b, reserves as r 
WHERE s.sid=r.sid AND b.bid=r.bid AND s.age>20 AND
NOT EXISTS (
SELECT s.sid 
FROM boats as b2, reserves as r2 
WHERE s.sid=r2.sid AND r2.bid=b2.bid AND b2.color='red' 
)
) as sok
WHERE sok.sid=s.sid
;


 sid 
-----
  74
(1 row)





opg9
----------------------------------------------------------------------


--9

SELECT s.sid 
FROM sailors as s, ( 
SELECT s.sid 
FROM sailors as s, boats as b, reserves as r, (
  
  SELECT COUNT( DISTINCT( b2.bid ) ) as ant
  FROM boats as b2 

) as bater

WHERE s.sid=r.sid AND b.bid=r.bid 
GROUP BY s.sid, bater.ant
HAVING COUNT( DISTINCT ( b.bid ) ) = bater.ant

) as sok
WHERE sok.sid=s.sid
;


 sid 
-----
  22
(1 row)





opg10
----------------------------------------------------------------------


--10

--SELECT s.sid, s.sname
--FROM sailors as s, boats as b, reserves as r
--WHERE s.sid=r.sid AND b.bid=r.bid AND 
--	NOT EXISTS ( 	SELECT b2.bid
--					FROM boats as b2
					----WHERE b2.bname='Interlake' AND NOT (
--						NOT EXISTS ( 	SELECT s3.sid
										--FROM sailors as s3
										--WHERE s3.sid=s.sid AND NOT (
----											NOT EXISTS ( 	SELECT b4.bid
															------FROM boats as b4
															--WHERE b4.bname!='Interlake'
														--)
										--)
									--)
					--)
				--);
				
				
--SELECT DISTINCT( s.sid ), s.sname 
--FROM sailors as s, boats as b, reserves as r
--WHERE s.sid=r.sid AND b.bid=r.bid AND (
--						NOT EXISTS (
--							SELECT b2.bid 
--							FROM boats as b2
--							WHERE b2.bid=b.bid AND b2.bname!='Interlake'
--							) OR b.bname='Interlake'
--							)
--						;


SELECT DISTINCT( s.sid ), s.sname 
FROM sailors as s, boats as b, reserves as r, (
	SELECT COUNT( DISTINCT( b2.bid ) ) as bater
	FROM boats as b2
	WHERE b2.bname='Interlake'
	) as sok
WHERE s.sid=r.sid AND b.bid=r.bid AND EXISTS ( 
	SELECT b3.bid
	FROM boats as b3
	WHERE b3.bname='Interlake' AND b3.bid=b.bid
	)
GROUP BY s.sid, s.sname, sok.bater
HAVING COUNT( b.bid ) = sok.bater
;


 sid |  sname  
-----+---------
  22 | Dustin
  64 | Horatio
(2 rows)





opg11
----------------------------------------------------------------------


--1.1

SELECT AVG( s.age )
FROM sailors as s
WHERE s.rating=10
;


 avg  
------
 25.5
(1 row)





opg12
----------------------------------------------------------------------


--1.2

SELECT s.sname, s.age 
FROM sailors as s
ORDER BY s.age DESC
LIMIT 1
;


 sname | age  
-------+------
 Bob   | 65.3
(1 row)





opg13
----------------------------------------------------------------------


--1.3

SELECT COUNT( DISTINCT( s.sname ) ) as antall
FROM sailors as s
;


 antall 
--------
      9
(1 row)





opg14
----------------------------------------------------------------------


--1.4

SELECT s.sname, s.sid
FROM sailors as s, (
	SELECT s2.age as alder, s2.sid as s2id
	FROM sailors as s2
	WHERE rating=10
	ORDER BY s2.age DESC
	LIMIT 1
	) as sok
WHERE sok.alder<s.age --AND s.sid!=sok.s2id 
-- kan trenge kommentaren hvis vi skulle funnet <= for da ville 10eren kommet med ogsa
;


 sname  | sid 
--------+-----
 Dustin |  22
 Lubber |  31
 Bob    |  95
(3 rows)





opg15
----------------------------------------------------------------------


--1.5


SELECT s.rating, s.sid, s.sname, s.age
FROM sailors as s, (
	SELECT s2.rating as s2rating
	FROM sailors as s2
	GROUP BY s2.rating
	) as sok
	
WHERE s.age = (
	SELECT MIN( s3.age )
	FROM sailors as s3
	WHERE sok.s2rating=s3.rating
	) 
	AND sok.s2rating=s.rating
;




 rating | sid |  sname  | age  
--------+-----+---------+------
      1 |  29 | Brutus  |   33
      3 |  85 | Art     | 25.5
      7 |  22 | Dustin  |   45
      8 |  32 | Andy    | 25.5
      9 |  64 | Horatio |   35
      9 |  74 | Horatio |   35
     10 |  71 | Zorba   |   16
(7 rows)





opg16
----------------------------------------------------------------------


--1.6
	SELECT s.rating, s.sid, s.sname, s.age
	FROM sailors as s, (
		SELECT s2.rating as s2rating
		FROM sailors as s2
		GROUP BY s2.rating
		HAVING COUNT(*)>=2
		) as sok
	WHERE s.age = ( 
		SELECT MIN( s3.age )
		FROM sailors as s3
		WHERE sok.s2rating=s3.rating AND s3.age >= 18
		)
	AND sok.s2rating=s.rating
	;
	


 rating | sid |  sname  | age  
--------+-----+---------+------
      3 |  85 | Art     | 25.5
      8 |  32 | Andy    | 25.5
      9 |  64 | Horatio |   35
      9 |  74 | Horatio |   35
     10 |  58 | Rusty   |   35
(5 rows)





opg17
----------------------------------------------------------------------


--1.7
	SELECT s.rating, AVG( s.age )
	FROM sailors as s
	GROUP BY s.rating
	HAVING COUNT(*) >= 2
	;
	


 rating |       avg        
--------+------------------
     10 |             25.5
      9 |               35
      8 |             40.5
      3 | 45.4000015258789
(4 rows)