Advance SQL Interview Questions

Advance SQL Interview Questions

If you’re looking for Advance SQL interview Questions and Answer for Experienced or Freshers, you are at the right place.

SQL database market continues even stronger by 2020 will be 70%.

According to research SQL by Microsoft grew at 10.32%, while Oracle grew 3.5%. In the upcoming era, the competition will be more heated than is has been for years. So, You still have the opportunity to move ahead in your career in SQL certification guide.

Programming71 offers Advanced SQL Interview Questions and Answers 2019 that helps you in cracking your interview & acquire dream career as SQL Developer.

I want to discuss about Advance SQL interview Questions.It will help for advance level SQL programmer. It’s new:

No : 1

What will be the result of the query below?

Explain your answer and also provide an alternative version of this query that will avoid the issue that it exposes.

 

Answer:

Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL NOT IN condition contains any values that are null, then the outer query here will return an empty set, even if there are many runner ids that match winner_ids in the races table.

Knowing this, a query that avoids this issue would be as follows:

Note, this is assuming the standard SQL behavior that you get without modifying the default ANSI_NULLS setting.

 

No : 2

Given two tables created and populated as follows:

What will the result be from the following query:

Answer: The result of the query will be as follows:

The EXISTS clause in the above query is a red herring. It will always be true since ID is not a member of dbo.docs. As such, it will refer to the envelope table comparing itself to itself!

The idnum value of NULL will not be set since the join of NULL will not return a result when attempting a match with any value of envelope.

 

No 3:

Given these contents of the Customers table:

Here is a query written to return the list of customers not referred by Jane Smith:

What will be the result of the query? Why? What would be a better way to write it?

Although there are 4 customers not referred by Jane Smith (including Jane Smith herself), the query will only return one: Pat Richards. All the customers who were referred by nobody at all (and therefore have NULL in their ReferredBy column) don’t show up. But certainly those customers weren’t referred by Jane Smith, and certainly NULL is not equal to 2, so why didn’t they show up?

SQL Server uses three-valued logic, which can be troublesome for programmers accustomed to the more satisfying two-valued logic (TRUE or FALSE) most programming languages use. In most languages, if you were presented with two predicates: ReferredBy = 2 and ReferredBy <> 2, you would expect one of them to be true and one of them to be false, given the same value of ReferredBy. In SQL Server, however, if ReferredBy is NULL, neither of them are true and neither of them are false. Anything compared to NULL evaluates to the third value in three-valued logic: UNKNOWN.

The query should be written in one of two ways:

or:

Watch out for the following, though!

 

Thanks for reading.

Share and Enjoy !

0Shares
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *