MySQL Interview Questions

1) What is MySQL?

MySQL is a database computer language designed for the retrieve and management of data in relational database.

furthermore, below are the following points to become MYSQL so popular:
  • MySQL is oftenused as an free back-end database for many web and another open source applications.
  • MySQL is a relational database management system.
  • Easy to use with basic SQL Concepts
  • MySQL is fast reliable and flexible
  • Secure by encrypting passwords.

2) What are the technical specification or feature of MySQL?

MySQL has the following technical specifications –
  • Flexible structure
  • High performance
  • Manageable and easy to use
  • Replication and high availability
  • Security and storage management

3) What are the different tables present in MySQL?

By default, there are many tables at present. But, MyISAM is the default database engine used in MySQL.

There are five types of tables that are present:
  • MyISAM
  • Heap
  • Merge
  • INNO DB
  • ISAM

4) What is the difference between DELETE,TRUNCATE and DROP in MySQL?

DELETE TRUNCATE DROP
In DELETE, Records can be delete one by one or all at a time. But table structure will be there. In TRUNCATE, All data can be delete at a time only. But table structure will be there. In DROP, Table will remove along with records.
Can be rollback Can’t rollback Can’t rollback

5) What is the difference between CHAR and VARCHAR?

CHAR VARCHAR
CHAR column length is fixed While, VARCHAR length is variable
The maximum no. of character CHAR data type can hold is 255 character While,VARCHAR can hold up to 4000 character
CHAR is 50% faster than VARCHAR While,VARCHAR is slower than CHAR
CHAR uses static memory allocation While,VARCHAR uses dynamic memory allocation

6) What do DDL, DML, and DCL stand for?

DDL DML DCL
DDL is the abbreviation for “Data Definition Language”. DML is the abbreviation for “Data Manipulation Language”. DCL is the abbreviation for “Data Control Language”
CREATE, ALTER, DROP, TRUNCATE etc. SELECT, INSERT, UPDATE, DELETE etc. GRANT and REVOKE.

7) What is the difference between Primary Key and Unique Key?

Primary Key Unique Key
Can not accept NULL value Accept only one NULL value
We can have only one Primary Key in a table We can have multiple Unique Key in a table
Primary Key can be made foreign key of other table Unique Key can not be a foreign Key

8) What is the difference between WHERE and HAVING?

WHERE HAVING
WHERE, can be use in SELECT,UPDATE and DELETE Command HAVING, Only use in SELECT command
It’s filter the records row wise It’s filter the records group wise
It’s not conatin aggregate function It’s conatin aggregate function

NOTE : Aggregate function performs a calculation on a set of values and returns a single value. MySQL provides many aggregate functions that include AVG , COUNT , SUM , MIN , MAX , etc.

9) How to get the current date in MySQL?

SELECT CURRENT_DATE();

10) What are meant by Joins in MySQL?

In MySQL the Joins are used to select the data from more than one table.

Join Syntax:

SELECT t1.id FROM table_name1 t1 join table_name2 on t1.id = t2.id;

There are different types of joins available, and they are following:
1)INNER JOIN : INNER JOIN keyword is used to select all from both table.
2) LEFT JOIN : LEFT JOIN keyword return all rows from the left table with the match row in the right table.
3)RIGHT JOIN : RIGHT JOIN keyword return all rows from the right table with the match row in the left table.
4) OUTTER JOIN : OUTTER JOIN keyword return all rows from the left table and from the right table.

11) How to find second highest salary in sql?

select DISTINCT(salary) from employee order by salary desc limit 1,1

NOTE :

  • limit 0,1 – Top max salary
  • limit 1,1 – Second max salary
  • limit 2,1 – Third max salary
  • limit 3,1 – Fourth max salary

12) What are the common MySQL functions?

Common MySQL functions are as follows:
  • NOW –

    function for returning current date and time as single value.

    SELECT NOW();
  • CURRDATE –

    function for returning the current date or time.

    SELECT CURRDATE ();
  • CONCAT –

    function to concatenates two string values creating single string output.

    SELECT (string 1, string 2, string 3);
  • DATEDIFF –

    function to determine difference two dates.

    SELECT DATEDIFF (X, Y);

13) What is the different between NOW() and CURRENT_DATE()?

NOW () command is used to show current year,month,date with hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.

14) What are the TRIGGERS that can be used in MySQL tables?

A Trigger is a set of action that are run automattcaly, when a specified changed operation (INSERT,UPDATE AND DELETE) is performed on a table.

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE
  • AFTER DELETE

15) What do you mean by % and _ in the LIKE statement?

% corresponds to 0 or more characters while, _ is exactly one character in the LIKE statement.

16) what is view in mysql with example?

A view is a virtual table and its contains row and column, just like a real table. By virtual, we mean, the tables do not store any data of their own.

Create view view_name as select column_name from table_name.

Eg:

Create view product_list as select product_id, product_name from product.

17) what is index in mysql?

User can not see the index, but they are just used by database search engine to locate records very fast.

18) What is the difference between LIKE and REGEXP operators in MySQL?

LIKE is denoted using the % sign. For example:SELECT * FROM user WHERE user name LIKE “%NAME”.• On the other hand the use of REGEXP is as follows:SELECT * FROM user WHERE username REGEXP “^NAME”;

19)How to get unique DEPARTMENT from employee table ?

SELECT distinct DEPARTMENT from EMPLOYEE

20)How to delete duplicate rows in mysql?

By Using INNER JOIN :

DELETE t1 FROM contacts t1
INNER JOIN
contacts t2
WHERE
t1.id > t2.id AND t1.email = t2.email;

21)How to find a string which contains “a_b”?

SELECT * FROM TABLE_NAME WHERE name LIKE ‘%a\_b%’;

22)How to drop a column in table?

ALTER table table_name drop column column_name

Let’s see an example:

ALTER table students drop column RollNO;

23)How to change the name of table ?

Rename students to employee;

24)How to change the column name in a table ?

Alter table table_name Rename column column_name to new_column_name;

Let’s see an example:

Alter table students Rename column Roll to Roll_numner

 

25) How to modify column in table.

Alter table table_name modify column column_name(length);

Let’s see an example:

Alter table students modify Roll_number(4)

 

26) How to get employee details from employee table whose joining year is “2013”?

Select * from EMPLOYEE where year(joining_date)=’2013′;

27) How to get department wise maximum salary from employee table order by salary ascending?

select DEPARTMENT,max(SALARY) MaxSalary from employee group by DEPARTMENT order by MaxSalary asc;

28) Explain advantages of MyISAM over InnoDB?

  • MyISAM is faster than InnoDB in most of the cases.
  • MyISAM table is stored as a separate file which can be compressed.
  • This means that MyISAM has a better storage management.
  • MyISAM supports full indexing that is not supported by InnoDb.

29) What is the default port of MySQL Server?

The default port of MySQL Server is 3306.

30) Write a query to count the number of rows of a table in MySQL.

SELECT COUNT emp_id FROM employee;

31) Give string types available for column?

The string types are:

  • SET
  • BLOB
  • ENUM
  • CHAR
  • TEXT
  • VARCHAR

32) What is the difference between BLOB AND TEXT?

A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –

  • TINYBLOB
  • BLOB
  • MEDIUMBLOB
  • LONGBLOB

They all differ only in the maximum length of the values they can hold. A TEXT is a case-insensitive BLOB. There are four types of TEXT.

  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

 

33) What is heap table?

Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.

34) What is ISAM?

ISAM is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

35) What is InnoDB?

InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 8.0, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.

36) What is the difference between mysql_fetch_array and mysql_fetch_object?

Following are the differences between mysql_fetch_array and mysql_fetch_object:
mysql_fetch_array() – Returns a result row as an associated array or a regular array from database.
mysql_fetch_object – Returns a result row as object from database.

37) What are the nonstandard string types?

Following are Non-Standard string types:

  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

38) Replace the substring “A” with “B”:

SELECT REPLACE(“ABC ABC ABC”, “A”, “B”);

39) Is Mysql query is case sensitive?

No.

  1. SELECT VERSION(), CURRENT_DATE;
  2. SeLect version(), current_date;
  3. seleCt vErSiOn(), current_DATE;