SQL Joins, or joining of database tables is one the most difficult for noobs to understand, but this is one of the more powerful queries out there. Yeah, its nice and comfy when you have just one table and everything is right there. But there will be times, many actually, when your databases will hold various tables that you have to bring together in order to retrieve query results.

Let’s take an example of a two table database where one table is for colleges and the other is for states.

SELECT DISTINCT
c.id,c.name
FROM colleges as c
INNER JOIN state AS s
ON s.college_id = c.id
WHERE id = ” id ” ;

Okay, obviously I need to explain a few things first. For Example, what is c.name ? Up until now we had been calling columns by their name. But what happens when you get to Joins is that sometimes the table and column name start getting long and before you know it the query starts reading longer Tolstoy’s war and peace.

So, taking it peace by peace:

SELECT DISTINCT (this helps to reduce any duplicates that could be in your DB)
c.id,c.name
FROM colleges as c (here we’re choosing the column’s id and name from the colleges table and giving them the alias of c (for colleges)
INNER JOIN (this is what is going to join the two tables)
state AS s (assigning the table to s)
ON (here’s the comparison, or with)
s.college_id = c.id (this says where the table holding ‘state’ and college_id should equal the table ‘college’ id
WHERE id=’id’ (this is the final refinement that gets all matches of id of this state has id of these colleges)

We discussed the SQL query for inserting data into a database, now let’s look at all the options we have of changing, deleting, and updating that data.

Okay, so let’s say we have a field where we would like to change or Update the contents of the entire column in every row. We would need to run a query like this: (once again we’ll be using the names table from our actors database)

UPDATE names SET name= “Jodie Foster”;

Now, that would essentially change all the fields in the ‘name’ column to Jodie Foster. It may make sense to change all in a lot of situations, but not in a names table, so what if we just wanted to change one name in the table. Say for example that an employee gets married and all instances of her last name need to change , we would need to use the conditional WHERE.

So let’s say Jodie Foster gets married (yeah right , wink wink):) and changes here last name, how would we change just her last name and leave everyone else’s alone. Here’s an example of the WHERE clause which is what we’ll use.

UPDATE names SET name =”Jodie Smith”
WHERE id =”1″;

See what we did there? We told the database, where id is =1 change the value of the ‘name’ column to ‘Jodie Smith’
This would be ideal in a work situation where more than likely an employee id is the primary key used.

Okay, now let’s see an example of DELETE

Rarely will you want to delete everything in a database (unless your a malicious hacker), usually you’ll want the more targeted form of ‘sql delete from database or table” so let’s focus on more useful commands involving the DELETE query.

Once again we can rely on the WHERE conditional to help us find exactly what we want to delete.

Let’s continue with the Jodie Smith example, say we wanted to delete just her. We would run a query like this:

DELETE FROM names WHERE name =”Jodie Smith”;

This would work in this instance, but how about in a giant corp where there are thousands of employees and very many people with the same name? It’s always best to use a unique identifier such as a Primary key:

DELETE FROM names WHERE id =”1″;

Remember before deleting anything, to have a backup.

Inserting data into a database is essential especially if we have a dynamic site. Data can be inserted into an existing table using INSERT INTO query.

INSERT INTO tableName VALUES (value,value);

Let’s see an example of INSERT INTO in action, remember the table we created, let’s continue where we left off.

In our SQL form, we are going to type in:

INSERT INTO names VALUES
(
‘NULL’,'Tom Cruise’
)

now remember we had two fields, id and name. What this query does is insert “Tom Cruise” into the name field , but notice the NULL, that is so it ignores the first field which is our primary key and is on auto_incremement. To get a glimpse of what our database is starting to look like, let’s see its layout.

In your SQL interface, let’s type in:

SELECT * FROM names;

You should see a 1 under id and Tom Cruise under the name column

Now obviously for any database of substantial size, we are going to need some quicker way of inserting information, or how about automated? Like when someone enters information on a form, all that goes into a database.

Now that you know what a SQL table looks like, and you have the tools to start running SQL commands, let’s go ahead and create one. If you have installed Xampp, go ahead and go to http://localhost in your browser, click on phpmyadmin.

Next need to create a database

In the text box under the create new database, enter actors as the name of the database and click ‘create’

Now go to the tab on top where it says SQL and click that. From now on we will always go to this tab to run our commands. phpmyadmin does have a gui interface to create, edit, and delete databases, but our goal is to learn SQL so we will go the way of the code.

Okay, so with the SQL tab open we are going to run our first query:

Type in:

CREATE TABLE names
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR( 100 )
);

(notice the commands are in all capitals, it is not required but it makes a clear distinction between SQL syntax and column names)

Let’s look at that for a moment,

• id is going to be the unique identifier for each value
• NOT NULL means it won’t show a null value
• AUTO_INCREMENT means the id number will automatically increase by each row added
• PRIMARY KEY is the unique identifier for each value- no two rows can have the same primary key

For the name column we used VARCHAR or variable character field meaning we could basically put anything in there and is the most widely used field. the 100 is the maximum number of characters allowed so if someone’s name is longer than that you would probably get an error…but I don’t know anyone who’s name is longer than 100 characters, do you?

Databases are similar to storage containers in that they hold information in a logical and convenient format. Your job as a database guru is to create and manage databases.

The Inner Workings of a Database

A database can have one table or many tables. Imagine if you are looking at an excel sheet, there are columns and rows in every book. The same principle applies to a database table. Just like you can have many books in an excel sheet, you can have many tables in a database.

Here’s an example table with 3 columns and 1 row, the top row are the names of the column:

I hope to make learning SQL very simple and easy with these tutorials. I have written tutorials on other subjects, but never on SQL, I am however experienced with it. I have created numerous database driven websites and have managed large scale websites that use databases as a foundation, such as forums.

With these tutorials I am going to go step-by-step. I am going to show you what information the database holds, and when I
show a command I will show you what will be returned.

The tutorials here refer to MySQL database software, which can be obtained for different platforms at no cost from http://www.mysql.com.

One thing to keep in mind is that there is very little difference in syntax between using Oracle, Mysql, or MSsql. They all use sql as a basis, but run on different engines so to speak.