MySQL Interview Questions and Answers

 

MySQL is most usable database in the World.  MySQL is free and easy to execute. We will study MySQL interview questions and answers here. If you are also looking for the interview questions on MySQL then you visited the right place.

Every company either its small scale, medium scale or large scale everywhere MySQL database is used. Below is the MySQL interview questions and answers.

 

List of frequently asked MySQL interview questions and Answers

What is MySQL?

MySQL is open source SQL query database. This is widely used database all over the World.

 

What is SQL?

SQL stands for Structured Query Language. SQL is language which interacts with the database by using line of statements.

 

What is Query?

Query is line of statements which SQL used to interacts with database to perform the operations.

 

How to create database in MySQL?

To create a database in MySQL create database query is used.

e.g : create Employee database in MySQL. Employee database can be created by using create command in MySQL.

create database Company;

This will create Company database in MySQL.

 

What is Default Storage engine in MySQL?

InnoDB is the default storage engine in MySQL.

 

What are the Various Storage engine in MySQL?

You can see storage engine of MySQL by hitting below query.

Show Engines;

 

Show engines; Query will return all the storage engines currently installed MySQL in your system

+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine
| NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| MyISAM | YES | MyISAM storage engine
| NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to
it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine
| NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for tempor
ary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine
| NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and f
oreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema
| NO | NO | NO |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
9 rows in set (0.03 sec)

Above 9 highlighted engines are storage engine in MySQL.

 

How to check MySQL version?

There are couple of ways to check the MySQL version of installed MySQL in your system

• When you open your MySQL command line, In the beginning MySQL version is shown

we can see that MySQL version is 5.6.51. We highlighted with yellow.

• We can get the MySQL version through MySQL query also. Query used to get the MySQL version is

select  version();

select version() query is used to get the version of installed MySQL. version() function return the MySQL version.

• MySQL version can also be fetch through @@version variable in MySQL. use Select @@version;

 

What is the default port of MySQL Database?

Default port of MySQL database is 3306. 3306 is the port which MySQL DB takes automatically while doing installation of MySQL.

 

How to comment the statement or code in MySQL?

Statement can be commented in MySQL by using double dash which is –. Anything you want to comment in MySQL, Just put double dash — in front of the statement and one white space is required.

-- This is comment in MySQL.

 

How to see all the tables inside database?

Query used to fetch the details of tables in a MySQL database is

show tables from database_name;

 

show tables command is used to fetch the details inside any database. database_name is the name of the database on which show table command will applied.

 

Which command is used to select the database before performing any operation?

use database_name is the query used to select the database before performing any operation. This is because, In database there could be multiple databases, so before performing any operation, we have to tell MySQL on which database action will be performed that’s why use command is used.

use database_name;

database_name is the name of the database.

 

How to create a new table in MySQL?

New table in MySQL is create through create statement. General syntax of create new table query in MySQL is :

create table table_name (col_name1 data_type, col_name2 data_type....);

• create and table are two keywords.
• table_name is the name of the table you want to create.
• col_name1 is name of the table column.
• data_type is the data type of column. data type represent that which kind of data will be stored in column.

Example:

Let’s take an example to understand create table query MySQL. We will create a new EMPLOYEE table in

CREATE TABLE EMPLOYEE (
EMPID int,
EMPNAME varchar(255),
DESIGNATION varchar(255),
SALARY varchar(10),
EMPTYPE varchar(10)
);

 

Above Create table statement will create EMPLOYEE table having EMPID,  EMPNAME, DESIGNATION , SALARY, EMPTYPE as its columns.

 

How to insert data in MySQL table?

To insert the data into MySQL database table insert query is used.

insert into tablename (col1,col2...) values (value1,value2..);

 

tablename is the name of the table in which data is going to be inserted. col1,col2 are name of the columns of a table and value1,value2 are actual values.

example:

insert into employee (EMPID, EMPNAME, DESIGNATION, SALARY, EMPTYPE) values (100,"Allen","Clerk","25000","Permanent");

 

How to fetch all data from a table in MySQL?

All the data is fetched from MySQL Database table by using select query. select query is used to fetch all the data from the table.

Select * from tableName;

 

* means all the data from tableName;

Example:

select * from employee;

It will get all the data from employee table.

 

How to fetch specific column value from a table in MySQL?

To fetch the specific column value from MySQL database table below query is used:

Select column_1,column_2 ... column_n from table_name;

column_1, column_2 is the list of the columns need to fetch from specific table_name;

Example

select empid from employee;

 

empid is the name of the column inside employee table.

 

How to update data in MySQL table?

To update the data into MySQL database table, update query is used.

update  table_name set column_name= new_value where unique_column_name=value;

 

using update query data can be updated or modify in MySQL table. In table there are lots of rows, to identify which column value to update, we have to use where clause. where clause act like a filter.

Example:

Suppose there is a requirement to update the salary of employee having empid=100 to “30000”. following query will be used

update employee set salary="30000" where empid=100;

 

Which query is used to check the schema of the table?

To check the schema of a table describe query is used.

describe table_name;

 

describe query will fetch the schema of a table_name.  It will provide the information of table’s columns, columns type, constraints etc.

 

How to delete data in MySQL table?

Delete the data means delete the rows or records. As in MySQL, everything is saved in rows.

To delete the rows Delete query is used with conditions, so that only those rows deleted which fulfil the reuirement.

Delete from tablename where <condition>;

example:

delete from employee where empid=101;

Above query will delete the record or row where empid=101;

 

How to delete MySQL table?

drop query is used to delete MySQL table.

drop table tablename;

tablename is the name of the table which is going to be deleted.

Example:

drop table test;

This will delete test table.

 

How to rename MySQL table?

MySQL table can be renamed by using rename query of MySQL.

Alter table tablename rename to new_tablename;

rename query is used with Alter to rename the MySQL table.

Example:

Alter table Employee rename to EMP;

Above query is used to rename the MySQL database table Employee to EMP;

 

How to rename MySQL table column?

MySQL table column can be renamed with following query

 alter table tablename change columnname new_columnname datatype;

Example:

Write a query to change the column name from emp to emp_id in MySQL.

 alter table employee change empid emp_id int(10);

How a table can be empty in MySQL?

truncate query is used to empty a MySQL table.

truncate tablename;

Example

truncate employee;

This will delete all the data inside employee table.

 

How to insure MySQL table column don’t have null value inserted?

To ensure that column don’t have null value, make that column NOT NULL.

CREATE TABLE EMP (
EMPID int NOT NULL,
EMPNAME varchar(255),
DESIGNATION varchar(255),
SALARY varchar(10),
EMPTYPE varchar(10)
);

 

How to create a primary key in MySQL?

Primary key in MySQL can be created in couple of ways:

During table creation

Primary key can be created during table creation in MySQL.

CREATE TABLE EMP (
EMPID int NOT NULL,
EMPNAME varchar(255),
DESIGNATION varchar(255),
SALARY varchar(10),
EMPTYPE varchar(10),
PRIMARY KEY(EMPID)
);

Using Alter Query

Primary key can be added in existing column in MySQL table by using Alter query.

 ALTER TABLE tablename ADD PRIMARY KEY (columnname);

Example:

 ALTER TABLE EMP ADD PRIMARY KEY (EMPID);

 

What is the difference between AND or OR operators in MySQL?

AND – AND operator in MySQL evaluates the conditions and return true if all condition are met.

OR – OR operator in MySQL evaluates the conditions and return true if any of the condition is true.

 

How to put more than one condition in MySQL Query?

Two or more conditions can be put by using AND or OR operator in MySQL.

 select * from Employee where EMPID =101 AND EMPID = 103;

In this example we put two condition by using AND operator.

 

What is the use of order by clause in MySQL?

order by clause is used to sort the query result in descending or ascending order.

SELECT EmpName FROM Employee  order by EmpName  desc;

SELECT EmpName FROM Employee  order by EmpName  asc;

 

What is by default order in MySQL?

In MySQL, By default order is ascending order.

 

Can we insert the records without mentioning the column name in insert query in MySQL?

Yes, we can insert into MySQL table without mentioning the column name in insert query.

insert into tablename values (val1, val2...);

 

All the values will be assigned by default to the corresponding columns.All values should match with the numbers of columns. if there are 5 columns then there should be 5 corresponding values. if values are more or less,then MySQL will throw an error.

 

How to Check whether column is null or not?

In MySQL, IS NULL is used to check whether any value is null or not.

IS NULL  – returns true if value is null.

IS NOT NULL  – returns true if value is not null.

 

Write a MySQL Query to get all records where lastName has no value.

Select * from Employee where lastName IS NULL;

 

Write a MySQL Query to get all records where lastName is not null.

Select * from Employee where lastName IS NOT NULL;

 

How to concatenate  in MySQL?

In MySQL concatenation can be done by using concate() function.

SELECT CONCAT(col1,col2) from tablename;

It will combine or concatenate col1 and col2.

 

How to check the length of a string in MySQL?

length() function is used to get the length of the column value.

Select length(value_to_check) from tablename;

 

How to remove extra spaces from left and right from the value in MySQL?

Using trim(), extra space or white space from left and right can be removed.

Select trim(value) from tablename;

 

What is the use of NOT operator in MySQL?

Not operator is used to reverse the value of the expression. if expression is true then NOT will make it false.

 

How to insert Null in value in MySQL?

In MySQL Null value can be inserted in table by using <strong>null</strong>. Just put null during insert statement.

 

How to show only 10 records in MySQL Select query?

Using limit clause, we can limit the number of records we have to fetch in select query. Put limit clause after MySQL query. First 10 records in MySQL can be fetched through below query

Select * from Employee limit 10;

 

What is the purpose of count function in MySQL?

Purpose of count() function is to calculate and return the total number of the records in the query. COUNT(*) is used to calculate the number of all the records.

Select count(*) from EMPLOYEE;

 

What is like operator in MySQL?

Purpose of like operator is to fetch the records with some criteria.

SELECT * FROM EMPLOYEE where location like ("I%"); 

 

It will fetch all records of EMPLOYEE table where location is starting from I. % means after I there could be anything like India,Ireland etc.

 

What are Joins in MySQL?

Joins are used to combine the data of one or more table. Joins are very useful in MySQL because using Joins we can combine the data of multiple table into one and thus we can reduce the time to call multiple tables and we can get only those columns which are required.

 

What are various type of Joins?

Following are the Joins in MySQL:

Inner Join: Inner join fetch those records which have match in records in all the tables who participate in Join.

Left Join: Left Join fetch all the records from the left table and also fetch the matching records from right table.

Right Join: Right Join fetch all the records from the right table and also fetch the matching records from left table.

Full Join : Full join is also called cross join. It returns all the records from both the tables.

 

How to insert current date in MySQL?

curdate() is used to get the current date in MySQL.

select curdate();

output

2022-09-06

How to insert current timestamp in MySQL?

now() function is used in MySQL to insert current timestamp.

select now()

output

2022-09-06 15:17:19

 

What is Group By clause in MySQL?

Group by clause is used to grouping the MySQL statement result. In MySQL, we can group the result with some column of the table.

example: if we want to know the number of employee from each city

select EmpName,city, count(city) from Employee group by city;

 

How to create a Stored procedure in MySQL?

Stored Procedure are created in MySQL with Create Procedure statement.

Example

CREATE PROCEDURE AllPermanentEmployees
AS
SELECT * FROM Employee where status = "Permanent";
GO;

In above example we created a stored procedure of all the employees who are permanent. name of the stored procedure is AllPermanentEmployees.

 

How to call Stored Procedure in MySQL?

By using EXEC statement stored procedure is called in MySQL.

Exec stored_procedure_name;

 

How to create triggers in MySQL?

Create Trigger statement is used to create the trigger in MySQL.  As per the official documentation of MySQL, general syntax of creating trigger is

Create Trigger triggerName

{ BEFORE | AFTER }  { INSERT | UPDATE | DELETE }

on TableName FOR EACH ROW

{ FOLLOWS | PRECEDES }

trigger_body;

Triggers are executed after or before any insert/update/delete statement.

 

How to create Alias in MySQL?

Alias is created in MySQL by using AS statement. Alias means to provide another name on which we are creating Alias.

Example:

 SELECT firstName AS fname FROM Employees; 

Here we created Alias of firstName to fname.

 SELECT * FROM Employee as emp; 

Employee table alias name is emp.

 

How to create index in Mysql?

Create Index statement is used to create the index in MySQL.

Example:

 Create Index index_name on Employee (name); 

index_name  is index name. Employee is table name and name is column name on which index is created.

 

How to create a view in MySQL?

Views are virtual tables created from one or more select statements. Create view statement is used to create the view in MySQL.

Example

Create view temp_emp AS

Select * from Employee where status is "Temp";

temp_emp view is created.

 

How to create a function in MySQL?

Create function statement is used to create the function in MySQL. Syntax of creating function is

CREATE FUNCTION function_name(param1,param2..)
RETURN datatype
body_of_the_function

 

What is the use of distinct keyword in MySQL?

Distinct keyword is used to fetch the unique records from the MySQL statement.

Example:

Select distinct city from Employee;

 

It  will fetch all unique city from Employee table.

 

So, Here we covered MySQL interview questions and answers of each and every topic of MySQL. We will update more and more interview questions of MySQL database. Hope these MySQL database questions will help you to crack interviews.