SQL Interview Questions
This article explains the type of interview questions that can be asked for SQL-related job positions such as database developer, database administrator, full stack developer, or even for general programming positions.
Depending upon the experience level required, the SQL interview questions can be broadly divided into three categories.
- Questions for Entry-Level Positions (Required experience of 0–2 years)
- Questions for Mid-Level Positions (Required experience of 3–5 years)
- Questions for Advanced-Level Positions (Required experience > 5 years)
If you wish to further develop your SQL skills and apply for your dream job check out our SQL training courses.
Questions For Entry-Level Positions
For entry-level job positions, recruiters look for fresh graduates with no experience up to someone with an experience of 0–2 years. The questions asked for entry-level positions are normally very basic and easy to answer.
To give you an idea, the following are some of the most commonly asked SQL interview questions for entry-level positions.
Question 1:
What is a foreign key column in SQL Server?
Answer:
A foreign key in SQL Server is used to implement relationships between two tables in an SQL Server database. The primary key of one column is normally used as the foreign key in another column.
Question 2:
Which JOIN statement is used when you want to SELECT all records from the first table and only the matching records from the second table, used in a JOIN query?
Answer:
The LEFT JOIN statement is used in this scenario.
Question 3:
What are subqueries? Why are they used?
Answer:
A subquery can be defined as a query inside another query. The subquery is normally used in conjunction with the WHERE clause to filter records that will be used by the outer query. In other words, a subquery is used as a condition to filter records as they restrict the data on which the outer query will be applied.
Question 4
Can you use the “Name” column of a “Patient” table (containing patient’s records) as the unique key?
Answer:
Technically, any column, including the “Name” column which stores patients’ names, can be used as the primary or unique key column. However, logically, it is recommended that a column containing only unique values is used as the unique key column.
Question 5
Explain the difference between DELETE and TRUNCATE statements.
Answer:
The DELETE statement deletes rows one at a time and does not reset the identity key column. The TRUNCATE statement removes all rows by deallocating memory pages that stored the table records. The TRUNCATE statement also resets the identity key to its seed value.
If you really want to impress someone with your SQL Knowledge, read this guide on Union Queries & Select Statements for some advanced information!
Questions For Mid-Level Positions
Following are the type of SQL interview questions asked by recruiters for mid-level positions for database developer/designer/administrator.
Question 1:
Explain the difference between clustered and a non-clustered index in SQL Server:
Answer:
-
- A clustered index is a type of index where the Table records physically match the index order. Non-clustered index table records are not stored in memory in the same order as defined by the non-clustered index.
- There can be only one clustered index per table. On the other hand, there can be multiple non-clustered indexes.
- Clustered indexes are faster compared to non-clustered indexes.
Question 2:
Explain three advantages of database normalisation.
Answer:
-
- Fosters data integrity
- Reduces data redundancy
- Higher data security
Question 3:
When should you prefer using HAVING over the WHERE clause in SQL Server?
Answer:
The WHERE clause is used to filter individual rows. On the other hand, the HAVING function is normally used to filter rows grouped by aggregate functions. The HAVING function is used in conjunction with the GROUP BY clause.
Question 4:
What is a query execution plan in SQL server?
Answer:
A query execution plan in SQL is a graphical view of a query which depicts how a query executes by breaking a query down into multiple parts. The query execution plan can be used to identify the part of the query that is taking longer to execute.
Question 5:
Which constraint is used to limit the values or type of data that can be stored in a column?
Answer:
The CHECK constraint is used to limit the type of data stored in a column. For instance, while creating a table if you specify the constraint CHECK (Value>=20), it ensures that only a value of 20 or greater is stored in the “Value” column.
Questions For Advanced-Level Positions
Following are some examples of SQL Interview questions asked by recruiters for advanced-level job positions for database developers/administrators.
Question 1:
What are triggers, and what are their common uses?
Answer:
A trigger is a type of stored procedure that executes whenever an event occurs in an SQL Server database. There are two main types of triggers: AFTER and INSTEAD. A common use of a trigger is to enforce data integrity among multiple databases that contain tables with foreign key relations.
Question 2:
What is dynamic SQL? Give an example of a scenario where you can use dynamic SQL.
Answer:
Dynamic SQL queries are interpreted and executed at runtime. The sp_executesql stored procedure is used to execute dynamic SQL queries along with parameter values. The parameter values are integrated within the SQL queries at runtime. Dynamic SQL helps prevent SQL injections and improve overall security.
Question 3:
What are deadlocks in SQL Server, and how can you avoid them?
Answer:
A deadlock normally occurs when two or more queries are waiting for resources occupied by the other queries. There are several strategies for deadlock avoidance, e.g. shorter transactions, accessing resources in round-robin fashion or automatic resource release.
Question 4:
When should you use synonyms in SQL Server?
Answer:
A synonym is an alternate name or alias for a database object such as a table, function, view, stored procedure, etc. that may exist on a local or remote server.
A synonym helps you decouple the name and location of a database object. For instance, with synonyms, you can execute queries against a table name even if the table has been renamed or moved to a new location.
Question 5:
Explain deferred name resolution in SQL Server. When does it come handy?
Answer:
Deferred name resolution refers to calling objects in SQL queries that do not yet exist. In SQL server, stored procedures support deferred name resolution. For example, while creating a stored procedure, you can write a SELECT query that fetches data from a table which doesn’t even exist on a database. On the other hand, functions in SQL Server do not support deferred name resolution.
Final Thoughts
After reading these sample questions and answers, hopefully you’ll feel better prepared to face that job interview. Be yourself and stay calm so that you can easily show your knowledge and personality. A good job/company is where you feel happy and appreciated, so remember to pay attention to all the details and assess if it’s the right place for you.
Looking for more SQL tips? Read our guide on the SQL Update Statement here!
