SQL in just 20 minutes

Every self-respecting web developer should know SQL.

Although it has existed since the 70s of last century, it is still very widely used, and without it it will be difficult to create something serious.

Most full-stack frameworks are able to work with SQL. Among them: ActiveRecord, Doctrine, Hibernate and many others. Despite this, sometimes you have to “dirty hands” and go into real SQL.

That is why we have prepared a short introduction, in which we will walk through the fundamental things in SQL. We strongly recommend you to try all the examples below yourself, because, as you know, theory is nothing without practice.

Well, here we go!

Let’s create a table

In order to create a table in SQL, the expression CREATE TABLE is used. It accepts as parameters all columns that we want to enter, as well as their data types.

Let’s create a table called “Months”, which will have three columns:

• id – in other words, the ordinal number of the month (integer type or int)
• name – month name (string or varchar(10) (10 characters – maximum string length))
• days – number of days in a certain month (integer type or int)

The code will look like this:

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

Also, when creating tables, it’s customary to add the so-called primary key. This is a column where the values are unique.

Most often, the primary key column is id, but in our case it can also be name, since the names of all months are unique. For more information, please follow this link.

Data Entry

Now let’s add a couple of months to our plaque. You can do that with the INSERT command. There are two different ways to use INSERT:

The first method does not involve specifying column names, but only takes the values in the order they are in the table.

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

The first method is shorter than the second, but if we want to add more columns in the future, all previous requests will not work. We should use the second method to solve this problem.

Its essence is that we specify the names of columns before entering the data.

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

If we don’t specify one of the columns, we’ll write NULL or the default value, but that’s a different story.

Select

This query is used when we need to show data in a table. Probably the easiest example of SELECT usage is the following query:

SELECT * FROM characters

The result of this query is a table with all data in the table. An asterisk (*) sign means that we want to show all columns from the table without exception.

Since there is usually more than one table in the database, we need to specify the name of the table from which we want to see the data. We can do this by using the FROM keyword.

When you only need some columns from a table, you can specify their names by comma instead of an asterisk.

SELECT name, weapon FROM characters

Also, sometimes we need to sort the output. For this we use ORDER BY “column name”. ORDER BY has two modifiers: ASC (ascending) (default) and DESC (descending).

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

Where

Now we know how to show only specific columns, but what if we want to include only some specific rows in the output? We use WHERE for that. This keyword allows us to filter the data by a specific condition.

In the next query, we will only output characters that use the gun as a weapon.

SELECT *
FROM characters
WHERE weapon = 'pistol';

And/or

Conditions in WHERE can be written using logical operators (AND/OR) and mathematical comparison operators (=, <, >, <=, >=, <>).

For example, we have a tablet that records data on 4 of the best selling music albums of all time. Let’s display only those that are rock genre, and sales were less than 50 million copies.

SELECT *
FROM albums
WHERE genre = 'rock' AND sales_in_millions <= 50
ORDER BY released

In/Between/Like

Conditions in WHERE can be written using several other commands, which are:

• IN – compares the value in a column with several possible values and returns true if the value matches at least one value
• BETWEEN – checks if the value is in a certain interval
• LIKE – looking for a pattern

For example, we can make a query to display data about albums in pop or soul genre:

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

If we want to output all albums that were released between 1975 and 1985, we can use the following record:

SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;

Also, if we want to output all albums that have an ‘R’ in their title, we can use the following record:

SELECT * FROM albums WHERE album LIKE '%R%';

The % sign means any sequence of characters (0 characters also counts as a sequence).

If we want to output all albums with ‘R’ as the first letter in their title, the record will change slightly:

SELECT * FROM albums WHERE album LIKE 'R%';

SQL also has an inversion. For example, try to write NOT before any logical expression in the condition (NOT BETWEEN and so on).

Functions

SQL is full of built-in functions for performing various operations. We will show you only the most frequently used functions:

• COUNT() – returns the number of lines
• SUM() – returns the sum of all fields with numerical values in them
• AVG() – returns the average value among rows
• MIN()/MAX() – returns minimum/maximum value among rows

To display the year of release of the oldest album, you can use the following query in the table:

SELECT MAX(released) FROM albums;

Note that if you write a query asking, for example, for the name and average of something, you will get an error in the output.

Let’s say that you are writing such a query:

SELECT name, avg(age) FROM students;

To avoid the error, you should add the following line:

GROUP BY name

The reason for this is that the avg(age) record is aggregated and you need to group the values by name.

Attached Select

In the previous steps, we learned how to make simple calculations with data. If we want to use the result of these calculations, we often need to use so-called nested queries. Let’s say we need to display the artist, the album and the year of the oldest album in the table.

You can display these columns using the following query:

SELECT artist, album, released FROM albums;

We also know how to get the earliest year available:

SELECT MIN(released) FROM album;

You can merge these queries in WHERE:

SELECT artist,album,released
FROM albums
WHERE released = (
SELECT MIN(released) FROM albums
);

Attachment of tables

In complex databases, we most often have several linked tables. For example, we have two tables: about video games and about developers.

In video_games table we have developer_id column, in this case it is so called foreign_key. To make it easier to understand, developer_id is a link between two tables.

If we want to display all information about the game, including information about its developer, we need to connect the second table. To do this, we can use INNER JOIN:

SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country.
FROM video_games
INNER JOIN game_developers
ON video_games.developer_id = game_developers.id;

This is probably the simplest example of using JOIN. There are several other ways to use it. For more information, please follow this link.

Aliases

If you look at the previous example, you will notice that there are two columns named equally: “name”. This can often be confusing. The solution to this problem are pseudonyms. By the way, they help make the column name more beautiful or understandable when necessary.

To assign an alias to a column, you can use the keyword AS:

SELECT games.name, games.genre, devs.name AS developer, devs.country
FROM video_games AS games
INNER JOIN game_developers AS devs
ON games.developer_id = devs.id;

Update

Often, we need to change the data in the table. In SQL, this is done using UPDATE.

Using UPDATE includes:

• selecting the table that contains the field we want to change
• new value entry
• using WHERE to indicate a specific location in the table

Let’s say we have a table of the most highly regarded shows of all time. However, we have a problem: “Game of Thrones” is marked as a comedy and we definitely need to change that:

UPDATE tv_series.
SET genre = 'drama'
WHERE name = 'Game of Thrones';

Removing records from the table

Deleting a record from a table via SQL is a very simple operation. All you need to do is designate what we want to delete.

DELETE FROM tv_series
WHERE id = 4;

Note: make sure you use WHERE when you delete an entry from the table. Otherwise, you will delete all records from the table without wanting to.

Deleting tables

If we want to remove all data from the table, but leave the table itself, we should use the TRUNCATE command:

TRUNCATE TABLE table_name;

If we want to remove the table itself, we should use the DROP command:

DROP TABLE table_name;

Conclusion

On this note, we conclude this SQL tutorial. Of course, that’s not all, and there is still a lot to learn to fully master, but this introduction will give you an incentive to further explore.

SQL Tutorial – Full Database Course for Beginners