Brought To You By
Tomlin Ventures Ltd

An Introduction To SQL

Structured Query Language the basics

Structured Query language or SQL for short is a language used to alter or interrogate database systems. It is a language in its own right just as PHP or HTML. It is a common misconception that SQL is some kind of database system in itself, this is not the case.

Many database systems are available that follow the SQL language, the most common being MySQL, SQL Server, Access, Sybase and Oracle On the whole they all function in roughly the same way.

SQL then is purely the language used to communicate with and interrogate these various database systems.

Why Use A Database Anyway

There are many reason why a web master may decide to use a database to store data rather than using alternate means. From the obvious uses like storing user names and passwords to the less obvious like forums and comment boards. In fact there are very few large web sites out there these days that do not utilize a database for some part of their functionality.

How To Use A Database

To actually integrate a database dynamically into a web page you will need some sort of server sided script to extract the data and then organize it in a away that would be usable on the web page. Two such languages are PHP (Hypertext Pre-Processor) and ASP (Active Server Pages), both have good support for database and are relatively easy to use.

You would use your sever side language to make requests to the database, the script would then process the returned data into a presentable form and send it to the browser. This is the basic principle of creating a database drive web site, we will however look at this in more detail in the server side languages section.

So Whats In A Database

For those of you that are not familiar with the concept of a database or have never knowingly used one, don't worry we will be starting at the very beginning and will guide you through every process.

Database Structure

The first important thing is the structure of a database a database with poor structure is very hard to use. A database is made up of one or more tables, usually many more. If you think of a database as a filing cabinet the tables would be the draws of the cabinet.

The tables group related information together for example a payroll database would have a table containing each employee and there employee number, address and other personal information. Another table within the database would contain the various pay check data for each employee for each month they are employed.

Within each table there will be columns each of which will contain the relevant data. The above example would be made up form two tables which would look something like this.

Payroll database

Employees table
employee_id name address
001 Tom Thumb 7 Pipe House
002 Jack Horner 1 The Corner
003 Bo Peep 14 Sheep Road
Payroll table
payroll_id employee_id amount date
1 001 500 1/08/2010
2 002 500 1/08/2010
3 003 1000 1/08/2010
3 002 750 2/09/2010
4 003 500 2/09/2010
5 002 1000 1/10/2010
6 003 1000 1/10/2010
7 003 1500 3/11/2010

Using SQL To Query A Database

There are many many ways to extract data from a database using various SQL statements which we will cover over the next few pages. It is very likely that from the above tables for example you would wish to pull data from both table simultaneously. To do this there must be one or more common reference point between the two table.

Reference Points Between Tables

As you can see within both tables there is a column named employee_id this would be a number unique to each employee. Without getting into too much detail at this point, it is by referencing each entry within the payroll table via the employee_id to the relevant employee that a structure is built between the two table.

This is where a poor table structure will let you down as without these columns it would be virtually impossible to extract data simultaneously form both tables.

Its is by using simple id columns like this that you will be able to efficiently pull relevant information back from the database using more advance table joins.

If this is your first time you have looked into databases don't worry if this is not totally clear yet we will be using more worked examples over the coming pages which will help understand this concept.

Now hit the next tutorial button below and we look at some simple SQL queries to extract the data from the tables.

Share to Facebook Share to Twitter Digg This Stumble It Share to Reddit Share to Delicious Technorati LinkedIn Google Buzz Blogger More...

blog comments powered by Disqus