SQL Basic Queries
SQL (Structured Query Language) is a language designed to communicate with relational databases. Here is a good link if you want to learn more about relational database Vs Non-Relational database.
If you decide to use Relational database for your app, there is good chance you will use SQL to Create, Retrieve, Update or Delete data from the database. In this post, I will go over very basic commands to retrieve data from the database. But before we can learn to retrieve data from the database, it is important to understand how the data is stored.
In relational database, the data is stored in tables made of rows and columns. I am not going to get into too much details, but you should know that the tables represent classes, the columns attributes and the rows instances. In Ruby on Rails, the convention is to pluralize the class name to get the table name and to use snake case for the columns (using underscore for name longer than one word). So if the class is ‘Person’, the table name will be ‘people’, and a column could be ‘first_name’ for example.
I removed several columns from the original table to make it easier to see, but if you want to see what SQL statements were used to create the original table, you can head to this page.
I will use this marvels table to show most of the basic SQL queries. The table that you see above is actually the result of this SQL statement:
SELECT * FROM marvels;
Like you can see, SQL is made up of keywords like SELECT and FROM and names (table and column names), you could almost read it like plain English. The keywords are in uppercase (They don’t have to be for the SQL statement to be valid, but it’s more clear).The star means all, so you can see that the previous statement is asking to select all from the marvels table. All (*) meaning all columns.
The anatomy of a simple query statement to retrieve one column from the table goes like this:
SELECT name_of_column FROM name_of_table;
Make sure you don’t forget the semi-colon to finish the statement.
For example, if we want to retrieve the name of all marvel hero from our table, we would use this SQL statement:
SELECT name FROM marvels;
And it would return this:
If I wanted to retrieve data from more than one column, I could just add the name of another column separated from the first one by a comma like this:
SELECT name, gender FROM marvels;
And it would return:
We wrote our SQL statement in one line, but we can also write it on different lines as it will be more clear when the query becomes more complex. So I could have wrote the last statement like this:
SELECT name, gender
In fact I think it is easier to visualize the first keyword of each line. Those keyword are coming in a specific order. We first need to see what we want to select and then from where we want it selected. We will see later the meaning of the different keywords, but for now, try to remember their order.
We don’t have to use them all but if we use them we generally use them in this order.
I will not talk about the JOIN keyword since it is to join another table, and in this post I want to stick to one table, but know that if we had two tables, we could get columns from both table at the same time using the JOIN keyword.
For the moment all the queries that I have shown you are retrieving all the entries of the column they are selecting, but there are many ways to limit the query. For example what if we wanted only the name of the super hero that are female:
WHERE gender = "Female";
And it would return:
I selected only the name, but I could select all columns using the (*):
WHERE gender = "Female";
Like we can see the WHERE keyword is used with a condition, we can have more than one condition:
WHERE gender = "Female" AND height_m > 1.70;
AND is an operator that combines two conditions. Both condition must be true for the row to be included. We can also use the operator OR, it filters the result to include rows where either condition is true:
WHERE gender = "Female" OR height_m > 1.70;
This statement returned all hero but Wolverine since his height is only 1.60
We can also use MAX(column_name) and MIN(column_name) to find the minimum and maximum value in the column those functions have as argument.
SELECT MAX(height_m) AS "Maximum Height",
MIN(height_m) AS "Minimum Height"
In the previous statement, I used also the keyword AS that allow us to rename a column or a table with an alias.
We can also use the aggregate function AVG(column-name) to return the average of a numerical column:
SELECT ROUND(AVG(height_m), 2) AS "Average Height"
This statement was a little more complex but shows how SQL can be powerful when we start combining those keywords and functions. In this statement, I used the AVG aggregate function to find the average height and the ROUND function to round the result to 2 decimals. ROUND(column_name, integer) rounds the values in the column to the number of decimal places specified by the integer.
We can also use the SUM(column_name) function that takes the name of a column as an argument and returns the sum of all the values in that column. For example the cumulated weight of all the hero:
SELECT ROUND(SUM(weight_m)) AS "Total Weight"
To finish, I will show you how to use the ORDER BY. It is a clause that indicates you want to sort the result set by a particular column either alphabetically or numerically. So for example, we want to get the weight and height of the three tallest hero ordered by height:
SELECT height_m, weight_kg
ORDER BY height_m DESC LIMIT 3;
In this last statement, I used DESC a keyword that allows you to order in a descending order, from the largest number to the smallest and LIMIT, a clause that lets you specify the maximum number of rows the result set will have. So I was looking for the weight of the 3 tallest hero.
This is only an intro to the basic SQL statements. SQL is very powerful and with a little bit of practice, you will be able to grab anything from your tables. In addition, I am sure you will learn how to JOIN two or three tables.
Here are some materials if you want to go deeper:
SQL Tutorial - Learn SQL (Structured Programming Language) in simple and easy steps starting from basics to advanced…www.tutorialspoint.com
SQL, 'Structured Query Language', is a programming language designed to manage data stored in relational databases. SQL…www.codecademy.com
Learn how to use SQL to store, query, and manipulate data. SQL is a special-purpose programming language designed for…www.khanacademy.org