must know 34 Important sql interview questions in 2024

Explore a comprehensive list of 34 SQL interview questions for 2024, covering topics like order of execution, joins, constraints, functions, and more. Get insights into SQL concepts and enhance your interview preparation.

Q1. Explain order of execution of SQL.

SQL statements follow a specific execution order.

1. FROM clause: Tables and views are selected.
2. WHERE clause: Rows are filtered based on specified conditions.
3. GROUP BY clause: Rows are grouped based on specified columns.
4. HAVING clause: Groups are filtered based on specified conditions.
5. SELECT clause: Columns are selected.
6. ORDER BY clause: Rows are sorted based on specified columns.
7. LIMIT/OFFSET (if applicable): Rows are limited or offset.

Q2. What is difference between where and having?

• WHERE: Filters rows before grouping.
• HAVING: Filters groups after grouping.

Q3. What is the use of group by?

GROUP BY organizes rows with identical values into summary rows. It is often used with aggregate functions (like SUM, COUNT, AVG) to perform calculations on each group.

Q4. Explain all types of joins in SQL?

SQL supports several types of joins.

• INNER JOIN
• LEFT JOIN (or LEFT OUTER JOIN)
• RIGHT JOIN (or RIGHT OUTER JOIN)
• FULL JOIN (or FULL OUTER JOIN)
• CROSS JOIN
• SELF JOIN.

Q5. What are triggers in SQL?

Triggers are special types of stored procedures that automatically execute in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table.

Q6. What is the stored procedure in SQL?

A stored procedure is a precompiled collection of SQL statements stored in the database, which can be called and executed repeatedly.

Q7. Explain all types of window functions, focusing on rank, row_num, dense_rank, lead, and lag functions?

Window functions enable calculations to be performed across a set of rows relative to the current row. Rank, row_number, dense_rank, lead, and lag functions are common window functions used for various analytical purposes.

Q8. What is difference between Delete and Truncat?

• DELETE is a DML (Data Manipulation Language) command that removes rows from a table based on a condition.
• TRUNCATE is a DDL (Data Definition Language) command Deletes all rows from a table while preserving its structure.

Q9. What is the difference between DML, DDL and DCL?

• DML (Data Manipulation Language) is used for manipulating data in the database (e.g., INSERT, UPDATE, DELETE).
• DDL (Data Definition Language) is used for defining or modifying the structure of the database (e.g., CREATE, ALTER, DROP).
• DCL (Data Control Language) is used for controlling access to the database (e.g., GRANT, REVOKE).

Q10. What is aggregate function and when do we use them? explain with few examples.

Aggregate functions perform calculations on a set of values and return a single value as the result.

Examples include

SUM, COUNT, AVG, MIN and MAX. They are typically used with the GROUP BY clause to perform calculations on groups of rows.

Q1. Which is faster between CTE and Subquery?

Generally, Common Table Expressions (CTEs) are more readable and easier to maintain than subqueries. However, in terms of performance, there might not be a significant difference between the two in modern database systems. The choice between them depends on readability and specific use cases.

Q11. What are constraints and types of Constraints?

Constraints ensure data integrity within a database by enforcing rules. Common types of constraints encompass PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL constraints.

Q12.Types of Keys?

In databases, keys play the important role of uniquely identifying individual rows within a table. Common types of keys include PRIMARY KEY, FOREIGN KEY, UNIQUE KEY, and COMPOSITE KEY.

Q13. Different types of Operators?

SQL operators facilitate data manipulation by executing various operations. Common types of operators include arithmetic operators (+, -, *, /), comparison operators (=, <>, >, <, >=, <=), logical operators (AND, NOT), and bitwise operators (&, |, ^).

Q14. Difference between Group by and where?

GROUP BY clause is used to group rows that have the same values into summary rows, typically used with aggregate functions.

WHERE clause is used to filter rows before any grouping or aggregation is performed.

Q15. What are Views?

Views are virtual tables generated by a SELECT query and stored in the database. They can simplify complex queries by providing a pre-defined set of data.

Q16. What are different types of constraints?

Constraints enforce rules on the data stored in tables to maintain data integrity. Common types include NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK constraints.

Q17. Explain the difference between varchar and nvarchar?

• VARCHAR is a variable-length character data type that stores non-Unicode characters.
• NVARCHAR is a variable-length Unicode character data type that can store both Unicode and non-Unicode characters.

Q18. Similar for char and nchar?

• CHAR is a fixed-length character data type that stores non-Unicode characters.
• NCHAR is a fixed-length Unicode character data type that can store both Unicode and non-Unicode characters.

Q19. What are index and their types?

An index is a database object that improves the speed of data retrieval operations on a table. Common types of indexes include clustered, non-clustered, unique, and composite indexes.

Q20. What is an index? Explain its different types.

An index is a database object that improves the speed of data retrieval operations on a table by providing a quick lookup mechanism. Common types of indexes include clustered, non-clustered, unique, and composite indexes.

Q21. List all types of SQL relationships.

Common types of relationships in SQL include one-to-one, one-to-many, and many-to-many relationships.

Q22. Differentiate between UNION and UNION ALL.

UNION combines the results of two or more SELECT queries into a single result set, removing duplicate rows. UNION ALL also combines results but retains all rows, including duplicates.

Q23. How many types of clauses are there in SQL?

There are several types of clauses in SQL, including SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT/OFFSET.

Q24. What is the difference between UNION and EXCEPT in SQL?

• UNION combines the results of two SELECT queries into a single result set, removing duplicates. EXCEPT returns distinct rows from the first SELECT query that are not present in the results of the second SELECT query.

Q25. What are the various types of relationships in SQL Server?

Relationships in SQL Server include one-to-one, one-to-many, and many-to-many relationships, which are established using primary and foreign keys.

Q26. Difference between Primary Key and Secondary Key in SQL?

A primary key is a column or set of columns that uniquely identifies each row in a table, while a secondary key is any other column or combination of columns used for data retrieval and integrity purposes.

Q27. What is the difference between the WHERE and HAVING clause in SQL?

The WHERE clause is used to filter rows before any grouping or aggregation is performed, while the HAVING clause is used to filter groups after grouping data using GROUP BY.

Q28. Find the second highest salary of an employee in SQL.

SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Q29. Write a retention query in SQL?

A retention query typically involves deleting or archiving old records from a table.

For Example

DELETE FROM table_name WHERE date_column < '2023-01-01';

Q30. Write year-on-year growth in SQL?

Year-on-year growth can be calculated using a self-join or window function.

For Example

SELECT year, value,
value - LAG(value) OVER (ORDER BY year) AS YoYGrowth
FROM sales_data;

Q31. Write a query for cumulative sum in SQL?

Cumulative sum can be calculated using a window function.

For Example

SELECT date, value,
SUM(value) OVER (ORDER BY date) AS CumulativeSum
FROM sales_data;

Q32. Difference between Function and Store procedure?

Functions return a single value and are typically used for computations, while stored procedures are sets of SQL statements that perform a task or operation and may return multiple values.

Q33. Do we use variable in views?

No, views cannot contain variables. However, views can be based on queries that use variables.

Q34. What are the limitations of views?

Views cannot contain variables, temporary tables, or dynamic SQL. They also have limitations on updating data, depending on the complexity of the underlying query.