49 MySQL Interview Questions With Answers | Download PDF

Today we're having a huge collection of MySQL interview questions to help both the freshers and experienced people searching for job. These questions are literally important regarding interview process. You might be asked one of the in the future or similar to one of them. Go through them and submit your own questions asked in the interview.

mysql interview questions with answers

1. What Is MySQL?
MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing content in a database. It is most noted for its quick processing, proven reliability, ease and flexibility of use. (Source)

2. Why MySQL Is Used?

Some of the top reasons are given below.

• Scalability and Flexibility
• High Performance
• High Availability
• Web and Data Warehouse Strengths
• Robust Transactional Support
• Comprehensive Application Development
• Strong Data Protection
• Management Ease
• Lowest Total Cost of Ownership
• Open Source Freedom and 24 x 7 Support

3. What Are The Technical Features Of MySQL?
You can have a complete list of technical features of MySQL from here.

4. What is the default port for MySQL Server?

The default port for MySQL server is 3306.

5. What Are Heap Tables?

Tables that are present in the memory are called as HEAP tables. When creating a HEAP table in MySql, user needs to specify the TYPE as HEAP. These tables are now more commonly known as memory tables. These memory tables never have values with data type like “BLOB” or “TEXT”. They use indexes which make them faster.

6. Difference Between Float And Double?

They both represent floating point numbers. A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers.
MySQL uses four bytes for single-precision values and eight bytes for double-precision values. (Source)

7. CHAR_LENGTH And LENGTH?

CHAR_LENGTH, as the name suggests, returns the number of characters / character count. The LENGTH returns the number of bytes / bytes count. To count the Latin characters, both lengths are the same. To count Unicode and other encodings, the lengths are different.

8. What are the advantages of MySQL over Oracle? 

• Scalability
• High Availability
• DR capabilities
• Performance
• Performance tuning capabilities

9. What Is BLOB?
A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB , BLOB , MEDIUMBLOB , and LONGBLOB . These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT , TEXT , MEDIUMTEXT , and LONGTEXT .

10. How Many Triggers Are Allowed In MySQL Table?

Six triggers are allowed in Mysql table:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

11. What is TEXT?

It's an case-insensitive BLOB. There are four types of TEXT:

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

12. Difference Between BLOB And Text?

BLOB is used for storing binary data while Text is used to store large string.

13. How are MySQL timestamps seen to a user?

In a readable format : YYYY-MM-DD HH:MM:SS.

14. How would you get the current date in Mysql?

By using command SELECT CURRENT_DATE();

15. How one can concatenate strings in MySQL?

By using CONCAT (string1, string2, string3)

16. How do you control the max size of a HEAP table?

Using MySQL config variable called max_heap_table_size.

17. How would you enter Characters as HEX Numbers?

Enter HEX numbers with single quotes and a prefix of (X).

18. You need to show all the indexes defined in a table say ‘user’ of Database say ‘Mysql’. How will you achieve this?

Using mysql> show index from user;

19. How will you export tables as an XML file in MySQL?

Using command mysql -u USER_NAME –xml -e 'SELECT * FROM table_name' > table_name.xml

20. How will you get current date in MySQL?

Using mysql> SELECT CURRENT_DATE();

21. You want to see only certain rows from a result set from the beginning or end of a result set. How will you do it?

Using Command mysql> SELECT * FROM name LIMIT 1; (To Show 1 Record) (Source)

22. Login In MySQL With Unix Shell?

# [mysql dir]/bin/mysql -h hostname -u root -p pass

23. Create a database on the mysql server with unix shell.  

mysql> create database databasename;

24. How you will list or view all databases from the mysql server?

mysql> show databases;

25. How to use a database?

mysql> use databasename;

26. See all the tables from a database of mysql server.

mysql> show tables;

27. How to see table’s field formats or description of table .

mysql> describe tablename;

28. Delete a database from mysql server.

mysql> drop database databasename;

29. How you will get Sum of column?

mysql> SELECT SUM(*) FROM [table name];

30. How to delete a table?

mysql> drop table tablename;

31. Show all data from a table.

mysql> SELECT * FROM tablename;

32. How to returns the columns and column information pertaining to the designated table?

mysql> show columns from tablename;

33. Show certain selected rows with the value “xyz”.

mysql> SELECT * FROM tablename WHERE fieldname = “xyz”;

34. How you will show unique records?

mysql> SELECT DISTINCT columnname FROM tablename;

35. Show selected records sorted in an ascending or descending order?

mysql> SELECT col1,col2 FROM tablename ORDER BY col2 DESC;

mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC;

36. How to Return total number of rows?

mysql> SELECT COUNT(*) FROM tablename;

37. How to Change a users password from unix shell?

[mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

38. How to Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server?

/etc/init.d/mysql stop 
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop

# /etc/init.d/mysql start  (Source)

39. Restore database from backup.

[mysql dir]/bin/mysql -u username -password databasename < /tmp/databasename.sql.

40. How to dump a table from a database?

[mysql dir]/bin/mysqldump -c -u username -password databasename tablename > /tmp/databasename.tablename.sql

41. How to dump one database for backup?

[mysql dir]/bin/mysqldump -u username -password –databases databasename >/tmp/databasename.sql

42. How to dump all databases for backup. Backup file is sql commands to recreate all db’s?

[mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

43. How to Load a CSV file into a table?

mysql> LOAD DATA INFILE ‘/tmp/name.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ (field1,field2,field3);

44. How to Update database permissions?

mysql> flush privileges;

45. List the objects that can be created using CREATE statement?

Below objects can be created using CREATE statement:

• DATABASE
• EVENT
• FUNCTION
• INDEX
• PROCEDURE
• TABLE
• TRIGGER
• USER
• VIEW

46. How many columns can be used for creating Index?

Maximum of 16 indexed columns can be created for any standard table.

47. What is InnoDB?

InnoDB is a storage engine for MySQL. MySQL 5.5 and later use it by default. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity). (Source: Wikipedia)

48. What are the drivers in MySQL?

• PHP Driver
• JDBC Driver
• ODBC Driver
• C WRAPPER
• PYTHON Driver
• PERL Driver
• RUBY Driver
• CAP11PHP Driver
• Ado.net5.mxj

49. What is SQLyog?

The SQLyog program is Software which provide GUI tool for MySQL.

Click Here To Download PDF File

So this was all about mysql interview questions and answers. If you've faced any interview then submit your interview questions below in the comment section. For any doubt or query feel free to leave a comment.

Tweet

Subscribe to receive free email updates: