Monday, November 3, 2014

Interview questions for database developer

Technical Round Database Interview Questions

1) What are the different types of Trace flags which we used for detecting the Deadlock?
2) What are the different types of database backups?
3) How do you perform the Error handling in SQL Server?
4) What is the ACID property in SQL Server?
5) What are the best practices in Database programming?
6) What are the different isolation level in SQL Server
7) What is the default isolation level in SQL Server
8) What is dirty read in SQL Server
9) What are the Serializable isolation level in SQL Server
10)What are the Rank functions in SQL server
11) What is Replication in SQL Server
12) What is the stuff function in SQL Server?
13) What is the Difference between Stuff command and Replace command in SQL Server
14) What is the merge command in SQL Server?
15) What is the Pivot/Unpivot table in SQL Server?
16) What is Derived table in SQL Server?
17) What is the Output clause in SQL Server?
18) What is the temporary tables in SQL Server?
19) What are the different types of database in SQL Server?
20) What is CTE in SQL Server?
21) What are the benefits of using CTE SQL Server  
22) What are cursors in SQL Server?
23) What are Views in SQL Server?
24) What are Triggers in SQL Server?
25) What are magic tables in SQL Server?
26) What are the difference between Stored Procedure and SQL triggers in SQL Server?
27)If no rows are affected by the SQL query, will after trigger will execute?
28) How can we improve the performance of an stored procedure?
29) What are the heap table in SQL Server?
30) Where we need to use table variable and where we should use temporary tables?
31) What is log shipping in SQL Server?
32) What is fragmentation in SQL Server?
33) What is the difference between logical and physical fragmentation?
34) In which database temporary tables are created?
35) What is the difference between Except command and Intersect command?
36) What is the BCNF in SQL Server?
37) What is database collation?
38) Suppose there are two tables A and B and we need to write 3 SQL queries which returns the record set as shown in the below figure.
 
39) Suppose we have to design a database in which employees can work on multiple projects.There are many designation defined in the organization to which each employee can belongs.
 

For example, Software engineer, Team lead, Project Manager, Project lead, QC engineer, QC Lead, QC manager etc. An employee can belong to one designation
at a time.  It means at a time an employee can either be Software engineer or team lead or project manager but not software engineer as well team lead at a same time.

Also the projects can work on different technologies like .Net, SQL Server, Oracle, HTML5 etc. Every project has a start data and end date. A project can work can include many technologies like .net, SQL Server , Java script, HTML5 etc. Most of the time every project work include a front end server technology like JAVA. Asp.net, uses Java script, HTML, CSS as client side technologies and SQL Server, oracle as back end database server. So In this ways a project work can include many technologies as in above example,  suppose a project can use Asp.net as server end front end technology, java script, HTML, CSS , AJAX as client side technology and SQL server as back end database server.

What should be the design the database schema (tables, their foreign keys, primary keys etc.?


40) Suppose we have a table named tbl_charactername have only one column say "charactername".
It contains 10 rows. First 5 rows contains "Vivek Johari" and the next 5 rows contains "Abhinav Golwalkar"

tbl_charactername
---------------------
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar

Now we need a SQL Query which can return the result in the form

Desired Result:-

Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Query should work even if the table has 30 rows with first 15 contains "vivek johari" and next 15 contains "Abhinav Golwalkar" or 40 rows with first 20 contains "vivek johari" and next 20 contains "Abhinav Golwalkar" etc. But output should contains "Vivek Johari" and "Abhinav Golwalkar" in alternate rows.

41)  Suppose we have a table says tbl_students whose structure is given below:-

Create table tbl_students(Studentid int identity(1,1) , Studentname nvarchar(150))

Suppose it contains the following data:-

Studentid  Studentname
1               Vivek Johari
2               Chandra Singh
4               Avinash Dubey
6               Akhil
7               Sunny Rana
10             Saurabh

As one can see from the data that some rows are deleted from the table as some studentids are missing.

So write a query which gives the studentid which are deleted from this table

Project Manager Round Questions:-

1) Give me your brief introduction.
2) What your current project and your role in it?
3) How is your team size?
4) what is the hierarchy in your organization?
5) Have you any experience of DBA activity?
6) How big your database size?
7) How you optimize database (same technical question but this time PM just want to know just an overview)?
8) What best practices you follow in your company?
9) Any tools your company uses for database designing?
10) Do your company follow Agile methodology ?
11) Any challenges you faces in your current project?
12) Why you want to change your current organization
13) Would you like to work on C#, web services, Java (in case of Oracle) if required?
14) How early you can join(want to know your notice period or possibility of early joining)?
15) Do you want to ask any question from us?



Sunday, October 19, 2014

My Best LinkedIn Articles

Sunday, October 12, 2014

My Best Technical Sites and Blogs

Many times my friends ask me which technical sites they should refers for finding the solutions, so below is the list of tech sites which I used to refer most of the times for learning technologies and finding the solutions

Mssqltips.com:- I like this site because I find here some very good tutorials in SQL which are very simple to understand and gives some good knowledge for a person which is not expert in SQL Server.


Simple-talk.com:- I love this site since it has some brilliant authors who share their knowledge through some very good articles.  

Technet.microsoft.com:- This is Microsoft sites where you can get help on any topic however small it may be. Some in depth information with easy to understand language.

Stackoverflow.com:- This is one of my favorite discussion forum where you can get the solutions of most of your problems. Also from multiple answers , you can also mention which is working or which is best which help others.

Codeproject.com:- This site needs no introduction as it contains lots of articles on each technology. Lots of technical professional share their knowledge on this site. You can ask your question in this site ask question section. You can get many quality articles on this site. Also best articles of the month competition is always going on where you can win some good prizes by submitting your articles and winning the competition. 

Techmixing.com:-This is my blog where I share my technical knowledge though articles. I always reads the articles on this site before going for any interview or whenever I needs to brush up my knowledge. So always trying to maintains the quality of articles on this site.

SQLServerCentral.com:- Like Simple-talk, this is one of the best site for database. Have so many brilliant database experts as its author. Daily publish so many quality articles and also mentions good articles from other sites too. Its Question of the Day sections always comes up with a very good objective questions.

Sqlauthority.com:- Whenever you search for any SQL topic or issue , Google always show the link of this site in first few entries. Run by one of the best Indian database legend Pinal Dave, this site contains lots of SQL articles from small to complex topics. This is the site which helps me a lot in learning the SQL Server.
  
Pluralsight.com:- A video tutorial site contains lots of video tutorials for each
and every technology. All tutorials contains good explanations of the concepts with some very good demo. You can also took pre course online test to judge your knowledge before starting the course and also took post course test to judge your increased knowledge.You only has to subscribe on it which cost you some monthly fees. It has 2 options of subscription


Developer.android.com:-This is the best site for learning Android development. All tutorials on this site has been written in a very simple language which is easy to understand. I must say it is a must read site for any developer who want to learn android or working on it.

There are some other popular sites also like beyondrelational.com, C#corner, DatabaseJournal.com, Tutorialpoint.com which you can refers for finding your solutions. If you refers some other quality technical site or blog, please mention it as a comment so that others also get benefited. 

Monday, August 18, 2014

SQLTea - New SQL tutorial app in Google play store

SQLTea - Our new SQL Tutorial App on Google Play Store
 
We are happy to announce our new SQL tutorial app SQLTea  ( Native android app) for SQL learning which includes articles from this blog.


This application includes 

1) Articles for basic concepts like Normalization, Constrains, Joins, SQL Functions, SQL commands etc and also advance concepts like SQL Triggers, Views, Cursors, Stored Procedures and many more.

2) Every article is explained with the help of examples.

Techmixing.com new apptechmixing.com new app


3) Once installed, it will work in offline mode (No Internet needed). You can read articles easily even if you don't have Internet connections

4) Includes many popular articles from this blog.

5) Light weight and fast

6) Will going to frequently update this App with new blog articles.

7) Simple to use and navigates.

So please download this app and gives your valuable comments on it.