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
-- 找出没有政党的议员
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