Home › Database › MySQL › 15 Basic MySQL Interview Questions: Are DBAs Ready?

15 Basic MySQL Interview Questions: Are DBAs Ready?

Categories: MySQL 2014-01-07 root Comments (0)

We have previously published three articles on basic Linux interview Q&A — Part 1, Part 2, and Part 3 — which were well received by our readers. We also got feedback that some readers hope this interactive learning method can be made more flexible. Actions speak louder than words, so here we bring you 15 MySQL Interview Questions.

Question 1: How Do You Determine if MySQL Is Running?

Answer: On Debian, run the command service mysql status. On RedHat, run service mysqld status. Then check the output.

 

·········10········20········30········40········50········60········
01.root@localhost:/home/avi# service mysql status
02. 
03./usr/bin/mysqladmin  Ver 8.42 Distrib 5.1.72, for debian-linux-gnu on i486
04.Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
05. 
06.Oracle is a registered trademark of Oracle Corporation and/or its
07.affiliates. Other names may be trademarks of their respective
08.owners.
09. 
10.Server version 5.1.72-2
11.Protocol version 10
12.Connection Localhost via UNIX socket
13.UNIX socket /var/run/mysqld/mysqld.sock
14.Uptime: 1 hour 22 min 49 sec
15. 
16.Threads: 1  Questions: 112138  Slow queries: 1  Opens: 1485  Flush tables: 1  Open tables: 64  Queries per second avg: 22.567.

Question 2: How Do You Start or Stop the MySQL Service?

Answer: Run service mysqld start to start the service; run service mysqld stop to stop the service.

 

·········10········20········30········40········50········60········
01.root@localhost:/home/avi# service mysql stop
02. 
03.Stopping MySQL database server: mysqld.
04. 
05.root@localhost:/home/avi# service mysql start
06. 
07.Starting MySQL database server: mysqld.
08. 
09.Checking for corrupt, not cleanly closed and upgrade needing tables..

Question 3: How Do You Log Into MySQL via Shell?

Answer: Run the command mysql -u root -p

 

·········10········20········30········40········50········60········
01.root@localhost:/home/avi# mysql -u root -p
02.Enter password:
03.Welcome to the MySQL monitor.  Commands end with ; or /g.
04.Your MySQL connection id is 207
05.Server version: 5.1.72-2 (Debian)
06. 
07.Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
08. 
09.Oracle is a registered trademark of Oracle Corporation and/or its
10.affiliates. Other names may be trademarks of their respective
11.owners.
12. 
13.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.
14. 
15.mysql>

Question 4: How Do You List All Databases?

Answer: Run the command show databases;

 

·········10········20········30········40········50········60········
01.mysql> show databases;
02.+--------------------+
03.| Database           |
04.+--------------------+
05.| information_schema |
06.| a1                 |
07.| cloud              |
08.| mysql              |
09.| phpmyadmin         |
10.| playsms            |
11.| sisso              |
12.| test               |
13.| ukolovnik          |
14.| wordpress          |
15.+--------------------+
16.10 rows in set (0.14 sec)

Question 5: How Do You Switch to a Database and Work on It?

Answer: Run the command use database_name; to enter the database named database_name.

 

·········10········20········30········40········50········60········
1.mysql> use cloud;
2.Reading table information for completion of table and column names
3.You can turn off this feature to get a quicker startup with -A
4. 
5.Database changed
6.mysql>

Question 6: How Do You List All Tables in a Database?

Answer: In the current database, run the command show tables;

 

·········10········20········30········40········50········60········
01.mysql> show tables;
02.+----------------------------+
03.| Tables_in_cloud            |
04.+----------------------------+
05.| oc_appconfig               |
06.| oc_calendar_calendars      |
07.| oc_calendar_objects        |
08.| oc_calendar_repeat         |
09.| oc_calendar_share_calendar |
10.| oc_calendar_share_event    |
11.| oc_contacts_addressbooks   |
12.| oc_contacts_cards          |
13.| oc_fscache                 |
14.| oc_gallery_sharing         |
15.+----------------------------+
16.10 rows in set (0.00 sec)

Q7: How to Get the Names and Types of All Fields in a Table?

Answer: Run the command describe table_name;

 

·········10········20········30········40········50········60········
1.mysql> describe oc_users;
2.+----------+--------------+------+-----+---------+-------+
3.| Field    | Type         | Null | Key | Default | Extra |
4.+----------+--------------+------+-----+---------+-------+
5.| uid      | varchar(64)  | NO   | PRI |         |       |
6.| password | varchar(255) | NO   |     |         |       |
7.+----------+--------------+------+-----+---------+-------+
8.2 rows in set (0.00 sec)

Q8: How to Delete a Table?

Answer: Run the command drop table table_name;

 

·········10········20········30········40········50········60········
1.mysql> drop table lookup;
2. 
3.Query OK, 0 rows affected (0.00 sec)

Q9: How to Delete a Database?

Answer: Run the command drop database database-name;

 

·········10········20········30········40········50········60········
1.mysql> drop database a1;
2. 
3.Query OK, 11 rows affected (0.07 sec)

Q10: How to View All Data in a Table?

Answer: Run the command select * from table_name;

 

·········10········20········30········40········50········60········
01.mysql> select * from engines;
02.+------------+---------+----------------------------------------------------------------+--------------+------+------------+
03.| ENGINE     | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS |
04.+------------+---------+----------------------------------------------------------------+--------------+------+------------+
05.| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
06.| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
07.| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
08.| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
09.| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
10.| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
11.| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
12.| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
13.+------------+---------+----------------------------------------------------------------+--------------+------+------------+
14.8 rows in set (0.00 sec)

Q11: How to Get All Data for a Single Field (e.g., uid) from a Table (e.g., oc_users)?

Answer: Run the command select uid from oc_users;

 

·········10········20········30········40········50········60········
1.mysql> select uid from oc_users;
2.+-----+
3.| uid |
4.+-----+
5.| avi |
6.+-----+
7.1 row in set (0.03 sec)

Q12: Suppose You Have a Table Named ‘xyz’ with Multiple Fields like ‘create_time’ and ‘engine’. The ‘engine’ Field Contains Values ‘Memory’ and ‘MyISAM’. How to List Only the ‘create_time’ and ‘engine’ Columns Where ‘engine’ Equals ‘MyISAM’?

Answer: Run the command select create_time, engine from xyz where engine = ”MyIsam”;

 

·········10········20········30········40········50········60········
01.mysql> select create_time, engine from xyz where engine="MyIsam";
02. 
03.+---------------------+--------+
04.| create_time         | engine |
05.+---------------------+--------+
06.| 2013-12-15 13:43:27 | MyISAM |
07.| 2013-12-15 13:43:27 | MyISAM |
08.| 2013-12-15 13:43:27 | MyISAM |
09.| 2013-12-15 13:43:27 | MyISAM |
10.| 2013-12-15 13:43:27 | MyISAM |
11.| 2013-12-15 13:43:27 | MyISAM |
12.| 2013-12-15 13:43:27 | MyISAM |
13.| 2013-12-15 13:43:27 | MyISAM |
14.| 2013-10-23 14:56:38 | MyISAM |
15.| 2013-10-23 14:56:38 | MyISAM |
16.| 2013-10-23 14:56:38 | MyISAM |
17.| 2013-10-23 14:56:38 | MyISAM |
18.| 2013-10-23 14:56:38 | MyISAM |
19.| 2013-10-23 14:56:38 | MyISAM |
20.| 2013-10-23 14:56:38 | MyISAM |
21.+---------------------+--------+
22.132 rows in set (0.29 sec)

Q13: How to List All Data from Table ‘xrt’ Where ‘name’ Equals ‘tecmint’ and ‘web_address’ Equals ‘tecmint.com’?

Answer: Run the command select * from xrt where name = “tecmint” and web_address = “tecmint.com”;

 

·········10········20········30········40········50········60········
1.mysql> select  * from xrt where name = "tecmint" and web_address = “tecmint.com”;
2.+---------------+---------------------+---------------+
3.| Id                  | name                   | web_address |
4.+---------------+---------------------+----------------+
5.| 13                 |  tecmint               | tecmint.com  |
6.+---------------+---------------------+----------------+
7.| 41                 |  tecmint               | tecmint.com  |
8.+---------------+---------------------+----------------+

Question 14: How to List All Data from Table ‘xrt’ Where the name Field Is Not ‘tecmint’ and the web_address Field Is ‘tecmint.com’?

Answer: Run the command select * from xrt where name != "tecmint" and web_address = "tecmint.com";

 

·········10········20········30········40········50········60········
1.mysql> select * from xrt where name != ”tecmint” and web_address = ”tecmint.com”;
2. 
3.+---------------+---------------------+---------------+
4.| Id            | name                | web_address   |
5.+---------------+---------------------+----------------+
6.| 1173          |  tecmint            | tecmint.com   |
7.+---------------+---------------------+----------------+

Question 15: How to Know the Number of Rows in a Table?

Answer: Run the command select count(*) from table_name;

 

·········10········20········30········40········50········60········
1.mysql> select count(*) from Tables;
2. 
3.+----------+
4.| count(*) |
5.+----------+
6.|      282 |
7.+----------+
8.1 row in set (0.01 sec)

That’s the full content of this article. Did this ‘Linux Interview Questions‘ article help you? Don’t forget to leave your valuable feedback in the comments below.

via: http://www.tecmint.com/basic-mysql-interview-questions-for-database-administrators/

Related Posts

Leave a Comment

Your email address will not be published.