Cracking SQL Interview

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');
customers
id name contact_name country
1 John Doe John USA
2 Harry Potter Harry UK
3 Oeray Oeray Borneo
6 Budi Budi Indonesia
7 Agus Agus Indonesia
8 Agus Agus Indonesia
orders
id customer_id created_at
1 2 2022-04-16
2 4 2021-04-16
3 5 2020-04-16
suppliers
id name country
1 T‘Challa Wakanda
2 Elon Musk Mars
3 Wong Hong Kong
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`;
id name created_at
1 Harry Potter 2022-04-16 00:00:00

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`;
id name created_at
1 Harry Potter 2022-04-16 00:00:00
2 (NULL) 2021-04-16 00:00:00
3 (NULL) 2020-04-16 00:00:00

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`;
id name created_at
1 Harry Potter 2022-04-16 00:00:00
(NULL) John Doe (NULL)
(NULL) Oeray (NULL)
(NULL) Budi (NULL)
(NULL) Agus (NULL)
(NULL) Agus (NULL)

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`;
id name created_at
1 John Doe 2022-04-16 00:00:00
2 John Doe 2021-04-16 00:00:00
3 John Doe 2020-04-16 00:00:00
1 Harry Potter 2022-04-16 00:00:00
2 Harry Potter 2021-04-16 00:00:00
3 Harry Potter 2020-04-16 00:00:00
1 Oeray 2022-04-16 00:00:00
2 Oeray 2021-04-16 00:00:00
3 Oeray 2020-04-16 00:00:00
1 Budi 2022-04-16 00:00:00
2 Budi 2021-04-16 00:00:00
3 Budi 2020-04-16 00:00:00
1 Agus 2022-04-16 00:00:00
2 Agus 2021-04-16 00:00:00
3 Agus 2020-04-16 00:00:00
1 Agus 2022-04-16 00:00:00
2 Agus 2021-04-16 00:00:00
3 Agus 2020-04-16 00:00:00

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;
customers country
3 Indonesia

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;
days
730

UNION

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

1
2
3
SELECT `country` FROM `customers`
UNION
SELECT `country` FROM `suppliers`
country
USA
UK
Borneo
Indonesia
Wakanda
Mars
Hong Kong

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`;
country total_names
Borneo 1
Indonesia 2
UK 1
USA 1

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;
id customer_id created_at
1 2 2022-04-16 00:00:00
2 4 2021-04-16 00:00:00

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`;
name race
T‘Challa Human
Elon Musk Alien
Wong Human
Steve Roger Human

IF

The IF is the short version of the CASE.

1
2
3
SELECT `name`,
IF(`country` = 'Mars', 'Alien', 'Human') AS `race`
FROM `suppliers`;
name race
T‘Challa Human
Elon Musk Alien
Wong Human
Steve Roger Human

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`;
name
john
harry
oeray
budi
agus
agus

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`;
country customers
Borneo Oeray
Indonesia Agus,Budi
UK Harry Potter
USA John Doe

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

Related Articles