Basic MySQL Interview Question Set

1. How can we repair a MySQL table?
The syntex for repairing a mysql table is
REPAIR TABLENAME, [TABLENAME, ], [Quick],[Extended]
This command will repair the table specified
if the quick is given the mysql will do a repair of only the index tree if the extended is given it will create index row by row

2. Maximum lenghth of mnames of database, table, columns
database- 64
table -64
columns-64
alias-255

3. How many values can the SET function of MySQL take?
Mysql set can take zero or more values but at the maximum it can take 64 values

4. What are the other commands to know the structure of table using MySQL commands except explain command?
describe table_name;

5. How can we find the number of rows in a table using MySQL?

Use this for mysql
>SELECT COUNT(*) FROM table_name;
but if u r particular about no of rows with some special result
do this
>SELECT [colms],COUNT(*) FROM table_name [where u put conditions];
and for PHP it cant be more simple
$result = mysql_query($any_valid_sql, $database_link);
$num_rows = mysql_num_rows($result);
echo ¡°$num_rows rows found¡±;

6. How many ways we can we find the current date using MySQL?
SELECT CURDATE();
CURRENT_DATE() = CURDATE()
for time use
SELECT CURTIME();
CURRENT_TIME() = CURTIME()

7. Give the syntax of Grant and Revoke commands?
The generic syntax for grant is as following
> GRANT [rights] on [database/s] 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
username@hotsname can be either username@localhost, username@hostname and username@%
where hostname is any valid hostname and % represents any name, the *.* any condition
password is simply the password of user
The generic syntax for revoke is as following
> REVOKE [rights] on [database/s] FROM [username@hostname]
now rights can be as explained above
a) All privilages
b) combination of create, drop, select, insert, update and delete etc.
username@hotsname can be either username@localhost, username@hostname and username@%
where hostname is any valid hostname and % represents any name, the *.* any condition

8. The structure of table view buyers is as follows:
+¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª-+¡ª¡ª+¡ª¨C+¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª-+
| Field | Type | Null | Key | Default | Extra |
+¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª-+¡ª¡ª+¡ª¨C+¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª-+
| user_pri_id | int(15) | | PRI | NULL | auto_increment |
| userid | varchar(10) | YES | | NULL | |
+¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª-+¡ª¡ª+¡ª¨C+¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª-+
the value of user_pri_id the last row 2345 then What will happen in the following conditions?
Condition1: Delete all the rows and insert another row then. What is the starting value for this auto incremented field user_pri_id ,
Condition2: Delete the last row(having the field value 2345) and insert another row then. What is the value for this auto incremented field user_pri_id
In botth cases let the value for auto increment field be n then next row will have value n+1 i.e. 2346

9. What is the difference between char and varchar data types?
Set char to occupy n bytes and it will take n bytes even if u r storing avalue of n-m butes
Set varchar to occupy n bytes and it will take only the required space and will not use the n bytes
eg. name char(10) will waste 5 bytes if we store ¡®kumar¡¯, if each char takes a byte
eg. name varchar(10) will just use 5 bytes if we store ¡®kumar¡¯, if each char takes a byte. rest 5 bytes will be free.

10. What is the functionality of md5 function in PHP?
string md5(string)
Calculate the md5 hash of a string. The hash is a 32-character hexadecimal number. I use it to generate keys which I use to identify users etc. If I add random no techniques to it the md5 generated now will be totally different for the same string I am using.

11. How can I load data from a text file into a table?
The mysql provides a LOAD DATA INFILE syntax. U can load data from a file. Gr8 tool but u need to make sure that
a) data is delimited
b) u match the colms and data correctly
dont use w/out first learning the syntax

12. How can we know the number of days between two given dates using MySQL?
Use DATEDIFF()
>SELECT DATEDIFF(NOW(),¡¯1947-08-15¡ä);
will give u the exact no of days India got independence from British.

13. How can we know the number of days between two given dates using PHP?
Simple arithmetic.
$date1 = date(¡±Y-m-d¡±);
$date2 = ¡°1947-08-15¡å;
$days = (strtotime() - strtotime()) / (60 * 60 * 24);
echo ¡± No of $days we got independence from Britts¡±;

14. How can we change the name of a column of a table?
How can we change the name and data type of a column of a table?
this will change the name of colm
> ALTER TABLE table_name CHANGE old_colm_name new_colm_name
this will change the name of colm and also the datatype
> ALTER TABLE table_name CHANGE old_colm_name new_colm_name [data type]

15. What are the differences between drop a table and truncate a table?
>DROP TABLE table_name
Will DELETE the table and DATA
>TRUNCATE TABLE table_name
Will DELETE the table DATA not the table definition

16. The structure of table view buyers is as follows:
+¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª-+¡ª¡ª+¡ª¨C+¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª-+
| Field | Type | Null | Key | Default | Extra |
+¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª-+¡ª¡ª+¡ª¨C+¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª-+
| user_pri_id | int(15) | | PRI | NULL | auto_increment |
| userid | varchar(10) | YES | | NULL | |
+¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª-+¡ª¡ª+¡ª¨C+¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª-+
the value of user_pri_id the last row 2345 then What will happen in the following conditions?
Condition1: Delete all the rows and insert another row then. What is the starting value for this auto incremented field user_pri_id ,
Condition2: Delete the last row(having the field value 2345) and insert another row then. What is the value for this auto incremented field user_pri_id
Mr. Kumar has given its answer but if you have used mysql as backend and if you fire a query ¡°delete from tablename¡± then in mysql it will reset auto index. so for condition one it will start from value 1.
for condition 2 it will start from n+1

17. What is PEAR in PHP
PEAR is short for ¡°PHP Extension and Application Repository¡± and is pronounced just like the fruit. The purpose of PEAR is to provide:
A structured library of open-sourced code for PHP users
A system for code distribution and package maintenance
A standard style for code written in PHP
The PHP Foundation Classes (PFC),
The PHP Extension Community Library (PECL),
A web site, mailing lists and download mirrors to support the PHP/PEAR community
PEAR is a community-driven project with the PEAR Group as the governing body. The project has been founded by Stig S. Bakken in 1999 and quite a lot of people have joined the project since then.
http://pear.php.net/manual/en/introduction.php

18. A .66
select count(id) id ,sitename from tbl_sites group by sitename having id>1;
Tech Interviews comment by Nasim Abbas

19. What is the difference between GROUP BY and ORDER BY in Sql?
ORDER BY [col1],[col2],¡­,[coln]; Tels DBMS according to what columns it should sort the result. If two rows will hawe the same value in col1 it will try to sort them according to col2 and so on.
GROUP BY [col1],[col2],¡­,[coln]; Tels DBMS to group 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.

21. What is MIME?
MIME is Multipurpose Internet Mail Extensions is an internet standard for the format of e-mail. Howewer browsers also uses MIME standart to transmit files. MIME has a header wich is added to a begining of the data. When browser sees such header it shows the data as it would be a file (for example image)
some mimes:
audio/x-ms-wmp
image/png
aplication/x-shockwave-flash

22. .How can we know that a session is started or not?
a session starts by session_start()function.
this session_start() is always declared in header portion.it always declares first.then we write session_register().

24. 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,numeric array.
MySQL_fetch_object()->Fetch a result row as an object.
MySQL_fetch_row()->Fetch a result set as an array().

25. If we login more than one browser window at the same time with same user and after that we close one window then is the session is exist to other window or not.And if yes then why? or if no then why?

session depends on browser. if browser is closed then session is lost. the session data will be deleted after session time out. if connection is lost and u recreate connection, then also sesssion will continue in the browser.

27. What is the maximum storage capacity of Mysql data,
Is Mysql not reliable, Why?

28. What are the database files stored in system?
Ans: data¡¯s are stored in name.Myd
table Structure are name.$frm
Index tables are name.myi

29. what is the difference between groupby and order by in mysql?
Ans: Group by which is used to sort a table interms of colums
order by which is used to index a table by colum value