Scribd
Upload
337 views8 pages

Mysql Interview Questions For Experienced

The document discusses various MySQL interview questions for experienced candidates. It covers topics such as heap tables, retrieving the SQL version, object-oriented MySQL libraries, storage engines, the ENUM data type, triggers, differences between DELETE TABLE and TRUNCATE TABLE, data definition language (DDL), data manipulation language (DML), data control language (DCL), functions like NOW() and CURRENT_DATE(), drivers, saving images, aggregate functions, the GRANT command, text data types, stored objects like views and procedures, features of stored procedures.

Uploaded by

Indows Soft Solutions
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
337 views8 pages

Mysql Interview Questions For Experienced

The document discusses various MySQL interview questions for experienced candidates. It covers topics such as heap tables, retrieving the SQL version, object-oriented MySQL libraries, storage engines, the ENUM data type, triggers, differences between DELETE TABLE and TRUNCATE TABLE, data definition language (DDL), data manipulation language (DML), data control language (DCL), functions like NOW() and CURRENT_DATE(), drivers, saving images, aggregate functions, the GRANT command, text data types, stored objects like views and procedures, features of stored procedures.

Uploaded by

Indows Soft Solutions
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 8

MYSQL INTERVIEW QUESTIONS FOR

EXPERIENCED

1. What are Heap Tables?


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.

2. How can a user get the current SQL version?


The syntax for getting the current version of MySQL:
SELECT VERSION ();

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


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

4. What is the storage engine used for MySQL?


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.

5. What is the use of ENUM in MySQL?


The use of ENUM will limit the values that can go into a table.
For a case, a user can create a table giving specific month values and
other month values would not enter into the table.
6. How to create triggers in MySQL?

We can use the CREATE TRIGGER statement for creating a


new trigger in MySQL.

SYNTAX:

CREATE TRIGGER trigger_name


(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON table_name FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END;

7. What is the difference between the DELETE TABLE and


TRUNCATE TABLE commands in MySQL?
DELETE TABLE is a logged operation, and every row deleted
is logged. Therefore, the process is usually slow. TRUNCATE
TABLE also deletes rows in a table, but it will not log any of the rows
deleted. The process is faster here in comparison. TRUNCATE
TABLE can be rolled back and is functionally similar to the DELETE
statement without a WHERE clause.

8. What is DDL in MySQL?


In MySQL, DDL is the short form for Data Definition
Language, which is used in database schemas and descriptions while
deciding how data should reside in the database.
A list of DDL Queries:

➢ CREATE
➢ ALTER
➢ DROP
➢ TRUNCATE
➢ COMMENT
➢ RENAME

9. What is DML in MySQL?


DML is a short form for Data Manipulation Language which is
used in data manipulation and mostly includes common SQL
statements to store, modify, retrieve, delete and update data in a
database.

The list of DML Queries:

➢ SELECT
➢ INSERT
➢ UPDATE
➢ DELETE
➢ MERGE
➢ CALL
➢ EXPLAIN PLAN
➢ LOCK TABLE

10. What is DCL in MySQL?


DCL is a short form for Data Control Language including
commands which are concerned with User rights, permissions and
other controls within the database system.
A list of queries for DCL:

➢ GRANT
➢ REVOKE

11. What are the difference between NOW and CURRENT_DATE


in MySQL?
NOW CURRENT

NOW() give you the CURRENT_DATE() will only give


current date time in the you the current date in format
format 'YYYY-MM_DD "YYYY-MM_DD"
HH:MM: SS'
12. What are the drivers in MySQL?

In MySQL, standards-based drivers for JDBC, ODBC, and .Net


are provided in order to enable developers in building database
applications with their language of choice.

Drivers in MySQL:

➢ PHP Driver
➢ JDBC Driver
➢ ODBC Driver
➢ C WRAPPER
➢ PYTHON Driver
➢ PERL Driver
➢ RUBY Driver
➢ CAP11PHP Driver
➢ Ado.net5.mxj
JDBC, ODBC, and .Net drivers are necessary for MySQL as they
enable the developers for building their database applications.

13. What are the difference between MongoDB and MySQL?


MONGO DB MYSQL

The representation of data in The representation of Mysql


MongoDB is totally different database is totally different
from Mysql, as in MongoDB form Mongodb, as in Mysql
data is represented in the data is stored in the form of
collection of JSON documents. tables (Row and columns).
MongoDB is an object-oriented Mysql is a structured query
database database.
14. How to save images in MySQL?
Images in MySQL can be stored as blobs. For saving them: All
the database images are converted into the blobs first. Then, they will
get inserted into the database, and later on, it will get stored into the
disk.

15. What are aggregate functions in MySQL?

The MySQL aggregate function performs a calculation on a set


of values, and it returns a single value as output. It ignores NULL
values when it performs calculation except for the COUNT function.

MYSQL Aggregrate functions are listed below:

➢ AVG()
➢ COUNT()
➢ SUM()
➢ MIN()
➢ MAX()
16. Explain GRANT command in MySQL.
When a new MySQL user is created, it requires certain privileges to
perform various database operations.

GRANT command grants certain privileges to the user.


For example below statement grants permission to
run SELECT and INSERT on TABLE customertable to
user username@localhost.

GRANT SELECT, INSERT ON customertable TO ‘username’@’localhost’

17. What are different TEXT data types in MySQL?


Different text data types in MySQL include:

• TINYTEXT,
• TEXT,
• MEDIUMTEXT and
• LONGTEXT.

18. What different Stored Objects are supported in MySQL?


Different stored objects in MySQL include VIEW, STORED
PROCEDURE, STORED FUNCTION, TRIGGER, EVENT.

• VIEW - It is a virtual table based on a result set of a database


query.
• STORED PROCEDURE - It is a procedure stored in database
which can be called using CALL statement. Stored procedure
does not return a value.
• STORED FUNCTION - It is like function calls which can
contain logic. It returns a single value and can be called from
another statement.
• TRIGGER - Trigger is program which is associated with a
database table which can be invoked before or after insert,
delete or update operations.
• EVENT - Event is used to run a program or set of commands at
defined schedule.

19. What is MySQL Stored Procedure?

A procedure (often called a stored procedure) is a collection of


pre-compiled SQL statements stored inside the database. It is a
subroutine or a subprogram in the regular computing language.

A procedure always contains a name, parameter lists, and SQL


statements.

We can invoke the procedures by using triggers, other


procedures and applications such as Java,Python,PHP etc. It was first
introduced in MySQL version 5. Now almost, it can be supported by
almost all relational database systems.

20. What are the features of Stored Procedures in MYSQL?

➢ Stored Procedure increases the performance of the applications.


Once stored procedures are created, they are compiled and
stored in the database.
➢ Stored procedure reduces the traffic between application and
database server. Because the application has to send only the
stored procedure's name and parameters instead of sending
multiple SQL statements.
➢ Stored procedures are reusable and transparent to any
applications.
➢ A procedure is always secure. The database administrator can
grant permissions to applications that access stored procedures
in the database without giving any permission on the database
tables.

You might also like