MYSQL Developer Interview Questions and Answers
Q1. What is MySQL?
Ans: MySQL is an open source DBMS that's constructed, supported and distributed by way of MySQL AB (now obtained by means of Oracle)
Q2. What are the technical features of MySQL?
Ans: MySQL database software is a client or server device which includes
Multithreaded SQL server supporting various customer programs and libraries
Wide range of application programming interfaces and
Q3. Why MySQL is used?
Ans: MySQL database server is dependable, speedy and very smooth to use. This software can be downloaded as freeware and can be downloaded from the net.
Q4. What are Heap tables?
Ans: HEAP tables are present in memory and they're used for high velocity storage on brief
BLOB or TEXT fields are not allowed
Only assessment operators can be used =, <,>, = >,=<
AUTO_INCREMENT isn't always supported by HEAP tables
Indexes should be NOT NULL
Q5. What is the default port for MySQL Server?
Ans: The default port for MySQL server is 3306.
Q6. What are the blessings of MySQL when in comparison with Oracle?
MySQL is open source software which is available at any time and has no cost involved.
MySQL is transportable
GUI with command prompt.
Administration is supported the use of MySQL Query Browser
Q7. Differentiate between FLOAT and DOUBLE?
Ans: Following are variations for FLOAT and DOUBLE:
Floating factor numbers are saved in FLOAT with eight region accuracy and it has four bytes.
Floating factor numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.
Q8. Differentiate CHAR_LENGTH and LENGTH?
Ans: CHAR_LENGTH is man or woman rely whereas the LENGTH is byte matter. The numbers are equal for Latin characters but they may be distinct for Unicode and different encodings.
Q9. How to represent ENUMs and SETs internally?
Ans: ENUMs and SETs are used to symbolize powers of two because of garage optimizations.
Q10. What is using ENUMs in MySQL?
Ans: ENUM is a string item used to specify set of predefined values and that may be used for the duration of table creation.
Create desk length(call ENUM('Small', 'Medium','Large');
Q11. Define REGEXP?
Ans: REGEXP is a pattern fit wherein suits pattern everywhere within the seek fee.
Q12. Difference between CHAR and VARCHAR?
Ans: Following are the differences between CHAR and VARCHAR:
CHAR and VARCHAR kinds range in storage and retrieval
CHAR column period is constant to the length that is declared even as growing table. The period value ranges from 1 and 255
When CHAR values are saved then they are right padded the usage of areas to unique length. Trailing areas are eliminated when CHAR values are retrieved.
Q13. Give string sorts to be had for column?
Ans: The string kinds are:
Q14. How to get cutting-edge MySQL version?
SELECT VERSION ();
is used to get the modern model of MySQL.
Q15. What storage engines are used in MySQL?
Ans: Storage engines are referred to as table types and statistics is saved in documents using various techniques.
Capabilities and features.
Q16. What are the drivers in MySQL?
Ans: Following are the drivers available in MySQL:
Q17. What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP records type?
Ans: TIMESTAMP column is updated with Zero when the table is created. UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to modern time every time there's a exchange in other fields of the table.
Q18. What is the distinction among number one key and candidate key?
Ans: Every row of a table is identified uniquely by way of number one key. There is best one number one key for a desk.
Primary Key is also a candidate key. By commonplace convention, candidate key can be detailed as primary and which can be used for any overseas key references.
Q19. How do you login to MySql the usage of Unix shell?
Ans: We can login through this command:
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
Q20. What does myisamchk do?
Ans: It compress the MyISAM tables, which reduces their disk or reminiscence utilization.
Q21. How do you manage the max size of a HEAP table?
Ans: Maximum length of Heal desk may be managed by using MySQL config variable known as max_heap_table_size.
Q22. What is the distinction between MyISAM Static and MyISAM Dynamic?
Ans: In MyISAM static all the fields can have constant width. The Dynamic MyISAM table may have fields like TEXT, BLOB, and many others. To deal with the facts sorts with various lengths.
MyISAM Static might be easier to restore in case of corruption.
Q23. What are federated tables?
Ans: Federated tables which permit get right of entry to to the tables positioned on other databases on different servers.
Q24. What, if a table has one column described as TIMESTAMP?
Ans: Timestamp field gets the current timestamp whenever the row gets altered.
Q25. What takes place when the column is ready to AUTO INCREMENT and in case you attain maximum value inside the table?
Ans: It stops incrementing. Any similarly inserts are going to provide an errors, for the reason that key has been used already.
Q26. How can we find out which auto increment become assigned on Last insert?
Ans: LAST_INSERT_ID will return the last cost assigned via Auto_increment and it isn't required to specify the desk call.
Q27. How can you see all indexes defined for a desk?
Ans: Indexes are defined for the desk with the aid of:
SHOW INDEX FROM <tablename>;
Q28. What do you mean by using % and _ in the LIKE assertion?
Ans: % corresponds to 0 or extra characters, _ is exactly one person inside the LIKE declaration.
Q29. How are we able to convert among Unix & MySQL timestamps?
Ans: 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.
Q30. What are the column comparisons operators?
Ans: The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.
Q31. How are we able to get the quantity of rows affected by query?
Ans: Number of rows can be obtained by
SELECT COUNT (user_id) FROM users;
Q32. Is Mysql question is case touchy?
SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;
All these examples are equal. It is not case touchy.
Q33. What is the distinction among the LIKE and REGEXP operators?
Ans: 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";
Q34. What is the difference among BLOB AND TEXT?
Ans: A BLOB is a binary big item which could hold a variable amount of statistics. There are four varieties of BLOB –
They all fluctuate only within the most period of the values they are able to keep.
A TEXT is a case-insensitive BLOB. The 4 TEXT types
They all correspond to the 4 BLOB sorts and feature the identical maximum lengths and storage necessities.
The best difference among BLOB and TEXT kinds is that sorting and assessment is carried out in case-sensitive for BLOB values and case-insensitive for TEXT values.
Q35. What is the distinction among mysql_fetch_array and mysql_fetch_object?
Ans: Following are the differences between mysql_fetch_array and mysql_fetch_object:
mysql_fetch_array() -Returns a end result row as an related array or a everyday array from database.
Mysql_fetch_object – Returns a result row as item from database.
Q36. How can we run batch mode in mysql?
Ans: Following instructions are used to run in batch mode:
Q37. Where MyISAM table will be saved and also deliver their formats of storage?
Ans: Each MyISAM table is saved on disk in 3 formats:
The ‘.Frm’ report shops the table definition
The facts record has a ‘.MYD’ (MYData) extension
The index document has a ‘.MYI’ (MYIndex) extension
Q38. What are the extraordinary tables found in MySQL?
Ans: Total 5 styles of tables are gift:
MyISAM is the default storage engine as of MySQL .
Q39. What is ISAM?
Ans: ISAM is abbreviated as Indexed Sequential Access Method.It changed into evolved via IBM to save and retrieve statistics on secondary garage systems like tapes.
Q40. What is InnoDB?
Ans: lnnoDB is a transaction safe storage engine evolved with the aid of Innobase Oy that's a Oracle Corporation now.
Q41. How MySQL Optimizes DISTINCT?
Ans: DISTINCT is transformed to a GROUP BY on all columns and it will likely be blended with ORDER BY clause.
SELECT DISTINCT t1.A FROM t1,t2 wherein t1.A=t2.A;
Q42. How to go into Characters as HEX Numbers?
Ans: If you need to enter characters as HEX numbers, you can input HEX numbers with single charges and a prefix of (X), or simply prefix HEX numbers with (Ox).
A HEX variety string will be automatically converted into a character string, if the expression context is a string.
Q43. How to show top 50 rows?
Ans: In MySql, top 50 rows are displayed by using this following question:
SELECT * FROM
Q44. How many columns may be used for developing Index?
Ans: Maximum of sixteen listed columns may be created for any widespread table.
Q45. What is the distinct between NOW() and CURRENT_DATE()?
Ans: NOW () command is used to reveal cutting-edge year,month,date with hours,minutes and seconds.
CURRENT_DATE() suggests contemporary 12 months,month and date most effective.
Q46. What are the gadgets may be created the use of CREATE announcement?
Ans: Following objects are created using CREATE announcement:
Q47. How many TRIGGERS are allowed in MySql desk?
Ans: SIX triggers are allowed in MySql table. They are as follows:
BEFORE DELETE and
Q48. What are the nonstandard string kinds?
Ans: Following are Non-Standard string types:
Q49. What are all of the Common SQL Function?
Ans: CONCAT(A, B) – Concatenates string values to create a unmarried string output. Often used to mix two or more fields into one single field.
FORMAT(X, D) – Formats the number X to D good sized digits.
CURRDATE(), CURRTIME() – Returns the cutting-edge date or time.
NOW() – Returns the contemporary date and time as one fee.
MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given statistics from a date value.
HOUR(), MINUTE(), SECOND() – Extracts the given information from a time price.
DATEDIFF(A, B) – Determines the difference between two dates and it's miles typically used to calculate age
SUBTIMES(A, B) – Determines the distinction between times.
FROMDAYS(INT) – Converts an integer quantity of days right into a date fee.
Q50. Explain Access Control Lists.
Ans: An ACL (Access Control List) is a listing of permissions that is associated with an object. This listing is the idea for MySQL server’s security version and it enables in troubleshooting issues like users no longer being capable of connect.
MySQL keeps the ACLs (also called grant tables) cached in reminiscence. When a consumer attempts to authenticate or run a command, MySQL assessments the authentication facts and permissions against the ACLs, in a predetermined order.