# Difference between revisions of "Scottish Parliament"

### Scottish Parliament

The data includes all Members of the Scottish Parliament (MSPs) in 1999. Most MSPs belong to a political party. Some parties have a leader who is an MSP. There are two tables:

msp
Name Party Constituency
Adam MSP, BrianSNPNorth East Scotland
Aitken MSP, BillConGlasgow
Alexander MSP, Ms WendyLabPaisley North
... Total number of records: 129
party
ConConservativeMcLetchie MSP, David
GreenGreen
LabLabourDewar MSP, Rt Hon Donald
... Total number of records: 9

## Dealing wit `NULL`

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

You might think that the phrase dept=NULL would work here. It doesn't. This is because NULL "propogates". Any normal expression that includes NULL is itself NULL, thus the value of the expressions 2+NULL and party || NULL and NULL=NULL for example are all NULL.

The `NULL` value does not cause a type error, however it does infect everything it touches with `NULL`-ness. We call this element the `bottom` value for the algebra - but we don't snigger because we are grown-ups. Bottom Type.

` `
`SELECT name FROM msp WHERE party IS NULL`

Obtain a list of all parties and leaders.

` `
`SELECT name, leader FROM party`

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

` `
```SELECT name, leader FROM party
WHERE leader IS NOT NUL```

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

` `
```SELECT DISTINCT party.name FROM msp, party
WHERE party=code```

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
ORDER BY msp.name```

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

` `
```SELECT party.name, COUNT(msp.name)
FROM msp, party
WHERE msp.party=party.code
GROUP BY party.name```

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```

## Note

Sadly Donald Dewar died in 2000. An able and popular leader of the Labour Party in Scotland.