1 DROP TABLE msp; 2 DROP TABLE party; 3  4 CREATE TABLE party (partyCode VARCHAR(10) NOT NULL 5                     ,partyName VARCHAR(50) 6             ,leader VARCHAR(50) 7             ,PRIMARY KEY (partyCode) 8             )DEFAULT CHARSET=utf8; 9     10 CREATE TABLE msp (mspName VARCHAR(50) NOT NULL11                   ,party VARCHAR(10)12           ,constituency VARCHAR(50)13                   ,PRIMARY KEY (mspName)14                   ,FOREIGN KEY (party) REFERENCES party(partyCode)15                   )DEFAULT CHARSET=utf8;16 17 CREATE INDEX msp_party ON msp(party);18 19 INSERT INTO party VALUES (‘Com‘,‘Communist‘,NULL);20 INSERT INTO party VALUES (‘Con‘,‘Conservative‘,‘McLetchie MSP, David‘);21 INSERT INTO party VALUES (‘Green‘,‘Green‘,NULL);22 INSERT INTO party VALUES (‘Lab‘,‘Labour‘,‘Dewar MSP, Rt Hon Donald‘);23 INSERT INTO party VALUES (‘LD‘,‘Liberal Democrat‘,‘Wallace QC MSP, Mr Jim‘);24 INSERT INTO party VALUES (‘NLP‘,‘Natural Law Party‘,NULL);25 INSERT INTO party VALUES (‘SNP‘,‘Scottish National Party‘,‘Salmond MSP, Mr Alex‘);26 INSERT INTO party VALUES (‘SSP‘,‘Scottish Socialist Party‘,NULL);27 INSERT INTO party VALUES (‘SWP‘,‘Socialist Workers Party‘,NULL);28 29 SELECT * FROM party;30 SELECT * FROM msp;31 32 -- 找出没有政党的议员33 SELECT mspName FROM msp WHERE party IS NULL;34 35 -- 列出所有的政党和领导者36 SELECT partyName,leader FROM party;37 38 -- 列出所有有领导者的政党39 SELECT partyName FROM party WHERE leader IS NOT NULL;40 41 -- 列出至少有一个议员的政党42 SELECT p.partyName,COUNT(m.mspName) FROM party p,msp m WHERE p.partyCode=m.party GROUP BY p.partyName HAVING COUNT(m.mspName)>0;43  44 -- 列出所有议员名字和他们所属政党45 SELECT p.partyName,m.mspName FROM party p,msp m WHERE p.partyCode=m.party GROUP BY m.mspName;46 47 -- 列出所有政党和每个政党议员人数48 SELECT COUNT(m.mspName),p.partyName FROM party p,msp m WHERE p.partyCode=m.party GROUP BY p.partyName;