Connor at 09:23, 18 July 2012

2012-07-18T09:23:59Z

Connor at 09:22, 18 July 2012

2012-07-18T09:22:21Z

Connor at 09:14, 18 July 2012

2012-07-18T09:14:28Z

Connor: Created page with "

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 ..."

2012-07-12T10:54:03Z

Created page with "<h3>Scottish Parliament</h3> 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 ..."

New page

<h3>Scottish Parliament</h3>
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:
<table border="1"><caption>msp</caption>
<tr>
<th >Name</th>
<th >Party</th>
<th >Constituency</th>
</tr>
<tr><td >Adam MSP, Brian</td><td >SNP</td><td >North East Scotland</td></tr>
<tr><td >Aitken MSP, Bill</td><td >Con</td><td >Glasgow</td></tr>
<tr><td >Alexander MSP, Ms Wendy</td><td >Lab</td><td >Paisley North</td></tr>
<tr><td colspan="3"><i>... Total number of records: 129</i></td></tr>
</table>
<table border="1"><caption>party</caption>
<tr>
<th >Code</th>
<th >Name</th>
<th >Leader</th>
</tr>
<tr><td >Con</td><td >Conservative</td><td >McLetchie MSP, David</td></tr>
<tr><td >Green</td><td >Green</td><td ></td></tr>
<tr><td >Lab</td><td >Labour</td><td >Dewar MSP, Rt Hon Donald</td></tr>

<tr><td colspan="3"><i>... Total number of records: 9</i></td></tr>
</table>

[[Selecting NULL values]]

<h2>Dealing wit <code>NULL</code></h2>
<div class='qu'>
One MSP was kicked out of the Labour party and has no party. Find him.
<div class='hint' title='Why we cannot use ='>
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.
<div title="Theory" class='hint'>
The <code>NULL</code> value does not cause a type error, however it does infect everything it touches with <code>NULL</code>-ness. We call this element the <code>bottom</code> value for the algebra - but we don't snigger because we are grown-ups. [http://c2.com/cgi/wiki?BottomType Bottom Type.]
</div>
</div>
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT name FROM msp WHERE party IS NULL
</source>
</div>

<div class='qu'>
Obtain a list of all parties and leaders.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT name, leader FROM party
</source>
</div>

<div class='qu'>
Give the party and the leader for the parties which have leaders.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT name, leader FROM party
WHERE leader IS NOT NUL
</source>
</div>

<div class='qu'>
Obtain a list of all parties which have at least one MSP.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT DISTINCT party.name FROM msp, party
WHERE party=code
</source>
</div>

[[Outer joins]]

<div class='qu'>
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 <code>Canavan MSP, Dennis</code> is in the list.
Use ORDER BY msp.name to sort your output by MSP.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT msp.name, party.name
FROM msp LEFT JOIN party ON party=code
ORDER BY msp.name
</source>
</div>

<div class='qu'>
Obtain a list of parties which have MSPs, include the number of MSPs.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT party.name, COUNT(msp.name)
FROM msp, party
WHERE msp.party=party.code
GROUP BY party.name
</source>
</div>

<div class='qu'>
A list of parties with the number of MSPs; include parties with no MSPs.
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT party.name, COUNT(msp.name)
FROM party
LEFT JOIN msp ON party.code=msp.party
GROUP BY party.name
</source>
</div>

@@ Line 119: / Line 119: @@
 [[Self_join |Self joins are the topic for the next tutorial]]
-</hr>
 <h2>Note</h2>
 <p>Sadly Donald Dewar died in 2000. An able and popular leader of the Labour Party in Scotland.</p>

← Older revision		Revision as of 09:22, 18 July 2012
Line 117:		Line 117:
	</source>		</source>
	</div>		</div>
		+
		+	[[Self_join \|Self joins are the topic for the next tutorial]]
		+	</hr>
		+	<h2>Note</h2>
		+	<p>Sadly Donald Dewar died in 2000. An able and popular leader of the Labour Party in Scotland.</p>

@@ Line 25: / Line 25: @@
    </table>
-[[Selecting NULL values]]
+[[Selecting_NULL_values. |Selecting NULL values]]
 <h2>Dealing wit <code>NULL</code></h2>

Scottish Parliament - Revision history

Connor at 09:23, 18 July 2012

Connor at 09:22, 18 July 2012

Connor at 09:14, 18 July 2012

Connor: Created page with "

Scottish Parliament