Categories
Business Intelligence

The Beginner’s Guide to SQL for Data Analysis

What Is SQL?

SQL stands for “Structured Query Language,” and it’s the programming protocol used for relational database management systems. Or, in plain English, SQL is the code that accesses and extracts information from data sets.

The Importance of SQL and Data Analysis

In our current economy, data ranks among the most commodifiable assets. It’s the fuel that keeps social media platforms profitable and the digital mana that drives behavioral marketing. As such, crafting the best SQL data queries is a top priority. After all, they directly affect bottom lines.

In our examples below, we use the wildcard * liberally. That’s just for ease and simplicity. In practice, wildcards should be used sparingly and only at the end of the query condition.

Display a Table

It’s often necessary to display tables on websites, internal apps, and reports.

In the examples below, we show how to a) pull every column and record from a table and b) pull specific fields from a table.

code

Adding Comments

Adding comments to SQL scripts is important, and if multiple people are working on a project, it’s polite! To add them, simply insert two dashes before the note. Don’t use punctuation in comments, as it could create querying problems.

Below is an example of a comment in a SQL query.

code

Combine Columns

You’ll want to combine two columns into one for reporting or output tables.

In our example below we’re combining the vegetable and meat columns from the menu table into a new field called food.

code

Display a Finite Amount of Records From a Table

Limiting the number of records a query returns is standard practice.

In the example below, we’re pulling all the fields from a given table and limiting the output to 10 records.

code

Joining Tables Using INNER JOIN

The INNER JOIN command selects records with matching values in both tables.

In the example below, we’re comparing the author and book tables by using author IDs. This SQL query would pull all the records where an author’s ID matches the author_ID fields in the book table.

code

Joining Tables Using LEFT JOIN

The LEFT JOIN command returns all records from the left table — in our example below that’s the authors table — and the matching records from the right table, or the orders table.

code

Joining Tables Using RIGHT JOIN

The RIGHT JOIN command returns all records from the right table — in our example the orders table — and the matching records from the left table, or the authors.

code

Joining Tables Using FULL OUTER JOIN

The FULL OUTER JOIN command returns records when there’s a match in the left table, which is the authors table in our example, or the right table — the orders table below. You can also add a condition to further refine the query.

code

Matching Part of a String

Sometimes, when crafting SQL queries, you’ll want to pull all the records where one field partially meets a certain criteria. In our example, we’re looking for all the people in the database with an “adison” in their first names. The query would return every Madison, Adison, Zadison, and Adisonal in the data set.

code

If/Then CASE Logic

Think of CASE as the if/then operator for SQL. Basically, it cycles through the conditions and returns a value when a row matches. If a row doesn’t meet any of the conditions, the ELSE clause is activated.

In our example below, a new column called GeneralCategory is created that indicates if a book falls under the fiction, non-fiction, or open categories.

code

HAVING Instead of WHERE

The HAVING and WHERE keywords accomplish very similar tasks in SQL. However, WHERE is processed before a GROUP BY command. HAVING, conversely, is processed after a GROUP BY command.

In our example below, we’re pulling the number of customers for each store, but only including stores with more than 10 customers.

code

It’s fair to argue that SQL querying serves as the spine of the digital economy. It’s a valuable professional asset, and taking time to enhance your skills is well worth the effort.

Polk County Schools Case Study in Data Analytics

We’ll send it to your inbox immediately!

Polk County Case Study for Data Analytics Inzata Platform in School Districts

Get Your Guide

We’ll send it to your inbox immediately!

Guide to Cleaning Data with Excel & Google Sheets Book Cover by Inzata COO Christopher Rafter