MySql Interview Questions and Answers – Part 2

This is second part of MySQL interview questions and answers tutorial.There are some common MySQL interview question which asked by interviewer,You can get here latest mysql interview questions with answers.You can also read first part of MySQL interview questions and answer.

What are the other commands to know the structure of a table using MySQL commands except EXPLAIN command?

DESCRIBE table_name;

How can we find the number of rows in a table using MySQL?
Use this for MySQL.

SELECT COUNT(*) FROM table_name;

What’s the difference between md5(), crc32() and sha1() crypto on PHP?

The major difference is the length of the hash generated. CRC32 is, evidently, 32 bits, while sha1() returns a 128 bit value, and md5() returns a 160 bit value. This is important when avoiding collisions.

How can we find the number of rows in a result set using PHP?

In mysql to get number of rows in a result set is very simple just get the result set and fire the
[code type=mysql]
mysql_num_rows($resultset) and get the number affected rows.
$result = mysql_query($any_valid_sql, $database_link);
$num_rows = mysql_num_rows($result);
echo “$num_rows rows found”;

How many ways we can we find the current date using MySQL?

[code type=mysql]
SELECT CURDATE();
SELECT CURRENT_DATE();
SELECT CURTIME();
SELECT CURRENT_TIME();

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 database by usingh *.* or some specific database by database.* or a specific table by database.table_name.

How can we know the number of days between two given dates using MySQL?

[code type=mysql]
Use DATEDIFF()
SELECT DATEDIFF(NOW(),’2006-07-01′);

How can we change the name of a column of a table?

This will change the name of column:
ALTER TABLE table_name CHANGE old_colm_name new_colm_name

How can we change the data type of a column of a table?

This will change the data type of a column:
ALTER TABLE table_name CHANGE colm_name same_colm_name [new data type]

What is the difference between GROUP BY and ORDER BY in SQL?

GROUP BY clause:
The most general way to satisfy a GROUP BY clause is to scan the whole table and create a new temporary table where all rows from each group are consecutive, and then use this temporary table to discover groups and apply aggregate functions (if any).
GROUP BY [col1],[col2],…[coln]; Tells DBMS to group (aggregate) results with same value of column col1. You can use COUNT(col1), SUM(col1), AVG(col1) with it, if you want to count all items in group, sum all values or view average.

ORDER BY clause:
To sort a result, use an ORDER BY clause.
ORDER BY [col1],[col2],…[coln]; Tells DBMS according to what columns it should sort the result. If two rows will have the same value in col1 it will try to sort them according to col2 and so on.

What are the MySQL database files stored in system ?

Data is stored in name.myd
Table structure is stored in name.frm
Index is stored in name.myi

What are the differences between mysql_fetch_array(), mysql_fetch_object(), mysql_fetch_row()?

mysql_fetch_array – Fetch a result row as an associative array and a numeric array.
mysql_fetch_object – Returns an object with properties that correspond to the fetched row and moves the internal data pointer ahead. Returns an object with properties that correspond to the fetched row, or FALSE if there are no more rows
mysql_fetch_row() – Fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset zero.
How can we encrypt and decrypt a data presented in a table using MySQL?
You can use functions: AES_ENCRYPT() and AES_DECRYPT() like:
[code type=mysql]
AES_ENCRYPT(str, key_str)
AES_DECRYPT(crypt_str, key_str)

How can we take backup from mysql and restore them:

Create a full backup of your database: shell> mysqldump tab=/path/to/some/dir opt db_name
Or: shell> mysqlhotcopy db_name /path/to/some/dir
The full backup file is just a set of SQL statements, so restoring it is very easy:
shell> mysql “.”Executed”;

Another Way:

To backup: BACKUP TABLE tbl_name TO /path/to/backup/directory
To restore: RESTORE TABLE tbl_name FROM /path/to/backup/directory
mysqldump: Dumping Table Structure and Data
Utility to dump a database or a collection of database for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump will contain SQL statements to create the table and/or populate the table.

-t, no-create-info
Don’t write table creation information (the CREATE TABLE statement).

-d, no-data
Don’t write any row information for the table. This is very useful if you just want to get a dump of the structure for a table!