Q1). What is a database?

A database is an organized collection of data that is stored and accessed electronically.

For example: think of a contact list on your phone. It’s organized by names, phone numbers, and other details, making it easy to find and manage contact information.

Q2). What is a DBMS?

A Database Management System (DBMS) is software that manages databases and provides an interface for users to interact with the data. Imagine a librarian who manages a library. The librarian helps you find and check out books, just like a DBMS helps you store, retrieve, and manage data in a database.

Q3). What is SQL?

SQL (Structured Query Language) is a language used to communicate with and manage databases.

For example: if you want to find all the contacts with the name 'John' in your phone’s contact list, you would use SQL to write a query that searches for 'John'.

Q4). What are tables in a database?

Tables are the basic building blocks of a database, where data is stored in rows and columns. Think of a table like a spreadsheet in Excel. Each row represents a different record (like a different contact), and each column represents a different attribute of the record (like phone number or email address).

Q5). What is a primary key?

A primary key is a unique identifier for a record in a table.

For example: in a school database, each student might have a unique student ID that ensures each student’s record can be uniquely identified and retrieved.

Q6). What is a foreign key?

A foreign key is a field in one table that links to the primary key in another table, creating a relationship between the two tables. For instance, in a library system, the 'Books' table might have a foreign key linking to the 'Authors' table to show which author wrote each book.

Q7). What is normalization?

Normalization is the process of organizing data to reduce redundancy and improve data integrity. Imagine you have a list of contacts with repeated information. Normalization would involve separating the repeated information into different tables, such as separating contact details from company information to avoid duplication.

Q8). What are the different types of relationships in databases?

The main types of relationships are one-to-one, one-to-many, and many-to-many.

For example: in a company's database: one-to-one might be a single employee having one company car, one-to-many might be a single customer having multiple orders, and many-to-many might be students enrolling in multiple courses and courses having multiple students.

Q9). What is an index in a database?

An index is a data structure that improves the speed of data retrieval operations. It’s like having a table of contents in a book that helps you quickly locate specific chapters or sections without reading the entire book.

Q10). What is a view in a database?

A view is a virtual table created by querying one or more tables.

For example: you might create a view in a sales database that only shows the total sales for each month, without altering the original sales data.

Q11). What is a stored procedure?

A stored procedure is a set of SQL statements that can be executed as a single unit. It’s like having a pre-defined checklist that you can use to perform a specific task, such as processing a customer order, without having to manually execute each step.

Q12). What is a trigger?

A trigger is a set of instructions that are automatically executed in response to certain events on a table or view.

For example: a trigger might automatically update the stock quantity in a warehouse database whenever a new order is placed.

Q13). What is a transaction in a database?

A transaction is a sequence of operations performed as a single logical unit of work.

For example: in online banking, transferring money from one account to another must be done in a single transaction to ensure that either both accounts are updated or neither is, to avoid errors.

Q14). What are ACID properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable database transactions.

For example: when booking a flight, atomicity ensures that the booking is either fully completed or not processed at all, consistency ensures the availability of accurate flight data, isolation keeps your booking process unaffected by other transactions, and durability ensures your booking is saved even if the system crashes.

Q15). What is denormalization?

Denormalization is the process of combining tables to improve read performance, often at the expense of introducing redundancy.

For example: combining customer and order information into a single table can speed up access times for queries that need both sets of information together.

Q16). What is a database schema?

A database schema is the structure that defines how data is organized and related. It’s like a blueprint for a building, showing where each room (table) goes and how they are connected (relationships).

Q17). What is data integrity?

Data integrity refers to the accuracy and consistency of data throughout its lifecycle.

For example: ensuring that a customer’s address is correctly and consistently updated across all records and systems helps maintain data integrity.

Q18). What is a join operation?

A join operation combines rows from two or more tables based on a related column.

For example: if you have a table of customer information and a table of orders, a join operation can combine these to show which customers made which orders.

Q19). What is a subquery?

A subquery is a query within another query.

For example: if you want to find employees who earn more than the average salary in your company, you would first calculate the average salary with a subquery and then use that result in your main query.

Q20). What is a cluster index?

A cluster index sorts the data rows in a table based on the index key.

For example: if you have a table of books sorted by title, the cluster index ensures that the books are physically stored in alphabetical order by title.

Q21). What is a non-clustered index?

A non-clustered index creates a separate structure from the table and holds pointers to the actual data.

For example: if you have an index on author names in a book database, it helps you quickly locate the author in the main table without rearranging the table's physical storage.

Q22). What is SQL injection?

SQL injection is a type of attack where malicious SQL code is inserted into a query to manipulate the database.

For example: if a login form allows users to input a username and password without proper validation, an attacker might enter SQL code to bypass authentication or access unauthorized data.

Q23). What is a data warehouse?

A data warehouse is a large store of data collected from various sources, optimized for analysis and reporting.

For example: a retail company might have a data warehouse that consolidates sales data from different stores to analyze overall performance.

Q24). What is OLAP?

OLAP (Online Analytical Processing) is used for complex querying and analysis of data.

For example: OLAP can help a business analyze sales trends over different periods and across various regions to make informed decisions.

Q25). What is OLTP?

OLTP (Online Transaction Processing) is used for managing day-to-day operations with a focus on transaction speed and efficiency.

For example: processing online orders or updating inventory levels in real-time are typical OLTP operations.

Q26). What is sharding?

Sharding is a method of splitting a database into smaller, more manageable pieces called shards.

For example: an e-commerce website might shard its database by user location to distribute the load and improve performance.

Q27). What is CAP theorem?

CAP theorem states that a distributed system can only achieve two out of three properties: Consistency, Availability, and Partition Tolerance.

For example: in a distributed database system, you can prioritize consistency and availability but might have to compromise on partition tolerance during network failures.

Q28). What is a NoSQL database?

NoSQL databases are non-relational databases designed for handling large volumes of data and various data types.

For example: MongoDB is a NoSQL database that stores data in flexible, JSON-like documents, making it suitable for applications with diverse data requirements.

Q29). What is a document store?

A document store is a type of NoSQL database that stores data in document formats, such as JSON or BSON.

For example: MongoDB is a document store where each record is a document that can contain a wide variety of data structures and nested information.

Q30). What is a key-value store?

A key-value store is a type of NoSQL database that stores data as key-value pairs.

For example: Redis is a key-value store where you can quickly retrieve data based on a unique key, similar to looking up an entry in a dictionary by its word.

Q31). What is a column-family store?

A column-family store is a NoSQL database that stores data in columns rather than rows.

For example: Apache Cassandra is a column-family store where data is organized in columns and column families, making it suitable for handling large amounts of data across distributed systems.

Q32). What is a materialized view?

A materialized view stores the results of a query physically, which can speed up query performance.

For example: if you frequently query a sales report that aggregates data from multiple tables, a materialized view can store this pre-computed result to make future queries faster.

Q33). What is a schema migration?

Schema migration involves making changes to the database schema to evolve its structure over time.

For example: if you need to add a new column to an existing table to store additional information, you would perform a schema migration to update the database structure.

Q34). What is a database replica?

A database replica is a copy of a database used for backup, load balancing, or failover.

For example: if you have a main database handling user data and a replica for backup, you can switch to the replica if the main database fails, ensuring data availability and reliability.

Q35). What is eventual consistency?

Eventual consistency is a model where data changes are propagated to all nodes eventually, but not immediately.

For example: in a distributed social media platform, a post might appear on your feed with a slight delay as it updates across different servers.

Q36). What is database caching?

Database caching involves storing frequently accessed data in memory to speed up access times.

For example: a website might cache user profile data so that it loads quickly when accessed multiple times, rather than retrieving it from the database each time.