Mysql Interview Questions

1.What is Difference between MYISAM and INNODB ?

ANS :

MYISM	INNODB
1.      MYISAM supports Table-level Locking	1.      InnoDB supports Row-level Locking
2.      MyISAM designed for need of speed	2.      InnoDB designed for maximum performance when processing high volume of data
3.      MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS	3.      InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4.      MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)	4.      InnoDB stores its tables and indexes in a tablespace
5.      MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.	5.InnoDB supports transaction. You can commit and rollback with InnoDB
6.      MYISAM supports fulltext search	6.InnoDB supports Fulltext index search
7.      You can use MyISAM, if the table is more static with lots of select and less update and delete.	7.you can use InnoDB for DML operation.

2. Give me an example for IF and case conditions in mysql?

Ans:
       
   UPDATE  cscart_users SET  is_root = IF(is_root = 'Y','N','Y')

   UPDATE  cscart_users SET  is_root =  CASE WHEN is_root = 'N' THEN 'Y' ELSE 'N' END 

3.Create dummy table structure.

     CREATE TABLE new_table LIKE old_table;  

 4. Move  distinct values Data from one table to another table

      INSERT INTO new_table SELECT * FROM old_table;

5. Transfer data from one table to another new table

   create table new_table select * from oldtable group by zip     
6. What is default port for MSQL server ? The default port for MySQL Server is 3306. Another standard default is port 1433 in TCP/IP for SQL Server. 

7. What do DDL, DML, and DCL stand for? DDL is the abbreviation for Data Definition Language dealing with database schemas as well as the description of how data resides in the database. An example is CREATE TABLE command. DML denotes Data Manipulation Language such as SELECT, INSERT etc. DCL stands for Data Control Language and includes commands like GRANT, REVOKE etc.8. What are meant by Joins in MySQL?

    In MySQL the Joins are used to query data from two or more tables. The query is made using relationship between certain columns existing in the table. There are four types of Joins in MySQL. Inner Join returns the rows if there is at least one match in both the tables. Left Join returns all the rows form the left table even if there is no match in the right table. Right Join returns all the rows from the right table even if no matches exist in left table. Full Join would return rows when there is at least one match in the tables.

9.What are the limits for using columns to create the Index?

      Different types of strings that can be used for database columns are SET, BLOB, VARCHAR, TEX, ENUM, and CHAR.

10.What is the difference between a candidate key and a primary key?

      There can be multiple Candidate keys in a table in relation database e.g. Oracle, MySQL, Sybase or MSSQL but only one primary key is permitted.

  An example of Primary key and Candidate key can be ID and SSN number(Social Security number) in a Employee table, Since both can identify each employee uniquely they are candidate key and any one can become primary key. Now if you have to choose between them as primary key, I will go ID as primary key because SSN is sensitive information and may not be allow/not safe to use as String in queries as frequently as ID. Second reason of choosing ID over SSN as primary key can be use of ID as primary tracking ID within organization and its frequent use all over the place. Once you choose a primary key, All candidate key are like unique keys.

11.What are the TRIGGERS that can be used in MySQL tables? 

     •    BEFORE INSERT

·         AFTER INSERT

·         BEFORE UPDATE

·        AFTER UPDATE

·          BEFORE DELETE

·          AFTER DELETE

  Ex: 

create trigger update_customer  //Trigger Name  

 before update on customer   
 for each row        //Before update on parent
  begin
      insert into mini_statement values (new.acc_no, new.avail_balance)  
  end

 

12. What is the difference between FLOAT and DOUBLE?

FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.

 13.What is the difference between CHAR and VARCHAR?

1. CHAR and VARCHAR are differ in storage and retrieval.
2. CHAR column length is fixed while VARCHAR length is variable.
3. The maximum no. of character CHAR data type can hold is 255 character while VARCHAR can hold up to 4000 character.
4. CHAR is 50% faster than VARCHAR.
5. CHAR uses static memory allocation while VARCHAR uses dynamic memory allocation.

14.What is the difference between MySQL_connect and MySQL_pconnect?

Mysql_connect:

1. It opens a new connection to the database.
2. Every time you need to open and close database connection, depending on the request.
3. Opens page every time when it loaded.

Mysql_pconnect:

1. In Mysql_pconnect, "p" stands for persistent connection so it opens the persistent connection.
2. the database connection can not be closed.
3. it is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.

15.What does " i_am_a_dummy flag" do in MySQL?

The " i_am_a_dummy flag" enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.

16.How to get the current date in MySQL?

select CURRENT_DATE();

17.How to change a password for an existing user via Mysqladmin?

 Mysqladmin -u root -p password "newpassword".

 18.How to display Nth highest salary from a table in a MySQL query?

 select distinct(salary) from employee order by salary desc limit n-1,1  
   (OR)
SELECT DISTINCT salary

FROM   employees e1
WHERE  1 = (SELECT Count(DISTINCT salary)
            FROM   employees e2
            WHERE  e2.salary > e1.salary)
ORDER  BY salary DESC;

19.How many columns can you create for an index?
You can create maximum of 16 indexed columns for a standard table.

20.What is the usage of ENUMs in MySQL?

 ENUM is a string object used to specify set of predefined values and that can be used during table creation.

exp: Create table size(name ENUM('SUNDAY', 'MONDAY','TUESDAY');

 21.What is View and give me an example ?

A view is a virtual table which consists of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship.

ex :
CREATE VIEW [VeiwName] AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = No; 

22.What is An Index ?

 An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

Index architectures are classified as clustered or non-clustered. Clustered indexes are indexes whose order of the rows in the data pages corresponds to the order of the rows in the index. This order is why only one clustered index can exist in any table, whereas, many non-clustered indexes can exist in the table.

 A clustered index is a type of index where the table records are physically re-ordered to match the index. A nonclustered index, on the other hand, is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

23. How to get json object in mysql ?

SELECT json_object('name', name, 'phone', phone)  FROM person;

 24. How to import mysql database using comand prompt ?

C:\xampp\mysql\bin>mysql -u username -ppassword databasename < C:\file.sql

25. What is difference between union and join ? 
A.  

A join connects tables horizontally: take the data from table A and add the columns from table B, on a row-by-row basis. For example, you might join Customer to Orders on the basis of CustomerID to get the name of each customer and the date of each order they've placed.

A union stacks the rows of two tables, vertically: take the data from table A and add rows from table B. This works when the fields are similar. For example, if you move old orders to an archive table periodically to keep your main Orders table short, you might run a UNION against the two tablets to get lifetime sales.

This applies equally for all common databases; MySQL is not unusual in its use of these two commands.

26. What is unionall in mysql ?
Ans: Union All Returns the duplicate values also. But union returns distinct values from two tables.

27. What is difference between functions and procedures ?

Ans: 

 

S.No	Functions	Procedures
1	Function is compiled and executed every time it is called	Procedure is compiled once and executed every time it is called
2	The functions always returns an value	Procedures need not to return an value but it can return 1 or n value
3	Functions have input parameters	Procedures have input and out put parameters
4	Functions called by procedures	Procedures can’t call from functions
5	You can’t use try catch blocks in functions	You can use try catch blocks in procedures
6	We can use functions in select query	We cant use procedure in select query.
7	Functions only allows select statements.	Procedure can allow DML statements and as well select statements also
8	Ex:  crate function Num_Of_Years(date1 date) return int Deterministic  begin      declare date2 date;     select current_date() into date2;     return year(date2)-year(date1); end; //Calling functions  select emp_id,first_name,Num_Of_Years(start_date) as 'years' from employee	Ex:  CREATE PROCEDURE country_hos (IN con CHAR(20)) BEGIN   SELECT Name, HeadOfState FROM Country   WHERE Continent = con; END  // Call Stored Procedure CALL country_hos('Europe');

     
28. What is difference between distinct and group by ?
Ans: GROUP BY lets you use aggregate functions, like AVG, MAX, MIN, SUM, and COUNT. On the other hand DISTINCT just removes duplicates.

    SELECT name, SUM(transaction) FROM myTbl GROUP BY name
    SELECT name, Distinct(SALARY)  from Employee 

29. Difference between Having clause and Group by clause
  
Having Clause is basically like the aggregate function with the GROUP BY clause. The HAVING clause is used instead of WHERE with aggregate functions. 

SELECT COUNT (SALARIES) AS COUNT_SALARIES, EMPLOYEES FROM EMPLOYEES GROUP BY SALARIES HAVING COUNT(SALARIES) > 1; 

  

S.No.	Having Clause	GroupBy Clause
1.	It is used for applying some extra condition to the query.	The groupby clause is used to group the data according to particular column or row.
2.	Having cannot be used without groupby clause.	groupby can be used without having clause with the select statement.
3.	The having clause can contain aggregate functions.	It cannot contain aggregate functions.
4.	It restrict the query output by using some conditions	It groups the output on basis of some rows or columns.

 30 . How to add index/primary key to existing table ?

ALTER TABLE `table` ADD INDEX `product_id_index` (`product_id`)

ALTER TABLE `tbl_student` ADD PRIMARY KEY (`student_id`)

31. How modify existing column datatype ?

ALTER TABLE employee ADD FirstName  VARCHAR(255);

ALTER TABLE employee MODIFY COLUMN FirstName VARCHAR(255);

32. How to change existing column name?

MySQL <=8
 ALTER TABLE table_names CHANGE `old_column_name` `new_column_name` varchar(50);
MySQL 8
 ALTER TABLE table_name RENAME COLUMN `old_col_name` TO `new_col_name`;

33. B RIGHT JOIN A, A LEFTJOIN B. Is it both statements give same result?
Ans: Yes both statements give same Results. Here A table is main table for 
both the statements

34. Write MySQL command to update a table with certain values but with 
an extra condition: if the row to update is not found you need to create 
a new entry.

 

35. What is cursor?
Ans: A cursor is a database object that allows you to retrieve and manipulate rows 
from a result set one at a time.
36. What are differences between MySQL Vs PostgreSQL Vs SQL Server

 http://pasteboard.co/c19l4Vgl9H8h.png

36. How to get department wise first two maximum salary's ?
ANS: PostGresSQL:
 
 WITH RankedSalaries AS (
    SELECT
        e.EmployeeName,
        d.DepartmentName,
        e.Salary,
        RANK() OVER (
            PARTITION BY d.DepartmentID ORDER BY e.Salary DESC
        ) AS SalaryRank
    FROM employee e
    INNER JOIN department d ON e.DepartmentID = d.DepartmentID
 )
 SELECT DepartmentName, EmployeeName, Salary
 FROM RankedSalaries
 WHERE SalaryRank <= 2;

Mysql:
 SELECT department_name, employee_name, salary
 FROM (
    SELECT
        d.name as department_name,
        e.firstname as employee_name,
        e.salary,
        ROW_NUMBER() OVER (
            PARTITION BY e.DepartmentId 
            ORDER BY e.salary DESC
        ) AS salary_rank
    FROM employee e
    INNER JOIN department d ON e.DepartmentId = d.id
) AS ranked_salaries
WHERE salary_rank <= 2