A good Introductory SQL Guide: How to Write Easy Queries

Posted on Posted in Blog

Ever heard associated with SQL? You may have heard of it in the context of data evaluation, but never believed it would apply to you as a marketer. Or, you may have thought, “That’s for the advanced data users. I could by no means do that. ”

Well, you could not be more wrong! Probably the most successful marketers are data-driven, and one of the very most important parts of getting data-driven is collecting data from databases quickly. SQL is the most popular tool around for doing exactly that.

If your firm already stores information in a database, you may need to learn SQL to get into the data. But not to worry — you’re within the right place to get started. Take a look at jump right in.

Download 10 Excel Templates for Marketers [Free Kit]

Why Use SQL?

SQL (often pronounced like “sequel”) stands for Structured Problem Language, and it’s used when companies have a ton of information that they want to manipulate. The beauty of SQL is the fact that anyone working at a company that stores data in a relational database can use this. (And chances are, yours does. )

For example , if you work for a software company and want to pull usage data on your customers, you can do that with SQL. If you’re helping develop a website for an e-commerce company that has information about customer purchases, you can use SQL to discover which customers are usually purchasing which items. Of course , these are just some of many possible programs.

Think about it this way: Have you ever opened a very large data set in Excel, only for your personal computer to freeze as well as shut down? SQL enables you to access only specific parts of your data at a time so you don’t have to down load all the data right into a CSV, manipulate this, and possibly overload Exceed. In other words, SQL protects the data analysis that you might be used to performing in Excel.

How to Write Simple SQL Queries

Before we begin, make sure you have a database software management application that will allow you to definitely pull data from your database. Some options include MySQL or Sequel Pro.

Start by downloading one such options, then talk to your company’s IT division about how to connect for your database. The option you choose will depend on your product’s back end, so seek advice from your product group to make sure you select the correct one.

Understand the hierarchy of your database

Next, you have to become accustomed to your database and its structure. If you have multiple directories of data, you’ll need to hone in at the location of the information you want to work with.

For example , let’s pretend we’re working with multiple databases about people in the United States. Enter the query “SHOW DATABASES; ”. The results may display that you have a couple of databases for different locations, including one for New Britain.

Within your database, you’ll have different tables containing the data you would like to work with. Using the same example above, let’s say we want to find out which information is found in one of the databases. If we use the query “SHOW TABLES in NewEngland; ”, we’ll discover that we have tables for every state in Brand new England: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland, and people_vermont.

Finally, you need to discover which fields are usually in the tables. Fields are the specific pieces of data that you can pull from your database. For instance , if you want to pull someone’s address, the field title may not just be “address” — it may be separated into address_city, address_state, address_zip. In order to shape this out, make use of the query “Describe people_massachusetts; ”. This provides a listing of all of the data that you could pull using SQL.

Let’s perform a quick review of the particular hierarchy using the New England example:

  • Our data source is: NewEngland.
  • Our tables inside that database are: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland, plus people_vermont.
  • Our fields within the people_massachusetts table include: address_city, address_state, address_zip, hair_color, age, first_name, and last_name.

Right now, let’s write a few simple SQL questions to pull data from our NewEngland database.

Basic SQL Queries

To learn the way to write a SQL query, let’s make use of the following example:

Who are the individuals who have red locks in Massachusetts plus were born in 2003 organized within alphabetical order?

SELECT

SELECT chooses the particular fields that you want shown in your chart. This is actually the specific piece of details that you want to pull from the database. In the example above, we want to get the people who fit the rest of the criteria.

Here is our SQL query:

SELECT

      first_name,

      last_name

;

FROM

THROUGH pinpoints the table that you want to pull the data from. In the earlier section, we learned that there have been six tables for each of the six states in New England: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland, plus people_vermont. Because we are going to looking for people within Massachusetts specifically, we’ll pull data through that specific desk.

Here is our SQL query:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

;

WHERE

WHERE allows you to filter a problem to be more specific. Within our example, we want to filtration system our query to incorporate only people with red-colored hair who were born in 2003. Let’s start with the red hair filter.

Here is our SQL query:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

WHERE

      hair_color = ‘red’

;

hair_color could have been part of your initial SELECT statement if you’d wanted to look at all of the people in Massachusetts with their hair color. In case you want to filter to see only individuals with red hair, that can be done so with a EXACTLY WHERE statement.

IN BETWEEN

Besides equates to (=), BETWEEN can be another operator you can use just for conditional queries. A BETWEEN statement holds true for values that fall between the specific minimum and optimum values.

In our case, we can make use of BETWEEN to pull records from a specific calendar year, like 2003. Here’s the query:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

WHERE

      birth_date AMONG ‘2003-01-01’ AND ‘2003-12-31’

;

AND

AND enables you to add additional criteria to your WHERE statement. Remember, we want to filter by people who had red hair in addition to people who were born in 2003. Since our WHERE declaration is taken up from the red hair requirements, how can we filtration system by a specific calendar year of birth too?

That’s where the AND statement comes in. In this case, the AND statement is a date property — but it doesn’t necessarily have to be. (Note: Check the format of the dates with your product team to make sure these are in the correct file format. )

The following is our SQL issue:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

WHERE

      hair_color = ‘red’

PLUS

      birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’

;

OR

OR can also be used with a WHERE statement. Along with AND, both conditions must be true to come in results (e. g., hair color should be red and must be born in 2003). With OR, either condition must be true to appear in results (e. g., hair color must be red or must be delivered in 2003).

Here’s what a good OR statement appears to be in action:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

WHERE

      hair_color = ‘red’

OR

      birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’

;

NOT REALLY

NOT can be used in a WHERE declaration to display values in which the specified condition can be untrue. If we wished to pull up all Ma residents without red hair, we are able to use the following problem:

CHOOSE

      first_name,

      last_name

THROUGH

      people_massachusetts

WHERE NOT

      hair_color = ‘red’

;

PURCHASE BY

Computations and organization can also be done within a problem. That’s where the PURCHASE BY and TEAM BY functions come in. First, we’ll take a look at our SQL concerns with the ORDER BY and then GROUP SIMPLY BY functions. Then, we’ll take a brief consider the difference between the 2.

An ORDER BY clause enables you to sort by any of the fields that you have specific in the SELECT declaration. In this case, let’s purchase by last name.

Here is our SQL query:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

EXACTLY WHERE

      hair_color = ‘red’

AND

      birth_date AMONG ‘2003-01-01’ AND ‘2003-12-31’

ORDER BY

      last_name

;

TEAM BY

TEAM BY is similar to ORDER BY, but aggregates data that has similarities. For example , if you have any kind of duplicates in your data, you can use GROUP SIMPLY BY to count the number of duplicates in your fields.

Here is your own SQL query:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

WHERE

      hair_color = ‘red’

AND

      birth_date BETWEEN ‘2003-01-01’ PLUS ‘2003-12-31’

GROUP BY

      last_name

;

ORDER BY VS . GROUP BY

To show the difference in between an ORDER SIMPLY BY statement and a TEAM BY statement, take a look at step outside the Massachusetts example quickly to look at a very simple dataset. Below is a list of four employees’ IDENTIFICATION numbers and brands.

a table of four names and IDs as a result of sql queries

If we were to use an ORDER BY declaration on this list, the names of the employees can have sorted in alphabetical order. The result would certainly look like this:

a table of four names and IDs as a result of sql queries with the name Peter appearing twice at the bottom

Whenever we were to use a GROUP BY statement instead, the employees will be counted based on the quantity of times they appeared in the initial table. Note that Peter made an appearance twice in the initial table, so the outcome would look like this:

sql query examples: a table of three names and IDs

With me so far? Okay, let’s return to the SQL query we have been creating about red-haired people in Massachusetts who were born in 2003.

RESTRICT

Depending on the quantity of data you have in your database, it may take quite a long time to run your queries. This can be frustrating, especially if you’ve made a mistake in your query and today need to wait before continuing. If you want to test a query, the LIMIT function allows you to limit the number of outcomes you get.

For instance , if we suspect there are thousands of people who have reddish hair in Massachusetts, we may want to try out our query using LIMIT before we run it in full to make sure we’re obtaining the information we want. Let’s say, for instance, we just want to see the first 100 people in our outcome.

Here is our own SQL query:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

WHERE

      hair_color = ‘red’

AND

      birth_date BETWEEN ‘2003-01-01’ PLUS ‘2003-12-31’

ORDER SIMPLY BY

      last_name

LIMIT

      one hundred

;

PUT INTO

Along with retrieving information from the relational database, SQL can also be used to modify the contents of a database. Of course , you’ll require permissions to make adjustments to your company’s information. But , in case you’re ever in charge of controlling the contents of a database, we’ll talk about some queries you should know.

First may be the INSERT INTO statement, which is for placing new values into your database. If we want to include a new person towards the Massachusetts table, we are able to do so by first offering the name of the table we want to modify, and the areas within the table we would like to add to. Next, all of us write VALUE along with each respective value we want to add.

Here’s what that will query could seem like:

PUT INTO

  people_massachusetts (address_city, address_state, address_zip, hair_color, age, first_name, last_name)

VALUES

  (Cambridge, Massachusetts, 02139, blonde, 32, Anne, Doe)

;

Alternatively, if you are adding a value to each field in the desk, you don’t need to specify fields. The values will be added to columns in the purchase that they are listed in the query.

INSERT INTO

  people_massachusetts

VALUES

  (Cambridge, Massachusetts, 02139, blonde, 32, Anne, Doe)

;

If you only wish to add values to specific fields, you must specify these areas. Say we just want to insert a list with first_name, last_name, and address_state — we can use the subsequent query:

INSERT INTO

  people_massachusetts (first_name, last_name, address_state)

VALUES

  (Jane, Doe, Massachusetts)

;

UPGRADE

If you want to replace existing values inside your database with different values, you can use UPDATE. What happens if, for example , someone is certainly recorded in the data source as having red hair when they have brown hair? We are able to update this record with UPDATE plus WHERE statements:

UPDATE

  people_massachusetts

ESTABLISHED

  hair_color sama dengan ‘brown’

WHERE

  first_name = ‘Jane’

AND

  last_name = ‘Doe’

;

Or, say there’s a problem in your table where a few values for “address_state” appear as “Massachusetts” and others appear since “MA”. To change just about all instances of “MA” to “Massachusetts” we can utilize a simple query and update multiple information at once:

UPDATE

  people_massachusetts

SET

  address_state = ‘Massachusetts’

WHERE

    address_state = MOTHER

;

Be cautious when using UPDATE. If you don’t specify which records to change with a WHERE statement, you’ll change all values in the table.

REMOVE

DELETE gets rid of records from your desk. Like with UPDATE, make sure to include a WHERE declaration, so you don’t unintentionally delete your entire desk.

Or, whenever we happened to find several records in our people_massachusetts table who actually lived in Maine, we can delete these types of entries quickly by targeting the address_state field, like so:

REMOVE FROM

  people_massachusetts

WHERE

  address_state = ‘maine’

;

Bonus: Advanced SQL Tips

Now that you’ve discovered how to create a easy SQL query, let’s take a discuss some other techniques that you can use to take your queries up a notch, beginning with the asterisk.

* (asterisk)

When you add a good asterisk character for your SQL query, it tells the query that you want to include all the columns of data in your results.

In the Massachusetts illustration we’ve been using, coming from only had two column names: first_name and last_name. Yet let’s say we had fifteen columns of data that we want to see within our results — it could be a pain to type all 15 line names in the CHOOSE statement. Instead, if you replace the names of those columns with an asterisk, the query know to pull all of the content into the results.

Here’s what the SQL query would appear to be:

SELECT

      *

FROM

      people_massachusetts

WHERE

      hair_color = ‘red’

AND

      birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’

ORDER SIMPLY BY

      last_name

LIMIT

      100

;

percent (percent symbol)

The percent symbol is a wildcard personality, meaning it can stand for one or more characters within a database value. Wildcard characters are ideal for locating records that share common personas. They are typically used with the LIKE operator to locate a pattern in the information.

For instance, if we wanted to get the titles of every person within our table whose scoot code begins with “02”, we can create this query:

SELECT

      first_name,

      last_name

WHERE

  address_zip LIKE ‘02%’

;

Here, “%” stands in for any group of numbers that follow “02”, and this query turns up any kind of record with a worth for address_zip that will begins with “02”.

LAST 30 DAYS

Once I started using SQL regularly, I found that one of my first queries involved trying to find which people got an action or fulfilled a certain group of criteria within the last 30 days.

Let’s pretend today is December 1, 2021. You could create these parameters by causing the birth_date span between November 1, 2021 and Nov 30, 2021. That SQL query might look like this:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

WHERE

      hair_color = ‘red’

AND

      birth_date BETWEEN ‘2021-11-01’ PLUS ‘2021-11-30’

ORDER SIMPLY BY

      last_name

LIMIT

      100

;

But , that would require considering which dates protect the last 30 days, plus you’d have to revise this query constantly.

Instead, to make the dates automatically span the last 30 days no matter which day it is, you are able to type this under AND: birth_date > = (DATE_SUB(CURDATE(), PERIOD 30))

(Note: You’ll want to double-check this syntax with your product team because it varies based on the software you use to pull your SQL queries. )

Your full SQL query would consequently look like this:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

WHERE

      hair_color sama dengan ‘red’

AND

      birth_date > = (DATE_SUB(CURDATE(), INTERVAL 30))

PURCHASE BY

      last_name

LIMIT

      100

;

COUNT

In some cases, you may want to count the number of times that a qualifying criterion of a field seems. For example , let’s say you would like to count the number of occasions the different hair shades appear for the individuals you are tallying up from Massachusetts. In this case, COUNT will come in convenient so you don’t have to by hand add up the number of individuals who have different hair colours or export that will information to Stand out.

Here’s what that SQL query would look like:

SELECT

      hair_color,

      COUNT(hair_color)

FROM

      people_massachusetts

AND

      birth_date BETWEEN ‘2003-01-01’ AND ‘2003-12-31’

GROUP BY

      hair_color

;

AVG

AVG calculates the common of an attribute in the results of your issue, excluding NULL values (empty). In our example, we could use AVG to calculate the standard age of Massachusetts occupants in our query.

Here’s what the SQL query could look like:

SELECT

  AVG(age)

FROM

  people_massachusetts

;

AMOUNT

SUM is another simple calculation that you can do in SQL. This calculates the total associated with all attributes from the query. So , whenever we wanted to add up all of the ages of Ma residents, we can make use of this query:

SELECT

  SUM(age)

FROM

  people_massachusetts

;

MINUTES and MAX

MIN and MAXIMUM are two SQL functions that give you the smallest and biggest values of a given field. We can use it to identify the oldest and youngest associates of our Massachusetts table:

This problem will give us the record of the oldest:

CHOOSE

  MIN(age)

THROUGH

  people_massachusetts

;

And this problem gives us the oldest:

SELECT

  MAX(age)

FROM

  people_massachusetts

;

JOIN

There may be a period when you need to access info from two various tables in one SQL query. In SQL, you can use a SIGN UP FOR clause to do this.

(For those acquainted with  Excel formulations, this is similar to utilizing the VLOOKUP formula when you really need to combine information from two different linens in Excel. )

Let’s say we now have one table which has data of all Ma residents’ user IDs and their birthdates. In addition , we have an entirely separate table that contains all Massachusetts residents’ user IDs plus their hair color.

If we want to figure out the head of hair color of Massachusetts occupants born in the calendar year 2003, we’d need to access information from both tables plus combine them. This works because each tables share the matching column: consumer IDs.

Because we’re calling out fields from 2 different tables, our SELECT statement can also be going to change slightly. Instead of just listing out the fields we want to include in our results, we’ll need to identify which table could possibly be coming from. (Note: The particular asterisk function may come in handy here so your query consists of both tables in your results. )

To specify an area from a specific desk, all we have to perform is combine the name of the table with the name of the field. For example , our SELECT statement would say “table. field” — using the period separating the table name and the field name.

We’re also supposing a few things in this case:

  1. The Massachusetts birthdate table contains the following fields: first_name, last_name, user_id, birthdate
  2. The Massachusetts hair color desk includes the following fields: user_id, hair_color

Your SQL issue would therefore appear like:

CHOOSE

      birthdate_massachusetts. first_name,

      birthdate_massachusetts. last_name

THROUGH

      birthdate_massachusetts JOIN haircolor_massachusetts USING (user_id)

EXACTLY WHERE

      hair_color = ‘red’

AND

      birth_date IN BETWEEN ‘2003-01-01’ AND ‘2003-12-31’

ORDER BY

      last_name

;

This query would sign up for the two tables utilizing the field “user_id” which usually appears in both the birthdate_massachusetts table and the haircolor_massachusetts table. You’re then able to view a table of people given birth to in 2003 who may have red hair.

CASE

Use a CASE statement when you wish to return different results to your query depending on which condition is definitely met. Conditions are evaluated in order. Every condition is fulfilled, the corresponding result is returned and all following conditions are skipped over.

You can include an ELSE condition at the end in the event that no conditions are met. Without an ELSE, the query will return NULL in case no conditions are met.

Here’s an example of using SITUATION to return a thread based on the query:

SELECT

      first_name,

      last_name

FROM

      people_massachusetts

CASE

  WHENEVER hair_color = ‘brown’ THEN ‘This person has brown hair. ’

  WHEN hair_color = ‘blonde’ THEN ‘This person has blonde curly hair. ’

  WHENEVER hair_color = ‘red’ THEN ‘This person has red tresses. ’

  ELSE ‘Hair color not known. ’

END

;

Basic SQL Queries Marketers Should Know

Congratulations. most likely ready to run your own personal SQL queries! Whilst there’s a lot more you can do with SQL, I hope you found this particular overview of the basics useful so you can get your hands dirty. With a strong foundation of the basics, you’ll be able to navigate SQL much better and work towards some of the more complex examples.

Editor’s note: This post had been originally published within March 2015 and has been updated just for comprehensiveness.

excel marketing templates

Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *