What is a Common Table Expression (CTE) in SQL?
A CTE is like creating a temporary named result set inside a query so the query becomes easier to read and reuse. Instead of writing a long complicated subquery multiple times you give it a name with WITH and then reference it like a temporary table.
What problem do CTEs solve?
They break complex queries into smaller understandable steps. Think of them like intermediate calculations in math where you store a result before continuing.
What is a Window Function?
A window function lets you perform calculations across related rows without collapsing them into one row like GROUP BY does. It lets every row see its neighbors.
What is the OVER() clause used for?
OVER tells SQL what group of rows a window function should look at. You define the “window” of rows the calculation should consider.
What does ROW_NUMBER() do?
ROW_NUMBER assigns a unique sequential number to rows within a group. Imagine numbering students in each classroom starting from 1.
What does RANK() do?
RANK orders rows but allows ties. If two rows tie for first place they both get rank 1 and the next rank becomes 3.
What does DENSE_RANK() do?
DENSE_RANK is similar to RANK but it does not skip numbers when there are ties.
What does PARTITION BY do?
PARTITION BY splits rows into groups for window functions. It is like running the same calculation separately for each category.
What is a Subquery?
A subquery is a query inside another query. It is used when you need a result first before finishing the main query.
What is a Correlated Subquery?
A correlated subquery depends on the outer query. For every row of the outer query the inner query runs again.
What is an Index in SQL?
An index is like a book index. Instead of scanning the whole table the database jumps directly to where the data lives.
Why do indexes improve performance?
Without an index the database must scan every row. With an index it uses a sorted structure to find data quickly.
What is a Composite Index?
A composite index indexes multiple columns together. It helps when queries filter using multiple columns.
What is Query Optimization?
Query optimization is the process the database uses to find the fastest way to run your query.
What is an Execution Plan?
An execution plan is the database’s step-by-step strategy for how it will retrieve the requested data.
What is Normalization?
Normalization organizes tables so that each piece of information is stored once. This prevents duplication and keeps data consistent.
What is Denormalization?
Denormalization intentionally duplicates data to make queries faster at the cost of extra storage.
What is a Primary Key?
A primary key uniquely identifies each row in a table. No two rows can share the same primary key.
What is a Foreign Key?
A foreign key links one table to another by referencing its primary key.
What is a Materialized View?
A materialized view stores the result of a query physically so the database does not need to recompute it every time.
What is an Aggregate Function?
An aggregate function summarizes many rows into a single value like totals or averages.
What are examples of aggregate functions?
COUNT
What is a Stored Procedure?
A stored procedure is a reusable program stored inside the database that can run complex logic.
What is a Trigger?
A trigger is automatic code that runs when certain events happen in the database such as INSERT or UPDATE.
