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

Non­clustered 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.

mysqli (MySQL Improved)

The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer

The mysqli extension has a number of benefits, the key enhancements over the mysql extension being:

– Object-oriented interface

– Support for Prepared Statements

– Support for Multiple Statements

– Support for Transactions

– Enhanced debugging capabilities

– Embedded server support

$mysqli = new mysqli("", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli->connect_error;

$res = $mysqli->query("SELECT 'choices to please everybody.' AS _msg FROM DUAL");
if (!$res) {
die('There was an error running the query [' . $db->error . ']');
$row = $res->fetch_assoc();
echo $row['_msg'];

echo 'Total results: ' . $res->num_rows;

$statment = $db->prepare("SELECT `name` FROM `users` WHERE `username` = ? and `age` = ?");
$statement->bind_param('si', $name, $age);
$result = $statement->get_result();
while($row = $result->fetch_array(MYSQLI_ASSOC)){

In bind param, first parameter denotes the types “s for string, i for integer, d for decimal, etc)

PHP Data Objects & Prepared Statements

PDO provides a data-access abstraction layer, which means that, regardless of which database you’re using, you use the same functions to issue queries and fetch data.

try {
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
foreach($dbh->query('SELECT * from FOO') as $row) {
$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "

// Persistent Connections

Prepared Statements :
They can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:
a) The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters.
b) The parameters to prepared statements don’t need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;

// repeated inserts
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// fetching data
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
while ($row = $stmt->fetch()) {

PDO vs mysqli :
While PDO has its advantages, such as a clean, simple, portable API, its main disadvantage is that it doesn’t allow you to use all of the advanced features that are available in the latest versions of MySQL server. For example, PDO does not allow you to use MySQL’s support for Multiple Statements.

PDO works with several databases whereas MySQLi works with MySQL only.

PDO has named parameters whereas MySQLi does not.
$pdo->prepare(‘SELECT * FROM users WHERE username = :username AND email = :email’);
$params = array(‘:username’ => ‘test’, ‘:email’ => $mail);

$mysqli->prepare(‘SELECT * FROM users WHERE username = ? AND email = ?’);
$query->bind_param(‘test’, $mail);–net-24059

System Design

1) “Design and code a system that can accept millions of events in real time and report the number of events for the last 10 minutes (sliding window). The system has to account for performance and concurrency.”

2) Design a URL shortner service :

3) Design a unique id service like youtube

4) Design an Elevator System and give the objects involved and their interactions.

Binary Tree Algorithms

1) Find deepest node of a binary tree
(Karumanchi, pg 120)

2) Find height of a binary tree (recursion method)
(Karumanchi, pg 119 , with small correction)

3) Find height of binary tree (non recursion)
(Karumanchi, pg 120, with small correction)

4) Find Least Common Ancestor of 2 nodes in a binary tree
(Karumanchi, pg 126)

5) Find Least Common Ancestor of 2 nodes in a binary SEARCH tree
(Karumanchi, pg 153)

6) Find Shortest path of 2 nodes in a binary SEARCH tree
(Hint: Find LCA and then calculate path from LCA to each node)

7) Determine if a Binary Tree is a Binary Search Tree
(Karumanchi : pg 155, prob 52)

8) Serialize and Deserialize a Binary Tree

8 b) Serialize and Deserialize a Binary Search Tree

9) Given a string of html tags like “< a >< b >< c >< /c >< d >< /d >< a >< /a >< /b >< /a >“, construct a tree where each node is like
Node { string tag, ArrayOfChildren[] };
Note that the tree need not be binary tree.
This was asked in SugarCRM.


BuildTree(root, parent) {
   tag = readTag(); // assume function readTag will output each tag serially 
   if (isOpenTag(tag)) {
       node = new Node;  // create a new Node
       node.tag = tag; 
       Stack.push(node); // push into stack 
       if (root == NULL) { 
           root = node;  
       } elseif (parent != NULL) {
           AddChild(parent, node);
           BuildTree(root, node);  // Build tree with node as parent 
       } else {
          // error
   } else {
       temp = Stack.pop();
       if (Stack.NotEmpty()) { 
          parent = Stack.pop; 
          BuildTree(root, parent);  
   return root; 

What is ob_start in php ?

The PHP output buffering will save all the server outputs ( html and php prints) to a string variable.

So to start buffering, use ob_start(); this will keep saved any output.
Then you use $variable = ob_get_clean(); to stop buffering, and copy the buffer content to the variable.

Here are few samples of the use of ob_start() and ob_get_clean()

ob_start(); //Turn on output buffering ?>
Hello world, link
$var = ob_get_clean(); ?>
//copy current buffer contents into $message variable and delete current output buffer

Explain PHP namespaces

Previously in PHP you can’t have two classes that share the same name. They have to be unique. The issue with this restriction is that if you are using a third party library which has a class named User, then you can’t create your own class also called User. This is a real shame, because that’s a pretty convenient class name right?

PHP namespaces allow us to circumvent this issue, in fact we can have as many User classes as we like. Not only that, but we can use namespaces to contain our similar code into neat little packages, or even to show ownership.

Using a class declared in a namespace

Classes in Global namespace can also be referred as :

$eddard = new \Eddard();

Namespaces can have as many levels of hierarchy as they need to.