Learn the basics of SQL

When you want to become a software developer you’ve got a bajillion things to learn. This makes it scary for the beginner, since there are few things to grab on to to simplify the task. I’ve taken unto me teaching a bunch of African students how to become software developers. To scale up my efforts, I’ve also started an article series where I teach all the basics they’ll need to understand before they are professionals. In this article I will have my students learn the basics of SQL, and I’ll try to compress it as much as possible, making it dead simple at the same time. There is a video further down in the article in case you prefer to see me demonstrate SQL instead of reading. However, first some basic theory allowing my students to create fruitful associations, simplifying the task.

CRUD

To simplify things, I’ve created the entire article series around CRUD. CRUD of course is an acronym and it means Create, Read, Update and Delete. CRUD is the axiom around which all data manipulation evolves. Basically, if you understand CRUD, you understand “data”. SQL is also obviously based around CRUD, except it’s using synonyms to the CRUD verbs, and these are as follows.

• Insert (Create)
• Select (Read)
• Update (Update)
• Delete (Delete)

The above are the four basic SQL keywords, and how they map to the CRUD verbs. If you understand how to use the above 4 verbs, you basically understand SQL. In the video below I run through all verbs, with example code, using Aista Magic Cloud and SQL Studio to execute my SQL towards the Chinook database. Watch the video and then look at the example code found below.

The select keyword

The select keyword is arguably the most complex. It allows for grouping, aggregating, filtering, paging, joins, and everything required to select data from your database. However, in its most fundamental syntax it looks as follows. This is the first example snippet I use from the above video.

select * from Album
  where Title = 'For Those About To Rock We Salute You'
    or Title = 'Balls to the Wall'

The above selects all columns from all records in the “Album” table. The Album table is a table in the Chinook database. The asterisk implies “everything”, or all columns. The above “where” keyword allows you to filter your result. If you only execute the first line of code above, the database will return every single record in its album table. The where keyword allows you to filter your result, and only return the parts that meets the condition(s) of your where. If you exchange the above asterisk (*) with a list of columns, you can select a subset of columns. This is called “projections” and even allows you to select columns and “renaming” the name of your columns as the result is being returned from the database.

Insert, Update and Delete

Below is the second snippet from the video, giving you an example use case of all CRUD verbs in SQL. Notice, to understand these keywords, you want to watch the above YouTube video where I walk you through them.

/* CREATE */
insert into Album (Title, ArtistId) values ('Howdy tutorial world', 1);

/* READ */
select * from Album
  where AlbumId = 348
    or Title = 'Balls to the Wall';

/* UPDATE */
update Album set Title = 'Howdy NEW turoaisl world' where AlbumId = 348;

/* DELETE */
delete from Album where AlbumId = 348;

SQL homework

In later articles I will dive deeper into the select keyword, since it allows for grouping, joins, aggregating, filtering and paging – And hence arguably is the most complex of all SQL keywords. However, if you want to start early, the Chinook plugin database installs a bunch of example SQL scripts you can play with, such as for instance the following SQL.

/*
 * Script to aggregate records for each artist in
 * chinook database.
 * Make sure you select the chinook database before executing
 * the script.
 */
select ar.Name, count(*) as count
  from Album al, Artist ar where al.ArtistId = ar.ArtistId
  group by al.ArtistId
  order by count desc
  limit 25

If you execute the above SQL, you will be given something resembling the following.

What the above is basically showing you, is how many records each artist have created. Then it’s sorting descending such that the artist with the most records is shown at the top, before it finally limits the result set to the first 25 records. To translate into humanly understandable words this becomes the following result set.

Give me the name of the 25 artists having released the most albums, and provide me with their album count, and sort such that the artists with the most albums released are returned first

The reason why the above works is because of the “count(*) as count” parts. The select statement allows you to declare which columns you want to return, where “*” means “all columns”. However, instead of returning columns, you can also return a list of columns as we do above. You can also return “aggregate function results”. The count function is one such aggregate function. The above SQL is also selecting records from multiple tables at the same time. In addition the SQL is using alias for both tables. This is the “ar” and “al” parts. This allows us to use “ar” as an alias for the “Artist” table, and “al” as and alias for the +“Album”_ table.

When you use aggregate functions you must also use the “group by” keywords. This informs the database how the aggregate should be grouped. The above SQL for instance is grouping by ArtistId. Since each artist have a unique ArtistId, and we’re grouping by ArtistId, the result of the count operation basically becomes a count of all albums with the same ArtistId.

In future articles I will go deeper into the subject. However, for now, feel free to look at the example SQL scripts that comes together with the Chinook database and look at this task as homework ...