MySQL interview questions for experienced

MySQL query for finding max and second max and nth max salary for a employee table using MySQL

Suppose you have a table ’employee’ given as below
——————————
| EmpId        |    Salary      |
| 2                  |    2000        |
| 3                  |    5000        |
| 5                  |    2000        |
| 8                  |    3000        |
| 9                  |    4564         |
| 12                |    3000        |
| 13                |    2000        |
| 15                |    8000        |
——————————
Now the question is : find max salary and 2nd max salary of employee (MySql Query)

1. Highest salary

SELECT MAX(Salary) FROM employee

2. 2nd highest salary

SELECT MAX(Salary) FROM employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM employee )

OR

SELECT Salary from employee order by Salary desc limit 1,1

You can fetch both as below

SELECT (SELECT max(Salary) from employee) as max_sal, (SELECT Salary from employee order by Salary desc limit 1,1) as max_2nd

SELECT (SELECT MAX(Salary) FROM employee) max_sal, (SELECT MAX(Salary) FROM employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM employee )) as max_2nd

nth height Salary

SELECT * FROM employee Emp1 WHERE (n-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

SELECT EmpId, Salary FROM employee  ORDER BY Salary DESC  LIMIT (n-1) , 1

 

MySQL query for case sensitive string comparison on MySQL.

 

According to MySQL Manual (http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html), It’s depends on what collation you have chooses during the column creation.

You can have a look on various collation available for column data storage. If it’s ‘_ci’ (case-insensitive) or ‘_cs’ (case-sensitive)

If you want the comparison to be case sensitive, you could add COLLATE just like this in your SQL query:
WHERE `colname` COLLATE latin1_general_cs = 'KeyWord'

 

You can also do by cast the keyword of column value to binary as when one side in the comparison is cast to binary the comparison is done binary and it will be case-sensitive

SELECT * FROM your_table WHERE BINARY description = 'KeyWord'

OR

SELECT * FROM your_table WHERE description = BINARY 'KeyWord'

 

To determine whether a value will compare as a non-binary or binary string, use the COLLATION() function. This shows that VERSION() returns a string that has a case-insensitive collation, so comparisons are case insensitive or vice-versa:

SELECT COLLATION(VERSION());

According to MySQL 5.0 Manual(http://dev.mysql.com/doc/refman/5.0/en/charset-column.html), If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation.

ALTER TABLE your_table CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

e.g

ALTER TABLE employee CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;

ALTER TABLE employee CONVERT TO CHARACTER SET utf8;

For modifying the collation for a column only you can follow as below

ALTER TABLE employee MODIFY  name VARCHAR(255) CHARACTER SET latin1  COLLATE latin1_swedish_ci;

 

What are Heap tables?

 

HEAP tables are present in memory and they are used for high speed storage on temporary basis. These tables are commonly known as memory tables.

Below are some feature of Heap tables.

• BLOB or TEXT fields are not allowed
• Only comparison operators can be used =, <,>, = >,=<
• AUTO_INCREMENT is not supported by HEAP tables
• Indexes should be NOT NULL

 

Differences between HEAP and Temporary tables?

The heap tables are shared among clients while temporary tables are not shared.

Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).

 

What is the default port for MySQL Server?

The default port for MySQL server is 3306.

 

What is the difference between primary key and candidate key?

Every row of a table is identified uniquely by primary key. There is only one primary key for a table.

Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

 

How many columns can you create for an index?

You can create maximum of 16 indexed columns for a standard table.

 

What does myisamchk do?

myisamchk is a command line tool for checking, repairing and optimizing non-partitioned MyISAM tables.

 

What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?

It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.

 

Where MyISAM table will be stored and also give their formats of storage?

Each MyISAM table is stored on disk in three formats:
The ‘.frm’ file stores the table definition
The data file has a ‘.MYD (MYData) extension
The index file has a ‘.MYI’ (MYIndex) extension

 

How to enter Characters as HEX Numbers?

If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).

A HEX number string will be automatically converted into a character string, if the expression context is a string.

 

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

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

 

What is SQLyog?

SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.

 

What is a trigger in MySQL & how many TRIGGERS are allowed in MySQL table?

A trigger is a set of codes that executes in response to some events.
SIX triggers are allowed in MySQL table. They are as follows:

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE and
AFTER DELETE

 

Some Common SQL Function?

CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.

FORMAT(X, D) – Formats the number X to D significant digits.

CURRDATE(), CURRTIME() – Returns the current date or time.

NOW() – Returns the current date and time as one value.

MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.

HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.

DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculate age

SUBTIMES(A, B) – Determines the difference between two times.

FROMDAYS(INT) – Converts an integer number of days into a date value.

 

Explain CSV tables.

They special tables for which the data is saved into comma-separated values files. They cannot be indexed.
CSV table stores data in plain text and tabular format. It typically contains one record per line. CSV tables are most widely used to store phone contacts to Import and Export and can be used to store any sort of plain text data.

 

Explain advantages of InnoDB over MyISAM.

InnoDb supports locking of rows while MyISAM supports only table locking.
Data integrity is more in InnoDB.
Transactional nature of InnoDB enables easy and online backups.

 

Explain advantages of MyISAM over InnoDB.

Data is not cached by the MySQL query browser.
MyISAM is a simple storage engine.
MyISAM provides more optimization.
MyISAM has a low relative memory use.

 

How large can a MySQL database become?

Thus, as long as your file system supports a maximum file size of at least 256 TB,
you will never run into any file system limitations before MySQL runs into other internal limits.

 

How to get the date after 7 days using MySQL query

select DATE_ADD(NOW(), INTERVAL 7 DAY) as last_date.

 

Define system tables.

mysql
All the user and permission information is stored in the mysql database in a set of tables known as the grant tables.

information_schema
The information_schema database really is not a database but an interface to various system metadata

 

MySQL command for showing current configuration variables

SHOW VARIABLES
SHOW VARIABLES LIKE '%max%'

 

For updating these configuration variable values

SET sort_buffer_size=10000;


SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;