Hands on: SQL conditions and operators

As I said in a previous column, knowing some SQL gives a great boost to your querying skills, and so the theme continues here.

We’ve already met the WHERE clause, as in:
SELECT LastName, FirstName
FROM People
WHERE FirstName=”Bill”;
which returns anyone in the People table with the first name of Bill. The WHERE clause is called a ‘condition’: it defines the conditions under which we want rows returned. The equals sign that follows WHERE is an ‘operator’ and is one of many that can be used.

A condition is evaluated and the rows for which it evaluates as true are included in the answer table. Apart from using the = operator, I slipped one other into the previous column mentioned above:
SELECT FirstName, LastName
FROM People
WHERE (3<4);

In this query the condition is ‘where three is less than four’ and will always evaluate to true and therefore return all rows.

Given the table shown in the accompanying picture, we can find all people with more than three children:
SELECT FirstName, ChildNo
FROM People
WHERE ChildNo >3;
(Q01 – see below for an explanation of the numbers in brackets.)

This returns four rows. (The file DBCMAR08.MDB on the cover disc for the March 2008 issue of PCW contains all the queries here, each has a number (ie (Q01) shown after the query). To exclude people with six or more children, you’d alter the condition to:
WHERE ChildNo>3 AND ChildNo<6;
(Q02)
and two rows are returned. Alternatively you could use IN to identify the acceptable values, which returns the same two rows:
WHERE ChildNo IN (4,5);
(Q03)

IN also works with text:
WHERE HomeTown IN (‘London’,’Wigan’,’Bedford’);
(Q04)
finds people from these towns. For numbers of children of between two and four, it’ll be no surprise that you’d use the BETWEEN… AND operator:
WHERE ChildNo BETWEEN 2 AND 4;
(Q05)

Numbers of two, three and four are returned. This operator can also be used with text strings, for instance:
WHERE HomeTown BETWEEN 4
‘Edinburgh’ AND ‘London’;
(Q06)
returns all home towns that are alphabetically between, and including, Edinburgh and London. The six comparison operators work with numbers, text and dates, so to find all home towns after (alphabetically speaking) but not including Hereford you can use:
WHERE HomeTown>’Hereford’;
(Q07)
and this:
WHERE HomeTown>=’Hereford’;
(Q08)
includes Hereford, while this:
WHERE People.DateJoined>4
=#1/3/2004#;
(Q09)
will return all rows with join dates on or later than 1 March 2004.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!