In this article we will discuss about the most asked SQL interview questions and answers. If you need to know other interview questions and answers, I strongly recommend to follow this link: Interview Questions. Now in this post we are going to share the interview questions or the information which you must know as a programmer or a developer, especially if you are a Dot Net developer. We will explain few questions here, if you still need some additional information on SQL, you can read here: SQL . I hope you will like this article.
Background
I am a dot net developer. As a dot net developer, there are so many things that I must be aware of. I am sharing those in the form of articles, you can always read my other interview questions here in the below links.
So shall we now discuss about SQL interview questions?
SQL Interview Questions
What are the types of Joins in SQL? Explain?
Returns all rows when there is at least one match in BOTH tables
Return all rows from the left table, and the matched rows from the right table
Return all rows from the right table, and the matched rows from the left table
Return all rows when there is a match in ONE of the tables
What is the default join in SQL? Give an example query?
The default join is INNER JOIN.
Example:
[sql]
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
[/sql]
Describe all the joins with examples in SQL?
SQL LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
[sql]
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
[/sql]
SQL RIGHT JOIN
The right join returns all the rows in the right table ie table 2 with the matching ones in the left table(table1).
[sql]
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
[/sql]
SQL FULL OUTER
The full join returns all rows from the left table (table1) and from the right table (table2)
[sql]
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
[/sql]
What is Union And Union All ? Explain the differences?
SQL UNION
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
Note: The UNION operator selects only distinct values by default.
[sql]
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
[/sql]
SQL UNION ALL
[sql]
SQL UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
[/sql]
Allows duplicate values.
Differentiate Clustered and Non clustered Index in SQL?
A clustered index is one in which the index’s order is arranged according to the physical order of rows in the table. Due to this reason there can only be one clustered index per table, usually this is the primary key.
A non clustered index is one in which the order of index is not in accordance with the physical order of rows in the table.
Create Index Syntax
[sql]
CREATE INDEX [ CLUSTERED | NONCLUSTERED ] PIndex ON Persons (LastName,FirstName)
[/sql]
Explain the difference between Stored Procedure and User Defined Function?
Stored Procedure
Stored procedures are reusable code in database which is compiled for first time and its execution plan saved. The compiled code is executed when everytime its called. You
Function
Function is a database object in SQL Server. Basically it is a set of SQL statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s). It is compiled eveyrtime its invoked.
Basic Difference
Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
Functions can have only input parameters for it whereas Procedures can have input/output parameters .
Functions can be called from Procedure whereas Procedures cannot be called from Function
Advanced Difference
Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
We can use transactions in stored procedure but not in functions.
That’s all. Have a great day.
Conclusion
Did I miss anything that you may think which is needed? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.
Your turn. What do you think?
A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, Asp.Net Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can.
Kindest Regards
Sibeesh Venu