Top 50 Most Asked SQL Interview Questions Part 1

Tutorialsrack 04/06/2019 SQL

If you are preparing for a SQL developer interview. Then, you are at the right place. RDBMS is one of the most commonly used databases in the software world. That's why SQL skills play a vital role for a software developer. In this post, we covered the top 50 most commonly asked SQL interview questions and answers which are helpful for fresher’s as well experienced candidates.

You can go through these questions for a quick revision of major SQL concepts before appearing for an interview. 

SQL Interview Questions

Q1. What is SQL?

Answer:  SQL stands for Structured Query Language. SQL is used for storing, modifying and retrieving data in databases. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database or retrieve data from a database.

Q2. What is RDBMS?

Answer: RDBMS stands for Relational Database Management System. (RDBMS) is a database management system (DBMS) based on the relational model of data which is for storing in data in a tabular form. RDBMS system supports a tabular structure of data and a relationship between them to access stored data or information.
Example of RDBMS, SQL SERVER, MySQL, PostgreSQL, SQL, etc. 

Q3. What are the different subsets of SQL? Or What are different types of statements supported by SQL?

Answer: There are three significant subsets of the SQL:

  • DDL (Data Definition Language): DDL actually consists of the SQL commands that can be used to manipulation of data present in the database. CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME are the following SQL commands which are used for defining the database schema and used to create and modify the structure of database objects in a database.
  • DML (Data Manipulation Language): DML actually consists of the SQL commands that can be used to define the database schema. SELECT, INSERT, UPDATE, DELETE are the following SQL commands which are used for manipulation of data in a database.
  • DCL (Data Control Language): DCL actually Consists of SQL Commands which can be used for rights, permissions and other controls of a database system. GRANT and REVOKE are the following SQL commands.

Q4. What are the two authentication modes in SQL Server?

Answer: These two authentication modes in SQL Server are as follow:

  • Windows Authentication.
  • SQL Server Authentication.

Q5. What is the difference between DELETE and TRUNCATE statement?

Answer: The differences between DELETE and TRUNCATE statement are as follow:

DELETE is a DML command. TRUNCATE is a DDL command.
DELETE statement supports WHERE clause. TRUNCATE statement does not support WHERE clause.
If your table column referenced by foreign key then you can delete rows if there is no reference to the row. If your table column referenced by foreign key then you can’t TRUNCATE the table even if the referred table is empty.
DELETE statement does not reset the identity column to its initial value. TRUNCATE Statement reset the identity column to its initial value.
DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE table always locks the table and page but not each row. As it removes all the data.
DELETE activates a trigger because the operation is logged individually. TRUNCATE table cannot activate a trigger because the operation does not log individual row deletions.
Performance wise, DELETE is Slower than TRUNCATE, because it Keeps logs. Performance wise, TRUNCATE is faster than DELETE, because it doesn’t Keep logs.

Q6. What does UNION do? What is the difference between UNION and UNION ALL?

Answer: SQL UNION clause/operator is used to combine the result set of two or more SELECT statements and only returned unique records/rows. 

Difference between UNION and UNION ALL:

UNION and UNION ALL Both used to combine the result-set of two or more SELECT statements. The Only difference between both UNION and UNION ALL is that UNION returns distinct records while UNION ALL returns all the records.

Q7. What are the differences between local and global temporary tables?

Answer: There are two types of temporary table in SQL as follow:

  1. Local temporary table
  2. Global temporary table

Difference between Local temporary table and Global temporary table:

Local temporary table Global temporary table
Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed.
Local temporary table name is stared with a single hash ("#") sign. Global temporary table name is stared with double hash ("##") sign.
Stored in temp database. Stored in temp database.
Automatically get deleted when a user disconnects or can be deleted using drop command Automatically get deleted when all referencing connections closed or can be deleted manually by using DROP command.

Q8. What Is SQL Profiler?

Answer: SQL Server Profiler is a tool to create and manage traces and analyze and replay trace results. In other words, SQL Profiler is a tool that enables you to monitor events within your SQL Server (or more specifically, a SQL Server instance). Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem.

Q9. What do you mean by table and field in SQL?


TABLE: A table is a collection of related data held in a table format within a database. It consists of columns and rows.

Field: A Field is referred to a no. of columns specified in a table.

For example, a table named “tbl_StudentInfo” having various columns such as StudentName, StudentAge, Student_RollNo, etc. These columns are termed as Field in a table.

Q10. What is a recursive stored procedure?

Answer: When a stored procedure can call itself until it reaches maximum boundary condition. This is known as the Recursive Store Procedure.

Q11. What are sub-query and its properties?

Answer: A query within a query is known as sub-query. The outer query is called as the main query, and inner query within parenthesis is called sub-query. Sub-query is always executed first, and the result of sub-query is passed on to the main query (outer query).

The properties of sub-query are as follow:

  • Sub-query must be enclosed in parenthesis.
  • Sub-query must be on the right side of the comparison operator.
  • Sub-query cannot contain an ORDER BY clause.
  • A query can contain more than one sub-query.

Q12. What are the types of sub-query?

Answer: The types of sub-query are as follow:

  • Single-row sub-queries: Return the outer query one row of results that consists of one column.
  • Multiple-row sub-queries: Return to the outer query more than one row of the results it requires the use of IN, ANY, ALL, or EXISTS operators. 
  • Correlated sub-queries: This is a type of nested subquery that uses columns from the outer query in its WHERE clause. A correlated subquery is evaluated once for each row.

Q13. What is a Primary Key?


  • SQL PRIMARY KEY is used to uniquely identify each row in a table.
  • SQL PRIMARY KEY Constraint ensures that only unique record is inserted in a table.
  • SQL PRIMARY KEY cannot contain NULL value, it must contain unique values.
  • A table can have only one PRIMARY KEY, which may consist of single or multiple fields.

Q14. What is a Unique Key?


  • SQL UNIQUE constraint is used to ensure that no duplicate value is inserted in a column.
  • In SQL, both UNIQUE and PRIMARY KEY constraint ensures that the uniqueness for a column or set of columns.
  • The main difference between UNIQUE and PRIMARY KEY is that you can have multiple UNIQUE constraints per table but only one PRIMARY KEY constraint per table.

Q15. What is a foreign key?


  • SQL FOREIGN KEY constraint is used to link two tables together and it is also termed as a referencing key.
  • SQL FOREIGN KEY constraint is a field or collections of fields in one table that refers to the PRIMARY KEY constraint in another table.
  • The table that contains PRIMARY KEY is termed as the parent table or referenced table, and a table containing the FOREIGN KEY is termed as the child table.

Q16. What is a JOIN?


  • JOIN Clause is used to Join two to more tables. It creates a set of rows in a temporary table.
  • JOIN Clause works on two or more tables but at least they have one common column field and have a relation between them.
  • A statement or query can contain one or multiple join operations.

Q17. What are the types of JOIN and explain each?

Answer: SQL has different types of Join as follow:

  • INNER JOIN: Returns records when there is at least one match on both the tables.
  • LEFT OUTER JOIN: Return all the records from the left table, and return matched records from the right table.
  • RIGHT OUTER JOIN: Return all the records from the right table, and return matched records from the left table.
  • FULL OUTER JOIN: Return all the records when there is a match either in a left table or right table.
  • CARTESIAN JOIN or CROSS JOIN: Returns the cartesian product of the sets of records from two or more joined tables.
  • SELF JOIN: It is a type of SQL JOIN which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY.

Q18. What is the VIEW?

Answer: A VIEW is a virtual table which consists of rows and column like a table and it does not physically exist. If the data changes in the underlying table, then the same changes are also reflected in the VIEW.

Q19. What is an INDEX?

Answer: Indexes are used to find rows with specific column values quickly. An INDEX allows you to find the specific row from a database without scanning the entire table. Indexes are created on table and views.

Q20. What is the difference between Clustered and Non-Clustered Index?

Answer: The difference between Cluster and Non-Cluster Index are as follow:

Clustered Index Non-Clustered Index
Clustered indexes sort and store the data rows in the table or view based on their key values. Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key-value entry has a pointer to the data row that contains the key value.
A table can only have one Clustered Index. A table can have multiple Non-Clustered Index. Prior to SQL Server 2008 only 249 Non-clustered Indexes can be created. With SQL Server 2008 and above 999 Non-clustered Indexes can be created.
A Primary Key constraint creates a Clustered Index by default. A Unique Key constraint created a Nonclustered Index by default.
Faster to read than non-clustered as data is physically stored in index order. Quicker for insert and update operations than a clustered index.

Q21. What is a query?

Answer: In SQL, a query is a request for data or information from a database table or combination of tables.

Q22.What is the difference between the WHERE and HAVING clauses?

Answer: The difference between the WHERE and HAVING clauses  are as follow:

  • WHERE Clause is used to filter Row Data whereas Having Clause is used to filter grouped data grouping made by GROUP BY clause.
  • WHERE Clause is used before grouping made whereas HAVING Clause is used on grouping made by GROUP BY clause
  • We cannot use aggregate functions with the WHERE clause whereas we can use aggregate functions with HAVING Clause

Note: When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

Q23. What is a stored procedure?

Answer: A stored procedure is a set of SQL statements with an assigned name, which are stored in a database as a group so it can be reused whenever we want.

Q24. What is the difference between stored procedure and function?

Answer: The difference between stored procedure and function are as follow:

Stored Procedure Function
Stored Procedure can return zero, single or multiple values. Function must return a value (which may be a scalar or a table).
Stored Procedure can have input/output parameter. Only input parameter.
We can use transaction in Stored Procedure. We can't use transaction in User Defined Functions.
We can call a function from a Stored Procedure. We can't call Stored Procedure from function.
We can't use Stored Procedure in SELECT/ WHERE/ HAVING statement. We can use User Defined Functions in SELECT/ WHERE/ HAVING statement.
We can use exception handling using Try-Catch block in Stored Procedure. We can't use Try-Catch block in User Defined Functions.
Stored Procedure can use temporary tables. Function can not use temporary tables.

Q25. What are the Advantages and Disadvantages of a Stored Procedure?


Advantages of the stored procedure:

  • Reusable and Transparent to any application.
  • Secure.
  • Reduce the data traffic between the application and database server.
  • Increase the performance of the application.

Disadvantages of the Stored procedure:

  • A large number of Logical operations increase CPU usage.
  • Difficult to Debug.
  • Not easy to Develop and Maintain.
  • Not designed for developing Complex or Flexible business logic.

Q26. What is a trigger?

Answer: A SQL trigger is a special type of stored procedure. A SQL trigger is a set of SQL statements stored in the database. A SQL trigger is executed or fired whenever an event associated with a table occurs, for example, insert, update or delete query fired on a table. It is special because it is not called directly like a stored procedure.

Q27. Can we insert a row for identity column implicitly?

Answer: Yes, we can. 



Q28. How can you use a CTE to return the fifth highest (or Nth highest) salary from a table?


with cte as
   select salary,DENSE_RANK() over (order by salary desc) as DenseRank from Employees
select top 1 salary from cte where DenseRank=5;

Q29. What is the use of @@SPID?

Answer: @@SPID Returns the session ID of the current user process.

For Example, This example returns the session ID, login name, and user name for the current user process. A query is as follow:

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name';

ID            Login Name                                           User Name
------      ------------------------------ ---          ------------------------------
55           LAPTOP-LH3JFI6M\GOD           dbo

Q30. What are Magic tables in SQL?

Answer: Magic Tables are the specially created table which is available only inside of a trigger when we perform the insert, update, and delete operations. The tables "INSERTED" and "DELETED" are called magic tables of the SQL Server.
We can not see these tables in the database. But we can access these tables from the "TRIGGER".

Q31. What will be query used to get the list of triggers in a database?

Answer: A query will be:

Select * from sys.objects where type='tr';

Q32. What is the difference between DROP and TRUNCATE?


TRUNCATE: Removes all rows from a table without logging the individual row deletions.

DROP: Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.

Q33. Are NULL values the same as that of zero or a blank space?

Answer: No, NULL values are not the same as that of ZERO or a blank space. NULL value represents an absence of value whereas Zero is a number value. It is definite with precise mathematical properties and blank space is a character or empty string value.

Q34. Where are SQL Server user names and passwords stored in SQL Server?

Answer: UserNames and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.

Q35. What is the difference between NVARCHAR and VARCHAR types?

Answer: The difference between NVARCHAR and VARCHAR types are as follow:

NVARCHAR stores UNICODE data. If you have requirements to store UNICODE or multilingual data, NVARCHAR is the choice. VARCHAR stores ASCII data and should be your data type of choice for normal use.
NVARCHAR uses 2 bytes per character. VARCHAR uses 1 byte per character.
Optional parameter n value can be from 1 to 4000.Can store maximum 4000 Unicode/Non-Unicode characters. Optional Parameter n value can be from 1 to 8000.Can store maximum 8000 Non-Unicode characters.
Max indicates that the maximum storage size is 2^31-1 bytes (2 GB). Max indicates that the maximum storage size is 2^31-1 bytes (2 GB).

Q36. What is the use of SET NOCOUNT ON/OFF statement?


SET NOCOUNT ON: This prevents the message from showing which contains the number of affected rows.

SET NOCOUNT OFF: This shows the number of affected rows in a message window.

Note: Using SET NOCOUNT ON within a stored procedure can improve the performance of the stored procedure by a significant margin.

Q37. What is CTE?

Answer: A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.

Q38. How can you create an empty table from an existing table?


Select * Into  From  Where 1 = 0;

Note: that this will not copy indexes, keys, etc.

Q39. What is the difference between GETDATE and SYSDATETIME in SQL Server?

Answer: Both GETDATE and SYSDATETIME are used to fetch the current date time of the server. There is a slight difference between GETDATE and SYSDATETIME is that:

For GETDATE, the precision is till milliseconds and in case of SYSDATETIME, the precision is till nanoseconds.

SELECT GETDATE() as 'GetDate Function', SYSDATETIME() as 'SysDateTime Function';

GetDate Function                               SysDateTime Function
---------------------------------            ----------------------------------------
2019-05-25 17:38:22.130            2019-05-25 17:38:22.1327587

Q40. How to fetch alternate records from a table?

Answer: Use these query to fetch the alternate records from a table

--For even:- 
 Select studentId from (Select rowID, studentId from student) where mod(rowID,2)=0;
 --or for odd:- 
 Select studentId from (Select rowID, studentId from student) where mod(rowID,2)=1;

Q41. What is the query used to fetch first 5 characters of the string?

Answer: The query used to fetch the first 5 characters of the string are as follow:

Example - Using SUBSTRING Function
Select SUBSTRING('Tutorialsrack',1,5) as 'First 5 Characters';

First 5 Characters

Example - Using LEFT Function
Select LEFT('Tutorialsrack',5) as 'First 5 Characters';

First 5 Characters

Q42. How many Aggregate Functions are available there in SQL?

Answer: These are the most common aggregate functions in SQL:

  • COUNT counts how many rows are in a particular column.
  • SUM adds together all the values in a particular column.
  • MIN and MAX return the lowest and highest values in a particular column, respectively.
  • AVG calculates the average of a group of selected values.

Q43. How do you get the last id without the max function?



select id from tableName order by id desc limit 1;

In SQL Server:

select top 1 id from tableName order by id desc;

Q44. What is SQL Injection?

Answer: SQL injection is one of the most common web hacking techniques. SQL Injection is a code injection technique which is used to attack data-driven applications. Using this technique, malicious SQL statements are inserted into an input field for execution using webpage input (e.g. to dump the database contents to the attacker).

Q45. How do you copy data from one table to another table?

Answer: If both tables are truly the same schema:

Example -  If both tables are truly the same schema
SELECT * FROM oldTable;

Otherwise, you'll have to specify the column names:

Example - Otherwise, you'll have to specify the column names
INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable;

Q46. How can we update the view?

Answer: Use this statement for Update views:

SELECT column_name(s)
FROM table_name
WHERE condition;

Q47. How do you update F as M and M as F from the below table TestTable?

Answer: Use this statement for updating F as M and M as F:

UPDATE TestTable SET Gender = CASE Gender WHEN 'F' THEN 'M' ELSE 'F' END;

Q48. What is the SQL CASE statement?


  • SQL CASE Statement has the functionality like IF-THEN-ELSE Statement.
  • If the condition is true, will return the result and if no condition is true, it returns the value in the ELSE clause.
  • If there is no ELSE part and no conditions are true, then it returns NULL.

Q49. How to get a list of all tables from a database?

Answer: Use this statement to get all the tables from a database:

SELECT * FROM sys.Tables;

Q50. What is the command used to Recompile the stored procedure at run time?



or we can include WITHRECOMPILE in the stored procedure itself.

I hope this article will help you to prepare for your SQL Interview. Share your valuable feedback and help us to improve. If you find anything incorrect, or you want to share more information about the topic discussed above. please post your comment at the bottom of this article. Thank you!

Related Posts


Recent Posts