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:

table: users
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| first_name  | varchar |
| last_name   | varchar |
+-------------+---------+
table: orders
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| order_id    | int     |
| user_id     | int     |
| date        | date    |
+-------------+---------+

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 *.

SELECT * FROM users;  --select all columns 
SELECT first_name, last_name FROM users;  --only select two columns

• 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.

SELECT * FROM orders WHERE date > '2020-01-01'

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

--Select user who's first_name starts with 'B', and last_name starts with 'c'
SELECT * FROM users WHERE first_name REGEXP '^B' AND last_name REGEXP '^c';

• Order result

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

SELECT * FROM users ORDER BY first_name;
SELECT * FROM users ORDER BY last_name DESC;

• 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:

--Sorting the orders in descending order then return the most recent  record:
SELECT * FROM orders ORDER BY date DESC LIMIT 1;
--return the 2nd most-recent record: 
SELECT * FROM orders ORDER BY date DESC LIMIT 1 OFFSET 1;

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.

SELECT * FROM users JOIN orders ON users.user_id = orders.user_id

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.

SELECT * FROM users LEFT JOIN orders ON users.user_id = orders.user_id

• 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:

table: employees
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| first_name  | varchar |
| last_name   | varchar |
| manager_id  | int     |
+-------------+---------+

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:

SELECT e.first_name, e.last_name, m.first_name AS manager 
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id

Practice

Let's start with the easiest problem:

— Combine tables:

Combine Two Tables - LeetCode

Here we use left join:

SELECT FirstName, LastName, City, State FROM Person 
LEFT JOIN Address
ON Person.PersonId = Address.PersonId;

—Self Compare

Employees Earning More Than Their Managers - LeetCode

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

SELECT e.Name AS Employee
FROM Employee e, Employee m
WHERE e.ManagerId = m.Id AND e.Salary > m.Salary

—Date compare

Rising Temperature - LeetCode

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

SELECT w1.id FROM Weather w1, Weather w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1 AND w1.Temperature > w2.Temperature

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