All The Useful Advanced MySQL Interview Questions & Answers

Q1: Describe BLOB in MySQL. What is it used for?
BLOB or Binary Large Object can be used to store binary data in MySQL. Sometimes binary data like images need to be stored in SQL databases. For example you might want to store user photos along with other user details in the database table. Binary data of the user photo can be saved as a BLOB. By using BLOB, we will not require separate storage for images. BLOB helps in removing complexity and providing portability in such cases.

Q2: How are VARCHAR and CHAR different. Talk about cases where you will use one over other.
Both CHAR and VARCHAR data types store characters up to specified length.

CHAR stores characters of fixed length while VARCHAR can store characters of variable length.
Storage and retrieval of data is different in CHAR and VARCHAR.
CHAR internally takes fixed space, and if stored character length is small, it is padded by trailing space characters. VARCHAR has 1 or 2 byte prefix along with stored characters.
CHAR has slightly better performance.
CHAR has memory allocation equivalent to the maximum size specified while VARCHAR has variable length memory allocation.

Q3: What is self referencing foreign key? Give an example.
A foreign key which is stored in a table itself is called to be self referencing foreign key.

For example consider an Employee database table. It has employee_id as primary key as well as a manager_id which is employee_id of his manager. If we create a foreign key constraint, as a manager is also an employee, manager_id will reference to empolyee_id in the same table. The Employee table with self referencing foreign key manager_id can be created using below statement.

CREATE TABLE `Employee`( 
`name` VARCHAR(25) NOT NULL, 
`employee_id` CHAR(9) NOT NULL, 
`manager_id` CHAR(9) NOT NULL, 
`salary` decimal(10,2) NULL,  
PRIMARY KEY(`employee_id`),
FOREIGN KEY (manager_id) REFERENCES employee(employee_id) ON DELETE CASCADE
);

Q4: What is the difference between Data Definition Language (DDL) and Data Manipulation Language (DML)?
Data definition language (DDL) commands are the commands which are used to define the database. CREATE, ALTER, DROP and TRUNCATE are some common DDL commands.

Data manipulation language (DML) commands are commands which are used for manipulation or modification of data. INSERT, UPDATE and DELETE are some common DML commands.

Q5: What is the difference between TRUNCATE and DELETE?
DELETE is a Data Manipulation Language(DML) command. It can be used for deleting some specified rows from a table. DELETE command can be used with WHERE clause.

TRUNCATE is a Data Definition Language(DDL) command. It deletes all the records of a particular table. TRUNCATE command is faster in comparison to DELETE. While DELETE command can be rolled back, TRUNCATE can not be rolled back in MySQL.

Q6: Both TIMESTAMP and DATETIME are used to store data and time. Explain difference between them and when should one be used?
Both TIMESTAMP and DATETIME store date time in YYYY-MM-DD HH:MM:SS format. While DATETIME stores provided date time, TIMESTAMP first converts provided time to UTC while storing and then again converts it back to server time zone upon retrieval. So if you need to serve different users in different countries using same time data, TIMESTAMP facilitates it. DATETIME simply stores provided date time without making any time zone related conversion.

Q7: Explain GRANT command in MySQL.
When a new MySQL user is created, he 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'

Q8: Explain the use of FEDERATED tables in MySQL.
FEDERATED tables are tables through which MySQL provides a way to access database tables located in remote database servers. Actual physical data resides in remote machine but the table can be accessed like a local table. To use a federated table ENGINE=FEDERATED and a connection string containing user, remote hostname, port, schema and table name are provided in CREATE TABLE command something like below.

CREATE TABLE table_fed (
... 
)
ENGINE=FEDERATED
CONNECTION='mysql://user@remote_hostname:port/federated_schema/table';

Q9: How can ENUM be used in MySQL. Give an example.
ENUM can be used to set a column as enum type. ENUM in MySQL is string object which can take one of the permitted value. In example below, country column can have one of the three values provided:

CREATE TABLE `Student`(
`rollnumber` INT NOT NULL, 
`name` VARCHAR(25) NOT NULL, 
`country` ENUM('USA', 'UK', 'Australia'), 
PRIMARY KEY(`rollnumber`));
Consider:

INSERT INTO `Student` values('6', 'John', 'USA');

Q10: What are different TEXT data types in MySQL. What is difference between TEXT and VARCHAR?
Different text data types in MySQL include:

TINYTEXT,
TEXT,
MEDIUMTEXT and
LONGTEXT.
These data types have different maximum size. While TINYTEXT can hold string up to 255 characters, TEXT can hold up to 65,535 characters, MEDIUMTEXT can hold up to 16,777,215 characters and LONGTEXT can hold up to 4,294,967,295 characters.

VARCHAR is also a variable text data type with some difference. VARCHAR is stored inline in the database table while TEXT data types are stored elsewhere in storage with its pointer stored in the table. A prefix length is must for creating index on TEXT data types. TEXT columns do not support default values unlike VARCHAR.

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

Q12: What is AUTO INCREMENT in MySQL? Explain with an example.
AUTO INCREMENT in MySQL is used to automatically assign next unique integer value to a particular column.

AUTO INCREMENT can be used to generate unique id for each inserted row without assigning a value to it. In MySQL, only columns which keep unique values like column with UNIQUE CONSTRAINT or PRIMARY KEY can be marked for AUTO INCREMENT. A table can have only one column marked for AUTO INCREMENT.

Code below can be used to mark studentid in Student table to auto increment. On adding a new Student without providing studentid, a unique student id with next available value is generated and assigned to the row.

CREATE TABLE `student`(`studentid` INT NOT NULL AUTO_INCREMENT,  `name` VARCHAR(25) NOT NULL, PRIMARY KEY(`studentid`));

Q13: What is Stored Function in MySQL. How are they different from Stored Procedure?
Stored function is a stored complex logic which can be executed like a function call from any other statement. It returns a single value. It can be used to store business logic and formulas in database. Stored functions can even run SELECT command or table manipulation commands like INSERT and UPDATE.

The most general difference between procedures and functions is that they are invoked differently and for different purposes:

A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records.
A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
You cannot invoke a function with a CALL statement, nor can you invoke a procedure in an expression.

e.g. SELECT get_foo(myColumn) FROM mytable is not valid if get_foo() is a procedure, but you can do that if get_foo() is a function. The price is that functions have more limitations than a procedure.

CREATE PROCEDURE proc_name ([parameters])
[characteristics]
routine_body

CREATE FUNCTION func_name ([parameters])
RETURNS data_type       // diffrent
[characteristics]
routine_body

Q14: What is difference between BLOB and TEXT in MySQL?
BLOB data types are designed to store binary data like picture or video in database.
TEXT data types are designed to store large text data.
BLOB stores binary byte string while TEXT stores character string. Although BLOB can be used for storing text data, TEXT data types support sorting and comparison around text which is not supported by BLOB.

There are four TEXT data types including TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT which can hold up to 255 characters, 65,535 characters, 16,777,215 characters and 4,294,967,295 characters respectively. Similarly four related BLOB types including TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB can hold up to 255 bytes, 65,535 bytes, 16,777,215 bytes and 4,294,967,295 bytes respectively.

Q15: What is the difference between commands create database and create schema in MySQL?
Terms database and schema are synonymous in MySQL. Some other enterprise level databases like Oracle and Microsoft SQL server make distinction between database and schema. A MySQL developer can interchangeably use both the terms. For example a database called test can be created by using either of the CREATE statements below.

   CREATE DATABASE test;
   CREATE SCHEMA test;

Q16: What is the use of DELIMETER command in MySQL?
MySQL workbench or MySQL client use ; as delimiter to separate different statements. DELIMITER command can be used to change delimiter in MySQL from ; to something else. It is used while writing trigger and stored procedures in MySQL. Command below make // as delimiter.

DELIMITER //
For example in a stored procedure, ";" is part of the actual stored procedure and not a delimiter. So while writing a stored procedure, we can make something else like // as delimiter and afterward revert it back by calling below command.

DELIMITER ;
 

More useful Advanced MySQL questions (Just Google each of these)

Q17: A multiple column index is created over firstName, lastName, city columns of a Customer table. Will this index be used for SELECT queries based on only first_name, only last_name or only city values?
Q18: Compare MySQL and PostgresSQL
Q19: Provide an example of UPSERT logic using MySQL
Q20: What are differences between MyISAM and InnoDB database engines commonly used in MySQL?
Q21: What are some major differences between MySQL and Oracle database?
Q22: What does OPTIMIZE TABLE command do in MySQL?
Q23: What is autocommit in MySQL? Can you run a transaction without disabling autocommit?
Q24: What is cursor used in MySQL? What are properties of MySQL cursor?
Q25: What is database engine or storage engine? Mention few storage engines supported by MySQL and their use.
Q26: Which partitioning types does MySQL support?
Q27:  How many tables can a trigger associate to in MySQL? Can a trigger be associated to a view?
Q28: What is difference between horizontal and vertical partitioning? Does MySQL support both horizontal and vertical partitioning?
Q29: What is the use of SAVEPOINT in MySQL?