Home >> Interviews Q & A >> Database Interview Questions and Answers for freshers and Experienced

Database Interview Questions and Answers for freshers and Experienced

Database Interview Questions and Answers for freshers and Experienced: DB Design, ER-Diagram, Normalization, Stored Procedure, Joins, Trigger, ACID Properties and Transactions, Indexing and Query Optimization, Constraints, Views and SQL etc
Q:- What is Database?
A Database is a collection of data.
Q:- What is Database Management Systems?
A Database management systems (DBMS) is software system for creating and managing databases.

Database management systems is also called DBMS in short.

Types of Database Management Systems:
  1. Hierarchical databases.
  2. Network databases.
  3. Relational databases.
  4. Object-oriented databases
Q:- What is Relational Database Management Systems (RDBMS) ?
  1. Relational Database Management System(RDBMS) is based on a relational model of data that is stored in tables inside the database.
  2. Data can be accessed, modify and managed using Structured Query Language (SQL).
Q:- What is NoSQL Database ?
  1. NoSQL, is an alternative to traditional relational databases (RDBMS).
  2. NoSQL databases are especially useful for working with large sets of distributed, semi-structured, un-structured data.
  3. MongoDB is one of the best example of most popular NoSQL database.
Q:- What is key? Type of key in Database?

Key: A key is single or combination of multiple fields used to access, modify data in database.

  1. Candidate key: A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table
  2. Primary key: The Minimal set of Candidate key which can uniquely identify a record in table, is called Primary key. A table can have only one primary key and it does not allow null value.
  3. Alternate key: A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.
  4. Composite key: A Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key
  5. Unique key: A Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table, Unique key can have only one NULL value.
  6. Super key: Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. Primary key, Unique key, Alternate key are the subset of Super Key.
  7. Foreign key: A Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.
    • RESTRICT: Rejects the delete or update operation for the parent table.
    • CASCADE: will propagate the change when the parent changes. (If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.)
    • SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.
      Note:- If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.
    • NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT.
    • SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.
You may also like - MySQL Interview Questions Answers
Q:- What is the difference between primary key and unique key?
  1. Primary key cannot have NULL value, while unique key can have NULL values.
  2. A table can have only one primary key, while a table can have multiple unique key.
Q:- What is the concept of Normalization?
  1. Normalization is the process of organizing (decomposing) the data in a relational database in accordance with a series of normal forms in order
    • To Reduce Data Redundancy
    • To Improve Data Integrity
    • To Remove Insert, Update & Delete Anomalies.
  2. It was first proposed by Edgar F. Codd
Q:- What are the different types of Normalization?
  1. First Normal Form (1NF):
    • A Table (relation) is in 1NF if each columns (attributes) contains an atomic value.

    As per the rule of first normal form, an column (attribute) of a table cannot hold multiple values.

    Student table which is not in First Normal Form(1NF):

    idnamesubject
    11JaiPHP, Python
    12ShivJava
    13AjayC++
    14VijayReact, Angular
    15AbdulNode js

    Now, Student table is in First Normal Form(1NF):

    idnamesubject
    11JaiPHP
    11JaiPython
    12ShivJava
    13AjayC++
    14VijayReact
    14VijayAngular
    15AbdulNode js

    Although some values are still getting repeated but values for the subject column are now atomic for each row.

    By Using the First Normal Form(1NF), data redundancy increases, as there will be many columns with same data in multiple rows but each row as a whole will be unique.

  2. Second Normal Form (2NF):

    A table is will be in 2NF if it satisfy following conditions:

    • It is in First Normal Form(1NF).
    • It should not have any Partial Dependency.
    idstudent_idsubject_idmarkstrainer
    111170PHP Trainer
    211275Python Trainer
    312380JavaScript Trainer

    Now if you look at the Score table, we have a column names trainer which is only dependent on the subject (subject_id).

    Now as we just discussed that the primary key for this table is a composition of two columns which is student_id and subject_id but the trainer's name only depends on subject.

    This is Partial Dependency, where an attribute in a table depends on only a part of the primary key and not on the whole key.

    Let's remove Partial Dependency by decomposing tables like below:

    subject_idtrainer
    1PHP Trainer
    2Python Trainer
    3JavaScript Trainer
    idstudent_idsubject_idmarks
    111170
    211275
    312380
  3. Third Normal Form (3NF):

    A table is will be in 3NF if it satisfy following conditions:

    • It is in Second Normal Form(2NF).
    • It should not have any Transitive Dependency.
  4. When a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key, this is called Transitive Dependency.

    Advantage of removing Transitive Dependency:

    • Data duplication will reduce.
    • Data integrity will be achieve.
  5. Boyce & Codd normal form (BCNF):

    Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anomaly that is not handled by 3NF

You may also like - Types of JOINS in SQL
Q:- What are the different types of SQL commands?
  • DDL – Data Definition Language
  • DML – Data Manipulation Language
  • DQL – Data Query Language
  • DCL – Data Control Language
  • TCL – Transaction Control Language

Read more about - SQL commands

Q:- What is the difference between Having and Where clause?
  1. HAVING: It is used to add a condition with GROUP BY. HAVING clause used to select rows after grouping.
  2. WHERE: It is used to add a condition to filter data. WHERE clause used to select rows before grouping.
Q:- What is an Index?

An index is used to speed up the performance of SQL queries, It can be created on column or group of columns

Q:- What is a Trigger?

A Trigger is a set of SQL statement that are associated with a table which automatically get executes before/after insert, update or delete operations.

Types of Triggers:
  1. Before/After Insert
  2. Before/After Update
  3. Before/After Delete
CREATE TRIGGER trigger_name 
when
ON table_name
FOR EACH ROW

BEGIN
//SQL Statements
END
#Example: Following is an example of a trigger on employee table which updates the increment column with the incremented salary of an employee.
CREATE TABLE `employee` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(250) NOT NULL,
	`manager_id` INT(11) NULL DEFAULT NULL,
	`salary` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
	`increment` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
);
DELIMITER $$
CREATE OR REPLACE TRIGGER empTrigger
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN   
      set new.increment = new.salary - old.salary; 
END $$
DELIMITER ;

Update all employees salary by 10%. once the following query will get executed after update empTrigger will also get executed and update increment column with increased salary of each employees.

UPDATE employee SET salary = salary + (salary * (10 /100) );
Q:- What is a stored procedure?

A stored procedure is a set of SQL statements, invoke whenever we call it.

Advantage of stored procedures:
  1. It is Faster because -
    1. It is Pre-compiled
    2. It reduce network traffic
  2. It is reusable
  3. It can handle complex operation
  4. It support nested stored procedure
  5. Secure - It’s security is high. No body can see the code because it stay in Database Server.
Disadvantage of stored procedures:
  1. It’s Debugging is difficult
  2. It's Difficult to maintain
  3. Memory usage increased
You may also like - MySQL Stored Procedure with Examples
Q:- What is an View?

A view is a virtual table whose content are defined by a query and stored in database. A view contains rows and columns, just like a real table.

//Create View
CREATE VIEW VIEW_NAME
AS 
SQL_QUERY;

//Select Data from View
Select * from VIEW_NAME;

//Note - Replace VIEW_NAME & SQL_QUERY with their actual value.

Note: As data update in tables view update automatically.

You may also like - MongoDB Interview Questions Answers
Q:- What is CAP Theorem?

The concept of CAP theorem was initially proposed by Eric Brewer.

A distributed database system can only have only two proporties at a time among three properties of CAP theorem i.e. Consistency, Availability and Partition Tolerance.
Following are 3 main properties of CAP Theorem:
  1. Consistency: Every read receives the most recent write or an error.
  2. Availability: Every request receives a (non-error) response – without the guarantee that it contains the most recent write.
  3. Partition tolerance: The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes.
Case: When to you use which?
  • Consistency: If you are going develope a transaction based system/project, relational database system then consistancy is more important than availablity.
  • Availability: If you are going develope a report dashboard system, analytics system, big data project, nosql database then availablity is more important than consistancy.

Note:

  • So it's upto your requirement which combination you have to choose among consistancy, availablity, partition tolerance.
  • Generally, RDBMS focus on consistancy while NoSQL database focus on Availability.
Q:- What is the difference between Acid and Base Properties in DBMS?
ACID Properties:
In order to maintain consistency in a database, before and after transaction, certain properties are followed, called ACID properties.
  1. Atomicity: It states that either all or none.
  2. Consistency: The database must remain in a consistent state after any transaction.
  3. Isolation: It ensures that multiple transactions can occur concurrently without leading to inconsistency of database state.
  4. Durability: The database should be durable enough to hold all its latest updates even if the system fails or restarts.

Examples: Almost all RDBMS like MySQL, Oracle, MSSQL Server, Postgre SQL etc follows ACID Properties.

BASE Properties:
In order to maintain high availablity of a database, certain properties are followed, called BASE properties.
  1. Basic Availability: The database system does gaurantees their availablity most of the time. i.e. Database System is Highly Available.
  2. Soft-state: It indicates that the state of the system may change over time, even without input. This is because of the eventual consistency model.
  3. Eventual consistency: It indicates that the system will become consistent over time, given that the system doesn't receive input during that time.

Examples: Almost all NoSQL databases like MongoDB, DynamoDB etc follows BASE Properties.

You may also like - MySQL 8.0 New Features
Full Stack Tutorials

Author @FullStackTutorials | View all Articles