SQL

How to answer in an interview

SQL, or Structured Query Language, is a standard language used to communicate with and manipulate relational databases. It's used to query, insert, update, and delete data, as well as define and manage database structures like tables, views, and indexes.

Explain like I am 5

SQL is the special language you use to ask a database questions, like 'show me all the customers who live in Delhi,' and the database answers back.

How to answer in an interview

SQL databases are relational, use structured schemas with tables and rows, and enforce ACID compliance, making them ideal for structured data with complex relationships. NoSQL databases are typically schema-less, store data as documents, key-value pairs, or graphs, and are designed for horizontal scalability and flexible or unstructured data.

Explain like I am 5

SQL databases are like a filing cabinet with strict labeled folders for everything. NoSQL is more like a flexible box where you can toss in different shaped items without needing a fixed folder for each.

How to answer in an interview

A primary key uniquely identifies each record in a table and cannot contain null values. A foreign key is a column in one table that references the primary key of another table, establishing a relationship between the two tables and enforcing referential integrity.

Explain like I am 5

A primary key is like a unique ID card number for a person. A foreign key is like writing someone else's ID number on your form to show you're connected to them.

How to answer in an interview

WHERE filters individual rows before any grouping occurs and cannot be used with aggregate functions. HAVING filters groups after a GROUP BY has been applied and is used specifically to filter based on aggregate function results, like COUNT or SUM.

Explain like I am 5

WHERE picks which individual people to include before you sort them into teams. HAVING picks which whole teams to keep after you've already grouped everyone.

How to answer in an interview

DELETE removes specific rows based on a condition and can be rolled back, and it's a DML command that fires triggers. TRUNCATE removes all rows from a table quickly without logging individual row deletions, and it resets identity counters. DROP removes the entire table structure along with its data permanently.

Explain like I am 5

DELETE is erasing specific lines from a notebook page. TRUNCATE is ripping out the page but keeping the notebook. DROP is throwing away the whole notebook.

How to answer in an interview

Aggregate functions perform a calculation on a set of rows and return a single summary value. Common examples include COUNT(), SUM(), AVG(), MIN(), and MAX(), and they're often used together with GROUP BY to summarize data by category.

Explain like I am 5

Aggregate functions take a whole pile of numbers and boil them down into one answer, like adding up all your allowance from the whole month into a single total.

How to answer in an interview

GROUP BY organizes rows that share the same value in specified columns into summary groups, typically used alongside aggregate functions like SUM or COUNT to calculate per-group results, such as total sales per region.

Explain like I am 5

GROUP BY sorts everything into piles based on something they share, like grouping students by grade, so you can then count or add things up per pile.

How to answer in an interview

UNION combines the result sets of two or more SELECT queries and removes duplicate rows, which requires extra processing. UNION ALL also combines results but keeps all duplicates, making it faster since no duplicate-checking is needed.

Explain like I am 5

UNION combines two lists and removes repeated names. UNION ALL combines two lists but keeps every name, even if it shows up twice.

How to answer in an interview

An INNER JOIN returns only matching rows from both tables. A LEFT JOIN returns all rows from the left table plus matched rows from the right, filling unmatched right-side columns with NULL. A RIGHT JOIN does the opposite, and a FULL OUTER JOIN returns all rows from both tables, matching where possible and filling in NULLs elsewhere.

Explain like I am 5

Think of two guest lists. INNER JOIN shows only people on both lists. LEFT JOIN shows everyone on the first list, marking who's missing from the second. FULL OUTER JOIN shows everyone from both lists combined.

How to answer in an interview

Normalization is the process of organizing database tables to reduce data redundancy and improve data integrity, typically by dividing large tables into smaller related tables and defining relationships between them. It's implemented through a series of normal forms (1NF, 2NF, 3NF, etc.), each addressing specific types of redundancy.

Explain like I am 5

Normalization is like organizing a messy closet into separate, labeled drawers so you don't have duplicate copies of the same thing scattered everywhere.

How to answer in an interview

An index is a database structure, typically a B-tree, that allows faster lookups of rows based on the values of one or more columns, similar to a book's index. Without an index, the database must scan the entire table, but with one, it can quickly locate matching rows, greatly speeding up SELECT queries at the cost of slower writes and extra storage.

Explain like I am 5

An index is like the index page at the back of a book — instead of flipping through every single page to find a topic, you jump straight to the right page.

How to answer in an interview

A transaction is a sequence of one or more SQL operations executed as a single logical unit of work, using COMMIT to save changes permanently or ROLLBACK to undo them if an error occurs. Transactions ensure that a set of related operations either fully succeed or fully fail, preserving data integrity.

Explain like I am 5

A transaction is like transferring money between two bank accounts — either both the withdrawal and deposit happen together, or neither happens, so money never just disappears.

How to answer in an interview

A subquery is a query nested inside another query, often used to filter results based on a computed value, while a JOIN combines rows from two or more tables based on a related column directly. JOINs are generally more efficient for combining data from multiple tables, while subqueries are useful for more isolated, conditional logic.

Explain like I am 5

A subquery is like asking a question inside another question, getting an answer first before finishing the main question. A JOIN is like laying two lists side by side and matching them up directly.

How to answer in an interview

A stored procedure is a precompiled set of SQL statements stored in the database that can be executed as a single callable unit, often accepting parameters. They improve performance through precompilation, promote code reuse, and encapsulate business logic close to the data.

Explain like I am 5

A stored procedure is like a saved recipe in the database's cookbook — instead of rewriting all the steps every time, you just say the recipe's name and it runs automatically.

How to answer in an interview

A view is a virtual table based on the result of a stored SQL query, which doesn't store data itself but presents data from one or more underlying tables. Views simplify complex queries, provide a layer of abstraction, and can restrict access to specific columns or rows for security purposes.

Explain like I am 5

A view is like a custom window into the data — you see exactly what the window shows, but the actual furniture (data) still lives in the real tables behind it.

How to answer in an interview

SQL injection is a security vulnerability where an attacker inserts malicious SQL code into an input field to manipulate or access the database in unintended ways. It's prevented by using parameterized queries or prepared statements instead of concatenating user input directly into SQL strings, along with input validation and least-privilege database accounts.

Explain like I am 5

SQL injection is like someone slipping a sneaky extra instruction into a form so the computer does something it shouldn't. Using safe question-and-answer templates instead of raw text prevents that trick from working.

How to answer in an interview

Denormalization intentionally introduces redundancy into a database design, often by merging tables or duplicating data, to improve read performance by reducing the number of joins needed. It's a trade-off used in read-heavy systems like reporting or analytics, at the cost of higher storage use and more complex data consistency management.

Explain like I am 5

Sometimes it's faster to keep duplicate notes in multiple places so you don't have to run around gathering information every time — that's denormalization, trading extra storage for speed.

How to answer in an interview

ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity ensures a transaction is all-or-nothing, Consistency ensures the database moves from one valid state to another, Isolation ensures concurrent transactions don't interfere with each other, and Durability ensures committed changes survive system failures.

Explain like I am 5

ACID is a set of promises a database makes: it either finishes a job completely or not at all, it never leaves things half-broken, different tasks don't trip over each other, and once something's saved, it stays saved even if the power goes out.

How to answer in an interview

A deadlock occurs when two or more transactions are waiting for each other to release locks, causing them to be stuck indefinitely. It can be avoided by acquiring locks in a consistent order across transactions, keeping transactions short, using appropriate isolation levels, and allowing the database's deadlock detection to roll back one of the conflicting transactions.

Explain like I am 5

A deadlock is like two people each holding a door the other needs, and both waiting for the other to let go first — neither can move forward.

How to answer in an interview

A clustered index determines the actual physical order of data in the table, so there can only be one per table, and it's typically built on the primary key. A non-clustered index maintains a separate structure with pointers back to the actual rows, allowing multiple non-clustered indexes per table for different query patterns.

Explain like I am 5

A clustered index is like arranging all the books on a shelf in the exact order of the index itself. A non-clustered index is like a separate card catalog that points to where each book actually sits.