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.