Many of my friends are now attending interviews. I thought I will jot down some of the questions I faced and some of them which I prefer to ask. Not just for interviews but knowing answers to these will help you at work.

I am only putting the questions and would leave it to you to find out the answers .. that will make sure you remember them :-) I am always a call/mail away if you need any help.

1. Joins

(a) What is an equi- join?

(b) What is self-join? How do you identify the different instances of the table in a self-join?

(c) What is an outer join? What happens to the non-matching rows in the inner table of an outer join?

1. Query basics

(a) What is the difference between a sub-query and a correlated sub-query?

(b) There is query which has GROUP BY, ORDER BY, HAVING and WHERE. In which order does these get picked up?

(c) I have a condition which I can put in WHERE or HAVING. Where do you think we should put this?

(d) Is there a way to explicitly insert value in an IDENTITY column? If yes, then how?

(e) What are deferred updates?

Functions

Sybase internal variables (@@error, @@rowcount, @@trancount, )

System tables and procedures (sp_%)

Grant/revoke ..

1. Indexes

(a) What are indexes? What are the advantages and also side effects?

(b) What are the different types of indexes in Sybase?

(c) What is the diff between clustered and non-clustered indexes?

(d) How many clustered indexes can a table have? And how many non-clustered?

(e) How are indexes stored internally?

(f) What is index covering? Can index covering happen with both types of indexes?

(g) If you know 15, is there some difference in the indexes from 12.5? If yes, then what are those?

4.Triggers

(a) What are triggers? Why are they used?

(b) There are specific tables associated with triggers. What are they and what do they contain?

(c) What is the new type of trigger being introduced in 15?

(d) What does the @@rowcount variable contain inside a trigger?

1. Views

(a) What are views and why are they used?

(b) Are there any disadvantages with views with respect to performance?

1. Stored procedures

(a) What are stored procedures and why do we need them?

(b) Is there a way to recompile a stored procedure each time it runs?

(c) If I create and populate a temp table within an sp, can I access it after sp finishes execution.

1. Logging

(a) What is a transaction log?

(b) What is the difference between DELETE and TRUNCATE?

(c) What is the difference between INSERT and SELECT * INTO?

(d) If I have a 150 MB transaction log and try to delete a million rows in a shot.What happens then? What should I do to avoid issues?

1. Temp tables

(a) What are temp tables?

(b) What is the difference between following two?

create table #test (a int)

create table tempdb..test (a int)

Will the second one remain in tempdb forever? Do we need to drop it explicitly?

1. Locking

(a) What is locking? Why is it needed?

(b) What are the types of locks available in Sybase?

(c) What are the different locking schemes for a table? And what is your thought process in choosing a locking scheme for a table?

(d) What is the “LOCK TABLE” command?

(e) What are isolation levels?

(f) What are latches?

1. Performance/Optimization

(a) There is a query which was running fine since months. The query is running slowly now. What are the different things that you will check?

(b) What are the different statistics maintained? How does the optimizer use it?

(c) How do we force the table order?

(d) What is an index hint?

(e) What are physical and logical reads?

(f) What are different things you will look out in a Query Plan?

(g) Under one of the table, we have a line “Index contains all necessary columns. Base table will not be read”. What does it mean? Does this happen with a clustered index or non-clustered index or both?

1. Normalization

(a)What is normalization?

(b) What are different normal forms? Explain 1, 2, 3 and BCNF with examples (u can refer to databases.about.com for this).

(c) What are denormalized tables and why do we need them?

---- Under Construction ---- More to come ----

PS: I wrote this in Sep 2010.

About the author

Vishnu Vardhan Chikoti is a co-author for the book "Hands-on Site Reliability Engineering". He is a technology leader with diverse experience in the areas of Application and Database design and development, Micro-services & Micro-frontends, DevOps, Site Reliability Engineering and Machine Learning.