Top 30 Database interview question

what is SQL ?

The ANSI standard programming language Structured Query Language (SQL) refreshes databases and instructions for accessing them.
SQL may be used to communicate and manage data, notably data found in relational database management systems (RDBMS), which organises data into tables. A common field can also be used to link many files that each contain tables of data. SQL allows you to query, update, and rearrange data, as well as construct and change a database system’s schema (structure) and manage access to its data.

What is DBMS?

Database Management Systems (DBMS) are software programmes that allow users to communicate with other software.

What are the features of Database language?

A database language may have characteristics such as:
DBMS-specific Storage engine configuration and administration
Summing, counting, averaging, grouping, sorting, and cross-referencing are examples of calculations used to modify query results. enforcing constraints API stands for application programming interface.

Distinguish the development of database technology

The following are the stages of database technology development:

Data model or structure
Model of navigation
Relational model (SQL)

What is RDBMS?

Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from relational database can be accessed using an API, Structured Query Language (SQL).

What is a Database system?

The database and DBMS software together is called as Database system.

Describe the three levels of data abstraction?

There are three levels of abstraction:

• Physical level: The lowest level of abstraction describes how data are stored.
• Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
• View level: The highest level of abstraction describes only part of entire database.

What are the different types of SQL’s statements?

SQL statements are broadly classified into three. They are-

• DDL: DATA DEFINITION LANGUAGE

Used to define the structure that holds the data.

Ex: create, alter, drop, truncate

• DML: DATA MANIPULATION LANGUAGE

Used for manipulation of the data itself.

Ex: Insert, Delete, Update, select and retrieving the data from the table

• DCL: DATA CONTROL LANGUAGE

Used to control the visibility of data like granting database access and set privileges to create tables.

Ex: Grant, Revoke access permission to the user to access data in the database.

What is a database transaction?

Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.

What are properties of a transaction?

Properties of the transaction can be summarized as ACID Properties.

• ATOMICITY: When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.
• CONSISTENCY: The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.
• ISOLATION: Every transaction should operate as if it is the only transaction in the system.
• DURABILITY: Once a transaction has completed with success, the updated rows/records should be out there for all different transactions on a permanent basis.

What are the different types of normalization?

In database design, we start with one single table, with all possible columns. A lot of redundant data would be present since it’s a single table. The process of removing the redundant data, by splitting up the table in a well defined fashion is called normalization.

• FIRST NORMAL FORM (1NF) :A relation is said to be in first normal form if and only if all underlying domains contain atomic values only. After 1NF, we can still have redundant data.
• SECOND NORMAL FORM (2NF): A relation is said to be in 2NF if and only if it is in 1NF and every non key attribute is fully dependent on the primary key. After 2NF, we can still have redundant data.
• THIRD NORMAL FORM (3NF): A relation is said to be in 3NF, if and only if it is in 2NF and every non key attribute is non-transitively dependent on the primary key.

What is a primary key?

A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as primary key, following conditions had to be met:

• No two rows can have the same primary key value.
• Every row must have a primary key value.
• The primary key field cannot be null.
• Value in a primary key column can never be modified or updated, if any foreign key refers to that primary key.

What are partial, alternate, artificial, compound and natural key?

• Partial Key: It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator.
• Alternate Key: All Candidate Keys excluding the Primary Key are known as Alternate Keys.
• Artificial Key: If no obvious key, either stand alone or compound is available, then the last resort is to simply create a key, by assigning a unique number to each record or occurrence. Then this is known as developing an artificial key.
• Compound Key: If no single data element uniquely identifies occurrences within a construct, then combining multiple elements to create a unique identifier for the construct is known as creating a compound key.
• Natural Key: When one of the data elements stored within a construct is utilized as the primary key, and then it is called the natural key.

What is BCNF ?

Boyce-Codd Normal Form (BCNF) a relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.

What is a view?

A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.

Define database model

A database model is a data model that determines how data may be saved, processed, and organized, as well as the logical structure of the database.

Enlist the various relationships of database

The following are the different database relationships:

• One-to-one: A single table with a drawn relationship to another table with columns of the same kind.
• One-to-many: A relationship between two tables based on their primary and foreign keys.
• Junction table has many tables connected to many tables (many-to-many).

Define Normalization

Normalization is the process of organising data in a database such that it is free of inconsistencies and redundancies.

Define Denormalization

Denormalization is the process of improving database performance by adding redundant data and thus removing complicated data.

Define DDL and DML

Data Definition Language is a language for managing database characteristics and attributes (DDL).

Data Manipulation Language is a programming language for manipulating data in a database, such as adding, updating, and removing records. (DML)

Enlist some commands of DDL

CREATE:

In the CREATE TABLE statement, create is used. Syntax is defined as:

CREATE TABLE [column name] ( [column definitions] ) [ table parameters]

DROP:

Its syntax is :

DROP objecttype objectname.

ALTER:

ALTER allows you to change an existing database object. Its syntax is :

ALTER objecttype objectname parameters.

Define cursor

Cursor is a database object that aids in the manipulation of data row by row to represent a result set.

Enlist the cursor types

• It’s dynamic, which means it changes as you scroll.
• Data change without reflection of new data is observed in the keyset.
• Static: does not reflect changes when scrolling and focuses on snapshot recording.

What do you mean by Index hunting?

Indexes help in enhancing the database’s speed as well as query performance. Index hunting is the process of increasing the number of indices in a collection.

Define Join and enlist its types

Joins help in the explanation of relationships between tables. They also provide you the option of selecting data that is related to data in another table.

The following are the various kinds:

• INNER JOINS: When more than two tables are linked, blank rows are left in the centre.
• OUTER JOINS: There are two types of outer joins: left outer join and right outer join. By connecting tables on the other side, blank rows are left on the given side.

Other joins : NON-EQUI JOIN , EQUI JOIN , NATURAL JOINs and CROSS JOINs .

Define Atomicity and Aggregation

Atomicity: 

It’s an all-or-nothing approach that ensures the user that any outstanding transactions will be completed. In the database management system, tasks involving incomplete transactions are left undone.

Aggregation:

This model aggregates the gathered entities and their relationships. It’s mostly used to express inter-relationship connections.

 Define Object-oriented model

This model is made up of object compilations, with values saved in instance variables inside the object. The object’s action is carried out via the object’s bodies, which are referred to as methods. Classes are objects that have the same kind of variables and methods.

What is Executive Plan?

• In terms of performance enhancement, this is an essential characteristic.
• Textual or visual representations of the data execution plan are available.
• SQL Server saves the collected procedure or query execution plan and uses it in later calls.

Make a list of ways to code transactions more efficiently

• Data transactions should not be opened when surfing.
• While transacting, the least amount of data must be accessible.
• During transactions, no user input should be permitted.
• Transactions should be kept to a minimum.
• Lower degrees of transaction segregation.

What do database languages do?

They have the following properties as special-purpose languages:

• Query language
• Data definition language
• Data manipulation language

We learnt how to answer various database interview questions. We learnt some fundamental database information that you may be asked for, as well as some responses and resources that you may find beneficial. If you obtain the job, please leave a remark at the bottom of the post or send us a message about your interview. Thank you very much.