1) What is the difference between primary key and unique key ?
both primary and unique key uniquely identifies each row in table but there are some subtle difference between them. here are some of them :
1) Unique key in a table can be null, at-least one but primary key can not be null in any table in relation database like MySQL , Oracle etc.
2) Primary key can be combination of more than one unique keys in same table.
3) There can be only one primary key per table in relation database e.g. MySQL, Oracle or Sybase but there can be more than one unique key per table.
4) Unique key is represented using unique constraint while primary key is created using primary key constraint in any table and it’s automatically gets unique constraint.
5) Many database engine automatically puts clustered index on primary key and since you can only have one clustered index per table, its not available to any other unique key at same time.
2) What is a clustered index?
With a clustered index the rows are stored physically on the disk in the same order as the index. There can therefore be only one clustered index.
With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.
A clustered index means you are telling the database to store close values actually close to one another on the disk. This has the benefit of rapid scan / retrieval of records falling into some range of clustered index values.
Clustered Index
Only one per table.
Faster to read than non clustered as data is physically stored in index order.
Nonclustered Index
Can be used many times per table.
Quicker for insert and update operations than a clustered index.
3) How many triggers are possible in MySQL?
Answer : There are only six triggers are allowed to use in MySQL database and they are.
Before Insert
After Insert
Before Update
After Update
Before Delete
After Delete
4) You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user?
SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase “Found 13,450,600 results, displaying 1-10”. Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.
5) How do you find out which auto increment was assigned on the last insert?
SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function.