Learning SQL - Basic Select Statements

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.

How do I see my data?

Easy! You just issue a SELECT statement:

Select All the columns

select * from QiSQL_Parents ;

The * symbol tells SQL to return all of the columns from the table

Select specific columns

If you want to be a bit more specific you can tell SQL which columns you want to see:

select first_name , last_name
from QiSQL_Parents ;

A comma between the column names is used as a separator.

Select restricted columns

How about restricting the rows of data returned?

select first_name , last_name
from QiSQL_Parents
where last_name = 'Brown' ;

We have added a WHERE clause to restrict the data returned from the database.

Alias poor Yorrick!

When we select data we can give the columns aliases, so for instance:

select first_name 'Friendly Name' , last_name 'Family Name'
from QiSQL_Parents
where last_name = 'Brown' ;

These column aliases don't show in some applications but this is how you will see a report in QiSQL and other nice SQL applications:

Friendly Name Family Name
Linda Brown
Susan Brown

You will see that in this SQL statement on of the aliases has used the keyword AS, the other hasn't. The AS keyword is optional, I don't tend to use AS, but it may help with the readability of your SQL - it's up to you. These column aliases don't show in some applications but this is how you will see a report in QiSQL and other nice SQL applications:

Friendly Name Family Name
Linda Brown
Susan Brown

Just imagine a query like this:

select qisql_children.first_name || ' ' || qisql_children.last_name || '/' || qisql_parents.first_name ||' ' ||qisql_parents.last_name
from qisql_children , qisql_parents
where qisql_children.parent_id = qisql_parents.id

Don't panic! we havent covered joins and concatenation yet - this will all become clear in a while. What we have done here is join two tables together, each time we refer to one of the tables in the query we needed to specify which table we mean ( both tables have first name and last name fields ). The resulting report will have a column header of - qisql_children.first_name || ' ' || qisql_children.last_name || ',' || qisql_parents.first_name ||' ' ||qisql_parents.last_name - that's really dumb, we want the column header to be 'Child, Parent' - so we use aliases.

To make the SQL even easier to read we can also use Table aliases:

select C.first_name || ' ' || C.last_name || '/' || P.first_name ||' ' ||P.last_name AS 'Child/Parent'
from qisql_children as C , qisql_parents as P
where C.parent_id = P.id

I've used the AS keywords here just to make it clear which bits are the aliases, but this code is equally valid:

select C.first_name || ' ' || C.last_name || '/' || P.first_name ||' ' ||P.last_name 'Child/Parent'
from qisql_children C , qisql_parents P
where C.parent_id = P.id

Negative restrictions

You can add negative where clauses as well:

select first_name , last_name
from QiSQL_Parents
where last_name = 'Brown'
and first_name <> 'Linda' ;

Add some order to the chaos!

Let's sort our returned rows of data:

select * from QiSQL_Parents
order by last_name;

The default sorting order is ascending but we can tell SQL to sort in a descending order by adding the keyword DESC ( or you can specially add ASC to ensure the sort is ascending)

select * from QiSQL_Parents
order by last_name desc;

Select - Putting it all together

select id 'Unique Identifier' , first_name 'Friendly Name' ,
last_name 'Family Name'
from QiSQL_Parents
where last_name <> 'Jones'
and first_name <> 'John'
order by last_name desc , first_name asc;

In the query above we have mixed descending and ascending ORDER BY clauses.