Quite often I need to quickly write a query to pull basic information from a database or aggregate a bunch of time-based data to be able to count the number of times a contact has checked in on the CityGro App for iPad.  In such situations, it’s very useful to be able to quickly “tell the database” what I’m looking for and receive a (nearly) instant response.  I’ve done a thing or two with databases over the years and wanted to impart of some of my experience in writing a simple database query to extract simple or complex data from a database.

The ability to write SQL enables business owners and marketers to illustrate data in digestible graphs and charts. Taking an overwhelming chunk of numbers and sorting it into visuals can help make marketing decisions driven by data. This data can be compiled into a data visualization software like Tableau or Klipfolio to track performance and optimize based on irregularities or patterns found in the data.

A SQL (short for “Structured Query Language” if you even care) query is a standardized way of accessing data from a database. Suffice it to say, in the olden days, there were people who built databases and agreed on a common way to request data from databases.  It wasn’t exactly a meeting where they shook hands, as with many standards in computers it’s often “right place/right time” (such as Microsoft) or in other cases “survival of the fittest/most convenient” (similar to roads in New England) that cause widespread adoption of patterns such as a “Structured Query Language”.  Enough of that… if you really want to read more, feel free.

There are usually four main types of SQL queries: SELECT, INSERT, UPDATE and DELETE. In short, one would use SELECT to read from the database. INSERT/UPDATE are for creating new records and updating existing ones. DELETE is used for removing records. In this article, I’m going to focus on SELECT. If you’re looking for information on INSERT, UPDATE, or DELETE, you’ve already surpassed the content in this post and I commend you for your accomplishments.

If you want to retrieve data from within the database a SELECT query will need to contain at least two items: the columns that you want to retrieve and from what table you want to retrieve them. Think of your database as a collection of Excel spreadsheets (this is a rudimentary example but works well for many cases).  A table is the equivalent of a spreadsheet. Each spreadsheet has columns and rows — simple as that.

Also, keep in mind that while SQL is *mostly* standardized across different databases, occasionally there is something that doesn’t carry over completely. Everything in this article should work on most mainstream relational databases including MySQL, Postgres, and MSSQL among others.

The first part of the query is the SELECT statement which contains all of the columns you are looking to retrieve. You can get a list of columns by typing “DESCRIBE __ your table name here__”.  Here are some examples of such statements and what they do:

• SELECT first_name – select only the first_name column
• SELECT first_name, email – select both first_name and email columns
• SELECT first_name as name, email – same as above except it returns first_name as “name” instead of “first_name”
• SELECT * – select all columns in the table

The second part of the query is the FROM statement, which is where you specify what table to pull from. Generally, databases have multiple tables and you have likely already decided which table you’re looking at since columns are specific to tables. Keep in mind that you cannot query a column that doesn’t exist in a table and attempting to do so will give you an error.  The FROM statement is generally very simple as follows:

• FROM users
• FROM widgets

Putting it all together now…

SELECT first_name, email FROM users

At this point, you should now see a list of users in your database. SQL actually has simplicity in its genetics (though it sometimes seems otherwise with some queries I’ve seen). All other parts of a query are optional but are really what make SQL powerful. One such optional but powerful feature are what’s called a “WHERE clause,” which allows filtering data using AND/OR conjunctions. To build a where clause you simply specify a column name, a method of comparison (such as =, >, or <) and a value.  It only gets more complex from here, so this where practice comes in handy. Here are some examples:

• SELECT * FROM users WHERE first_name = ‘Ben’ – only return users who are named Ben (case sensitive)
• SELECT * FROM users WHERE age > 18 – only return users who are older than 18 years
• SELECT * FROM users WHERE first_name = ‘Ben’ AND age > 18 – Anyone older than 18 who is named Ben — you’re getting the idea here.
• SELECT * FROM users WHERE first_name = ‘Ben’ OR age > 18 – You can also use OR instead of AND — return anyone named Ben or older than 18.

At this point, you should be able to look for things in your database somewhat easily.  This covers columns that are Text (char/varchar) and Numbers but doesn’t take into account more complex data types such as Dates, Timestamps, Geo Coordinates and others that may be more specific to your database.  If you’re looking for help with these, Google will be your friend.

You’re on your way to writing impressively complex SQL queries that might just have the power to change the world. Cheers to you.