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 do you login to MySql using Unix shell?
We can login through this command:
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
What does myisamchk do?
It compress the MyISAM tables, which reduces their disk or memory usage.
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.
How can we get the number of rows affected by query?
Number of rows can be obtained by
SELECT COUNT (user_id) FROM users;
1
SELECT COUNT (user_id) FROM users;
Is Mysql query is case sensitive?
No.
SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;
1
2
3
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 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";
1
2
SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";
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 –
TINYBLOB
BLOB
MEDIUMBLOB and
LONGBLOB
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
TINYTEXT
TEXT
MEDIUMTEXT and
LONGTEXT
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.
How MySQL Optimizes DISTINCT?
DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
1
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
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 BLOB?
- BLOB stands for binary large object.
- It that can hold a variable amount of data.
There are four types of BLOB based on the maximum length of values they can hold:
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
What is TEXT?
TEXT is case-insensitive BLOB. The four types of TEXT are:
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
What is the difference between BLOB and TEXT?
- In BLOB sorting and comparison is performed in case-sensitive for BLOB values.
- In TEXT types sorting and comparison is performed case-insensitive.
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 do you login to MySql using Unix shell?
We can login through this command:
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
What does myisamchk do?
It compress the MyISAM tables, which reduces their disk or memory usage.
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.
Number of rows can be obtained by
SELECT COUNT (user_id) FROM users;
1
SELECT COUNT (user_id) FROM users;
 |
| Mindtree MySQL Most Frequently Asked Latest Interview Questions Answers |
Is Mysql query is case sensitive?
No.
SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;
1
2
3
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 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";
1
2
SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";
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 –
TINYBLOB
BLOB
MEDIUMBLOB and
LONGBLOB
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
TINYTEXT
TEXT
MEDIUMTEXT and
LONGTEXT
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.
How MySQL Optimizes DISTINCT?
DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
1
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
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 does myisamchk do?
- It compresses the MyISAM tables, which reduces their disk or memory usage
How can we convert between Unix & MySQL timestamps?
- MySQL timestamp can be converted into Unix timestamp using the command UNIX_TIMESTAMP.
- Unix timestamp can be converted into MySQL timestamp using the command FROM_UNIXTIME.
- It compresses the MyISAM tables, which reduces their disk or memory usage
How can we convert between Unix & MySQL timestamps?
- MySQL timestamp can be converted into Unix timestamp using the command UNIX_TIMESTAMP.
- Unix timestamp can be converted into MySQL timestamp using the command FROM_UNIXTIME.
What is BLOB?
- BLOB stands for binary large object.
- It that can hold a variable amount of data.
There are four types of BLOB based on the maximum length of values they can hold:
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
What is TEXT?
TEXT is case-insensitive BLOB. The four types of TEXT are:
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
What is the difference between BLOB and TEXT?
- In BLOB sorting and comparison is performed in case-sensitive for BLOB values.
- In TEXT types sorting and comparison is performed case-insensitive.
https://mytecbooks.blogspot.com/2019/04/mindtree-mysql-interview-questions.html