As a technical recruiter I came across many positions where SQL was a critical skill. To be a Business Analyst, Data Scientist, Backend Developer, Marketing professional or UX Design Researcher understanding SQL queries can be fundamental to your work.
Let me back up. What is SQL anyway? SQL is a query language for talking to structured databases. Pretty much all databases that have tables and rows will accept SQL based queries. SQL has many flavors but the fundamentals stay the same. Professionals and amateurs alike use SQL to find, create, update and delete information from their sources of record. It can be used with a ton of different databases like MySQL, Postgres, SQL Server and Oracle. It powers the logic behind popular server-side frameworks like Ruby On Rails and Laravel. If you want to find information associated with a particular account or query what buttons users click in your app there is a good chance SQL can help you out!
SQL: Pronounced “S-Q-L” or “Sequel” — you choose.
Before we hop on the SQL train to Database Town I’d like to acknowledge some alternatives. You can use ORMs to query databases. ORM stands for Object Relational Mapper, which is a fancy way of saying that you can write code in a programming language like PHP or Node.js that translates to SQL queries. Popular ORMs are Active Record for Ruby On Rails, Eloquent for Laravel and Sequelize for Node.js. All of these services allow you to write code that translates to SQL under the hood. SQL is important for building applications with these technologies.
There are many databases that do not use SQL, such as MongoDB and GraphQL. These are newer technologies and not as widely adopted as relational databases. Relational databases have been around a very long time and power the majority of data storage on the internet. To fully appreciate NoSQL technologies and the reasons they came about it’s helpful to know how relational databases and SQL work.
Create a table
The first thing to know is that relational databases (such as MySQL, SQLite or PostgreSQL) are made up of tables. One database can hold many tables and each table consists of a particular category of record. For example, in an eCommerce site we might have tables for orders, users, transactions and products. In many web applications you’ll create tables through migrations, but it’s still helpful to be able to read and write SQL create statements.
id integer primary key autoincrement,
The above SQL statement creates a table called exercise_logs with five columns (id, type, minutes, calories and heart_rate). Each column has a specific data type, such as integer or text.
On the second line of our create statement we specify that each new record will have a unique id, known as the table’s primary key.
The structure and definition of a database’s tables is known as the database’s schema.
Insert data into a table
To add an exercise log to our table we can write a SQL insert statement.
values ('biking', 30, 100, 110);
In this example we specify the table name, exercise_logs and the columns for which we’d like to insert data. After the values keyword we include the data to be inserted into the database.
For many professions, querying data using SQL select statements is their primary bread and butter.
To select all records from a database:
To find all the activities a user engaged in and the total amount of calories they burned doing that activity you could write:
group by type;
You can do all kinds of funky stuff like determine the number of students had which letter grade.
You could group each of the exercises by heart rate zones.
when heart_rate > 220 - 30 then 'above max'
when heart_rate > round(.9 * (220 - 30)) then 'above target'
when heart_rate > round(.5 * (220 - 30)) then 'within target'
else 'below target'
end as 'heart_rate_zone'
group by heart_rate_zone;
SQL is very helpful for reporting purposes. It can be used to filter marketing results, find customer information, search server logs or create reporting dashboards.
Subqueries and Like
Sometimes you’d like to dynamically grab data with a query and use that result in another query. For this case we have subqueries. For example, we could have a table called drs_favorites that holds doctor recommended activities:
create table drs_favorites (
id integer primary key, /* Unique identifier */
type text, /* Type of activity */
reason text /* Why the doctor recommends it */
values ('running', 'improves cardiovascular health.');
For our main query we would like to find all the activities in the activity_log table that doctors recommended for improving cardiovascular health.
drs_favorites WHERE reason LIKE "%cardiovascular%");
In between the parenthesis we have a subquery that selects all the records where the word “cardiovascular” is included somewhere in the reason the doctor recommends it. Read more about the LIKE operator. Note that SQL can use capital or lowercase letters.
That’s the tip of the iceberg! The exercise code, including joins and SQL statements to talk to multiple databases are available on this Github repository.
If you’d like to play around with a test database and write some queries, W3schools has a SQL editor available here: