MySQL Interview Questions for Freshers & Experienced
Edicationidol is providing MySQL interview questions and answers for fresher as well experienced candidates to get their dream job.
Question : What is the difference between commands create database and create schema in MySQL?
Answer :
Words database and schema are synonymous in MySQL. In other database like Oracle and Microsoft SQL server make distinction between database and schema. But in MySQL developer can interchangeably use both the terms.
Example :
CREATE DATABASE Educationidol;
CREATE SCHEMA Educationidol;
Question : What is the use of DELIMETER command in MySQL?
Answer :
DELIMITER command can be used to change delimiter in MySQL from. DELIMITER command is used while writing trigger and stored procedures in MySQL.
Question : What is the difference between Data Definition Language and Data Manipulation Language?
Answer :
Data definition language (DDL) are used to define the database. CREATE, ALTER, DROP and TRUNCATE are some common DDL commands.
Data manipulation language (DML) are used for manipulation or modification of data. INSERT, UPDATE and DELETE are some common DML commands.
Question : What is the difference between TRUNCATE and DELETE?
Answer :
Below is list of difference between TRUNCATE and DELETE.
1- DELETE is a Data Manipulation Language(DML) command.
2- DELETE can be used for deleting some specified rows from a table.
3- DELETE command can be used with WHERE clause.
4- DELETE command can be rolled back.
1-TRUNCATE is a Data Definition Language(DDL) command.
2-TRUNCATE deletes all the records of a table.
3-TRUNCATE command is faster in comparison to DELETE.
4-TRUNCATE can not be rolled back in MySQL.
Question : What is use of GRANT command in MySQL.
Answer : In mysql , when we will create new user is created , New user requires certain privileges to perform differnet database operation.
Example : If you want give permission to new user only inser and select data in table.
GRANT SELECT, INSERT ON customertable TO ‘username’@’localhost’
Question : How can ENUM be used in MySQL. Give an example.
Answer :
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’);
Question : What are different TEXT data types in MySQL. What is difference between TEXT and VARCHAR?
Answer :
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.
Question : What is difference between BLOB and TEXT in MySQL?
Answer :
BLOB data types are designed to store binary data like images or video in database.
TEXT data types are designed to store large 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.
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.
Question : How many different stored objects are supported in MySQL?
Answer :
Different stored objects in MySQL include VIEW, STORED PROCEDURE, STORED FUNCTION, TRIGGER, EVENT.
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.
VIEW – VIEW is a virtual table based on a result set of a database query.
STORED PROCEDURE – STORED PROCEDURE is a procedure stored in database which can be called using CALL statement. Stored procedure does not return a value.
STORED FUNCTION – STORED FUNCTION is like function calls which can contain logic. It returns a single value and can be called from another statement.
Question : What is Stored Function in MySQL. How are they different from Stored Procedure?
Answer :
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
Question : What is AUTO INCREMENT in MySQL? Explain with an example.
Answer :
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));
Question : Describe BLOB in MySQL. What is it used for?
Answer : In MySQL, Blob uses to store binary data. If you want to store image in SQL database than you will define the data type Blob.
Question : How are VARCHAR and CHAR different.
Answer : 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.
Question : TIMESTAMP and DATETIME are used to store data and time. Explain difference between TIMESTAMP and DATETIMETIMESTAMP and DATETIME and when should one be used?
Answer :
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.
Question : What is FEDERATED tables and how to use of FEDERATED tables in MySQL.
Answer :
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’;