Top 50 Intermediate SQL Interview Questions with Detailed Answers
1. What is SQL?
Answer: SQL (Structured Query Language) is a standard programminglanguage used for managing and manipulating relational databases by performingtasks like querying, updating, and managing data.
2. What is a JOIN in SQL?
Answer: A JOIN in SQL is used to combine rows from two or more tablesbased on a related column between them.
3. Explain the different types of JOINs in SQL.
Answer:
- INNER JOIN: Returns only matching rows from both tables.
- LEFT JOIN: Returns all rows from the left table and matching rows from the right table; if no match, returns NULL.
- RIGHT JOIN: Returns all rows from the right table and matching rows from the left table; if no match, returns NULL.
- FULL OUTER JOIN: Returns all rows when there is a match in either table; otherwise, NULL.
4. What is a Primary Key?
Answer: A primary key is a column (or set of columns) in a table thatuniquely identifies each row. It does not allow NULL values and ensuresuniqueness.
5. What is a Foreign Key?
Answer: A foreign key is a column (or set of columns) that creates a linkbetween two tables. It references the primary key in another table, maintainingreferential integrity.
6. What is the difference between DELETE and TRUNCATE?
Answer:
- DELETE: Removes specific rows based on a condition and can be rolled back. It logs each row deletion.
- TRUNCATE: Removes all rows from a table without logging individual row deletions and cannot be rolled back.
7. What is a subquery in SQL?
Answer: A subquery is a query nested within another query. It is used toperform operations that depend on the results of another query.
8. What is a correlated subquery?
Answer: A correlated subquery is a subquery that references columns fromthe outer query. It is executed once for each row processed by the outer query.
9. How do you retrieve unique records from a table?
Answer: You can use the DISTINCT keyword to eliminate duplicaterecords from the result set.
SqlQuery
SELECTDISTINCT column_name FROM table_name;
10. What is a View in SQL?
Answer: A view is a virtual table that contains data from one or moretables. It is a saved query that can simplify complex SQL operations andrestrict access to specific data.
11. Can we update a View in SQL?
Answer: Yes, we can update a view if it is based on a single table, andthe update doesn’t violate any constraints. However, updating views thatinvolve complex joins or aggregations is not allowed.
12. What is a stored procedure?
Answer: A stored procedure is a precompiled collection of one or more SQLstatements stored in the database. It is used to encapsulate logic and promotereusability.
13. What is the difference between a stored procedure and afunction in SQL?
Answer:
- Stored Procedure: Can perform actions like INSERT, UPDATE, and DELETE, and may or may not return a value.
- Function: Always returns a single value and cannot modify the database (i.e., no INSERT/UPDATE/DELETE operations).
14. What is indexing in SQL?
Answer: Indexing improves query performance by allowing the database tofind rows faster. Indexes are created on columns that are frequently used insearch conditions or joins.
15. What are the types of indexes in SQL?
Answer: Common types include:
- Clustered Index: Sorts and stores data rows in the table based on the index key.
- Non-Clustered Index: Maintains a separate structure with pointers to the data rows.
- Unique Index: Ensures that no duplicate values are inserted in the indexed column.
16. What is a Self-Join?
Answer: A Self-Join is a join where a table is joined with itself,typically using table aliases to differentiate between the two instances of thetable.
17. What is the difference between UNION and UNION ALL?
Answer:
- UNION: Combines the result sets of two queries and removes duplicates.
- UNION ALL: Combines the result sets of two queries without removing duplicates.
18. What are ACID properties in a database?
Answer:
- Atomicity: Ensures that all operations in a transaction are completed; otherwise, none are applied.
- Consistency: Ensures that a transaction moves the database from one valid state to another.
- Isolation: Transactions are executed in isolation and their intermediate states are not visible to others.
- Durability: Once a transaction is committed, the changes are permanent even in case of system failure.
19. How do you optimize a SQL query?
Answer:
- Use indexing for columns in WHERE and JOIN clauses.
- Avoid SELECT * and only select the required columns.
- Use EXISTS instead of IN for large datasets.
- Optimize subqueries with joins.
- Analyze execution plans to find bottlenecks.
20. What is a trigger in SQL?
Answer: A trigger is a set of SQL statements that automatically executein response to specific events on a table, such as INSERT, UPDATE, or DELETE.
21. What is the difference between HAVING and WHERE clause?
Answer:
- WHERE: Filters rows before aggregation.
- HAVING: Filters groups after aggregation has been performed.
22. Explain the difference between a clustered and a non-clusteredindex.
Answer:
- Clustered Index: Determines the physical order of data in the table. A table can have only one clustered index.
- Non-Clustered Index: Does not affect the physical order of data and stores a separate structure with pointers to the data.
23. What is the use of the GROUP BY clause?
Answer: The GROUP BY clause is used to group rows that have the samevalues in specified columns, often used with aggregate functions like COUNT,SUM, AVG, etc.
24. What is a CTE (Common Table Expression)?
Answer: A CTE is a temporary result set defined within the execution of asingle SQL statement. It can be referenced within that query to simplifycomplex joins and subqueries.
SqlQuery
WITHCTE_name AS (
SELECT column_name FROM table_name
)
SELECT* FROM CTE_name;
25. What are window functions in SQL?
Answer: Window functions perform calculations across a set of table rowsrelated to the current row, often used with OVER(). Examples include ROW_NUMBER(),RANK(), DENSE_RANK(), and LEAD().
26. What is normalization?
Answer: Normalization is the process of organizing data in a database toavoid redundancy and improve data integrity by dividing a large table intosmaller, related tables.
27. What is denormalization?
Answer: Denormalization is the process of combining tables to reduce thecomplexity of queries by allowing redundant data, often used to improve readperformance at the cost of write efficiency.
28. What is a composite key?
Answer: A composite key is a combination of two or more columns used touniquely identify rows in a table.
29. What is a surrogate key?
Answer: A surrogate key is a unique identifier for a row that has nobusiness meaning, typically an auto-incremented integer.
30. What are constraints in SQL?
Answer: Constraints are rules enforced on data columns, ensuring dataintegrity. Examples include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL,and CHECK.
31. How do you find the second highest salary in a table?
Answer:
SqlQuery
SELECTMAX(salary)
FROMemployees
WHEREsalary < (SELECT MAX(salary) FROM employees);
32. What is a CROSS JOIN?
Answer: A CROSS JOIN returns the Cartesian product of two tables, meaningevery row from the first table is paired with every row from the second table.
33. What is a NULL value in SQL?
Answer: A NULL value represents the absence of a value in a column. It isdifferent from zero or an empty string.
34. How do you handle NULL values in SQL?
Answer: You can handle NULL values using IS NULL, IS NOT NULL, orby replacing them with another value using COALESCE() or IFNULL().
SqlQuery
SELECTCOALESCE(column_name, 'default_value') FROM table_name;
35. What is the difference between a scalar function and anaggregate function?
Answer:
- Scalar Function: Returns a single value for each row (e.g., LEN(), UPPER()).
- Aggregate Function: Performs a calculation on multiple values and returns a single result (e.g., SUM(), COUNT()).
36. What is the purpose of the COALESCE function?
Answer: COALESCE() returns the first non-NULL value in the list ofexpressions.
SqlQuery
SELECTCOALESCE(column1, column2, 'Default') FROM table_name;
37. What is a transaction in SQL?
Answer: A transaction is a sequence of SQL operations executed as asingle unit of work, ensuring the ACID properties.
38. How do you use the RANK() function in SQL?
Answer: The RANK() function assigns a unique rank to each rowwithin a partition of a result set, with gaps in rank for tied values.
SqlQuery
SELECTcolumn_name, RANK() OVER (ORDER BY column_name) FROM table_name;
39. What is the difference between RANK() and DENSE_RANK()?
Answer:
- RANK(): Leaves gaps in ranking when there are ties.
- DENSE_RANK(): Assigns consecutive ranks without gaps.
40. What is a MERGE statement in SQL?
Answer: The MERGE statement allows you to perform INSERT, UPDATE,or DELETE operations in a single query based on certain conditions.
41. What is a scalar subquery?
Answer: A scalar subquery returns a single value. It is used where asingle value is expected, such as in a SELECT or WHERE clause.
42. What is the difference between DELETE and DROP?
Answer:
- DELETE: Removes specific rows from a table based on conditions.
- DROP: Deletes an entire table or database, including all of its data and structure.
43. What is a partitioned table in SQL?
Answer: A partitioned table is divided into smaller, more manageablepieces (partitions) based on the values of one or more columns, improving queryperformance and scalability.
44. How do you use the CASE statement in SQL?
Answer: The CASE statement provides conditional logic in SQL,allowing you to return different results based on conditions.
SqlQuery
SELECTCASE
WHEN condition THEN result
ELSE default_result
ENDFROM table_name;
45. How do you handle duplicate records in SQL?
Answer: Use the DISTINCT keyword to remove duplicates or ROW_NUMBER()with a common table expression to delete duplicate rows.
SqlQuery
WITHCTE AS (
SELECT column_name, ROW_NUMBER() OVER (PARTITIONBY column_name ORDER BY column_name) AS row_num
FROM table_name
)
DELETEFROM CTE WHERE row_num > 1;
46. What is a sequence in SQL?
Answer: A sequence is an object used to generate a sequence of uniquenumeric values, often used for auto-incrementing primary keys.
47. What is the difference between a database and a schema in SQL?
Answer:
- Database: A collection of related tables, views, and other objects.
- Schema: A logical grouping of database objects, serving as a namespace within the database.
48. What is a deadlock in SQL?
Answer: A deadlock occurs when two or more transactions are waiting foreach other to release locks on resources, preventing them from completing.
49. What is a materialized view?
Answer: A materialized view stores the result of a query physically,unlike a regular view that just stores the query definition. This allows forfaster query performance at the cost of storage.
50. What are user-defined functions (UDFs) in SQL?
Answer: UDFs are custom functions created by the user to perform complexcalculations or operations, which can be reused throughout queries.
Thiscomprehensive set of SQL interview questions and answers will provideintermediate-level candidates with the knowledge required to ace SQL-basedinterviews, improving their understanding of key concepts and practicalimplementations.
CSDT CENTRE