Top Interview Questions and Answers

Top Interview Questions and Answers
Interview Sortout

Browsing "Older Posts"

Browsing Category "DBMS"

TOP 10 : MySQL Database Interview Questions For Experienced

By Dhananjay → March 22, 2018
MySQL Database Interview Questions For Experienced

What are the storage engines used by MySQL?
Storage engines supported by MySQL are as follows:
  1. InnoDB
  2. MyISAM
  3. Memory
  4. CSV
  5. Merge
  6. Archive
  7. Federated
  8. Blackhole
Which one is the default storage Engine in MySQL?
The default engine is InnoDB.

What is a database engine?
Database Engine is the basic building blocks of storage systems , used by Database Management Systems to run CRUD (INSERT,SELECT,UPDATE,DELETE)instructions.

What is difference between InnoDB and MyISAM?
InnoDB is featured with ACID propety of transactions, but MyISAM does not support transaction. MyISAM supports FULLTEXT search index where as InnoDB does not.

What are HEAP tables in MySQL?
HEAP tables are the existing tables in memory.They also known as memory tables and use Indexes instead of BLOB and TEXt.

What are Stored Procedures in MySQL?
Stored Procedure is Batch of SQL statements and saved in database. It also accepts parameters and can be triggered explicitly. It improves the traffic control and gives a modular structure to implement maintainability.

CREATE PROCEDURE show_employee

AS

    SELECT FirstName, LastName

    FROM employee;

EXEC show_employee;

What is Trigger?
Auto executable sets of SQL statements stored in database are known as Triggers. They are fired when some event CRUD event occurs.When we do some CRUD operations on table the trigger linked with that table fires. Trigger is behaves like a stored procedure but it invoked automatically. It adds a layer of security in database.

What are differences between Trigger and Stored Procedure?

We can execute procedure explicitly whenever we would like , but trigger can only be invoked on an event fire.We can call a Procedure inside another Procedure but we can not invoke a trigger directly inside another trigger. We can use trigger for transaction purpose but we can not use procedure for transactions.

What are the CONSTRAINTs in MySQL?
MySQL CONSTRAINTs used to define the rules for data restrictions. MySQL provides various types of constraints as:
NOT NULL- does not allow NULL value
UNIQUE - does not allow duplicate entry in column
PRIMARY KEY - uniquely identify a rows in a table
FOREIGN KEY - Primary Key of another table is being as a reference in another table
CHECK - Check for the valid data to be saved in the database
DEFAULT - set the default value for the column, in case of no value passed to SQL statement.
We can declare MySQL CONSTRAINT at the time of table creation or we can alter table to apply CONSTRAINT.

What do you mean by index in a table?
MySQL Indexing is the indexing of data as keys to make the accessibility fast and easy. Rather than searching all over the data indexing allows to get directly to the indexed values. It Eliminates the unnecessary iteration because the indexes are stored in the form of B.Tree.

Basic MySQL Interview Questions and Answers

By Dhananjay → March 19, 2018


What is MySQL?
MySQL is a Relational Database Management System marketed and distributed by Oracle as an open source software. However, enterprise edition is also available with support systems.

What is Relational Database?
Yes, we can say a database as a Relational database, because tables in the database are related to each other.

What is the use of the DISTINCT phrase in MySQL?
DISTINCT eliminates the duplicate records in the selection and return unique values. We can use it also with aggregate functions like AVG, SUM, MAX, MIN etc.

What is SELF JOIN?
Join a table to itself is called SELF JOIN. We must use an alias of the table to join itself. There is no any phrase like SELF JOIN.

What is LEFT JOIN?
LEFT JOIN gets the column of the LEFT table if the relation exists with the next table.

What is CROSS JOIN?
CROSS JOIN is a cartesian product of two tables and does not require any join condition. CROSS JOIN used to JOIN two table having no relations.If table A has 100 rows and Table B has 200 rows then we will get 100x200 = 20000 rows in CROSS JOIN.

What is INNER JOIN?
INNER JOIN gets all the records that are matched or common between the tables followed by the condition.

What is FULL JOIN?
FULL JOINS returns the records from both the tables. It is the result of LEFT JOIN and RIGHT JOIN at the same time.

What is RIGHT JOIN?
RIGHT JOIN results in the column of the RIGHT table if the relation exists with the next table.


What is OUTER JOIN?
LEFT JOIN, RIGHT JOIN and FULL JOIN are OUTER joins.

What are aggregate functions in MySQL?
The function that performs a calculation on records and returns a resulting single value is known n as an aggregate function. We have many aggregate functions in MySQL. As : AVG,SUM,MIN,MAX,COUNT.

What is the difference between DISTINCT and GROUP BY?
DISTINCT can return NULL values because it considers NULL as a value and selects one NULL from many, however, GROUP BY does not consider NULL values as a record. GROUP BY sorts the records where DISTINCT does not.

What is VIEW MySQL?
VIEW is an imaginary table holding the real records, based on query statements. It fetches data from real tables and displays always updated values. We can do all the MySQL operations on it.  It helps to achieve security and reusability while implementing database operations. We can hide sensitive data from end users while limiting the access of original tables.
For example, we can have a VIEW for all the employee who is older than 40 years.

CREATE VIEW older_than_fourty AS SELECT * FROM employee WHERE age > 40 ;




DBMS Interview Questions ans Answers - Basic Concepts

By Dhananjay → January 25, 2018
What is DBMS?
A database management system (DBMS) is system software. The DBMS is used for creating databases and manipulating data and records in an organized way.

What is RDBMS?
RDBMS stands for Relational Database Management System. it is based on relational blocks.RDBMS replaces the traditional file system to store the data. All modern systems follow the concept of RDBMS. for example MS SQL Server, Oracle, MySQL etc.