If you are looking for a DBA(Database Administrator) Job, You must have good knowledge about Databases. Here, I have stated some most common MySQL Interview Questions which will help you to go through your interviews.
1) Compare MySQL with SQL Server.
|Programmed In||C and C++||Mainly C++, but some parts in C|
|Platforms||Supports many platforms||Supports only Linux and Windows|
|Syntax||Complex Syntax||Simpler and easy-to-use syntax|
2) What is SQL Server?
The SQL server is one of the famous and user friendly database management system [DBMS]. It is developed and designed by Microsoft. Database management systems are the computer system applications which can interact with users and various Database, too. Main objectives of SQL Servers are as follows:
- Analysing Data
- Managing Definition and query
- Creation, updating and administration of Database
3) Which port is set as default for MySQL Server?
3306 is the default port for MySQL Server. 1433 is another standard default port used in TCP/IP for SQL Server.
4) Why is SQL server famous in people?
User can download SQL Server for free and use it for developing SQL based applications. It is easy to understand and use as a user.
5) Describe the features of MySQL.
MySQL provides a wide range of interface and supports different cross-platforms for application programming. It supports many procedures like cursors and triggers that can be used for managing the Database.
6) Explain in brief DDL, DCL and DML.
DDL stands for data definition language which deals with different database schemas and descriptions about how Database resides in the Database. Create and ALTER table is the example of a DDL statement.
DCL stands for the data control language, which deals with different types of permissions for users.GRANT and REVOKE are examples of Data Control Language.
DML stands for data manipulation language, which deals with different manipulation statements like INSERT, SELECT and UPDATE of the data.
7) Explain different types of joints in MySQL?
We can use joints for querying data from two or more tables. We can make a relationship between two or more column of existing tables. Four types of joints are used in MySQL. They are as follows:
- Inner Join: It returns rows if one or more match found in both the tables.
- Left Join: It returns all rows of the left side table if there is no match in the right side table.
- Right Join: It returns all rows of right side table if there is no match in the left side table.
- Full Join: It returns rows if there is at least one match in the tables
8) What is the main difference between char and varchar type?
When the user creates a table char type is used to define the fixed length of columns in the table. The length value must be in the range of 1-255. The varchar is the variable-length type which is used to adjust the column length of the table as per requirements.
9) Explain the standard MySQL functions CURRDATE, CURTIME, CONCATE, DATEDIFF and NOW?
- CURRDATE(): The function returns the current date
- CURTIME(): The function returns the current time
- CONCAT (X, Y): The function concatenates two string values and creates a single string output
- NOW(): The function is used for returning the current date and time as a single value
- DATEDIFF (X, Y): The function returns the difference between two dates
10) Write syntax for concatenating tables in MySQL?
The syntax for concatenating tables in MySQL:
CONCAT (string 1, string 2, string 3)
Here string 1, string 2 and string 3 are strings which we want to concate as a string.
11) How can we get the current SQL version?
The below syntax is used for getting the current version of MySQL:
SELECT VERSION ();
12) Which types of strings are used in database columns in MySQL?
In MySQL, We can use SET, BLOB, VARCHAR, TEXT, ENUM, and CHAR type of strings.
13) What is the limit of indexed columns created for a table?
The maximum limit of indexed columns that can be created for any table is 16.
14) Is it possible to use MySQL With Linux operating system?
Yes, We can use MySQL with the Linux operating system as follows:
15) The object-oriented version of MySQL library function is possible or not?
Yes, Object-oriented version of MySQL is possible. MYSQLi is one of the Object-oriented version of MySQL library function.
16) What is the main difference between the primary key and candidate key?
The primary key is used to identify each row of a table uniquely. There can be more than one primary key. We can use the candidate key to reference the foreign keys. The primary key is one type of candidate key.
17) Explain the different types of tables in MySQL?
There are four different types of tables mainly used in MySQL.
- Heap: This table is used for fast data access, but the main disadvantage is that data will be lost if the table or the system crashes.
- MyISAM: It is the default table that is based on the sequential access method.
- InnoDB: It is the table that supports transactions using the COMMIT and ROLLBACK commands.
- BDB: It can support transactions similar to InnoDB, but the execution is slower.
18) What are Heap Tables?
Heap tables are used for high-speed temporary storage. But, TEXT and BLOB fields are not allowed within them. They do not support AUTOINCREMENT.
19) How can the user take an incremental backup in MySQL?
Percona XtraBackup is used to take an incremental backup in MySQL.
20) What is the primary use of ENUM in MySQL?
ENUM can limit the values that can go into a table. For instance, we can create a table giving specific day values and other day values would not enter into the table
21) Which of the TRIGGERS can be used in MySQL tables?
Following TRIGGERS are allowed in MySQL:
- BEFORE INSERT
- AFTER INSERT
- BEFORE DELETE
- AFTER DELETE
- BEFORE UPDATE
- AFTER UPDATE
22) Explain the difference between REGEXP and LIKE operators in MySQL?
- LIKE is denoted using the ‘^’ sign.
- SELECT * FROM user WHERE username REGEXP “^NAME”;
- LIKE is denoted using the ‘%’ sign. For example:
- SELECT * FROM user WHERE user name LIKE “%NAME.”
23) How can the user change the root password if it is lost?
When the password of the user is lost, the user should start the Database with skip-grants-table and then change the password. After that, the user should restart the DB with the new password in a normal mode.
24) What is the main difference between the DELETE TABLE and TRUNCATE TABLE commands in MySQL?
DELETE TABLE is a logged type operation, and every deleted row is logged. Therefore, the process is prolonged.
TRUNCATE TABLE also deletes rows in a table, but it will not log any of the deleted rows.
So, the process is faster than DELETE TABLE in comparison. We can also roll back TRUNCATE TABLE, and it functionally is similar to the DELETE statement without a WHERE clause.
25) What are the primary storage models of OLAP?
The storage models in OLAP are as follows:
I hope you all get enough knowledge of MySQL with our MySQL Interview Questions.
Also read: Top 25 SQL Queries Interview Questions