Scribd
Upload
3 views2 pages

SQL Interview Prep: Key Concepts Explained

The document outlines key SQL concepts including Slowly Changing Dimensions (SCD), CTE vs Subquery, and the differences between Views and Materialized Views. It also covers performance optimization techniques, various types of joins, file types, cursors, triggers, temporary tables, and counting values. Additionally, it explains the behavior of inner joins with NULL and blank values.

Uploaded by

barath jothi
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views2 pages

SQL Interview Prep: Key Concepts Explained

The document outlines key SQL concepts including Slowly Changing Dimensions (SCD), CTE vs Subquery, and the differences between Views and Materialized Views. It also covers performance optimization techniques, various types of joins, file types, cursors, triggers, temporary tables, and counting values. Additionally, it explains the behavior of inner joins with NULL and blank values.

Uploaded by

barath jothi
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Interview Preparation Questions &

Explanations

Slowly Changing Dimensions (SCD)


SCD handles how data changes in a data warehouse.
- SCD0: No changes tracked, only current data kept.
- SCD1: Overwrites old data with new data.
- SCD2: Keeps history with new row versioning.
- SCD3: Keeps limited history using extra columns.
SCD2 handles duplicates by inserting new rows with effective start and end dates.

CTE vs Subquery
CTE (Common Table Expression) improves readability, can be recursive, and reused.
Subquery is inline and less reusable.
Performance depends on optimizer, often similar.

EXPLAIN for Queries


EXPLAIN shows how the database will execute a query, which indexes are used, and estimated
costs.

View vs Materialized View


View: Virtual table, no data stored, recalculates on each query.
Materialized View: Stores query results physically, faster for repeated access but needs refreshing.

Indexes
Index improves search speed.
Clustered Index: Data physically stored in index order, one per table.
Non-Clustered Index: Separate structure pointing to data, many per table.

Improving Query Speed


Tips: Use indexes, avoid SELECT *, reduce subqueries, use LIMIT, partitioning, and analyze query
plans.

Joins (Anti & Semi)


Anti Join: Returns rows from left table not matching right.
Left Anti Join: Specifically from left excluding right matches.
Semi Join: Returns rows from left that match right but without duplicating right side.

File Types
Parquet: Columnar, compressed, efficient for analytics.
CSV: Row-based, plain text, larger in size.

Cursors and Triggers


Cursor: Iterates row by row, used rarely due to slowness.
Trigger: Auto executes on insert/update/delete, better than cursors for automation.

Types of Triggers
AFTER: Executes after event.
INSTEAD OF: Replaces operation.
DDL Triggers: On schema changes (CREATE, DROP).

Temporary Tables vs Variables


Temporary Table: Stores intermediate results, accessible by multiple queries.
Temporary Variable: Holds single value or small dataset in memory.
Use Temp Tables even with Materialized Views for complex joins.

Counting Values
COUNT(*): Counts all rows including NULLs.
COUNT(column): Ignores NULLs.
COUNT(DISTINCT column): Counts unique non-NULL values.

Inner Joins with NULL & Blanks


NULLs do not match NULLs in joins. Only non-null matching values will join.
Blanks ('') can match other blanks, but NULL ≠ NULL.

You might also like