Mysql Questions and Answers - part1

What is the difference between mysql_fetch_object and mysql_fetch_array?

MySQL fetch object will collect first single matching record where mysql_fetch_array will collect all matching records from the table in an array

What are the different tables present in MySQL? Which type of table is generated when we are creating a table in the following syntax: create table employee(eno int(2),ename varchar(10))?

Total 5 types of tables we can create
1. MyISAM
2. Heap
3. Merge
4. INNO DB
5. ISAM
MyISAM is the default storage engine as of MySQL 3.23. When you fire the above create query MySQL will create a MyISAM table.

What is the purpose of the following files having extensions: frm, myd, and myi? What these files contain?

In MySQL, the default table type is MyISAM.
Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type.

The '.frm' file stores the table definition.
The data file has a '.MYD' (MYData) extension.
The index file has a '.MYI' (MYIndex) extension,

Write a select query that will be displayed the duplicated site name and how many times it is duplicated?

SELECT sitename, COUNT(*) AS NumOccurrences
FROM tbl_sites
GROUP BY sitename HAVING COUNT(*) > 1


What are the differences between DROP a table and TRUNCATE a table?

DROP TABLE table_name - This will delete the table and its data.

TRUNCATE TABLE table_name - This will delete the data of the table, but not the table definition.

Give the syntax of GRANT commands?

The generic syntax for GRANT is as following

GRANT [rights] on [database] TO [username@hostname] IDENTIFIED BY [password]

Now rights can be:
a) ALL privilages
b) Combination of CREATE, DROP, SELECT, INSERT, UPDATE and DELETE etc.

We can grant rights on all databse by usingh *.* or some specific database by database.* or a specific table by database.table_name.

What is the difference between CHAR and VARCHAR data types?

CHAR is a fixed length data type. CHAR(n) will take n characters of storage even if you enter less than n characters to that column. For example, "Hello!" will be stored as "Hello! " in CHAR(10) column.

VARCHAR is a variable length data type. VARCHAR(n) will take only the required storage for the actual number of characters entered to that column. For example, "Hello!" will be stored as "Hello!" in VARCHAR(10) column.