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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
