Latest MySQL Interview Questions and Answers
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.
What are the column comparisons operators?
The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.
How to display top 50 rows?
In MySql, top 50 rows are displayed by using this following query:
SELECT * FROM
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 can we get the number of rows affected by query?
Number of rows can be obtained by
SELECT COUNT (user_id) FROM users;
How many columns can be used for creating Index?
Maximum of 16 indexed columns can be created for any standard table.
What are the advantages of MySQL when compared with Oracle?
- MySQL is open source software which is available at any time and has no cost involved.
- MySQL is portable
- GUI with command prompt.
- Administration is supported using MySQL Query Browser
How do you login to MySql using Unix shell?
We can login through this command:
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
Is Mysql query is case sensitive?
SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;
All these examples are same. It is not case sensitive.
What is the different between NOW() and CURRENT_DATE()?
NOW () command is used to show current year,month,date with hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.
Differentiate between FLOAT and DOUBLE?
Following are differences for FLOAT and DOUBLE:
• Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.
• Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.
What does myisamchk do?
It compress the MyISAM tables, which reduces their disk or memory usage.
What is the difference between the LIKE and REGEXP operators?
LIKE and REGEXP operators are used to express with ^ and %.
SELECT * FROM employee WHERE emp_name REGEXP “^b”;
SELECT * FROM employee WHERE emp_name LIKE “%b”;
What are the objects can be created using CREATE statement?
Following objects are created using CREATE statement:
Differentiate CHAR_LENGTH and LENGTH?
CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.
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 is the difference between BLOB AND TEXT?
A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –
- MEDIUMBLOB and
They all differ only in the maximum length of the values they can hold.
A TEXT is a case-insensitive BLOB. The four TEXT types
- MEDIUMTEXT and
They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.
The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.