Category Archives: Database

SQL Brushup

http://a4academics.com/interview-questions/53-database-and-sql/397-top-100-database-sql-interview-questions-and-answers-examples-queries?showall=&limitstart= Get employee details from employee table whose employee name are “John” and “Roy” Select * from EMPLOYEE where FIRST_NAME in (‘John’,’Roy’) Get employee details from employee table whose employee name are not “John” and “Roy” Select * from EMPLOYEE … Continue reading

Posted in Database | Leave a comment

Stored Procedure vs Function in MySQL

* Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes whenever it is called. But Function is compiled and executed every time when it is called. Stored * Procedures are … Continue reading

Posted in Database | Leave a comment

Questions on MYSQL Queries

1) How to display nth highest record in a table for example? How to display 4th highest (salary) record from customer table? SELECT DISTINCT(`salary`) FROM `customer` ORDER BY `salary` DESC LIMIT 3,1;

Posted in Database | Leave a comment

MySQL Query Set

1) http://www.crazyforcode.com/mysql-query-set-5/ We have 3 tables Movie, Reviewer, Rating as shown below: Movie ( mID, title, year, director ) There is a movie with ID number mID, a title, a release year, and a director. Reviewer ( rID, name ) … Continue reading

Posted in Database | Leave a comment

Index in MySQL

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes. http://www.kylescousin.com/2010/09/a-simple-explanation-on-how-b-tree-database-indexes-work/

Posted in Database | Leave a comment

How does MySQL uses indexes ?

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on … Continue reading

Posted in Database | Leave a comment

MySQL 10 Interview Questions : Set-1

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 … Continue reading

Posted in Database | Leave a comment

Joins

INNER JOIN: Returns all rows when there is at least one match in BOTH tables LEFT JOIN: Return all rows from the left table, and the matched rows from the right table (Tip: Read it as : All rows from … Continue reading

Posted in Database | Leave a comment

Features of NoSql Databases

1) Elastic Scaling: Because of the distributed nature of non-relational databases, to scale NoSQL all you need to do is add machines to the cluster to meet demand. The new breed of NoSQL databases are designed to expand transparently to … Continue reading

Posted in Database | Leave a comment

Why is ENUM considered evil ?

Disadvantages of Enum datatype in MySQL: http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

Posted in Database | Leave a comment