MySQL Interview Questions and Answers-min

Overview:

MySQL is one of the most famous and often used Database management systems in the world. It is a very popular database and part of LAMP (Linux, Apache, MySQL, and PHP) open source stack which powers many websites in the world.

MySQL is an open source relational database which was developed by two developers, by Axmark and Widenius. It uses mixed languages for development, C, C++, and yacc. Although MySQL is considered the open source with a GPL version, some of them do come with proprietary versions too.

There is a large community available for supporting the different applications which are part of MySQL and for paid support, there is an enterprise version offered by Oracle. There are other organizations also who provide third-party support, one of them MariaDB includes original MySQL developer. 

Development History:

            1. Released Internally as an alternative to mSQL language. (1995)

            2. First Windows NT release in 1998.

            3. Version 3.3 released as production version in Jan 2001.

            4. Version 5.0 included triggers, subqueries, and stored procedures.

            5. Version 5.6 is still supported and is one of earlier versions with the end of support till Feb 2021.

            6. Version 8.0 is the latest version of MySQL and it is supported till April 2026.

Ques 1. What is a MySQL database?

MySQL is a Relational Database Management system (RDBMS) which is open source. This was a project created an alternative to mSQL which was a slower language. First windows release was done on 8th January 1998. Now MySQL is acquired by Oracle and it is maintained as a free database.

Ques 2. Explain the features of MySQL

Some of the features of MySQL are:-

            • It is dependable and simple to utilise as well.

            • It is the appropriate database software from individual to enterprise level application.

            • Anybody can install and utilise it at no expense.

            • It is backed up by several leading programming languages like PERL, Java, C++, PHP, etc.

            • It assists standard Structured Query Language.

            • The open source license is customisable. Thus, a developer can alter it as per the prerequisites of the application.

Ques 3. MySQL is written in which language?

MySQL is basically written in two languages which is C and C++

Ques 4. How to install MySQL on Linux?

The suggested method to install MySQL on a Linux framework is through RPM. MySQL AB makes the accompanying RPMs accessible for download on its site −

• MySQL − MySQL database server manages the databases and tables and it also controls user access and even processes the SQL queries.

• MySQL-client − MySQL client programs make it possible to interface with to and collaborate with the server.

• MySQL-devel − Libraries and header files that are convenient when compiling other programs that use MySQL.

• MySQL-shared − These are the shared libraries for the MySQL client.

• MySQL-bench − Benchmark and performance testing instruments for MySQL database server.

All these RPMs are basically built on a SuSE Linux system but these RPMs normally work on other Linux variants without any difficulty

Once the above step is done then the following steps need to be followed:-

• Step 1:-Login to the system using the root user.

• Step 2:-Now switch to the directory which contains RPMs.

• Step 3:-Lastly install the MySQL database server by executing the [root@host]# rpm -i MySQL-5.0.9-0.i386.rpm command. The developer has to make sure that the filename which has to be replaced with the file name of the RPM should be in italics.

Ques 5. How a developer can get the server version on Linux in MySQL?

A developer can use mysqladmin binary to check the version of the server. This  mysqladmin binary is available in /usr/bin on linux

[root@host]# mysqladmin –version

The following result will be produced on Linux but the result may change according to the installation:-

mysqladmin  Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386

Ques 6. How to run a MySQL database at Boot Time?

To run the MySQL database at boot time then the developer has to that he/she has the following written in the /etc/rc.local file.

/etc/init.d/mysqld start

Also, the developer must have mysqld binary in  /etc/init.d/ directory.

Ques 7. Mention some technical specification in MySQL database.

Some of the  technical specification in MySQL database are:-

            • JSON Support

            • High performance

            • MySQL Enterprise Security

            • OLTP and Transactions

            • Geo-Spatial Support

            • Drivers

            • Flexible structure

            • Replication & High-Availability

Ques 8. List the difference between SQL and MySQL.

S No. SQL MySQL
1 SQL is used to interact with the many databases by MySQL. MySQL is a database which stores different types of data and keeps these data safe.
2 It is a computer language It is an application or a software
3 SQL is utilised for the production of database management systems  MySQL is utilised to enable data handling, storing, deleting and modifying data

Ques 9. Mention the different tables which are present in MySQL database?

The five tables which are present in the MySQL database are:-

            • MyISAM

            • Heap

            • Merge

            • INNO DB

            • ISAM

Ques 10. How many triggers are allowed in MySQL database?

There are six triggers which are as follows:-

            1. Before Insert

            2. After Insert

            3. Before Update

            4. After Update

            5. Before Delete

            6. After Delete

Ques 11. Mention the disadvantages of MySQL database server?

Some of the disadvantages of the  MySQL database server are:-

            • For larger size databases MySQL is inefficient.

            • Inefficient transaction handling.

            • Dependent on other add-ons for functionality.

            • Community-driven development does not happen.

Ques 12. Can you explain about Heap Tables in MySQL?

HEAP tables exist in memory and these heap tables are basically used for excessive speed storage on a temporary basis. Heap tables are also known as Memory tables.

• Heap tables restrict BLOB or TEXT fields.

• Comparison operators can only be used =, <,>, = >,=< in Heap Tables

• HEAP tables do not support AUTO_INCREMENT

• Indexes must always be NOT NULL

Ques 13. Mention the difference between the primary key & foreign Key

S No. Primary Key Foreign Key
1 It uniquely distinguishes the record It refers to another table primary key
2 It can never acknowledge a NULL value It always acknowledges a NULL value
3 When a record is erased from the table which contains the primary key then the comparing record must be erased from the table which contains the foreign key for the data consistency. Any record can be erased from the table which contains a foreign key without erasing a related record of another table.

Ques 14. Can you explain about BLOB & TEXT in MySQL database?

BLOB means large binary object.BLOB is actually used to hold a variable amount of data.

BLOB is of four types:-

            • TINYBLOB

            • BLOB

            • MEDIUMBLOB

            • LONGBLOB

The only difference between all these types is the maximum length of values they can hold.

TEXT is nothing but a case-insensitive BLOB. TEXT values are the non-binary strings or character string. TEXT contain set of characters & storing of values as well as comparing happens on the basis of collation of the character set.

TEXT are of four types:-

            • TINYTEXT

            • TEXT

            • MEDIUMTEXT

            • LONGTEXT

Ques 15. Name the default port for the MySQL server

3306  is the default port for the MySQL Server. 1433 is another standard default port in TCP/IP for the SQL Server.

Ques 16. How can you run the batch mode in MySQL database server?

Batch mode can be run using the following command:-

            • mysql;

            • mysql mysql.out;

Ques 17. List the drives in  MySQL database server

Some of the drivers in the MySQL database server are:-

            • RUBY Driver

            • PHP Driver

            • JDBC Driver

            • ODBC Driver

            • PERL Driver  

            • CAP11PHP Driver

            • C WRAPPER

            • PYTHON Driver

            • Ado.net5.mxz

Ques 18. What do you mean by DML, DDL & DCL?

SQL commands majorly can be divided into three categories which are

DCL(Data Control Languages)

DDL(Data Definition Language)

DML(Data Manipulative Language)

• Data Definition Language (DDL) deals with the entire database schemas, and DDL defines how the data should actually reside in the database.CreateTABLE and ALTER TABLE are the commands which are the part of DDL.

• Data Manipulative Language (DML) deals with operations & the manipulations on the data. Insert, Select, etc are the commands in DML.

• Data Control Languages (DCL) are affiliated to the Grant & permissions. In short, the authorisation to access any part of the database is defined by Data Control Languages.

Ques 19. Explain the role of “i-am-a-dummy” flag in MySQL?

  The “i-am-a-dummy” flag enables the MySQL engine to prevent the UPDATE and DELETE commands except if the WHERE clause is available.

Ques 20. Explain about the TIMESTAMP data types.

A TIMESTAMP data type is utilised to store the combination of date and time value which is just 19 characters in length.

The configuration of TIMESTAMP is YYYY-MM-DD HH:MM: SS. TIMESTAMP can store the data from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. By default, the present date and time of the server get embedded in the field of this data type when another new record is inserted or updated.

Ques 21. What are the patterns that can be used with REGEXP (regular expressions) operator in MySQL database?

^—It means the beginning of a string

$—It means an end of a string

.(dot)—Any one character

[…]—Any character which is listed between the square brackets

[^…]—Any character which is not listed between the square brackets

p1|p2|p3—Alternation; matches any patterns p1, p2, or p3

*—Matches zero or more instances of the preceding element

+—Matches one or more instances of the preceding element

{n}—n instances of the preceding element

{m, n}—m through n instances of the preceding element

Ques 22. What is the role of mysql_close() in MySQL database server?

Mysql_close() is only used to close the connection which was opened by mysql_connect().

Ques 23. What is the data directory in MySQL?

MySQL data directory is an area where MySQL stock its data. All the subdirectory which are under this data dictionary represents a MySQL database.

Ques 24.Which function is used to concatenate a string in MySQL?

The function used to concatenate a  two or more string in MySQL is CONCAT().

For Example:-

This example will combine five words, ‘Welcome ‘, ‘to’, ‘MySQL’,‘interview’,‘questions’.

SELECT CONCAT(‘Welcome ‘, ‘to’, ‘MySQL’,‘interview’,‘questions’);

Ques 25.Can you change the name of the table with the use of the SQL statement?

Yes, the name of the existing table can be changed with the command:-

RENAME TABLE table_name TO new_name

For Example:- Rename the table name Employee by new Retired Employee.

RENAME TABLE Employee TO Retired Employee;

Ques 26. What is the syntax for adding and removing any column of a table?

Syntax for adding column in the table:-

ALTER TABLE table_name ADD COLUMN column_name column_definition [FIRST|AFTER existing_column]

The syntax for removing a column in the table:-

ALTER TABLE table_name DROP COLUMN column_name;

Ques 27. What do you mean by an index? How can you declare an index in MySQL?

An index is actually a data structure of the MySQL table which is used to accelerate the queries.

An Index is used by the database search engine to find the records quicker. At least one or more fields of a table can be utilised as an index key. Index key can be assigned at the time of declaration of table or Index key can be assigned after creating the table.

Example:

username and email fields are set as the index in the following create table statement.

            1. CREATE TABLE users(

            2. username VARCHAR(50) PRIMARY KEY,

            3. email VARCHAR(50) NOT NULL,

            4. password VARCHAR(50) NOT NULL,

            5. INDEX (username, email));

Ques 28.Can you explain the function of mysqldump?

mysqldump is a very useful utility tool of MySQL which is used to dump one or more than one or all the databases from the server for backup or transfer to another database server.

Syntax:

For a single database, the syntax is:-

mysqldump [OPTIONS] db_name [TABLES]

For multiple databases, the syntax is:-

mysqldump [OPTIONS] –databases DB1 [DB2 DB3…]

For all databases, the syntax is:-

mysqldump [OPTIONS] –all-databases

Ques 29.How the password of a MySQL user can be changed?

The password of a MySQL user can be changed by using the SET PASSWORD statement.

Syntax:-

SET PASSWORD FOR ‘username’@’hostname’ = PASSWORD(‘password’);

For Examples:-SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘456789’);

This statement will set or change the root password.

LEAVE A REPLY

Please enter your comment!
Please enter your name here