MYSQL INTERVIEW QUESTIONS AND ANSWER 2021

Q1 What is SQL Server?

Answer : SQL Server is one of the database management systems (DBMS) and is designed by Microsoft.  DBMS are computer software applications with the capability of interacting with users, various other applications, and databases. The objective of SQL Server is capturing and analyzing data and managing the definition, querying, creation, updating, and administration of the database.

Q2 How and why use SQL Server?

Answer : SQL Server is free and anyone can download and use it. The application uses SQL (Structured Query Language), and it is easy to use.

Q3 What are the features of MySQL?

Answer : MySQL provides cross-platform support, a wide range of interfaces for application programming, and has many stored procedures like triggers and cursors that help in managing the database.

Q3 What is the Traditional Network Library for a system?

Answer : In either Windows or POSIX systems, the named pipes provide ways of inter-process communications to connect different processes running on the same machine. It dispenses with the necessity of using the network stack, and data can be sent without affecting the performance. Servers set up named pipes to listen to requests. The client process needs to know the specific pipe name to send the request.

Q4 What is the default port for MySQL Server?

Answer : The default port for MySQL Server is 3306. Another standard default port is 1433 in TCP/IP for SQL Server.

Q5 What do DDL, DML, and DCL stand for?

Answer : DDL is the abbreviation for Data Definition Language dealing with database schemas, as well as the description of how data resides in the database. An example of this is the CREATE TABLE command. DML denotes Data Manipulation Language which includes commands such as SELECT, INSERT, etc. DCL stands for Data Control Language and includes commands like GRANT, REVOKE, etc.

Q6 What is a join in MySQL?

Answer : In MySQL, joins are used to query data from two or more tables. The query is made using the relationship between certain columns existing in the table. There are four types of joins in MySQL. Inner join returns rows if there is at least one match in both tables. Left join returns all the rows from the left table even if there is no match in the right table. Right join returns all the rows from the right table even if no matches exist in the left table. Full join would return rows when there is at least one match in the tables.

Q7 What are the common MySQL functions?

Answer : Common MySQL functions are as follows:

NOWO: The function for returning the current date and time as a single value

CURRDATEO: The function for returning the current date or time
CONCAT (X, Y): The function to concatenate two string values creating a single string output
DATEDIFF (X, Y): The function to determine the difference between two dates

Q8 What is the difference between CHAR and VARCHAR?

Answer : When a table is created, CHAR is used to define the fixed length of the table and columns. The length value could be in the range of 1–255. The VARCHAR command is used to adjust the column and table lengths as required.

Q9 What are Heap Tables?

Answer : Basically, Heap tables are in-memory tables used for high-speed temporary storage. But, TEXT or BLOB fields are not allowed within them. They also do not support AUTO INCREMENT.

Q10 What is the limit of indexed columns that can be created for a table?

Answer : The maximum limit of indexed columns that can be created for any table is 16.

Q11 What are the different types of strings used in database columns in MySQL?

Answer : In MySQL, the different types of strings that can be used for database columns are SET, BLOB, VARCHAR, TEXT, ENUM, and CHAR.

Q12 How can a user get the current SQL version?

Answer : The syntax for getting the current version of MySQL:

SELECT VERSION ();

Q13 Is there an object-oriented version of MySQL library functions?

Answer :Yes. MySQLi is the object-oriented version of MySQL, and it interfaces in PHP.

Q14 What is the storage engine used for MySQL?

Answer : Storage tables are named as table types. The data is stored in the files using multiple techniques such as indexing, locking levels, capabilities, and functions.

Q15 What is the difference between the primary key and the candidate key?

Answer : The primary key in MySQL is used to identify every row of a table in a unique manner. For one table, there is only one primary key. The candidate keys can be used to reference the foreign keys. One of the candidate keys is the primary key.

Q16 What are the different types of tables in MySQL?

Answer : MyISAM is the default table that is based on the sequential access method.

Heap is the table that is used for fast data access, but the data will be lost if the table or the system crashes. 
InnoDB is the table that supports transactions using the COMMIT and ROLLBACK commands.
BDB can support transactions similar to InnoDB, but the execution is slower.

Q17 What are the advantages and disadvantages of using MySQL?

Answer:

There are several advantages of MySQL which are making it a more popular database system now.

Some significant advantages and disadvantages of MySQL are mentioned below.

Advantages:

It is well-known for its reliable and secure database management system. Transactional tasks of the website can be done more securely by using this software.

It supports different types of storage engines to store the data and it works faster for this feature.
It can handle millions of queries with a high-speed transactional process.
It supports many advanced level database features, such as multi-level transactions, data integrity, deadlock identification, etc.
Maintenance and debugging processes are easier for this software.

Disadvantages:

It is hard to make MySQL scalable.
It is not suitable for a very large type of database.
The uses of stored routines and triggers are limited to MySQL.

Q 18 What is the function of myisamchk?

Answer:

myisamchk is a useful database utility tool that is used to get information about MyISAM database tables.
It is also used for checking, debugging, repairing and optimizing database tables. It is better to use this command when the server is down or when the required tables are not in use by the server

Q19 What are the technical specifications of MySQL?

Answer : MySQL has the following technical specifications –

Flexible structure
High performance
Manageable and easy to use
Replication and high availability

Security and storage management
Drivers
Graphical Tools
MySQL Enterprise Monitor
MySQL Enterprise Security

JSON Support
Replication & High-Availability
Manageability and Ease of Use
OLTP and Transactions
Geo-Spatial Support

Q20 What is the difference between MySQL and SQL?

Answer : SQL is known as the 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.

SQL is a computer language, whereas MySQL is a software or an application

SQL is used for the creation of database management systems whereas MySQL is used to enable data handling, storing, deleting and modifying data

Q 21 What is the difference between database and table?

Answer : 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 the 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.
A database is a collection of organized data and also features which are used to access them, whereas table is a collection of rows and columns which are used to store the data.

Q 22 Why do we use the MySQL database server?

Answer : First of all MYSQL server is free to use for developers and a small fee for enterprises.

MySQL server is open source.

The community of MySQL is tremendous and supportive hence any help regarding MySQL is resolved as soon as possible.

MySQL has very stable versions available, as MySQL has been in the market since a long time so all bugs arising in the previous builds have been continuously removed and a very stable version is provided after every update.

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.

Q 23 What are federated tables?

Answer : Federated tables are tables which points to the tables located on other databases on some other server.

Q 24 What is the difference between primary key and candidate key?

Answer : To identify each row of a table, a primary key is used. For a table, there exists only one primary key.

A candidate key is a column or a set of columns which can be used to uniquely identify any record in the database without having to reference any other data.

Q25 What are the drivers in MySQL?

Answer : Following are the drivers available in MySQL:

PHP Driver
JDBC Driver
ODBC Driver
C WRAPPER
PYTHON Driver

PERL Driver
RUBY Driver
CAP11PHP Driver
Ado.net5.mxz

Q26 . What Is DDL, DML, And DCL?

Answer : Majorly SQL commands can be divided into three categories i.e. DDL, DML & DCL. Data Definition Language (DDL) deals with all the database schemas, and it defines how the data should reside in the database. Commands like CreateTABLE and ALTER TABLE are part of DDL.

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

Data Control Languages (DCL) are related to the Grant and permissions. In short, the authorization to access any part of database is defined by these.

Q27 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 deny the UPDATE and DELETE commands unless the WHERE clause is present.

Q28 Which command is used to view the content of the table in MySQL?

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

Q29 Explain Access Control Lists.

Answer : An ACL is a list of permissions which are associated with an object. MySQL keeps the Access Control Lists cached in memory and whenever the user tries to authenticate or execute a command, MySQL checks the permission required for the object and if the permissions are available then execution completes successfully.

Q30 What is InnoDB?

Answer : InnoDB is a storage database for SQL. The ACID-transactions are also provided in addition InnoDB also includes support for the foreign key. Initially owned by InnobaseOY now belongs to Oracle Corporation after it acquired the latter since 2005.

Q31 What is the difference between mysql_connect and mysql_pconnect?

Answer : Mysql_connect() is used to open a new connection to the database while mysql_pconnect() is used to open a persistent connection to the database. It specifies that each time the page is loaded mysql_pconnect() does not open the database.

Q32 What is the use of mysql_close()?

Answer : Mysql_close() cannot be used to close the persistent connection. Though it can be used to close connection opened by mysql_connect().

Q33 What is MySQL data directory?

Answer : MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default the information managed my MySQL = server mysqld is stored in data directory.

Q34 How do you determine the location of MySQL data directory?

Answer : The default location of MySQL data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data.

Q35 What are the advantages of MyISAM over InnoDB?

Answer : MyISAM follows a conservative approach to disk space management and stores each MyISAM table in a separate file, which can be further compresses, if required. On the other hand, InnoDB stores the tables in tablespace. Its further optimization is difficult.

Q36 What is the difference between NOW() and CURRENT_DATE()?

Answer : 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.

Q37 What is the query to display top 20 rows?

Answer : SELECT * FROM table_name LIMIT 0,20;

Q38 Write a query to display current date and time?

Answer : If you want to display current date and time, use –

SELECT NOW();

If you want to display current date only, use:

SELECT CURRENT_DATE();

Q39 What is save point in MySQL?

Answer : A defined point in any transaction is known as savepoint.

SAVEPOINT is a statement in MySQL which is used to set a named transaction save point with a name of identifier.

Q40 What is SQLyog?

Answer : SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.

Q41 How do you backup a database in MySQl?

Answer : It is easy to back up data with phpMyAdmin. Select the database you want to backup by clicking the database name in the left-hand navigation bar. Then click the export button and make sure that all tables are highlighted that you want to back up. Then specify the option you want under export and save the output

Q42 How can we convert between Unix & MySQL timestamps?

Answer : UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp

FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.

Q43 What does myisamchk do?

Answer : It compress the MyISAM tables, which reduces their disk or memory usage.

Q44. How do you control the max size of a HEAP table?

Answer : Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size.

Q45 What is the difference between MyISAM Static and MyISAM Dynamic?

Answer : In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.

MyISAM Static would be easier to restore in case of corruption.

Q46. What are federated tables?

Answer : Federated tables which allow access to the tables located on other databases on other servers.

Q47 What, if a table has one column defined as TIMESTAMP?

Answer : Timestamp field gets the current timestamp whenever the row gets altered.

Q48. What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?

Answer : It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.

Q49 How can you see all indexes defined for a table?

Answer : Indexes are defined for the table by:

SHOW INDEX FROM <tablename>;

Q50 What do you mean by % and _ in the LIKE statement?

Answer : % corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.