Top 70 MySQL Interview Questions and Answers

1.
What is a database?
A database is a collection of related data. It is organized in such a way that a computer program can create, access, manage and replicate the data it holds.
2.
Define column and row?
Column is a single vertical cell that contains data of one or same kind and row is a group of columns.
3.
Define Primary Key?
A column or set of columns that uniquely identifies each row in the table is called Primary key.
4.
Define Foreign Key?
The foreign key is used to relate two or more than two tables. One table primary key can be a foreign key of another table.
5.
Define Composite Key?
A composite key (compound key) consists of multiple column, but one column is not sufficiently unique.
6.
What is an Index?
The index is used to uniquely identify each row in a column. It is used to fetch/ update/ delete desired records in a table.
7.
What is Referential Integrity?
It makes sure that a foreign key value always points to an existing row.
8.
What is the default file size limit for a table?
MySQL can store up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but according to more requirement and operating system support we can increase this up to 8TB.
9.
How many types of storage engines in MySQL?
Here are the storage engines used in MySQL.

1. MyISAM
2. InnoDB
3. MEMORY
4. NDB
10.
How many data types are used in MySQL?
These are the general datatypes used in MySQL

1. Numeric Types- (INTEGER, FLOAT, DOUBLE, DECIMAL, NUMERIC)
2. Date and Time- (DATE, TIME, DATETIME, TIMESTAMP, YEARS)
3. String Types- (CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT ENUM, SET)
11.
What is ENUM Datatype?
Enum is an enumerated data type. It is a finite set of values defined by the user. The Enum column value will be one of the set of values.
12.
What is a BLOB datatype?
BLOB stands for Binary Large Objects. It is used to store variable amounts of binary data like images or other types of files. It has maximum size limits of 65535 characters. There are four BLOB types that differ only on size limits - TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB.
13.
What is MySQL?
MySQL is Open Source relational database management system. MySQL is developed by Oracle Corporation.
14.
What is relational database?
The Relational Database provides a flexible programming language. Instead of storing all the data in a single file, it stores the data in separate tables.
15.
What are the major features of MySQL?
MySQL is lightweight, reliable and secure database management system. It is free to download and easy to use. It ensures data protection and more security features. Today, MySQL is most popular choice of database.
16.
What is the advantage of using Transaction Storage Engine?
In Transaction Storage Engine, we can rollback/ recover our data. So in case of system crash, or update failure, we can easily revert data. InnoDB Storage Engine provides transaction safe tables.
17.
Give some examples of Transaction and Non Transaction Storage Engines.
InnoDB, NDB are the Transaction Storage Engines and MyISAM, Archive, Memory are Non Transaction Storage Engines.
18.
What are the required parameters to connect to MySQL Server?
To connect to MySQL Server, we need the host name of the server where MySQL server is running and the username and password of MySQL Server Account.
19.
How to eliminate duplicate records from a MySQL result set?
By using the DISTINCT clause with the SELECT statement, we can eliminate the duplicate records.

SELECT DISTINCT name FROM Employee;
20.
What is the use of TIMESTAMPDIFF() function?
TIMESTAMPDIFF() function is used to get the interval between two datetime. This function can return the interval in DAY, MONTH, YEAR, HOUR, WEEK as we define the format in the first parameter.

Syntax - TIMESTAMPDIFF(UNIT, DATETIME1, DATETIME2);

Example - TIMESTAMPDIFF(YEAR, DOB, CURRENTDATE);
21.
How to get the highest value of a Column?
By using MAX() function, we can get maximum value of the column.
Example- To get maximum salary from the employee table.
SELECT MAX(Salary) AS MAXSALARY FROM EMPLOYEE;
22.
What is an AUTO_INCREMENT attribute?
AUTO_INCREMENT attribute is used to generate a unique identity of the column automatically. Generally, we will set the Primary Key column as Auto Increment. For every new record it is increased by 1.
23.
How to get the last inserted id of a table?
By using LAST_INSERT_ID( ) function, we will get the last inserted Primary Key of the table.
24.
Define UNION Operator?
Union Operator is used to combine two or more tables result sets into a single result set. It removes the duplicate rows by default and return distinct values.

SELECT Columnname1 FROM Table1 UNION SELECT Columnname2 FROM TABLE2;
25.
How to change the Table name?
By using the RENAME Table statement, we can rename Table name.

RENAME TABLE table_old_name TO table_new_name;
26.
What is an alias? How to define a column alias?
An alias is used to give a descriptive name to a Table or a Column. We can provide a column alias by using AS keyword.

SELECT employee_name AS empname FROM Employee;
27.
What is MySQLi?
MySQLi is improved version of MySQL. It provides both Procedural and Object Oriented Interface. It supports Prepared statements and transactions.
28.
Define BETWEEN operator.
BETWEEN Operator is used in the WHERE clause to set the range. This can be used in SELECT, UPDATE, INSERT, DELETE Statements.

SELECT * FROM EMPLOYEE WHERE AGE BETWEEN 40 AND 45;
29.
Define REGEXP in MYSQL?
REGEXP is regular expression based pattern matching operator.
Example: Query to select all employee name staring with 'An'.

SELECT * FROM EMPLOYEE WHERE emp_name REGEXP '^AN';
30.
How to find the current date in MySQL?
By using the CURRENT_DATE() function, we can get the current date.

SELECT CURRENT_DATE();
31.
How to get the count of a number of different values?
By using COUNT(DISTINCT) function.
32.
Define DATEDIFF() function?
This function returns duration between two dates.
SELECT DATEDIFF('2016-03-01', '2015-12-20');
33.
Define FIND_IN_SET() function?
This function is used to find the position of a string in a set of strings.

Syntax

FIND_IN_SET(string, stringlist);

First parameter is the string to find and second parameter is the lists of the strings.

Example

<?php
   echo 'Welcome to etutorials';
   exit('Terminate the code execution'); 
   echo 'This will not execute as this is after the exit function';
?>

34.
How to check a value is within a set of values or not?
By using IN() operator.
35.
How to check whether a value is not within a set of values?
By using NOT IN() function.
36.
How to get the number of rows updated, while executing a query?
By using ROW_COUNT() function, we can get the number of rows updated.
37.
Define VALUES() function in mysql?
The VALUES() function is used during the INSERT statement to specify the values to be inserted.
38.
What is the role of CONCAT_WS operator?
It returns the concatenated string with a separator. The separator is specified in the first argument and the strings are in second and third arguments.
CONCAT_WS(', ', $string1, $string2);
39.
How to remove leading and trailing spaces in mysql?
By using TRIM() function, we can remove leading and trailing spaces.
40.
Define MyISAM?
MyISAM is the default storage engine for MySQL version priors to 5.5.
41.
Define NOW() function.
It returns current data and time as one value and in (current year,month,date with hours,minutes and seconds) format.
42.
What is Heap table?
Heap Table is used for high speed data storage. Heap tables are stored in memory. It does not support BLOB and TEXT fields.
43.
What is the command to list all the databases on the command prompt?
By using the SHOW Database command.
44.
How will you know the total number of rows in a table?
By using COUNT() function, we can get the total number of records in a table.

Example -
Select count(*) FROM Employee;
45.
How will you get all the records from the table 'Employee' where profile is not IT?
SELECT * FROM Employee WHERE profile != 'IT';
46.
What is the difference between MyISAM Static and MyISAM dynamic?
MyISAM static has fixed width for all the fields and MyISAM has flexible width for all the fields.
47.
What is the full form of ISAM?
Indexed Sequential Access Method
48.
How many triggers are used in MYSQL Tables?
BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE , AFTER DELETE
49.
How to check existence of a table?
By using the following command, we can check existence of a table-
CHECK TABLE table_name;
50.
Write MySQL command to show current date and time.
SELECT NOW();
51.
How to get number of days difference between two date?
SELECT DATEDIFF('2010-10-22', '2010-10-19');
52.
What is the default port of MySQL Server?
The default port for MySQL Server is 3306.
53.
What is the default port in TCP/IP of MySQL Server?
The default port in TCP/IP of MySQL Server is 1433.
54.
Write a query to delete rows using INNER JOIN.
DELETE students FROM students
LEFT JOIN marks ON students.stdid = marks.stdid 
WHERE marks IS NULL;
55.
What is the object oriented version of MySQL?
MySQLi is the object oriented version of MySQL.
56.
What is the difference between truncate and delete?
TRUNCATE is a DDL command, it removes all rows from a table wheras DELETE is a DML command, it is used to remove rows from a table based on WHERE condition. Truncate uses the less transaction space than Delete statement.
57.
How to convert a mysql expression in a binary string?
The BINARY operator converts the expression to a binary string. In Binary expression the string comparison to be done byte by byte rather than character by character.
58.
What is the role of myisamchk?
myisamchk works with MyISAM tables. It is used to get information about database tables and also check, repair and optimize tables.
59.
What is the use of CONCAT function?
The MySQL CONCAT function takes one or more string arguments and concatenates them into a single string.
Example -
CONCAT (string1, string2, string3)
60.
How to get number of days difference between two date?
SELECT DATEDIFF('2010-10-22', '2010-10-19');
61.
What is the difference between BLOB and TEXT?
BLOB values are treated as binary strings (byte strings). They have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set other than binary, and values are sorted and compared based on the collation of the character set.
62.
How to display 3rd highest rank record from result table?
SELECT rank FROM `result` order by rank desc limit 2,1
63.
What is the use of Having Clause?
The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified condition. If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause.
64.
How AUTO_INCREMENT can be used to generate unique identity?
CREATE TABLE students (id INT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL);
65.
How can be count number of rows with different values?
By using COUNT(DISTINCT expr), we can count number of rows with different non-null values.
SELECT COUNT(DISTINCT price) FROM items;
66.
How would you write a query to select all students that choose either maths, phy, chm?
SELECT name FROM students WHERE subject IN (maths, phy, chm);
67.
What is the use of NOT NULL constraint?
The NOT NULL constraint is a column constraint that forces the values of a column to non-NULL values only.
CREATE TABLE students (id INT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL);
68.
How would you write a query to select all students that choose either maths, phy, chm?
SELECT name FROM students WHERE subject IN (maths, phy, chm);
69.
What is difference between CHAR and VARCHAR data types in sql?
CHAR stores character values only wheras VARCHAR stores alphanumeric values. CHAR is used to store fixed length memory storage wheras VARCHAR is used for variable length memory storage.
70.
How do you check defined Index on a table?
We can see defined Index on a table by using SHOW INDEX command.
Example -
SHOW INDEX FROM Employees;




Read more articles