Structure Query Language (SQL) still continues to remain the most ubiquitous database programming language, especially for established companies. Individuals with SQL skills remain all-time favourites for headhunters and recruiters. If you got a call from one of such recruiters, then it is necessary to put together yourself for the upcoming SQL interview. You can ace the interview by understanding the nuances of some of the most frequently asked SQL interview questions and Answers for 2021.
Some of the frequently asked SQL Interview Questions are:
| Sr. No. | Questions |
| 1. | How do you explain SQL? |
| 2. | How many different types of SQL queries exist? |
| 3. | What are the major differences between DELETE and TRUNCATE commands? |
| 4. | Explain major differences between Functions and Procedures? |
| 5. | Differentiate between RANK,DENSE_RANK,ROW_NUMBER and NTILE ? |
- How do you explain SQL?
SQL is the abbreviation for Structured Query Language. As the name suggests, this language uses a set of queries to create, access or manipulate data available in a database.
- How many different types of SQL queries exist?
Five different types queries exist in SQL:
- Data Query Language (DQL) – Database schema is defined using DDL. Includes Select statement, Join statements, Subqueries.
- Data Manipulation Language (DML) – Database modifications can be done using DML. Includes statements like INSERT, UPDATE, DELETE.
- Data Definition Language (DDL) – Database schema is defined using DDL. Includes statements like CREATE, ALTER, DROP, TRUNCATE.
- Data Control Language (DCL) – Permissions and Rights are provided using DCL. Includes commands like GRANT, REVOKE, DENY.
- Transaction Control Language (TCL) – These are the commands dealing with the transaction within the database.
- What are the major differences between DELETE and TRUNCATE commands?
| DELETE | TRUNCATE |
| The DELETE command can be used to delete only specific number of rows from a table | All the rows from the table are deleted using TRUNCATE command |
| It is a DML (Data Manipulation Language) type of query. | It is a DDL(Data Definition Language) type of query. |
| DELETE command can be used with a WHERE Clause to delete specific records. | TRUNCATE command cannot be used with a WHERE clause. |
| The DELETE statement removes rows one by one and a transaction log for each deleted row is recorded. | TRUNCATE command removes records from the table by deallocating the data pages used to store the table data and only the transaction log of page deallocations is recorded. |
| Since transaction logs for each row are generated which is why DELETE command is slower. | No transaction logs are generated for the rows which is why TRUNCATE statements work faster. |
| Deleted records can be rolled back. | Truncated records cannot be rolled back because of the non-availability of transaction logs. |
| Identity of column is retained after using DELETE Statement on table. | In case a table contains an Identity column, then the identity of the column is reset to its initial value. |
| The delete can be used with indexed views. | Truncate cannot be used with indexed views. |
- Explain major differences between Functions and Procedures?
| Stored Procedure (SP) | Function (UDF – User Defined Function) |
| Stored Procedures can return zero, single or multiple values. | Function must return a single scalar or a table value. |
| Transaction can be used in a Stored Procedure. | Transactions can not be used in a UDF. |
| SP can have both input as well as output parameters. | UDF can only have input parameters. |
| A UDF can be called from a Stored Procedure. | A Stored Procedure can not be called from a UDF. |
| A stored procedure can not be used in a SELECT, WHERE or HAVING statements. | UDF can be easily used in a SELECT, WHERE or HAVING statements. |
| Exception handling using Try-Catch block can be implemented in a Stored Procedure. | Try-Catch block can not be used in a UDF. |
- Differentiate between ROW_NUMBER, RANK, DENSE_RANK, and NTILE ?
ROW_NUMBER – Once the result set is partitioned, this function returns the sequential number of a row within that partition. No gap in the ranking will exist i.e., the rank of a row is one plus the number of distinct ranks that come before the row in the discussion.
RANK – Once the result set is partitioned, this function returns the rank of each row within that partition. Gaps in the ranking can exist. If the values are the same then the same rank is provided to both the values and hence the rank of a row is one plus the number of distinct ranks that come before the row in the discussion.
DENSE_RANK – This function returns the rank of rows within the partition of a result set, No gap in the ranking will exist i.e. the rank of a row is one plus the number of distinct ranks that come before the row in the discussion.
NTILE – This function distributes the rows in an ordered partition into a specified number of groups. Numbering of the groups starting with one. The number of the group is returned by NTILE to which each row belongs.
Image representing functions applied on the grades of 6 individuals
These frequently asked SQL interview questions will provide a quick refresher for your skillset. We hope that you were able to benefit from the blog and have greater clarity on the kind of SQL interview questions you can expect in your upcoming interviews.