In this tutorial, we will see some SQL interview questions with answers. If you are applying for the job of SQL developer, then you need to prepare this question before going for an interview/test. SQL language has different components like JOINs, Clauses, Functions, Keywords, Sub-Queries, and Operators. This article contains a few popular questions based on them. Let’s directly start with question-answers.
1. Differences between INNER JOIN and OUTER JOIN.
INNER JOIN: Select only the matching rows within both tables against the stipulated join condition.
OUTER JOIN: Retrieves all rows of a first table and also the corresponding rows of the second table. When it finds that there is no match, the results of columns from the second table will be NULL.
2. What is a correlated subquery?
A subquery that correlates with the outer query by depending on the outer values for its values is defined as a correlated subquery. It is evaluated only once for each row processed by the outer query and it also references specific columns from the outer query.
Also Read: SQL Correlated Subqueries
3. Describe what is meant when we say that there is a need for normalization in databases.
Normalization refers to putting data into a standard format that makes it less overlapping (repetitious) and independent. That is, it entails dividing tables from all aspects and stipulating linkages between them. This helps reduce data replications and also ensures data correctness.
4. Why do we need a GROUP BY clause in SQL?
In this sense, we may think of the GROUP BY clause as creating “summary” rows on which values such as total sales per product or average salary per department are based.
Also Read: GROUP BY Clause in SQL
5. Differentiate between UNION and UNION ALL.
UNION: Filtering out the duplicates from the result set.
UNION ALL: Does not delete duplicate rows among rows retrieved from the union of all result sets of different SELECT statements.
6. Describe the use and application of the COALESCE() procedure.
The first non-null expression in a list can be retrieved through the COALESCE(). This is commonly applied as a default where null may exist along a column.
7. Why is an ACID transaction?
These include atomicity, consistency, isolation, and durability (ACID). The properties are required to guarantee the reliable and repeatable nature of the database transactions.
- Atomicity: The transaction considers each work unit as one whole act.
- Consistency: One valid state follows a transaction, which takes the database to another valid state. It keeps the database safe.
- Isolation: Interference is prevented by executing transactions individually.
- Durability: After a transaction has been committed, its changes cannot be reversed, even if the system encounters problems afterwards.
8. Why would one use the ROW_NUMBER() window function?
One such window function is called ROW_NUMBER(), which uniquely numbers each row in a result set partition. It has been mainly employed to rank in line row-wise according to some parameters.
9. What do you mean by a self-join?
Self-join is just an ordinary join but involves joining the table with itself. It is utilized when dealing with tables that are structured hierarchically, or for comparing data between rows from the same table.
Also Read: SELF-JOIN in SQL
10. What are the differences between a stored procedure and a function in SQL? Provide examples.
Stored Procedure: A stored procedure is defined as a group of SQL statements, which are executed as one unit at a time. It can have returned a value or not at all.
Function: A function consists of several SQL statements that deliver one value at a time. It has to result in a value while not affecting the database state.
Stored Procedure Query:
CREATE PROCEDURE Employ_Count
AS
BEGIN
SELECT COUNT(*) FROM Employ;
END;
Code language: SQL (Structured Query Language) (sql)Function Query:
CREATE FUNCTION EmployeeFunction()
RETURNS INT
BEGIN
DECLARE employee_Count INT;
SELECT COUNT(*) INTO employee_Count FROM Employ;
RETURN employee_Count;
END;
Code language: SQL (Structured Query Language) (sql)Summary
In this article, we have seen 10 advanced-level SQL questions. These questions are important so prepare them before the test/interview. These questions are based on basic concepts, comparisons, and differences between two different techniques. These question-answers are frequently asked in many interviews for SQL developer jobs. We hope you will enjoy this article.