1. What is the CAP Theorem in database systems?

📌 The CAP Theorem states that in a distributed database, you can only guarantee two out of three:
✔ Consistency (C) – All nodes see the same data at the same time
✔ Availability (A) – Every request receives a response (even if stale)
✔ Partition Tolerance (P) – System continues to function despite network failures

Example: MongoDB prioritizes Availability & Partition Tolerance (AP), while SQL databases prioritize Consistency & Partition Tolerance (CP).

2. What is ACID compliance in SQL databases?

📌 ACID properties ensure data integrity in databases:
✔ Atomicity – Transactions are all-or-nothing
✔ Consistency – Database stays in a valid state
✔ Isolation – Transactions do not interfere with each other
✔ Durability – Committed data remains even after crashes

3. Write an SQL query to find the second highest salary from an Employee table.

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

📌 OFFSET 1 skips the highest salary, retrieving the second highest.

4. How do you detect and remove duplicate records in SQL?

📌 Detect duplicates:

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

📌 Remove duplicates (keep only one entry):

DELETE FROM employees WHERE id NOT IN ( SELECT MIN(id) FROM employees GROUP BY email );

5. Explain Normalization and its types.

📌 Normalization reduces redundancy and improves efficiency:
✔ 1NF – Remove duplicate columns
✔ 2NF – Ensure full functional dependency
✔ 3NF – Remove transitive dependencies
✔ BCNF – Ensure every determinant is a candidate key

6. What is an Index in SQL? How does it improve performance?

📌 Indexes speed up query execution by enabling quick lookups.
✔ Clustered Index – Sorts and stores rows physically
✔ Non-clustered Index – Stores pointers to actual data

Example: Create an index on a frequently searched column:

CREATE INDEX idx_customer ON orders(customer_id);

7. What is a Materialized View? How does it differ from a Normal View?

📌 Materialized Views store precomputed query results, improving performance.
✔ Normal View – Executes the query on demand
✔ Materialized View – Stores query results physically

Example: Create a Materialized View for faster access:

CREATE MATERIALIZED VIEW sales_summary AS SELECT region, SUM(revenue) FROM sales GROUP BY region;

8. What are Window Functions in SQL? Give an example.

📌 Window functions operate over a subset of rows related to the current row.

Example: Running total of sales per region

SELECT region, sales, SUM(sales) OVER (PARTITION BY region ORDER BY date) AS running_total FROM sales_data;

9. How does Data Partitioning improve database performance?

📌 Partitioning splits large tables into smaller, manageable pieces.
✔ Horizontal Partitioning – Divides rows across multiple tables
✔ Vertical Partitioning – Divides columns into separate tables

Example: Partitioning a sales table by year

PARTITION BY RANGE (year) ( PARTITION p1 VALUES LESS THAN (2023), PARTITION p2 VALUES LESS THAN (2024) );

10. What is the difference between UNION and UNION ALL?

✔ UNION – Removes duplicates
✔ UNION ALL – Includes duplicates

Example:

SELECT name FROM customers UNION SELECT name FROM employees;

11. What is ETL, and how is it used in Data Warehousing?

📌 ETL (Extract, Transform, Load) moves data from source systems to a data warehouse.
✔ Extract – Pull raw data from databases, APIs
✔ Transform – Clean, filter, and aggregate data
✔ Load – Insert data into a structured data warehouse

12. Explain how a Data Pipeline works.

📌 A data pipeline automates data flow between sources and storage systems.
✔ Batch Processing – Periodic data movement (ETL jobs)
✔ Stream Processing – Real-time data movement (Kafka, Spark Streaming)

13. What is Dimensional Modeling in Data Warehousing?

📊 Dimensional modeling optimizes databases for analytical queries.
✔ Fact Table – Stores measurable business data
✔ Dimension Table – Stores descriptive attributes

Example: A "Sales" fact table linked to "Product" and "Customer" dimensions.

14. How do you optimize a Power BI or Tableau dashboard?

✅ Pre-aggregate data in SQL queries
✅ Reduce the number of visual elements
✅ Use Extract mode instead of Live Connection
✅ Optimize DAX expressions (Power BI)

15. What is a Correlation Matrix, and when is it used?

📌 A Correlation Matrix shows relationships between multiple variables.
✔ Used in feature selection for Machine Learning models

Example using Python:

import pandas as pd df.corr()

16. What is the difference between Z-score and Min-Max Scaling?

✔ Z-score (Standardization) – Centers data around mean (good for Normal distributions)
✔ Min-Max Scaling – Rescales data between 0 and 1 (used for bounded features like age, salary)

17. What is an Outlier? How do you detect it?

📌 Outliers are extreme values that deviate significantly from the dataset.
✔ Z-score Method – Values beyond ±3 standard deviations
✔ IQR Method – Values outside Q1 - 1.5×IQR and Q3 + 1.5×IQR

Example: Detect outliers using Python

import numpy as np outliers = df[np.abs(df['column'] - df['column'].mean()) > (3 * df['column'].std())]

18. What is Feature Engineering?

📌 Feature Engineering creates meaningful features from raw data.
✔ Examples: Creating "Year" from a "Date" column, One-Hot Encoding

19. What is Time Series Forecasting, and which methods are used?

📊 Time Series Forecasting predicts future values based on historical data.
✔ Methods: ARIMA, LSTM, Prophet

20. What is Data Imputation?

📌 Data Imputation replaces missing values in a dataset.
✔ Mean/Median Imputation – Replace missing values with mean/median
✔ KNN Imputation – Use nearest neighbors to fill missing values

21. What is the difference between Batch Processing and Stream Processing?

✔ Batch Processing – Processes data in fixed intervals (ETL, Hadoop)
✔ Stream Processing – Processes real-time data (Kafka, Spark Streaming)

22. What is A/B Testing?

📌 A/B Testing compares two variants to determine the better-performing one.
✔ Uses statistical significance (p-value, confidence intervals)

23. What is a Data Governance Framework?

📌 Data Governance ensures data quality, security, and compliance.
✔ Includes data ownership, policies, security measures

24. What is a Data Mesh?

📌 Data Mesh is a decentralized data architecture focusing on domain ownership.
✔ Used in large-scale data environments

25. What is a Cloud Data Warehouse?

📌 A Cloud Data Warehouse stores large-scale data in a managed cloud environment.
✔ Examples: Google BigQuery, AWS Redshift, Snowflake