SQL in action

As you might remember, earlier we touched on the query language SQL. I guess you can call this the SQL sequel. We'll try to make this one even better than the original. Remember, SQL can do lots of the same things with data that spreadsheets can do.

You can use it to store, organize and analyze your data, among other things. But like any good sequel, it's on a larger scale, bigger, more action-packed. Think of it as supersize spreadsheets. For example, you might want to consider a spreadsheet when you have a smaller dataset, such as one with just 100 rows. But if your data set seems to go on forever, and your spreadsheet is struggling to keep up, SQL would be the way to go. When you use SQL, you need a place where the SQL language is understood. If you've ever gone somewhere and not known the language, it can be challenging to communicate. You might think you're asking for one thing and get something completely different. Well, SQL knows the feeling. SQL needs a database that will understand its language.

Let's talk. There are a number of databases out there that use SQL. You may use several of them during your time as a data analyst. But here's the thing, no matter which database you use, SQL basically works for the same in each. For example, in SQL, queries are universal. We've talked about queries before, but it never hurts to have a refresher. A query is a request for data or information from a database. Here's the structure of a basic query.

You can see that with this query we can select specific data from a table by adding where we can filter the data based on certain conditions. Let's get started. We'll open our database and see how SQL can communicate with it
to do some simple data task. First, let's select our dataset. We'll use an asterisk to select all of the data from the table. With that simple query, the database calls up the table we need. Magic. Let's add Where to our query to show how that changes what data we get. You can see the data now only shows, movies that are in the action genre. That's it, a basic query in SQL. Pretty cool, huh? Soon you'll learn about building more complex queries. For now, though, we can celebrate learning about the structure of a basic SQL query, select, from, and where. As you continue the program, you have the opportunity to use SQL yourself. I hope this video was a useful sneak peek at
what's coming later.

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:

SELECT
FROM
WHERE

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:

SELECT 
	# columns you want to look at 
FROM 
	# table the data lives in 
WHERE 
	# certain condition is met

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:

SELECT 
	ColumnA, ColumnB, ColumnC 
FROM Table where the data lives 
WHERE Condition 1 AND condition 2 AND condition 3

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' 

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.

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 field /* this is the last name column */
FROM table --this is the customer data table
WHERE field1 LIKE 'Ch%'

Image of SQL statements with comments shown between /* and */ and after --
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.

--This is an important query used later to join with the accounts table. SELECT 
	rowkey, -- key used to join with account_id
    Info.date, -- date is in string format YYYY-MM-DD HH:MM:SS
    Info.code -- e.g., 'pub-###'
FROM Publishers

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%';

Each alias has a comment with --.

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'

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 Enployees
WHERE jobCode <> 'INT' AND salary <= 300000;

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.
  https://www.w3schools.com/sql/default.asp

• 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.
  https://towardsdatascience.com/sql-cheat-sheet-776f8e3189fa