know-sql-in-2018

Ready to learn SQL? Browse courses like Apache Spark SQL developed by industry thought leaders and Experfy in Harvard Innovation Lab

Credit xkcd

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.

SQL alternatives

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.

create table exercise_logs (
id integer primary key autoincrement,
type text,
minutes integer,
calories integer,
heart_rate integer
);

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.

insert into exercise_logs(type, minutes, calories, heart_rate)
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.

Select statements

For many professions, querying data using SQL select statements is their primary bread and butter.

To select all records from a database:

select * from exercise_logs;

To find all the activities a user engaged in and the total amount of calories they burned doing that activity you could write:

select type, sum(calories) as total_calories
from exercise_logs
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.

/* Group the count of exercises by each heart rate zone */
select count(*),
case
    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'
from exercise_logs
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:

/* This is a comment */
create table drs_favorites (
id integer primary key,    /* Unique identifier */
type text,                 /* Type of activity */
reason text                /* Why the doctor recommends it */
);

insert into drs_favorites
(type, reason)
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.

SELECT * FROM exercise_logs WHERE type IN (SELECT type FROM
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.

Conclusion

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:

SQL Tryit Editor v1.5
Edit descriptionwww.w3schools.com

Happy coding!