MySQL is the most popular and most used open-source relational database management system. If you're a programmer then the knowledge of MySQL is essential for you because MySQL can be used with every programming language. Whenever you will go for the job interview, questions related to MySQL on top. So here is the list of the most asked MySQL interview questions with answers for both beginners and experienced.
MySQL is the world's most popular open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). With its simplicity, performance, reliability and ease-of-use, MySQL has become the first database choice for most of web-based applications.
There are following MySQL Table Types, or Storage Engines are used in MySQL. While InnoDB is the default MySQL storage engine.
InnoDB and MyISAM are two most commonly used storage engines of MySQL database. The InnoDB is the default storage engine chosen by MySQL database, when creating a new table. The major differences between these two storage engines are:
The tables created in MyISAM are believes to have higher speed compared to tables in InnoDB. As the InnoDB supports volume, transactions, integrity, it’s always a better option which you are dealing with a larger database to choose storage engine.
A primary key is a column or a combination of columns that is uniquely identifying each row. There is only one primary key for a table.
A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. As there can be only one primary key for a table, there can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.
With the MySQL SUBSTR() function, we can extracts a substring from a string. the below query will return 10 character subtring from position 1.
We can use CONCAT() MySQL function to concatenate two or more column values. The below query will return three columns into one "Address" column
We can use the INSTR() MySQL function to get the position of the first occurrence of a string in another string. The below query will return the search for "COM" in string "phpzag.com", and return position.
You can use below query with the table name see all index used in table.
You can use CONVERT MySQL function to switch between different types of encoding.
1. What is MySQL
MySQL is the world's most popular open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). With its simplicity, performance, reliability and ease-of-use, MySQL has become the first database choice for most of web-based applications.
2. What are the MySQL Table Types, or Storage Engines?
There are following MySQL Table Types, or Storage Engines are used in MySQL. While InnoDB is the default MySQL storage engine.
3. What are the main difference between InnoDB and MyISAM
InnoDB and MyISAM are two most commonly used storage engines of MySQL database. The InnoDB is the default storage engine chosen by MySQL database, when creating a new table. The major differences between these two storage engines are:
- InnoDB supports transactions which is not supported by tables which use MyISAM storage engine.
- InnoDB has row-level locking, relational integrity supports foreign keys, which is not possible in MyISAM.
- InnoDB performance for high volume data cannot be beaten by any other storage engines available.
The tables created in MyISAM are believes to have higher speed compared to tables in InnoDB. As the InnoDB supports volume, transactions, integrity, it’s always a better option which you are dealing with a larger database to choose storage engine.
4. What is Primary Key in MYSQL
A primary key is a column or a combination of columns that is uniquely identifying each row. There is only one primary key for a table.
5. What is the Candidate Key in MySQL
A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. As there can be only one primary key for a table, there can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.
6. How do you get a portion of a string in MySQL
With the MySQL SUBSTR() function, we can extracts a substring from a string. the below query will return 10 character subtring from position 1.
SELECT SUBSTR(EmpName, 1, 10) FROM Employee;7. How do you concatenate strings in MySQL?
We can use CONCAT() MySQL function to concatenate two or more column values. The below query will return three columns into one "Address" column
SELECT CONCAT(Address, " ", PostalCode, " ", City) AS Address FROM Employee;8. How do you get a position of a string in MySQL
We can use the INSTR() MySQL function to get the position of the first occurrence of a string in another string. The below query will return the search for "COM" in string "phpzag.com", and return position.
SELECT INSTR("phpzag.com", "com") AS position;9. How can you see all indexes defined for a table?
You can use below query with the table name see all index used in table.
SHOW INDEX FROM table10. Convert output of MySQL query to utf8?
You can use CONVERT MySQL function to switch between different types of encoding.
SELECT column1, CONVERT(column2 USING utf8) FROM my_table;You may also check:
- PHP Interview Questions and Answers
- CodeIgniter Interview Questions and Answers
- Zend Framework Interview Questions and Answers
- Magento Interview Questions and Answers
- HTML5 interview questions and answers
- Top WordPress Interview Questions and Answers
- Angular Interview Questions and Answers
- jQuery Interview Questions and Answers
- Laravel Interview Questions and Answers