Question 1

Can you explain the difference between OLTP and OLAP?

Accepted Answer

**OLTP**: Optimized for many small transactions (inserts, updates, deletes). Row-oriented, normalized, high concurrency. Examples: MySQL, PostgreSQL. **OLAP**: Optimized for complex analytical queries and aggregations on large datasets. Column-oriented, denormalized (star/snowflake). Examples: Snowflake, BigQuery, Redshift. **Why the split**: Different access patterns; mixing them degrades both. OLTP needs low latency and ACID; OLAP needs scan throughput....

Question 2

Explain the concept of ACID properties in the context of databases.

Accepted Answer

**ACID**: Atomicity (all or nothing), Consistency (valid state transitions), Isolation (concurrent transactions don't interfere), Durability (committed data persists). **Why it matters**: Without ACID, financial and operational data become inconsistent; retries and failures create duplicates or lost updates. **Scalability trade-off**: Strict isolation (e.g., Serializable) limits throughput; most OLTP systems use Read Committed or Repeatable Read....

Question 3

Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

Accepted Answer

**INNER JOIN**: Only rows with matches in both tables. **LEFT JOIN**: All from left; matches from right; NULLs where no match. **RIGHT JOIN**: All from right; matches from left. **FULL JOIN**: All from both; NULLs where no match. **Why it matters**: Join choice affects result cardinality and semantics. Wrong join = wrong numbers. **Scalability**: Hash joins are common; broadcast for small dimension. FULL OUTER can be expensive—large shuffle....

Question 4

How do you handle NULL values in SQL? Mention functions like COALESCE and NULLIF.

Accepted Answer

**Approaches**: IS NULL / IS NOT NULL for filtering. **COALESCE(val1, val2, ...)**: First non-NULL value; useful for defaults. **NULLIF(val1, val2)**: Returns NULL if equal; e.g., NULLIF(divisor, 0) to avoid divide-by-zero. **Why it matters**: NULL propagates in expressions; aggregate functions ignore NULL (except COUNT(*)). JOIN on NULL yields no match (NULL ≠ NULL). **Scalability**: COALESCE in SELECT is cheap; in WHERE or JOIN it can prevent index use....

Question 5

What is a Common Table Expression (CTE), and when would you use it?

Accepted Answer

**CTE**: A named temporary result set in a WITH clause, referenced in the main query. **Use cases**: Readability—break complex queries into steps. Reusability—reference same CTE multiple times. Recursion—hierarchies (org chart, bills of materials). **Why it matters**: CTEs improve maintainability; deep subqueries are hard to debug. **Scalability**: In some engines (e.g., PostgreSQL), CTEs are optimization fences—materialized once. In others (Snowflake, BigQuery), they're inlined....

Question 6

What is the difference between a primary key and a unique key?

Accepted Answer

**Primary Key**: Unique identifier; NOT NULL; one per table; often clustered. **Unique Key**: Enforces uniqueness; can have NULL (SQL allows one NULL per column in uniqueness); multiple per table. **Why it matters**: PK defines identity and referential integrity; unique constrains alternate keys (e.g., email). **Scalability**: PK is often the clustering key; choice affects physical layout. Unique indexes enable lookups. **Cost**: Each constraint adds index overhead....

Question 7

What is the difference between WHERE and HAVING clauses in SQL?

Accepted Answer

**WHERE**: Filters rows before grouping/aggregation; cannot use aggregate functions. **HAVING**: Filters groups after GROUP BY; used with aggregate functions. **Why it matters**: WHERE reduces rows early (cheaper); HAVING filters on computed values. Misplacing predicates causes wrong results or inefficiency. **Scalability**: Pushing filters to WHERE reduces data before aggregation; HAVING on large grouped result can be expensive....

Question 8

Describe a recent project where you used AWS services extensively. What was your role, and what challenges did you face?

Accepted Answer

**Situation**: Built a data platform on AWS for real-time analytics serving 100M events/day. **Task**: Lead design and implementation; deliver sub-minute latency for critical dashboards. **Action**: Architected S3 (raw), Glue (ETL, catalog), Athena (ad-hoc), Redshift (curated), Kinesis (streaming). Challenges: (1) Schema evolution in Glue—addressed with schema registry. (2) Kinesis shard scaling—implemented auto-scaling. (3) Athena cost—partitioning and lifecycle policies....

Question 9

Describe the process for migrating data from an on-premises SQL database to AWS. What services and strategies would you use?

Accepted Answer

**Architectural Logic**: Migration is phased—assessment, strategy, execution, validation. **Process**: (1) Assessment—catalog schema, volume, dependencies. (2) Strategy—lift-and-shift vs modernize; targets (RDS, Redshift, S3). (3) Tools—AWS DMS (CDC/replication), SCT (schema conversion), DataSync (bulk file). (4) Execution—bulk load historical; enable CDC; cutover. (5) Validation—row counts, checksums, sample comparison. **Why DMS**: Minimal-downtime; ongoing sync....

Question 10

Discuss a project where you significantly impacted performance or cost optimization.

Accepted Answer

**Situation**: BigQuery costs spiked 40% MoM from ad-hoc queries. **Task**: Reduce cost without blocking users. **Action**: (1) Identified heavy users and expensive queries. (2) Implemented partitioned tables and clustering. (3) Created materialized views for common patterns. (4) Added query cost alerts. (5) Trained users on partition pruning. Also: optimized 2-hour nightly job to 15 min via incremental. **Result**: 35% cost reduction; faster queries....

EPAM SQL Interview Questions

Companies asking these questions

All 14 Questions

More Interview Prep Guides

Get All Answers in PDF Format

EPAM SQL Interview Questions

Companies asking these questions

All 14 Questions

More Interview Prep Guides

Get All Answers in PDF Format