数据库党政专题

Navicat fro MySQL 只能添加121条数据，所以好多数据都为null
-- 找出没有政党的议员
SELECT * FROM msp WHERE party IS NULL;




-- 列出所有的政党和领导者
SELECT p.partyName,p.leader FROM party p;




-- 列出所有有领导者的政党
SELECT partyName FROM party WHERE leader IS NOT NULL;






-- 列出至少有一个议员的政党
SELECT p.partyName FROM party p,msp m WHERE p.partyCode=m.party GROUP 
BY p.partyName HAVING COUNT(m.mspName)>=1;


 
-- 列出所有议员名字和他们所属政党
SELECT p.partyName,m.mspName FROM party p LEFT JOIN msp m ON p.partyCode=m.party ;




-- 列出所有政党和每个政党议员人数
SELECT p.partyName,COUNT(m.mspName) FROM party p LEFT JOIN msp m ON p.partyCode=m.party GROUP BY p.partyName;


SELECT COUNT(mspName) FROM msp