Learning SQL - Views

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.

A Different View

Views allow us to build complex select statements, often joining many tables and use the view a bit like a table. In the Triggers Lesson we created a table and a trigger, updated some data then ran this select statement:

select
table_name' Table Name' ,
record_id' Record Id' ,
change_date' Change Date'
from qisql_changes_history;

The results were a bit like this:

Table Name Record Id Change Date
QiSQL_Children 2 2008-12-20 12:17:09
QiSQL_Children 1 2008-12-20 12:22:14
QiSQL_Children 2 2008-12-20 12:22:14
QiSQL_Children 3 2008-12-20 12:22:14
QiSQL_Children 4 2008-12-20 12:22:14

Join 'em up

Now lets get a bit more detail into that select statement. I would like to know the name of the child and the child's parent as well as the date and time the record was updated. To do this I will need to join these three tables: Qi_SQL_Children , QiSQL_Parents and QiSQL_Changes_History. As we discussed earlier using table aliases makes handling join statements easier.

select
T.table_name 'Table_Name' ,
T.record_id 'Id' ,
T.change_date 'Change_Date' ,
P.first_name 'Parent_First_Name' ,
P.last_name 'Parent_Last_Name' ,
C.first_name 'Child_First_Name' ,
C.last_name 'Child_Last_Name',
T.change_date 'Change_Date'
from qisql_changes_history T,
QiSQL_Parents P,
QiSQL_Children C
where T.record_id = C.id
and C.parent_id = P.id ;

That gives us the following:

Table_Name Id Change_Date Parent_
First_Name
Parent_
Last_Name
Child_
First_Name

Child_
Last_Name

QiSQL_Children 2 2008-12-20 12:17:09 Susan Brown Lucile Brown
QiSQL_Children 1 2008-12-20 12:22:14 Susan Brown Peggy Brown
QiSQL_Children 2 2008-12-20 12:22:14 Susan Brown Lucille Brown
QiSQL_Children 3 2008-12-20 12:22:14 Susan Brown Harry Brown
QiSQL_Children 4 2008-12-20 12:22:14 Susan Brown Quentin Brown

Create The View

It would be a pain having to type in all this every time I wanted to see the Updates table, so we can create a view using the exact same SELECT statement we used above (I like to identify views by adding a V_ somewhere in the name - it makes it easier when your database grows to hundreds of tables and views):

create view QiSQL_V_child_changes as
select

T.table_name 'Table_Name' ,
T.record_id 'Id' ,
T.change_date 'Change_Date' ,
P.first_name 'Parent_First_Name' ,
P.last_name 'Parent_Last_Name' ,
C.first_name 'Child_First_Name' ,
C.last_name 'Child_Last_Name'
from qisql_changes_history T,
QiSQL_Parents P,
QiSQL_Children C
where T.record_id = C.id
and C.parent_id = P.id;

Now we can get the same data returned as the SELECT statement above by issuing this SQL command

select * from QiSQL_V_child_changes;

Cool!