5 Steps to using SQL Code for Non-Coders

by | Sep 24, 2019

SQL is the key which opens the door to data visualizations and positive business impact.

If you’re scared of spiders or of visit­ing the dentist, I completely empathize with you. (I’m on the same page! Shh, don’t tell my dentist 🙂 .) But if you’re afraid of SQL, then it’s time to get things straight: SQL is not scary. You don’t need to be a Data Scien­tist or Soft­ware Devel­oper in order to use it, and it even reads in sentences similar to every­day English. To quote Mike Martinez from his post on Quora, “SQL is halfway to English already”.

Many busi­ness users or analysts who work with data already know how to use Excel data func­tions or visual inter­faces for SQL e.g. Magic ETL in Domo, Tableau Prep in Tableau, Query Editor in Power BI. Those tools are great — yet the func­tion­al­ity can be limited. For the purpose of this article, I’ll call those tools “Fake SQL” since you’re actu­ally using SQL — just without knowing it!

If you’ve ever wished that you could have greater control over the data manip­u­la­tion process — or if you’ve consid­ered learn­ing SQL to expand your skill set, then you’ve reached the right spot. Meant for regular, non-tech­ni­cal users, this post will guide you through 5 steps to build your skills and help you use “Real SQL” to prepare your data prop­erly.

Ready? Let’s dive right in.

Step 1: Ensure you understand basic SQL concepts.

SQL stands for “Struc­tured Query Language” and it’s simply a way of commu­ni­cat­ing with your data­base. To use it prop­erly, there are a few concepts you’ll need to under­stand. We’re not talking about the syntax; that comes after. At this point, we need to under­stand what SQL is and how it behaves. 

I’ll give a few quick expla­na­tions here, but there are many online resources which can help you gain famil­iar­ity in this area e.g. Tuto­ri­al­s­Point, W3Schools, Codea­cad­emy.

Data in a data­base is orga­nized into tables, rows, and columns. “Queries” are the state­ments that ask the data­base to find specific piece(s) of data.

Let’s say you own a grocery store. Using the Consumer_Table below, you can find out infor­ma­tion such as: how many total customers you had on a given day, which day of the week is the most prof­itable, and so on.

 

If you want to find out infor­ma­tion which is located in both tables, you’ll need to join these two tables together. When joining tables, there needs to be a match key, also known as a join key. That is at least one column from each table which has a match in the corre­spond­ing table. (There are many types of joins — Make sure you under­stand what each join type accom­plishes. Keep an eye out for our next post to learn more about that! )

For example, to list the names of the fastest-selling foods, you’d need to do a left join where Consumer_Table is the left side and Food_Table is the right side. By joining on the Food_ID column in both tables, you have access to all the columns from both files, includ­ing the Food_Name column which we want to use.

In our Food_Table, the Food_ID column is called our primary key since it iden­ti­fies a unique row. It matches up to the Food_ID column in the consumer_table. Notice that the Food_ID column (in Consumer_Table) repeats values (ie. Food_ID 4 is listed twice). That’s because it is simply refer­enc­ing the full direc­tory of foods that exist within the Food_Table. Here’s an article that provides a detailed expla­na­tion of primary keys and refer­ence columns (a.k.a. foreign keys).

Once you under­stand the funda­men­tal concepts, it’s time to dip your toes into the “Real SQL” syntax.

Step 2: Take an existing “Fake SQL” transform or Excel function and convert it to SQL. Test the results and see if you get the same results with both methods.

Using the same example from before, let’s say we want to find out how many unique people made purchases on 8/22/2018.

In Excel, we can filter to the specific date and count the people. Clearly, there were 2 customers that day.

When using SQL, it’s the same idea. In English, this request can be stated as: “Get the count of unique people who made a purchase on 8/22/2018”. Trans­lat­ing that into SQL, “Get” becomes “Select”, “Count of Unique People” becomes “Count(distinct people)”, and “who made a purchase on 8/22/2018” becomes “where date_purchased = 2018-08-22′”. Put it together and you have your very own SQL state­ment:

SELECT count(distinct consumer_name) WHERE date_purchased = ‘2018−08−22’

After running that query, we can vali­date that it worked by check­ing that the result is still 2.

If you’ve used “Fake SQL” tools, prac­tice convert­ing your trans­for­ma­tions into SQL. I recom­mend spend­ing the most time on this step since this gets you accus­tomed to the core syntax of SQL actions.

Quick note: There are many flavors of SQL, includ­ing MySQL, Redshift SQL, and SQL Server, among others. These “flavors” are all based on the same stan­dard SQL syntax, but there are syntax and func­tion differ­ences. Unless spec­i­fied, the exam­ples here apply to stan­dard SQL syntax, but make sure that you’re prac­tic­ing with the SQL flavor that you want to use.

Once you’re comfort­able with this part, you’re ready to move into slightly more advanced stuff:

Step 3: Gain familiarity with the functions that weren’t available in your “Fake SQL” tool.

Switch­ing to “Real SQL” gives you access to a lot more SQL func­tions. For instance, MySQL (and many of the other SQL flavors as well) provides the LEFT() func­tion. The request to the data­base is fairly straight­for­ward — Take a string and pull out a certain number of char­ac­ters, start­ing from the left.

Let’s say our column is named “customer” and the first row has a value “Joe Smith”. Our query would look like: SELECT LEFT(customer, 2) FROM table_name , and the result would be “Jo”. Simple enough, no?

This example is just the tip of the iceberg: There are quite a lot of func­tions avail­able. Once you find func­tions that you want to use, research them and ensure you under­stand how they work. When it comes to this step, Google (or your favorite search engine) is your best friend!

Quick Note: In general, don’t worry about memo­riz­ing SQL syntax. It’s more worth­while to focus on the concepts and imple­men­ta­tion of SQL since you usually can find the exact func­tion syntax with a quick Google search.

Step 4: Challenge yourself to implement one of those functions.

Let’s take another example. Perhaps you’re in charge of manag­ing pricing for a grocery store (wouldn’t that be nice? 🙂 and you’re trying to list all food items where the price is between $1.50 and $2.50.

We can use the same table as before:

Assum­ing our “Price” column is saved as a decimal, we can use the MySQL “BETWEEN” func­tion to limit the data to a specific numeric range. In English, we’re asking: Please get all the data where “Price” is greater than or equal to 1.50 and less than or equal to 2.50.

In SQL, we say: SELECT * FROM food_table WHERE Price BETWEEN 1.50 AND 2.50;

The star (*) is the SQL code word for “get all the data”. It’ll retrieve all avail­able columns instead of asking to explic­itly list which columns of data you care about.

Not too hard, right?

And finally… the last step to becom­ing a profi­cient SQL user:

Step 5: Repeat steps 3 and 4 until you’re 100% comfortable with “Real SQL

 I know… I can hear your groan all the way from my computer. Yep, Step 5 is cliche: prac­tice, prac­tice, prac­tice. But for better or for worse, it’s true!

Imple­ment­ing SQL and learn­ing new func­tions requires a decent amount of trial and error. Don’t get concerned if you hit up against errors or even the wrong result at times; The more you do it, the more you learn and the easier it gets!

Be sure to grab hold of resources along the way to help you out — whether that’s a website, article, colleague, friend, or consul­tant.

To summa­rize: You might’ve caught the trend here. My “5 steps” are really just two basic iter­a­tion steps: learn the concepts, try it out in prac­ti­cal terms, repeat!

SQL is fabu­lous. It’s easy enough for non-tech­ni­cal folks and yet it’s power­ful enough for extremely advanced data manip­u­la­tion. As you know, data is only valu­able if it’s prepared prop­erly and is accu­rate, which is exactly what you’re learn­ing how to do!

At Search Discovery, we believe data holds the keys to under­stand­ing your busi­ness and its poten­tial growth. This is the first post in a SQL series intended to help you move your data in the right direc­tion.

I’d love to hear your feed­back and comments on this post. If you need a hand with SQL manip­u­la­tions or are inter­ested in further expla­na­tions for how data can be valu­able for your busi­ness, please reach out to me person­ally at shira.shkarofsky@searchdiscovery.com or click here and our expert team of Data and Cloud Engi­neer­ing consul­tants would be happy to help!

Ready to get started?
Reach out to learn more about how we can help.

I consent to having Search Discovery use the provided infor­ma­tion for direct market­ing purposes includ­ing contact by phone, email, SMS, or other elec­tronic means.