What is Index in SQL?
With the help of Indexes, information retrieval from the database happens faster and with greater efficiency. Thus, indexes improve performance. There are three types of indexes:
There can be many non-clustered indexes in a table, however, there can be only one clustered index.
What is a Synonym in SQL?
As the name suggests, a synonym is used to give different names to the same object in the database. In the case of object-renaming or object schema-change, existing applications can continue to use older names because of synonyms. A synonym must only reference an object and not another synonym. Additionally, synonyms can also be used to reference objects in different databases or servers, by using 3 or 4 part object names. There can be many names for a single database object as long as all the names directly refer to the same database object.
You must ensure that you know answers to such SQL interview questions as answering them correctly will give you the much-needed confidence for the more difficult ones.
Mention some advantages of Synonyms.
Below are some advantages of using Synonyms:
Are there any disadvantages to using Synonyms?
Yes, there are some disadvantages.
For SQL interview questions that ask you to talk about the advantages or disadvantages of a certain component or tool, ensure that you list as many as you can. Also, you can make your answer to such an SQL interview question meaty by adding personal anecdotes about some of the advantages or disadvantages.
Are NULL values equal to zero?
No. NULL values show an absence of characters, whereas zero is a numerical value. NULL values occur when a character is unavailable or not known. NULL values should also not be confused with blank space because a blank space is not supposed to have any data attached to it, whereas a NULL value shows a data record without any value assigned to it.
What are Scalar subqueries and Correlated subqueries?
A Scalar subquery is when a query returns just one row and one column of data. A Correlated subquery occurs when a query cannot process without information from an outer query. In such cases, table aliases define the scope of the argument and the subquery is parameterized by an outer query. Thus, there is a correlation between the inner and outer queries. As a result, back and forth execution takes place where a single row of results from the outer query passes parameters to the inner query.
SQL interview questions like the one above try to ascertain the depth of your knowledge of SQL.
What is the difference between NVL and NVL2 functions?
The function NVL (exp1, exp2) is a conversion function that changes exp1 into the target exp2 under the condition that exp1 is NULL. The data type of exp1 is the same as that of a return value. The function NVL2 (exp1, exp2, exp3), on the other hand, is a checking function, which determines whether exp1 is null or not. When exp1 is not null, exp2 is returned as the result. When exp1 is null, exp3 is returned as the result.
What do you mean by ‘auto increment’?
With the auto-increment command, one can generate unique numbers when new records are added to a table. This function is especially useful when one wants to automatically generate the primary key field values upon inserting new records. This command comes in handy on several platforms. The auto-increment command for the SQL servers is “identity”.
What is the main use of ‘recursive stored procedure’?
The main use of the recursive stored procedure is to make the code calls till the time certain boundary conditions are reached. This helps programmers enhance productivity by using the same code multiple times.
An SQL interview question like this one shows that even though some of the advanced concepts may be easy to understand, they may be difficult to recount when suddenly faced with the question. Thus, when you prepare for SQL interview questions, ensure to revise all types of concepts.
Describe ‘datawarehouse’ in SQL.
A ‘datawarehouse’ is a system used for analyzing and reporting data. It is very similar to a physical warehouse where inventory is stored and assessed before being sent to a customer. Here, data is stored, analyzed, and reported. A datawarehouse functions as a central repository of data integrated from different areas and sources and makes this data available for use.
Now that you have seen some SQL interview questions and answers, you can prepare more on the same pattern. However, your SQL interview will not just have technical SQL interview questions. Apart from the technical SQL interview questions, the interview will also focus on a variety of soft skills such as team skills, project management skills, time-management skills, etc. Recruiters hiring SQL developers must ensure that they ask both the technical SQL interview questions and the soft-skills-related questions to get the best candidate for the job.
If you are an experienced SQL developer looking for a job change, apply to Top MNC jobs at Turing. If you are a company looking to build your dream team of experienced SQL developers, come to Turing.
Tell us the skills you need and we'll find the best developer for you in days, not weeks.