8 Scottish Parliament-SQLZoo

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;