作业

-- createholy, 1, 50
DROP TABLE msp;
DROP TABLE party;

CREATE TABLE party (partyCode VARCHAR(10) NOT NULL
,partyName VARCHAR(50)
,leader VARCHAR(50)
,PRIMARY KEY (partyCode)
)DEFAULT CHARSET=utf8;

CREATE TABLE msp (mspName VARCHAR(50) NOT NULL
,party VARCHAR(10)
,constituency VARCHAR(50)
,PRIMARY KEY (mspName)
,FOREIGN KEY (party) REFERENCES party(partyCode)
)DEFAULT CHARSET=utf8;

CREATE INDEX msp_party ON msp(party);


GO
-- tabparty, 1, 50
INSERT INTO party VALUES ('Com','Communist',NULL);
INSERT INTO party VALUES ('Con','Conservative','McLetchie MSP, David');
INSERT INTO party VALUES ('Green','Green',NULL);
INSERT INTO party VALUES ('Lab','Labour','Dewar MSP, Rt Hon Donald');
INSERT INTO party VALUES ('LD','Liberal Democrat','Wallace QC MSP, Mr Jim');
INSERT INTO party VALUES ('NLP','Natural Law Party',NULL);
INSERT INTO party VALUES ('SNP','Scottish National Party','Salmond MSP, Mr Alex');
INSERT INTO party VALUES ('SSP','Scottish Socialist Party',NULL);
INSERT INTO party VALUES ('SWP','Socialist Workers Party',NULL);
-- tabmsp, 1, 50
INSERT INTO msp VALUES ('Adam MSP, Brian','SNP','North East Scotland');
INSERT INTO msp VALUES ('Aitken MSP, Bill','Con','Glasgow');
INSERT INTO msp VALUES ('Alexander MSP, Ms Wendy','Lab','Paisley North');
INSERT INTO msp VALUES ('Baillie MSP, Jackie','Lab','Dumbarton');
INSERT INTO msp VALUES ('Barrie MSP, Scott','Lab','Dunfermline West');
INSERT INTO msp VALUES ('Boyack MSP, Ms Sarah','Lab','Edinburgh Central');
INSERT INTO msp VALUES ('Brankin MSP, Rhona','Lab','Midlothian');
INSERT INTO msp VALUES ('Brown MSP, Robert','LD','Glasgow');
INSERT INTO msp VALUES ('Campbell MSP, Colin','SNP','West of Scotland');
INSERT INTO msp VALUES ('Canavan MSP, Dennis',NULL,'Falkirk West');
INSERT INTO msp VALUES ('Chisholm MSP, Malcolm','Lab','Edinburgh North and Leith');
INSERT INTO msp VALUES ('Craigie MSP, Cathie','Lab','Cumbernauld and Kilsyth');
INSERT INTO msp VALUES ('Crawford JP MSP, Bruce','SNP','Mid-Scotland and Fife');
INSERT INTO msp VALUES ('Cunningham MSP, Roseanna','SNP','Perth');
INSERT INTO msp VALUES ('Curran MSP, Ms Margaret','Lab','Glasgow Baillieston');
INSERT INTO msp VALUES ('Davidson MSP, Mr David','Con','North East Scotland');
INSERT INTO msp VALUES ('Deacon MSP, Susan','Lab','Edinburgh East and Musselburgh');
INSERT INTO msp VALUES ('Dewar MSP, Rt Hon Donald','Lab','Glasgow Anniesland');
INSERT INTO msp VALUES ('Douglas-Hamilton QC MSP, Rt Hon Lord James','Con','Lothians');
INSERT INTO msp VALUES ('Eadie MSP, Helen','Lab','Dunfermline East');
INSERT INTO msp VALUES ('Elder MSP, Dorothy-Grace','SNP','Glasgow');
INSERT INTO msp VALUES ('Ewing FRSA MSP, Dr Winnie','SNP','Highlands and Islands');
INSERT INTO msp VALUES ('Ewing MSP, Fergus','SNP','Inverness East, Nairn and Lochaber');
INSERT INTO msp VALUES ('Ewing MSP, Mrs Margaret','SNP','Moray');
INSERT INTO msp VALUES ('Fabiani MSP, Linda','SNP','Central Scotland');
INSERT INTO msp VALUES ('Ferguson MSP, Patricia','Lab','Glasgow Maryhill');
INSERT INTO msp VALUES ('Fergusson MSP, Alex','Con','South of Scotland');
INSERT INTO msp VALUES ('Finnie MSP, Ross','LD','West of Scotland');
INSERT INTO msp VALUES ('Galbraith FRCSGlas MP MSP, Mr Sam','Lab','Strathkelvin and Bearsden');
INSERT INTO msp VALUES ('Gallie MSP, Phil','Con','South of Scotland');
INSERT INTO msp VALUES ('Gibson MSP, Mr Kenneth','SNP','Glasgow');
INSERT INTO msp VALUES ('Gillon MSP, Karen','Lab','Clydesdale');
INSERT INTO msp VALUES ('Godman MSP, Trish','Lab','West Renfrewshire');
INSERT INTO msp VALUES ('Goldie MSP, Miss Annabel','Con','West of Scotland');
INSERT INTO msp VALUES ('Gorrie OBE MSP, Donald','LD','Central Scotland');
INSERT INTO msp VALUES ('Grahame MSP, Christine','SNP','South of Scotland');
INSERT INTO msp VALUES ('Grant MSP, Rhoda','Lab','Highlands and Islands');
INSERT INTO msp VALUES ('Gray MSP, Iain','Lab','Edinburgh Pentlands');
INSERT INTO msp VALUES ('Hamilton MSP, Mr Duncan','SNP','Highlands and Islands');
INSERT INTO msp VALUES ('Harding MSP, Mr Keith','Con','Mid-Scotland and Fife');
INSERT INTO msp VALUES ('Harper MSP, Robin','Green','Lothians');
INSERT INTO msp VALUES ('Henry MSP, Hugh','Lab','Paisley South');
INSERT INTO msp VALUES ('Home Robertson MSP, Mr John','Lab','East Lothian');
INSERT INTO msp VALUES ('Hughes MSP, Janis','Lab','Glasgow Rutherglen');
INSERT INTO msp VALUES ('Hyslop MSP, Fiona','SNP','Lothians');
INSERT INTO msp VALUES ('Ingram MSP, Mr Adam','SNP','South of Scotland');
INSERT INTO msp VALUES ('Jackson MSP, Dr Sylvia','Lab','Stirling');
INSERT INTO msp VALUES ('Jackson QC MSP, Gordon','Lab','Glasgow Govan');
INSERT INTO msp VALUES ('Jamieson MSP, Cathy','Lab','Carrick, Cumnock and Doon Valley');
INSERT INTO msp VALUES ('Jamieson MSP, Margaret','Lab','Kilmarnock and Loudoun');
-- tabmsp, 51, 50
INSERT INTO msp VALUES ('Jenkins MSP, Ian','LD','Tweeddale, Ettrick and Lauderdale');
INSERT INTO msp VALUES ('Johnston MSP, Mr Nick','Con','Mid-Scotland and Fife');
INSERT INTO msp VALUES ('Johnstone MSP, Alex','Con','North East Scotland');
INSERT INTO msp VALUES ('Kerr MSP, Mr Andy','Lab','East Kilbride');
INSERT INTO msp VALUES ('Lamont MSP, Johann','Lab','Glasgow Pollok');
INSERT INTO msp VALUES ('Livingstone MSP, Marilyn','Lab','Kirkcaldy');
INSERT INTO msp VALUES ('Lochhead MSP, Richard','SNP','North East Scotland');
INSERT INTO msp VALUES ('Lyon MSP, George','LD','Argyll and Bute');
INSERT INTO msp VALUES ('MacAskill MSP, Mr Kenny','SNP','Lothians');
INSERT INTO msp VALUES ('Macdonald MSP, Lewis','Lab','Aberdeen Central');
INSERT INTO msp VALUES ('MacDonald MSP, Ms Margo','SNP','Lothians');
INSERT INTO msp VALUES ('Macintosh MSP, Mr Kenneth','Lab','Eastwood');
INSERT INTO msp VALUES ('MacKay MSP, Angus','Lab','Edinburgh South');
INSERT INTO msp VALUES ('MacLean MSP, Kate','Lab','Dundee West');
INSERT INTO msp VALUES ('Macmillan MSP, Maureen','Lab','Highlands and Islands');
INSERT INTO msp VALUES ('Martin MSP, Paul','Lab','Glasgow Springburn');
INSERT INTO msp VALUES ('Marwick MSP, Tricia','SNP','Mid-Scotland and Fife');
INSERT INTO msp VALUES ('Matheson MSP, Mr Michael','SNP','Central Scotland');
INSERT INTO msp VALUES ('McAllion MSP, Mr John','Lab','Dundee East');
INSERT INTO msp VALUES ('McAveety MSP, Mr Frank','Lab','Glasgow Shettleston');
INSERT INTO msp VALUES ('McCabe MSP, Mr Tom','Lab','Hamilton South');
INSERT INTO msp VALUES ('McConnell MSP, Mr Jack','Lab','Motherwell and Wishaw');
INSERT INTO msp VALUES ('McGrigor MSP, Mr Jamie','Con','Highlands and Islands');
INSERT INTO msp VALUES ('McGugan MSP, Irene','SNP','North East Scotland');
INSERT INTO msp VALUES ('McIntosh MSP, Mrs Lyndsay','Con','Central Scotland');
INSERT INTO msp VALUES ('McLeish MSP, Henry','Lab','Central Fife');
INSERT INTO msp VALUES ('McLeod MSP, Fiona','SNP','West of Scotland');
INSERT INTO msp VALUES ('McLetchie MSP, David','Con','Lothians');
INSERT INTO msp VALUES ('McMahon MSP, Mr Michael','Lab','Hamilton North and Bellshill');
INSERT INTO msp VALUES ('McNeil MSP, Duncan','Lab','Greenock and Inverclyde');
INSERT INTO msp VALUES ('McNeill MSP, Pauline','Lab','Glasgow Kelvin');
INSERT INTO msp VALUES ('McNulty MSP, Des','Lab','Clydebank and Milngavie');
INSERT INTO msp VALUES ('Monteith MSP, Mr Brian','Con','Mid-Scotland and Fife');
INSERT INTO msp VALUES ('Morgan MSP, Alasdair','SNP','Galloway and Upper Nithsdale');
INSERT INTO msp VALUES ('Morrison MSP, Mr Alasdair','Lab','Western Isles');
INSERT INTO msp VALUES ('Muldoon MSP, Bristow','Lab','Livingston');
INSERT INTO msp VALUES ('Mulligan MSP, Mrs Mary','Lab','Linlithgow');
INSERT INTO msp VALUES ('Mundell MSP, David','Con','South of Scotland');
INSERT INTO msp VALUES ('Munro MSP, Mr John','LD','Ross, Skye and Inverness West');
INSERT INTO msp VALUES ('Murray MSP, Dr Elaine','Lab','Dumfries');
INSERT INTO msp VALUES ('Neil MSP, Alex','SNP','Central Scotland region');
INSERT INTO msp VALUES ('Oldfather MSP, Ms Irene','Lab','Cunninghame South');
INSERT INTO msp VALUES ('Paterson MSP, Mr Gil','SNP','Central Scotland');
INSERT INTO msp VALUES ('Peacock MSP, Peter','Lab','Highlands and Islands');
INSERT INTO msp VALUES ('Peattie MSP, Cathy','Lab','Falkirk East');
INSERT INTO msp VALUES ('Quinan MSP, Mr Lloyd','SNP','West of Scotland');
INSERT INTO msp VALUES ('Radcliffe MSP, Nora','LD','Gordon');
INSERT INTO msp VALUES ('Raffan MSP, Mr Keith','LD','Mid-Scotland and Fife');
INSERT INTO msp VALUES ('Reid MSP, Mr George','SNP','Mid-Scotland and Fife');
INSERT INTO msp VALUES ('Robison MSP, Shona','SNP','North East Scotland');
-- tabmsp, 101, 50
INSERT INTO msp VALUES ('Robson MSP, Euan','LD','Roxburgh and Berwickshire');
INSERT INTO msp VALUES ('Rumbles MSP, Mr Mike','LD','West Aberdeenshire and Kincardine');
INSERT INTO msp VALUES ('Russell MSP, Michael','SNP','South of Scotland');
INSERT INTO msp VALUES ('Salmond MSP, Mr Alex','SNP','Banff and Buchan');
INSERT INTO msp VALUES ('Scanlon MSP, Mary','Con','Highlands and Islands');
INSERT INTO msp VALUES ('Scott MSP, John','Con','Ayr');
INSERT INTO msp VALUES ('Scott MSP, Tavish','LD','Shetland');
INSERT INTO msp VALUES ('Sheridan MSP, Tommy','SSP','Glasgow');
INSERT INTO msp VALUES ('Simpson MSP, Dr Richard','Lab','Ochil');
INSERT INTO msp VALUES ('Smith MSP, Elaine','Lab','Coatbridge and Chryston');
INSERT INTO msp VALUES ('Smith MSP, Iain','LD','North East Fife');
INSERT INTO msp VALUES ('Smith MSP, Mrs Margaret','LD','Edinburgh West');
INSERT INTO msp VALUES ('Steel KBE MSP, Rt Hon Sir David','LD','Lothians');
INSERT INTO msp VALUES ('Stephen MSP, Nicol','LD','Aberdeen South');
INSERT INTO msp VALUES ('Stone MSP, Mr Jamie','LD','Caithness, Sutherland and Easter Ross');
INSERT INTO msp VALUES ('Sturgeon MSP, Ms Nicola','SNP','Glasgow');
INSERT INTO msp VALUES ('Swinney MSP, Mr John','SNP','North Tayside');
INSERT INTO msp VALUES ('Thomson MSP, Elaine','Lab','Aberdeen North');
INSERT INTO msp VALUES ('Tosh MSP, Mr Murray','Con','South of Scotland');
INSERT INTO msp VALUES ('Ullrich MSP, Mrs Kay','SNP','West of Scotland');
INSERT INTO msp VALUES ('Wallace MSP, Ben','Con','North East Scotland');
INSERT INTO msp VALUES ('Wallace QC MSP, Mr Jim','LD','Orkney');
INSERT INTO msp VALUES ('Watson MSP, Mike','Lab','Glasgow Cathcart');
INSERT INTO msp VALUES ('Welsh MSP, Mr Andrew','SNP','Angus');
INSERT INTO msp VALUES ('White MSP, Ms Sandra','SNP','Glasgow');
INSERT INTO msp VALUES ('Whitefield MSP, Karen','Lab','Airdrie and Shotts');
INSERT INTO msp VALUES ('Wilson MSP, Allan','Lab','Cunninghame North');
INSERT INTO msp VALUES ('Wilson MSP, Andrew','SNP','Central Scotland');
INSERT INTO msp VALUES ('Young OBE MSP, John','Con','West of Scotland');

SELECT * FROM party;
SELECT * 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