(a) The complete declaration is below:
CREATE TABLE Members ( name PRIMARY KEY, status CHAR(10) ); CREATE TABLE Votes ( res INT, country CHAR(50) REFERENCES Members(name), vote CHAR(10) CHECK(vote = 'yes' OR vote = 'no' OR vote = 'abstain'), PRIMARY KEY(res, country) ):
Oddball notes: You actually did not have to declare vote NOT NULL, because the check fails if the vote is NULL. You did not lose credit for including NOT NULL. Also, many people used conditions such as vote LIKE 'yes'. While not incorrect, there is no reason not to use =. I suspect some people were worrying that a CHAR(10) value is stored like 'yes*******', where * is some mysterious character. It is stored that way, but when the value is extracted by the DMBS for comparison, the value is just 'yes'.
(b,c,d) Here is the complete query, with comments.
SELECT res FROM Votes vv WHERE 9 <= ( /* count the number of votes for vv.res */ SELECT COUNT(*) FROM Votes WHERE vote = 'yes' AND res = vv.res ) AND NOT EXISTS ( /* a permanent member with vote no on vv.res */ SELECT * FROM Members, Votes WHERE name = country AND vote = 'no' AND status = 'permanent' AND res = vv.res );
(e,f) The continuation of the query, with comments. As many of you noted, this was essentially the "Surigao Strait" query from the Battleships assignment.
UNION ( /* names of permanent members that never vetoed, with 0 as the value of numVetos */ SELECT name, 0 AS numVetos FROM Members WHERE status = 'permanent' AND name NOT IN ( /* countries that ever voted no */ SELECT country FROM Votes WHERE vote = 'no' ) );