Data & Analytics

|

September 9, 2020

SQL Basics: Introduction to Queries

Close-up on an Apple laptop with its keyboard lit-up. On screen, laptop has multiple lines of colorful code.
SECTIONS
  1. How are SQL Queries structured?
  2. Using the SELECT statement
  3. Using the FROM statement
  4. Using the WHERE clause
  5. Using the GROUP BY clause
  6. Practice tools & resources

Structured Query Language (SQL) is the language of relational databases.  If you haven’t already, be sure to check out our article introducing relational databases. The lines of code that speak to a relational database system are called queries. In this article, we’re going to cover the structure of basic SQL queries so we can confidently build upon them later on.

SQL is used to extract or “pull” data from a relational database. This is achieved with instructions (or words) that specifies from where and how the data is to be selected. 

SQL query that reads "SELECT name, department, email FROM employee;"
Some instructions in the query are in upper case to create a clear design and readable code. If you’re just starting out with SQL, you don’t have to worry about upper case and lower case. Just know that there are formatting standards that make SQL easier to read and write!

Queries have a certain structure that needs to be followed in order to make the query work. They can be very short and simple, with a few key elements, or extremely lengthy and detailed.  SQL offers almost limitless flexibility when it comes down to how we want to view our data. One key limitation is an inability to create data visualizations. When it comes to visualizing data, the Python programming language is dominant. For this reason, Data Science is usually performed using Python.

person looking down at a python textbook
Pop open a Python textbook if you want to learn how to best visualize data!

SQL Query Structure

SQL queries are used to generate reports that make use of data in relational databases. They extract information from specific places in tables with specific conditions and present them in a meaningful way. Without SQL queries, relational databases would just be heaps of data with no meaningful insight. As a typical relational database can easily consist of a few million rows, learning to harness the power of SQL will enable you to pull, analyze, and draw insights from data.

Queries typically consist of the following statements: SELECT, FROM, WHERE, and GROUP BY. In between these statements are columns, tables, and conditions which tell the computer the pieces of data to extract from which tables under what conditions. At its core, a SQL query is that simple. Let’s now break down a SQL query into its components, starting with SELECT.

SELECT

Most queries start with the SELECT statement. Following SELECT, enter the names of the columns that we want to use in our reports. For each column we want, we enter their names separated by commas. If we want to use all the columns, we can simply type SELECT *. The asterisk is a short-hand way of selecting all the columns without having to type them manually. If your table consists of 25 columns, the asterisk can save a lot of time.

SELECT * statement with the corresponding columns created
Showing off a good old SELECT statement.

An important thing to note is that when selecting columns, we have to type their names as they exist in the database. If a column name contains an underscore (_), you need to use it. Otherwise, they are not case sensitive.


You may want a column to be called something different in your report than the name of the column in the database. You can rename columns with an optional parameter called ‘AS’. This keyword allows you to define the column name so it shows differently in your report.

Let’s say we have ‘Address_1’ and ‘Address_2’ columns in our database and we want our report to make them more presentable.To do that, we can write a query like SELECT Address_1 AS primary_address, Address_2 AS secondary_address FROM Employee. The report will show the columns after the ‘AS’ keyword. The order of how they’re written matters.

SELECT statement with "AS" keywords highlighted in red
What 'AS' keywords look like in a query.

Other than columns, we can also make use of some tools called functions which typically result in numerical answers. These functions help us in extracting the count of entries, the maximum or minimum value, the average, and the sum of a specific column. See our article, [Top 10 SQL Functions You Should Know](LINK), to learn some of these functions. 

FROM

The FROM statement allows us to specify the table from which we want to extract our columns. It is possible to link tables together, making use of the relationships in your relational database, using a function called JOIN. More on this in our upcoming Advanced SQL article.

FROM statement highlighted in red for a SQL query
There’s not much to the FROM clause. It’s that simple. 


Note that table names are not case sensitive, just like columns. If you want to know more about naming rules in SQL, check this doc.

As a typical relational database can easily consist of a few million rows, learning to harness the power of SQL will enable you to pull, analyze, and draw insights from data.

WHERE

The WHERE clause allows us to add conditions to our queries. In the above examples, you might’ve guessed that we would receive all the rows from the table we are extracting the data from. That is what happens when there is no WHERE clause.

The WHERE clause adds value to our reports. Simply extracting all the entries won’t provide us with meaningful insights and that’s why this clause is so useful.

WHERE clause highlighted in red for a SQL query
This clause is super flexible. It allows us to search for almost anything we can think of. 

If you want to know the count of how many employees in your table have a salary greater than a certain amount, the details of all employees born in June, or the phone numbers of every employee named John, you can easily do that with the help of the WHERE clause.

The WHERE clause contains operators which are basically the conditions. Operators can be equal, greater than, less than, not equal to, etc. For a full list of WHERE operators, check this page out.

GROUP BY

This clause is a bit tricky to understand, so read closely. In queries where you see the GROUP BY clause being used, you will almost always see functions like COUNT, AVG, and SUM being used too.

If we want to know the count of all employees with specific degrees, we can use the GROUP BY statement to make it easier. In this case, we will use GROUP BY with the Degree column.

GROUP BY clause highlighted in red for a SQL query; Degrees example
As you can imagine, "GROUP BY Degree" will group by degrees!


Another example is if we are a manager at a car dealership and we want to know the average price of the different brands of cars we currently have. In this case, GROUP BY will be used together with the Brand column.

Demonstrating the clause "GROUP BY brand" for a SQL query
I hope you see the pattern by now. "GROUP BY brand" will group by brand!

Similarly, if we want to see the average price by each style of car, we can do this by changing the GROUP BY.

Demonstrating the clause "GROUP BY style" for a SQL query
Alright, one more time for good measure. "GROUP BY style" will group by style!

In the car dealership case, we could find the average price of each brand or style in a separate query for each. The GROUP BY clause enables us to get the average for all in one query. A big part of mastering SQL is being able to know which tools you need to utilize to make your task the most efficient.

Practice Makes Perfect

That’s it! You just learned the basics of a SQL query. With the above information, you’re more than ready to start writing basic SQL queries.The best way to continue to learn is to practice! Use the tools below to take your first stab at writing SQL. They’re free!

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.