Basic SQL Interview Questions

What is SQL? - Structured Query Language used for managing relational databases
What are the different types of SQL statements? - DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK)
What is the difference between WHERE and HAVING? - WHERE filters rows before grouping, HAVING filters after grouping
What is the difference between INNER JOIN and OUTER JOIN? - INNER returns matching rows, OUTER returns all rows from one/both tables
What is a primary key? - Unique identifier for a table record
What is a foreign key? - Field that references a primary key in another table
What is the difference between UNION and UNION ALL? - UNION removes duplicates, UNION ALL keeps duplicates
What is normalization? Explain 1NF, 2NF, and 3NF. - Process of organizing data to minimize redundancy
What are constraints in SQL? - Rules enforced on data columns (NOT NULL, UNIQUE, PRIMARY KEY, etc.)
What is the difference between DELETE, TRUNCATE, and DROP? - DELETE removes rows, TRUNCATE removes all rows, DROP removes entire table

What are aggregate functions? Name a few. - Functions that operate on multiple rows (COUNT, SUM, AVG, MAX, MIN)
How does the GROUP BY clause work? - Groups rows with same values into summary rows
What is the use of ORDER BY clause? - Sorts the result set in ascending/descending order
What is the difference between CHAR and VARCHAR? - CHAR is fixed-length, VARCHAR is variable-length
What is a view? Can we update a view? - Virtual table based on SQL statement; sometimes updatable
How can you find duplicate records in a table? - Using GROUP BY and HAVING COUNT(*) > 1
Explain the use of indexes. - Improves query performance by creating quick lookup structures
What are the different types of joins? - INNER, LEFT, RIGHT, FULL, CROSS, SELF
What is a correlated subquery? - Subquery that depends on outer query for values
What is a self join? Provide an example. - Joining a table to itself (e.g., employee-manager relationships)

How would you find the second highest salary in a table? - Using LIMIT/OFFSET or subqueries
Explain window functions with an example. - Functions that operate across related rows (ROW_NUMBER, RANK)
What is a common table expression (CTE)? - Temporary result set defined within execution scope
How would you write a query to fetch employees who earn more than their manager? - Self join with salary comparison
What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()? - Different ranking methods with ties
What are triggers? When would you use them? - Procedures that automatically execute on table events
Explain ACID properties. - Atomicity, Consistency, Isolation, Durability
What is a transaction? How do COMMIT and ROLLBACK work? - Sequence of operations as single unit
What are materialized views and how are they different from regular views? - Physically stored query results
How can you optimize a slow-running SQL query? - Indexing, query restructuring, analyzing execution plan

You have a table of employee salaries. Write a query to find departments where the average salary is above 60,000.
Write a SQL query to find customers who haven't placed any orders.
Given a blog schema, find the top 3 most active users by number of comments.
How would you design a schema for a library management system?
Write a query to find users who signed up in the last 7 days.