SQL Guide: Getting started

Just as humans use different languages to communicate with others, so do computers. Structured Query Language (or SQL, often pronounced “sequel”) enables data analysts to talk to their databases. SQL is one of the most useful data analyst tools, especially when working with large datasets in tables. It can help you investigate huge databases, track down text (referred to as strings) and numbers, and filter for the exact kind of data you need—much faster than a spreadsheet can.

If you haven’t used SQL before, this reading will help you learn the basics so you can appreciate how useful SQL is and how useful SQL queries are in particular. You will be writing SQL queries in no time at all.

What is a query?

A query is a request for data or information from a database. When you query databases, you use SQL to communicate your question or request. You and the database can always exchange information as long as you speak the same language.

Every programming language, including SQL, follows a unique set of guidelines known as syntax. Syntax is the predetermined structure of a language that includes all required words, symbols, and punctuation, as well as their proper placement. As soon as you enter your search criteria using the correct syntax, the query starts working to pull the data you’ve requested from the target database.

The syntax of every SQL query is the same:

• Use SELECT to choose the columns you want to return.
• Use FROM to choose the tables where the columns you want are located.
• Use WHERE to filter for certain information.

A SQL query is like filling in a template. You will find that if you are writing a SQL query from scratch, it is helpful to start a query by writing the SELECT, FROM, and WHERE keywords in the following format:

Next, enter the table name after the FROM; the table columns you want after the SELECT; and, finally, the conditions you want to place on your query after the WHERE. Make sure to add a new line and indent when adding these, as shown below:

Following this method each time makes it easier to write SQL queries. It can also help you make fewer syntax errors.

Example of a query

Here is how a simple query would appear in BigQuery, a data warehouse on the Google Cloud Platform.

SELECT
       first_name
FROM
       customer_data.customer_name
WHERE
       first_name = 'Tony'

The above query uses three commands to locate customers with the first name Tony:

1. SELECT the column named first_name
2. FROM a table named customer_name (in a dataset named customer_data) (The dataset name is always followed by a dot, and then the table name.)
3. But only return the data WHERE the first_name is Tony

The results from the query might be similar to the following:

first_name
Tony
Tony
Tony

As you can conclude, this query had the correct syntax, but wasn't very useful after the data was returned.

Multiple columns in a query

In real life, you will need to work with more data beyond customers named Tony. Multiple columns that are chosen by the same SELECT command can be indented and grouped together.

If you are requesting multiple data fields from a table, you need to include these columns in your SELECT command. Each column is separated by a comma as shown below:

Here is an example of how it would appear in BigQuery:

SELECT
       customer_id,
       first_name,
       last_name
FROM
       customer_data.customer_name
WHERE
       first_name = 'Tony'

The above query uses three commands to locate customers with the first name Tony.

1. SELECT the columns named customer_id, first_name, and last_name
2. FROM a table named customer_name (in a dataset named customer_data) (The dataset name is always followed by a dot, and then the table name.)
3. But only return the data WHERE the first_name is Tony

The only difference between this query and the previous one is that more data columns are selected. The previous query selected first_name only while this query selects customer_id and last_name in addition to first_name. In general, it is a more efficient use of resources to select only the columns that you need. For example, it makes sense to select more columns if you will actually use the additional fields in your WHERE clause. If you have multiple conditions in your WHERE clause, they may be written like this:

Notice that unlike the SELECT command that uses a comma to separate fields/variables/parameters, the WHERE command uses the AND statement to connect conditions. As you become a more advanced writer of queries, you will make use of other connectors/operators such as OR and NOT.

Here is a BigQuery example with multiple fields used in a WHERE clause:

SELECT
	customer_id,
	first_name,
	last_name
FROM
	customer_data.customer_name
WHERE
	customer_id > 0
	AND first_name = 'Tony'
	AND last_name = 'Magnolia'

The above query uses three commands to locate customers with a valid (greater than 0) customer ID whose first name is Tony and last name is Magnolia.

1. SELECT the columns named customer_id, first_name, and last_name
2. FROM a table named customer_name (in a dataset named customer_data) (The dataset name is always followed by a dot, and then the table name.)
3. But only return the data WHERE customer_id is greater than 0, first_name is Tony, and last_name is Magnolia.

Note that one of the conditions is a logical condition that checks to see if customer_id is greater than zero.

If only one customer is named Tony Magnolia, the results from the query could be:

customer_id	first_name	last_name
1967	Tony	Magnolia

If more than one customer has the same name, the results from the query could be:

customer_id	first_name	last_name
1967	Tony	Magnolia
7689	Tony	Magnolia

Key takeaway

The most important thing to remember is how to use SELECT, FROM, and WHERE in a query. Queries with multiple fields will become simpler after you practice writing your own SQL queries later in the program.

Endless SQL possibilities

You have learned that a SQL query uses SELECT, FROM, and WHERE to specify the data to be returned from the query. This reading provides more detailed information about formatting queries, using WHERE conditions, selecting all columns in a table, adding comments, and using aliases. All of these make it easier for you to understand (and write) queries to put SQL in action. The last section of this reading provides an example of what a data analyst would do to pull employee data for a project.

Capitalization, indentation, and semicolons

You can write your SQL queries in all lowercase and don’t have to worry about extra spaces between words. However, using capitalization and indentation can help you read the information more easily. Keep your queries neat, and they will be easier to review or troubleshoot if you need to check them later on.

SELECT
	field1
FROM
	table
WHERE
	field1 = condition;

Notice that the SQL statement shown above has a semicolon at the end. The semicolon is a statement terminator and is part of the American National Standards Institute (ANSI) SQL-92 standard, which is a recommended common syntax for adoption by all SQL databases. However, not all SQL databases have adopted or enforce the semicolon, so it’s possible you may come across some SQL statements that aren’t terminated with a semicolon. If a statement works without a semicolon, it’s fine.

WHERE conditions

In the query shown above, the SELECT clause identifies the column you want to pull data from by name, field1, and the FROM clause identifies the table where the column is located by name, table. Finally, the WHERE clause narrows your query so that the database returns only the data with an exact value match or the data that matches a certain condition that you want to satisfy.

For example, if you are looking for a specific customer with the last name Chavez, the WHERE clause would be:

WHERE field1 = 'Chavez'

However, if you are looking for all customers with a last name that begins with the letters “Ch," the WHERE clause would be:

WHERE field1 LIKE 'Ch%'

You can conclude that the LIKE clause is very powerful because it allows you to tell the database to look for a certain pattern! The percent sign (%) is used as a wildcard to match one or more characters. In the example above, both Chavez and Chen would be returned. Note that in some databases an asterisk (*) is used as the wildcard instead of a percent sign (%).

SELECT all columns

Can you use SELECT * ?

In the example, if you replace SELECT field1 with SELECT * , you would be selecting all of the columns in the table instead of the field1 column only. From a syntax point of view, it is a correct SQL statement, but you should use the asterisk (*) sparingly and with caution. Depending on how many columns a table has, you could be selecting a tremendous amount of data. Selecting too much data can cause a query to run slowly.

Comments

Some tables aren’t designed with descriptive enough naming conventions. In the example, field1 was the column for a customer’s last name, but you wouldn’t know it by the name. A better name would have been something such as last_name. In these cases, you can place comments alongside your SQL to help you remember what the name represents. Comments are text placed between certain characters, /* and */, or after two dashes (--) as shown below.

SELECT
	field1 /* this is the last name column */
FROM
	table -- this is the customer data table
WHERE
	field1 LIKE 'Ch%';

Comments can also be added outside of a statement as well as within a statement. You can use this flexibility to provide an overall description of what you are going to do, step-by-step notes about how you achieve it, and why you set different parameters/conditions.

The more comfortable you get with SQL, the easier it will be to read and understand queries at a glance. Still, it never hurts to have comments in a query to remind yourself of what you’re trying to do. This also makes it easier for others to understand your query if your query is shared. As your queries become more and more complex, this practice will save you a lot of time and energy to understand complex queries you wrote months or years ago.

Example of a query with comments

Here is an example of how comments could be written in BigQuery:

-- Pull basic information from the customer table
SELECT
	customer_id, --main ID used to join with customer_address
	first_name, --customer's first name from loyalty program
	last_name --customer's last name
FROM
	customer_data.customer_name

In the above example, a comment has been added before the SQL statement to explain what the query does. Additionally, a comment has been added next to each of the column names to describe the column and its use. Two dashes (--) are generally supported. So it is best to use -- and be consistent with it. You can use # in place of -- in the above query, but # is not recognized in all SQL versions; for example, MySQL doesn’t recognize #. You can also place comments between /* and */ if the database you are using supports it.

As you develop your skills professionally, depending on the SQL database you use, you can pick the appropriate comment delimiting symbols you prefer and stick with those as a consistent style. As your queries become more and more complex, the practice of adding helpful comments will save you a lot of time and energy to understand queries that you may have written months or years prior.

Aliases

You can also make it easier on yourself by assigning a new name or alias to the column or table names to make them easier to work with (and avoid the need for comments). This is done with a SQL AS clause. In the example below, the alias last_name has been assigned to field1 and the alias customers assigned to table. These aliases are good for the duration of the query only. An alias doesn’t change the actual name of a column or table in the database.

Example of a query with aliases

field1 AS last_name -- Alias to make my work easier
table AS customers -- Alias to make my work easier

SELECT
	last_name
FROM
	customers
WHERE
	last_name LIKE 'Ch%';

Putting SQL to work as a data analyst

Imagine you are a data analyst for a small business and your manager asks you for some employee data. You decide to write a query with SQL to get what you need from the database.

You want to pull all the columns: empID, firstName, lastName, jobCode, and salary. Because you know the database isn’t that big, instead of entering each column name in the SELECT clause, you use SELECT *. This will select all the columns from the Employee table in the FROM clause.

SELECT
      *
FROM
      Employee

Now, you can get more specific about the data you want from the Employee table. If you want all the data about employees working in the SFI job code, you can use a WHERE clause to filter out the data based on this additional requirement.

Here, you use:

SELECT
	*
FROM
	Employee
WHERE
	jobCode = 'SFI'

A portion of the resulting data returned from the SQL query might look like this:

empID	firstName	lastname	jobCode	salary
0002	Homer	Simpson	SFI	15000
0003	Marge	Simpson	SFI	30000
0034	Bart	Simpson	SFI	25000
0067	Lisa	Simpson	SFI	38000
0088	Ned	Flanders	SFI	42000
0076	Barney	Gumble	SFI	32000

Suppose you notice a large salary range for the SFI job code. You might like to flag all employees in all departments with lower salaries for your manager. Because interns are also included in the table and they have salaries less than $30,000, you want to make sure your results give you only the full time employees with salaries that are $30,000 or less. In other words, you want to exclude interns with the INT job code who also earn less than $30,000. The AND clause enables you to test for both conditions.

You create a SQL query similar to below, where <> means "does not equal":

SELECT
	*
FROM
	Employee
WHERE
	jobCode <> 'INT'
	AND salary <= 30000;

The resulting data from the SQL query might look like the following (interns with the job code INT aren't returned):

empID	firstName	lastname	jobCode	salary
0002	Homer	Simpson	SFI	15000
0003	Marge	Simpson	SFI	30000
0034	Bart	Simpson	SFI	25000
0108	Edna	Krabappel	TUL	18000
0099	Moe	Szyslak	ANA	18000

With quick access to this kind of data using SQL, you can provide your manager with tons of different insights about employee data, including whether employee salaries across the business are equitable. Fortunately, the query shows only an additional two employees might need a salary adjustment and you share the results with your manager.

Pulling the data, analyzing it, and implementing a solution might ultimately help improve employee satisfaction and loyalty. That makes SQL a pretty powerful tool.

Resources to learn more

Nonsubscribers may access these resources for free, but if a site limits the number of free articles per month and you already reached your limit, bookmark the resource and come back to it later.

• W3Schools SQL Tutorial: If you would like to explore a detailed tutorial of SQL, this is the perfect place to start. This tutorial includes interactive examples you can edit, test, and recreate. Use it as a reference or complete the whole tutorial to practice using SQL. Click the green Start learning SQL now button or the Next button to begin the tutorial.

• SQL Cheat Sheet: For more advanced learners, go through this article for standard SQL syntax used in PostgreSQL. By the time you are finished, you will know a lot more about SQL and will be prepared to use it for business analysis and other tasks.

Practical Quiz: Test your knowledge on SQL

Question 1

SELECT *
FROM employee
WHERE jobCode = 'FTE'
	AND LastName = 'James'

What does the asterisk (*) after SELECT tell the database to do in this query?

A. Select all data that meets the criteria as stated in the query, then multiply it

B. Select the LastName column from the employee table

C. Select all data that meets the criteria as stated in the query

D. Select all columns from the employee table

The correct answer is D. Select all columns from the employee table. Explain: SELECT * tells the database to select all columns from the employee table. The criteria in the WHERE clause tells the database what data in those columns the query should return.

Question 2

SELECT *
FROM employee
WHERE jobCode = 'FTE'
	AND LastName = 'James'

In this query, the data analyst wants to retrieve data from which table?

A. employee

B. LastName

C. James

D. jobCode

The correct answer is A. employee. Explain: The data analyst wants to retrieve data from the employee table.

Question 3

SELECT *
FROM employee
WHERE jobCode = 'FTE'
	AND LastName = 'James'

In this query, what will be retrieved from the database?

A. All data from the employee table, where the jobCode is FTE and the employee has any last name other than James.

B. All data from the jobCode table, where the jobCode is FTE and the employee has any last name other than James.

C. All data from the employee table, where the jobCode is FTE and the last name is James.

D. All data from the FTE table, where the employee's LastName is James.

The correct answer is C. All data from the employee table, where the jobCode is FTE and the last name is James. Explain: This query will select all data from the employee table, where the jobCode is FTE and the last name is James.

Question 4

You are working with a database table that contains data about music artists. The table is named artist. You want to review all the columns in the table.

You write the SQL query below. Add a FROM clause that will retrieve the data from the artist table.

SELECT * FROM artist;

Output:

+-----------+---------------------------------+
| artist_id | name                            |
+-----------+---------------------------------+
|         1 | AC/DC                           |
|         2 | Accept                          |
|         3 | Aerosmith                       |
|         4 | Alanis Morissette               |
|         5 | Alice In Chains                 |
|         6 | Antônio Carlos Jobim            |
|         7 | Apocalyptica                    |
|         8 | Audioslave                      |
|         9 | BackBeat                        |
|        10 | Billy Cobham                    |
|        11 | Black Label Society             |
|        12 | Black Sabbath                   |
|        13 | Body Count                      |
|        14 | Bruce Dickinson                 |
|        15 | Buddy Guy                       |
|        16 | Caetano Veloso                  |
|        17 | Chico Buarque                   |
|        18 | Chico Science & Nação Zumbi     |
|        19 | Cidade Negra                    |
|        20 | Cláudio Zoli                    |
|        21 | Various Artists                 |
|        22 | Led Zeppelin                    |
|        23 | Frank Zappa & Captain Beefheart |
|        24 | Marcos Valle                    |
|        25 | Milton Nascimento & Bebeto      |
+-----------+---------------------------------+
(Output limit exceeded, 25 of 275 total rows shown)

How many columns are in the artist table?

A. 5

B. 9

C. 8

D. 2

There are 2 columns in the artist table. Explain: The clause FROM artist will retrieve the data from the artist table. The complete query is SELECT * FROM artist. The FROM clause specifies which database table to select data from. There are two columns in the artist table.

Question 5

You are working with a database table that contains data about music albums. You are only interested in data related to the album with ID number 277. The album IDs are listed in the album_id column from the album table.

You write the SQL query below. Add a WHERE clause that will return only data about the album with ID number 277.

SELECT
*
FROM
album
WHERE album_id = 277;

Output:

+----------+---------------------------+-----------+
| album_id | title                     | artist_id |
+----------+---------------------------+-----------+
|      277 | Bach: Goldberg Variations |       211 |
+----------+---------------------------+-----------+

What is the name of the album with ID number 277?

A. Vivaldi: The Four Seasons

B. Beethoven: Piano Sonatas

C. Mozart: Chamber Music

D. Bach: Goldberg Variations

The name of the album with ID number 277 is Bach: Goldberg Variations. Explain: The clause WHERE album_id = 277 will return only data about the album with ID number 277. The complete query is SELECT * FROM album WHERE album_id = 277. The WHERE clause filters results that meet certain conditions. The WHERE clause includes the name of the column, an equals sign, and the value(s) in the column to include. The name of the album with ID number 277 is Bach: Goldberg Variations.