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

--- 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 (+) ;