Learning SQL - Select And Compare

These tips are mostly applicable to all forms of SQL, some are specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

Finding your data - Logical Operators

In the last lesson we looked at the Select command. You will use this a lot.

Now we are going to look at some of the operators and functions used to build queries. The first one and the most obvious is:

= Equals

select * from QiSQL_Parents where last_name = 'Brown'

No explanation needed there. Select all the records with a last name of 'Brown'

<> Not Equal

select * from QiSQL_Parents where last_name <> 'Brown'

You can also use != if you want.Now we're Selecting any records where the last name does not equal brown

> Greater Than

select * from QiSQL_Parents where last_name > 'Brown'

Yes, why not? SQL will not only check numeric values are bigger but will alphabetically test as well

>= Greater than OR Equal To

select * from QiSQL_Parents where id >= 1

This should return every row. IDs are automatically generated and start at 1, so the query asking for any row greater than or equal to 1 should return them all

< Smaller Than

select * from QiSQL_Parents where id < 2

<= Smaller than OR Equal To

select * from QiSQL_Parents where id <= 1

LIKE - Special Operator

select * from QiSQL_Parents where last_name like 'B%';

When you use a LIKE operator along with a & symbol, the % is used to represent any other character or set of characters - in the query above we are looking for last_names that begin with 'B'

select * from QiSQL_Parents where last_name like '%n';

Now we want all the names that end with 'n'

select * from QiSQL_Parents where last_name like '%o%';

The above query will return all rows where the letter 'o' is somewhere in the last name

select * from QiSQL_Parents where last_name like 'brown';

In this query we haven't substituted any characters using the % symbol so we have turned the Like operator into something like an equals operator but not quite - read on!

Just in Case

Eagle eyed readers will have noticed that in the last select statement I did not use the uppercase 'B' - if you ran the query you should have seen all the rows with a last name of 'Brown' returned. It makes sense really because 'brown' IS like 'Brown'. However if we were to ask for all rows that had a last name equal to 'brown'

select * from QiSQL_Parents where last_name = 'brown';

As you will see if you execute the query - No Rows Returned! 'brown' does NOT equal 'Brown'. Now we can use the Case changing functions. We can convert data using UPPER and LOWER.

It's probably a good idea of getting into the habit of using Upper and or Lower in your queries so you do nit get surprising results.

select * from QiSQL_Parents where upper(last_name) = upper('brown');
select * from QiSQL_Parents where lower(last_name) = lower('BRowN');
select * from QiSQL_Parents where lower(last_name) = 'brown';
select * from QiSQL_Parents where upper(last_name) = 'BROWN';

The above select statements will all work. You will usually definitely need to apply an Upper (or Lower) function to database values as you will not know the Case the data will be stored in. You do usually know the case of literal comparisons like 'brown' - because you typed them!

Ands and Ors (and Brackets)

if we want to apply multiple selection criteria to our SQL Select statements we add ANDs and ORs:

select * from QiSQL_Parents where upper(last_name) = upper('brown')
AND id = 3
OR first_name like 'John'

When you are using multiple ANDs and ORs - be careful. In the above SQL Select command the OR at the end could confuse you! You need to use brackets to group your query into how you really mean it. The following queries will return very different sets of data - give them a try and work out what they are doing:

select * from QiSQL_Parents where upper(last_name) = upper('brown')
AND id = 3 OR first_name like 'John';

select * from QiSQL_Parents where upper (last_name) = upper('brown')
AND ( id = 3 OR first_name like 'John' );

select * from QiSQL_Parents where ( upper (last_name) = upper('brown')
OR id = 2 ) OR first_name like 'John';

select * from QiSQL_Parents where ( upper (last_name) = upper('brown')
OR lower (last_name) = 'henderson'
OR last_name = 'Smith'
OR last_name like 'J%' )
AND 1 = 2 ;

The next lesson in the series will add some more exciting functions for you to learn.