Learning SQL from Scratch
The SQL language (Structured Query Language) is the dominant language used to access data stored in databases. This was one of the original languages applied to Edgar Codd’s relational model and overtime became the defacto standard. SQL standardization started when it was accepted by the “American National Standards Institute” in 1986 and then again in 1987 by the ISO “International Organization for Standardization”.
The SQL Language has been around long enough to become very mature and this is a good thing as the skills you use from one database to another are transferrable to a high degree. Every rdbms has slight differences and functions that are not covered under the standard but almost everything else will be familiar.
To start this tutorial we will begin at the beginning — with the “select” statement, this is the most used statement and one of the most versatile. All relational databases arrange there information in tables. These tables are a collection of rows and columns much like a grid. Each column carries a named piece of data.
For example let’s say we need a table that carries the names of members if a website. In the table we would like to store their name, username and password.
The construction of the table would be like this:
First_name Last_name Username Password
These columns would be grouped in rows and in turn that would be the general make-up of the table. The next thing that is important is how do we identity the correct rows in this or any table. Normally there would be an identity column that uniquely identifies a row. This can be done with one column a or a group of columns as long as it is unique.
So now what we have is:
MemberId First_name Last_name Username Password
An identity key in the form of an integer will be added to this column for every row that is inserted into the table.
With this we can now identity the row we seek. To select a row in a database there are a number of options, but for now the simplest is to select all columns where the “rowid” in this case MemberId is the one you seek.
select * from Members where MemberId=1024;
1024 being the value that was assigned to that row when it was created.
This will return the member data that we need from the database. You can also ask for specific columns if you only need specific information. That is appropriate when all the columns are not neccessary and would cause unneccessary work for the system.
Select First_name from Members where MemberId=1024;
This will of course just return the first name for the member.
The next concept I would like to introduce is “joins”. Now let’s say that we have another table that carries address information for the member. How will we also grab that information at the same time?
Below is the example layout for the Address table.
AddId Street_address City State Zip
So in order to obtain the additional information that we now need we will have to “join” the data from the first table with that of the second table.
This will be what is called an “inner join” or “equi-join”. We will have to add one other column to the second table that is common to both tables.
AddId MemberId Street_address City State Zip
By adding the MemberId column to the second table, we create a way to “join” the two tables, this value will be the same as the MemberId of the first table.
Select * from Members inner join Addresses on Members.MemberId=Addresses.MemberId;
Now in truth this will turn us back a table of “all” Members and their addresses. We need to add one more thing: The “where” clause from the initial example.
Select * from Members inner join Addresses on
This will return 1 row consisting of all the columns of the first and second table allowing us to have the infromation that we need.
Look for part 2 of “Learning SQL From Scratch”.