Java Databases Interview Questions

ACID ensures reliable transactions: Atomicity (all-or-nothing), Consistency (valid state), Isolation (no interference), Durability (persistence after commit).

Database normalization organizes data into tables to reduce redundancy and dependency by applying rules like 1NF, 2NF, 3NF, ensuring data integrity and efficient queries.

SQL is relational (structured tables, ACID properties, complex joins), while NoSQL is non-relational (document/key-value stores, eventual consistency, horizontal scaling).

Indexes are database structures (typically B-tree based) that speed up data retrieval by creating sorted pointers to rows, trading write performance and storage for faster reads.

INNER JOIN returns only matching rows from both tables where join condition is true. LEFT JOIN returns all rows from left table plus matching rows from right table, filling nulls for non-matching right records.

SQL has at least 6 normal forms: 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies), BCNF (stricter 3NF), 4NF (multi-valued dependencies), 5NF (join dependencies). Most databases use up to 3NF.

Partitioning splits large tables horizontally into smaller chunks (range, list, hash, composite) to improve query performance and manage data distribution across storage.

CHAR is fixed-length (padded with spaces), VARCHAR is variable-length; CHAR is faster for exact-size data, VARCHAR saves storage for variable-sized strings.

INNER JOIN returns only matching rows from both tables; LEFT JOIN returns all rows from left table plus matching rows from right; RIGHT JOIN returns all rows from right table plus matching rows from left.

Foreign keys are database constraints that establish relationships between tables; a column references primary key in another table, enforcing referential integrity.

Index disadvantages include increased storage overhead, slower write/update/delete operations due to index maintenance, and potential performance degradation if indexes aren't properly optimized or are over-indexed.

Primary keys uniquely identify records with NOT NULL and UNIQUE constraints; foreign keys reference primary keys in other tables establishing relationships, enforcing referential integrity and preventing orphaned records.

Normalization reduces redundancy and improves data integrity by organizing data into normalized forms; denormalization intentionally introduces redundancy for query performance, trading storage for faster reads in read-heavy systems.

A foreign key enforces referential integrity by ensuring values in one table's column match values in another table's primary key, preventing invalid relationships and maintaining data consistency across related tables.

JPQL (Java Persistence Query Language) is database-agnostic and uses entity names/properties, while HQL (Hibernate Query Language) is Hibernate-specific and can reference database tables directly; both support similar syntax but differ in portability.

The relational data model organizes data into tables (relations) with rows (tuples) and columns (attributes), using SQL as the standard query language; it's based on set theory with enforced ACID properties.

Transaction anomalies include dirty reads (uncommitted data), non-repeatable reads (data changing between reads), and phantom reads (new rows appearing); they occur with insufficient isolation levels between concurrent transactions.

Database replication is copying data across multiple database instances for redundancy, failover capability, and load balancing; can be synchronous (immediate) or asynchronous (delayed).

Data persistence typically uses ORMs (Hibernate, JPA), direct JDBC connections, or document stores depending on requirements; connection pooling and caching strategies optimize performance.

Use database transactions with COMMIT/ROLLBACK capability; the client sends a message, server processes it within a transaction, and if cancellation is needed, ROLLBACK discards changes before COMMIT is called.

Implement idempotent operations where duplicate requests produce the same result; use unique identifiers (UUIDs) or checksums to detect duplicates, and design endpoints to handle re-submission safely.

Name the databases you have hands-on experience with and explain why you chose each for its use case. Common answers: PostgreSQL or MySQL for relational data, MongoDB for document storage, Redis for caching, Elasticsearch for search. Mention any experience with database design, query optimization, or migrations.

Key constraints include PRIMARY KEY (unique, not null), FOREIGN KEY (referential integrity), UNIQUE (distinct values), NOT NULL (mandatory values), and CHECK (value validation) constraining data at the database level.

@Transactional annotation marks methods/classes for Spring-managed transactions, automatically handling COMMIT/ROLLBACK; configure on service layer methods to ensure data consistency across database operations.

Databases provide structured querying (SQL), ACID compliance, concurrent access control, indexing for performance, backup/recovery mechanisms, and data integrity constraints; text files lack these features making them unsuitable for complex data management.

Indexing creates data structures (B-trees, hash indexes) on table columns to speed up query lookups, reducing full table scans from O(n) to O(log n).

HTTP requests: GET (retrieve), POST (create), PUT (update), DELETE (remove), PATCH (partial update), HEAD, OPTIONS.

DDL (Data Definition Language) defines schema: CREATE, ALTER, DROP. DML (Data Manipulation Language) modifies data: INSERT, UPDATE, DELETE, SELECT.

NOT NULL (column must have value), PRIMARY KEY (unique identifier), FOREIGN KEY (references another table), UNIQUE (no duplicates), CHECK (condition validation).

2NF requires removing partial dependencies: every non-key attribute must depend on the entire primary key, not just part of it.

Code shape refers to normalization forms (1NF, 2NF, 3NF, BCNF) that eliminate data anomalies and redundancy through structured database design.

COUNT(*) for row count, SUM() for totals, AVG() for averages, MAX()/MIN() for extremes, GROUP_CONCAT() for concatenation in aggregation queries.

WHERE clause is evaluated before GROUP BY, and GROUP BY/HAVING before JOIN to filter individual rows, then aggregates are computed.

Add restrictions using WHERE clause for row-level filtering, HAVING for aggregate filtering, and CHECK constraints in table definition.

Constraints specified via CREATE TABLE (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT, NOT NULL) or ALTER TABLE ADD CONSTRAINT.

Venn diagrams visually represent set operations: INNER JOIN (intersection), LEFT/RIGHT JOIN (one set with overlap), FULL OUTER JOIN (union of sets).

Add database restrictions via DDL constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) or application-level validation before INSERT/UPDATE.

Join combines rows from multiple tables based on related columns; INNER JOIN returns matching rows, OUTER JOINs include unmatched rows.

EXISTS checks if a subquery returns any rows (true/false), used in WHERE clause to filter results based on correlated subquery existence.

2NF eliminates partial dependencies on composite keys. BCNF (Boyce-Codd Normal Form) ensures every determinant is a candidate key, stricter than 3NF.

Denormalization intentionally adds redundant data to improve query performance by reducing joins, trading storage for speed.

Criteria API is a type-safe, programmatic way to build JPA queries using objects instead of strings, enabling compile-time checking.

HQL (Hibernate Query Language) is an object-oriented query language similar to SQL but operates on mapped entities instead of database tables.

SQL injection is a code injection attack where malicious SQL statements are inserted into input fields to manipulate database queries and unauthorized access or data manipulation.

NoSQL databases include document stores (MongoDB), key-value stores (Redis), column-family stores (HBase), and graph databases (Neo4j), designed for unstructured data and horizontal scaling.

Charming refers to database sharding or partitioning, where large datasets are distributed across multiple databases or servers based on a shard key for improved performance and scalability.

Transaction properties (ACID) are: Atomicity (all-or-nothing), Consistency (valid state), Isolation (concurrent independence), and Durability (persistence after commit).

Full table scans are more efficient than index access when querying a large percentage of rows (typically >20-30%), especially on small tables or when results require sequential I/O.

Indexing columns with few distinct values (low cardinality) has limited benefit since the index may still require scanning many rows per value, making full table scans often preferable.

Clustered indexes determine the physical order of table rows (one per table, usually the primary key), while non-clustered indexes create separate lookup structures pointing to row locations.

Index types include primary key indexes, unique indexes, composite indexes, full-text indexes, spatial indexes, and bitmap indexes depending on database system and use case.

Database connection types include one-to-one (user to account), one-to-many (department to employees), many-to-many (students to courses), and self-referencing (employee to manager).

Alternate key is a candidate key not chosen as primary key but still uniquely identifies rows, maintaining uniqueness constraints as a backup unique identifier.

Candidate key is a column or column set that could qualify as a primary key by uniquely identifying each row with no null values.

Composite key is a primary or unique key made up of multiple columns that together uniquely identify a row.

Simple key is a primary or unique key consisting of a single column that uniquely identifies each row.