Big Data

SQL refresher: How to gather data from multiple tables

As a TechRepublic reader, you're probably familiar with SQL. But reviewing the basics can help keep your skills sharp.

mangostarstudioistock-626566018.jpg

Image: iStockphoto.com/MangoStar_Studio


It's never a bad idea to give yourself a brief refresher on skills that may be a little rusty. You may not use these skills every day, but they can come in handy at crunch time. There are many tools available for gathering data. Most come with flashy user interfaces and buttons. But occasionally, the good ol' skill of SQL is the way to go. Let's do a quick refresher on grabbing data from multiple tables in a database with SQL.

SEE: How to use SQL to create more SQL

Data request

Let's start with a simple data request scenario. Your shoe company's executive wants to know what shoes were sold in the state of Oregon during Q4 of 2016. It's cold and rainy during those months, so the executive team wants to know what assumptions can be made about the footwear customers in Oregon are purchasing. A quick SQL script can provide these numbers. You just have to know your database.

First, you have an inventory table. Let's call it "inv_shoes." The inv_shoes table (Table A) has a few fields (columns) noted as "shoe_type," "shoe_color," and "shoe_name."

Table A: inv_shoes

shoe_typeshoe_colorshoe_name
SNEAKERWHITEULTIMATE
BOOTBROWNENDURO
DRESSBLACKEXECUTIVE

Next you will need to extract customer order information. Take a look at the "sys_orders" table (Table B).

Table B: sys_orders

customerstate_codeshoe_namedate
ALFRED HITCHCOCKOREXECUTIVE11/20/2016
HANK MCCOYNYENDURO12/15/2016
SHELDON COOPEROREXECUTIVE01/04/2016

Make the connection

The key to gathering data across different tables in a relational database is knowing where the data relationships are. When you discover the related data, use an SQL join within your "where" clause to extract the data. We know a standard SQL statement consists of three components. The "select," the "from" and the "where." The select statement describes the basis of the data you're looking for. The from statement describes where you want the data to come—from. The where clause allows you to specify any particular details and parameters of the data you're looking for. So in our shoe sales query, we want to know what shoe names sold in the state of Oregon during the fourth quarter. Let's construct the SQL script:

Select customer, shoe_name, state_code

From inv_shoes, sys_orders

Where state_code = 'OR'

And date BETWEEN '10/01/2016' and '12/31/2016'

The quick scribble of my SQL would return a syntax error because I didn't craft the script properly. I'm asking the code to look in two tables for the data. Therefore, I need to add a join in my where clause and I need to clearly specify the field names in the select statement. Otherwise, I'll get an error about my requested column names being ambiguous.

Select b.customer, b.shoe_name, b.state_code

From inv_shoes a, sys_orders b

Where a.shoe_name = b.shoe_name /*this is the join */

And state_code = 'OR'

And date BETWEEN '10/01/2016' and '12/31/2016'

The above SQL should return the following report:

customershoe_namestate_code
ALFRED HITCHCOCKEXECUTIVEOR

This data could be presented to your executive as a simple report. Sure there were two sales for the state of Oregon, but only one sale was during the fourth quarter in this hypothetical scenario—so there's only one record is in the report.

Big data is king these days. Recognizing the different data points and being able to retrieve the data is just as important. If you're wanting to spice up your data analysis days, jump away from the fancy front ends of your reporting applications, fire up a database management system, and start writing SQL.

Also read...

Other suggestions?

What SQL techniques do you brush up on from time to time? Share your thoughts and advice with fellow TechRepublic members.

About Ant Pruitt

Ant Pruitt is an IT Support Professional with a passion for showing the non-geek how great technology can be. He writes for a variety of tech publications and hosts his own podcast. Ant is also an avid photographer and weight lifter.

Editor's Picks