2: Filtering Records

Filtering Rows with "Where"

What if we wanted to find all the records in our table that have an area greater that 4000?

SELECT name, area FROM cities WHERE area > 4000;

How is the statement interpreted? It is best not to thing of it as left to right.

The order for our statement:

  1. FROM cities
  2. WHERE area > 4000
  3. SELECT name, area

So we have filtered before we have selected.

The are many comparison operators that we could have used instead of area.

An example of a more complicated comparison:

Compound WHERE clauses

We can make some more complicated queries:

SELECT name, area FROM cities WHERE area BETWEEN 2000 AND 4000;

To make a compound comparison, we can use AND:

SELECT name, area FROM cities WHERE area BETWEEN 2000 AND 4000 AND name = 'Dehli';

You can combine as many AND and OR statements as required.

Calculations in WHERE clauses

You can in fact do calculations in the WHERE clause. Note: you need to consider the order of operations for this:

SELECT name, population / area AS density FROM cities WHERE population / area > 6000;

Updating Records

UPDATE cities SET population = 39505000 WHERE name = 'Tokyo';

Deleting Records

DELETE FROM cities WHERE name = 'Dehli';