Do you think learning SQL will help you in your career? You are right.
SQL is one of the easiest computer languages to learn. These days many non-IT employees have SQL skills and use them to extend their professional capacity. Moreover, more and more companies are encouraging their employees in non-IT areas (like sales, advertising, and finances) to learn and use SQL. One of the benefits of empowering employees with SQL skills is that once you answer one data question, the results will generate a new data question, and then perhaps a cascade of further data questions. A non-IT employee with the capacity to answer business data questions with SQL will increase their knowledge about the company and about productivity. Go for SQL—be a solid business contributor in your company!
SQL is Easy: Let’s Try a Basic Query
Do you know why SQL is an easy language to learn? Because it is very similar to natural language; specifically, it is similar to English. Most other computer languages require complex programming techniques, but SQL uses relatively simple commands. In SQL you only need to describe the data you want. Easy!
SQL does, however, need a database where the data are organized and stored, and you need to know a little bit about the database, even if you only know about the portion of the database you are interested in. Let’s see a simple example database. Suppose that we work for a company with plans to create branches in some new countries and cities. We need a database with all the countries and cities of the world in order to research which locations are the best candidates for creating the new branches. The requirement for creating a new branch is that the capital city should have a population of greater than 4 million inhabitants.
In a database, data of the same type are grouped into tables. In our example database we have two tables:
CITIES. In the following image you can see the database, but not all of the data are being shown.
|New York||8765998||United States||NO|
|Rio do Janeiro||5873422||Brasil||NO|
We can treat every query as a business case, where the starting point is a business question to be answered using the data in the database. The tool to extract the answer from the database is an SQL query. For every different business question you need to create a different SQL query.
What are all of the capital cities of the world with a population greater than 4,000,000?
Below is the SQL query to answer this question.
SELECT NAME FROM CITIES WHERE IS_CAPITAL = ‘YES’ AND POPULATION > 4000000
As we can see, this query is really simple, and, if you read it, it sounds like a sentence in English describing exactly what data we are looking for.
Based on the data shown before, the result of this query will be
A Strategy for Writing Simple SQL Queries
In my personal experience, after some years of working with SQL, the best and easiest way to learn how to create your first queries in SQL is to follow a three question mental process. I will try to explain the mental process I go through when I create an SQL query. As we previously mentioned, our starting point is a business question.
What are all of the country names where English is the official language?
The process to solve this question is:
First step: Ask yourself this question: Do I have a table with all of the data needed to answer this business question?
If yes, what is this table? The table name must be put in the
FROM clause. Then the outcome of this step is:
FROM COUNTRIES. Congratulations, you already have the
FROM clause of the query!
Lesson Learned: Every SQL query needs a
FROM clause followed by the table from which the data are taken.
Second step: Once you have identified the table to work with, you need to think about the
WHERE clause. Ask yourself: what are the records (rows) of the table that I need? What do they have in common? How can I define a condition (like I
S_CAPITAL = ‘YES’) for these records? For our particular question the condition is:
WHERE OFFICIAL_LANGUAGE = ‘english’.
Lesson Learned: In an SQL query, the
WHERE clause is used to isolate the records you are looking for. The
WHERE clause needs a condition, like
OFFICIAL_LANGUAGE = ‘english’. Other conditions can be more complex.
Third step: What data am I looking for—the city
POPULATION, or perhaps both? All of these data elements must be in the
SELECT list. In our example this will be
SELECT NAME, because we are looking just for the country name.
Lesson Learned: Every SQL query needs a
SELECT clause followed by all the columns that you want to extract from the table.
Then, once we have the three pieces of our query, we need to put them together. The pieces we obtained MUST be in the following order:
SELECT NAME FROM COUNTRIES WHERE OFFICIAL_LANGUAGE = ‘english’
Let’s Create a Pair of Extra Queries
If you completed the previous section you already know the basics about an SQL query. Let’s continue by doing a pair of queries with an extra level of complexity.
The first query involves a more complex
WHERE clause. Suppose your boss wants a list of cities where your company could potentially open new branches. You only want to consider cities in the United States with a population greater than 1,000,000. The list should exclude New York and Washington, D.C., because your company already has branches in those cities.
What are the names of the cities in the United States with a population greater than 1,000,000?
Following the technique we learned in the previous section, the first and third steps are really easy to perform. The results of these steps are below:
First step: This step is really easy, resulting in the statement:
Second step: For this business question, this is the most complex step to solve. Let’s see how to solve it. For this query we need to use a logical connector called
AND. Perhaps you remember the
AND from school. We want to find cities meeting the following three conditions:
COUNTRY = ‘United States’
POPULATION > 1000000
NAME NOT IN (‘New York’, ‘Washington’)
Then if we connect all these conditions using
AND, we obtain the following result:
WHERE COUNTRY = ‘United States’ AND POPULATION > 1000000 AND NAME NOT IN (‘New York’, ‘Washington’)
Third step: This step is also really easy:
Then, putting all the pieces together, the complete query will be:
SELECT NAME FROM CITIES WHERE COUNTRY = ‘United States’ AND POPULATION > 1000000 AND NAME NOT IN (‘New York’, ‘Washington’)
The final example query will be shown in a more concise way. The interpretation can be read from the SQL code itself.
What are the names of all the Spanish speaking countries in the Americas that were founded in the 17th century?
SELECT NAME, INDEPENDENCE_DAY FROM COUNTRIES WHERE CONTINENT = ‘Americas’ AND INDEPENDECE_DAY BETWEEN ‘1700-01-01’ AND ‘1800-12-31’ AND OFFICIAL_LANGUAGE = ‘spanish’
First steps with SQL are really easy—you can have your first query running in a few hours or even in a few minutes. If you want to learn to create SQL queries, Vertabelo Academy has a long history of helping non-IT people to learn SQL.