Database Interview Questions and Answers

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.

Some important modern databases are

• Centralised Database
• Relational Database
• Cloud Database
• NoSQL Database
• Personal Database
• Distributed Database
• End user Database
• Commercial Database
• Operational Database
• Graph Database
• Object oriented Database

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.

Relational Database: A relational database refers to a database that stores data in a structured format using tables (rows and columns)

Example of Relational Databases such as DB2, MySQL, Oracle, PostgreSQL, SQL Server, etc

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

Example of Non relational databases such as Apache HBase, IBM Domino, MongoDB, Cassandra, CouchDB, Neo4j and Oracle NoSQL database, etc

• Data definition language
• Data manipulation language
• Query language

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.

A data model determining fundamentally how data can be stored, manipulated and organised and the structure of the database logically is called database model.

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.

Data mining refers to extracting knowledge from large amounts of data. That data sources can include databases, DW, web etc.

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.

Division of logical database into independent complete units for improving its management, availability and performance is called Database partitioning.

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.

Data integrity is of particular concern in relational databases, and RDBMS use a number of constraints to ensure that the data contained in your tables is reliable and accurate.

Duplication of data in the database is known as Data redundancy.

A relation is said to be in Functional dependency when one attribute uniquely defines another attribute.

Data Independence refers to the ability to modify the schema definition in one level in such a way that it does not affect the schema definition in the next higher level.

• Physical Data Independence: It modifies the schema at the physical level without affecting the schema at the conceptual level.
• Logical Data Independence: It modifies the schema at the conceptual level without affecting or causing changes in the schema at the view level.

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.

De-normalization is the process of adding up redundant data on the table in order to speed up the complex queries and thus achieve better performance.

Stored procedure is a set of pre-compiled SQL statements, executed when it is called in the program.

Phantom deadlock detection is the condition where the deadlock does not actually exist but due to a delay in propagating local information, deadlock detection

A data structure in the form of tree which stores sorted data and searches, insertions, sequential access and deletions are allowed in logarithmic time.

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.

Triggers are similar to stored procedure with the difference that they get automatically executed when any operations occur in the table.

Fragmentation can be defined as a database feature of server that promotes control on data which is stored at table level by the user.

 A query contained by a query is called sub-query.

Joins help in explaining the relation between different tables.

Self join is a join where two instances of the same table will be joined in a query.

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.

Some DDL commands are:

• CREATE to create a new table or database.
• ALTER for alteration.
• Truncate to delete data from the table.
• DROP to drop a table.
• RENAME to rename a table.

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.

Some DML commands are:

• INSERT to insert a new row.
• UPDATE to update an existing row.
• DELETE to delete a row.
• MERGE for merging two rows or two tables.

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.

• Some DCL commands are:
• COMMIT to permanently save.
• ROLLBACK to undo the change.
• SAVEPOINT to save temporarily.

It allows searching for information and computing derived information.

VDL: VDL stands for View Definition language. It represents user view and their mapping to the conceptual schema.

SDL: SDL stands for Storage definition Language. It specifies the mapping between two schemas.

Database lock basically signifies the transaction about the current status of the data item i.e. whether that data is being used by other transactions or not at the present point of time.

There are two types of Database lock which are Shared Lock and Exclusive Lock

Cursor is temporary work area which stores the data as well as result set occurred ofter manipulation of data retrieved.

Two types of Cursors are:

Implicit cursors: Implicit cursors are declared automatically when DML statements like INSERT, UPDATE, and DELETE are executed.

Explicit cursors: Explicit cursors have to be declared when SELECT statements which are returning more than one row are executed.

A program module providing the interface between low-level data in database, application programs & queries submitted to the system is referred to as a storage manager.

Indexes help in improving the speed as well as the query performance of database. The procedure of boosting the collection of indexes is named as Index hunting.

A program module responsible to get data from disk storage into main memory and decide on the data that should exist in cache memory.

PostgreSQL is an open source, object-relational database management system (ORDBMS). It is used to store data securely; supporting best practices and allow retrieving them when request is processed.  It supports both SQL (relational) and JSON (non-relational) querying.

All system objects are physically stored in resource database and logically available on every database. Resource database can faster the service packs or upgrades.

System databases are the default databases that are installed when the SQL Server is installed. Basically there are 4 system databases: Master, MSDB, TempDB and Model. It is highly recommended that these databases are not modified or altered for smooth functioning of the SQL System.

A program module ensuring that database remains in a consistent state even after the system failures and concurrent transaction execution keeps going on without conflicting.

A program module that manages space allocation on disk and data structure used to represent information on a disk.

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.