SqlZoo习题答案：Dealing with NULL.

习题地址：http://sqlzoo.net/4.htm

 

表结构：　　msp(name, party, constituency)

　　　　　　party(code, name, leader)
　　　　　　msp.party = party.code

 

1a.One MSP was kicked out of the Labour party and has no party. Find him.

select name from msp where party is null

 

1b.Obtain a list of all parties and leaders.

select name,leader from party

 

1c.Give the party and the leader for the parties which have leaders.

select name, leader from party where leader is not null

 

1d.Obtain a list of all parties which have at least one MSP.

select distinct party.name from party  join msp 
on (msp.party = party.code) where msp.name is not null

 

2a.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, Dennisis 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 
(msp.party = party.code) order by msp.name

 

2b.Obtain a list of parties which have MSPs, include the number of MSPs.

select party.name, count(msp.name) from party join msp on (party.code = msp.party) group by party.name 

 

2c.A list of parties with the number of MSPs; include parties with no MSPs.

select party.name, count(msp.name) from party left join msp on (party.code = msp.party) group by party.name