1. One MSP was kicked out of the Labour party and has no party. Find him
select name from msp
where party is null;
2. Obtain a list of all parties and leaders
select name,leader from party;
3. Give the party and the leader for the parties which have leaders
select name,leader from party
where leader is not null;
4. Obtain a list of all parties which have at least one MSP
select party.name
from msp join party on party = code
group by party.name
having count(msp.name) >=1;
5. Obtain a list of all MSPs by name, give the name of the MSP and the name of the party where available. Be sure that Canavan MSP, Dennis is in the list. Use ORDER BY msp.name to sort your output by MSP
select msp.name,party.name
from msp left join party on party = code
group by msp.name,party.name
order by msp.name;
6. Obtain a list of parties which have MSPs, include the number of MSPs
select party.name,count(msp.name)
from msp right join party on party = code
where msp.name is not null
group by party.name;
7. A list of parties with the number of MSPs; include parties with no MSPs
select party.name,count(msp.name)
from msp right join party on party = code
group by party.name;