Possible MySQL interview questions

1. 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.

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

Use this for mysql >SELECT COUNT(*) FROM table_name; but if You Are particular about no of rows with some special result. do this: >SELECT [colms],COUNT(*) FROM table_name [where u put conditions];

3. 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()

4. 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

5. 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.

6. 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 eg.:- $sql = 'LOAD DATA LOCAL INFILE ''I:/wamp_install/tmp\\\\php19.tmp'' INTO TABLE `webprogrammer` FIELDS TERMINATED BY '';'' ENCLOSED BY ''"'' ESCAPED BY ''\\\\'' LINES TERMINATED BY ''\\r\\n''';

7. 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.

8. What is MySQL?

MySql is a Relational database management system, provided from open source community. Currently fast growing and hight used RDBMS. MySql Developed by MySql AB.

9. difference between mysql_num_rows() and mysql_num_fields()?

mysql_num_rows():- returns the number of rows in the result set. mysql_num_fields():- returns the number of fields in the result set

10. Explain SQL HAVING: The sQL HAVING clause is used to do exactly this, to specify a condition for an aggregate function. or SQL GROUP BY: The sQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s). or SQL ORDER BY: The sQL ORDER BY clause comes in handy when you want to sort your SQL result sets by some column(s). or The order by statement allows for table column assortment. It allows for ascending or descending lists of your table column values permitting SQL to reorder your table rows for the purpose of viewing.

Read more »

Most Important MySQL Interview Questions

1. What’s the difference between primary key and unique key?

Index: An index is a structure in a table that orders the data. It allows the database to access data quickly (In MySQL its implemented using B-tree algorithms). Primary Key: This is an index that cannot be NULL, Primary Keys are used in building relationships between tables in a database. (an index is automatically created on the primary key). The difference between primary and ordinary keys is that there can be multiple keys, but only one primary key. Unique Key: Unique and Index are same, the difference is, in Unique, duplicate are not allowed in any circumstances and that is enforced by database server. Primary key(s) qualify to be Unique on basis of their uniqueness. In case, your table has 2 primary keys means that the 2 fields together form one unique key. Each field by itself may have repeating values, but both primary keys combined together must be unique.

2. List of MySQL Commands:

alter table, analyze table, backup table, begin, check table, commit, create database, create function, create index, create table, delete, describe, drop database, dop function, drop index, drop table, explain, flush, grant, insert, join, kill, load data infile, lock tables, optimize table, rename table, repair table, replace, restore table, revoke, rollback, select, set, set transaction, show, truncate, unlock tables, update, use.

3. how many ways we can find the current date using mysql?

date() and now()

4. 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

5. Explain Normalization concept

Normalization helps to reduce redendence. Ist normlization gives primary key, 2 nd foreign key.

6. How can we repair a MySQL table?

The syntex for repairing a mysql table is REPAIR TABLENAME, [TABLENAME, ], [Quick],[Extended] or $sql = "REPAIR TABLE `Table_Name`"; or REPAIR TABLE table_name TO new_tabel_name [,table_name1 TO new_tabel_name1,..] ; 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.

7. What is the maximum length of a table name, database name, and fieldname in MySQL?

maximum lenghth of Names of database, table, columns are: database- 64 table -64 columns-64 alias-255

8. What are the other commands to know the structure of table using MySQL commands except explain command?

describe table_name;

9. How many tables will create when we create table, what are they?

3 files, *.frm, *.MYD, *.MYI

10. What is the purpose of the following files having extensions 1) .frm 2) .myd 3) .myi? What do these files contain?

data’s are stored in name.Myd table Structure are name.$frm Index tables are name.myi

11. What is maximum size of a database in MySQL?

no limit database. database containing 50,000,000 records, Table size: 64TB~16PB, Rows in a table: 2^64, table Indexes: 32, column size: 16MB~4GB, columns in a table: 1,000 , Row size: 4GB

12. 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

Read more »

Frequently asked MySQL interview questions

Frequently asked mysql intrview questions

1. How can we create a database using PHP and mysql?

We can create MySQL database with the use of mysql_create_db(“Database_Name”) or $sql = 'CREATE DATABASE `database_name`'; if (mysql_query($sql, $link))      echo “Database my_db created successfully\n”; else       echo ‘Error creating database: ‘ . mysql_error() . “\n”;

2. 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. ISAM (Index Sequential Access Method) 2. MyISAM    o Static    o Dynamic    o Compress 3. Heap (Fastest tables because it stores in to the RAM. It is ideal if you need a small amount of     fast but temporary local table space) 4. Merge ( allows to combine several other tables into a single logical table) 5. INNO DB (Transaction safe table) 6. BDB ( berkelyeyDB) MyISAM is the default storage engine as of MySQL 3.23.

3. What are the current versions of apache, PHP, and mysql?

For information about latest version of Apache, Php and Mysql please visits the following sites respectively: http://www.apache.org/ http://www.php.net/ http://www.mysql.com/

4. How can we optimize or increase the speed of a mysql select query?

First, one thing that affects all queries: The more complex permission system setup you have, the more overhead you get. If you do not have any GRANT statements done, MySQL will optimise the permission checking somewhat. So if you have a very high volume it may be worth the time to avoid grants. Otherwise, more permission check results in a larger overhead.

5. How can we change the name and data type of a column of a table ?

$sql = 'ALTER TABLE `webprogrammer` CHANGE `duration` `durations` INT(30) NOT NULL';

6. What are the differences between drop a table and truncate a table

drop a table command, drops a full table ( deletes structure and table data )truncate table deletes data only, and keeps the structure alive. Drop: "DROP TABLE table_name" Truncate: "TRUNCATE TABLE table_Name"

7. A select query over a large table runs very slow because of the growing number of entries in that table. What different measures could be taken to improve speed? or How can we increase the speed of a mysql select query? or How can increase the performance of mysql select query?

should on indexed field,generally using primarykey. or using "Index" fileds.

8. What is meant by MIME

Multipurpose Internet Mail Extensions or 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

Read more »

What is differenc between mysql_connect and mysql_pconnect?

mysql_pconnect establishes a persistent connection. If you don't need one (such as a website that is mostly HTML files or PHP files that don't call the db) then you don't need to use it. mysql_connect establishes a connection for the duration of the script that access the db. Once the script has finished executing it closes the connection. The only time you need to close the connection manually is if you jump out of the script for any reason.

If you do use mysql_pconnect. You only need to call it once for the session. That's the beauty of it. It will hold open a connection to the db that you can use over and over again simply by calling the resource ID whenever you need to interact with the db.

Read more »

What are the differences between drop and truncate commands in MySQL?

Suitable Answer : DROP TABLE table_name Will DELETE the table and DATA TRUNCATE TABLE table_name Will DELETE the table DATA not the table definition ....!

More About This:

Both the above statements remove all records from the table, but the essential difference is as follows.

delete from friends – will delete all records from the friends table. That’s it. I.e. the auto_increment counter does not get reset.

truncate table friends – will delete all records from the table and also rebuild the table, thus resetting the auto_increment counter.

Read more »