SQL — Select and Lookup

Follow up on the previous article about SQL CRUD https://toddypet.medium.com/sql-create-insert-f9afe47316b5. In this article, I will go through the command you need while selecting data with SQL. I will first introduce a strategy for data analysis and then go through the SQL command which maps with each step.

Data analysis strategy

Almost every single thing we do can be recorded as data, businesses can empower their business decision by analyzing these data. However, have you ever encounter difficulties that reading a big set of data but don’t know what to do? It didn't matter which programming language, tools you are using(SQL, Python, Excel). You can easily clean up your data by following below 4 steps.

  1. Select: Select the data you need. Either from one table or is a combination of multiple tables
  2. Dimension: Decide how you are going to slice or group the data.
  3. Metrics: Decide what you want to measure. What you are calculating. Remember, you can only do it on numeric data
  4. Function: What function do you want to apply to the data

Let’s see how this strategy applies with SQL

Select

First of all, you need to get the set of data you need. You can do it in below ways:

  1. Select from one table

2. Select from two tables — JOIN (column level)

we use this when you have a foreign key to other tables and join on the key

  • Left join: Contain all information from the left-hand table
  • Right join: Contain all information from the right-hand table
  • Inner join: Contain information that exists in both tables
  • Self join: join with itself.

3. Select to filter out the data you need, below are some of the combition you can use on WHERE command

WHERE + Like + wildcard

WHERE + IN

WHERE + BETWEEN (often used on date)

WHERE [condition 1] AND [consition 2]. AND operator display the value that all condition is true

WHERE [condition 1] OR [consition 2]. OR operator display if any of the condition is TRUE

Dimension

This step is like pivot in excel, let’s say you have a table of drinks order, you would like to see that which

GROUP BY + Having (on the aggregate function)

NOTE: whatever is in group by need to show in select

Metrics

After Group by, what you want to measure. Metrics are quantitive measurements, are the thing can be count.

Function

Once you have chosen the Measure column, then you can think about what you are going to do with that number, below are some common commands you will put on the column

  1. Create a new column derives from an existing record

2. Common commands that has been used: MIN, MAX, COUNT, AVG, SUM

Practice

There are lots of websites provides some interview question for you to practice your SQL. Below is some recommend website:

  1. https://www.hackerrank.com/domains/sql?filters%5Bstatus%5D%5B%5D=unsolved&badge_type=sql
  2. https://sqlzoo.net/wiki/SQL_Tutorial

Ref

Hello, I am Heidi. I started this medium blog to post all of the note I have when I am learning new things. Hope you find it useful as well:)