Learn SQL in Everyday Language
Updated: Dec 4, 2020
What is SQL After All?
SQL stands for "Structured Query Language", so it is a programming language just like python, java or R. What it differs from most common language is that it is a type of declarative language, which means it tells computers what to do instead of how to do it. As its name suggests, SQL is the language used to communicate with the database for the purpose of requesting and extracting the data we want. Let's first understand what is database? The easiest way to understand "database" is to see it as a huge collection of spreadsheets or tables that are intertwined and associated with each other.
Many people may confused SQL with some buzz words that we frequently encountered, such as MySQL, MS SQL server, or SQLite. First of all, the fundamental difference is that SQL is a language, not a software or a system. However, MySQL and SQL server are software that utilises SQL. Hopefully, the table below helps you to differentiate these concepts.
Basic Database Terminology
When it comes to describing a database, there are some commonly used words: "entity", "attribute" and "instance". Simply put, they are just the nerdy version of "table", "column" and "row".
Entity: it refers to the table in database. Each entity gathers a group of similar objects or individuals. It can be tangile or intangible, e.g. cars, employees, customers, or transactions, accounts etc.
Instance: each row in the table is called an instance. It represents an individual in the group, e.g. one transaction, a customer, a product etc.
Attribute: it is used to describe a dimension or a characteristic of the instances. So basically, each column of the table represents an attribute.
Write the Simplest SQL Statement
As mentioned earlier, SQL is basically a language built to communicate with the database and to ask questions like "can I have all the transactions from last month?"or "where are the majority of my audience coming from?". Then SQL is a structured way to ask those questions so that it is easier for the database to understand.
The simplest statement to ask a question would be:
SELECT <what_we_want> FROM <where_to_find_what_we_want>;
This statement is composed of three parts:
1. Where to find the information we want?
This refers to the entity or the table that stores the data. Imagine that you want to get information from a folder with hundreds of spreadsheets, you first need to know what is the name of the table. FROM statement allows you to tell the database what tables you are looking for. For example, if we want to know the age distribution of the members, and all customer related data are stored in the table "Customer_Data". Then we write the FROM statement as:
2. What information do you want to know?
We use SELECT statement to get the information that we want. Sometimes, we want all the information from one table, then in this case, we use * to represents all columns. For example, "SELECT * FROM Customer_Data;" would give you all the contents in the table Customer_Data. Keep in mind that SELECT statement is a column-wise manipulation, which means that it controls the columns that we select in a table rather than the rows.
When we are presented with too many columns of information, we want to limit the number of attributes in the view. Using SELECT statement is just like the "Hide" function in Excel which helps to clean up the spreadsheet and filter down to the attributes required for further analysis.
For example, to analyze the customer demographics, then we potential need to select these attributes "age", "gender", "country" using the following SELECT statement:
SELECT age, gender, country
3. The semi-colon
Depends on the SQL development environment you are using, some will give you an error if you forget to put the ";" at the end of the statement, and you end up getting stuck for minutes or even hours only to find out that it is this simple symbol that caused the pain.
Overall, put all of three essential components together, we can write down the simplest SQL statement:
SELECT age, gender, country FROM Customer_Data;
SQL is Just Like Excel
Once we have finished the basic SELECT statement and FROM statement, we can apply additional f