SQL Interview Questions

SQL (Structured Query Language) is a language to access RDBMS. Here is a list of most popular SQL interview questions and answers.

In most of the software application you develop, you'll end up using some kind of database. As a software developer, companies expect you to know SQL.  SQL is the language used to work with relational databases such as SQL Server and Oracle. In this article, I'm going to share some popular SQL Interview Questions and their answers.
 
Here is a list of the questions and their answers.  
 

Q. What is SQL?

 
Structured Query Language (SQL) is the language used in relational database management systems (RDBMS) to query, update, and delete data. SQL is a standard query language for RDBMS. SQL language’s queries are also known as SQL commands or SQL statements.There are four tupes of joins in SQL.
 
Here is a detailed article on SQL: What is SQL
 

Q. What are the types of Joins in SQL?

 
There are four types of joins in SQL.
  1. INNER JOIN: Returns all rows when there is at least one match in BOTH the tables.
  2. LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
  3. RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.
  4. FULL JOIN: Returns all rows when there is a match in ONE of the table.
Here is a detailed article on Joins with examples: Joins in SQL Server
 

Q. What is the default join in SQL? Give an example query?

 
The default join in SQL is INNER JOIN. Here is an example that is applied on two tables.

Example 
  1. SELECT column_name(s)  
  2. FROM table1  
  3. INNER JOIN table2  
  4. ON table1.column_name=table2.column_name;  

Q. 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. 
    1. SQL LEFT JOIN Syntax  
    2. SELECT column_name(s)  
    3. FROM table1  
    4. LEFT JOIN table2  
    5. ON table1.column_name=table2.column_name;  
  • SQL RIGHT JOIN

    The right join returns all the rows in the right table i.e. table 2 with the matching ones on the left table (table1).
    1. SELECT column_name(s)  
    2. FROM table1  
    3. RIGHT JOIN table2  
    4. ON table1.column_name=table2.column_name;  
  • SQL FULL OUTER

    The full join returns all rows from the left table (table1) and from the right table (table2).
    1. SELECT column_name(s)  
    2. FROM table1  
    3. FULL OUTER JOIN table2  
    4. ON table1.column_name=table2.column_name;  

Q. 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.
    1. SELECT column_name(s) FROM table1  
    2. UNION  
    3. SELECT column_name(s) FROM table2;  
  • SQL UNION ALL
    1. SQL UNION ALL Syntax  
    2. SELECT column_name(s) FROM table1  
    3. UNION ALL  
    4. SELECT column_name(s) FROM table2;  
    Allows duplicate values.
Here is a detailed article: Union vs Union All in SQL Server
 

Q: 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 

    CREATE INDEX [ CLUSTERED | NONCLUSTERED ] PIndex ON Persons (LastName,FirstName)

    Lea

Q: 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 every time it is called.

  • 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 a single value or a table. We can’t use functions  to Insert, Update, Delete records in the database table(s). It is compiled every time it is 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 Differences

  • 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 retention and reusing 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.
 

Q. What are Cursors and why do we use Cursors in SQL Server?

 
A SQL cursor is a database object which is used to retrieve data from a result set one row at a time. A SQL cursor is used when the data needs to be updated row by row.
 
In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with one row at a time rather than the entire result set at once. This can be done using cursors. 
 
Here is a detailed article on Cursors in SQL Server
 

Q. What are triggers and when to use a trigger

 
A trigger is a special kind of Stored Procedure or stored program that is automatically fired or executed when some event (insert, delete and update) occurs.
 
If you write a trigger for an insert operation on a table, after firing the trigger, it creates a table named “INSERTED” in memory. Then it does the insert operation and then the statements inside the trigger executes. We can query the “INSERTED” table to manipulate or use the inserted row(s) from the trigger. Similarly, if you write a trigger for a delete operation on a table, it creates a table in memory named “DELETED” and then deletes the row.
 
We use a trigger when we want some event to happen automatically on certain desirable scenarios.
 
Here is a detailed article on Triggers in SQL Server. 
 

Q. What are Views in SQL Server

 
Views are database objects which are like virtual tables that have no physical storage and contains data from one table or multiple tables. A View does not have any physical storage so they do not contain any data. When we update, insert or apply any operation over the View then these operations are applied to the table(s) on which the view was created.
 
There are two types of views: 
  1. System View
  2. User Define View
User Defined Views are important so I describe only User Defined Views. They are of two types:
  1. Simple View
  2. Complex view
Here is a detailed article on Views in SQL Server.
 

Q. How to find Nth Highest Salary in SQL?

 
To find out the Nth highest salary (for example: here I am finding 3rd highest salary), I wrote the query like below
  1. SELECT TOP 1 Salary AS 'Higest Salary',Name FROM (SELECT DISTINCT TOP 3 Salary,Name FROM tblSalary ORDER BY Salary DESC) a ORDER BY Salary ASC
Here is a detailed article on Finding Nth Highest Salary In SQL
 

Q. What is the Difference Between Count and Count_BIG.

 
Count Function returns value that data type is INT. Count_Big Function returns value that data type is BIG_INT.
 
 

Q. What is the difference between CHAR and VARCHAR datatype?

 
The CHAR data type:
  • It is a fixed length data type
  • Used to store non-Unicode characters
  • Occupiers 1 byte of space for each character
About the VARCHAR data type:
  • It is a variable length data type
  • Used to store non-Unicode characters
  • Occupies 1 byte of space for each character
 

Q. What is the difference between NCHAR and NVARCHAR datatype?

 
The NCHAR data type:
  • Is a fixed length data type
  • Used to store Unicode characters (for example the languages Arabic, German and so on)
  • Occupies 2 bytes of space for each character
The NVARCHAR data type:
  • It is a variable-length data type
  • Used to store Unicode characters
  • Occupies 2 bytes of space for each character
 

Q. What is Cast() and Convert() Functions in SQL Server?

 
The Cast() function is used to convert a data type variable or data from one data type to another data type. The Cast() function provides a data type to a dynamic parameter (?) or a NULL value.
 
Syntax
 
CAST ( [Expression]
AS Datatype)
 
When you convert expressions from one type to another, in many cases there will be a need within a stored procedure or other routine to convert data from a datetime type to a varchar type. The Convert function is used for such things. The CONVERT() function can be used to display date/time data in various formats.
 
Syntax
 
CONVERT(data_type(length), expression, style)
 
 

Q. What is Maximum Limit Value For Integer Data Type?

 
The Bigint data type represents an integer value. It can be stored in 8 bytes.
 
Formula
 
2^(n-1) is the formula of the maximum value of a Bigint data type.
 
In the preceding formula N is the size of the data type. The ^ operator calculates the power of the value.
 
Int represents an integer value that can be stored in 4 bytes. INT is the short form of integer.
 
Formula
 
2^(n-1) is the formula to find the maximum of an INT data type.
 
In the preceding formula N is the size of data type. The ^ operator calculates the power of the value.
 
 

Q. What is Commit and Rollback Commands in SQL Server?

 
Commit is used for permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.
 
Syntax
 
begin tran tranName
Command for operation
commit tran tranName
 
Rollback is used to undo the changes made by any command but only before a commit is done. We can't Rollback data which has been committed in the database with the help of the commit keyword.
 
Syntax
 
begin tran tranName
Command for operation
Rollback tran tranName
 
 

Q. What is Scalar Value Function in SQL Server?

 
A Scalar-valued function in SQL Server 2012 is used to return a single value of any T-SQL data type. A CREATE FUNCTION statement is used to create a Scalar-valued function. The name of the function should not be more than 128 characters. It is not a rule but it is conventional that the name of the function should begin with the prefix fn.
 
Up to 1024 input parameters can be defined for Scalar-valued functions. A Scalar-valued function however cannot contain an output parameter. The value is returned by a Scalar-valued function using the RETURNS clause.
 
 

Q. What is Pivot And Unpivot In SQL Server?

 
PIVOT and UNPIVOT are two relational operators that are used to convert a table expression into another. PIVOT is used when we want to transfer data from row level to column level and UNPIVOT is used when we want to convert data from column level to row level. PIVOT and UNPIVOT relational operators are used to generate a multidimensional reporting. Today we will discuss both the operators. PIVOT and UNPIVOT relational operators are used to generate an interactive table that quickly combines and compares large amount of data.
 
 

Q. What is User Defined Table Types And Table Valued Parameters?

 
The concepts of User-Defined Table Types (UDTTs) and Table-Valued Parameters (TVPs) were introduced in SQL Server 2008. Before SQL Server 2008, it was not possible to pass a table variable in stored procedure as a parameter, after SQL Server now we can pass Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.
 
Before passing the table variable, first we need to create a user-defined table variable. So now we create a user-defined table type.
 
 

Q. Explain TRY_PARSE, TRY_CONVERT And TRY_CAST

 
TRY_PARSE
 
It converts string data type to target data type(Date or Numeric). For example, source data is string type and we need to convert to date type. If conversion attempt fails it returns NULL value.
 
TRY_CONVERT
 
It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need date/integer format. Then this will help us to achieve the same.
 
TRY_CAST
 
It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need it in double/integer format. Then this will help us in achieving it.
 
 

Q. What @@FETCH_STATUS Function do in SQL Server?

 
To find the most recent FETCH statement in SQL Server 2012 we use the @@FETCH_STATUS system function. We can use the @@FETCH_STATUS system function with a while loop in SQL Server 2012. The @@FETCH_STATUS system function returns three values in SQL Server 2012 which are explained below.
 
When @@FETCH_STATUS system function returns 0 the FETCH is successful and it is equal to zero.
When @@FETCH_STATUS system function returns -1 the FETCH was unsuccessful.
When @@FETCH_STATUS system function returns -2 the FETCH was unsuccessful because the row was deleted.
 
 

Q. Difference Between Row_Number() Rank() And Dense_Rank()

 
Row_number plays a very important role in SQL server. Row_Number function can help to perform more complex ordering of row in the report format that allow the over clause in SQL standard.
 
Syntax
 
ROW_NUMBER () OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)
 
Rank() Function function will assign a unique value to each distinct Row, but it leaves a group between the groups.
 
Dense_Rank() Function is similar to Rank with only difference, this will not leave gaps between groups.
 
 

Q. What is the use of @@IDENTITY and SCOPE_IDENTITY?

 
@@IDENTITY will return the last identity value entered into a table. @@IDENTITY is not limited to a specific scope. Suppose we create a table and the set identity value to true for a column in the table. After that when we insert data into table we get the last identity value using @@IDENTITY. If the statement did not affect any tables with identity columns, then @@IDENTITY returns NULL. You can also access the @@IDENTITY value in your application.
 
SCOPE_IDENTITY returns the last identity values that were generated in any table in the current session. You will always get the value that was last inserted by your insert statement in the identity column, regardless of whether the insertion happens with your insert statements in any table or you execute any procedure that is doing any insertion operation in any table.
 
 

Q. Difference between After Trigger vs Instead of Trigger

 
After Trigger fire after the execution of an action query that can be either DDL statements like Create, Alter and Drop or DML statements like Insert, Update and Delete.
 
Instead of Trigger triggers fire before the execution of an action query that can only be DML statements like Insert, Update and Delete but after the execution of that query. The table data will not be affected, in other words if you want to insert or update the data of the table then you need to write it in the trigger using "inserted" or "deleted" virtual tables.
 
 

Q. Difference Between IsNull() and Coalesce() Functions?

 
The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments.
 
The Coalesce() function returns the first non-null value among its arguments. This function doesn't limit the number of arguments, but they must all be of the same data type.
 
 

Q. What is Clustered Index and Non-Clustered Index?

 
In SQL Server the clustered indexes are a critical consideration in the overall architecture of the database. They are often overlooked, misunderstood or, if the database is small, considered unimportant. They determine the logical order in which table data is stored because the leaf/lower level of the clustered index consists of the actual data pages of the table. A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key.
 
Non-Clustered indexes are stored separate from the table. They are created outside of the database table and contain a sorted list of references to the table itself. In SQL Server 2005 and earlier, a maximum of 249 non-clustered indexes could be created on a table but now in SQL Server 2008 that limit has been increased and now 999 non-clustered indexes can be created on a single table. Non-clustered indexes are sorting of the columns not copies of the table, you specify that "point" back to the data pages in the clustered index. This is why the clustered index you choose is so important because if effects all other indexes.
 
Read more here

Q. Explain Cross Apply And Outer Apply

 
CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. It other words, result of CROSS APPLY doesn’t contain any row of left side table expression for which no result is obtained from right side table expression. CROSS APPLY work as a row by row INNER JOIN.
 
OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function. OUTER APPLY work as LEFT OUTER JOIN.
 
 

Q. What is Temporary Table in SQL Server

 
Temporary tables are tables that are available only to the session that created them.
 
These tables are automatically destroyed at the termination of the procedure or session that created them.
 
Use of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in the current session.
 
Temporary tables are created using the same syntax as a CREATE TABLE except the table name starts with a '#' sign. When the table consists of a single '#' sign, it is defined as a local temporary table and its scope is limited to the session it is created in.
 
 

Q. How can you delete data from tables of a single database

 
The Stored Procedure "sp_MSforeachtable" allows us to easily process some code against each and every table in a single database. It means that it is used to process a single T-SQL command or a number of different T-SQL commands against every table in the database.
 
 

Q. Explain Row_Number function?

 
Ranking functions provide a very good feature of assigning numbering to the records in the result set in SQL. Row_Number is one of these functions available in SQL Server that allows us to assign rankings or numbering to the rows of the result set data. Different values are assigned to different rows, based on the type of ranking function used.
 
This function works by assigning continuous ranking to the records, without skipping any number in the result set, whether it is partitioned or not. At the end of the discussion, we will see what we mean by continuous ranking and not skipping any record.
 
 

Q. What is Surrogate Key in SQL Server?

 
A Surrogate Key in SQL Server is a unique identifier for each row in the table. It is just a key. Using this key we can identify a unique row. There is no business meaning for Surrogate Keys. This type of key is either database generated or generated via another application (not supplied by user).
 
A Surrogate Key is just a unique identifier for each row and it may use as a Primary Key. There is only requirement for a surrogate Primary Key, which is that each row must have a unique value for that column. A Surrogate Key is also known as an artificial key or identity key. It can be used in data warehouses.
 
 

Q. How To Recover Deleted Rows In SQL Server?

 
We can recover deleted rows if we know the time when data is deleted We can achieve this goal using LSN ( Log Sequence Numbers ). As per Microsoft, “Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN)". We will use these LSNs to recover our deleted data.
 
To recover deleted rows from the table in database, database must be FULL Recovery or BULK-LOGGED Recovery Model.
 
 

Q. What is Normalization and its Types?

 
Sometimes, a database design that looks OK at first sight may have some hidden problems. One such kind of problem is Non-Atomic values. This means that the value stored in a single column in the database is actually a combination of multiple values which makes the database harder to work with because you’ll always need expressions to extract the individual value. And it comes with major impact to the performance as well.
 
A database design suffers from redundancy if it allows multiple copies of the same fact (data) to be stored, which is bad for several reasons, as mentioned below -
  • Same data is stored multiple places
  • Storage space is wasted
  • Performance Impact
  • The possibility of Conflicting Data (Data Inconsistency)
The third problem that may harm in the database is Modification Anomalies.
 
Read more here 

Q. What is Lock in SQL Server?

 
As we all know, multiple users need to access databases concurrently. So locks come into the picture to prevent data from being corrupted or invalidated when multiple users try to do operations such as read, write and update on database.
 
“Lock is defined as a mechanism to ensure data integrity, consistency while allowing concurrent access to data. It is used to implement concurrency control when multiple users access Database to manipulate its data at the same time”
 
 

Q. What is ACID Property?

 
For any business, transactions that may be comprised of many individual operations and even other transactions, play a key role.
Transactions are essential for maintaining data integrity, both for multiple related operations and when multiple users that update the database concurrently.
 
A transaction is characterized by four properties, often referred to as the ACID properties: atomicity, consistency, isolation, and durability.
 
Read more here What is ACID Property?
 

Q. Why Is Isolation Level Required?

 
Isolation is one of the properties of SQL Transaction. Isolating / separating transactions from each other to maintain Data Integrity in Database is called Isolation.
 
Before going to implementation part of isolation, we will understand why isolation is required in database?
 
While developing large enterprise/public networking kind of applications where a huge number of users access same Database, same Table and at the same Time, Data concurrency situation may occur. We will discuss this situation into 4 parts:
  • Loss of Data
  • Dirty Read
  • Phantom Read
  • Inconsistency Analysis
 

Q. What is difference between Delete, Truncate, and Drop?

 
The difference between TRUNCATE, DELETE, and DROP is one of the most common interview questions. Here are some of the common differences between them.
 
 

Q. What is SQL Injection and how you prevent it?

 
Many vulnerabilities exist allowing hackers to steal data from organizations and SQL Injection is one of them. It is perhaps one of the most common application layer attack techniques used today. When improper coding of the web application is done then a hacker can inject into SQL commands. By using SQL commands a hacker can steal your data, they can modify your details and they can delete your data permanently.
 
In simple terms, SQL injection is nothing but it a technique where malicious users can inject SQL commands into an SQL statement, via webpage input and this input can break the security of the web application.
 
Learn SQL Injection here:
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?
 
If you have any questions, then please mention it in the comments section.
 
Read this article in my blog here.
 
Background
 
I am a .NET developer. As a .NET developer, there are so many things that I must be aware of besides .NET. I am sharing those in the form of articles, you can always read my other interview questions here in the following links.