source: liacs/db/opdr2/ex2.sql@ 10

--- 1a
--- Note: RTRIM is used to remove trailing whitespace
SELECT S.sname 
FROM Sportsman S 
WHERE RTRIM(sname) LIKE 'S%m%e';

--- 1b
SELECT S.sname
FROM Sportsman S, Coaches T
WHERE S.sid = T.sid AND S.sage > 23;


--- 1c
SELECT C.cname
FROM Coach C, Coaches T, Sportsman S
WHERE C.cid = T.cid AND 
      T.sid = S.sid AND
      C.ccountry = S.scountry AND
      C.cage < ( SELECT MAX( C2.cage )
                 FROM Coach C2);

--- 1d
SELECT S.sname, C.cname
FROM Sportsman S, Coach C, Coaches T
WHERE S.sid = T.sid AND
      T.cid = C.cid AND 
      ABS(S.sage - C.cage) < 10;

--- 1e1
SELECT UNIQUE C.cname
FROM Coach C, Coaches T1, Coaches T2
WHERE C.cid = T1.cid AND
      T1.cid = T2.cid AND
      NOT T1.sid = T2.sid;
     
--- 1e2
SELECT C.cname
FROM Coach C
WHERE C.cid = ( SELECT T.cid 
                FROM Coaches T
                WHERE T.cid = C.cid
                GROUP BY T.cid
                HAVING COUNT(*) > 1 );

--- 1f
SELECT C.cname
FROM Coach C
WHERE C.cid IN ( SELECT T.cid
                 FROM Coaches T, Sportsman S
                 WHERE T.sid = S.sid AND S.scountry = 'nl') AND
      C.cid IN ( SELECT T2.cid
                  FROM Coaches T2, Sportsman S2
                  WHERE T2.sid = S2.sid AND S2.scountry = 'de'
                  GROUP BY T2.cid 
                  HAVING COUNT(*) = 1);

--- 1g
SELECT C.cname 
FROM Coach C
WHERE C.cid IN ( SELECT T.cid
                 FROM Coaches T, Sportsman S
                 WHERE T.sid = S.sid AND S.scountry = 'nl'
                 GROUP BY T.cid
                 HAVING COUNT(*) = 1) AND
      C.cid NOT IN ( SELECT T2.cid
                     FROM Coaches T2, Sportsman S2
                     WHERE T2.sid = S2.sid AND S2.scountry = 'de');

--- 1h
SELECT C.cname C
FROM Coach C
WHERE C.cid NOT IN ( SELECT T.cid
                     FROM Coaches T, Sportsman S
                     WHERE T.sid = S.sid AND S.scountry = 'fr');
      
--- 1i
SELECT C.cname C
FROM Coach C
WHERE C.cid IN ( SELECT T.cid
                 FROM Coaches T, Sportsman S
                 WHERE T.sid = S.sid AND S.scountry != 'fr');

--- 2a 
SELECT R.Branch, S.sname, R.NumHours
FROM Sleep R, Sportsman S, (SELECT R2.Branch, MAX(R2.NumHours) AS max
                            FROM Sleep R2
                            GROUP BY R2.Branch) SleepB 
WHERE S.sid = R.sid AND  R.Branch = SleepB.Branch AND R.NumHours = SleepB.max;


--- 2b
SELECT S.sname, Person.avg AS avg_sleep, Person2Branch.Branch 
FROM Sportsman S, (SELECT R.Branch, AVG(R.NumHours) AS avg
                   FROM Sleep R
                   GROUP BY R.Branch) Branch,
                  (SELECT DISTINCT R2.sid, R2.Branch
                   FROM Sleep R2) Person2Branch,
                  (SELECT R3.sid, AVG(R3.NumHours) AS avg
                   FROM Sleep R3
                   GROUP BY R3.sid) Person
WHERE S.sid = Person.sid AND
      S.sid = Person2Branch.sid AND 
      Person2Branch.Branch = Branch.Branch AND
      Person.avg < Branch.avg;


--- 3a
--- nothing done, when deleting a country from Members
DROP TABLE Votes;
DROP TABLE Members;
CREATE TABLE Members(
  name char(20),
  status char(20) NOT NULL,
  PRIMARY KEY ( name ),
  CHECK( (status = 'permanent') OR (status = 'elected') ) 
);

CREATE TABLE Votes(
  res char(20),
  country char(20),
  vote char(20) NOT NULL,
  PRIMARY KEY ( res, country ),
  FOREIGN KEY ( country ) REFERENCES Members( name ),
  CHECK( (vote = 'yes') OR ( vote = 'no' ) OR ( vote = 'abstrain' ) )
);


--some bogus data
INSERT INTO Members VALUES ('dk', 'elected' );
INSERT INTO Members VALUES ('nl', 'elected' );
INSERT INTO Members VALUES ('de', 'permanent' );
INSERT INTO Members VALUES ('en', 'permanent' );
INSERT INTO Members VALUES ('fr', 'permanent' );

INSERT INTO Votes VALUES ('oud', 'nl', 'no' );
INSERT INTO Votes VALUES ('oud', 'dk', 'yes' );
INSERT INTO Votes VALUES ('oud', 'en', 'yes' );
INSERT INTO Votes VALUES ('nieuw', 'nl', 'yes' );
INSERT INTO Votes VALUES ('oud', 'de', 'no' );
INSERT INTO Votes VALUES ('oud1', 'de', 'no' );

SELECT V.res
FROM Votes V
GROUP BY V.res
HAVING 0 = ( SELECT COUNT(*)
             FROM Votes V2, Members M
             WHERE V.res = V2.res AND
             V2.country = M.name AND
             M.status = 'permanent' AND
             V2.vote = 'no' ) AND
       14 < ( SELECT COUNT(*)
             FROM Votes V3
             WHERE V3.res = V.res AND
                  V3.vote = 'yes');

---3c
--this is not a correct answer, cause it don't give value to countries with no veto votes, but it lists all the 'permanent countries'
SELECT M.name, Vote.veto
FROM Members M, ( SELECT V.country, Count (*) AS veto 
                  FROM Votes V 
                  WHERE V.vote = 'no'
                  GROUP BY V.country
                ) Vote
WHERE M.status = 'permanent' AND M.name = Vote.country (+) ;