Top 70 MySQL Interview Questions and Answers

1.
What is a database?
A database is a collection of related data. We can organize the data into tables, rows, columns, and index in such a way that a computer program can easily 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. It is arranged from top to bottom. In DBMS, columns are called fields which contain the collection of characters.
And, Row is a group of columns. It is arranged from left to right. In DBMS, rows are known as records that contain fields.
3.
Define Primary Key?
A column or set of columns that uniquely identifies each row in the table is called Primary key. It cannot contain null values and every row must have a primary key value.
4.
Define Foreign Key?
A foreign key is a column or group of columns in a relational database table that 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 in DBMS is a candidate key that is composed of two or more attributes and is capable of uniquely identifying a table or a relation.
6.
What is an index in DBMS?
Indexing is a data structure technique to efficiently retrieve records from the database files based on some attributes on which the indexing has been done. It uses to find records within a table more quickly. As, it 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?
Referential integrity refers to the relationship between tables. Because each table in a database must have a primary key, this primary key can appear in other tables because of its relationship to data within those tables. It ensures that relationships between tables remain consistent.
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?
Storage engines are MySQL components that handle the SQL operations for different table types. 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 developed, distributed, and supported by Oracle Corporation and written in C and C++ languages. MySQL is Open Source relational database management system. It is developed by Oracle Corporation. It supports small and large database, so it is used in small and large applications.
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. It runs very fast as it has very fast thread based memory allocation system. 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. Example -
SELECT DISTINCT name FROM Employee;
20.
What is the use of TIMESTAMPDIFF() function?
The 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 the MAX() function, we can get maximum value of the column.
Example- To get maximum salary from the employee table, the query is as follows -
SELECT MAX(Salary) AS MAXSALARY FROM EMPLOYEE;
22.
What is an AUTO_INCREMENT attribute?
The AUTO_INCREMENT attribute is used to generate a unique identity of the column automatically. Generally, we 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 the LAST_INSERT_ID( ) function, we can get the last inserted Primary Key of the table.




24.
Define UNION Operator?
The 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 the 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 the 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. This improved version is built to use with the PHP programming language.
28.
Define BETWEEN operator in MySQL.
The BETWEEN Operator is used in the WHERE clause to set the range. This can be used in SELECT, UPDATE, INSERT, DELETE Statements. Example -
SELECT * FROM EMPLOYEE WHERE AGE BETWEEN 40 AND 45;
29.
Define REGEXP in MYSQL?
REGEXP is regular expression based pattern matching operator. It also supports a number of metacharacters which allow more flexibility and control when performing pattern matching.
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 the COUNT(DISTINCT) function.




32.
Define DATEDIFF() function?
This function returns duration between two dates. Example -
SELECT DATEDIFF('2016-03-01', '2015-12-20');
33.
Define FIND_IN_SET() function?
The FIND_IN_SET() 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 the IN() operator. The IN operator allows you to specify multiple values in a WHERE clause. This operator is a shorthand for multiple OR conditions.
35.
How to check whether a value is not within a set of values?
By using the NOT IN() operator. The NOT IN operator checks if a value is not present in a set of values and returns those values.
36.
How to get the number of rows updated, while executing a query?
By using the ROW_COUNT() function, we can get the number of rows updated. This function returns the number of rows updated, inserted or deleted by the preceding statement.
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?
The CONCAT_WS operator 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 the TRIM() function, we can remove the leading and trailing spaces.
40.
Define MyISAM?
MyISAM is the default storage engine for MySQL version priors to 5.5.
41.
What is the NOW() function in MySQL.
The NOW() function 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. Heap tables can be used when the data coming into the table is random and has no natural order but non-clustered indexes should always be created on heap tables.
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 the 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?
The 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 the COUNT(DISTINCT expr), we can count the 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

PHP MySQL PDO Database Connection and CRUD
Operations
Submit form data using PHP, AJAX and JavaScript
PHP User Authentication by IP Address
Simple File Upload Script in PHP
Convert MySQL to JSON using PHP
Polling system using PHP, Ajax and MySql
PHP user registration and login/ logout with
secure password encryption
File Upload Validation in PHP

General Knowledge

listen
listen
listen
listen
listen
listen
listen
listen
listen


Learn Popular Language

listen
listen
listen
listen
listen