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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Similarly, if we want to see the average price by each style of car, we can do this by changing the GROUP BY.
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!
SQLite Online (This link was used while making the tutorial and we recommend this)