Mysql Questions

Q1.What is a join, what types of join are supported in MySQL.
Ans: A ‘join’ joins two table in such a way that all or partial records are selected from both the table based on join criteria.

Joins supported in mysql are :

[INNER | CROSS] JOIN

STRAIGHT_JOIN

LEFT [OUTER] JOIN

NATURAL [LEFT [OUTER]] JOIN

RIGHT [OUTER] JOIN

NATURAL [RIGHT [OUTER]] JOIN



Q2.In MySQL, what table type is required for foreign keys to work?
Ans: innoDB


Q3.How does full text search work.
Ans: A table must be a myISAM table
Table must have char varchar and text columns
FULLTEXT index must be created at the time of creation of table


Q4.What is the use of files .frm, .MYD, .MYI
Ans: frm files store the table definition
MYD files store the data
MYI files store the index


Q5.What is maximum size of a database in MySQL?
Ans: 65+GB / table / [ limited by the OS]


Q6.How many columns can exist in a mySql table?
Ans: 4096 colums

Q7.What is the maximum size of a row in a mysql table?
Ans: 65,535 not including blobs (as these are stored separately)


Q8. What would you use if you have a choice Natural [left] join or inner join or left join with using clause?
Ans: The NATURAL [LEFT] JOIN of two tables is defined to be semantically
equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables