SQL for beginners
skill level: easy

:: Welcome

Welcome to my tutorial: SQL for beginners. SQL (speak: 'Es Que Ell') stands for Structured Query Language.

In this tutorial we are going to create a database which stores your release information. If you want to see how it's implemented into a PHP driven page, see Sweeper's PHP tutorial. If you don't know what PHP is or how to code it, check my PHP for beginners tutorial found in this issue of Hugi.

:: CREATE a Database

To retrieve your release information, we must have something where all this information is stored. Therefore we choose to make a database. Into this database we place information such as: Name(Title), Creator, Type, Release Date, Downloadable from, etc.

As we create such a database we get some sort of table which looks something like this. (This may differ from your chosen items, but the idea is the same.)

| Name		| Creator	| Type	| Release Date	| URL			|
|		|		|	|		|			|
|		|		|	|		|			|

To create this table SQL has a command CREATE TABLE. Syntax:

              CREATE TABLE <tablename> (
		<columnname>	<columntype> [(<length>)]	[NOT NULL],
		<columnname>	<columntype> [(<length>)]	[NOT NULL]

Each column (in the table) is separated by a comma.
For the tablename you may choose a name which you like, for example: 'releases'.
For the columnname you also may choose a name, for example: 'name' or 'style'.

For the columntype you can choose one of these:

CHAR characters
NUMBER numbers / non-characters
DATE or DATETIME date or date & time
LONG long characters

The length is optional, it's only required with CHAR.

NOT NULL states that that particular value has no value.

By this time, we have determined what our database is going to look like.

| Name		| Type		| Length	| Additions			|
| Name		| CHAR		| 30		| NOT NULL			|
| Creator	| CHAR		| 30		| NOT NULL			|
| Type		| CHAR		| 10		| 				|
| Release_Date	| DATE		|		|				|
| URL		| CHAR		| 50		|				|

In this database there is no Primary Key (Value that may exist only 1 time!) defined. If we would create a member list, a primary key must exist, because there could be 2 or more members with the same name, and which one is the correct?? See the example below to see what I mean: (this example doesn't fit in our tutorial actually)

| ID(*)	| Last Name	| Name		| Phone		| Address		|
| 00001	| King		| Jack		| 01-23456789	| Houston Av. 34	|
| 00002	| King		| Tom		| 01-98765432	| Middle E. street 99	|
| 00003	| Wood		| Gary		| 09-87654321	| Woodstock 354		|
| 00004	| Wood		| Gary		| 05-12346789	| Houston Av. 86	|

( * = Primary Key)

See that there are Two Kings? Could be a problem. In this case NOT, because there is only 1 Jack King and 1 Tom King. But.. we also have 2 Woods! And this time 2 Gary Woods! Which one is the correct if you want to raise his salary?? Use his ID instead! And if you want to raise Gary's salary, raise it to 00003 instead of to Gary Wood because two Garys are happy -> you're not.

To define a primary key, please in the CREATE TABLE command:

              Primary Key ( [columname] )

So in OUR (not the salary example) database this would be

              Primary Key (Name)

Now would be a good time to actually create our database.

              CREATE TABLE releases (
		Name		CHAR(30)	NOT NULL,
		Creator 	CHAR(30)	NOT NULL,
		Type		CHAR(10),
		Release_Date	DATE,
		URL		CHAR(50),
		Primary Key(Name)

:: INSERT into a database

By this time we have created our database, but the database has no information to show! To insert data into your database use the INSERT command. Syntax:

              INSERT INTO <tablename> [(columnname, ...)] VALUES (value, ...);

In our database we add the release "Storm" created by "Cenix", it is an "XM". The release Date is set at "2001-01-14" and the URL (for download is) "http://download_storm". Thus we get:

              INSERT INTO releases (Name, Creator, Type, Release_Date, URL)
                     VALUES ('Storm','Cenix','XM','2001-01-14','http://download_storm');

Now the database looks like this:

| Name		| Creator	| Type	| Release Date	| URL			|
| Storm		| Cenix		| XM	| 2001-01-14	| http://download_storm	|
|		|		|	|		|			|

:: SELECT from a database

Now we have the information entered into the database. But we would see what's inside the database, wouldn't we? Herefore the SELECT command is the right one to use. Syntax:

              SELECT <data> FROM <tablename> [WHERE <columnname> = '<value>']

To select all data from the database use

              SELECT * FROM releases

Or to select all data from the database which contains the name "Cenix" use

              SELECT * FROM releases WHERE Creator = 'Cenix'

We also can select just a few columns from the database, therefore use

              SELECT Name,Creator,Type FROM releases

:: WHERE additives

As we saw before we could use the WHERE command with the SELECT command. We have seen that we can select values which equals (=) to a specified value. But we can do more! We can also use the < and/or > to have other values. Some examples are

              SELECT * FROM releases WHERE Release_Date > 2000-01-01

              SELECT * FROM releases WHERE Release_Data < 2001-01-14

Please be aware that when you use < or > at CHAR columns you might get different values than what you expected because ASCII characters are CASE SENSITIVE, this means that character 'a' has a lower value than 'A'.

The operators are:

> greater than
< smaller than
!= value is NOT
>= greater than or equals
<= smaller than or equals
<> equals NOT (is not the same as)

And it still doesn't end here. We can use AND and OR to create multiple conditions. Example:

              SELECT * FROM releases WHERE Creator = 'Cenix' AND Type = 'XM'

:: UPDATE a database

With this command we can mutate a certain value inside a database. Syntax:

              UPDATE <tablename> SET <columname> = '<value>'
              WHERE <columnname> = '<value>'

If we, for example, have written "Strom" instead of "Storm" you can mutate the information. Then we could write

              UPDATE releases SET Name = 'Storm' WHERE Name = 'Strom'

:: Automation

You see that SQL is not that difficult. You can use this to automate your site. Just fill in a form and submit it to the SQL database. If you want to achieve this, please read the article written by Sweeper (found in this issue of Hugi).

:: END

And here I like to end this tutorial about SQL for beginners. I hope you have learned a lot of this tutorial.

For questions about (beginning) SQL, unclear issues, and more:


e-mail: b_clephas@hotmail.com