Java SQL Interview Questions

Yes, proficient with SQL for queries, joins, subqueries, transactions, indexes, and optimization.

Yes, proficient with SQL for DML/DDL operations, optimization, and query design.

INNER JOIN returns matching rows from both tables; LEFT/RIGHT JOIN includes unmatched rows from one side; FULL OUTER JOIN includes all unmatched rows; CROSS JOIN produces Cartesian product.

WHERE filters rows before aggregation; HAVING filters groups after aggregation, typically used with GROUP BY.

A stored procedure is precompiled SQL code stored in the database, executed on server-side for performance, reusability, and reduced network traffic; used for complex business logic.

A temporary table exists only for the current session/transaction and is automatically dropped; used for storing intermediate results, complex transformations, and improving query performance.

A view is a virtual representation of table data with computed or filtered columns; used for security, simplifying queries, and data abstraction.

Temporary tables exist only during a session or transaction, used for intermediate processing, staging data, or avoiding repeated complex calculations.

NULL in SQL represents missing or undefined values; it's not equal to zero or empty string and requires IS NULL or IS NOT NULL operators.

SQL operators include arithmetic (+, -, *, /), comparison (=, <>, <, >), logical (AND, OR, NOT), and aggregate functions (SUM, AVG, COUNT).

A cursor is a database object that retrieves and iterates through query result sets row-by-row, used in stored procedures for procedural processing.

Triggers are database objects that automatically execute SQL statements (stored procedures) in response to events (INSERT, UPDATE, DELETE) on specific tables.

SELECT [DISTINCT] column_list FROM table [WHERE condition] [GROUP BY columns] [HAVING condition] [ORDER BY columns] [LIMIT offset, count].

HAVING filters aggregated result sets post-GROUP BY, while WHERE filters raw rows pre-aggregation.

MERGE (SQL) inserts or updates based on existence, combining INSERT/UPDATE logic in a single statement.

JOIN combines rows from two tables based on a condition: INNER JOIN (matching rows), LEFT/RIGHT/FULL OUTER JOIN (include unmatched rows).

Stored procedures are precompiled SQL routines stored in database; call them via CallableStatement with syntax {call procedure_name(?, ?)} and retrieve results using getObject() or OUT parameters.

Stored procedures are precompiled SQL statements; call them via CallableStatement with {call procedure_name(?, ?)} syntax in JDBC.

SQL restrictions (WHERE clause) filter rows based on conditions; examples include WHERE age > 18, WHERE status = 'active'.

JOIN is preferred for most cases (single query, index optimization); subqueries for complex logic or when JOINing same table multiple times. JOINs are typically faster due to query optimization.

ORDER BY sorts query results by one or more columns in ascending (ASC) or descending (DESC) order.

GROUP BY treats NULL as a distinct group value, so all NULL rows are grouped together separately.

Main aggregate functions: COUNT, SUM, AVG, MIN, MAX, and STDDEV.

COUNT(*) counts all rows including NULLs; COUNT(column) counts only non-NULL values in that column.

IN checks if value exists in a list; BETWEEN checks range inclusively; LIKE performs pattern matching with wildcards.

UNION combines result sets from multiple queries, removing duplicates; UNION ALL keeps duplicates.

Primary Key is unique and non-null (one per table); Unique Key allows one NULL and multiple constraints per table.

Yes, Foreign Key columns can contain NULL values; NULL is treated as 'unknown' and doesn't violate FK constraints.

CREATE INDEX index_name ON table_name(column_name); or CREATE UNIQUE INDEX for uniqueness constraint.

DELETE removes rows and generates rollback logs (slower, reversible); TRUNCATE removes all rows instantly (faster, irreversible).

DATETIME stores date and time (8 bytes, no timezone); TIMESTAMP stores a point in time (4 bytes, timezone-aware in some systems).

PIVOT transforms rows to columns (aggregating data); UNPIVOT transforms columns back to rows.

ROW_NUMBER, RANK, DENSE_RANK, NTILE assign row numbers/ranks; used with OVER clause for partitioning and ordering.

INTERSECT returns rows common to both queries; EXCEPT returns rows from first query not in second.

SQL constraints enforce data integrity: PRIMARY KEY ensures uniqueness, FOREIGN KEY maintains referential integrity, UNIQUE prevents duplicates, NOT NULL requires values, CHECK validates conditions, and DEFAULT provides fallback values.

Stored procedures are precompiled SQL scripts stored in database executed via CALL statement. Call via JDBC using CallableStatement: `CallableStatement cs = conn.prepareCall("{call procName(?)}");` then set parameters and execute.

JOIN combines rows from multiple tables based on related columns: INNER JOIN returns matching rows, LEFT JOIN includes unmatched left rows, RIGHT JOIN includes unmatched right rows, FULL JOIN includes all unmatched rows from both.

MERGE (or UPSERT) inserts or updates rows atomically: if row exists update it, else insert new row. Syntax: `MERGE INTO target USING source ON condition WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT`.

HAVING filters aggregated data after GROUP BY, similar to WHERE but for groups. WHERE filters rows before grouping; HAVING filters grouped results, so HAVING can use aggregate functions like COUNT, SUM.

SELECT syntax: `SELECT [DISTINCT] columns FROM table [JOIN conditions] [WHERE conditions] [GROUP BY columns] [HAVING conditions] [ORDER BY columns] [LIMIT n]`. Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Triggers are database objects that execute automatically when INSERT, UPDATE, or DELETE occurs. They enforce business rules, maintain audit trails, and ensure data consistency at database level.

Cursor is a database object that iterates through result sets row-by-row. Declare cursor with SELECT, open it, fetch rows sequentially, and close it; used mainly in stored procedures since application-level iteration is preferred.

SQL operators include: comparison (=, !=, <, >, <=, >=), logical (AND, OR, NOT), arithmetic (+, -, *, /, %), aggregate (COUNT, SUM, AVG, MIN, MAX), and set operators (UNION, INTERSECT, EXCEPT).

NULL represents missing or unknown data in SQL; it's not equal to zero or empty string and requires IS NULL/IS NOT NULL operators for comparison.

Temporary tables exist only during a session and are used for intermediate data storage, reducing complexity of complex queries and improving performance.