20 Database Interview Questions and Answers 2020

20 Database Interview Questions and Answers 2020

Define Database.

A database is a collection of inter-related records that can be organized into rows, columns, and tables so it can be easily accessed, managed and updated.

What are DBMS and Data Warehouse?

DBMS: A Database Management System (DBMS) is a collection of database and mechanisms (programs) to manipulating the information.

Data Warehouse: A data warehouse is a database that is designed for query and analysis instead of for transaction handling. It usually used for reporting and analysis. It can include data from other sources.

It is also known as an enterprise data warehouse which used for managing the information i.e. business intelligence, ETL, etc. that framework is known as Data Warehousing.

Define Join and enlist its types.

Joins help to combining between different tables in a relation database.

Types of Joins:

Cross JOIN: it is a production of the first table row with the second table row.

 

INNER JOIN : Selects all rows from multiple tables where the join condition happens.

OUTER JOIN: it is divided into 3 types  Left Outer Join, Right Outer Join, and Full outer join.

left outer join ⟕

Right outer join ⟖

Full outer join ⟗

What is a PRIMARY KEY in DBMS?

The PRIMARY KEY is the used for uniquely identify each row of a table.

What is a FOREIGN KEY in DBMS?

A FOREIGN KEY is one or more uniquely identifies columns whose values are based on the PRIMARY or CANDIDATE KEY values from the database.

What is a UNIQUE KEY in DBMS?

A UNIQUE KEY is defined as unique to the database for each row of the table.

What is E-R model?

It is a graphical representation of entities (or objects) in a database.  An entity-relationship model also called an entity-relationship (ER) diagram.

What are the data types available in DBMS?

CHARACTER (length) or CHAR (length): Character string. fixed length i.e. CHAR(25) or CHARACTER(25)

VARCHAR (length):  VARCHAR maximum size for this field, but allocate only data entered

INT: it includes the whole number

SMALLINT: the store numeric value implied scale of zero

REAL: number with the fraction part

FLOAT: maximum length of number up to 64.

DATE: accept a date as a value.

TIME: accept time as a value.

TIMESTAMP: accept timestamp as a value. Which is a combination of date and time.

BOOLEAN: store two values true and false.

What is Normalization in a database?

Normalization is the method to reduce redundant data from database table to increase the storage efficiency, scalability, and integrity.

Explain various normal forms in DBMS?

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce Codd Normal Form (BCNF)

Fourth Normal Form (4NF)

Fifth Normal Form (5NF)

What are some conditions which in you would de-normalize data?

In the actual world, the database may be very large and it’ll have lots of data. Small request to retrieve the data from a database. It takes time. The time of the query will explosion due to normalization perform in a database. Which will need to be a part of various tables to get the data; any small query on the table will have to traverse the table till it find the record. When we normalize the table, we may not recognize approximately record amount.

What is an index and how does it support your database?

A database index is a record which speeds up a performance of queries operations on a database table.  At the cost of additional writes and storage space to preserve the index data structure. Indexes are used to find data speedy without having to search every row each time in a database table.

It will reduce the time spent in transactions. In simple word, an index is like a book index. It will make search query simpler and faster. In database, some concept applies to files from the memory.

What is a transaction and why is it important?

A transaction is a minor part of a program that can be accessed and update data. A transaction in a database system must follow to the ACID (Atomicity, Consistency, Isolation, and Durability) properties.

ACID properties are assurances that transaction complete or not.

Why should every SELECT always include DISTINCT?

SELECT DISTINCT queries is used for removing duplicates values for results, Let’s assume your query is unsuitable and does return duplicates values, then including DISTINCT simply hides the problem. It performs like SELECT queries

What is a deadlock?

A deadlock is a state when two or more transactions are waiting for other transactions finished but none of the transactions is willing to surrender the resources that other transactions needs. In this state, no transactions ever get finished and are in waiting for state forever.

Explain sequences.

A sequence is a feature which is supported by certain database management systems to produce unique values on request. It is like AUTO_INCREMENT but it has some additional features.

Syntax:

CREATE Sequence sequence-name

start with initial-value

increment by increment-value

maxvalue maximum-value

cycle|nocycle

Explain triggers in Database.

A database trigger is one kind of stored procedure that automatically executes when an event occurs in a particular table in a database. It is typically used for keeping the integrity of the data in the database.

Who is the most important user of a database?

Every user is important which has a connection to a database server like all other User.

Which is faster: inserting 1000 rows of data, or updating 1000 rows of data?

We can’t update and insert 1000 rows right away; you can only do them one at a time.

Tell me a few statements used in DBMS

In DBMS we’ve got many statements such as select, update, delete

What is Dynamic memory allocation?

it is of two kinds of memory allocation, malloc, calloc.

In malloc, the memory is allocated into a single unit and it is divided

But in calloc it allocates the memory in the form of blocks. They are type casted since the default return type of malloc, calloc is void.

Leave a Reply

Close Menu