CareerRide DBMS Interview Questions and Answers

Introduction

• Speaker: Nishant
• Topic: Top 20 DBMS interview questions and answers for freshers and experienced professionals.

What is a Database?

• Organized collection of logically related data.
• Varies in size depending on purpose (e.g., a local contractor vs. a large corporation).

What is DBMS?

• Database Management System: middleman between user and database.
• Provides systematic approach to creating, storing, updating, and retrieving data.

Types of DBMS

1. Hierarchical Database System: tree hierarchy, single parent, multiple children.
2. Network Database System: hierarchical structure with multiple parents and children.
3. Object-Oriented Database System: information represented as objects.
4. Relational Database Management System (RDBMS): most popular, data stored in tables, row-based.

Advantages of DBMS

• Data Security: restricts access to authorized users.
• Improved Data Quality: enforces constraints for accurate data.
• Faster Data Accessibility: quick retrieval and display of data.
• Data Backup: automated periodic backups.
• Simplicity: easy operations (deletion, insertion).
• Reduce Data Redundancy: prevents duplicate data.

Differences Between DBMS and RDBMS

• Storage Format: DBMS = flat file; RDBMS = tables (rows and columns).
• Data Relationships: DBMS has none; RDBMS has relationships between tables.
• Normalization: absent in DBMS; present in RDBMS.
• Dataset Size: DBMS for small datasets; RDBMS for larger datasets.
• Distributed Database: not supported by DBMS; supported by RDBMS.

Query Optimization

• Process to find best methods for implementing queries.
• Minimizes time and resources for query execution.

Data Partitioning

• Divides a large table into smaller, manageable parts.
• Types:
  • Vertical Partitioning: split by columns.
  • Horizontal Partitioning: split by rows.
• Advantages: improved performance, better manageability.

Data Replication

• Copies of data distributed across multiple locations.
• Advantages:
  • Disaster Recovery: recover data from other servers.
  • Improved Accessibility: better response time from closest server.
  • Enhanced Performance: reduced load on individual servers.
• Types:
  • Full Table Replication: copies entire data.
  • Transactional Replication: copies initial data, updates only.
  • Snapshot Replication: takes a snapshot periodically.
  • Merge Replication: merges data from multiple databases.

Normalization

• Organizes data to reduce redundancy.
• Types:
  • First Normal Form (1NF): each cell contains one value, unique column names.
  • Second Normal Form (2NF): in 1NF, all non-key columns depend on primary key, no partial dependency.
  • Third Normal Form (3NF): in 2NF, no transitive dependency among non-primary attributes.

Denormalization

• Adding redundant data to optimize access speed.
• Done on normalized databases to avoid complex operations.

Types of Keys in a Database

• Candidate Key: can uniquely identify a row.
• Primary Key: selected from candidate keys, unique and not null.
• Super Key: attribute(s) that can uniquely identify a row, may contain nulls.
• Foreign Key: establishes relationship between two tables.
• Composite Key: combination of attributes to identify a record uniquely.
• Alternate Key: candidate keys not chosen as primary keys.

Differences Between Unique Key and Primary Key

• Primary Key: unique, not null, only one per table.
• Unique Key: unique, can contain nulls, multiple allowed.

Differences Between Trigger and Stored Procedures

• Stored Procedures: invoked explicitly, can take inputs and return results.
• Triggers: invoked implicitly during data modification, cannot take inputs.

What are Indexes?

• Data structure for optimizing performance by quickly locating data.
• Clustered Index: physical order of rows matches index.
• Non-Clustered Index: separate storage from table data, multiple allowed.

What is Cursor? Types of Cursors

• Cursor: temporary work area in memory for DML operations.
• Types:
  • Implicit Cursor: automatically created for DML operations.
  • Explicit Cursor: defined by programmer for SELECT statements.

What is SQL?

• Structured Query Language for interacting with databases.

What are Subqueries in SQL?

• Query within another query, executed first to supply output to main query.

Differences Between HAVING and WHERE Clause

• WHERE: filters individual rows, cannot use aggregate functions.
• HAVING: filters groups, can use aggregate functions.

Joins in SQL

• Used to retrieve data from multiple tables based on related columns.
• Types:
  • Inner Join: matches records in both tables.
  • Left Join: all from left table, matching from right.
  • Right Join: all from right table, matching from left.
  • Full Outer Join: all records from both tables.
  • Self Join: table joined to itself for comparisons.

Differences Between DROP, TRUNCATE, and DELETE Command

• TRUNCATE: removes all rows but keeps table structure, cannot be rolled back.
• DELETE: removes specific records based on conditions, can be rolled back.
• DROP: deletes the entire table including its structure.