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Ā  as 'Friendly Name' , last_name 'Family Name'
from QiSQL_Parents
where last_name = 'Brown' ;

You will see that in this SQL statement one 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 NameFamily Name
LindaBrown
SusanBrown


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 haven't 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.

Back to Top