Beginning SQL

It’s great to have information organized in a database, but what do you do with it once it’s in there? The answer is that you have to get it out again before you can do anything with it. And that’s where Structured Query Language (SQL) comes in. Keep reading to learn more.

History of SQL

It was the early 1970s, a time of high tech revolution that brought us the marvels of the Gremlin and Ford Pinto. Richard Nixon was relaxing at the luxurious Water Gate Hotel, and the do-gooder hippies finally had a day they could call their own (we gonna party like it’s your Earth Day!).

Meanwhile a young Donald D. Chamberlin and Raymond F. Boyce sat staring at IBM’s ominously named System R. They had just finished filling the relational database with data and after drinking a whole bunch of champagne, realized that now that they had the data in the database…how could they get it out?

The answer: SEQUEL. No, there was no PREQUEL (I told you, they just had a bunch of champagne). An acronym for Structured English Query Language, SEQUEL was designed to be the first relational database language. Its mission: manipulate information stored in databases. Unfortunately the name was copyrighted by an airline in the United Kingdom, so the name was eventually changed to SQL, or Structured Query Language.

There are many extensions of SQL, including SQL-PL, MYSQL, PLSQL, and PL/pgSQL (for users under the age of thirteen not accompanied by an adult — sorry, just kidding). For practical purposes, and because I have a word limit, we will only attempt to learn the basics of SQL itself.

One last thing. I know you are thinking: How do I pronounce SQL? Well, I can tell you it is not the way I originally thought it was pronounced, which was squeal (you know, the sound an angered pig might make). There is a heated debate between those that wear tape on their glasses and those with pocket protectors about the proper pronunciation, and at the moment there is a gridlock between the original way of saying it, aka SEQUEL, and the hip-cool-nineties way of saying it- es queue el. I know, seeing it spelled like that makes it seem like something sexy you might say in Spanish.

But trust me, you never want to talk about it on a hot date. Unless you date programmers or database administrators, of course, and even then I’d use caution.


The zenith of 1970’s technology

Before We Begin

Before we start, I will make a few assumptions. I’ll assume you are familiar with relational databases and have a bizarre yet totally understandable desire to manipulate the data within them. I’ll assume that you know how to use a computer (or it’s one heck of a coincidence that you are reading this article) and that you have a database program, such as Microsoft Access.

Okay, let’s begin.

{mospagebreak title=Retrieving Data from a Database}

For starters, build a database with a table called Employees. Give the table four columns: FirstName, LastName, Position, Salary. Now enter the following data into the table.

First Name

Last Name

Position

Salary

Larry

Smith

President

$100,000

Homer

Sampson

CEO

$75,000

Your

Name

Whipping Boy

$15,000

In the above table you can see that we have three text fields and a currency field (and also, that you make way less than everyone else; but that’s okay…once you learn SQL, you’ll be sleeping on a quilt made of dollar bills).

Say you want to see a list of the last names of every employee within the company. For that you would need the most useful Statement in SQL, the Select statement.

Select LastName from Employees;

This will return the following result:

Last Name

Smith

Sampson

Your Last Name

The statement Select literally tells the database to choose data. Typing LastName tells the database which column the data is in, and from Employees tells it which table the data is in. The semi-colon (;) at the end tells the database to separate your statement from other statements.

Note that unlike many languages, SQL statements are not case-sensitive.

To select more than one column from the table, you would use the following code:

Select FirstName, LastName from Employees;

This returns the results:

First Name

Last Name

Larry

Smith

Homer

Sampson

Your

Name

As you can see, we now have a list of all of the employees first names and corresponding last names.

Now say that you want to select all fields within the Employee table. To do this, you simply use an * symbol.

Select * from Employees;

Your result would be a table showing all of the data in your Employee database.

There may come a time when you wish to select a specific piece of data from your database. Like maybe you want the phone number of that cute girl in the mail room. You know the one I’m talking about. With the WHERE CLAUSE you could get it (you could…but you probably shouldn’t). Likewise, when the person in charge of payroll needs to know which address to send your last paycheck to (since you got fired for harassing the cute girl in the mail room), the WHERE CLAUSE is what they would use.

For now let’s say that you wanted to see all employees that are CEOs within the company.

Select * from Employees

Where Position=’CEO';

This will return the following table:

First Name

Last Name

Position

Salary

Homer

Sampson

CEO

$75,000

Note that when you are searching for text, you use a single quotation (‘). However when you search for a numerical value, you would not use a quotation.

There are a number of operators you can use alongside a WHERE Clause. I bet they would look pretty in a table. Let’s see.

Operator

Function

=

Equals

>

Greater than

<

Less than

<>

Not equal

<=

Less than or equal to

>=

Greater than or equal to

Like

For similar data

In

The exact value you wish to return

Between

For data with a certain range

The Between Operator works well with numbers. For instance, you would use it if you wanted to see a list of employees that made between $75,000 and $100,000. The Like Operator will show you data that is similar. If you had a list of names and one was Sampson and the other was Simpson (DOH!), then it would return both results, as the two are similar.

Select * from Employees

where LastName Like ‘%son%';

Technically, the above code would show us any employee who last name contained “son”

{mospagebreak title=The Distinct Statement}

Let’s say now that Homer Sampson has a twin brother. And let’s further say that Homer Sampson’s parents wanted to add to the confusion, and, much like the great boxer George Foreman, decided to give all their children the same exact name (perhaps they too, had been hit in the head one too many times). Go ahead, add him to your table, with the position of CFO and the same salary as his brother.

If you were to print a last of first and last names, and their respective positions, it would look like this:

Select FirstName, LastName, Position from Employees;

First Name

Last Name

Position

Larry

Smith

President

Homer

Sampson

CEO

Homer

Sampson

CFO

Your

Name

Whipping Boy

As you can see, there are two Homer Sampsons, with different positions within the company. Now say that you wanted to print just a list of first names, without having duplicates. For this you would need the Distinct statement.

Select DISTINCT FirstName from Employees;

This will give us a result that shows only first names that are different from one another.

First Name

Larry

Homer

You

As you can see, there is now only one Homer listed.

{mospagebreak title=Placing Data into a Database with SQL}

All you do is take, take, take. It’s not right. The database has emotions too. It has needs. Face it, if your tawdry love affair with the employee database is ever gonna blossom into a real, meaningful relationship (and what man doesn’t want that?), then you are going to have to learn how to give a little. Unless you are Chuck Norris. Chuck Norris doesn’t give — he takes.

To insert a new row into a table, you’ll need the ever helpful INSERT INTO statement.

INSERT INTO EMPLOYEE

VALUES (‘BRUCE’, ‘LEE’, ‘SECURITY’, $50,000);

This would result in the following row being added to the table:

First Name

Last Name

Position

Salary

Bruce

Lee

Security

$50,000

Note: now that Bruce Lee is added to your Employee list, you do not want to make him mad.

If you ever want to insert a new row of data, but for some reason do not have enough information to fill in every column, the INSERT INTO statement can help with that as well.

INSERT INTO EMPLOYEE (FirstName, LastName)

VALUES (‘Bruce’, ‘Lee’);

The above code would insert the first and last name into the table and leave the remaining columns blank, to be filled in when you obtain the remaining information.

The Update Statement

As discussed above, we never put in the rest of Bruce Lee’s information, which means good ole’ Bruce isn’t getting paid this week. And he is mad. Fighting mad, which is really the worst kind of mad for Bruce Lee to be. But never fear, use your fingers of fury to pound out the following code.

UPDATE EMPLOYEE SET SALARY=$50,000

WHERE FirstName=’Bruce”;

That’s right. I just saved your life. Because now the row will contain Bruce’s salary.

But now you’ve forgotten to add the Position, so Bruce is just walking around being a slacker. If ninjas attack he won’t know what to do. Should he fight them off or do their taxes? Never fear. You can update multiple columns with the UPDATE statement.

UPDATE EMPLOYEE

SET SALARY=$50,000, POSITION=’Security’

WHERE FirstName=’Bruce';

Lastly, the UPDATE command also allows us to insert a whole new column.

UPDATE EMPLOYEE

SET column_name=Whatevernameyouwant

WHERE column_name=whatevervalue

The DELETE Statement

Eventually someone is going to get fired. Or murdered by Bruce Lee (let’s face it, the guy is a loose canon). Then it will be time to pull out the old DELETE statement.

DELETE from EMPLOYEE WHERE FirstName=’Homer';

This will delete the row containing Homer Sampsons information.

Now in the event that Homer was Bruce Lee’s victim, we will have to delete the entire database, due to the impending lawsuits. Here is how we do that:

DELETE * FROM EMPLOYEE;

You can also Delete a column from your table.

DELETE FROM EMPLOYEE

WHERE column_name=whatever_column_you_wish_to_delete;

And that’s it for the basics of SQL. Get a little rest and work on that kung-fu grip. The second tutorial in the Beginning SQL series will be coming soon.

[gp-comments width="770" linklove="off" ]

chat