mysql interview questions for 1 year experience

Q. Which one is the default storage engine in mysql ?
A.  InnoDB is the default storage engine in latest mysql.
      MyISAM  is the default storage engine before mysql 5.5.5

Q. What is difference between MyISAM and InnoDB ?
A. Below are the differences based on mysql 5.5.5

MyISAM       
    Storage limits:256TB   
    MVCC    : No       
    Full-text search indexes : Yes          
    Transaction support : No
    Clustered indexes : No 
    Locking granularity : Table
    Data caches : No
    Foreign key support : No
    Store table in 3 files : .frm (table format) extension, .MYD (data file) extension,
                                             .MYI (MYIndex) extension.
InnoDB
    Storage limits:64TB
    MVCC    : Yes
    Full-text search indexes : No
    Transaction support : Yes
    Clustered indexes : Yes
    Locking granularity : Row
    Data caches : Yes
    Foreign key support : Yes   
    Store table in one single system tablespace

Q. What are the difference b/w primary key and unique key ?
A. Only one primary key exists in one table while multiple unique keys can exist.
     Primary key doesn't allows null values while unique key can be null (only single
     null can be allowed)
     We can make unique keys with multiple columns.

Q. Write the query to set foreign key ?
A.
CREATE TABLE user (
    id int(11) NOT NULL,
    userName varchar(200) NOT NULL,
    password varchar(200) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE user_details (
    id int NOT NULL,
    userId int NOT NULL,
    fName varchar(255) NOT NULL,
    lName varchar(255) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (userId) REFERENCES user(id)
);

Q. Write the query to find the 2nd maximum salary ?
A. Take the example of Employee table.
     CREATE TABLE `tblEmp`
     (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `eName` varchar(200) DEFAULT NULL,
       `salary` int(11) DEFAULT NULL,   PRIMARY KEY (`id`)
     );

    and below are the sample data:
 
   id    eName    salary
    1    sh1            1000
    2    sh2            500
    3    sh3            4000
    4    sh4            2000
    5    sh5            700
    6    sh6            800
    7    sh7            1500
    8    sh8            3000
    9    sh9            200

    Now, the query to find the 2nd max salary :
 
    SELECT e.eName, e.salary FROM tblEmp e ORDER BY salary DESC LIMIT 1,1;


   Also, If you want go to get the nth maximum salary then use the below query :

    SELECT e.eName, e.salary FROM tblEmp e ORDER BY salary DESC LIMIT n-1,1;


Q. What are the joins ?
A. SQL Joins are used when we need data from two or more tables. Both tables must have related column. Below are the joins used in MySQL .

 INNER JOIN: Returns records that have matching values in both tables

 LEFT JOIN or LEFT OUTER JOIN : Return all records from the left table, and the 
 matched records from the right table.

 RIGHT JOIN or RIGHT OUTER JOIN: Return all records from the right table, and the
 matched records from the left table.

 FULL JOIN or FULL OUTER JOIN : Return all records when there is a match in either
 left or right table