SQL Interview Questions (MySQL): Part 1

Summary: The knowledge of SQL interview questions is important for any aspiring DBA. Join us as we walk you through some of the most popular interview questions in the SQL realm!

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client
The MySQL database version 8 or later

Walking into an interview soon? We know, you’re nervous. No matter if it’s your first or fourth developer job – the thoughts of everything involving the interview can be nerve-wracking. No worries – in this blog we’ve compiled some of the most popular SQL interview questions you can use to learn from yourself and teach your friends. Let’s roll!

Who is This Tutorial Targeting?

Before we start, you may be wondering who these interview question series are targeting in the first place. The answer is simple – the initial parts of these SQL interview question series will target more junior developers and aspiring DBAs, while other parts will target advanced developers and database administrators who have been in the industry for a while. This blog will target MySQL engineers, other blogs written by us will target DBAs working with other database management systems.

We will provide you SQL interview questions, you will familiarize yourself with them, and crush your next SQL interview. Our friends over at Database Dive also frequently release content around SQL interview questions, so make sure to subscribe to them on YouTube as well.

Ready? Let’s get into the questions!

The Questions

You walk into the room and an interviewer is sitting right across the table from you. “Oh no!”, – you think to yourself. Am I prepared? Walk yourself through the answers to the questions given below and you will surely be!

What are some of the DDL statements available in MySQL? What does this acronym stand for?

DDL statements are Data Definition Language statements. Such statements include queries that build upon (create), modify, or delete databases, tables, or views. Such statements would be as follows:

• CREATE
• ALTER
• DROP

What are some of the DML statements in MySQL? What does this acronym stand for?

• DML statements are Data Manipulation (some may call it Modification, premise is the same) Language statements. Such statements include statements that add, modify, or remove data from tables. Such statements are CRUD (Create, Read, Update, Delete) statements and in MySQL they are:
• INSERT
• SELECT
• UPDATE
• DELETE

What is an index in MySQL? What’s the most frequent index type in MySQL?

An index is a database structure used to quickly find rows (this explanation pertains to any DBMS.) The most frequent index type in MySQL is a B-tree index.

List some of the storage engines available in MySQL. Tell me a little about them.

The storage engines that are available within MySQL are as follows:

• InnoDB (the main storage engine.)
• MyISAM (obsolete as of MySQL 5.5.)
• MEMORY (stores data in memory.)
• CSV (works with CSV data files.)
• ARCHIVE (intended to be used as an archive.)
• BLACKHOLE (only accepts data but never stores it.)
• MERGE or MRG_MyISAM (identical MyISAM-based tables.)
• FEDERATED (lets you access data from a remote MySQL database.)
• EXAMPLE (to be used as an example.)

How do you check the version of your MySQL instance?

Use one of the following queries when logged in to your MySQL DB:

• SELECT version()
• SELECT @@version
• SHOW VARIABLES LIKE ‘%version%’;

How do you add columns to a MySQL database?

Use an ALTER TABLE query like so:

ALTER TABLE your_table ADDCOLUMN your_column DATATYPE [FIRST|
AFTER existing_column];

How to change the name of an existing column within MySQL?

Use an ALTER TABLE query like so:

ALTER TABLE your_table CHANGE COLUMN old_c new_c [datatype] [FIRST|AFTER column_x];

How to add a user to a MySQL database?

Run a CREATE USER query like so:

CREATEUSER'demo'@'localhost'IDENTIFIED BY'password';

How to rename a table in MySQL?

Use the RENAME TABLE statement:

RENAME TABLE a TO b;

How to remove rows from a database? How to remove all rows at once?

Row removal is done with a DELETE query. To remove all data, use TRUNCATE:

DELETEFROM table_name WHERE column_name = ‘x’;
TRUNCATE table_name;

How to insert data into MySQL?

Use an INSERT statement:

INSERT INTO demo_table (c1, c2) VALUES (‘column1 data’, ‘column2 data’);

Columns can be not specified if you’re inserting data into all of the columns at once. Also, INSERT statements can be done in bulk like so:

INSERT INTO demo_table VALUES (‘data’,’data’),(‘data’,’data’);

Describe the basics of joining tables and provide a SQL query.

JOINs help us acquire data from multiple tables in a single result set. To acquire data from multiple tables with a JOIN operation, use a query like so:

SELECT [columns] FROM [table_1]
INNER JOIN [table_2]
ON [table_1.column_name] = [table_2.column_name];

How to update tables in MySQL?

Use an UPDATE query like so:

UPDATE [table_name] 
SET [column_name] = ‘New Value’, [column2] = ‘New’;

What does a primary key do? How to implement it into a table?

A primary key automatically increments integer values by 1 every time a row is added to a table and a table can only have one primary key at a time. A primary key is usually implemented when creating a table by providing AUTO_INCREMENT PRIMARY KEY after the data type:
`id INT(12) AUTO_INCREMENT PRIMARY KEY,`

What is the default port number allocated to MySQL?

MySQL’s default port number is 3306.

Familiarize yourself with these SQL interview questions for junior MySQL DBAs and you should crush your next SQL interview!

DbVisualizer and SQL Interview Questions

You’re good to go! Well.. almost. You see, almost every SQL interview will ask you one simple question in addition: name a good SQL client. How to work with it? The answer to this question is plain and simple as well – DbVisualizer is a top-rated SQL client used by many companies across the globe. DbVisualizer has more than 28,000 customers in over 150 countries and supports many database management systems including MySQL, PostgreSQL, SQL Server, and many others. Redshift, Cassandra, ClickHouse, and others are also in the list.

We’ll let you in on a secret – click here and we will provide you with a 21-day free trial! Make sure to try DbVisualizer before you go into your next SQL interview and answer questions there – by doing so you will make sure that you’re way above other candidates wanting to work for the company.

Conclusion

This blog has provided you with some of the most frequent SQL interview questions for freshers and for junior developers alike. Take note of these questions and make sure to glance over them before you go into your next developer interview surrounding databases – make sure to come back to our SQL blog after a while to stay updated in the database world, check out what our friends over at Database Dive are doing as well, and until next time.

About the author

Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.