Backend interview questions

Sources:

• http://www.mssqltips.com/sql-server-tip-category/139/interview-questions-developer/
• top-50-datawarehousing-questions-answers.pdf

Important questions and links:

• Difference between all three identity functions( @@identity, SCOPE_IDENTITY() and IDENT_CURRENT() ): http://www.mssqltips.com/sqlservertip/1385/properly-capturing-identity-values-in-a-sql-server-database/
• Difference between CHECKSUM and BINARY_CHECKSUM:  http://decipherinfosys.wordpress.com/2007/05/18/checksum-functions-in-sql-server-2005/
• GROUPING SET(): http://sqlandme.com/2011/07/12/sql-server-tsql-group-by-grouping-sets/
• CDC: https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/
• Indexing: http://www.mssqltips.com/sqlservertip/1206/understanding-sql-server-indexing/
• Difference between Unique Clustered Index and Clustered Index (non-unique): http://dba.stackexchange.com/questions/55654/difference-between-clustered-index-and-unique-clustered-index
• Comma separated: http://davidduffett.net/post/5334646215/get-a-comma-separated-list-of-values-in-sql-with-for
• nth salary: http://sqlfiddle.com/#!6/c486e/1/0
• For XML: https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/ Fiddle: http://sqlfiddle.com/#!6/ebf7c/15
• Pivot n Unpivot: http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/
• No duplicates: http://www.mssqltips.com/sqlservertip/1103/delete-duplicate-rows-with-no-primary-key-on-a-sql-server-table/

TSQL Questions:

• Finding Grand parent in a parent-child table (http://sqlfiddle.com/#!6/24c7b/6)
• There are three projects and each have its table. Table have information of employee ids and hours of work they have done in that project. Print list of all employees and total hours they have worked across all projects. (Full Outer Join with Coalesce)
• Implementation of SCD – 2 (Pull data from sources, make a dimension which has 4 of its columns trended)
• Output for a query that contains a self-joined table on condition A.ID = B.ID – 1 and select has subtraction of fact
• Many to many relationship (keep mapping table)
• Left Join and WHERE clause (which works as EXCEPT

SQL Hacks: http://sqlzoo.net/wiki/Running_Total

SQL Hacks book: (Download it from 4shared.com)

Important hacks:

• 3
• 10 (Without the subquery, the optimizer finds it much easier to use your indexes)
• 11
• 23
• 24 (Multiply across result set) (Exp, SUM, Log)
• 25
• 27 (Join on equality condition and OR)
• 31
• 51
• 52
• 56
• 75
• 78
• 80
• 81
• 82
• 84
• 85
• 88

TOEFL – WQ2 (10)

In my view old friends that we have and new friends that make are pretty different on the scale of trust we lay on them and expectation they have from us. Comparing them on this scale I tend to agree with the topic that it is more important to keep your old friends than it is to make new ones.
The level of trust we bestow on our old mates is more than we do on the new ones. There are several reasons for that, long years of realtionship being the most important. When it comes to lending vehicle, looking after our home or asking for money, for instance, it requires us to trust someone whom we know from several years and not months or days. Hence, new friends, who have been there with us for few months, cannot be trusted when it comes to such important aspects of life.
Second, the old friends do not have much expectation from us. We often befriend people when we are young. Back then friendship has no strings attached. We get together to play sports, have conversation or study together. However, as we grow up we start making friends in the same economic status we belong. The new friends we have have an expectation from us to be in the same zone of economy so that they can maintain their reputation. Also, some friends we make who belong to lower economy have expectation of getting help from us now and then. Personally, I have seen cases where these are the friends who are first to leave us during our economic crisis. Old friends, who have never had any expectation from us in past, tend to lend us hand for help.
However, it is important to make new friends. As the life progresses it may happen that we take a different career path than our old friends. We lose contacts and our conversation with them weakens. In that case, it is always adviced to make new friends and be socially active. Among these new friends, then, we will have to choose one best friend we would like to be in contact as and when we get old. After all, it is only after a considerable time when we can start trusting them and become as good as old friends.
All in all, in my view, it doesn’t hurt to make new friends but, given the higher level of trust we place and no expectations, it is better to concentrate more on sticking with old friends.

TOEFL WQ1 (10)

The professor in the lecture refutes all the points made in the reading passage in order to state that Rembrandt was the creator of the painting Portrait of an Elderly Woman in White Bonnet. According to her, the painting was edited several years after its creation.
The reading passage posits that Rambrandt, given his attention to detail, could not make a mistake of making a woman wear luxurious fur collar and a servant’s white cap at the same time. The lecturer, however, states that according to some researchers the fur collar was painted long after the painting’s completion. It was added in the painting to add more value and make it look aristocratic.
The lecturer also claims that before the painting was edited, woman was portrayed to wear a simple collar. This made the light reflect onto the face of the woman. This clearly contradicts the point mentioned in the passage according to which Rembrandt cannot make a mistake of painting woman’s face shiny when there is a fur collar, which shall abosrb any light that falls on it, in middle of the light coming from the bottom and her face.
The reading also mentioned that there were several pieces of wood conjoined at the end which was not the way Rembrandt painted. He used to paint on wood panels but never glued several pieces together. The lecturer states that the pieces of the wood were conjoined several years after the completion of the painting in order to add more value to it. Moreover, the wood that was used in initial formation of the painting was from the same tree whose wood was used by Rembrandt during creation of his self-protrait.

TOEFL WQ2 (9)

In my view, the high importance that money holds in life is only till we attain the basic needs. Beyond that, it is our family, friends and time we have spent with them which matter a lot. An Indian author has, hence, very rightly said that you will not remember your presentations when you die. I am of the opinion that having less pay and short working hours and spending most of the time with friends and family is key to happiness. If given an option to do so, I will definitely opt for it.

In one life which we have got we all hunt for happiness. Key to happiness is when you spend time with someone who is near and dear to you. This is quite evident in case of small kids who seem very cheerful when they are in vicinity of their parents or siblings. However, their mood swings when they do not find anyone familiar around them. Although adults do not portray so much emotions as kids do but still this feeling of happiness persists in them as well. Hence, I would definitly prefer being with my family and friends to making more money as money is something through which you can buy material commodity and not happiness.

Moreover, working for long hours also has ill effects on health. Studies show that working non-stop for long hours on computers not only affects the eye-sight but also hampers the memory. Many doctors have started receiving more cases of severe memory loss from computer engineers, specially from the ones who work for long hours. Grey cells in the brain are resonsible for generating and remembering facts and places. These cells are quite susceptible to the computer monitor radiations which reach them through the optical nerve endings, consequently, hampering the memory power of a person. Other ill-effects include sleep depriviation, increased tension, migraine, high blood pressure and diabetes. It seems better to work less than to fall prey to such health hazards which not only make your life difficult but also might prove fatal.

We are also not born just to earn money or keep on working until end of our lives. Working for appropriate hours every day provides a person enough time to look after all his needs ranging from his health, kids, family to traveling and spending time with friends. With leisure time one can also practice his interests like reading books, collecting stamps of different countries etc.

Hence, if given an option, I would definitly go with getting less payment and spending more time with family and friends.

TOEFL – Q2 (8)

External appearence is the first aspect which is noticed in a person. However, external appearences can be deceiving. Many people contend that one who takes care of his external appearence is the one to be trusted. According to them, if a person is attentive enough for his external appearence, he might also be prudent about other aspects of life. However, this does not seem quite true for several reasons.

First, it is quite evident from history that a bad external appearence does not show that a person is careless or less intelligent. For instance, Albert Einstein, arguably the most intelligent scientist, never used to comb his hair. He was too engrossed in his works to get concerned with his outer appearence. A person who might be meeting him for the first time might have judged him as someone who is weird which which would not be true. To judge such person, it would require more conversation and spending more time.

Second, a person’s outer appearence, the clothes he wear and his skin colour, is also due to the culture and geographic region he belongs to. For instance, farmers from India generally wear “dhoti” which is quite thin and just cover quarter of the lower body. They also have dark colour complexion, as most of the Indians do. Hence, a person who meets a farmer for the first time might judge him to be quite poor, given the color of his skin and clothing wears which might not quite true. Not all farmers in India are poor. There are farmers who adorn the same clothing and have same colour complexion who grow saffron, cotton and sugarcane are among the millionares.

Moreover, a person should never be judged in first glance. Maintaining your outer appearence by just combing your hair properly, applying the face wash or polishing your shoe are quite easy and can be performed by nearly all the people. However, a good personality, charming behaviour and intellectual prowess might take years of hard work. These abilities can only be judged if you come to know about his views in different topics which can only be performd by spending time with him.

Hence, I agree that a person should never be judged on the basis of his outer appearence. In order to judge people, we should spend time with them conversing on various topics.