MySQL DBA Interview Questions and Answers

MY SQL is a relational database management system that utilizes SQL to add and manage content in a database. The role of DBA is installation, migration, database design, security, and performance monitoring. MY SQL DBA job has exponential growth with a long life career. MY SQL DBA jobs require experience in all type of SQL, knowledge on limitations of MY SQL, performance tuning, and Database optimization. MY SQL DBA jobs offer various placements as Lead SQL DBA, senior system analyst, Senior Database Administrator in many reputed IT organizations designed MY SQL DBA job interview questions and answers page to enhance your interview clearing capabilities. Register with us to have a broad view of all latest job notifications.

1.Can you tell the difference between Mysql_connect And Mysql_pconnect?

Ans:

Mysql_connect	Mysql_pconnect
Used to open a new connection to a database.	Used to open a persistent connection in a database
You can open and close the database connection based on the request.	You cannot close the database connection.
Opens a page everytime the page is loaded.	There is no need to open and close a connection every time a page is loaded.

2.What is the default port for MySQL server?

Ans:

MySQL Server’s default port is 3306. Apart from this, another standard default port for the SQL Server in TCP/IP is 1433.

3.Can you tell what are the different set operations available in MySQL?

Ans:

The various set operations available in MySQL are as follows:

• UNION – This operation returns all the distinct rows selected by a query
• UNION ALL – This operation returns all the rows selected by a query and also includes all duplicate rows.
• MINUS – This operation returns all the distinct rows selected by the first query but does not select the rows selected by the second query.
• INTERSECT – This operation returns all the distinct rows selected by both queries.

4.Can you tell the order of SQL SELECT statement?

Ans:

The order of SQL SELECT statement is as follows:

5.What is Database White Box Testing?

Ans:

The Database Whitebox Testing deals with the tables, data model, schema and referential integrity rules. It also deals with the triggers, logical views with database consistency and ACID properties.

6.What is Database Black Box Testing?

Ans:

Database Black Box Testing deals with data mapping, data storing and retrieving. The Database Black Box Testing is used for techniques such as Equivalence Partitioning and Boundary Value Analysis. 

7.What is CTE?

Ans:

An expression which consists of a temporary set of results defined in a SQL statement is said to be a Common Table Expression(CTE).

8.What are the different tables present in Mysql?

Ans:

There are mainly five types of tables present in MySQL. Out of all these database engines, the default database engine used in MySQL is MyISAM. Refer below to know the five types of tables:

• MyISAM
• Heap
• Merge
• INNO DB
• ISAM

9.What is a Cursor?

Ans:

Considered as a pointer to point to one row in a set of rows, a Cursor is nothing but a control which enables traversal over the records in the table. So, the cursor is used for performing traversing actions such as addition, retrieval, and removal of records in a database.

10.How can you test for NULL values in a database?

Ans:

A NULL value is a field with no value present in that particular field. Since the NULL value cannot be compared to any other NULL values, you cannot use the comparison operators such as =, <, or <>. To compare the fields with NULL values, you have to use the IS NULL and IS NOT NULL operator.

Refer below for Syntax of IS NULL and IS NOT NULL.

11.Can you elaborate on BLOB and TEXT in MySQL?

Ans:

BLOB

BLOB(Binary Large Object.is used to hold a variable amount of data and holds up to 65,535 bytes of data. The following are the four types of BLOB.

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

TEXT

TEXT is used to store string values and holds up to a maximum length of 65,535 characters. The following are the four types of TEXT

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

12.Can you tell how can you display the Maximum salary in SQL?

Ans:

To display the maximum salary in SQL, you can use the inbuilt function called MAX().

13.What is the difference between the NVL function, IFNULL function, and the ISNULL function?

Ans:

The NVL function, IFNULL function, and the ISNULL function all of them are used to replace the NULL value with another value. The ORACLE users use the NVL function, MySQL users use the IFNULL function and the SQL servers use the ISNULL function

For example, let us say we have a column(column_3.which has NULL values.

So, if you run the below statement, the output you would get is a NULL value.

Now, to overcome this, you can use the above three functions as follows:

14.What is the difference between GUI Testing and Database Testing?

Ans:

GUI Testing	Database Testing
Also known as User Interface Testing of Front-end Testing.	Also known as Back-End Testing or Data Testing.
Deals with items that interact with users.	Deals with items that are hidden from users.
Testers need not know SQL.	Testers need to know SQL.
GUI Testing focuses on the outlook of the application	Database Testing focuses on the integrity of data in the front end with the data present in the back end

15.How To Display Nth Highest Salary From A Table In A Mysql Query?

Ans:

Consider the table named “Employee”.

Now, to find the Nth salary consider the below statement.

So, if you want to find out the 7th largest salary, consider the below query.

16.What is the command used to create a database using PHP and MySQL?

Ans:

The command used to create a database using both PHP and MySQL is

17.Can you tell the  Difference Between Mysql_fetch_object And Mysql_fetch_array?

Ans:

Both of them are similar but vary with a single difference. Mysql_fetch_object return as object and Mysql_fetch_array returns an array. This means that you cannot access the data by their offsets but can only access through its fields names.

18.What are the ways in which you can retrieve data in the result set of MySQL using PHP?

Ans:

The different ways in which you can retrieve data in the result set of MySQL using PHP are as follows:

• mysql_fetch_object: This constant fetches a result row as an object.
• mysql_fetch_array: This constant fetches a result row as an associative array, numeric array or as both.
• mysql_fetch_row: This constant gives us a result row as an enumerated array.
• mysql_fetch_assoc: This constant gives us a result row as an associative array.

19.Can you tell how many values can Set the function of MySQL to consider?

Ans:

MySQL’s Set function can take a maximum of 64 values, but can also consider 0 values.

20.Can you tell the reasons for selecting Lamp(Linux, Apache, MySQL, PHP.instead of any other combination of software programs, servers, and operating system?

Ans:

The reason behind selecting Lamp stack is very simple. Linux, Apache, MySQL, PHP are open source software. The security of the Linux operating system is much more than Windows.  The Apache server is a better server than others in the perspective of functionalities and security. MySQL is one of the most popular open source databases is used with PHP to perform various functionalities.

21.Can you tell a way to know the number of days between the two given dates in PHP?

Ans:

You can simply declare the two dates, and then use the strtotime function to subtract both the dates and find the differences between the days in seconds.

Consider the below example.

22.Can you tell how to find the number of rows in a resultset using PHP?

Ans:

You can use the mysql_num_rows function to find the number of rows in a resultset.

Consider the below example.

23.What are the functions used to encrypt and decrypt the data present in MySQL?

Ans:

The function used to encrypt the data is AES_ENCRYPT(.and the function used to decrypt the data is AES_DECRYPT().

24.If you wish to encrypt the username and password using PHP, how will you do that? 

Ans:

You can encrypt the username and password using the following functions respectively:

25.How can you increase the performance of MySQL SELECT query?

Ans:

The SELECT statement is used to select data from a database and the data returned is stored in a result table, called the result-set. The SELECT statement can be either individually used or can be used with other statements such as ORDER BY, GROUP BY, and HAVING clause.

To increase the performance of a MySQL SELECT query, you can use the LIMIT clause to limit MySQL from further search in a table, after collecting the required number of records. Apart from this, we can also use the LEFT JOIN or the RIGHT JOIN to retrieve data from two or more tables.

26.Can you tell the difference between $message and $$message?

Ans:

$message and $$message are both PHP variables. $message is used to store the variable data and $$message is used to store the variable of a variable. So basically, data is stored in $message and $$message is used to store the data that can be changed dynamically.

27.Write a program using the SELECT statement, While Loop?

Ans:

You can write a program to select the students details from the student table and use the loop to just print the name of students.

28.How can you take the backup and restore a MySQL database using PHP? 

Ans:

MySQL comes with a utility mysqldump to provide the database backup and restore. The command you can use for backup and restore are as follows respectively.

//To take the backup of databasemysqldump database > backup -file.sql;//To restore the databasemysqldump database < backup -file.sql;

You can also use the phpMyAdmin user interface to backup your database. If you wish to backup, the database you just have to click on the “export” link on the phpMyAdmin main page.

29.Can you tell the difference between ereg_replace(.and eregi_replace()?

Ans:

ereg_replace and eregi_repalce(.are regular expressions used to replace the matching characters. The only difference between these functions are  eregi_replace(.function ignores the case distinction when it matches alphabetic characters. 

30.How to copy data from one server to another using PHP?

Ans:

You can use the following three options:

Option 1: You can use the PHP Copy to move files from server to server. Refer to the syntax below:

Option 2:  You can use the PHP FTP to move files from server to server. Refer to the syntax below.

Option 3: You can use the ZIP and UNZIP Files option in PHP.

31.Can you tell few best practices to be followed for optimization in SQL?

Ans:

The best practices to be followed for SQL optimizations depend on the individual to individual, but the following list consists of the most popular practices that are advised to follow. Refer below.

• Try avoiding prefixing your stored procedure names with “sp_”.
• It is recommended to use the column list in INSERT statements.
• Preferably use the ANSI-Standard JOIN Clauses rather than the Old style clauses.
• While using SELECT statement, avoid using * queries.
•  Do not use double quotes in T-SQL code.
• Try avoiding to use column numbers in the ORDER BY clause.
• Try using table aliases if your SQL statement involves more than a single source.

32.Can you tell what are various ways to create an index?

Ans:

The various options to create an index are as follows:

• You can create an index using the T-SQL statements.
• You can use the SQL Server Management Studio. In this, you can browse to the table you need to create an index and then right click on the Indexes node. Over here you have to choose the New Index option.
• You can indirectly identify the index by defining the PRIMARY KEY and the UNIQUE constraint within the CREATE TABLE or ALTER TABLE statement.

33.What is the difference between a Heap table and Temporary table?

Ans:

Heap Table	Temporary Table
Heap Table exists in the memory	A temporary table is valid only during the session.
Heap Tables are shared among a various number of clients.	Temporary tables are not shared among the clients.
Temporary tables need a special privilege to create tables.	Heap Tables are storage engines which do not need special privileges.

34.Why do you think it is advised to not to use GUID and CHARACTER columns as Clustered Index arrays?

Ans:

GUID columns affect the clustered index sorting performance as the nature of the random GUID value generated is larger than the integer data types.

CHARACTER columns affect the sorting performance of the character data types, larger-size values, non-increasing values, and non-static values which often tend to change. These values cannot be compared as binary values, as the characters comparison mechanism depends on the used collection.

35.How can you handle the –secure-file-priv in MySQL?

Ans:

secure-file-priv option limits the MySQL Server from loading the directories using the LOAD DATA INFILE.

If you wish to see the directory that has been configured then you may use the SHOW VARIABLES LIKE “secure_file_priv”; 

You have mainly two options to tackle:

• Either move your file to the directory specified by the secure-file-priv.
• Or you can disable secure-file-priv.  You cannot disable this later on, and you have to remove it from the start itself.

36.What is the difference between B-Tree and Hash Indexes?

Ans:

B-Tree	Hash Indexes
A B-Tree index can be used for column comparisons like =, >, <, >=, <= or BETWEEN operators.	A Hash-Index can be only used for equality comparisons that use =, >=, <=.
B-Tree can be used to search the next entry in the order.	Hash Index cannot be used to search for the next entry in the order.
Any leftmost prefix of the key can be used to find the rows.	Only whole keys are used to find a row.

37.Where is the MyISAM table stored?

Ans:

Each and every MyISAM Table is stored on disk in the following three files:

• .frm file – Stores the table definition.
• .MYD file – A data file has an MYData extension.
• .MYI  index file – The index file has an MYIndex extension.

38.State the differences between MongoDB and MySQL?

Ans:

MongoDB	MYSQL
An open source database that stores JSON like documents which vary in structure.	An open source relational database management system which stores relational data.
Each and every individual record are stored as documents.	Each and every individual record are stored as rows in a table.
Documents from a particular class or a group are stored in a collection.	A similar type of records are stored in a table.

39.Identify what is wrong in the below query?

Ans:

40.What is Normalization and list the different types of normalization?

Ans:

Normalization is the process of organizing data to avoid duplication and redundancy. There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one. The first three normal forms are usually adequate.

• First Normal Form (1NF.– No repeating groups within rows
• Second Normal Form (2NF.– Every non-key (supporting.column value is dependent on the whole primary key.
• Third Normal Form (3NF.– Dependent solely on the primary key and no other non-key (supporting.column value.

41.Consider you have a composite index of three columns. Now, you have to provide the value of two columns in the WHERE clause of a SELECT query. Do you think Index can be used for the operation?

Ans:

Usage of index completely depends on if you consider the primary index or not.  Consider you have a student table. Now, suppose if an Index is present on StudentID, StudentFirstName, and StudentLastName then you can consider a query as follows:

42.Suppose you have to collect the first name, middle name and the last name of students from the below table. But, you observe that there few missing values either in the first name, middle name and the last name columns. How will you return the first non-null values?

Ans:

StudentID	FirstName	MiddleName	LastName
1	Rohit	Kumar	NULL
2	Sakshi	Chowdhary	NULL
3	NULL	Yash	Singhania
4	Akash	NULL	Kumar
5	Avinash	NULL	Daksh

You can use the COALESCE function to return the first non-null value from the table. Consider the below query.

43.Consider a scenario where you have two to three tables with thousand tuples in each of them. Now, if you have to perform a JOIN operation between them will you choose to perform filtering of rows or transforming of rows first. 

Ans:

The answer to this question is quite logical. If you have three tables with thousands of tuples in each of them, then you are first supposed to filter the rows in those tables and then transform the table. This would be beneficiary as if you transform the table, then the number of columns may increase reducing the performance. Due to such performance issues, a lot of memory will be used and the output will appear on your screen after quite a long wait of time.

44.How can you validate emails using a single query?

Ans:

To validate emails you can use the regular expressions function (REGEXP_LIKE). Consider the below query.

45.Consider a scenario where you have to send an email to a client from the SQL database. How do you think you can achieve this task?

Ans:

To send an email from the database, you can use the stored procedures. Follow the below procedure to send the emails:

• Configure your SQL Server Mail account.
• Enable the database mail.
• Write a script to send an email. Refer below for the script.

46.Consider you have the following three tables which have to be linked together?

Ans:

The problem statement is to select all the departments from the Department table, with the “desc” field from the EmployeeContactDetails and EmployeeAddress where Ord=1.  Now, you have to solve this problem statement with a single query.

To solve this problem statement you can use the JOINS concept. You simply have to perform a JOIN on the Department.Ssn and the  DepartmentID in the other tables.

Now, if you are sure that the Ssn exists in all the three considered tables, then you can use the INNER JOIN. Also, if you are not sure that you have matching rows, then you can use the LEFT JOIN. Consider the below query.

47.If you are assigned a task, to find the information of PROCEDURES. What are the basic commands that you will use to do so?

Ans:

To check the procedures, you can consider the following query.

To find the procedures columns information, you can consider the following query.

48.Can you tell which of the following WHERE clauses is faster?

Ans:

If we compare both the statements, then the second WHERE clause would be comparatively faster than the first one. That is because, for the first statement, MYSQL would retrieve the value of ‘col’ for each and every row, multiplied by four. After that, it would compare the result to 16. Also, in the first case no Index can be used, and hence it makes it further slow.

49.What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?

Ans:

BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.

Example of BETWEEN:

Example of IN:

50.What are the different types of Collation Sensitivity?

Ans:

Following are the different types of collation sensitivity:

• Case Sensitivity
• Kana Sensitivity
• Width Sensitivity
• Accent Sensitivity

51.In Which Language Mysql Is Written?

Ans:

MySQL is written in C and C++ and its SQL parser is written in yacc.

52.What Are The Technical Specification Of Mysql?

Ans:

MySQL has the following technical specifications –

• Flexible structure
• High performance
• Manageable and easy to use
• Replication and high availability
• Security and storage management

53.What Is The Difference Between Mysql And SQL?

Ans:

SQL is known as a standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.

A PHP script is required to store and retrieve the values inside the database.

54.What Is The Difference Between Database And Table?

Ans:

There is a major difference between a database and a table. The differences are as follows:

Tables are a way to represent the division of data in a database while, database is a collection of tables and data.

Tables are used to group the data in relation with each other and create a dataset. This dataset will be used in the database. The data which are stored in the table in any form is a part of the database, but the reverse is not true.

55.Why Do We Use Mysql Database Server?

Ans:

The MySQL database server is very fast, reliable and easy to use. You can easily use and modify the software. MySQL software can be downloaded free of cost from the internet.

56.What Is The Difference Between Char And Varchar?

Ans:

A list of differences between CHAR and VARCHAR:

CHAR and VARCHAR types are different in storage and retrieval.

CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255.

When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.

57.What Is The Difference Between Truncate And Delete In Mysql?

Ans:

The DELETE command is used to delete data from a table. It only deletes the rows of data from the table while, truncate is very dangerous command and should be used carefully because it deletes every row permanently from a table.

58.How Many Triggers Are Possible In Mysql?

Ans:

There are only six Triggers allowed to use in MySQL database.

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

59.What Is Heap Table?

Ans:

Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.

60.What Is A Trigger In Mysql?

Ans:

A trigger is a set of codes that executes in response to some events.

61.What Is The Difference Between Heap Table And Temporary Table?

Ans:

Heap tables:

Heap tables are found in memory. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.

Heap tables do not support AUTO_INCREMENT.

Indexes should be NOT NULL.

Temporary tables:

The temporary tables are used to keep the temporary data. Sometimes it is very useful in cases to keep temporary data. Temporary table is deleted after current client session terminates.

Main differences:

The heap tables are shared among clients while temporary tables are not shared.

Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).

62.What Is The Difference Between Float And Double?

Ans:

FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.

63.What Are The Advantages Of Mysql In Comparison To Oracle?

Ans:

MySQL is a free, fast, reliable, open source relational database while Oracle is expensive, although they have provided Oracle free edition to attract MySQL users.

MySQL uses only just under 1 MB of RAM on your laptop while Oracle 9i installation uses 128 MB.

MySQL is great for database enabled websites while Oracle is made for enterprises.

MySQL is portable.

64.What Are The Disadvantages Of Mysql?

Ans:

MySQL is not so efficient for large scale databases.

It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.

Transactions are not handled very efficiently.

65.What Is The Difference Between Mysql_connect And Mysql_pconnect?

Ans:

Mysql_connect:

It opens a new connection to the database.

Every time you need to open and close database connection, depending on the request.

Opens page every time when it loaded.

Mysql_pconnect:

In Mysql_pconnect, “p” stands for persistent connection so it opens the persistent connection.

the database connection can not be closed.

it is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.

66.What Does ” I_am_a_dummy Flag” Do In Mysql?

Ans:

The ” i_am_a_dummy flag” enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.

67.How To Get The Current Date In Mysql?

Ans:

To get current date, use the following syntax:

68.What Are The Security Alerts While Using Mysql?

Ans:

Install antivirus and configure the operating system’s firewall.

Never use the MySQL Server as the UNIX root user.

Change root username and password

Restrict or disable remote access.

69.How To Change A Password For An Existing User Via Mysqladmin?

Ans:

70.What Is The Difference Between Unix Timestamps And Mysql Timestamps?

Ans:

Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.

71.What Is Mysql Default Port Number?

Ans:

MySQL default port number is 3306.

72.What Is Regexp?

Ans:

REGEXP is a pattern match using regular expression. Regular expression is a powerful way of specifying a pattern for a complex search.

73.How Many Columns Can You Create For An Index?

Ans:

You can create maximum of 16 indexed columns for a standard table.

74.What Is The Difference Between Now(.And Current_date()?

Ans:

NOW(.command is used to show current year, month, date with hours, minutes and seconds while CURRENT_DATE(.shows the current year with month and date only.

75.Which Command Is Used To View The Content Of The Table In Mysql?

Ans: