Tag Archives: MySQL

MySQL Interview Questions & Answers

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;

 

What is the difference between GROUP BY and ORDER BY in MySql?

What is the difference between GROUP BY and ORDER BY in MySql?

 

Mostly this question has been asked by the interviewer, even if you have used it many times but at that time generally the guy not satisfied the interviewer by the answer.

 

Please go through the below and give very appropriate answer and increase your chances of selection.

 

GROUP BY

 

GROUP BY is used to make the group-set of records based on the fields of the table or you can say GROUP BY keeps related data items together.

 

GROUP BY clause often used with the aggregate functions (SUM, AVG, MAX) on the columns in a query the other columns should be in group by query, you can use HAVING clause to apply the condition on the group-set that will fetch by the query.

 

Let’s take an example:
Suppose we have a table student as below.

 

tbl_student

——————————————–
Id                     Name
——————————————–
01                     Nirmal
98                     Stephen
65                     Mahesh
65                     Hodge
90                     Pamlea
90                     Paul
90                     Shakira
——————————————-

 

Question 1: Due to some mistake by the programmer, there was some duplicate id for some students. Fetch which id is duplicated and how many times?

Query:

SELECT Id, count(Id)
FROM tbl_student
GROUP BY Id

 

Question 2: Fetch which id is duplicated and how many times and the Ids should be greater than 80.

SELECT Id, count(Id)
FROM tbl_student
GROUP BY Id
HAVING Id > 80

 

ORDER BY

 

The ORDER BY clause is used to sort the result-set by a specified column either by ASC or DESC.
By default, ORDER BY clause sorts the records in ascending order.

 

ASC = Ascending (shortest first)
Desc = Descending (largest first)

 

SELECT Name
FROM tbl_student
ORDER BY Name ASC

 

I think it will make sense?

 

Difference between MySql drop table, truncate table, delete from table commands

DROP TABLE tablename;

DROP command is used for deleting the table and its structure from the data base.Use this command when you don’t need that table any more.

 

Other Spec:

1. Removes a table from the database.Table structures, indexes, privileges, constraints will also be removed.
2. Cannot be rolled back.
3. No Triggers can be fired.

 

DELETE FROM tablename;

DELETE command is used for deleting the records from the table, and it removing the table space which is allocated by the database, and returns number of rows deleted.Delete allows you to use a WHERE clause so you can delete specific records according to your requirement.

 

Other Spec:

1. Can be rolled back, as during DELETE operation, all the data get copied into Rollback Table space first, and then delete operation perform.
2. Does not reset identity of the table.
3. Triggers can be fired.

 

TRUNCATE TABLE tablename;

TRUNCATE operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
After this, the table is empty, and auto-incrementing keys are reset to 1.It faster than DELETE because delete scan the table to generate a count of rows that were affected.

 

Other Spec:

1. Removes all rows from a table, but the table structures and its columns, constraints, indexes remains.
2. Cannot be rolled back.
3. Resets the identity of the table.
4. Cannot use TRUNCATE on a table referenced by a FOREIGN KEY constraint.
5. No Triggers can be fired.
6. Cannot use WHERE conditions.

 

ACID (Atomicity Consistency Isolation Durability) Model of database

ACID(Atomicity Consistency Isolation Durability) is a set of properties that guarantee that database transactions are processed reliably.

In the context of databases, a single logical operation on the data is called a transaction.

 

Atomicity
Atomicity is an all-or-none proposition.
Modifications on the data in the database either fail or succeed. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure.

 

Consistency
Consistency guarantees that a transaction never leaves your database in a half-finished state.
Only valid data (valid according to integrity constraints) may be committed. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.

 

Isolation
Isolation keeps transactions separated from each other until they’re finished.
One transaction does not interfere with another. The ‘executor’ of a transaction has the feeling that he has the entire database for himself.

 

Durability
A committed transaction will not be lost.
Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors,  i.e. the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

 

Example of printer to understand the ACID property more clearly.
Atomicity
Either the entire document gets printed or nothing at all.

Consistency
At end-of-transaction, the paper feed is positioned at top-of-page.

Isolation
No two documents get mixed up while printing.

Durability
The printer can guarantee that it was not “printing” with empty cartridges

 

ACID properties provide the basic building blocks of any database transaction model. So spend few minutes to review these characteristics and commit these in your memory 🙂

 

How to change or reset XAMPP MySQL root password?

If you want to reset or change xampp mysql password, or have forgot the password for accessing phpMyAdmin then just follow the below step to reset the password or change the password.

 

You can do this by two methods.

 

Method 1

The easiest way is to use the security console, which you can access at http://localhost/security/
This “console” creates a password for the MySQL user “root” and is adjusting the phpMyAdmin configuration.

http://localhost/security/xamppsecurity.php

 

Method 2

With the “XAMPP Shell” (command prompt) you can also reset the password. Open the shell and execute this command
mysqladmin.exe -u root password newpassword

 

Of course, your password should not be “newpassword”, too. In the next step you must adjust the phpMyAdmin configuration for this new password. In the file “D:\xampp\phpMyAdmin\config.inc.php” change the lines:

$cfg['Servers'][$i]['user']     = 'root';
$cfg['Servers'][$i]['password'] = '';

To:

$cfg['Servers'][$i]['user']     = 'root';
$cfg['Servers'][$i]['password'] = 'newpassword';

 

Instead in the XAMPP Shell, you can also change the password with phpMyAdmin, and then adjust the phpMyAdmin configuration.

I hope, it will help.

How to use multiple MYSQL databases in a project

If your databases are located on same MySQL server.You don’t need two connections, you can access them both as unique user.

You also don’t need to select a DB. Just use the database-name as prefix when specifying the tables. That’s it.

<?php

mysql_connect("localhost","username","password") or die(mysql_error());

$query = "SELECT * FROM database1.tablename";

$result = mysql_query($query)or die(mysql_error());

var_dump($result);

$query2 = "SELECT * FROM database2.tablename";

$result2 = mysql_query($query2)or die(mysql_error());

var_dump($result2);

?>

Or if you want to select database, you can do

Please note there can only be one active DB at a time, it will work in this way:

<?php

$conn1 = mysql_connect("localhost","username","password") or die(mysql_error());

$conn2 = mysql_connect("localhost","username","password",true) or die(mysql_error());

mysql_select_db("database1",$conn1);

// tablename is a table of database1

$query = "SELECT * FROM tablename";

$result = mysql_query($query,$conn1);

var_dump($result);

mysql_select_db("database2",$conn2);

// tablename is a table of database2

$query2 = "SELECT * FROM tablename";

$result2 = mysql_query($query2,$conn2);

var_dump($result2);

?>

Although there’s no need for 2 connections; you can select both DB’s using the same connection.

You can make multiple calls to mysql_connect(), but if the parameters are the same you need to pass true for the ‘$new_link’ (fourth) parameter, otherwise the same connection is reused.

So then you have to do is

<?php

$db_conn1 = mysql_connect($hostname, $username, $password);

$db_conn2 = mysql_connect($hostname, $username, $password, true);

mysql_select_db('database1', $db_conn1);

mysql_select_db('database2', $db_conn2);

//Then to query database 1, do

mysql_query('select * from tablename', $db_conn1);

//and for database 2

mysql_query('select * from tablename', $db_conn2);

?>

It is used if MySQL server of database is different.