Introduction
Most software applications use some database and Relational Database Management Systems RDBMS) are the most popular among all DBMSs. As a software developer, most companies expect you to know SQL. SQL is the language used to work with relational databases such as SQL Server, MySQL, and Oracle. Here is a list of the top 40 popular SQL Interview Questions and their answers.
Q. What is SQL?
Structured Query Language (SQL) is used in relational database management systems (RDBMS) to query, update, and delete data. SQL is a standard query language for RDBMS. SQL language queries are also known as SQL commands or SQL statements. There are four types of joins in SQL.
If you are new to SQL, here is a must-read on SQL, What is SQL, and if you are familiar with SQL but want to learn more about SQL queries, check out The Most Important SQL Queries for Beginners.
Q. What are the types of Joins in SQL?
There are four types of joins in SQL.
- INNER JOIN: Returns all rows when there is at least one match in BOTH the tables.
- LEFT JOIN: Returns all rows from the left table and the matched rows from the right table.
- RIGHT JOIN: Returns all rows from the right table and the matched rows from the left table.
- FULL JOIN: Returns all rows when there is a match in ONE of the tables.
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 joins in SQL is INNER JOIN. Here is an example that is applied to two tables.
Example
SELECT column_name(s)
FROM table1
INNER JOIN table2
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 on the right side when there is no match.
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
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 (table 1).
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
SQL FULL OUTER
The full join returns all rows from the left table (table1) and the right table (table2).
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
Q. What are Union and Union All ? Explain the differences.
SQL UNION
The UNION operator combines 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.
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
SQL UNION ALL
SQL UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
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 the index is not by 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 a database compiled for the first time, and its execution plan is saved. The compiled code is executed when every time it is called.
Function
A function is a database object in SQL Server. It is a set of SQL statements that accepts only input parameters, performs actions, and returns the result. It is compiled every time it is invoked. The function can produce only a single value or a table. We can't use functions to Insert, Update, and Delete records in the database table(s).
Basic Difference
The 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, whereas procedures can have input/output parameters.
Functions can be called from the procedure, whereas Procedures cannot be called from the function.
Advanced Differences
- The procedure allows SELECT and DML(INSERT/UPDATE/DELETE) statements, whereas the function allows only SELECT statements in it.
- Procedures cannot be utilized in a SELECT statement, whereas functions can be embedded in a SELECT statement.
- Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas functions can be.
- The most crucial feature of stored procedures over function is retention and reusing the execution plan, while in the case of a 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 Functions can be considered views that take parameters and can be used in JOINS and other Rowset operations.
- A try-catch block can handle an exception in a procedure, whereas a try-catch block cannot be used in a Function.
- We can use transactions in stored procedures but not in functions.
Here is a detailed article Difference between Stored Procedures and Functions in SQL Server
Q. What are Cursors, and why do we use Cursors in SQL Server?
A SQL cursor is a database object 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 called a result set. Sometimes the application logic must work with one row at a time rather than the entire result set simultaneously. 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, it creates a table named "INSERTED" in memory after firing the trigger. Then it does the insert operation, and the statements inside the trigger execute. We can query the "INSERTED" table to manipulate or use the inserted row(s) from the trigger. Similarly, writing a trigger for a delete operation on a table creates a table in memory named "DELETED" and then deletes the row.
We use a trigger when we want some event to happen automatically in certain desirable scenarios.
Here is a detailed article on Triggers in SQL Server.
Q. What are Views in SQL Server
Views are database objects like virtual tables with no physical stores and containing data from one table or multiple tables. A View has no physical storage, so they do not contain any data. When we update, insert, or apply any operation over the View, these operations are applied to the table(s) on which the view was created.
There are two types of views:
- System View
- User Define View
User Defined Views are essential, so I describe only User Defined Views. They are two types.
- Simple View
- Complex view
Here is a detailed article on Views in SQL Server.
Q. How to find Nth Highest Salary in SQL?
I wrote the query below to find out the Nth highest salary (for example: here I am finding the 3rd highest salary).
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 the value that the data type is INT. Count_Big Function returns a value that data type is BIG_INT.
Learn more here Difference Between Count and Count_BIG
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
Read more here Difference between CHAR and VARCHAR datatype
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
Learn more here Difference between NCHAR and NVARCHAR datatype
Q. What are 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)
The CONVERT() function can display date/time data in various formats. When you convert expressions from one type to another, there will often be a need within a stored procedure or another routine to convert data from a DateTime type to a varchar type. The Convert function is used for such things.
Syntax
CONVERT(data_type(length), expression, style)
Read more Cast() and Convert() Functions in SQL Server
Q. What is the 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 an 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 the data type. The ^ operator calculates the power of the value.
Read more here Maximum Limit Value For Integer Data Type
Q. What are Commit and Rollback Commands in SQL Server?
Commit is used for permanent changes. When we use Commit in any query, 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 that has been committed in the database with the help of the commit keyword.
Syntax
begin tran tranName
Command for operation
Rollback tran tranName
Read more here Commit and Rollback Commands in SQL Server
Q. What is Scalar Value Function in SQL Server?
A Scalar-valued function in SQL Server 2012 returns 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 conventionally, the function's name should begin with the prefix fn.
The value is returned by a Scalar-valued function using the RETURNS clause. Up to 1024 input parameters can be defined for Scalar-valued functions. A Scalar-valued function, however, cannot contain an output parameter.
Read more here Scalar Value Function in SQL Server
Q. What are Pivot And Unpivot In SQL Server?
PIVOT and UNPIVOT are two relational operators used to convert a table expression into another. PIVOT is used when we want to transfer data from the row level to the column level, and UNPIVOT is used to convert data from the column level to the row level. PIVOT and UNPIVOT relational operators are used to generate multidimensional reporting. Today we will discuss both operators. PIVOT and UNPIVOT relational operators generate an interactive table that quickly combines and compares a large amount of data.
Read more here https://www.c-sharpcorner.com/UploadFile/f0b2ed/pivot-and-unpovit-in-sql-server/.
Q. What are 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 a 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, we first need to create a user-defined table variable. So now we create a user-defined table type.
Read more User Defined Table Types And Table Valued Parameters
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 a string type, and we must convert it to a date type. If the conversion attempt fails, it returns a NULL value.
TRY_CONVERT
It converts the value to a specified data type and returns NULL if conversion fails; for example, the source value is in string format and needs date/integer format. Then this will help us to achieve the same.
TRY_CAST
It converts the value to a specified data type and returns NULL if conversion fails; for example, the source value is in string format, and we need it in double/integer format. Then this will help us in achieving it.
Read more here TRY_PARSE, TRY_CONVERT And TRY_CAST in SQL Server
Q. What does @@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 the @@FETCH_STATUS system function returns 0, the FETCH is prosperous and equal to zero.
When the @@FETCH_STATUS system function returns -1, the FETCH is unsuccessful.
When the @@FETCH_STATUS system function returns -2, the FETCH is unsuccessful because the row was deleted.
Read more here @@FETCH_STATUS function do in SQL Server
Q. Difference Between Row_Number() Rank() And Dense_Rank()
Row_number plays a very important role in SQL server. Row_Number function can help perform more complex row ordering in the report format, allowing the over a clause in SQL standard.
Syntax
ROW_NUMBER () OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)
- Rank() Function- The function will assign a unique value to each distinct Row, but it leaves a group between the groups.
- Dense_Rank()- The function is similar to Rank with only one difference. This will not leave gaps between groups.
Read more here Difference Between Row_Number() Rank() And Dense_Rank()
Q. What is the use of @@IDENTITY and SCOPE_IDENTITY?
@@IDENTITY is not limited to a specific scope. @@IDENTITY will return the last identity value entered into a table. Suppose we create a table and set the identity value to true for a column in the table. After that, when we insert data into the 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 generated in any table in the current session. You will always get the value last inserted by your insert statement in the identity column, regardless of whether the insertion happens with your insert statements in any table or if you execute any procedure that does any insertion operation in any table.
Read more here. What is the use of @@IDENTITY and SCOPE_IDENTITY
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, 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.
Read more Difference between After Trigger vs Instead of Trigger
Q. Difference Between IsNull() and Coalesce() Functions?
The ISNULL() function replaces 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; they must all be of the same data type.
Read More here Difference Between IsNull() and Coalesce() Functions
Q. What are 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 considered unimportant if the database is small. 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 table's data rows or views in order based on the clustered index key.
Non-Clustered indexes are stored separately from the table. They are created outside 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. The clustered index you choose is so important because it affects all other indexes.
Read more here
Q. Explain Cross Apply And Outer Apply
CROSS APPLY for work as a row-by-row INNER JOIN. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. In other words, the 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.
OUTER APPLY returns both rows that produce a result set and those that do not, with NULL values in the columns produced by the table-valued function. OUTER APPLY for work as LEFT OUTER JOIN.
Read more here Cross Apply And Outer Apply in SQL Server
Q. What is Temporary Table in SQL Server
Temporary tables 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.
The 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.
Read more here Temporary Table in SQL Server.
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 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.
Read more How can you delete data from tables of a single database
Q. Explain the Row_Number function?
Ranking functions provide an outstanding 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 numbers 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 a 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.
Read more here Row_Number function in SQL Server
Q. What is Surrogate Key in SQL Server?
A Surrogate Key in SQL Server is a unique identifier for each row in the table. Using this key, we can identify an individual row. There is no business meaning for Surrogate Keys. It is just a key. This key type is either database generated or generated via another application (not supplied by the user).
A Surrogate Key is a unique identifier for each row and may be used as a Primary Key. There is only one requirement for a surrogate Primary Key: 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.
Read more here What is Surrogate Key in SQL Server
Q. How To Recover Deleted Rows In SQL Server?
We can recover deleted rows if we know when data is deleted. We can achieve this goal using LSN ( Log Sequence Numbers ). Microsoft says, "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 the database, the database must be FULL Recovery or BULK-LOGGED Recovery Model.
Read more here How To Recover Deleted Rows In SQL Server
Q. What is Normalization and its Types?
Sometimes, a database design that looks OK at first sight may have 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 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 a major impact on 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 -
- The same data is stored in multiple places
- Storage space is wasted
- Performance Impact
- The possibility of Conflicting Data (Data Inconsistency)
The third problem that may harm 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 the database.
"Lock is defined as a mechanism to ensure data integrity and consistency while allowing concurrent access to data. It is used to implement concurrency control when multiple users access a Database to manipulate its data simultaneously."
Read more here What is Lock in SQL Server?
Q. What is ACID Property?
For any business, transactions comprising many individual operations and even other transactions play a key role.
Transactions are essential for maintaining data integrity for multiple related operations and when multiple users update the database concurrently.
Four properties characterize a transaction, often called 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 Transactions. Isolating/separating transactions from each other to maintain Data Integrity in Database is called isolation.
Before going to the implementation part of isolation, we will understand why isolation is required in the database.
While developing significant enterprise/public networking kind of applications where many users access the same Database and the same Table, at the same time, Data concurrency situations may occur. We will discuss this situation in 4 parts.
- Loss of Data
- Dirty Read
- Phantom Read
- Inconsistency Analysis
Read more here. Using isolation level in SQL transactions
Q. What is the difference between Delete, Truncate, and Drop?
The difference between TRUNCATE, DELETE, and DROP is among the most common interview questions. Here are some of the common differences between them.
Read more here. What is the difference between Delete, Truncate, and Drop?
Q. What is SQL Injection, and how do 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 today's most common application layer attack techniques. When improper web application coding is done, a hacker can inject into SQL commands. Using SQL commands, a hacker can steal your data, modify your details, and delete your data permanently.
In simple terms, SQL injection is nothing but 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
I hope you liked this article. Did I miss anything that you may think is needed? Could you find this post helpful? Please share me your valuable suggestions and feedback.
Your turn. What do you think?
If you have any questions, please mention them in the comments section.
Please 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.
So, shall we now discuss SQL interview questions?
More Interview Questions