Free PHPFree PHP scripts classes and info

Learn SQL In 20 Minutes

Every web developer should know SQL. Although it has been around since the 70s, it is still widely used, and you can’t build a serious application without it. Most full-stack frameworks have libraries for dealing with the SQL complexity – ActiveRecord, Doctrine, Hibernate and more. But often times you need to get your hands dirty and write low-level SQL.

This is why we’ve prepared a short and to-the-point introduction to the basics of SQL. Within the article, you will find our interactive editors for writing and executing your code. Don’t be afraid to experiment with them and try to complete the bonus tasks.

Let’s begin!

1. Create Table

When creating new tables in SQL the CREATE TABLE statement is called. It expects as it’s arguments all the columns we want in the table, as well as the their data types.

Here we are creating a simple table called months. It consists of 3 columns:

• id – The number of the month in the calendar year (integer).
• name – Name of the month (string, maximum of 10 characters).
• days – The number of days in that month (integer).

And this is how the respective SQL looks like:

CREATE TABLE months (id int, name varchar(10), days int);

Also when creating tables, it’s advisable to add a primary key to one of the columns. It will help keep entries unique and will speed up select queries. We won’t be covering them in this lesson but you can read about them here.

2. Insert Rows

Now let’s populate months with a few rows of information. Adding entries to a table is done via the INSERT statement. There are two different ways to use it:

The first way does not specify the column names where the data will be inserted, it only expects the values and leaves it up to the developer to provide all the data in the correct order.

INSERT INTO months VALUES (1,'January',31);

The above is shorter, but there is a major issue – if we add additional columns in the future, the query will break. The preferred way to write this is to include the columns:

INSERT INTO months (id,name,days) VALUES (2,'February',29);

You can try running these commands in the editor below.

Bonus: Write an INSERT statement to add more months to the table.

3. Select

Select queries are our best friend when we want to fetch data from the database. They are used all the time so this lesson is going to spend a lot of time covering them.

The most simple SELECT example would be this query, which will return all the columns and rows from the characters table:

SELECT * FROM "characters"

The asterisk (*) means that we want to grab all of the columns, without excluding anything. Since SQL databases usually consist of more then one table, the FROM keyword is required to specify which table we want to look in.

Sometimes we don’t want all of the columns in a table. SQL allows us to choose and get only the ones we need: instead of putting the asterisk (*), we write the names of the desired columns.

SELECT name, weapon FROM "characters"

Also, in many cases we want the results be sorted in a certain way. In SQL we do this with ORDER BY. It can take an optional modifier – ASC (default) or DESC for sorting direction:

SELECT name, weapon FROM "characters" ORDER BY name DESC

Bonus: Write a query that will SELECT the name, race and hobby columns.

4. Where

You learned how to select only specific columns, but what if only certain rows need to be acquired. To the rescue here comes the WHERE clause, allowing us to filter data depending on a condition.

In this query we select only those entries from the characters table, who use a pistol to fight baddies.

Bonus: Create a SELECT query that fetches the name, race and hobby for those characters who are “Wookiees”.

5. AND / OR

WHERE conditions can be made as specific as you like, with the help of the logical operators (AND,OR) and math-like comparisons (=,<,>,<=,>=,<>).

Here we have a table containing the top 4 most sold albums of all time. Let’s select those of them that are classified as rock and have sold under 50 million copies. This can easily be done by placing the AND operator between the two statements.

Bonus: Try and write an SQL query that returns the albums released after 1975 with sales less than 60 million.

6. In/Between/Like

WHERE statements also support a few special commands, allowing a quick way to check commonly used queries. They are:

• IN – compares the column to multiple possible values, resolves true if it matches at least one
• BETWEEN – checks if a value is within a range
• LIKE – searches for a specific pattern

For example if we want to do a query selecting the pop and soul albums from our table, we can use IN("value1","value2").

SELECT * FROM albums WHERE genre IN ('pop','soul');

If we want to get all the albums released between 1975 and 1985 we would write:

SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;

Bonus: All of the above operations can be reversed by placing NOT in front of them. Try using NOT BETWEEN to get the albums released before 1975 and after 1985.

7. Functions

SQL is packed with functions that do all sorts of helpful stuff. Here are some of the most regularly used ones:

• COUNT() – returns the number of rows
• SUM() – returns the total sum of a numeric column
• AVG() – returns the average of a set of values
• MIN() / MAX() – gets the minimum/maximum value from a column

To get the most recent year in our table we can run:

Bonus: Try combining SUM with a WHERE clause and fetch the combined sales of all rock albums.

8. Nested Select

In the previous point we learned how to do simple calculations with data. If we want to actually use the result from these calculations, often times a nested query (also known as sub select) is necessary. Let’s say we want to get the artist, album and release year for the oldest album in the table.

We know how to get these specific columns:

SELECT artist, album, released FROM albums;

We also know how to get the earliest year:

SELECT MIN(released) FROM album;

All that is needed now is to combine the two with the help of WHERE:

Bonus: Modify the above statement to return the album, genre and year for the newest album.

9. Joining Tables

In more complex databases, most of the time there are several tables connected to each other in some way. For example, below in the editor are two tables about video games and video game developers.

In the video_games table there is a developer column, but it holds an integer instead of the name of the developer. This number represents the id of the respective developer from the game_developers table, linking logically the two sheets, allowing us to use the information stored in both of them at the same time.

If we want to create a query that returns everything we need to know about the games, we can use INNER JOIN to acquire the columns from both tables.

This is the simplest and most common type of JOIN. There are a couple of other options, but they are applicable to less frequent scenarios. Chart of SQL Joins

10. Aliases

If you look at the previous example you’ll notice that there are two columns called name. That’s confusing, so let’s change it by setting an alias to one of the repeating columns: name from game_developers will appear as developer.

We can also shorten the query drastically by setting aliases to the table names: video_games becomes games and game_developers becomes devs.

11. Update

Often times we have to change the data in some of the rows. In SQL this is done via the UPDATE statement.

The usage of UPDATE consists of

• Choosing the table where the record we want to change is located.
• Setting new value(s) for the wanted column(s).
• Selecting with WHERE which of the rows we want to update. If this is omitted all rows in the table will change.

Here is a table of some of the highest rated TV series of all time. There is one tiny problem about it though, the show Game of Thrones is described as comedy, which it clearly isn’t. Let’s fix that!

Bonus: Let’s say that Breaking Bad is renewed for a new season. Write an SQL statement changing the still_running column to yes.

12. Delete Rows

Deleting a table row through SQL is a really simple process. All that is needed is to select the right table and row we want to remove.

Important: Always be cautions when writing a DELETE statement and make sure there is a WHERE clause attached. Without it all table rows will be deleted!

13. Deleting Tables

If we want to delete all the rows, but leave the table itself, the proper command is TRUNCATE:

TRUNCATE TABLE table_name;

In the case when we actually want to remove every trace of the table whatsoever, the DROP command comes into play:

DROP TABLE table_name;

Be very careful with these commands. There is no undo!

Conclusion

This wraps up our tutorial on SQL! There is a lot more to cover, but the above should be enough to give you a few practical skills in your web dev career.

For more information on SQL check out these great resources:

• Codeacademy SQL course – here
• SQL Fiddle: online tool for testing and sharing SQL – here