Structured Query Language (SQL) is a specialized programming language crafted for efficiently handling data within a relational database management system (RDBMS) or facilitating stream processing in a relational data stream management system (RDSMS).
Generally sql questions asked in job interview depends on the experience of the candidate, so we have categorized the article into two segment, like Fresher SQL Interview and Experienced SQL Job Interview.
All questions are taken from the real time interviews of sql developers.
SQL Interview Questions for Freshers
Q1. What is SQL?
Answer: SQL (Structured Query Language) is a domain-specific language used for managing and manipulating relational databases. It facilitates tasks such as querying data, updating records, and defining and modifying database structures.
Q2. Differentiate between SQL and NoSQL databases.
Answer: SQL databases are relational and use a structured schema, while NoSQL databases are non-relational and offer a flexible schema for unstructured data.
Q3. Explain the difference between INNER JOIN and LEFT JOIN.
Answer: INNER JOIN retrieves rows from both tables where there is a match based on the specified condition. LEFT JOIN retrieves all rows from the left table and the matching rows from the right table.
Q4. What is the purpose of the GROUP BY clause in SQL?
Answer: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, often for aggregating data using functions like COUNT, SUM, AVG, etc.
Q5. How do you find duplicate values in a table?
Answer: You can find duplicate values using the GROUP BY clause along with the HAVING clause, like this:sqlCopy codeSELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;
Q6. Explain the concept of a subquery in SQL.
Answer: A subquery is a query embedded within another query. It can be used to retrieve data that will be used by the main query as a condition or for further computation.
Q7. What is the purpose of the ORDER BY clause in SQL?
Answer: The ORDER BY clause is used to sort the result set based on one or more columns, either in ascending (ASC) or descending (DESC) order.
Q8. How do you update data in a table in SQL?
Answer: The UPDATE statement is used to modify existing records in a table. For example:sqlCopy codeUPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
Q9. Explain the differences between UNION and UNION ALL.
Answer: UNION combines the result sets of two or more SELECT statements and removes duplicate rows, while UNION ALL combines the result sets but retains all rows, including duplicates.
Q10. What is a primary key, and why is it important?
Answer: A primary key is a unique identifier for a record in a table. It ensures data integrity by guaranteeing that each record can be uniquely identified. It also establishes relationships between tables.
Q11. How do you find the nth highest or lowest value in a table?
Answer: You can use the LIMIT clause along with the ORDER BY clause to find the nth highest or lowest value. For example, to find the third highest value:sqlCopy codeSELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 2, 1;
Q12. What is a stored procedure in SQL?
Answer: A stored procedure is a precompiled collection of one or more SQL statements that are stored in the database. It can be executed multiple times with different parameters.
Q13. Explain the ACID properties in the context of database transactions.
Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably, guaranteeing the integrity of the database even in the event of failures.
Q14. How do you perform a self-join in SQL?
Answer: A self-join is performed when a table is joined with itself.
SELECT a.column_name, b.column_name
FROM table_name a, table_name b
WHERE a.common_column = b.common_column;Q15. Explain the concept of a foreign key.
Answer: A foreign key is a field that establishes a link between two tables. It refers to the primary key of another table and enforces referential integrity between the two tables.
Q16. What is the purpose of the LIKE operator in SQL?
Answer: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It can include wildcards such as % (for any sequence of characters) and _ (for a single character).
Q17. How do you perform a cross join in SQL?
Answer: A cross join, or Cartesian join, combines each row from the first table with every row from the second table.
SELECT *
FROM table1
CROSS JOIN table2;Q18. Explain the concept of indexing in SQL.
Answer: Indexing is a database feature that enhances the speed of data retrieval operations on a table. It creates an ordered structure that allows the database engine to locate and access rows more efficiently.
Q19. What is the purpose of the COUNT() function in SQL?
Answer: The COUNT() function is used to count the number of rows that meet a specified condition.
SELECT COUNT(*)
FROM table_name
WHERE condition;Q20. How do you delete duplicate rows in a table using SQL?
Answer: You can delete duplicate rows using the DELETE statement with a self-join.
DELETE t1
FROM table_name t1, table_name t2
WHERE t1.column_name = t2.column_name
AND t1.id > t2.id;SQL Interview Questions for Experienced
Q1. What is a trigger in SQL?
Answer: A trigger is a set of instructions that are automatically executed, or “triggered,” in response to certain events on a particular table or view. Triggers are commonly used to maintain data integrity, enforce business rules, or automate actions.
Q2. Explain the difference between a clustered and a non-clustered index.
Answer: A clustered index determines the physical order of data in a table, and the table can have only one clustered index. In contrast, a non-clustered index does not affect the physical order of the table and allows multiple indexes on the same table.
Q3. What is the purpose of the GROUP_CONCAT() function in SQL?
Answer: The GROUP_CONCAT() function concatenates values from multiple rows into a single string. It is often used with the GROUP BY clause to concatenate values within each group.
Q4. How do you handle ‘NULL’ values in SQL?
Answer: ‘NULL’ represents the absence of a value in a database.
Q5. Explain the concept of a self-referencing foreign key.
Answer: A self-referencing foreign key is a foreign key in a table that references the primary key column of the same table. It is used to create hierarchical relationships within a single table, such as in the case of an organizational chart.
Q6. What is a common table expression (CTE) in SQL?
Answer: A common table expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It simplifies complex queries and enhances code readability.
Q7. How do you find the second highest (or Nth highest) salary in a table?
Answer: You can use the LIMIT clause with the ORDER BY clause to find the second highest salary.
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;Q8. Explain the purpose of the SQL HAVING clause.
Answer: The HAVING clause is used in conjunction with the GROUP BY clause to filter the results of an aggregate function based on a specified condition. It is similar to the WHERE clause but operates on aggregated data.
Q9. What is the purpose of the SQL CASE statement?
Answer: The CASE statement is used to perform conditional logic within a SQL query. It allows you to execute different branches of code based on specified conditions, similar to a switch statement in programming languages.
Q10. Explain the difference between UNION and JOIN.
Answer: UNION combines the result sets of two or more SELECT statements vertically (stacked on top of each other), removing duplicate rows. JOIN combines columns from two or more tables horizontally based on a specified condition.
Q11. What is the purpose of the SQL LIKE operator?
Answer: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It allows the use of wildcards (% for any sequence of characters, _ for a single character) to match patterns.
Q12. Explain the concept of normalization and denormalization in databases.
Answer: Normalization is the process of organizing data to reduce redundancy and improve data integrity. Denormalization, on the other hand, involves combining tables to simplify queries and improve performance.
Q13. How do you use the SQL IN operator?
Answer: The IN operator is used in a WHERE clause to specify multiple values in a search condition. It is often used with a list of values or a subquery.
Q14. What is a composite key in SQL?
Answer: A composite key is a key that consists of more than one column to uniquely identify a record in a table. It is created by combining two or more columns.
Q15. Explain the concept of database transactions.
Answer: A database transaction is a sequence of one or more SQL statements that are executed as a single unit of work. Transactions ensure data integrity, consistency, and isolation during database operations.
Q16. How do you perform an SQL JOIN on multiple tables?
Answer: You can perform a join on multiple tables by using the JOIN clause with the appropriate conditions that link the tables.
SELECT *
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name
INNER JOIN table3 ON table2.column_name = table3.column_name;Q17. Explain the purpose of the SQL TRUNCATE statement.
Answer: The TRUNCATE statement is used to remove all records from a table quickly. Unlike DELETE, it does not log individual row deletions and is generally faster for clearing a table.
Q18. How do you find the total number of rows in a table?
Answer: You can use the COUNT function to find the total number of rows in a table.
SELECT COUNT(*)
FROM table_name;Q19. What is the purpose of the SQL UNIQUE constraint?
Answer: The UNIQUE constraint ensures that all values in a column are unique. It can be applied to one or more columns and is used to enforce the uniqueness of data.
Q20. Explain the difference between a view and a table in SQL.
Answer: A table is a physical storage structure that contains data, while a view is a virtual table based on the result of a SELECT query. Views do not store data themselves but provide a way to represent data from one or more tables.
Q21. What is the purpose of the SQL MAX() and MIN() functions?
Answer: The MAX() function returns the highest value in a column, while the MIN() function returns the lowest value in a column. They are often used with the GROUP BY clause to find maximum and minimum values within groups.
Q22. Explain the concept of an SQL index and its types.
Answer: An index in SQL is a data structure that improves the speed of data retrieval operations on a table. Common types include clustered indexes, non-clustered indexes, and unique indexes.
Q23. How do you use the SQL BETWEEN operator?
Answer: The BETWEEN operator is used in a WHERE clause to filter results based on a range of values.
SELECT *
FROM table_name
WHERE column_name BETWEEN value1 AND value2;Q24. What is the purpose of the SQL HAVING clause?
Answer: The HAVING clause is used in conjunction with the GROUP BY clause to filter the results of an aggregate function based on a specified condition. It is similar to the WHERE clause but operates on aggregated data.
Q25. How do you add a new column to an existing table in SQL?
Answer: You can add a new column to an existing table using the ALTER TABLE statement.
ALTER TABLE table_name
ADD COLUMN new_column_name data_type;Q26. Explain the purpose of the SQL CASE statement.
Answer: The CASE statement is used for conditional logic in SQL. It allows you to perform different actions based on different conditions within a single query.
Q27. What is the purpose of the SQL DISTINCT keyword?
Answer: The DISTINCT keyword is used in a SELECT statement to eliminate duplicate values from the result set. It returns unique values for the specified columns.
Q28. How do you retrieve the current date and time in SQL?
Answer: You can use the GETDATE() function in SQL Server, CURRENT_TIMESTAMP or NOW() function in MySQL, and SYSDATE in Oracle to retrieve the current date and time.
Q29. What is the purpose of the SQL COALESCE function?
Answer: The COALESCE function is used to return the first non-null expression among its arguments. It is often used to replace null values with a specified default value.
Q30. Explain the concept of an SQL subquery.
Answer: A subquery is a query nested inside another query. It can be used to retrieve data that will be used by the main query as a condition or for further computation.
Q31. What is the purpose of the SQL ALL operator?
Answer: The ALL operator is used in conjunction with a comparison operator and a subquery to compare a value to all values returned by the subquery.
Q32. Explain the concept of SQL injection and how to prevent it.
Answer: SQL injection is a technique where attackers insert malicious SQL code into a query. Prevention methods include using parameterized queries, input validation, and avoiding dynamic SQL.
Q33. How do you find the nth row in a table in SQL?
Answer: You can use the LIMIT and OFFSET clauses to find the nth row.
SELECT *
FROM table_name
LIMIT 1 OFFSET 4;Q34. What is the purpose of the SQL RANK() function?
Answer: The RANK() function is used to assign a rank to each row within the result set based on the values of one or more columns. It is often used in ranking scenarios.
Q35. Explain the difference between a LEFT JOIN and a RIGHT JOIN.
Answer: A LEFT JOIN retrieves all rows from the left table and the matching rows from the right table. A RIGHT JOIN retrieves all rows from the right table and the matching rows from the left table.
Q36. How do you find the average value in a column using SQL?
Answer: You can use the AVG() function to find the average value in a column.
SELECT AVG(column_name)
FROM table_name;Q37. What is the purpose of the SQL ROLLUP operator?
Answer: The ROLLUP operator is used in conjunction with the GROUP BY clause to generate subtotals and grand totals for a result set. It creates multiple grouping sets.
Q38. Explain the concept of the SQL PIVOT and UNPIVOT operators.
Answer: The PIVOT operator is used to rotate rows into columns, and the UNPIVOT operator is used to rotate columns into rows. They are often used for transforming data.
Q39. How do you use the SQL EXISTS operator?
Answer: The EXISTS operator is used in a WHERE clause to check for the existence of rows returned by a subquery. It returns TRUE if the subquery returns one or more rows.
Q40. What is the purpose of the SQL TRANSACTION statement?
Answer: The TRANSACTION statement is used to begin a transaction in SQL. Transactions ensure the consistency and integrity of the database by grouping a set of SQL statements into a single unit of work.
