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:
- INNO DB
4) What is the difference between DELETE,TRUNCATE and DROP in MySQL?
|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 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 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, 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?
10) What are meant by Joins in MySQL?
In MySQL the Joins are used to select the data from more than one table.
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?
- 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:
function for returning current date and time as single value.SELECT NOW();
function for returning the current date or time.SELECT CURRDATE ();
function to concatenates two string values creating single string output.SELECT (string 1, string 2, string 3);
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.
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 ?
20)How to delete duplicate rows in mysql?
By Using INNER JOIN :
t1.id > t2.id AND t1.email = t2.email;
21)How to find a string which contains “a_b”?
22)How to drop a column in table?
Let’s see an example:
23)How to change the name of table ?
24)How to change the column name in a table ?
Let’s see an example:
25) How to modify column in table.
Let’s see an example:
26) How to get employee details from employee table whose joining year is “2013”?
27) How to get department wise maximum salary from employee table order by salary ascending?
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:
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 –
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.
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:
38) Replace the substring “A” with “B”:
SELECT REPLACE(“ABC ABC ABC”, “A”, “B”);
39) Is Mysql query is case sensitive?
- SELECT VERSION(), CURRENT_DATE;
- SeLect version(), current_date;
- seleCt vErSiOn(), current_DATE;