1. What is a DBMS?
A DBMS (Database Management System) is software that manages databases, enabling storage, retrieval, and manipulation of data. It ensures data integrity, security, and efficient access.
2. What are the types of DBMS?
DBMS types include Hierarchical, Network, Relational, Object-Oriented, and NoSQL. Relational DBMS is most common, using tables to store data.
3. What is a relational database?
A relational database stores data in tables with rows and columns, linked by keys. It uses SQL for querying and ensures data consistency.
4. What is SQL?
SQL (Structured Query Language) is a language for managing relational databases. It supports querying, updating, and schema creation.
5. What is the difference between SQL and NoSQL?
SQL databases are relational, structured, and use tables, while NoSQL databases are non-relational, flexible, and handle unstructured data like documents or graphs.
6. What is a primary key?
A primary key is a unique column in a table that identifies each row. It ensures no duplicate or null values.
7. What is a foreign key?
A foreign key is a column that links two tables by referencing the primary key of another table. It enforces referential integrity.
8. What is a composite key?
A composite key is a combination of two or more columns that uniquely identify a row. It’s used when a single column isn’t unique.
9. What is normalization?
Normalization organizes data to eliminate redundancy and ensure data integrity. It divides tables into smaller, related units following normal forms.
10. What is the first normal form (1NF)?
1NF requires that each column contains atomic (indivisible) values and each row is unique. It eliminates repeating groups.
11. What is the second normal form (2NF)?
2NF builds on 1NF, ensuring no partial dependency. All non-key attributes must depend on the entire primary key.
12. What is the third normal form (3NF)?
3NF ensures no transitive dependency, meaning non-key attributes depend only on the primary key, not on other non-key attributes.
13. What is denormalization?
Denormalization combines tables to improve query performance, often at the cost of redundancy. It’s used in data warehouses for faster reads.
14. What is a database schema?
A schema defines the structure of a database, including tables, columns, and relationships. It acts as a blueprint for data organization.
15. What is a table in SQL?
A table is a collection of rows and columns storing related data. Each column has a specific data type.
16. What is a view in SQL?
A view is a virtual table created from a query’s result set. It simplifies complex queries and enhances security.
17. What is an index in DBMS?
An index is a data structure that speeds up data retrieval on a table. It’s created on columns for faster searches.
18. What is a clustered index?
A clustered index determines the physical order of data in a table. Each table can have only one clustered index.
19. What is a non-clustered index?
A non-clustered index stores a separate structure with pointers to the data. A table can have multiple non-clustered indexes.
20. What is the difference between a unique key and a primary key?
A primary key ensures uniqueness and no nulls, while a unique key allows one null value but still enforces uniqueness.
21. What is a candidate key?
A candidate key is a column or set of columns that can uniquely identify a row. One is chosen as the primary key.
22. What is a super key?
A super key is any set of columns that uniquely identifies a row. It may include extra columns beyond the minimal key.
23. What is the purpose of the SELECT statement in SQL?
The SELECT statement retrieves data from a database table. It specifies columns and conditions to filter results.
24. What is the WHERE clause used for?
The WHERE clause filters rows in a query based on specified conditions. It’s used with SELECT, UPDATE, or DELETE.
25. What is the GROUP BY clause?
GROUP BY groups rows with the same values in specified columns into summary rows. It’s often used with aggregate functions.
26. What is the HAVING clause?
HAVING filters grouped rows based on conditions, similar to WHERE. It’s used with GROUP BY for aggregate functions.
27. What is the ORDER BY clause?
ORDER BY sorts query results in ascending (ASC) or descending (DESC) order based on specified columns.
28. What is a JOIN in SQL?
A JOIN combines rows from two or more tables based on a related column. Types include INNER, LEFT, RIGHT, and FULL.
29. What is an INNER JOIN?
INNER JOIN returns only the rows where there’s a match in both tables. It’s the most common join type.
30. What is a LEFT JOIN?
LEFT JOIN returns all rows from the left table and matched rows from the right table. Unmatched rows return NULL.
31. What is a RIGHT JOIN?
RIGHT JOIN returns all rows from the right table and matched rows from the left table. Unmatched rows return NULL.
32. What is a FULL JOIN?
FULL JOIN returns all rows from both tables, with NULLs in places where there’s no match. It combines LEFT and RIGHT JOIN.
33. What is a self-join?
A self-join joins a table with itself to compare rows within the same table. It uses aliases to differentiate columns.
34. What is a cross join?
A cross join produces a Cartesian product, combining every row of one table with every row of another. It’s rarely used.
35. What is a subquery in SQL?
A subquery is a query nested inside another query, often used in WHERE or SELECT clauses. It returns data for the outer query.
36. What is a correlated subquery?
A correlated subquery depends on the outer query’s values, executing repeatedly for each row of the outer query.
37. What is the difference between a subquery and a JOIN?
A subquery nests a query within another, while a JOIN combines tables directly. JOINs are often more efficient for large datasets.
38. What is the purpose of the INSERT statement?
The INSERT statement adds new rows to a table. It specifies the table, columns, and values to be inserted.
39. What is the UPDATE statement used for?
The UPDATE statement modifies existing rows in a table. It uses SET to change column values and WHERE to specify rows.
40. What is the DELETE statement?
The DELETE statement removes rows from a table based on a condition specified in the WHERE clause.
41. What is the TRUNCATE statement?
TRUNCATE removes all rows from a table without logging individual deletions. It’s faster than DELETE but can’t be rolled back.
42. What is the DROP statement?
DROP removes an entire table or database object from the database. It’s irreversible and deletes structure and data.
43. What is the ALTER statement used for?
ALTER modifies a table’s structure, such as adding, dropping, or changing columns. It updates the schema without affecting data.
44. What is a transaction in DBMS?
A transaction is a sequence of operations treated as a single unit. It ensures data consistency with properties like ACID.
45. What does ACID stand for in DBMS?
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable database transactions.
46. What is atomicity in a transaction?
Atomicity ensures all operations in a transaction complete successfully or none are applied. It prevents partial updates.
47. What is consistency in a transaction?
Consistency ensures a transaction brings the database from one valid state to another, maintaining all constraints and rules.
48. What is isolation in a transaction?
Isolation ensures transactions are executed independently. Partial changes of one transaction aren’t visible to others until complete.
49. What is durability in a transaction?
Durability guarantees that committed transactions are permanently saved, even in case of system failure.
50. What is a deadlock in DBMS?
A deadlock occurs when two or more transactions wait indefinitely for each other to release resources. DBMS detects and resolves deadlocks.
51. What is a lock in DBMS?
A lock restricts access to data during a transaction to ensure consistency. Types include shared and exclusive locks.
52. What is a shared lock?
A shared lock allows multiple transactions to read data simultaneously but prevents writing. It’s used for read-only operations.
53. What is an exclusive lock?
An exclusive lock prevents other transactions from reading or writing data. It’s used for write operations to ensure data integrity.
54. What is two-phase locking?
Two-phase locking ensures serializability with a growing phase (acquiring locks) and a shrinking phase (releasing locks).
55. What is a trigger in SQL?
A trigger is a stored procedure that automatically executes in response to events like INSERT, UPDATE, or DELETE on a table.
56. What is a stored procedure?
A stored procedure is a precompiled set of SQL statements stored in the database. It can be called with parameters for reuse.
57. What is a function in SQL?
A function is a database object that returns a single value based on input parameters. Unlike procedures, it’s used in queries.
58. What is the difference between a stored procedure and a function?
A stored procedure performs actions and may not return a value, while a function always returns a single value and is used in expressions.
59. What is a cursor in SQL?
A cursor allows row-by-row processing of a query’s result set. It’s used when iterative processing is needed.
60. What are the types of cursors?
Cursors can be implicit (automatically created by DBMS) or explicit (user-defined). They can also be static or dynamic.
61. What is a database constraint?
A constraint enforces rules on data, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK, ensuring data integrity.
62. What is the NOT NULL constraint?
NOT NULL ensures a column cannot have null values. It enforces mandatory data entry for that column.
63. What is the CHECK constraint?
CHECK ensures that values in a column meet a specific condition, like age > 18, before being inserted or updated.
64. What is a default constraint?
A default constraint assigns a default value to a column if no value is provided during insertion, like setting status to ‘Active’.
65. What is a database trigger’s advantage?
Triggers automate tasks like logging or updating related tables. They ensure consistency without manual intervention.
66. What is a disadvantage of triggers?
Triggers can complicate debugging and reduce performance if overused, as they execute automatically and may hide logic.
67. What is a clustered table?
A clustered table is organized based on a clustered index, determining the physical order of data for efficient retrieval.
68. What is a heap table?
A heap table stores data without a clustered index, leading to unordered storage. It’s less efficient for searches.
69. What is a temporary table?
A temporary table stores data for a session or transaction. It’s automatically dropped when the session ends.
70. What is a global temporary table?
A global temporary table is accessible to all sessions but retains data only for the creating session. It’s prefixed with ##.
71. What is a local temporary table?
A local temporary table is visible only to the creating session and dropped when the session ends. It’s prefixed with #.
72. What is the difference between DROP and TRUNCATE?
DROP deletes the table and its structure, while TRUNCATE removes all rows but keeps the structure intact.
73. What is a database backup?
A database backup is a copy of the database used for recovery in case of data loss or corruption.
74. What are the types of database backups?
Backups include full (complete data), differential (changes since last full), and transaction log (records of transactions).
75. What is a database recovery model?
A recovery model defines how transactions are logged and backed up. Types include Simple, Full, and Bulk-Logged.
76. What is the Simple recovery model?
Simple recovery model minimizes logging, auto-truncates logs, and supports only full or differential backups, not point-in-time recovery.
77. What is the Full recovery model?
Full recovery model logs all transactions, enabling point-in-time recovery with full, differential, and log backups.
78. What is a database transaction log?
A transaction log records all database operations, enabling recovery and rollback in case of failures or errors.
79. What is a checkpoint in DBMS?
A checkpoint writes all modified data to disk, ensuring consistency. It reduces recovery time after a crash.
80. What is data integrity?
Data integrity ensures accuracy, consistency, and reliability of data through constraints, transactions, and validation rules.
81. What is referential integrity?
Referential integrity ensures foreign key values match primary key values in related tables, maintaining consistent relationships.
82. What is a database role?
A database role is a set of permissions assigned to users or groups to control access to database objects and operations.
83. What is the difference between DML and DDL?
DML (Data Manipulation Language) manages data (e.g., SELECT, INSERT), while DDL (Data Definition Language) defines structures (e.g., CREATE, ALTER).
84. What is a TCL command?
TCL (Transaction Control Language) commands manage transactions, like COMMIT (save), ROLLBACK (undo), and SAVEPOINT (set recovery point).
85. What is a DCL command?
DCL (Data Control Language) commands manage access and permissions, like GRANT (give access) and REVOKE (remove access).
86. What is a database user?
A database user is an account with specific permissions to access and manipulate database objects, often linked to roles.
87. What is a database privilege?
A privilege is a permission granted to a user or role to perform actions like SELECT, INSERT, or EXECUTE on database objects.
88. What is the GRANT command?
GRANT assigns permissions to users or roles, allowing them to perform specific actions like SELECT or UPDATE on tables.
89. What is the REVOKE command?
REVOKE removes previously granted permissions from users or roles, restricting their access to database objects.
90. What is a database synonym?
A synonym is an alias for a database object, simplifying access by providing an alternative name, often used across schemas.
91. What is a sequence in SQL?
A sequence generates unique numeric values, often used for primary keys. It’s created with CREATE SEQUENCE and accessed via NEXTVAL.
92. What is a database partition?
A partition divides a large table into smaller, manageable pieces based on ranges or lists, improving performance and maintenance.
93. What is range partitioning?
Range partitioning divides a table based on a range of values, like dates or numbers, for efficient data management.
94. What is list partitioning?
List partitioning divides a table based on discrete values, like regions or categories, grouping rows by specified lists.
95. What is hash partitioning?
Hash partitioning distributes rows across partitions using a hash function, balancing data evenly for load distribution.
96. What is a materialized view?
A materialized view stores the result of a query physically, unlike a regular view, and can be refreshed periodically.
97. What is the difference between a view and a materialized view?
A view is virtual and doesn’t store data, while a materialized view stores query results physically and needs refreshing.
98. What is a database schema owner?
A schema owner is a user who creates and controls a schema, managing its objects and permissions within the database.
99. What is a database connection?
A database connection is a communication link between an application and the DBMS, enabling data access and queries.
100. What is connection pooling?
Connection pooling reuses database connections to improve performance, reducing the overhead of creating new connections.
101. What is a database driver?
A database driver is software that enables applications to communicate with a DBMS, implementing specific protocols like JDBC or ODBC.
102. What is JDBC?
JDBC (Java Database Connectivity) is an API for connecting Java applications to databases, executing SQL queries, and retrieving results.
103. What is ODBC?
ODBC (Open Database Connectivity) is a standard API for accessing databases, allowing applications to work with various DBMS.
104. What is a database query optimizer?
A query optimizer evaluates and selects the most efficient execution plan for a SQL query, minimizing resource usage.
105. What is an execution plan?
An execution plan outlines the steps a DBMS takes to execute a query, including joins, indexes, and data retrieval methods.
106. What is a cost-based optimizer?
A cost-based optimizer chooses the query execution plan with the lowest estimated cost, based on statistics and resource usage.
107. What is a database statistic?
Database statistics provide metadata about data distribution, like row counts or index usage, used by the optimizer for efficient plans.
108. What is a histogram in database statistics?
A histogram shows the distribution of data values in a column, helping the optimizer estimate query selectivity and plan efficiency.
109. What is a database buffer?
A buffer is a memory area that temporarily holds data being read or written, reducing disk I/O and improving performance.
110. What is a database cache?
A cache stores frequently accessed data in memory to speed up retrieval, reducing the need for repeated disk access.
111. What is a rollback segment?
A rollback segment stores old data during transactions, enabling ROLLBACK to undo changes if a transaction fails.
112. What is a database snapshot?
A snapshot captures the state of a database at a specific point, used for reporting or recovery without affecting the live database.
113. What is database replication?
Replication copies and maintains database data across multiple servers, ensuring availability, fault tolerance, and load balancing.
114. What is master-slave replication?
Master-slave replication involves a master database handling writes, with slaves copying data for reads, improving scalability.
115. What is multi-master replication?
Multi-master replication allows multiple databases to handle writes, synchronizing changes across all masters, but it’s complex to manage.
116. What is database sharding?
Sharding splits a database into smaller, independent pieces (shards) distributed across servers, improving scalability and performance.
117. What is the difference between sharding and partitioning?
Sharding distributes data across multiple databases, while partitioning splits a single table within a database for better management.
118. What is a distributed database?
A distributed database stores data across multiple physical locations, managed as a single system, improving scalability and availability.
119. What is CAP theorem in DBMS?
CAP theorem states a distributed database can only guarantee two of three properties: Consistency, Availability, and Partition Tolerance.
120. What is eventual consistency?
Eventual consistency ensures that, given enough time, all replicas in a distributed database will converge to the same state.
121. What is a NoSQL database?
A NoSQL database handles unstructured or semi-structured data, offering flexibility and scalability for non-relational data models.
122. What are the types of NoSQL databases?
NoSQL databases include Key-Value, Document, Column-Family, and Graph databases, each suited for specific data needs.
123. What is a key-value store?
A key-value store is a NoSQL database that stores data as key-value pairs, offering fast retrieval for simple queries.
124. What is a document store?
A document store is a NoSQL database that stores semi-structured data as JSON or BSON documents, supporting flexible schemas.
125. What is a column-family store?
A column-family store organizes data in columns instead of rows, optimized for large-scale analytics and big data.
126. What is a graph database?
A graph database stores data as nodes and edges, optimized for relationships and complex queries, like social networks.
127. What is MongoDB?
MongoDB is a NoSQL document store that uses JSON-like documents, supporting flexible schemas and horizontal scaling.
128. What is Cassandra?
Cassandra is a NoSQL column-family store designed for high availability and scalability, handling large-scale distributed data.
129. What is Redis?
Redis is an in-memory key-value store used as a cache or message broker, known for high performance and low latency.
130. What is the difference between RDBMS and NoSQL?
RDBMS uses structured tables and SQL, while NoSQL supports unstructured data, flexible schemas, and scales horizontally.
131. What is a database transaction isolation level?
Isolation levels define how transactions are separated, controlling visibility of changes. Examples include Read Uncommitted and Serializable.
132. What is the Read Uncommitted isolation level?
Read Uncommitted allows transactions to read uncommitted changes, risking dirty reads but offering high performance.
133. What is the Read Committed isolation level?
Read Committed ensures transactions only read committed data, preventing dirty reads but allowing non-repeatable reads.
134. What is the Repeatable Read isolation level?
Repeatable Read ensures consistent reads within a transaction, preventing non-repeatable reads but allowing phantom reads.
135. What is the Serializable isolation level?
Serializable ensures complete isolation, preventing all concurrency issues like phantom reads, but it reduces performance.
136. What is a dirty read?
A dirty read occurs when a transaction reads uncommitted changes from another transaction, which may later be rolled back.
137. What is a non-repeatable read?
A non-repeatable read occurs when a transaction reads the same row twice but gets different values due to another transaction’s update.
138. What is a phantom read?
139. A phantom read occurs when a transaction re-executes a query and finds new rows inserted by another transaction.
140. What is a database index’s disadvantage?
Indexes slow down write operations (INSERT, UPDATE, DELETE) and require additional storage, impacting performance and space.
141. What is a covering index?
A covering index includes all columns needed for a query, allowing the DBMS to retrieve data without accessing the table.
142. What is a composite index?
A composite index is created on multiple columns, improving queries that filter or sort on those columns together.
143. What is a full-text index?
A full-text index enables efficient searching of text data, supporting complex queries like keyword searches or relevance ranking.
144. What is a bitmap index?
A bitmap index uses bit arrays to represent column values, efficient for low-cardinality columns in data warehouses.
145. What is a B-tree index?
A B-tree index organizes data in a balanced tree structure, supporting range queries and efficient searches in relational databases.
146. What is a hash index?
A hash index uses a hash function to map keys to data locations, ideal for equality searches but not range queries.
147. What is a database view’s limitation?
Views cannot store data physically and may have performance issues with complex queries. Some cannot be updated directly.
148. What is a recursive query in SQL?
A recursive query uses a Common Table Expression (CTE) to repeatedly process hierarchical or tree-structured data until a condition is met.
149. What is a Common Table Expression (CTE)?
A CTE is a temporary result set defined within a query, improving readability and supporting recursive or complex queries.
150. What is a window function in SQL?
A window function performs calculations across a set of rows related to the current row, like RANK or ROW_NUMBER, without grouping.
151. What is the RANK function?
RANK assigns a rank to each row within a partition, with ties receiving the same rank and gaps in subsequent ranks.
152. What is the DENSE_RANK function?
DENSE_RANK assigns ranks like RANK but without gaps after ties, ensuring consecutive rank values.
153. What is the ROW_NUMBER function?
ROW_NUMBER assigns a unique sequential number to each row within a partition, regardless of ties.
154. What is the PARTITION BY clause in window functions?
PARTITION BY divides rows into groups for window function calculations, similar to GROUP BY but without collapsing rows.
155. What is the OVER clause in SQL?
The OVER clause defines the window or set of rows for a window function, specifying partitioning and ordering.
156. What is a pivot operation in SQL?
A pivot operation transforms rows into columns, aggregating data to create a cross-tabular report from a table.
157. What is an unpivot operation?
Unpivot converts columns into rows, reversing a pivot operation to normalize data for analysis or querying.
158. What is a database audit?
A database audit tracks user actions, like queries or modifications, to ensure security, compliance, and monitoring of database activity.
159. What is a database trigger’s use case?
Triggers are used for automatic logging, enforcing business rules, or updating related tables, like updating stock after a sale.
160. What is a database synonym’s benefit?
Synonyms simplify queries by providing shorter or more intuitive names for database objects, improving usability across schemas.
161. What is a database link?
A database link enables queries to access data in a remote database, treated as a single database for cross-server operations.
162. What is a database cluster?
A database cluster is a group of servers running a single database instance, providing high availability and load balancing.
163. What is a database failover?
Failover automatically switches to a standby server if the primary server fails, ensuring continuous database availability.
164. What is a database mirror?
A database mirror is a real-time copy of a database used for high availability, allowing failover if the primary fails.
165. What is a database log shipping?
Log shipping copies transaction logs to a secondary server, enabling disaster recovery or warm standby for databases.
166. What is a database snapshot isolation?
Snapshot isolation provides a consistent view of data at transaction start, avoiding conflicts but risking write skew.
167. What is a database maintenance plan?
A maintenance plan schedules tasks like backups, index rebuilding, and statistics updates to ensure database performance and reliability.
168. What is index fragmentation?
Index fragmentation occurs when index pages become disordered, slowing queries. It’s fixed by rebuilding or reorganizing indexes.
169. What is the difference between REBUILD and REORGANIZE index?
REBUILD recreates the index, removing fragmentation but using more resources, while REORGANIZE compacts pages with less overhead.
170. What is a database parameter?
A database parameter is a configuration setting, like memory allocation or connection limits, controlling DBMS behavior.
171. What is a database instance?
A database instance is a set of memory structures and processes managing one or more databases in a DBMS.
172. What is a database filegroup?
A filegroup is a logical container for database files, allowing data to be spread across multiple disks for performance.
173. What is a database collation?
Collation defines rules for sorting and comparing strings, like case sensitivity, affecting query results and indexing.
174. What is a case-sensitive collation?
A case-sensitive collation treats uppercase and lowercase letters as distinct, impacting searches and sorting in queries.
175. What is a database role’s fixed role?
A fixed database role has predefined permissions, like db_owner or db_datareader, simplifying user management.
176. What is a database schema’s default schema?
A default schema is assigned to a user, so unqualified object names resolve to that schema, simplifying queries.
177. What is a database deadlock’s resolution?
DBMS detects deadlocks and terminates one transaction, freeing resources. Proper indexing and transaction design prevent deadlocks.
178. What is a database performance tuning?
Performance tuning optimizes queries, indexes, and configurations to reduce response time and improve database efficiency.
179. What is a query hint in SQL?
A query hint overrides the optimizer’s default behavior, forcing specific execution plans or join types for a query.
180. What is a database profiler?
A profiler monitors database activity, capturing queries and performance metrics to identify bottlenecks or inefficiencies.
181. What is a database trace?
A trace records detailed database events, like query execution or errors, for performance analysis or troubleshooting.
182. What is a database encryption?
Encryption protects data by converting it into an unreadable format, ensuring security at rest or during transmission.
183. What is Transparent Data Encryption (TDE)?
TDE encrypts database files at the storage level, protecting data at rest without requiring application changes.
184. What is a database key management?
Key management handles encryption keys, ensuring secure storage, rotation, and access for database encryption processes.
185. What is a database audit trail?
An audit trail logs all database activities, like logins or data changes, for security monitoring and compliance.
186. What is a database role-based access control?
Role-based access control assigns permissions to roles, not individual users, simplifying security management and scalability.
187. What is a database connection timeout?
A connection timeout is the maximum time an application waits to establish a database connection before failing.
188. What is a database session?
A session is an active connection between a user or application and the database, managing queries and transactions.
189. What is a database connection string?
A connection string contains details like server, database, and credentials to establish a connection to a DBMS.
190. What is a database load balancing?
Load balancing distributes database queries across multiple servers to optimize performance and prevent overload.
191. What is a database failover cluster?
A failover cluster uses multiple servers to ensure continuous operation, switching to a standby if the primary fails.
192. What is a database high availability?
High availability ensures minimal downtime through redundancy, replication, or failover, keeping the database accessible.
193. What is a database disaster recovery?
Disaster recovery restores a database after major failures using backups, replication, or failover to minimize data loss.
194. What is a database consistency check?
A consistency check verifies database integrity, detecting corruption in data, indexes, or structures, often via DBCC commands.
195. What is a database compression?
Compression reduces database storage size, improving I/O performance but increasing CPU usage during read/write operations.
196. What is row-level compression?
Row-level compression minimizes data size by optimizing storage for each row, reducing space without altering table structure.
197. What is page-level compression?
Page-level compression compresses entire data pages, combining row compression with techniques like prefix compression for efficiency.
198. What is a database migration?
Migration moves a database to a new platform, version, or environment, ensuring data integrity and minimal downtime.
199. What is a database schema migration?
Schema migration updates the database structure, like adding tables or columns, often using tools like Flyway or Liquibase.
200. What is a database ETL process?
ETL (Extract, Transform, Load) extracts data from sources, transforms it for consistency, and loads it into a target database.
201. What is a data warehouse?
A data warehouse is a centralized database optimized for analytical queries, storing historical data for reporting and analysis.
202. What is the difference between a database and a data warehouse?
A database supports transactional processing (OLTP), while a data warehouse is optimized for analytical processing (OLAP) with historical data.
203. What is OLTP?
OLTP (Online Transaction Processing) handles high-volume, real-time transactions, like banking or e-commerce, with fast, small queries.
204. What is OLAP?
OLAP (Online Analytical Processing) supports complex, analytical queries on large datasets, used in data warehouses for reporting.
205. What is a star schema?
A star schema organizes data in a data warehouse with a central fact table connected to dimension tables, simplifying queries.
206. What is a snowflake schema?
A snowflake schema normalizes dimension tables in a star schema, reducing redundancy but increasing query complexity.
207. What is a fact table?
A fact table stores quantitative data, like sales or metrics, linked to dimension tables in a data warehouse schema.
208. What is a dimension table?
A dimension table stores descriptive attributes, like time or product details, connected to fact tables in a data warehouse.
209. What is a database cube?
A database cube is a multidimensional data structure in OLAP, enabling fast analysis across dimensions like time or region.
210. What is a database rollup?
A rollup aggregates data along a dimension hierarchy, like summing sales from daily to monthly, in OLAP queries.
211. What is a database drill-down?
Drill-down navigates from summarized data to detailed data, like viewing daily sales within a monthly total, in OLAP.
212. What is a database slice and dice?
Slice and dice selects and reorients data subsets in a cube, enabling flexible analysis across dimensions in OLAP.
213. What is a database materialized view refresh?
A materialized view refresh updates its stored data, either fully (rebuilding) or incrementally (adding new data), to stay current.
214. What is a database connection retry?
A connection retry attempts to reconnect to a database after a failure, often configured with delays to handle temporary issues.
215. What is a database connection failover?
Connection failover redirects a connection to a standby server if the primary fails, ensuring continuous application access.
216. What is a database read replica?
A read replica is a copy of the primary database used for read-only queries, improving performance and load distribution.
217. What is a database write-ahead log?
A write-ahead log records changes before they’re applied to the database, ensuring durability and recovery after crashes.
218. What is a database hot backup?
A hot backup occurs while the database is running, capturing data without downtime, suitable for high-availability systems.
219. What is a database cold backup?
A cold backup is taken when the database is offline, ensuring a consistent copy but requiring downtime.
220. What is a database incremental backup?
An incremental backup captures only changes since the last backup, reducing time and storage compared to full backups.
221. What is a database differential backup?
A differential backup captures changes since the last full backup, requiring more storage than incremental but simpler recovery.
222. What is a database point-in-time recovery?
Point-in-time recovery restores a database to a specific moment using transaction logs, minimizing data loss after failures.
223. What is a database archive log?
An archive log stores transaction logs for recovery, enabling point-in-time or full recovery in case of data loss.
224. What is a database flashback?
Flashback restores a database to a previous state using undo data or logs, useful for recovering from user errors.
225. What is a database data dictionary?
A data dictionary stores metadata about database objects, like tables, columns, and permissions, used by the DBMS for operations.
226. What is a database system catalog?
A system catalog is a set of tables storing metadata about the database, like schemas and constraints, accessed by the DBMS.
227. What is a database metadata?
Metadata describes the database’s structure, like table definitions, indexes, and relationships, stored in the system catalog.
228. What is a database anomaly?
A database anomaly is an inconsistency caused by redundant data, like insertion, deletion, or update issues, fixed by normalization.
229. What is an insertion anomaly?
An insertion anomaly occurs when data cannot be added without unrelated data, often due to poor table design.
230. What is a deletion anomaly?
A deletion anomaly occurs when deleting data removes unintended related data, caused by improper table relationships.
231. What is an update anomaly?
An update anomaly occurs when updating data leads to inconsistencies, often due to redundant data in unnormalized tables.
232. What is a database concurrency control?
Concurrency control manages simultaneous transactions to prevent conflicts, using techniques like locking or timestamp ordering.
233. What is optimistic concurrency control?
Optimistic concurrency assumes low conflicts, checking for changes before committing a transaction, suitable for read-heavy systems.
234. What is pessimistic concurrency control?
Pessimistic concurrency locks resources during transactions to prevent conflicts, ideal for write-heavy systems but reducing concurrency.
235. What is a database timestamp ordering?
Timestamp ordering assigns unique timestamps to transactions, ensuring they execute in order to maintain consistency without locks.
236. What is a database multiversion concurrency control?
MVCC maintains multiple versions of data, allowing reads to access a snapshot while writes create new versions, improving concurrency.
237. What is a database serializability?
Serializability ensures transactions execute as if in a sequential order, maintaining consistency in concurrent environments.
238. What is a database view’s update restriction?
Views with joins, aggregates, or certain subqueries cannot be updated directly, as they don’t map uniquely to base tables.
239. What is a database role’s privilege inheritance?
Privilege inheritance allows a role to inherit permissions from another role, simplifying access management in complex systems.
240. What is a database connection multiplexing?
Connection multiplexing shares a single database connection among multiple clients, optimizing resource usage in high-traffic systems.
241. What is a database connection heartbeat?
A heartbeat is a periodic signal sent to check a database connection’s health, ensuring it remains active and responsive.
242. What is a database connection load balancing?
Connection load balancing distributes client connections across multiple database servers to optimize performance and resource usage.
Share this protal to share friends and complete unlimited tests here. You can also make friends on our protal also start mutual competition tests with your firends easily.