Zensar Technologies MySQL Most Frequently Asked Latest Interview Questions Answers
What are the technical features of MySQL?
MySQL database software is a client or server system which includes
Multithreaded SQL server supporting various client programs and libraries
Different backend
Wide range of application programming interfaces and
Administrative tools.
Why MySQL is used?
MySQL database server is reliable, fast and very easy to use. This software can be downloaded as freeware and can be downloaded from the internet.
What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary
basis.
• 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
What is the default port for MySQL Server?
The default port for MySQL server is 3306.
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
Give string types available for column?
The string types are:
SET
BLOB
ENUM
CHAR
TEXT
VARCHAR
How to get current MySQL version?
SELECT VERSION ();
1
SELECT VERSION ();
is used to get the current version of MySQL.
What storage engines are used in MySQL?
Storage engines are called table types and data is stored in files using various techniques.
Technique involves:
Storage mechanism
Locking levels
Indexing
Capabilities and functions.
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.
What are all the 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 Access Control Lists.
An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and it helps in troubleshooting problems like users not being able to connect.
MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.
What are federated tables?
Federated tables which allow access to the tables located on other databases on other servers.
What, if a table has one column defined as TIMESTAMP?
Timestamp field gets the current timestamp whenever the row gets altered.
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.
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.
How can you see all indexes defined for a table?
Indexes are defined for the table by:
SHOW INDEX FROM <tablename>;
What do you mean by % and _ in the LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.
How can we convert between Unix & MySQL timestamps?
UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp
FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.
What are the column comparisons operators?
The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.
What are the drivers in MySQL?
Following are the drivers available in MySQL:
PHP Driver
JDBC Driver
ODBC Driver
C WRAPPER
PYTHON Driver
PERL Driver
RUBY Driver
CAP11PHP Driver
Ado.net5.mxj.
How Do I Find Out All Databases Starting With 'tech' To Which I Have Access To?
SHOW DATABASES LIKE ‘tech%’;
How Do You Concatenate Strings In Mysql?
CONCAT (string1, string2, string3)
How Do You Get A Portion Of A String?
SELECT SUBSTR(title, 1, 10) from techpreparation_Questions;
What's The Difference Between Char_length And Length?
The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.
How Do You Convert A String To Utf-8?
SELECT (techpreparation_Question USING utf8);
What Do % And _ Mean Inside Like Statement?
% corresponds to 0 or more characters, _ is exactly one character.
What Does + Mean In Regexp?
At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.
How Do You Get The Month From A Timestamp?
SELECT MONTH(techpreparation_timestamp) from techpreparation_Questions;
What are the technical features of MySQL?
MySQL database software is a client or server system which includes
Multithreaded SQL server supporting various client programs and libraries
Different backend
Wide range of application programming interfaces and
Administrative tools.
Why MySQL is used?
MySQL database server is reliable, fast and very easy to use. This software can be downloaded as freeware and can be downloaded from the internet.
What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary
basis.
• 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
What is the default port for MySQL Server?
The default port for MySQL server is 3306.
 |
| Zensar Technologies MySQL Most Frequently Asked Latest Interview Questions Answers |
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
The string types are:
SET
BLOB
ENUM
CHAR
TEXT
VARCHAR
How to get current MySQL version?
SELECT VERSION ();
1
SELECT VERSION ();
is used to get the current version of MySQL.
What storage engines are used in MySQL?
Storage engines are called table types and data is stored in files using various techniques.
Technique involves:
Storage mechanism
Locking levels
Indexing
Capabilities and functions.
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.
What are all the 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 Access Control Lists.
An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and it helps in troubleshooting problems like users not being able to connect.
MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.
Federated tables which allow access to the tables located on other databases on other servers.
What, if a table has one column defined as TIMESTAMP?
Timestamp field gets the current timestamp whenever the row gets altered.
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.
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.
How can you see all indexes defined for a table?
Indexes are defined for the table by:
SHOW INDEX FROM <tablename>;
What do you mean by % and _ in the LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.
How can we convert between Unix & MySQL timestamps?
UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp
FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.
What are the column comparisons operators?
The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.
What are the drivers in MySQL?
Following are the drivers available in MySQL:
PHP Driver
JDBC Driver
ODBC Driver
C WRAPPER
PYTHON Driver
PERL Driver
RUBY Driver
CAP11PHP Driver
Ado.net5.mxj.
How Do I Find Out All Databases Starting With 'tech' To Which I Have Access To?
SHOW DATABASES LIKE ‘tech%’;
How Do You Concatenate Strings In Mysql?
CONCAT (string1, string2, string3)
How Do You Get A Portion Of A String?
SELECT SUBSTR(title, 1, 10) from techpreparation_Questions;
What's The Difference Between Char_length And Length?
The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.
How Do You Convert A String To Utf-8?
SELECT (techpreparation_Question USING utf8);
What Do % And _ Mean Inside Like Statement?
% corresponds to 0 or more characters, _ is exactly one character.
What Does + Mean In Regexp?
At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.
How Do You Get The Month From A Timestamp?
SELECT MONTH(techpreparation_timestamp) from techpreparation_Questions;
https://mytecbooks.blogspot.com/2018/10/zensar-technologies-mysql-most.html