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:
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:
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
SELECT name, area FROM cities WHERE area BETWEEN 2000 AND 4000 AND name = 'Dehli';
You can combine as many
OR statements as required.
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;
UPDATE cities SET population = 39505000 WHERE name = 'Tokyo';
DELETE FROM cities WHERE name = 'Dehli';