10 MySQL interview questions you need to know

There are a ton of different database standard and extensions in use in the business world today — like MySQL, Postgres, SQLite, and more. There's a good chance that if you're interviewing for a technical role, your interviewer will ask you about the differences between these standards. Here are some basic talking points:

Advantages of MySQL

Compared to other database standard, MySQL has a number of distinct advantages:

• Easy installation. MySQL can be downloaded, installed, and running within minutes on just about any computer in the world — with very few dependences. It's extreme accessibility has contributed to its status as one of the most popular database standards in the world.
• Extremely fast. Among database standards, MySQL stands out as one of the fastest to query large quantities of data. That makes it particularly useful for at-scale applications within large enterprises.
• Secure. MySQL contains a ton of advanced security features that make it one of the most robust database systems in the world.
• GUI available. MySQL ships with a GUI (or Graphical User Interface), making it much easier to pick up than some other SQL extensions that only use the command line.

Disadvantages of MySQL

Of course, it ain't all sunshine and roses — every language and standard has its disadvantages! Let's take a look at some of MySQL's:

• Incomplete capabilities. MySQL implements a subset of the full SQL standard, but it's not fully compliant. That means that some features and functions you can use in other SQL extensions — for example, WITH statements in Postgres — aren't available within MySQL. That can make it frustrating to migrate for new users who are familiar with databases that use the full SQL standard.
• Slow development. MySQL is an open-source standard. That's both good and bad: it means that people from all over the world have contributed to and advanced it; but it also means that development and updates can be a bit slower than they are with other database standards.

Overall, MySQL is an exceptionally strong choice of database framework.

In MySQL, a JOIN clause is used to return a table that merges the contents of two or more other tables together. For example, if we had two tables — one containing information on Customers and another containing information on the Orders various customers have placed — we could use a JOIN clause to bring them together to create a new table: a complete list of orders by customer, with all necessary information to make shipments.

There are multiple types of JOIN clauses, and they all serve slightly different functions:

• INNER JOIN returns a list of rows for which there is a match in both tables specified. It's the default join type, so if you just type JOIN without specifying any other conditions, an INNER JOIN will be used.
• LEFT JOIN will return all results from the left table in your statement, matched against rows in the right table when possible. If a row in the left table does not contain a corresponding match in the right table, it will still be listed — with NULL values in columns for the right table.
• RIGHT JOIN will return all results from the right table in your statement, matched against rows in the left table when possible. If a row in the right table does not contain a corresponding match in the left table, it will still be listed — with NULL values in columns for the left table.

Bear in mind that FULL JOIN and CROSS JOIN, two types supported in numerous other database languages, aren't supported within MySQL. That's one of the disadvantages of using an extension that doesn't implement the full SQL standard.

Aliases are one of the most helpful organizational tools that MySQL has to offer, and it's very likely that you'll be asked about them during your interview. To that end, it's highly recommended that you study them in advance and learn the ins and outs of using them in SELECT queries.

The short story here is that aliases are created using the AS statement. The portion of code that precedes the AS represents the data to be aliased, and the tag after the AS represents the name of the alias itself.

Consider the following (complex!) code snippet from Deskbright's SQL Practice Pack:

SELECT
	t.order_id, orders.customer_id, t.total, orders.order_placed_date
FROM
	# List of all orders and total order values (alias t)
	(
		SELECT 
			order_id, SUM(purchase_price * quantity) AS total
		FROM 
			products_to_orders 
		GROUP BY order_id
	) AS t
INNER JOIN
	orders ON t.order_id = orders.order_id;

This snippet is a pretty complex query — and it would be impossible to execute without the AS statement! In the code above, a smaller query — namely the following — is aliased as t:

# List of all orders and total order values (alias t)
(
	SELECT 
		order_id, SUM(purchase_price * quantity) AS total
	FROM 
		products_to_orders 
	GROUP BY order_id
) AS t

Throughout the rest of the query, the alias t can be used to refer to the results of this inner query. This sort of aliasing is invaluable when constructing complex nested queries that rely on joining multiple calculated tables together.

It's worth noting that in some other database extensions, like Postgres, the WITH statement can also be used to create sub-query aliases like this one. But in MySQL, the WITH statement doesn't exist — AS is all you need.

Like any database, MySQL provides support for multiple tables — and these tables contain fields of various different types. For example, a "price" field in a database might appear as an integer, whereas a "name" field would appear as a text string.

During your MySQL interview, you'll likely be asked about the different data types available, as well as the uses for each. So, it's helpful to brush up on your data types in advance.

Here's a handy list:

• TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT all support integers, and are used to store things like quantity and price data. The difference between them is the size of integers they allow. TINYINT, for example, only allows storage of 1 byte, which corresponds to about the range -128 to 127. MEDIUMINT, on the other hand, allows storage of 3 bytes, which corresponds to about the range -8,388,608 to 8,388,607.
• FLOAT and DOUBLE all support decimal numbers, like 3.14. They're used to store data that can be represented as fractions, like weighting on a scale of 0 to 1. The difference between them is the level of precision they provide.
• DATE, DATETIME, TIMESTAMP, and TIME are all variants of types that support the storage of chronological information. Generally, DATETIME is used in most databases, as it can store data on both the date and time of an action.
• CHAR and VARCHAR store fixed- and variable-length strings of text, respectively. Oftentimes, VARCHAR is used in databases to store variable-length text blocks like names and addresses.
• TEXT and BLOB store larger strings of text that wrap across multiple lines.
• ENUM fields only accept a pre-defined set of inputs specified by the database owner on field creation. They're generally used as validation fields to ensure that invalid input is not entered.

Given the numerous field types above, which type to choose for various input formats can be a difficult, subjective decision. But there are some fairly common conventions across the tech world, and it can be helpful to know them before your interview.

One of the most common — and counterintuitive — is currency storage. Since we tend to write prices and transactions as decimals — for example, 19.95 — you might be tempted to use a FLOAT or DOUBLE field for currency storage. But for more applications, the most commonly-used field type is actually INTEGER.

Why is that? Because storing currency values as US Dollars in a field type like FLOAT can lead to rounding errors and other compatibility problems down the line.

As a solution, most currencies are stored in databases as INTEGER fields representing cents and not dollars. So, an item that costs $19.95 would be represented in a database as costing 1995 cents.

One of the most commonly-used applications of databases is aggregating data — meaning taking large data sets and combining them together to make them more understandable.

For example, a database containing 1,000 rows of order history — with one row representing each order — isn't particularly useful for the owner of a fast-growing business. She can't piece together any information on what's going on with her company by combing through hundreds or thousands of separate entries.

But if the same business owner uses aggregation functions — for example, to SUM() her total sales numbers by product line month-to-month — she can extract some truly valuable insights from what would otherwise be a nondescript pile of numbers.

Here are some of the most commonly-used aggregation functions in MySQL:

• SUM() — used to calculate the sum of various rows of data to find meaningful patterns and trends.
• AVG() — used to find averages over large quantities of data.
• COUNT() — used to count rows; particularly useful for aggregating quantity information.
• MAX() and MIN() — used to find maximum and minimum values within a large set of data.

UNION and UNION ALL are two commonly-used functions within MySQL. Simply put, they combine the contents of two or more tables into one larger table. This can be extremely useful when mashing two datasets together into one — particularly when those two datasets share similar table structures.

The difference between them? UNION removes duplicate records, whereas UNION ALL does not. Here's a quick example to illustrate the difference:

SELECT 'data_point' AS a UNION SELECT 'data_point' AS b;

/*
+------------+
| a          |
+------------+
| data_point |
+------------+
1 row in set (0.01 sec)
*/


SELECT 'data_point' AS a UNION ALL SELECT 'data_point' AS b;

/*
+------------+
| a          |
+------------+
| data_point |
| data_point |
+------------+
2 rows in set (0.00 sec)
*/

Some other database extensions — Postgres, for example — offer support for the FULL OUTER JOIN command, which combines all rows of two separate tables on a linked column id, regardless of whether those rows match.

Unfortunately, MySQL doesn't provide support for the FULL OUTER JOIN command, which means that it's a bit more difficult to combine tables in this manner.

If your interviewer is feeling particularly tricky, he or she might ask you how you could simulate a FULL OUTER JOIN in MySQL. The answer is fairly simple, but takes a bit of thinking to get to!

Here's the trick: use MySQL's UNION ALLoperator, which combines the results of two select statements.

Here's a brief example using two sample tables, students and advisors:

SELECT
	*
FROM 
	students
LEFT JOIN
	advisors ON students.advisor_id = advisors.advisor_id
UNION ALL
SELECT
	*
FROM
	students
RIGHT JOIN
	advisors ON students.advisor_id = advisors.advisor_id
WHERE
	students.advisor_id IS NULL;

In this case, we start with the students table and perform a LEFT JOIN onto advisors. This will pull in information on all students — but not advisors to whom no students are assigned.

How do we get the missing advisor information? We start with the students table, then RIGHT JOIN onto advisors where students.advisor_id IS NULL. This will pull in advisors data for all the advisors who are not assigned to students.

We then perform a UNION ALL between these two tables to directly combine their contents.

Until now, we've been primarily examining code snippets related to SELECT statements, which query information from a database without actually modifying its contents. But knowing how to read a database is only half the battle. To succeed in the tech world, you've also got to know how to write to a database!

This is where INSERT, UPDATE, and DELETE come in. Here's a short description of what each of them does:

• INSERT adds new rows to a database. It is the primary tool used to expand a database with new information. For example, INSERT would be used to create a new order record in a database when a user places an order on an e-commerce site.
• UPDATE changes existing records in a database. It's used to make updates to records that are already present. For example, UPDATE would be used to help a user change her password.
• DELETE removes records from a database permanently. It should be used very carefully, as it has the potential to wipe out information unnecessarily, if executed incorrectly. DELETE might be used to remove a user's account from a database if she decides to cancel her membership at a site.

Comparison operators are the bread and butter of many SQL queries. They help users create complex queries that evaluate rows of data against complex specified criteria.

You probably know a fair number of comparison operators already from your work in every day programming — for example, the >> operator evaluates whether the expression on its left side is greater than or equal to the expression on its right side.

Here's a complete list of comparison operators usable in MySQL:

Congrats — you've learned the important stuff, and now have a solid foundation for success in your interview! But practice makes perfect — so scroll down for a free SQL practice challenge.