SQL for morons

Author: Steve
WebSite: http://www.websitegravy.com

Structured Query Language, or SQL (also pronounced "sequel") is a human readable language used to interact with databases. This "for morons" guide is a quick and dirty introduction to SQL - it is not meant to dive deep into database theory or programming logic. It is built to quickly provide you with enough skill to interact intelligently with any SQL standard database.

What you should learn in this tutorial

1. What SQL is, including its capabilities

2. How to select, insert, update and delete data

3. Restricting data using WHERE, LIKE, BETWEEN and AND/OR

4. How to create databases and tables

What is SQL, and what are its capabilities

SQL has been standardized by an organization known as ANSI, or American National Standards Institute. SQL is quite powerful - it is used to create databases and database objects like tables or indexes. SQL is also used to select data, insert data, modify data and delete data.

SQL uses keywords as its basis to instruct the database. Keywords include SELECT, INSERT, UPDATE and DELETE. Other keywords include LIKE, WHERE, CREATE and TRUNCATE. Each of these keywords will be discussed and illustrated throughout this tutorial.

Let's use the following table and data for this tutorial:

FirstName LastName PhoneNo Salary John Adams 343-4422 56000 Henry Harrison 345-4524 52000 John Smith 597-8904 49000 Sam Spade 234-4797 64000 George Washington 234-7859 78000

In this tutorial, I write each SQL keyword and clause in capital letters, making it easier to read and recognize. Be aware that SQL is not case sensitive, so this is not a requirement when interacting with every database.

Selecting data

Selecting data using SQL is very easy. For a simple statement (or query), we use the SELECT and FROM keywords. For a more advanced statement, we throw in WHERE, which restricts the rows that return. Let's look at each below.

SELECT * 
FROM emp

The * in the above statements means everything. So, the above statement selects every column from the emp table. The results will include every column and every row.

This is all well and good, but what if we only want the name and phone number returned in this table? What can we do to restrict the number of columns? In SQL, this is easy.

SELECT FirstName, LastName, PhoneNo 
FROM emp

To select only specific columns, we specify the names of those columns within the statement. While this statement still returns all rows, SQL will only return those specified columns from the table. Our results will look like this (note the Salary column is missing):

FirstName LastName PhoneNo John Adams 343-4422 Henry Harrison 345-4524 John Smith 597-8904 Sam Spade 234-4797 George Washington 234-7859

Restricting data using WHERE

Great. Now, if you look at the results, there are two employees with the first name John. Let's create a SQL statement that will only select those employees with the first name of John, and again we'll only use the first name, last name and phone number. Here, we throw in an extra keyword, WHERE.

SELECT FirstName, LastName, PhoneNo 
FROM emp 
WHERE FirstName = 'John'

Easy, huh? The results look like the following, using the previous results as our base table.

FirstName LastName PhoneNo John Adams 343-4422 John Smith 597-8904

Keep in mind that I used single quotation marks around the name John. When using strings (text), always quote it - numeric values, such as 1 or 13, do not need quotations.

What have we learned thus far? Well, we learned how to select data. We learned how to restrict the columns returned (by specifying each column in the query) and also the number of rows returned (by using the WHERE keyword, also known as a 'clause').

Our table includes a numeric salary column, so let's use our WHERE clause and get more advanced in our restrictions. Let's say that we want to return all employees with a salary greater than 55000. To do this, we issue the following query:

SELECT *  
FROM emp 
WHERE Salary > 55000

That query produces the following results:

FirstName LastName PhoneNo Salary John Adams 343-4422 56000 Sam Spade 234-4797 64000 George Washington 234-7859 78000

Using the AND/OR keywords

With numeric data, we can use the greater than sign (>), less than sign (<) or equals sign (=). We can also combine a couple of these and further restrict our data. Let's issue a SQL statement that returns all employees with a salary between the values of 55000 and 65000. This is done in one of two ways.

SELECT * 
FROM emp 
WHERE Salary > 55000 AND Salary < 65000

This statement ensures that all records have a salary greater than 55000 AND a salary of less than 65000. The AND keyword requires that both conditions are true. Both 'salary > 55000' and 'salary < 65000' are conditions. Let's look at another way to write this same query.

The BETWEEN keyword

SQL provides a special keyword for use when we are restricting rows based on ranges. Let's translate the above SQL statement using the BETWEEN keyword.

SELECT *  
FROM emp 
WHERE Salary BETWEEN 55000 AND 65000

The lowest quantity is placed on the left, while the largest quantity is placed on the right. Let's take a look at what our results from either of the above queries look like.

FirstName LastName PhoneNo Salary John Adams 343-4422 56000 Sam Spade 234-4797 64000

The LIKE keyword

Let's take these restrictions a bit further by using SQL's LIKE keyword. Here, we can restrict returned rows based on portions of text fields. For example, let's return all records where the first name starts with the letter 'H'.

SELECT *  
FROM emp 
WHERE FirstName LIKE 'H%'

The % sign is used as a wildcard, which means anything. So, the above query looks at each row where the value in the first name field begins with H. After H, SQL doesn't care. So, the above query returns the following:

FirstName LastName PhoneNo Salary Henry Harrison 345-4524 52000

Having fun? Okay, let's now select all records were the last name ends with the letter 'n'. Our query now looks like this:

SELECT *  
FROM emp 
WHERE LastName LIKE '%n'

Now, SQL doesn't care about any letter but the last one within the LastName field. This query will return these results:

FirstName LastName PhoneNo Salary Henry Harrison 345-4524 52000 George Washington 234-7859 78000

Cool, huh? Let's do one more example, this time using a pattern as our criteria and referencing two columns. Try to figure out what the following query does first before reading on.

SELECT *  
FROM emp 
WHERE FirstName LIKE '%oh%' OR LastName LIKE '%ing%'

Can you figure this out? Here, we are looking for any first name with the pattern 'oh' anywhere within the string, or with the pattern 'ing' contained anywhere within the last name. These patterns can be contained anywhere within the names because we used the % wildcard on both ends of the pattern. The above query would return these results:

FirstName LastName PhoneNo Salary John Adams 343-4422 56000 John Smith 597-8904 49000 George Washington 234-7859 78000

Notice that we are using OR, not AND. OR means that only one of the two conditions must be true, not both. From the results above, we see that in each case, only one of the two conditions turned out to be true.

Next, let's look at inserting data into our table. Let's go ->