DBMS Interview Questions and Answers

Constraints are set of rules used to restrict the type of data that can go into a table, to preserve the accuracy and integrity of the records internal the table.

The different types of constraints in DBMS are as follows:-

• NOT NULL:-it makes sure that column does not hold a NULL value.
• UNIQUE:- It invokes a column to have a UNIQUE value
• DEFAULT:-gives the default value to the column
• CHECK:-specify the range of values for a column
• Key Constraints –
• PRIMARY KEY:-it diagnose each and every record in a table
• FOREIGN KEY:- it points to the primary key of any other table.

Aggregate functions are the calculations on the set or group of value, and they return a single value.

The aggregate functions are :-

• AVG-- to calculate the average of the data.
• MAX-- to find the maximum value among the given data.
• MIN-- to find the minimum value among the given data.
• SUM-- It returns the SUM of the data.
• COUNT( )--It returns the total number of value in the particular column in the table.
• COUNT (*)-- It returns the number of rows in the table even the NULL value.

Scalar Functions are the calculations on the data given by the user, and they return a single value.

The scalar functions are :-

• UCASE()-- it converts the data into the CAPITAL LETTERS
• LCASE()-- it translates the data into the SMALL LETTERS
• MID()-- it extracts the particular text from the given text
• LEN()—it returns the length of the text in the text files
• ROUND()-- it round of the decimal number for ex-5.5 to 6
• NOW()-- it returns the date and time of the system in use
• FORMAT()-- it formats the field as per the requirement.

There are seven types of database keys:-

• Super Key:- This key recognizes the row in the table.
• Primary Key:-This key is a column in the table which recognizes each row in the same table uniquely.
• Candidate Key:-This key does not have any repeated attribute.
• Alternate Key:- the -The key which is not primary is called Alternate Key.
• Foreign Key:-This key a column in the table to create a relationship with another table.
• Compound Key:-This key has various fields which allow the user to identify a particular record uniquely.
• Composite Key:-This multiple key attributes to identify the rows uniquely.
• Surrogate Key:-This is an artificial key finds each of the records uniquely.

A database management system is a software which is used to manage the data by efficiently storing, managing and retrieving it along with the high-end security. Some of the database software are -MySQL, Oracle, Sybase, etc.

Uses of DBMS

• Secure management of data
• Easy to understand and Implement
• Data Storage
• Multiuser interface

DBMS has some of the following advantages:-

• An amazing decision-making ability
• Minimizes the data Redundancy
• Fast searching Capability
• Low maintenance cost

This question has always been a center of the discussion in DBMS interview questions.

Data Redundancy means when the same data is repeated again and again at multiple locations. As a result, deletion, insertion, and updating of the data become a tedious job and also a lot of space is also wasted. Normalization solves this problem by reducing the data redundancy.

There are 3 types of Normalization:-

• 1st Normal Form
• 2nd Normal Form
• 3rd Normal Form
• BCNF

A deadlock condition occurs when one task is waiting for the other work to leave the resource which it has a hold. In this current situation, none of the functions gets completed, and the work is always in the waiting state.

There are 3 methods by which deadlocks can be handled:-

• Deadlock Prevention:-Do not allow the condition that may lead to deadlock.

• Deadlock Avoidance:- Does not accept the resource request if it can lead to deadlock

• Deadlock Detection:-Allow the resource request but periodically checks the deadlocks. If found then one of the transaction is aborted.

There are 4 types of database languages:-

• Data Definition Language:-It includes CREATE:-Create a new database or table, ALTER:-Alter the existing database or table, DROP: It drops the database, RENAME: Set a new name for the current database

• Data Manipulation Language:- It includes:-SELECT: Retrieve the data from the database, INSERT: Insert the data, UPDATE: Update the data, DELETE: Delete all the records

• Data Control Language:-It includes:-GRANT: It gives permission to access the database, REVOKE: Take back the permission to access the database.

• Transaction Control Language:- It includes COMMIT: It saves the work, SAVEPOINT: It sets a point in the transaction to rollback later, ROLLBACK: It restores since the last commit

Point to be noted: Go through this Q&A very thoroughly as this is one of the critical DBMS interview questions for freshers.