10 MySQL interview questions you need to know

Got a MySQL interview coming up?
These are the 10 questions you need to practice before the big day.

MySQL is the most commonly-used Relational Database Management System (RDBMS) in the world. Pronounced "my-ess-cue-ell" and short for "My Structured Query Language", it is virtually ubiquitous in the world of technology, startups, and large corporations.

In short, MySQL is used to store large quantities of data that relate to each other in some way. For example, an e-commerce company might use MySQL to store information on its customers, the orders they have placed, and the products contained within those orders. Lots of other useful information could be bundled along with this data, like shipping dates, estimated delivery times, invoices, and more.

MySQL is an extension of SQL, which is a generic language used to modify and communicate with databases. There are many different extensions of SQL used around the world, but MySQL is the most common. Most extensions of SQL share very similar functionality and features — so be sure to check out our list of top SQL interview questions for some info that applies to all SQL's extensions.

Since MySQL is so commonly-used in the business world, it's important to have a strong grasp of it before interviewing at many modern companies. Recruiters often screen candidates for strong MySQL skills — and conduct SQL knowledge assessments before extending offers.

If you've got an interview at a tech company coming up, you'll want to brush up on your MySQL skills before the big day. Questions about the world's most popular database management system are bound to come up, and knowing the ropes could help you land an offer at the company of your dreams.

To help you out, we’ve compiled a list of the top 10 MySQL interview questions to study before you walk into your interview. With our help and a little advance prep, you'll be well-prepared to knock your recruiter's socks off.

New: Sign up for a free SQL mini-course

Make sure you're prepared for the interview that could change your career. Sign up to receive five free must-learn lessons in preparation for your SQL interview. Start preparing today!

By submitting this information, you agree to Deskbright's privacy policy.

 

1. What are some of the advantages and disadvantages of MySQL compared to other database standards?

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.

2. What are the different types of MySQL JOIN clauses, and how are they used?

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.

For more practice with JOIN clauses, check out our article on JOIN interview questions!

3. How do you create an alias in MySQL?

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.

New: Sign up for a free SQL mini-course

Make sure you're prepared for the interview that could change your career. Sign up to receive five free must-learn lessons in preparation for your SQL interview. Start preparing today!

By submitting this information, you agree to Deskbright's privacy policy.

4. What are some of the most common data types supported in MySQL?

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.

5. What type of field would you use to store currency information in a MySQL database?

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.

6. What are aggregation functions?

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.

New: Sign up for a free SQL mini-course

Make sure you're prepared for the interview that could change your career. Sign up to receive five free must-learn lessons in preparation for your SQL interview. Start preparing today!

By submitting this information, you agree to Deskbright's privacy policy.

7. What do UNION and UNION ALL do? What is the difference between them?

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)
*/

8. How would you simulate a FULL OUTER JOIN in MySQL?

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.

9. How are INSERT, UPDATE, and DELETE used in MySQL databases?

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.

10. What sorts of comparison operators exist in MySQL?

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:

Operator Meaning
= Equal to
<> or != Not equal to
> Greater than
>= Greater than or equal to
< Less than
< Less than or equal to
> Greater than
IN() Matches a value within another list
NOT The opposite of a given condition
BETWEEN() Within a specified range (inclusive)
IS NULL Checks to see whether a given value is NULL (generally, =NULL does not work for this)
IS NOT NULL Checks to see whether a given value is not NULL (generally, =!NULL does not work for this)
LIKE Checks to see whether a given value matches a simple pattern using the '%' and '_' symbols
EXISTS Returns TRUE if query matches at least one row in set

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.

New: Sign up for a free SQL mini-course

Make sure you're prepared for the interview that could change your career. Sign up to receive five free must-learn lessons in preparation for your SQL interview. Start preparing today!

By submitting this information, you agree to Deskbright's privacy policy.

Free practice question

Want more SQL interview prep? Try your hand with this free practice question:

Cocoa Confections is a small bakery that sells brownies, cookies, pies, and other delicious treats to customers online. It keeps records of all of its online sales in an SQL database that is automatically populated as customers place orders on its site.

In its database, Cocoa Confections has a customers table to keep track of customer contact information, and an orders table to keep track of various orders that those customers have placed. The schema of these tables is as follows:

CREATE TABLE customers (
	customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR(255) NOT NULL,
	last_name VARCHAR(255) NOT NULL,
	email VARCHAR(255) NOT NULL,
	address VARCHAR(255) DEFAULT NULL,
	city VARCHAR(255) DEFAULT NULL,
	state VARCHAR(2) DEFAULT NULL,
	zip_code VARCHAR(5) DEFAULT NULL,
);

CREATE TABLE orders (
	order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	customer_id INT NOT NULL,
	order_placed_date DATE NOT NULL,
	FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

It's the end of 2016, and the owner of Cocoa Confections wants to write an SQL query that finds the COUNT of orders placed in 2016 by customer e-mail address. She wants to ORDER the results by the COUNT of orders placed in 2016, descending, so that she can personally send thank-you e-mails to Cocoa Confection's top customers by order volume.

Can you write a query that will help the owner of Cocoa Confections find the COUNT of all orders placed in 2016, by customer e-mail address, sorted descending?

Hint: Combine tables

The owner of Cocoa Confections wants to find the COUNT of orders placed by e-mail address. But the email field is located within the customers table, not the orders table. What type of statement do we need to use to combine the two tables together?

Hint: Use an INNER JOIN

To combine our two tables together, we'll use a JOIN. Since the orders table has a customer_id field, we can match that against the customer_id in the customers table to link up the two separate data sources. We'll use an INNER JOIN so that data is only included if the customer_id listed matches both of our input tables. Try the following to get you started — it'll compile a list of all orders in our database with all requisite customer information appended:

SELECT
	*
FROM
	orders
INNER JOIN
	customers ON orders.customer_id = customers.customer_id;
Hint: Use COUNT(*)

Now that we have a complete list of orders with e-mail addresses included, we can use the COUNT(*) and GROUP BY functions to get a list of orders by e-mail address. This will return a COUNT of all orders in our database by e-mail address. We'll also restrict our SELECT clause so that it only pulls the e-mail address and the COUNT of orders from our database, excluding all other fields.

SELECT
	email, COUNT(*)
FROM
	orders
INNER JOIN
	customers on orders.customer_id = customers.customer_id
GROUP BY
	email;

/*
Results:
+------------------------------+----------+
| email                        | COUNT(*) |
+------------------------------+----------+
| adawson@yahoo.com            |        6 |
| aferguson@gmail.com          |       16 |
| amy.cummings@yahoo.com       |       13 |
...100 rows in set (0.01 sec)
*/
Hint: Use WHERE and ORDER BY

We're not done yet! We still need to restrict the dates of our pull to 2016 only. How do we filter for particular results in SQL? Using a WHERE clause! We'll also need to use an ORDER BY clause to ensure that our results are sorted descending by number of orders. These two clauses, combined, should look something like this:

WHERE
	order_placed_date BETWEEN CAST('2016-01-01' AS DATE) AND CAST('2016-12-31' AS DATE)
ORDER BY
	2016_num_orders DESC;

Note that we need to use the CAST function to ensure that our numerical inputs are properly interpreted as dates by MySQL.

Does your answer combine tables?

Your solution will need to combine the customers table with the orders table. Does it do so?

Do you use an INNER JOIN?

To combine the customers and orders tables together, an INNER JOIN query will be most effective. Does the solution you've created use an INNER JOIN statement?

Does your answer count total orders?

Don't forget that in order to find the total number of orders placed by e-mail address, you'll need to use SQL's COUNT(*) or SUM functions, which also necessitate a GROUP BY clause in your query. Does your solution contain those features?

Have you included results from only 2016 and ordered descending?

Don't forget that we're only pulling orders from the year 2016, so we'll need to restrict our pull to that year only. We'll also need to arrange our results in descending order by total COUNT. Have you got those features in your query?

The solution

Great! Sounds like you're on the right track, so let's move on to the solution.

Here's our full solution to the problem:

SELECT
	customers.email, COUNT(*) AS 2016_num_orders
FROM
	orders
INNER JOIN
	customers on orders.customer_id = customers.customer_id
WHERE
	orders.order_placed_date BETWEEN CAST('2016-01-01' AS DATE) AND CAST('2016-12-31' AS DATE)
GROUP BY
	customers.email
ORDER BY
	2016_num_orders DESC;

/*
Results:
+------------------------------+-----------------+
| email                        | 2016_num_orders |
+------------------------------+-----------------+
| doreen.glover@yahoo.com      |              14 |
| rpayne@hotmail.com           |              10 |
| fobrien@gmail.com            |              10 |
...99 rows in set (0.01 sec)
*/

Check out what's happening above:

First, we use a SELECT statement to pull specific fields from our database. We'll pull the email field, which is located on the customers table, and the aggregated COUNT(*) of records, using the AS clause to refer to this column as 2016_num_orders.

We'll use a FROM orders clause to note that we're pulling this data from our orders table. But since the email field isn't located on the orders table, we'll also need to perform an INNER JOIN linking the orders.customer_id field to the customers.customer_id field.

We'll use a WHERE clause to restrict our data range to 2016 only using two criteria: >= '2016-01-01' and <= '2016-12-31'.

Since we're using a COUNT(*) function to sum records, we'll need to use a GROUP BY clause to tell SQL that we want to group our results by email address.

Finally, we'll tell SQL that we want our results arranged by number of orders, from greatest to least, using ORDER BY.

Our query is complete!