Cracking SQL Interview

April 16, 2022

A (general-purpose) programming interview is not enough for hiring back-end engineers. We need an SQL interview too. That is rare but important.

Some startups or tech companies even miss this kind of interview. But it's okay to have some preparation. These tips could also improve our SQL skills.

SQL stands for Structured Query Language. It is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS).

MySQL is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter. It is free and open-source software under the terms of the GNU General Public License.

The current stable version of MySQL is 8. You can install the MySQL server to your local or server from the installer here https://dev.mysql.com/downloads/mysql. Or maybe you want to pull the Docker image for your local, https://hub.docker.com/_/mysql.

There are a lot of installation instructions out there that can help you. We also don't discuss the basics of SQL or MySQL here. This article is about general SQL syntaxes with examples that happen in interviews.

Initial

Let's set up the database, tables, and data. You can use the SQL syntaxes below. You can use any client such as MySQL CLI, MySQL Workbench, phyMyAdmin, Adminer, etc. I use Microsoft Visual Code with MySQL extension by cweijan. So, I can run SQL queries right in the text editor.

Database

1
2

CREATE DATABASE `sql_interview`;
USE `sql_interview`;

We create a database with the name sql_interview. You can use whatever name you want.

Tables

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

CREATE TABLE `customers` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `contact_name` varchar(255),
  `country` varchar(255)
);

CREATE TABLE `orders` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `created_at` datetime NOT NULL
);

CREATE TABLE `suppliers` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `country` varchar(255)
);

These three tables are enough for our demo. No foreign key is needed because I want to demonstrate some JOIN queries.

Data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

INSERT INTO `customers` VALUES (1, 'John Doe', 'John', 'USA');
INSERT INTO `customers` VALUES (2, 'Harry Potter', 'Harry', 'UK');
INSERT INTO `customers` VALUES (3, 'Oeray', 'Oeray', 'Borneo');
INSERT INTO `customers` VALUES (6, 'Budi', 'Budi', 'Indonesia');
INSERT INTO `customers` VALUES (7, 'Agus', 'Agus', 'Indonesia');
INSERT INTO `customers` VALUES (8, 'Agus', 'Agus', 'Indonesia');

INSERT INTO `orders` VALUES (1, 2, DATE('2022-04-16'));
INSERT INTO `orders` VALUES (2, 4, DATE('2021-04-16'));
INSERT INTO `orders` VALUES (3, 5, DATE('2020-04-16'));

INSERT INTO `suppliers` VALUES (1, "T'Challa", 'Wakanda');
INSERT INTO `suppliers` VALUES (2, 'Elon Musk', 'Mars');
INSERT INTO `suppliers` VALUES (3, 'Wong', 'Hong Kong');
INSERT INTO `suppliers` VALUES (4, 'Steve Roger', 'USA');

JOIN

In some interviews or questions, you need to know the difference between JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN. Here they are:

JOIN / INNER JOIN

JOIN is equal to INNER JOIN. INNER JOIN returns records that have matching values in both tables.

1
2
3

SELECT `orders`.`id`, `customers`.`name`, `orders`.`created_at`
FROM `orders`
INNER JOIN `customers` ON `orders`.`customer_id` = `customers`.`id`;

LEFT JOIN

LEFT JOIN returns all records from the left table, and the matched records from the right table.

1
2
3

SELECT `orders`.`id`, `customers`.`name`, `orders`.`created_at`
FROM `orders`
LEFT JOIN `customers` ON `orders`.`customer_id` = `customers`.`id`;

RIGHT JOIN

RIGHT JOIN returns all records from the right table, and the matched records from the left table.

1
2
3

SELECT `orders`.`id`, `customers`.`name`, `orders`.`created_at`
FROM `orders`
RIGHT JOIN `customers` ON `orders`.`customer_id` = `customers`.`id`;

CROSS JOIN

CROSS JOIN returns all records from both tables.

1
2
3

SELECT `orders`.`id`, `customers`.`name`, `orders`.`created_at`
FROM `orders`
CROSS JOIN `customers`;

HAVING

The HAVING was added because the WHERE cannot be used with aggregate functions.

1
2
3
4

SELECT COUNT(1) AS `customers`, `country`
FROM `customers`
GROUP BY `country`
HAVING COUNT(1) > 1;

DATEDIFF

The DATEDIFF returns the number of days between two date values.

1
2
3
4

SELECT DATEDIFF(`o1`.`created_at`, `o2`.`created_at`) AS `days`
FROM `orders` AS `o1`
JOIN `orders` AS `o2`
WHERE `o1`.`id` = 1 AND `o2`.`id` = 3;

UNION

The UNION is used to combine the result of two or more SELECT statements.

• Every SELECT within UNION must have the same number of columns.
• The columns must have the same data types.
• The columns in every SELECT statement must be in the same order.

1
2
3

SELECT `country` FROM `customers`
UNION
SELECT `country` FROM `suppliers`

DISTINCT

The DISTINCT is used to return only distinct (unique) values.

1
2
3

SELECT `country`, COUNT(DISTINCT `name`) AS `total_names`
FROM `customers`
GROUP BY `country`;

Math Functions

There are a lot of mathematical functions in MySQL. The mod is one of them.

1
2
3

SELECT *
FROM `orders`
WHERE MOD(`customer_id`, 2) = 0;

CASE

The CASE is just like IF ELSE in other programming languages.

1
2
3
4
5
6

SELECT `name`,
CASE
  WHEN `country` = 'Mars' THEN 'Alien'
  ELSE 'Human'
END AS `race`
FROM `suppliers`;

IF

The IF is the short version of the CASE.

1
2
3

SELECT `name`,
IF(`country` = 'Mars', 'Alien', 'Human') AS `race`
FROM `suppliers`;

CONCAT

Combine string with CONCAT.

1
2

SELECT CONCAT(LOWER(SUBSTRING(`contact_name`, 1, 1)), SUBSTRING(`contact_name`, 2, LENGTH(`contact_name`) - 1)) AS `name`
FROM `customers`;

GROUP_CONCAT

The GROUP_CONCAT returns a list of entries separated by a comma.

1
2
3
4
5

SELECT
  `country`,
  GROUP_CONCAT(DISTINCT `name` ORDER BY `name`) AS `customers`
FROM `customers`
GROUP BY `country`;

Closing

You can learn more about SQL syntaxes on W3Schools and practice more on LeetCode. I just summarized what I got the most in SQL exercises. Maybe I miss something here. Feel free to put them in the discussion below. You can get those examples at https://github.com/aristorinjuang/mysql.

References

• https://en.wikipedia.org/wiki/SQL
• https://en.wikipedia.org/wiki/MySQL

Related Articles

• Cascading Style Sheets (CSS)
• HyperText Markup Language (HTML)
• TypeScript Crash Course
• JavaScript Crash Course
• Concurrency in Go (Part 2)

• Share
• Tweet