Testing a candidate’s SQL knowledge can sometimes seem a daunting task. Having a good battery of SQL interview questions and answers is always a good point to start when it comes to preparing technical interviews.
Whether you’re a recruiter or a job seeker looking for some SQL interview questions on various expertise levels, you’ve come to the right place.
Beginner SQL Interview Questions
To begin, let’s go over some SQL interview questions for freshers.
What is a VIEW?
An SQL view is a virtual table that can be used to collect pertinent information from anywhere within the database (including different tables):
You can then use that view just like any other table to perform further operations:
If you want to dispose of a view, you can use the DROP VIEW command:
What is a PRIMARY KEY and a FOREIGN KEY?
A PRIMARY KEY designates a column that contains non-null and unique values that can be used to identify every record in a table. A table can only have one PRIMARY KEY.
A FOREIGN KEY designates a column that references a PRIMARY KEY in another table for the purposes of referential integrity. A table can have more than one FOREIGN KEY and, unlike a PRIMARY KEY, can hold repeated values.
What is the difference between DELETE and TRUNCATE?
DELETE is a Data Manipulation Language (DML) command used to remove rows from a table or relation. It is usually accompanied by a WHERE clause that will filter rows based on a specified condition, otherwise, it removes all rows from the table. DELETE logs the removed rows and thus can be rolled back.
TRUNCATE is a Data Definition Language (DDL) command that removes all rows from a table, with no questions asked. Since it does not have any conditions to analyze, it is usually faster than a DELETE. TRUNCATE does not create any records of the removed rows, and thus cannot be rolled back.
What is DEFAULT?
DEFAULT is used to set a default value for a column in case the value isn’t specified. This only applies to new records after this constraint is set in place.
DEFAULT can also be used to insert system values:
What is normalization?
Database normalization is a process by which all data is organized in tables to ensure data integrity. It serves mainly to reduce ambiguity by removing redundant data.
This process has several steps that build incrementally on previous ones with increasingly refined conditions, each preventing and solving more problems than the last:
- 1st Normal Form (1NF)
- 2nd Normal Form (2NF)
- 3rd Normal Form (3NF)
- Boyce Codd Normal Form (BCNF)
Intermediate SQL Interview Questions
In this section, we give you some more advanced Sql server interview questions.
What’s the difference between a Clustered Index and a Non-Clustered Index?
A clustered index operates directly on a given table, defining the order in which its data is stored in memory. A table can only have one clustered index, which is usually tied to its primary key.
A non-clustered index, on the other hand, is separate from a table’s data. It creates a copy of the table’s data ordered in a certain way to improve the performance of frequently used or other specific queries. A table can have any number of non-clustered indexes.
What is Collation?
This question is a common staple of SQL server interview questions. Collation is a set of rules that determine how data is stored, retrieved, and compared in an SQL database.
The two most commonly used modes are Case Sensitive and Case Insensitive.
Collation can be defined database-wide or even table-wide.
As an example, say we have a table with the following data:
Table People:
| ID | Name |
| 1 | Evelyn |
| 2 | Jonatan |
| 3 | Rick |
If we perform the query:
In a case sensitive collation no results would be found, whereas in a case insensitive collation it would return the first row of the table.
What’s the difference between a PRIMARY KEY and a UNIQUE Key?
A PRIMARY KEY designates a column that contains non-null and unique values that can be used to identify every record in a table. A table can only have one PRIMARY KEY, which usually defies the clustered index or that table.
A UNIQUE key designates a column that contains unique values. This value can be NULL (although it can only happen once). A table can have as many UNIQUE keys as needed, and each generates a non-clustered index for that table.
What is the difference between INNER JOIN, OUTER JOIN and FULL OUTER JOIN?
INNER JOINis one of the most commonly used join types, and will only return rows where the join condition is true.
OUTER JOIN will match all rows from the main table (the first one in an LEFT OUTER JOIN, and the second in a RIGHT OUTER JOIN) to the secondary table. In the case where no matches can be found, NULL values are introduced instead.
FULL OUTER JOIN will perform essentially a LEFT OUTER JOINand RIGHT OUTER JOIN in one.
Let’s consider the following example:
Table People:
| ID | Name | JobID |
| 1 | Evelyn | 2 |
| 2 | Jonatan | NULL |
| 3 | Rick | 1 |
Table Jobs:
| ID | Name |
| 1 | Colonel |
| 2 | Librarian |
| 3 | Medjai |
INNER JOIN:
| People.ID | People.Name | People.JobID | Jobs.ID | Jobs.Name |
| 1 | Evelyn | 2 | 2 | Librarian |
| 3 | Rick | 1 | 1 | Colonel |
OUTER JOIN:
| ID | Name | JobID | JobName |
| 1 | Evelyn | 2 | Librarian |
| 2 | Jonatan | NULL | NULL |
| 3 | Rick | 1 | Colonel |
|
| People.ID | People.Name | People.JobID | Jobs.ID | Jobs.Name |
| 1 | Evelyn | 2 | 2 | Librarian |
| 2 | Jonatan | NULL | NULL | NULL |
| 3 | Rick | 1 | 1 | Colonel |
FULL OUTER JOIN:
| People.ID | People.Name | People.JobID | Jobs.ID | Jobs.Name |
| 1 | Evelyn | 2 | 2 | Librarian |
| 2 | Jonatan | NULL | NULL | NULL |
| 3 | Rick | 1 | 1 | Colonel |
| NULL | NULL | NULL | 4 | Medjai |
What is the difference between UNION and UNION ALL?
Both UNION and UNION ALL combine the results of two SELECT statements, the difference lies in the way they do it:
- UNION will combine the results but will not show repeated values.
- UNION ALL will combine the results, including repeated values.
Let’s see an example:
Table BirthRegistry:
| ID | Name | Country |
| 1 | Rick | USA |
| 2 | Ardeth | Egypt |
Table LivingRegistry:
| ID | Name | Country |
| 1 | Rick | Egypt |
| 2 | Ardeth | Egypt |
UNION:
| Name | Country |
| Rick | USA |
| Ardeth | Egypt |
| Rick | Egypt |
UNION ALL:
| Name | Country |
| Rick | USA |
| Ardeth | Egypt |
| Rick | Egypt |
| Ardeth | Egypt |
Expert SQL Interview Questions
If you’re looking for SQL interview questions and answers for experienced developers and with examples, this section will give you some tough questions to ask.
What is the difference between Shared Lock, Exclusive Lock, and Update Lock?
A shared lock allows any number of processes to access and read the data in a specific resource, but will not allow any outside modifications to it.
An exclusive lock locks the resource exclusively, not allowing any outside processes to read or perform modifications.
An update lock is a more flexible exclusive lock, and can be imposed on an existing shared lock. It will allow reading the data until the point where the transaction will take place, upon which it will turn into an exclusive lock. It can be seen as a dynamic lock: being a shared lock but becoming an exclusive lock at the time of the transaction.
What is the difference between UNION, MINUS and INTERSECT?
UNION, MINUS, and INTERSECT combine the results of two SELECT statements, the difference lies in the way they do it:
- UNION will combine the results but will not show repeated values.
- MINUS will subtract the second SELECT statement from the first.
- INTERSECT will only show results that are present in both SELECT statements.
Let’s see an example:
Table BirthRegistry:
| ID | Name | Country |
| 1 | Rick | USA |
| 2 | Ardeth | Egypt |
Table LivingRegistry:
| ID | Name | Country |
| 1 | Rick | Egypt |
| 2 | Ardeth | Egypt |
UNION:
| Name | Country |
| Rick | USA |
| Ardeth | Egypt |
| Rick | Egypt |
MINUS:
| Name | Country |
| Rick | USA |
INTERSECT:
| Name | Country |
| Ardeth | Egypt |
What is faster, one big query or many small queries?
This will depend on the specific situation.
Each “round trip” to the database will introduce overhead, which makes a big query faster in principle. However, if that query has a lot of extra information that isn’t pertinent to the operation at hand, the time gained by reducing the round trips to the database can cancel itself with the data transmission of all that information.
If the small queries depend on each other for the final result, then consolidating them into one big query can save you time. However, if they are all independent and can be run in parallel, you’ll get the results faster by leaving them split.
What is the difference between B-Tree, R-Tree and Hash indexing?
Each of these indexes are optimized for certain kinds of comparisons, making their choice an important factor into making fast searches:
- B-Tree: An efficiently ordered key-value map. Can be used to find a record and can be scanned in order. Efficient at finding values within a range.
- R-Tree: A spatial index that can quickly get records whose values are close to a given value in 2 or more dimensions. Used a lot in geographical databases.
- Hash: An unordered key-value map. Even more efficient than B-Trees at finding records, but has no concept of order and can’t be used to fetch ranges.
What is Optimistic Locking and Pessimistic Locking?
Optimistic Locking is a strategy where any number of users of a database can read a record, and checks (usually tied to timestamps or version numbers) are performed if any of them want to write to it, ensuring that the operation is being conducted on the most recent version of the record. Otherwise, the operation is rolled back.
Pessimistic Locking, on the other hand, locks a record for exclusive use by a single user until they no longer require it.
Best SQL Interview Questions To Hire Expert Remote Developers
By using the questions in this article, you’ll be able to quickly pinpoint the level of expertise of your candidate. You can also run a coding challenge with an appealing problem for your candidates to tackle. You’ll find the right person for the position faster.
If you’re looking for expert SQL developers to add to your development teams, DistantJob can help you find the right candidates for your business. We have a wide array of people ready to fill your positions and we make sure that they fit your company culture. Interested? Check out our hiring page!
