Useful SQL queries & practice

A database is important in App development. After getting so familiar with commands to build a database in Ruby on Rails, I found it’s necessary to review some common SQL queries. In this post, I’ll list the queries and do some practice together.

Let’s assume we’re developing a shopping website. We have lots of users, each user may have many orders.

Based on the relationship, we create the following tables in our database:

I’ll introduce the queries based on these tables.

Queries on a single table

When dealing with a single table, usually, we wanna retrieve rows of data as we wish. We apply some conditions on them, and SQL will filter the result for us. Several common queries are:

  • Select columns

‘SELECT’ is the most basic query in SQL. It will retrieve rows of data as we selected. We can specify several columns, or all columns using *.

  • Filter data with conditions

Sometimes we don’t need all the data, we wanna filter data with some conditions. In this case, we use WHERE and operators together.

For example, we wanna filter the orders that only show orders placed this year.

We can also apply multiple conditions together using AND/OR/NOT

  • Order result

Sometimes we want our results shown in ascending or descending order, we can simply achieve it by add ORDER BYin the end:

  • Limit result

Limit is used when we wanna return a limited number of results. For example, we wanna return the most recent order from orders table:

Relational tables

Tables can have relationships. Relationships could be “has many” or “belongs to”. Take our users and orders table as an example, one user can have much order, and the order must belong to one user. When dealing with relationships, we use JOIN in SQL to handle it.

  • Inner Join

Inner join occurs when we wanna combine two relational tables. For example, we wanna combine users and orders tables into one big table which contains all columns.

By inner join, user in each row in the big combined table will have the corresponding order information.

  • Outer Join

One problem for inner join is that it only retrieves the intersection of two tables. For example, if one user never orders, then the inner join will not show this record. To solve this, we use outer Join instead.

There’re two outer join methods: left join and right join. The difference is to join with the left or right table.

For example, we wanna return all the users and orders info, regardless the user has ordered or not.

  • Self Join

The last join method is self join. It happens when we wanna join a table with itself. For example, we have an employees table which holds the following columns:

Since each employee has a manage, expect the CEO doesn’t have a manager. We wanna see the employee name and manager name in the same table. Here we use self join:

Practice

Let's start with the easiest problem:

— Combine tables:

Here we use left join:

—Self Compare

It’s similar to our example in self join, but here we need to compare employee’s and manager’s salary :

—Date compare

Different from integer comparison, compare two dates is a little tricky. We can use DATEDIFF to tell the difference:

All the queries stated are super common and useful. Hope my blog can help you better-understanding SQL!

on the way to become a programmer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store