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
-
What SQL is, including its capabilities
-
How to select, insert, update and delete data
-
Restricting data using WHERE, LIKE, BETWEEN and AND/OR
-
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:
|
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):
|
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.
|
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:
|
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.
|
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:
|
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:
|
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:
|
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.
Inserting data
Now that we know how to select data, let’s learn how to insert data into our table. At the moment, our table consists of 5 records and looks like this:
|
We’ve hired a new employee, named Jim Parker. His telephone number is 433-1111 and his starting salary is 45,000. So, let’s insert this data using the INSERT INTO keywords.
INSERT INTO emp (FirstName, LastName, PhoneNo, Salary) VALUES (’Jim’,'Parker’,'433-1111′,45000)
To insert data, we use the keywords INSERT and INTO, then we specify the table name (emp, in this case). Next, we place the columns that we’re inserting data into in parenthesis. After, we use the VALUES keyword and use parenthesis to specify the values that will be placed in each respective column. The first column listed will receive the first value listed.
After this query executes, our table looks like this:
|
A question often asked is, “If I am inserting data into each column, do I have to specify every column in the query?” The answer to this question is no. After specifying the table name, you can immediately use the VALUES keyword and write the values to be inserted. Again, the first column in the database will receive the first value specified. So, the query might look like this:
INSERT INTO emp VALUES (’Jim’,'Parker’,'433-1111′,45000)
If, however, an insert statement does not insert data into each column, the column names that will receive data must be specified.
Updating data
Thus far, we’ve learned how to select and insert data. In the last section, we inserted Jim Parker into our table using the INSERT INTO keywords. But, it turns out that we’ve made a mistake with Jim’s phone number and salary. They should read 456-4422 and 47000, respectively.
Before we begin, here’s a look at our table.
|
Okay, let’s make the appropriate changes to our table. To update a record, we use the UPDATE keyword. To update Jim Parker’s record, we’d use something like this:
UPDATE emp SET PhoneNo = ‘456-4422′, Salary = 47000 WHERE FirstName = ‘Jim’ AND LastName = ‘Parker’
Here, we use the UPDATE keyword, then specify the table name. The SET keyword is used to directly modify a value within a column. In this case, we are setting PhoneNo and Salary to the values on the right hand side of the equals sign (=). Lastly, we specify which record that we’d like these updates to take effect for. So, our query specifies that both changes should take effect for ALL records where the first name is Jim and the last name is Parker.
Note that if we forgot to use the WHERE keyword, ALL records would subsequently get changed.
UPDATE emp SET PhoneNo = ‘456-4422′, Salary = 47000
Bad query. This means all employees would have a phone number of 456-4422 and a salary of 47000 because we don’t restrict where these changes take place, using WHERE. Do not forget to use the WHERE keyword when the changes should only take place for certain records.
Deleting data
Up to this point, we’ve seen how to select data, insert data and modify data, using our emp table as our example. Now, let’s take a look at how data is deleted. Just like with our update SQL statement, it is important to remember to use the WHERE keyword when deleting.
Before we begin, here’s our current emp table.
|
Let’s delete Sam Spade from our table. To do this, we use the DELETE keyword. Here’s what the statement looks like.
DELETE FROM emp WHERE FirstName = ‘Sam’ AND LastName = ‘Spade’
Here, we don’t need to specify either * or column names because the entire record will get removed from our table. We used the WHERE keyword to restrict the deletion only to Sam Spade.
Our table now looks like this:
|
What if we want to delete all records from the table? Actually, there are two ways of doing this. First, we can use the DELETE keyword and not restrict the row, like this:
DELETE * FROM emp
The TRUNCATE keyword
In addition to DELETE, some databases like Oracle or MySQL support the ‘truncate’ keyword. The truncate keyword actually removes and recreates the table in a single statement and is faster than our DELETE statement above when deleting large tables. A truncate SQL statement looks like this:
TRUNCATE TABLE emp
At this point, you should be able to interact with a database and perform basic tasks, including selecting and restricting data, inserting data, updating data and deleting data. The next couple of pages of this tutorial dives into the administrative tasks of creating databases and tables.
Creating a database
With the basics of interacing with tables out of the way, we can get our hands a bit dirty and look at administrative tasks, like creating entire databases and tables.
Creating a database is very simple; we use the CREATE and DATABASE keywords. To create a database called MyTechCompany, use the following statement:
CREATE DATABASE MyTechCompany
Can’t get much simpler than that, can it? Depending on the database management system in use (ie: Oracle, DB2, MySQL, SQL Server), some configuration options might be available. Because these configuration options are highly unique based on the system, they will not be discussed here.
At this point, we have our database created. But, we cannot store our data within this database because no tables exist. So, let’s learn how to create tables.
Creating tables
Now that we have a database created, we can begin creating tables.
Similar to our CREATE DATABASE statement, we create a table by using the CREATE TABLE keywords. So, to create a table called emp, we issue this SQL statement:
CREATE TABLE emp
Although this does create the table emp, no columns exist within it. After the table is created, the only way to add columns to it is to alter the table, using the ALTER TABLE statement. But, if we thought ahead and came up with a collection of columns before we created the table, we can add the columns to the table in the same statement. Let’s take a look at how we do that.
CREATE TABLE emp
(
FirstName varchar(45),
LastName varchar(45),
PhoneNo varchar(10),
Salary int(7)
)
Here, we have defined our table name as emp. Next, with define each column name along with its data type, separated by a comma. A data type defines the type of data to be stored within the column. For First and Last name, we’ve defined the column to hold a maximum of 45 characters. The phone number column holds no more than 10 characters and our Salary column holds an integer (numeric), with no more than 7 numbers.
You might ask yourself why I didn’t define PhoneNo as an integer as well - after all, phone numbers are integers, right? Yes, they are. But, in our case, we use the dash (-) between the first 3 and the last 4 digits. Since the dash is NOT an integer, it makes our phone number a string of characters and therefore cannot be defined as an integer data type.
A number of different data types exist for data. Some data types are generic and available in most databases, like varchar, char, int and date, while others are database specific. Also be aware that although most databases support the ‘date’ data type, some format it differently. Be sure to read the documentation for your database management system to obtain correct formatting dependencies in your case.
So great, now our database MyTechCompany contains one table, emp. From here, we can use our INSERT statement to feed data to it, and our SELECT statement to return the data that we’ve stored.
Wrapping it up
Throughout this tutorial, we’ve learned how to create databases and tables. We also learned how to insert data into our table, select it, update it and delete it. Take a look at our mock scenario below, where we create a database called MyTechCompany. We then create the emp table within that database and insert a record into it. We then select that record, update it and select it again. Lastly, we delete the record from the table.
CREATE DATABASE MyTechCompany
CREATE TABLE emp
(
FirstName varchar(45),
LastName varchar(45),
PhoneNo varchar(10),
Salary int(7)
)
INSERT INTO emp (FirstName, LastName, PhoneNo, Salary) VALUES (’Jim’,'Parker’,'433-1111′,45000)
SELECT * FROM emp
Our tasks above produce the following output:
|
Now let’s update the record and select from it again.
UPDATE emp SET PhoneNo = ‘456-4422′, Salary = 47000 WHERE FirstName = ‘Jim’ AND LastName = ‘Parker’
SELECT * FROM emp
Our update above provides this result:
|
And lastly, let’s delete the record.
DELETE FROM emp WHERE FirstName = ‘Jim’ AND LastName = ‘Parker’
And there you have it - I hope you have learned something.
