81.What are the column comparisons operators?

The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.

82.How can we get the number of rows affected by query?

Number of rows can be obtained by

• SELECT COUNT (user_id) FROM users;

83.Is Mysql query is case sensitive?

• SELECT VERSION(), CURRENT_DATE;
• SeLect version(), current_date;
• seleCt vErSiOn(), current_DATE;

84.What is the difference between the LIKE and REGEXP operators?

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";

85.What is the difference between BLOB AND TEXT?

A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –

• TINYBLOB
• BLOB
• MEDIUMBLOB and
• LONGBLOB
They all differ only in the maximum length of the values they can hold.
A TEXT is a case-insensitive BLOB. The four TEXT types

• TINYTEXT
• TEXT
• MEDIUMTEXT and
• LONGTEXT

86.What is the difference between mysql_fetch_array and mysql_fetch_object?

Following are the differences between mysql_fetch_array and mysql_fetch_object:
mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.
mysql_fetch_object – Returns a result row as object from database.

87.How can we run batch mode in mysql?

Following commands are used to run in batch mode:Each MyISAM table is stored on disk in three formats:

• mysql ;
• mysql mysql.out

88.Where MyISAM table will be stored and also give their formats of storage?

Each MyISAM table is stored on disk in three formats:

• The ‘.frm’ file stores the table definition
• The data file has a ‘.MYD’ (MYData) extension
• The index file has a ‘.MYI’ (MYIndex) extension

89.What are the different tables present in MyMYSQL?

Total 5 types of tables are present:

• MyISAM
• Heap
• Merge
• INNO DB
• ISAM

90.What is ISAM?

ISAM is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

91.What is InnoDB?

lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.

92.How MyMYSQL Optimizes DISTINCT?

DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.

• SELECT DISTINCT t1.aFROM t1,t2 where t1.a=t2.a;

93.How to enter Characters as HEX Numbers?

If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).
A HEX number string will be automatically converted into a character string, if the expression context is a string.

94.How to display top 50 rows?

In MySql, top 50 rows are displayed by using this following query:

• SELECT * FROM
• LIMIT 0,50;

95.How many columns can be used for creating Index?

Maximum of 16 indexed columns can be created for any standard table.

96.What is the different between NOW() and CURRENT_DATE()?

NOW () command is used to show current year,month,date with hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.

97.What are the objects can be created using CREATE statement?

Following objects are created using CREATE statement:

• DATABASE
• EVENT
• FUNCTION
• INDEX
• PROCEDURE
• TABLE
• TRIGGER
• USER
• VIEW

98.How many TRIGGERS are allowed in MySql table?

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

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE and
• AFTER DELETE

99.What are the nonstandard string types?

Following are Non-Standard string types:

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

100.What are all the Common MYSQL 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.