Prepare for your next SQL interview with this list of 35+ must-know questions and answers. This article covers a wide range of SQL topics, from basic SELECT statements to advanced JOIN operations. By reading this article, you'll be well on your way to acing your next SQL interview
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 35+ popular SQL Interview Questions and their answers.
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.
There are four types of joins in SQL.
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;
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;
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.
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
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
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.
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.
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:
User Defined Views are essential, so I describe only User Defined Views. They are two types.
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
Count Function returns the value that the data type is INT. Count_Big Function returns a value that data type is BIG_INT.
The CHAR data type.
About the VARCHAR data type.
The NCHAR data type.
The NVARCHAR data type:
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)
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.
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
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.
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.
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.
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.
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.
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)
@@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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 third problem that may harm the database is Modification Anomalies.
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."
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.
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.
The difference between TRUNCATE, DELETE, and DROP is among the most common interview questions. Here are some of the common differences between them.
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.
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.
#sql