Jul 14 2010

Some notes on SQL: 4 – Advanced select

This is the fourth in a series of blog posts on SQL, the first covered creating a database, the second selecting information from a database, the third commands to modify the structure and contents of an existing database. This post covers more advanced commands for selecting information from a database and ways of manipulating the results returned. No claim of authority is made for these posts, they are mainly intended as my notes on the topic.

SQL supports CASE statements, similar to those which are found in a range of programming languages, they are used to write multiple comparison sequences more compactly:


UPDATE my_table
 SET    new_column = CASE
WHEN column1 = somevalue1 THEN newvalue1
WHEN column2 = somevalue2 THEN newvalue2
ELSE newvalue3
END;

The CASE statement can also be used in a SELECT:


SELECT title,
price,
budget = CASE price
WHEN price > 20.00 THEN ‘Expensive’
WHEN price BETWEEN 10.00 AND 19.99 THEN ‘Moderate’
WHEN price < 10.00 THEN ‘Inexpensive’
ELSE ‘Unknown’
END,
 FROM   titles

(This second example is from here)

The way in which results are returned from a SELECT statement can be controlled by the ORDER BY keyword with the ASC (or ASCENDING) and DESC (or DESCENDING) modifiers. Results can be ordered by multiple keys. The sort order is numbers before letters, and uppercase letters before lowercase letters.

SELECT title,purchased
 FROM   movie_table
 ORDER  BY title ASC, purchased DESC;

ASCENDING order is assumed in the absence of the explicit keyword.

There are various functions that can be applied to sets of rows returned in a query to produce a single value these include MIN, MAX, AVG, COUNT and SUM. The  functions are used like this:

SELECT SUM(sales)
 FROM   cookie_sales
 WHERE  first_name = ‘Nicole’;

This returns a sum of all of the “sales” values returned by the WHERE clause. Related is DISTINCT which is a keyword rather than a function so the syntax is slightly different:

SELECT DISTINCT sale_date
 FROM   cookie_sales
 ORDER  BY sale_date;

This returns a set of unique dates in the sale_date column.

The GROUP BY keyword is used to facilitate the use of functions such as SUM etc which take multiple arguments to produce a single output, or to reduce a list to distinct elements (in these circumstances it is identical to the DISTINCT keyword but execution may be faster). The format for GROUP BY is shown, by example below:


SELECT first_name, SUM(sales)
 FROM   cookie_sales
 GROUP  BY first_name;

This will return a sum of the “sales” by each person identified by “first_name”. A final keyword used to control the output of a SELECT statement is the LIMIT keyword which can take one or two parameters the behaviour for the two forms is quite different. One parameter form:

SELECT * FROM your_table LIMIT  5;

This returns the first five results from a SELECT. Two parameter form:

SELECT * FROM your_table LIMIT  5, 5;

This returns results 6,7,8,9 and 10 from the SELECT. The first parameter is the index of the first result to return (starting at 0 for the first position) and the second parameter is the number of results to return.


Keywords: CASE, WHEN, THEN, ELSE, ORDER BY, ASC, DESC, DISTINCT, MIN, MAX, AVG, COUNT, SUM, GROUP BY, LIMIT

Jul 12 2010

How does a magnet work?

How does a magnet work? This question arose on “I’m a Scientist, Get me out of here“, a fine piece of science communication which involved putting scientists in contact with school children. This is my attempt at an answer, which says a bit more about science in general but is utterly untimely. The short answer to the question is that magnets are made from atoms which act like little magnets and in a proper magnet are all lined up, but as an answer this is somewhat unsatisfactory.

From a scientific point of view, what you’d commonly call magnets are just one group of magnetic materials – the ferromagnets. They are accompanied in early magnetism courses for aspiring physics students by paramagnetic and diamagnetic materials. A ferromagnetic material, like iron, is strongly attracted to a magnet, a paramagnetic material is weakly attracted and a diamagnetic material is very weakly repelled. Diamagnetism and paramagnetism are useful for scientific research but it is ferromagnetism where all the practical applications are found. Iron, cobolt and nickel are the only ferromagnetic elements.

At this point I am ashamed to admit I nearly missed out on a tortured analogy to explain magnetism but fortunately I caught myself in time! Imagine, if you will, a crowd bearing vuvuzelas. Individuals in this crowd can blow their vuvuzelas in any direction they please, however much we might wish they didn’t. In a ferromagnet groups of vuvuzela players spotting their neighbours spontaneously face the same direction to play their devilish instruments. The whole crowd may not be blowing them in the same direction but groups of them will. They can be marshalled to all blow their horns in the same direction by a band leader, and once pointing in the same direction they will continue to face that way, even in the absence of the band leader.

The individuals in this group are atoms in a material, and the vuvuzelas represent the magnetic field of a single atom. Groups of players facing in the same direction represent magnetic domains and the band leader represents an applied magnetic field. The point about ferromagnets is they massively enhance an a magnetic field applied by something like a coil of wire with a current flowing through it – this is how you make an electromagnet. The difference between a “magnet” and any old bit of ferromagnet is that in a “magnet” all the domains have been lined up to face the same way.

In paramagnetic materials vuvuzelas players ignore their neighbours and play away in random directions, they respond in a somewhat feeble fashion to the directions of the band leader.

In diamagnetic materials the crowd have no vuvuzelas but use their hands as a substitute, rather petulantly they face the opposite direction to that proposed by the band leader. In scientific language the hands represent induced magnetic dipole moments.

But why is an atom magnetic? An atom could be magnetic because the electron orbiting the nucleus acts like a little current loop, which gives it a magnetic field like a little bar magnet (posh name for “little bar magnet” is “magnetic dipole moment”) but actually the majority of the magnetic dipole moment of an atom comes from the intrinsic magnetic dipole moment of individual electrons.

We really don’t know why the electron acts as a magnetic dipole, it is ascribed to a property known as ‘spin’ but it can’t be spin as we normally define it since electrons are, as far as we can tell, point-like – nobody has every managed to measure the diameter of an electron. Therefore how can we meaningfully describe it as spinning? In a sense the origin of the electron magnetic dipole moment is not important, it exists, we know what it is and we can use the measured value in our calculations for designing magnetic materials. This question of the “why” of fundamental properties of sub-atomic particles is what string theory seeks to address. For most scientists the answer is unimportant for practical applications, but for physicists in particular it is a nagging unpleasantness that we don’t know why.

Magnetism and electricity have been known since antiquity but as two very separate phenomena, and unsurprisingly really. Magnetism is a property of some funny rocks (lodestone) whilst electricity is a property of rubbed materials, and lemons. The connection between the two is far from obvious, the link was made in the early part of the 19th century. This is a recurring theme in science, we blithely teach that such and such is true, implying that it’s truth is pretty much self-evident and elide the fact that for most of history we have not believed these things and that it has taken the painstaking work of a number of very great minds to reveal these self-evident truths. I must admit to being a little unsure of the history in this area, taught in England the key figures were Michael Faraday who did much of the experimental work in linking electricity and magnetism followed by James Clerk Maxwell who formulated a mathematical theory. In the experimental area in particular there were many other participants in the story, I suspect who takes centre stage depends on where you are taught.

So there you have it: magnets work because the vuvuzela players copy each other and play in the same direction.

Jul 10 2010

The Lake District – Santon Bridge

Wast Water with Great Gable

Once again we return to the Lake District for a holiday. This time to Santon Bridge, close to the foot of Wast Water, at Hallflat Farm where we have a one bedroom converted cowshed rather more modern feeling than our previous stays in the Lake District with rather fine exposed wooden beams making it feel almost tent-like. The farm has been turned over completely to tourism, with half a dozen or so rentable units. We have a little area to sit out in the sunshine and neighbours to whom we can merely nod if we so desire. Shopping seems to be best done at the village store in Gosforth, a few miles down the road. There are local great-spotted woodpeckers the male apparently sports a natty red beret, the female is a little more subtle. Mrs SomeBeans managed to capture some video, I’ve never had a clear sight of a greater-spotted woodpecker – here they’re regular visitors to the bird feeder outside the kitchen.

It is traditional for each Lake District holiday that I show Mrs SomeBeans a campsite with it’s distant toilet block and flimsy canvas walls and highlight the comparison with the great luxury in which we stay. I saw much of Europe and the UK from a tent as a child, and whilst this no doubt greatly broadened my horizons I intend never to sleep under canvas (or nylon) again.

Last year we stayed at Chapel Stile in Great Langdale, a fine location from which we walked everyday with only one brief trip in the car to the head of the valley to ascend Bow Fell (whose head remained steadfastly in cloud). The cottage we stayed in was good, with an excellent kitchen the only drawback slightly restricted parking (not a problem if you never use the car) and rather limited outdoor seating. My parents honeymooned at the Dungeon Ghyll Hotel (famous amongst climbers), a mile or so up the road from our cottage. For shopping we used the local village general store which furnished our needs admirably, providing in addition to food, two very light macs which turned out to be rather useful. The previous two years we stayed in Borrowdale, at Rosthwaite, again providing walks from the door for most of a week. A slightly quieter location with fewer neighbours. Shopping this time was in Keswick, or rather Booths – a rather fine slatey version of Waitrose.

Alfred Wainwright is the undisputed king of the Lakes for people like us, not only was he intensely well-organised – planning his fantastic guides to be completed over a 13 year period which he hit almost to the week, he was fond of peaks and hated people. We differ only slightly in that we like the valleys too, and enjoy the wide variety of nature. A successful days walk for us is one in which me meet no other people and see some interesting nature: sundews, butterwort (carnivorous plants), miscelleous beetles, birds, orchids, and rocks. It’s unlikely that Wainwright strode along the broader, flatter paths with the Imperial March from Star Wars running through his mind, which is what Mrs SomeBeans and I do – it’s the syncopation of marching foot on gravel path.

What we did on our holidays this year (sounds like a school project):

Sunday: We venture out on a short walk of our own devising over to Nether Wasdale, part way around it starts raining VERY heavily, and we trudge through the rain for an hour and a half or so. Towards the end my well water-proofed boots are squelching vigorous and I struggle to identify any part of my body which is not wet. On our return to the cottage I wring out my socks. In the afternoon we head out to the local countryside: visiting Seascale, neighbour of the Sellafield nuclear reprocessing plant, Drigg (low level waste disposal). Ravenglass (we return here for a walk on Friday), sporting the terminus of a dinky steam railway. Later we head up towards Eskdale Green, a mile or so from Boot where the Cumbrian shooter finally met his own end. We’d passed flowers by the roadside on the way to Seascale (a grim settlement whipped by the wind, featuring much gray concrete render). Eskdale, in which Boot lies, is different – almost fairy tale with heavily mossed deciduous woodland and little crags.

Steam train at Ravenglass

Monday: we do a walk from “the book” (#12 Wast Water and Irton Fell), taking in the foot of Wast Water and heading up Irton Fell. The walk is very varied with some meadow on the valley floor, lake side, steep brackened climb, forestry woodland, and high moor. The view up Wast Water towards Great Gable (scaled on a previous holiday) is excellent. Nature-wise we catch our first sight of sundew, a very large caterpillar and note we are walking on granite, not the slate of Borrowdale. Very few people seen.

Tuesday: a trip to Ennerdale to walk around the Water and then onto St Bees where we looked in rock pools. We did the book-walk (#13) around Ennerdale Water, although it’s a very simple affair. The path on the left of the lake is a forestry road, and hence well-made, we met no-one on this stretch although a few vehicles passed us in each direction. The path back along the right of the lake is much more rugged and was much busier, this is part of the coast to coast path. For lunch, naturally overlooking the lake, we were joined by a frog. St Bees and Egremont seem to have seen better days, they both have quite significant buildings (St Bees a smart school, a hall and a large church) which don’t seem consistent with their current situation. Honeysuckle seems to be the flower of the week.

Wednesday: another trip of our own devising, today the weather was uncertain in the morning so we made a relatively late start to walking (10am). We parked close to Nether Wasdale and went up to Whin Rigg and then on to Illgill Head then dropping down into Wasdale Head. As it was clouds wafted across us for an hour or so in the morning then it cleared, and as we finished walking just before 4pm it was fairly clear and sunny. For the first time on a walk, Mrs SomeBeans and I parted ways at Wasdale Head. I walked along the near shore of Wast Water, whilst Mrs S. took the road. This was for fear of the screes, probably justified, there are two bands of scree: the first is fairly civilised with a path across it. The second is rather more challenging, with no discernable path just a jumble of boulders of man-size and above. I collected an impressive scrape on the approach to the screes, spotting a distant Mrs S across the lake apparently pulling ahead I hurried on rather quicker than was wise and ended up head first in bracken! I am forming a fine scab, the like of which I have not had since childhood.

Thursday: We walked up Overbeck to Dore Head from where fantastic views of Pillar and into Mosedale are available. The original plan was to head up on a great loop over Red Pike but this turned out to be a bit over-ambitious. We contemplated Yewbarrow which looks quite difficult to get onto, Wainwright appears to agree but finds a easier route. Cows were used in the valley, and each cowpat had it’s own little forest of mushrooms.

Friday: A wet day with intermittent showers most the morning as we did the Ravenglass and Muncaster walk (#15), this is a fairly long walk (7 miles) well suited to the poor weather, with a large fraction of the route under cover of trees and relatively low altitude – even so we briefly entered cloud on the Muncaster ridge. Highlights were the ruins of a Roman Bath on the outskirts of Ravenglass, these ruins comprise walls several metres above ground level including complete arches and an alcove, very few Roman ruins in the UK extend above ground level. A second highlight was a infestation of tiny frogs on a forest path.

Tiny frog!
Ravenglass Roman Baths

All in all not a bad holiday, weather about par for the Lake District with 2 days seriously affected by rain, and the rest threatening rain at some point. Not as much big mountain walking as we have done in the past, but more varied with trips to the seaside. The area feels quieter than the central areas of the Lake District, with free or honesty box parking. Once again mobile phones entirely useless for the entire length of the holiday due to lack of reception.

Jul 01 2010

A brief return to politics – the Budget

Following on from my pre-Budget “Sceptical look at the economy“, I thought I’d return to politics and the Budget.

The financial position seems to be largely what was expected before the election and the size of the proposed cuts seems consistent with the scale of cuts in Spain, Greece and Ireland.

What I would have done? I suppose I prevaricated in my last post on what I would have done in the recent Budget. To be a bit more explicit: I would have put probably something like 3p on basic rate tax, lifted the lower threshold of basic rate and brought down the threshold to the higher rate. And looked to cutting something like 15% across government spending with no ring-fencing. I may have put up capital gains tax a bit more at the higher rate and not reduced corporation tax – but to be honest these measures don’t bring in much cash anyway. As for benefit cuts, I’d probably have gone for means-testing things such as child benefit, winter fuel allowance and so forth. My impression is this would approximately fill the appropriate gaps (but I haven’t done any calculation).

But then nobody voted for me, and the Liberal Democrat experience is if you offer the voting public an increase in income tax they say how great this is, and how they’d really love you to spend the money where you’ve said you’ll spend it, and then vote for someone else who has promised not to raise income tax. Of the national parties the Green Party manifesto was the only one to imply they would not make any cuts, but increase overall taxation to cover the structural deficit; electorally the Green Party didn’t do that well in the General Election with about 1.0% of the vote.

As it stands the Budget was somewhat different from my preferred option. There are a few mitigating factors but I’m not convinced that VAT rises are a good way to raise tax (it has been suggested that they are better than income tax rises because they do not fall on essentials and they are “voluntary” to a degree, which income tax rises most definitely are not). It seems rather notable that there was much symbolic “dipping of the hands in the blood” by Nick Clegg, Danny Alexander and Vince Cable, you’d have though the Tories would have been a bit more forthcoming about defending a budget in which they were the majority partner and which largely matched their electoral commitments.

The Labour Party has started pointing out that this is a very political Budget, that’s true, and so was their idea of defering cuts into next year. For the Opposition this has the positive political benefit of not needing to be clear about what you would do until well after the General Election (and not even then) and allows you free-reign to criticise cuts by the incoming government without proferring an alternative because obviously you’d be doing this next year when things would have become magically better.

I’ve come to the conclusion that macroeconomics is almost entirely about politics, and the vehemence with which economic opinions are presented leads me to believe that everyone realises they don’t actually know what they’re talking about and that by shouting loudly they can get away with it. Presumably MP’s and ministers feel they have learnt to run the economy through the odd lecture course on the infamous Politics, Philosophy and Economics undergraduate degree course at Oxford. It seems notable that prior to the election the global consensus appears to have been for “economic stimulus” and after it is for “deficit reduction” (with the exception of the US). I’m not clear how this has happened, because I can’t believe it’s entirely driven by the UK election.

Inferring what the voting public want from elections and opinion polls is always a tricky business but the evidence seems to be they’re happy with the Budget and it’s pretty much what they expected. I suspect the reason for this is that the majority of them will be in the private sector and over the past few years the companies they work in would have laid people off, been on pay freezes and, over a longer period, treated employees less generously in pension terms but this largely hasn’t happened in the public sector. The same opinion poll shows fairly good support for maintaining the state pension whilst “cutting benefits for those of working age”. 

The Office of Budget Responsibility is pretty upfront in saying it’s estimates for GDP growth are subject to large uncertainty (see p10 of this report, and also Annex A on how figures are derived – hat-tip to Christopher Cook for that). The biggest problem seems to be that recession are utterly unpredictable. I’d be interested to see similar analysis for unemployment figures – can’t help thinking they’re not going to be good.

My useful pieces of contextual information for the day: UK employed population is about 30million, of which about 5million are in the public sector.

Jun 24 2010

Some notes on SQL: 3 – changing a table

This is the third in a series of blog posts on SQL, the first covered creating a database, the second selecting information from a database. This post covers commands to modify the structure and contents of an existing database. No claim of authority is made for these posts, they are mainly intended as my notes on the topic.

UPDATE and DELETE allow the rows in a table to be either updated or deleted according to a select-like WHERE clause. This is UPDATE, acting on multiple columns:

UPDATE your_table
SET    first_column = ‘newvalue’,
second_column = ‘another_value’
WHERE  some_column = ‘a test’;

And this is the DELETE command:

DELETE FROM your_table
WHERE  some_column = ‘a test’;

In combination with the ALTER keyword, the following operations can be performed:
The CHANGE keyword allows the name and data type of an existing column to be changed.

ALTER TABLE project_table 
CHANGE COLUMN a_silly_column_name a_better_column_name VARCHAR(100), 
CHANGE COLUMN another_poorly_named_column a_better_name VARCHAR(30);

It’s necessary to be cautious here because data loss can occur depending on the source and destination types, for example going from VARCHAR(100) to VARCHAR(30) could potentially lose 70 characters.
The MODIFY keyword allows the data type or position of an existing column to be changed.

ALTER TABLE my_table 
MODIFY COLUMN target_column VARCHAR(120), 
MODIFY COLUMN another_column AFTER target_column;

The ADD keyword allows new columns to be added to a table:

ALTER TABLE my_table 
ADD COLUMN new_column INT NOT NULL AUTO_INCREMENT FIRST;

The ADD and MODIFY keywords take position identifiers: FIRST, LAST and BEFORE, AFTER – which require a second column identifier as indicated in the MODIFY example.

In addition RENAME TO allows the table to be renamed:

ALTER TABLE poor_name RENAME TO good_name;

And DROP deletes a column:

ALTER TABLE my_table DROP COLUMN unwanted_column; 

Obviously you should use DROP COLUMN cautiously!

Keywords: ALTER, UPDATE, CHANGE, MODIFY, ADD, DELETE, AFTER