MySQL Questions & Answers (Compiled from UPSC, SSC ,PSC ,IBPS previous question papers)

What is save point in MySQL?
A defined point in any transaction is known as savepoint. SAVEPOINT is a statement in MySQL which is used to set a named transaction save point with a name of identifier.

What is the difference between primary key and unique key?
While both are used to enforce uniqueness of the column defined but primary key would create a clustered index whereas unique key would create non-clustered index on the column. Primary key does not allow ‘NULL’ but unique key allows it.

How can you see all indexes defined for a table?
Indexes are defined for the table by: SHOW INDEX FROM ;

How to define testing of network layers in MySQL?
For this it is necessary reviewing the layered architecture and determining hardware and software configuration dependencies in respect of the application put to test.

How can we find out which auto increment was assigned on Last insert?
LAST_INSERT_ID will return the last value assigned by Auto_increment and it is not required to specify the table name.

What is the query to display top 20 rows?
SELECT * FROM table_name LIMIT 0,20;

What are the storage models of OLAP?
The storage models in OLA are MOLAP, ROLAP, and HOLAP.

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.

What is the difference 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.

How to resolve the problem of data disk that is full?
When the data disk is full and overloaded the way out is to create and soft link and move the .frm as well as the .idb files into that link location.

What, if a table has one column defined as TIMESTAMP?
Timestamp field gets the current timestamp whenever the row gets altered.

How to display Nth highest salary from a table in a MySQL query?
Let us take a table named employee.

To find Nth highest salary is:
1. select distinct(salary) from employee order by salary desc limit n-1,1
if you want to find 3rd largest salary:
1. select distinct(salary) from employee order by salary desc limit 2,1

How can you change the root password if the root password is lost?
In such cases when the password is lost the user should start the DB with – skip-grants-table and then change the password. Thereafter with the new password the user should restart the DB in normal mode.

What are federated tables?
Federated tables which allow access to the tables located on other databases on other servers.

What is the difference between Unix timestamps and MySQL timestamps?
Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.

How can one take incremental backup in MySQL?
User can take incremental backup in MySQL using percona xtrabackup.

What is the difference between MyISAM Static and MyISAM Dynamic?
In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption.

How to change a password for an existing user via Mysqladmin?
Mysqladmin -u root -p password "newpassword".

How to use the MySQL slow query log?
Information that is provided on the slow query log could be huge in size. The query could also be listed over thousand times. In order to summarize the slow query log in an informative manner one can use the third party tool “pt-qury-digest”.

How do you control the max size of a HEAP table?
Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size.

What are the security alerts while using MySQL?
Install antivirus and configure the operating system's firewall. Never use the MySQL Server as the UNIX root user. Change root username and password Restrict or disable remote access.

What is the difference between LIKE and REGEXP operators in MySQL?
LIKE is denoted using the % sign. For example:SELECT * FROM user WHERE user name LIKE “%NAME”.• On the other hand the use of REGEXP is as follows:SELECT * FROM user WHERE username REGEXP “^NAME”;

What does myisamchk do
It compress the MyISAM tables, which reduces their disk or memory usage.

How to get the current date in MySQL?
To get current date, use the following syntax: SELECT CURRENT_DATE();

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.

Can you use MySQL with LINUX operating system?
Yes, the syntax for using MySQL with LINUX operating system is /etc/init.d/mysqlstart

How do you login to MySql using Unix shell?
We can login through this command: # [mysql dir]/bin/mysql -h hostname -u -p

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.

What is the difference between primary key and candidate key
Primary key in MySQL is use to identify every row of a table in unique manner. For one table there is only one primary key. One of the candidate keys is the primary key and the candidate keys can be used to reference the foreign keys.

What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type
TIMESTAMP column is updated with Zero when the table is created. UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever there is a change in other fields of the table.

1 2 3 4

Categories

Android C programming C++ programming Cloud Computing Computer Computer System Architecure Data Base Data Structure Internet & Social Media Java programming Manual Testing MS Office MySQL Networking Operating System PHP Programming Software Engineering Software Testing

MySQL Interview Question Answers for Freshers & Experienced Candidates