The Essential Toolbox: Fundamental SQL Statements-I
SQL is a language that is used to manage and create databases. It is like a bridge to communicate with your data! It stands for Standard Query Language. It allows us to access a database and by writing queries, we may manipulate, filter, examine and export the data we need.
SQL is an important tool for a data analyst! It is really compatible with English and it is like your are making sentences. You will see what I mean in a couple of minutes!
For this reason, in this article, we will cover the main SQL Statements.
It is the first article of the SQL-article series and we will focus on basics and will have more advance topics along the way in next articles.
At the end of this article you would be familiar with the following statements:
- SELECT
- SELECT DISTINCT
- WHERE
- COUNT
- UPPER/LOWER Functions
- ORDER BY
- LIMIT
- BETWEEN
- IN
- LIKE/ILIKE
- COLUMN ALIASES
- Aggregate Functions
We have lots of things to cover! Without losing time, let’s dive into it!
Note: I will cover PostgreSQL syntax in this article, but note that the most of the syntax is applicable for other SQL Databases.
SELECT Statement
This is the most important syntax you should be familiar with. Also, if you know that syntax, then you cover most of SQL! :)
Select statement is used to bring information from a table.
General Syntax:
Then we receieve the following result from the database:
With these query, we retrieve all the columns from a table and have a general overview of our data.
But if we want to retrieve specific columns then the syntax will be as follows:
Note: If we write the SQL queries with small letters, it will still work, but capitalizing the SQL commands makes them more readable.
Similarly, if we don’t use semicolon ‘;’ then it will still work, too. But the semicolon shows that your query has finished at that line, and it makes the query more readable, especially if you need to write lots of queries.
SELECT DISTINCT Statement
As a data analyst one of the most common challenges you would face is dealing with the duplicates. Or, in some cases you would want to know how many distinct/unique values you have in a column.
For these kinds of scenarios, what you need is SELECT DISTINCT statement.
General Syntax:
This query gives how many distinct cities we have in our customers table.
SELECT WHERE Statement
We may use SELECT WHERE statement when we want to filter our data.
Also, we may use the logical operations such as
- = equal
- != not equal
- > = greater than or equal
- < = less than or equal
- AND
- OR
with WHERE condition to combine multiple conditions.
General Syntax:
This query gives the distinct cities in UK in our database.
Question: Retrieve the customers’ information who live in Barcelona or Madrid in Spain?
The outcome will be as follows:
COUNT Function
Count function gives the number of rows or observations in our data.
General Syntax:
The output is 91 which means that we have 91 number of records in our database or in other words 91 observations or in other words 91 rows…
Note: You may use either COUNT(column_name) or COUNT(*) as the number of rows will be exactly the same.
But I think it is more efficient to use with the column name since it gives you a quick reminder what you were thinking , or gives a clue about the problem in your mind while writing your query!
Question: How many distinct countries are there in our database?
And the output is:
UPPER / LOWER Function
UPPER() and LOWER functions are useful while we are dealing with strings! As their names suggest, we are using them while changing a string to an upper case or a lower case.
For example, you want to examine a column with strings. But you are not sure whether lower or upper cases are used. If you will use the logical operator “=”, since it is case sensitive, you may end up missing some of the data.
For these kinds of scenarios, you may capitalize or uncapitalize your column.
General Syntax:
And the outcome is:
ORDER BY
This statement allows us to sort the column in either ascending order or descending orders.
It has ASC as a default, so if you did not mention anything on how you would prefer your outcome, it will sort it by ascending automatically.
It is the ‘Products’ table. Let’s work on ‘Price’ column!
Question: What is the most expensive product in our database?
General Syntax:
This query order the Price from highest to lowest.
And the outcome is:
Now we know that the product with the highest price is Côte de Blaye with value 263.5.
LIMIT
The LIMIT command helps us to limit the outcome of the query! After SQL has done all the other steps like filtering, ordering, so on, then the limit command gives the number of rows we have wanted!
Note: If you are working with big data, it is always useful to limit all the queries while examining your data. It saves time!
General Syntax:
This query gives the top 5 prices for the products with CategoryID = 1. The outcome is:
BETWEEN
The between operator helps us to select values for a given range.
General Syntax:
This query gives the number of products where the prices are between 50 and 100.
Note: When we use between operator, it would be helpful to keep in mind that the values are included while making the selection.
So, the BETWEEN operator is the same as using “> =” and “< =”
Note: We may also use NOT BETWEEN operator. And, in this case, we exclude the values between the range that we wrote.
IN
The IN operator is used to select the values that belong to a list of options.
General Syntax:
Similarly, we may look for values where does not belong to a list of options. In that case, we may use NOT IN operator.
General Syntax:
The query gives the products with CategoryID = 1 and with product names are different from ‘Chais’ and ‘Chang’ . And the outcome is:
LIKE and ILIKE
We use these operators to select the general patterns in our data.
We use the wildcard character “%” to match any sequence of characters.
General Syntax:
- To select the names starting with ‘A’:
- To select the names ending with ‘A’:
- To select the names containing ‘A’ somewhere in the string:
Note: It is important to keep in mind that LIKE is case-sensitive. So, it will be affected whether we use upper or lower case while writing the query. If you are not sure which one you need to use, you may prefer to use UPPER()/LOWER() functions or you may use ILIKE instead of LIKE.
Note: We may also use NOT LIKE if we want to exclude the data with the given pattern.
General Syntax:
The outcome of this query will give the customer names without having letter ‘A’!
Aggregate Functions
The purpose of an aggregate functions is to return one output for many input values.
Most common aggregate functions are as follows:
- MIN() → returns the minimum value of the data.
- MAX() → returns the maximum value of the data.
- AVG() → returns the average value.
- COUNT() → returns the number of rows.
- SUM() → returns the sum of the values.
Column Aliases
If we want to name the columns, then we may name it by using AS keyword. And it is really useful to rename the columns while examining your data especially for the aggregated columns.
Also, note that column aliases valid only for the query you wrote.
General Syntax:
And the outcome will be as follows:
Note: If we look at the average of the price, it is clear that it would be more readable with 2 decimals. We may round any numerical value with ROUND() function.
General Syntax:
Let’s end this article with a query that summarises most of what we have seen throughout the article!
It will give the top 5 products with the highest price where SupplierID is 1 or 2, product name is not starting with L and the price is greater than 10.
The output will be as follows:
Note that there are only 4 rows despite we wanted top 5, it is because there are only 4 products in our database that satisfy the given conditions.
Thanks for reading the article and making it to the end.
Hope you enjoy reading it!
If so, you may also like my other articles on my medium account.
For more, follow → https://medium.com/@meliss85
Disclaimer: The database in this article is from the website https://www.w3schools.com/sql. It is a website that you can practice SQL without downloading anything to your computer. I highly recommend it!
References