15 Basic MySQL Interview Questions for Database Administrators

Prior to This Article, three articles has already been published in ‘Linux Interview‘ Section and all of them were highly appreciated by our notable readers, however we were receiving feedback to make this interactive learning process, section-wise. From idea to action, we are providing you 15 MySQL Interview Questions.

Mysql Interview Questions
Mysql Interview Questions
1. How would you check if MySql service is running or not?
Answer : Issue the command “service mysql status” in ‘Debian’ and “service mysqld status” in RedHat. Check the output, and all done.
[email protected]:/home/avi# service mysql status

/usr/bin/mysqladmin  Ver 8.42 Distrib 5.1.72, for debian-linux-gnu on i486
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 5.1.72-2
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 1 hour 22 min 49 sec

Threads: 1  Questions: 112138  Slow queries: 1  Opens: 1485  Flush tables: 1  Open tables: 64  Queries per second avg: 22.567.
2. If the service is running/stop how would you stop/start the service?
Answer : To start MySql service use command as service mysqld start and to stop use service mysqld stop.
[email protected]:/home/avi# service mysql stop

Stopping MySQL database server: mysqld.

[email protected]:/home/avi# service mysql start

Starting MySQL database server: mysqld.

Checking for corrupt, not cleanly closed and upgrade needing tables..
3. How will you login to MySQL from Linux Shell?
Answer : To connect or login to MySQL service, use command: mysql -u root -p.
[email protected]:/home/avi# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 207 
Server version: 5.1.72-2 (Debian) 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql>
4. How will you obtain list of all the databases?
Answer : To list all currently running databases run the command on mysql shell as: show databases;
mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| a1                 | 
| cloud              | 
| mysql              | 
| phpmyadmin         | 
| playsms            | 
| sisso              | 
| test               | 
| ukolovnik          | 
| wordpress          | 
+--------------------+ 
10 rows in set (0.14 sec)
5. How will you switch to a database, and start working on that?
Answer : To use or switch to a specific database run the command on mysql shell as: use database_name;
mysql> use cloud; 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 

Database changed 
mysql>
6. How will you get the list of all the tables, in a database?
Answer : To list all the tables of a database use the command on mysql shell as: show tables;
mysql> show tables; 
+----------------------------+ 
| Tables_in_cloud            | 
+----------------------------+ 
| oc_appconfig               | 
| oc_calendar_calendars      | 
| oc_calendar_objects        | 
| oc_calendar_repeat         | 
| oc_calendar_share_calendar | 
| oc_calendar_share_event    | 
| oc_contacts_addressbooks   | 
| oc_contacts_cards          | 
| oc_fscache                 | 
| oc_gallery_sharing         | 
+----------------------------+ 
10 rows in set (0.00 sec)
7. How will you get the Field Name and Type of a MySql table?
Answer : To get the Field Name and Type of a table use the command on mysql shell as: describe table_name;
mysql> describe oc_users; 
+----------+--------------+------+-----+---------+-------+ 
| Field    | Type         | Null | Key | Default | Extra | 
+----------+--------------+------+-----+---------+-------+ 
| uid      | varchar(64)  | NO   | PRI |         |       | 
| password | varchar(255) | NO   |     |         |       | 
+----------+--------------+------+-----+---------+-------+ 
2 rows in set (0.00 sec)
8. How will you delete a table?
Answer : To delte a specific table use the command on mysql shell as: drop table table_name;
mysql> drop table lookup; 

Query OK, 0 rows affected (0.00 sec)
9. What about database? How will you delete a database?
Answer : To delte a specific database use the command on mysql shell as: drop database database-name;
mysql> drop database a1; 

Query OK, 11 rows affected (0.07 sec)
10. How will you see all the contents of a table?
Answer : To view all the contents of a particular table use the command on mysql shell as: select * from table_name;
mysql> select * from engines; 
+------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
| ENGINE     | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS | 
+------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        | 
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         | 
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         | 
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         | 
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         | 
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       | 
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         | 
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         | 
+------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
8 rows in set (0.00 sec)
11. How will you see all the data in a field (say, uid), from table (say, oc_users)?
Answer : To view all the data in a field use the command on mysql shell as: select uid from oc_users;
mysql> select uid from oc_users; 
+-----+ 
| uid | 
+-----+ 
| avi | 
+-----+ 
1 row in set (0.03 sec)
12. Say you have a table ‘xyz’, which contains several fields including ‘create_time’ and ‘engine’. The field ‘engine’ is populated with two types of data ‘Memory’ and ‘MyIsam’. How will you get only ‘create_time’ and ‘engine’ from the table where engine is ‘MyIsam’?
Answer : Use the command on mysql shell as: select create_time, engine from xyz where engine=”MyIsam”;
12. mysql> select create_time, engine from xyz where engine="MyIsam";

+---------------------+--------+ 
| create_time         | engine | 
+---------------------+--------+ 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-12-15 13:43:27 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
| 2013-10-23 14:56:38 | MyISAM | 
+---------------------+--------+ 
132 rows in set (0.29 sec)
13. How will you show all the records from table ‘xrt’ where name is ‘tecmint’ and web_address is ‘tecmint.com’?
Answer : Use the command on mysql shell as: select * from xrt where name = “tecmint” and web_address = “tecmint.com”;
mysql> select  * from xrt where name = "tecmint" and web_address = “tecmint.com”;
+---------------+---------------------+---------------+ 
| Id                  | name                   | web_address | 
+---------------+---------------------+----------------+ 
| 13                 |  tecmint               | tecmint.com  |
+---------------+---------------------+----------------+ 
| 41                 |  tecmint               | tecmint.com  |
+---------------+---------------------+----------------+
14. How will you show all the records from table ‘xrt’ where name is not ‘tecmint’ and web_address is ‘tecmint.com’?
Answer : Use the command on mysql shell as: select * from xrt where name != “tecmint” and web_address = “tecmint.com”;
mysql> select * from xrt where name != ”tecmint” and web_address = ”tecmint.com”;

+---------------+---------------------+---------------+ 
| Id            | name                | web_address   | 
+---------------+---------------------+----------------+ 
| 1173          |  tecmint            | tecmint.com   |
+---------------+---------------------+----------------+
15. You need to know total number of row entry in a table. How will you achieve it?
Answer : Use the command on mysql shell as: select count(*) from table_name;
mysql> select count(*) from Tables; 

+----------+ 
| count(*) | 
+----------+ 
|      282 | 
+----------+ 
1 row in set (0.01 sec)

Read Also : 10 MySQL Database Interview Questions Intermediates

That’s all for now. How you feel about this ‘Linux Interview Question‘ section. Don’t forget to provide us with your valuable feedback in our comment section.

If you liked this article, then do subscribe to email alerts for Linux tutorials. If you have any questions or doubts? do ask for help in the comments section.

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

Support Us

We are thankful for your never ending support.

22 thoughts on “15 Basic MySQL Interview Questions for Database Administrators”

  1. Well he tried hos best. The good thing, he web site, blog was on my top Google search. So he did something right.

    Let say thank you for taking the time and explaining something obvious unless u are new to MySQL.

    Adam

    Reply
  2. Sir,
    I want to become a DB tester. I have to test an application which is CRM (Salesforce) based. Could you please guide me to write DB testing scenarios for application.

    Thanks a lot

    Reply
  3. Seems that “How will you show all the records from table ‘xrt’ where name is not ‘tecmint’ and web_address is ‘tecmint.com’?” actually returns a record where name=’tecmint’….

    Very helpful site :)

    Niels

    Ps.
    The ‘helpful’ remark isn’t ment ironically ;)

    Reply
  4. Have to agree with all the others, this article is flawed You would not be asked to show such basic knowledge at an interview for a DBA postition.

    I notice that the author replied to the first comment that praised his work, but does not have the gumption to reply to comments that are critical of his work.

    Reply
  5. What a bunch of crap questions – a DBA should know SQL and the inner details of the DB the company users eg. MySQL in the above examples.
    All of the questions abover are for home use and nothing else.

    As Barak mentions then a DBA works with SQL, inner optimizations of the DB (storages engines/partitioning tables, master-master/master-slave etc.) setup.

    A DBA’s primary job is to make sure the DB is running optimally and help/assist developers in optimizing poorly written SQL queries and optimize indexes and implement backup that does not affect the systems performance and restore options etc.

    Poorly written questions for getting page hits. Please don’t use novice questions for job interview question examples. Please change the title to “How do I get to know my own personal MySQL server” or something like that.

    Reply
    • I would change the title to “Please tell me how great I am because I wrote a bunch of stupid questions that any novice could learn in less than 5 minutes.”

      That’s all the author is doing.

      Reply
  6. I’m afraid those are all novice/entry-level questions one would ask a junior web programmer, none of the question answers indicate any real DBA skills, about 80% of the questions concern generic user-level SQL syntax and answering them test’s the interviewee’s memory rather then his deep understanding of MySQL.
    If I was hiring a MySQL DBA, I would have to ask questions about the following issues:
    1. What are storage engines and what are the main differences between MyISAM and Innodb, Advanced DBAs would have to be able to name at lease one more storage engine and specify what could it be used for.
    2. Disk space management (DBA candidate would absolutely have to know about innodb_files_per_table)
    3. Memory management
    4. SQL Execution Plan analysis and and tuning (I would expect developers to know this too)
    5. How to tell what MySQL is doing right now and when stuff is going slow
    6. How to do backups and restores
    7. How MySQL replication works and how to set it up
    8. Data security – How do I prevent access to specific data, how to store sensitive information such as passwords and how to prevent SQL injection.

    Reply
    • Exactly. These “questions for interviews” articles are nothing but click bait so he can get attaboys from his friends. The author doesn’t care one whit about producing quality articles or responding to people who politely point out that his articles contain factual errors.

      I had hoped that politely pointing out the errors would result in improvements in the quality of the articles here, because I admire people who take the time to explain things to newcomers, but instead it resulted in the author complaining about people being critical of him. Apparently, if you don’t care to feed his ego and point out that his articles need correcting, you are being too negative.

      Based on the content of his articles alone, I wouldn’t hire this guy even as an entry level admin. He obviously thinks he knows way more than he actually does.

      Reply

Got something to say? Join the discussion.

Have a question or suggestion? Please leave a comment to start the discussion. Please keep in mind that all comments are moderated and your email address will NOT be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.