Interview Questions.

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

Different backend

Wide range of application programming interfaces and

Administrative gear.

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

foundation.

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? 

Ans:

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.

HubSpot Video
 

Q13. Give string sorts to be had for column?

Ans: The string kinds are:

SET

BLOB

ENUM

CHAR

TEXT

VARCHAR

Q14. How to get cutting-edge MySQL version?

Ans:

     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.

Technique involves:

Storage mechanism

Locking tiers

Indexing

Capabilities and features.

Q16. What are the drivers in MySQL?

Ans: Following are the drivers available in MySQL:

PHP Driver

JDBC Driver

ODBC Driver

C WRAPPER

PYTHON Driver

PERL Driver

RUBY Driver

CAP11PHP Driver

net5.Mxj

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?

Ans: No.

 
 

 

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 –

TINYBLOB

BLOB

MEDIUMBLOB and

LONGBLOB

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

TINYTEXT

TEXT

MEDIUMTEXT and

LONGTEXT

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:

 
 

mysql ;

mysql mysql.Out

 

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

Heap

Merge

INNO DB

ISAM

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

LIMIT 0,50;

 

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:

DATABASE

EVENT

FUNCTION

INDEX

PROCEDURE

TABLE

TRIGGER

USER

VIEW

Q47. How many TRIGGERS are allowed in MySql desk?

Ans: SIX triggers are allowed in MySql table. They are as follows:

BEFORE INSERT

AFTER INSERT

BEFORE UPDATE

AFTER UPDATE

BEFORE DELETE and

AFTER DELETE

Q48. What are the nonstandard string kinds?

Ans: Following are Non-Standard string types:

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

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.