Basic SQL Interview Questions for Data Engineer

Basic-level SQL interview questions for Data Engineer positions.

1. What is the difference between OLTP and OLAP databases?

Answer: OLTP (Online Transaction Processing): Optimized for writes, normalized data, row-oriented, used for daily operations. OLAP (Online Analytical Processing): Optimized for reads, denormalized/star schema, column-oriented, used for analytics and reporting.

-- OLTP: Normalized tables, frequent small transactions
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, total DECIMAL);
CREATE TABLE order_items (order_id INT, product_id INT, quantity INT);

-- OLAP: Denormalized fact table with dimensions
CREATE TABLE fact_sales (
  date_key INT,
  product_key INT,
  customer_key INT,
  quantity INT,
  revenue DECIMAL
);