Databases and SQL.

The lecture notes equipped me with initial background knowledge about databases. A database contains a set of information which is organised in a structured way, making it accessible and manageable (Tech Target Inc, 2006).

There are different approaches to how to organise information in databases. The most efficient seems to be a relational database.  A relational database is a tabular database where information is organised in tables. Each table represents information about one item. Tables are connected together by keys. The primary key is a unique attribute assigned to a specific row in a table. The foreign key in a table represents a link to another table where this particular key is the primary key. The use of keys provides flexibility in updating and managing information in specific tables.   

SQL is a command language which enables databases to be created and updated as well as information to be retrieved from them.

As it was recommended in the “Resources” section, I started practical exercise by learning the basics of SQL  at  www.sqlcourse.com . The course is very well designed for those like me who are encountering databases for the first time.  I practised creating tables, inserting data, updating tables and producing queries.

Then it was time for serious stuff!

The first seven exercises were not easy (I made a lot of mistakes in syntax), but I understood pretty fast how SQL works.  

Then the fun started. From exercise number 8 onwards, I spent at least one hour on each query!

Finally,

I came up with productive queries.

8) The query       

select company_name,year_published,title from publishers,titles where year_published>1990 and title like '%programming%' and publishers.pubid=titles.pubid;

yielded 954 rows from the database.

9) The following query 

 select name, ISBN from publishers,titles where isbn='0-0280074-8-4' and publishers.pubid=titles.pubid;

came back with  ‘Glencoe’ as the publisher’s name.

10) After sweating for another hour, I came up with a query which produced 108 rows! All I needed was the author’s name, one and only one, not 108 rows of information. Back to work. My joins were definitely wrong.

I spent another hour trying to understand how it works and I got it. Me! Conclusion: everything is possible - even the impossible.

My last and most precious query

mysql> select title,title_author.isbn,authors.au_id,author from titles, title_author, authors where title="A beginner's Guide to Basic" and titles.isbn=title_author.isbn and title_author.au_id=authors.au_id;

returned a beautiful table:

+-----------------------------+---------------+-------+--------------------+

| title                       | isbn          | au_id | author             |

+-----------------------------+---------------+-------+--------------------+

| A Beginner's Guide to Basic | 0-0307445-1-2 |  3648 | Martin, Sherry J.  |

| A Beginner's Guide to Basic | 0-0307445-1-2 |  5027 | Parker, Charles S. |

+-----------------------------+---------------+-------+--------------------+

2 rows in set (0.02 sec)

There were actually two names instead of one.

This was hard! But I loved it.

 I had such a fabulous feeling of achievement.

References:

Tech Target Inc. (2006). Available at:  http://www.whatis.com/ (accessed: 10^th October 2011).