Database Interview Questions and Answers

Database is a collection of data stored in an organized manner. Many enterprises and organizations create and manage databases using a database management system. Special DBMS software can be used create and store product inventory and customer information, for example. Organizations most often use databases for online transaction processing (OLTP). Database software needs to provide easy access to information and fast querying so that transactions can be carried out efficiently. Databases are often referred to as operational systems, meaning they are used to process day-to-day transactions in an organization.(Introduction of database Source: Panoply)

Database is a set of data or information that is stored in a logically related format. The collected data could be in any number of formats like electronic, printed, graphic, audio or video, statically, combinations.

SQL stands for Structured Query Language. Its being ANSI standard language updates database and commands for accessing. It is designed specifically for storing and managing that data in RDMS using all kind of Data operations.

DBMS stand for Database Management System is a set of programs that enables storing, modifying, and extracting information from a database, it also provides used to add, delete, modify, access, and analyze data stored in Database. Data is stored in either a hierarchical form or a navigational form. Examples are file systems, XML, etc.

RDBMS stands for Relational Data Base Management Systems. It is a specially designed for relational databases. Which data is stored in tables (rows and columns) and the relationships among the data is also stored in tables. Examples are MySQL, PostgreSQL, Oracle, SQLserver, DB2 etc.

A multidimensional database is created from multiple relational databases. While relational databases allow users to access data in the form of queries, the multidimensional databases allow users to ask analytical questions related to business or market trends. These databases are used mostly OLAP and DW.

Non Relational Database: This database is also called NoSQL database. This database stores data in a semi structured format using XML, JSON. These databases are usually grouped in to four categories such as Key value stores, Graph stores, Column stores, and document stores

A spatial database is optimized to store and query data representing objects. These are the objects which are defined in a geometric space. Spatial data is associated with geographic locations such as cities, towns etc.

Data warehousing is a collection of tools and techniques using which more knowledge can be driven out from a large amount of data. This helps with the decision-making process and improving information resources.

A data dictionary contains metadata i.e. data about the database. The data dictionary is very important as it contains information such as what is in the database, who is allowed to access it, where is the database physically stored etc.

Sequence of operation performed which changes the consistent state of the database to another is known as the database transaction. After the completion of the transaction, either the successful completion is reflected in the system or the transaction fails and no change is reflected.

A database instance describes a complete database environment, including the RDBMS software, table structure, stored procedures and other functionality. Database administrators might create multiple instances of the same database for different purposes.

Normalization is the process of removing the redundant data from the database by splitting the table in a well-defined manner in order to maintain data integrity. This process saves much of the storage space.

Checkpoint declares a point before which all the logs are stored permanently in the storage disk and is the inconsistent state. In the case of crashes, the amount of work and time is saved as the system can restart from the checkpoint.

DDL stand for Data Definition Language. It is managing properties and attributes of database.DDL commands are used to define the structure that holds the dataThese commands are auto-committed i.e. changes done by the DDL commands on the database are saved permanently.

DML stands for Data Manipulation Language. It is manipulating data in a database such as inserting, updating, deleting.DML commands are used to manipulate the data of the database. These commands are not auto-committed and can be rolled back.

DCL stands for Data Control Language. It is control the data in database.DCL commands are used to control the visibility of the data in the database like revoke ace permission for using data in the database.

E-R model is an Entity-Relationship model which defines the conceptual view of the database. E-R model basically shows the real world entities and their association/relations. Entities here represent the set of attributes in the database.