29 Advanced MySQL Interview Questions Developers Must Know Before Tech Interview

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.

Both CHAR and VARCHAR data types store characters up to specified length.

1. CHAR stores characters of fixed length while VARCHAR can store characters of variable length.
2. Storage and retrieval of data is different in CHAR and VARCHAR.
3. 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.
4. CHAR has slightly better performance.
5. CHAR has memory allocation equivalent to the maximum size specified while VARCHAR has variable length memory allocation.

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
);

• 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.

• 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.

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.

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'

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';

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');

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.

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.

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:

1. 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.
2. A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
3. 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

• 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`));

• 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.

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;

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 ;