Pacecode Blog Tips, Scripts and More…

Important MySQL Questions and Answers

How many types of join are supported by mysql? Which are they? Explain:

ans: there are three types of joins supported in mysql as inner, right and left.
The inner join displays all the rows from both tables where match is found.
Left join returns all the rows from first table even if the match is not found in second table.
While the right join returns all the rows from second table even if match is not found in first table.

What are the advantages of mysql comparing with oracle?

MySql has many advantages in comparison to Oracle.

1 - MySql is Open source, which can be available any time

2 - MySql has no cost of development purpose.

3 - MySql has most of features , which oracle provides

4 - MySql day by day updating with new facilities.

5 - Good for small application.

6 - easy to learn and to become master.

7 - MySql has a good power these days.

even though MySql having so many advantages, Oracle is best database ever in Software development.

What are the limitations of mysql in Comparison of Oracle?

• Mysql supports six different types of tables, four of which do not support transactions (MyISAM, MERGE, ISAM, and HEAP)
• MySQL only supports one index type
• Unlike Oracle, MySQL date data types can store invalid dates, do not support time zones, and the precision of time values is limited to one second.

How many drivers in MYSQL?

There are eleven drivers in MYSQL .Six of them from MySQL AB and five by MYSQL Communities. They are1.PHP Driver2.ODBC Driver3.JDBC Driver4.ado.net5.mxj6.CAPI1PHP DRIVER2.PERL DRIVER3.PYTHON DRIVER4.RUBY DRIVER5.C++ WRAPPER

How are triggers created in MySQL?

CREATE TRIGGER `tgr_name` AFTER UPDATE ON `addresses` FOR EACH ROW begin

update products set countryName = new.countryName where products.shippingAddressId = old.id;

end;

Use of triggers in MySQL:

Triggers are programmable events that react to queries and reside directly on the database server. Triggers can be executed before or after INSERT, UPDATE or DELETE statements.

Ref: http://www.digitalpropulsion.org/Programming/Triggers_in_MySQL_5_0

What are the data types available in MySQL?

DATA TYPES in mysql

varchar => A variable section from 0 to 255 characters long.

tinyint => A string with a maximum length of 255 characters.

text => A string with a maximum length of 65535 characters.

date => YYYY-MM-DD.

smallint mediumint int =>      -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.

bigint =>      -9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.

float => A small number with a floating decimal point.

double => A large number with a floating decimal point.

decimal => A DOUBLE stored as a string , allowing for a fixed decimal point.

datetime => YYYY-MM-DD HH:MM:SS. timestamp => YYYYMMDDHHMMSS.

time =>       HH:MM:SS. year

char => CHAR( ) A fixed section from 0 to 255 characters long.

tinyblob tinytext

blob => A string with a maximum length of 65535 characters.BLOB stands for Binary Large OBject.

mediumblob => A string with a maximum length of 16777215 characters.

longblob => A string with a maximum length of 4294967295 characters.

longtext enum => Short for ENUMERATION which means that each column may have one of a specified possible values.

set => Similar to ENUM except each column may have more than one of the specified possible values. bool binary varbinary

How is sub queries handled in MySQL?

Sub queries in MySQL handled nested way, You can see the example and understand yourself -

SELECT name, headofstate, population FROM Country WHERE population=(SELECT MAX(population) FROM Country);