How to Learn SQL: A U.S. News Guide

If you work with data but find programs like Excel too limiting, learning SQL may be the next step on your data journey.

(Getty Images)

This tutorial will show you the basics of SQL, a tool used by programmers, scientists and others around the world.

SQL is handy for breaking down a large amount of information into digestible parts, and you can learn this language using online resources.

SQL is often pronounced “sequel,” but it stands for “structured query language.” Think of it as the computer equivalent of asking verbal questions.

Let’s say you have a dataset of 911 calls in your city. You might ask, “How many robberies have there been?” and “What’s the most common emergency people have called in for?”

If you were on the phone with someone, you’d ask those questions in a spoken language. On a computer, you need to use a query language like SQL to communicate with a database.

Typically, SQL is reserved for datasets that are too big to fit in spreadsheet programs like Excel. This language is popular among businesses, financial managers, schools, software engineers and even journalists who need to present a large amount of information in a more manageable way.

Several courses are available for you to start learning SQL online.

To learn SQL, you need to understand a few basic concepts and commands. SQL uses a certain syntax, or word order, to converse with a database. A database is a collection of data, but typically someone who says “database” means a large one, such as hundreds of thousands of 911 calls.

A database manager is a program connecting two or more databases. Like SQL, these programs are often called into play when the database is too big for a spreadsheet.

“SQL is exciting because it’s really powerful,” says Lucia Walinchus, executive director of the Ohio Center for Journalism, an investigative journalism nonprofit. “It allows you to access vast amounts of data that would crash Excel.”

Walinchus and her team used SQL to extract relevant information from a dataset built from property and mortgage records. The result was a story that calculated thousands of dollars that small business owners were losing under a legal loophole.

Getting Started With SQL Queries

With SQL, the key is its middle name: query. In this context, a query is a question written in SQL syntax. For example:

SELECT call_time
FROM 911_calls
WHERE call_date = 2020-01-01

This query asks, “What time did 911 calls come in on New Year’s Day 2020?”

Here are some common phrases for making a query:

SELECT tells the computer what data you want to see. You can say “all” or limit it to certain criteria, like the ones below.

FROM tells the computer what dataset you’re using. In this case, our dataset is called 911_calls.

WHERE is similar to its use in English. You only want to see the data that meets certain criteria – for example, calls placed on New Year’s Day.

GROUP BY is a little more complex. If the dataset has a column with matching rows, it groups those rows together.

ORDER BY arranges your output. You can order by alphabet, date, number and so on.

Here’s a query using all five:

SELECT scanner_code, call_description
FROM 911_calls
WHERE call_date = 2020-01-01
GROUP BY call_description
ORDER BY call_description ASC

This query asks, “Why did people call 911 on New Year’s Day 2020?”

In this case, we’ve limited the data we want to see to the same criteria: 911 calls on New Year’s Day. But now the data is grouped by the call description and is in alphabetical order.

Already, we’ve taken a large amount of information and arranged it in a more useful way. Out of hundreds of thousands of calls, we found the ones we want to see and put them in order.

Getting Started With Functions

SQL is also useful for solving math problems quickly. You can put mathematical functions such as divide, sum, count and average into your query. For example:

SELECT call_description, COUNT(call_description)
FROM 911_calls
WHERE call_date = 2020-01-01
GROUP BY call_description
ORDER BY COUNT(call_description) DESC

This query asks, “How many of each call description came in on New Year’s Day?”

When you’re working with multiple datasets at once, you can use more powerful commands like JOIN. Try using math functions like sums by moving to the next step of W3Schools’ interactive dashboard.

SQL is a language, so it isn’t limited to one particular software program. Many professionals use MySQL or SQL Server, but if you’re just starting out, you can try a more basic version, like SQLite Manager.

SQLite Manager is a Firefox add-on that runs like a regular SQL program in a web browser. That way, you don’t have to install software that might confuse your computer settings.

Try playing around in a sandbox – a self-contained environment that lets you execute software or programs and test code. An online sandbox like DB Fiddle lets you practice queries without affecting other parts of your computer.

Once you have the basic syntax down, you can up your data game from spreadsheets to commands and code, like Python.

“SQL has outlived a lot of efforts to replace it because it just works,” says Troy Thibodeaux, who leads a team of data journalists at The Associated Press. “For a lot of data journalists, it has been an important step on the path from point-and-click to code.”

Since SQL is so popular, online resources for learning the language are widely available. Thibodeaux wrote a guide that uses a free program called DB Browser. If you prefer learning through videos, consider subscribing to a series produced by the nonprofit Investigative Reporters & Editors’ National Institute for Computer-Assisted Reporting.

Test your skills with the Knight Lab’s SQL Murder Mystery. Find a killer in “SQL City” using crime data like those in the above examples.

These days, practically every career involves data – even non-tech fields like construction or design. Being able to analyze data can make you stand out from other job candidates.

In some fields, like business and engineering, you might use these tools right out of the gate. You may not have a job title like “SQL programmer,” but you might use this language to perform high-level tasks, like finding trends in customer shopping habits. Including your SQL experience on your resume can demonstrate your technical proficiency and problem-solving skills.

With the endless stream of data today and the need to understand it better, knowing SQL can boost your career.

Source link