Top 100+ Mysql Dba Interview Questions And Answers

Question 1. What Is Mysql?

Answer :

MySQL is a multithreaded, multi-consumer SQL database management gadget which has greater than eleven million installations. This is the arena's second most popular and extensively used open supply database.

Question 2. In Which Language Mysql Is Written?

Answer :

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

PHP Interview Questions
Question three. What Are The Technical Specification Of Mysql?

Answer :

MySQL has the subsequent technical specs -

Flexible shape
High performance
Manageable and easy to apply
Replication and high availability
Security and storage control
Question 4. What Is The Difference Between Mysql And Sql?

Answer :

SQL is called fashionable query language. It is used to interact with the database like MySQL. MySQL is a database that stores numerous varieties of records and continues it secure.
A PHP script is needed to store and retrieve the values within the database.

PHP Tutorial
Question 5. What Is The Difference Between Database And Table?

Answer :

There is a chief distinction between a database and a table. The variations are as follows:

Tables are a way to represent the department of facts in a database even as, database is a set of tables and records.
Tables are used to group the records in relation with every different and create a dataset. This dataset may be used inside the database. The records which are stored inside the desk in any shape is a part of the database, but the reverse isn't proper.
MySQL Interview Questions
Question 6. Why Do We Use Mysql Database Server?

Answer :

The MySQL database server is very fast, reliable and smooth to use. You can without problems use and adjust the software. MySQL software may be downloaded freed from cost from the net.

Question 7. What Are The Different Tables Present In Mysql?

Answer :

There are many tables that stay present through default. But, MyISAM is the default database engine utilized in MySQL. There are five kinds of tables which can be gift:

MyISAM
Heap
Merge
INNO DB
ISAM
MySQL Tutorial PHP+MySQL Interview Questions
Question eight. What Is The Difference Between Char And Varchar?

Answer :

A listing of differences among CHAR and VARCHAR:

CHAR and VARCHAR types are exceptional in storage and retrieval.
CHAR column period is fixed to the length that is declared even as creating desk. The duration value degrees from 1 and 255.
When CHAR values are stored then they're proper padded using areas to specific period. Trailing areas are removed when CHAR values are retrieved.
Question 9. What Is The Difference Between Truncate And Delete In Mysql?

Answer :

The DELETE command is used to delete facts from a desk. It only deletes the rows of statistics from the desk whilst, truncate may be very risky command and should be used cautiously because it deletes each row permanently from a desk.

Drupal Interview Questions
Question 10. How Many Triggers Are Possible In Mysql?

Answer :

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

Before Insert
After Insert
Before Update
After Update
Before Delete
After Delete
Drupal Tutorial
Question eleven. What Is Heap Table?

Answer :

Tables that are found in memory is known as HEAP tables. When you create a heap table in MySQL, you ought to want to specify the TYPE as HEAP. These tables are usually known as memory tables. They are used for high pace garage on transient basis. They do not allow BLOB or TEXT fields.

PHP5 Interview Questions
Question 12. What Is Blob And Text In Mysql?

Answer :

BLOB is an acronym stands for binary massive item. It is used to preserve a variable quantity of information.

There are four kinds of BLOB.

TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
The variations among all these are the most period of values they are able to maintain. TEXT is case-insensitive BLOB. TEXT values are non-binary strings (individual string). They have a man or woman set and values are stored and in comparison primarily based at the collation of the man or woman set.

There are four types of TEXT.

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
PHP Interview Questions
Question 13. What Is A Trigger In Mysql?

Answer :

A trigger is a fixed of codes that executes in response to a few activities.

WordPress Tutorial
Question 14. What Is The Difference Between Heap Table And Temporary Table?

Answer :

Heap tables:

Heap tables are found in reminiscence. They are used for high velocity storage on temporary foundation. They do now not allow BLOB or TEXT fields.
Heap tables do now not support AUTO_INCREMENT.
Indexes should be NOT NULL.
Temporary tables:

The brief tables are used to hold the brief information. Sometimes it's far very useful in instances to maintain temporary information. Temporary desk is deleted after cutting-edge purchaser session terminates.
Main differences:

The heap tables are shared amongst clients at the same time as temporary tables aren't shared.
Heap tables are simply every other garage engine, while for transient tables you need a unique privilege (create transient desk).
Question 15. What Is The Difference Between Float And Double?

Answer :

FLOAT stores floating point numbers with accuracy up to 8 places and allocates four bytes, alternatively DOUBLE shops floating point numbers with accuracy up to 18 locations and allocates 8 bytes.

WordPress Interview Questions
Question sixteen. What Are The Advantages Of Mysql In Comparison To Oracle?

Answer :

MySQL is a unfastened, fast, dependable, open supply relational database while Oracle is high-priced, even though they have provided Oracle free version to attract MySQL users.
MySQL uses most effective simply under 1 MB of RAM in your computer at the same time as Oracle 9i set up makes use of 128 MB.
MySQL is great for database enabled websites even as Oracle is made for firms.
MySQL is transportable.
Joomla Tutorial
Question 17. What Are The Disadvantages Of Mysql?

Answer :

MySQL isn't always so green for big scale databases.
It does no longer help COMMIT and STORED PROCEDURES functions version much less than five.Zero.
Transactions are not handled very effectively.
Joomla Interview Questions
Question 18. What Is The Difference Between Mysql_connect And Mysql_pconnect?

Answer :

Mysql_connect:

It opens a brand new connection to the database.
Every time you need to open and near database connection, relying on the request.
Opens web page each time when it loaded.
Mysql_pconnect:

In Mysql_pconnect, "p" stands for continual connection so it opens the chronic connection.
The database connection cannot be closed.
It's far more beneficial if your site has extra traffic because there may be no need to open and close connection regularly and each time whilst web page is loaded.
MySQL Interview Questions
Question 19. What Does " I_am_a_dummy Flag" Do In Mysql?

Answer :

The " i_am_a_dummy flag" allows MySQL engine to refuse any UPDATE or DELETE declaration to execute if the WHERE clause is not present.

CakePHP Tutorial
Question 20. How To Get The Current Date In Mysql?

Answer :

To get modern date, use the following syntax:
SELECT CURRENT_DATE();  

CakePHP Interview Questions
Question 21. What Are The Security Alerts While Using Mysql?

Answer :

Install antivirus and configure the operating gadget's firewall.
Never use the MySQL Server because the UNIX root person.
Change root username and password
Restrict or disable remote get admission to.
Question 22. How To Change A Password For An Existing User Via Mysqladmin?

Answer :

Mysqladmin -u root -p password "newpassword".

CodeIgniter Tutorial
Question 23. What Is The Difference Between Unix Timestamps And Mysql Timestamps?

Answer :

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

CodeIgniter Interview Questions
Question 24. How To Display Nth Highest Salary From A Table In A Mysql Query?

Answer :

Let us take a desk named worker.

To discover Nth highest earnings is:
1. Choose distinct(salary) from worker order by way of salary desc restriction n-1,1  

in case you want to discover third biggest earnings:
1. Choose wonderful(earnings) from employee order by earnings desc restrict 2,1 

PHP+MySQL Interview Questions
Question 25. What Is Mysql Default Port Number?

Answer :

MySQL default port number is 3306.

PHP7 Tutorial
Question 26. What Is Regexp?

Answer :

REGEXP is a pattern fit using normal expression. Regular expression is a powerful manner of specifying a sample for a complicated seek.

PHP7 Interview Questions
Question 27. How Many Columns Can You Create For An Index?

Answer :

You can create most of 16 listed columns for a standard desk.

Drupal Interview Questions
Question 28. What Is The Difference Between Now() And Current_date()?

Answer :

NOW() command is used to show present day yr, month, date with hours, mins and seconds at the same time as CURRENT_DATE() shows the modern 12 months with month and date handiest.

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

Answer :

The SELECT command is used to view the content material of the table in MySQL.

Question 30. What Is The Usage Of I-am-a-dummy Flag In Mysql?

Answer :

In MySQL, the i-am-a-dummy flag makes the MySQL engine to disclaim the UPDATE and DELETE commands except the WHERE clause is present.

Question 31. What Is The Usage Of Regular Expressions In Mysql?

Answer :

In MySQL, regular expressions are used in queries for searching a pattern in a string.

* Matches 0 greater times of the string preceding it.
+ fits 1 extra instances of the string preceding it.
? Matches 0 or 1 times of the string preceding it.
. Matches a single man or woman.
[abc] fits separates strings
^ anchors the healthy from the begin.
"." Can be used to suit any unmarried man or woman" can be used to in shape both of the two strings
REGEXP can be used to suit the input characters with the database.
Example:
The following statement retrieves all rows where column employee_name includes the text 1000 (example salary):

Select employee_name  
From worker  
Where employee_name REGEXP 'a thousand'  
Order by way of employee_name  
Question 32. How Do You Determine The Location Of Mysql Data Directory?

Answer :

The default region of MySQL statistics listing in windows is C:mysqldata or C:Program FilesMySQLMySQL Server 5.Zero information.

Question 33. What Is Mysql Data Directory?

Answer :

MySQL facts directory is a place in which MySQL stores its facts. Each subdirectory below this information dictionary represents a MySQL database. By default the records managed my MySQL = server mysqld is saved in facts directory.

PHP5 Interview Questions
Question 34. What Is The Use Of Mysql_close()?

Answer :

Mysql_close() can't be used to shut the persistent connection. Though it may be used to close connection opened via mysql_connect().

Question 35. How Is Myisam Table Stored?

Answer :

MyISAM desk is stored on disk in three codecs.

'.Frm' document : storing the table definition
'.MYD' (MYData): information document
'.MYI' (MYIndex): index record
Question 36. What Is The Usage Of Enums In Mysql?

Answer :

ENUMs are used to restriction the feasible values that go in the table:

For example: CREATE TABLE months (month ENUM 'January', 'February', 'March'); INSERT months VALUES ('April').

WordPress Interview Questions
Question 37. What Are The Advantages Of Myisam Over Innodb?

Answer :

MyISAM follows a conservative technique to disk space management and shops each MyISAM table in a separate report, which can be similarly compresses, if required. On the alternative hand, InnoDB stores the tables in tablespace. Its further optimization is tough.

Question 38. What Are The Differences Between Mysql_fetch_array(), Mysql_fetch_object(), Mysql_fetch_row()?

Answer :

Mysql_fetch_object is used to retrieve the result from the database as gadgets whilst mysql_fetch_array returns end result as an array. This will allow get admission to to the information by using the sector names.
For example:
Using mysql_fetch_object discipline may be accessed as $result->name.
Using mysql_fetch_array discipline may be accessed as $end result->[name].
Using mysql_fetch_row($end result) where $end result is the result useful resource back from a a success question done the use of the mysql_query() characteristic.
Example:
1.$end result = mysql_query("SELECT * from college students");  
2.While($row = mysql_fetch_row($result))  
3.  
Four.Some announcement;  
five.  

Question 39. How Do You Backup A Database In Mysql?

Answer :

It is simple to backing up facts with phpMyAdmin. Select the database you want to backup through clicking the database call within the left hand navigation bar. Then click the export button and ensure that every one tables are highlighted which you need to backup. Then specify the choice you need underneath export and keep the output.

Question 40. What Is Sqlyog?

Answer :

SQLyog program is the most famous GUI device for admin. It is the most famous MySQL supervisor and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL the front ends and MySQL GUI equipment.

Joomla Interview Questions