MySql Interview Questions

MySQL is the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation.

Ques:-What is the default port for MySQL Server?
Ans:- The default port for MySQL server is 3306.

Ques:- How to Create a database on the mysql server through query?
Ans:- create database databasename

Ques:- How to show all database on the mysql server through query?
Ans:- show databases

Ques:- How to delete a database from mysql server?
Ans:- drop database databasename

Ques:- How to create table into mysql server?
Ans:- CREATE TABLE table_name (column_name column_type);
Example:- [php]
CREATE TABLE employee(name VARCHAR(20), address VARCHAR(20));
[/php]

Ques:- How To see all the tables from a database?
Ans:- show tables

Ques:-How to see table’s field formats or description of table?
Ans:- describe tablename

Ques:-How to delete a table?
Ans:- drop table tablename

Ques:-How you will Show all data from a table?
Ans:- SELECT * FROM tablename

Now():- It is used to show current year,month,date with hours,minutes and seconds.

CURRENT_DATE():- It is used to show current year,month and date.

MySQL supports several storage engines that act as handlers for different table types.

MYISAM:
(i) MYISAM is the default MySQL storage engine.
(ii) MYISAM supports Table-level Locking.
(iii) MyISAM is faster than InnoDB.
(iv) MyISAM does not support foreign keys.

InnoDB:
(i) InnoDB is not default MySQL storage engine.
(ii) InnoDB supports Row-level Locking.
(iii) InnoDB is slower than MYISAM.
(iv) InnoDB supports foreign keys.

A trigger is a stored program that are run automatically when a specified change operation (SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table.

there are six type of triggers in mysql
(i) BEFORE INSERT
(ii) AFTER INSERT
(iii) BEFORE UPDATE
(iv) AFTER UPDATE
(v) BEFORE DELETE
(vi) AFTER DELETE

Primary Key:- (i) It has unique value and it can’t accept null values.
(ii) We can have only one Primary key in a table.

Unique Key:- (i) It has unique value and it can accept only one null values.
(ii) We can have more than one unique key in a table.

Candidate Key:- candidate key full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key. So thus type of key is known as candidate key. Every table must have at least one candidate key but at the same time can have several.

Ques:- Maximum salary of the employee?
Ans:- [php]
select max(salary) from employee;
[/php]

Ques:- Second highest Maximum salary of the employee?
Ans:- We can get through sub-query, limit etc.
(i) Through Sub-query
[php]
SELECT max(salary) FROM `employee` where salary<(select max(salary) from employee)
[/php]

(ii) Through Limit
[php]
SELECT salary FROM `employee` Order by salary desc limit 1,1
[/php]

In this eg. limit is not work correctly because, salary may be same more than one person so its not good for use, it can use if employee salary is unique.

if Interviewer ask, how many person get second highest salary or third highest salary or fourth highest salary then you can use simple query method

SELECT salary FROM `employee` emp_a where (n-1)= (select count(distinct(salary)) from employee emp_b where emp_b.salary>emp_a.salary)

where n is use for number of highest salary

if you want to get second highest salary then
[php]
SELECT salary FROM `employee` emp_a where (2-1)= (select count(distinct(salary)) from employee emp_b where emp_b.salary>emp_a.salary)
[/php]

In this example 2 employee has second highest salary

if you want to get third highest salary then
[php]
SELECT salary FROM `employee` emp_a where (3-1)= (select count(distinct(salary)) from employee emp_b where emp_b.salary>emp_a.salary)
[/php]

“JOIN” is an SQL keyword used to query data from two or more related tables.

Types of join:-
1) Inner Join
2) Left Join
3) Right Join
4) Full Join
5) Self Join

Example:- In the above figure, it has two tables, (i) Employee (ii) location

(1) Inner Join:- It require that a row from the first table has a match in the second table based on the join conditions.
OR
An inner join of two tables gives the result of intersect of two tables.

Ques:- Please write a query fetch employee name with location record?
mysql->SELECT e.emp_name,l.location_name from employee e inner join location l on e.emp_id=l.emp_id

(2) Left Join:- It returns all rows from the left table (employee), with the matching rows in the right table (location). The result is NULL in the right side when there is no match.

mysql->SELECT e.emp_name,l.location_name from employee e left join location l on e.emp_id=l.emp_id

(3) Right Join:- It returns all rows from the right table (location), with the matching rows in the left table (employee). The result is NULL in the left side when there is no match.
mysql->SELECT e.emp_name,l.location_name from employee e right join location l on e.emp_id=l.emp_id

(4) Full Join:- It returns all records in both tables regardless of any match. Where no match exists, the missing side will contain NULL.
OR
A full outer join will give you the union of employee and location table,
mysql->(SELECT e.emp_name,l.location_name from employee e left join location l on e.emp_id=l.emp_id) union (SELECT e.emp_name,l.location_name from employee e right join location l on e.emp_id=l.emp_id)

(5) Self Join:- SELF JOIN is used to join a table to itself using join statement.
Ques:- write a query, get the employee name with employee reporting officer name?

mysql->SELECT e1.emp_name,e2.emp_name as reporting_officer_name from employee e1 left join employee e2 on e1.emp_reporting_officer=e2.emp_id

Distinct operator is used when we want unique value in the column.

mysql> SELECT DISTINCT columnname FROM tablename

When we want the value in ascending or descending order through the column then we use Order by operator

Note:- By default Order is ascending we can change into descending order.

mysql> SELECT col1,col2,col3,col4 FROM tablename ORDER BY col1 DESC;

mysql> SELECT col1,col2,col3,col4 FROM tablename ORDER BY col1 ASC;

count operator is use for count the row in the table.

mysql> SELECT COUNT(*) FROM tablename;

LIKE operator in mysql, is used in a WHERE clause to search for a specified pattern in a column.

Wildcard characters are used with the SQL LIKE operator.
(i) % :- A substitute for zero or more characters.
(ii) _ :- A substitute for a single character.

In this above figure, we have employee table which has three fields emp_id, emp_name, emp_reporting_officer.

Example:- (i) Write a query, get the all employee which start from “emp”

mysql->SELECT emp_id, emp_name FROM `employee` where emp_name like “emp%”

Example:- (ii) Write a query, get the all employee which ending from “A”

mysql->SELECT emp_id, emp_name FROM `employee` where emp_name like “%A”

Example:- (ii) Write a query, get the all employee which starting with any character, followed by “mpC”

mysql->SELECT emp_id, emp_name FROM `employee` where emp_name like ‘_mpC’