Post reply

TSQL Best Functionalities needed

  • November 12, 2010 at 5:34 am

    #242795

    Hi for the below questions i need good and efficient answers. Below are the inrterview questions asked in one interview. I am having the real time exp. But the interviewer is not satisfied with my answers. I will furnish my answers also.

    1) I am having one table and the customer is exported some data from an excel sheet to the table. After that some changes has been made and uploaded the excel sheet again in to the application. How we should decide which was updated and which was the new item was added in the excel sheet?

    MY ANSWER:

    Wht i told is : First i will take the excel data in to a temporary table and i will repeat the loop for every record and i will check whether the record was updated or not?

    But the interviewer wants me to compare all the records at atime. I don't know how it was compared?

    2) BOM Pricing Required?

    BOM Pricing in the sense:

    One product is having sub products. Tha sub products are also having BOM. Like that i have so many levels of bom's are there. How i should find out the BOM priciong at that time?

    MY ANSWER:

    I told him to write a while loop by saying thast, loop thru which the product is not having the sub products and then take all the items and claculate the price.

    But he is not impressed with this answer.

    3) I have one table. In that some other third party application is inserting data in to it. But one stored procedure is looking in to that table and if any row inserted in to the table then this procedure should take the inserted or updated records and have to insert in some other tables. What is the best have to be implemented in the stored procedure?

    How we know whether the record was inserted in to the main table?

    MY ANSWER:

    For the main table take one cursor. If any record was inserted in the main table then trigger will insert/update an item in some other table. Stored procedure will look in to the table and decide whether the record was inserted or not?

    But he told me that i understood the logic. Totally He is not satisfied with my answers.

    Please answer these questions as soon as possible.

    Srinadh

  • November 12, 2010 at 5:59 am

    #1249949

    srinadh.ramineni (11/12/2010)

    Hi for the below questions i need good and efficient answers.

    1) I am having one table and the customer is exported some data from an excel sheet to the table. After that some changes has been made and uploaded the excel sheet again in to the application. How we should decide which was updated and which was the new item was added in the excel sheet?

    2) BOM Pricing Required?

    3) I have one table. In that some other third party application is inserting data in to it. But one stored procedure is looking in to that table and if any row inserted in to the table then this procedure should take the inserted or updated records and have to insert in some other tables. What is the best have to be implemented in the stored procedure?

    How we know whether the record was inserted in to the main table?

    Please answer these questions as soon as possible.

    Srinadh

    Hi

    For 1.)Try with CDC Change Data capture

    2.)<Not clear>

    3.)For this it cant be accomplish by Stored procedure,thats why we have a Trigger.Trigger will do this type of work

    First you get this concepts well,try yourself if you cant come back

    Thanks

    Parthi

    Thanks
    Parthi

  • November 12, 2010 at 6:01 am

    #1249951

    srinadh.ramineni (11/12/2010)

    1) I am having one table and the customer is exported some data from an excel sheet to the table. After that some changes has been made and uploaded the excel sheet again in to the application. How we should decide which was updated and which was the new item was added in the excel sheet?

    you need to have insert/update triggers and some sort of flag column on the underlying that gets updated by the trigger.

    2) BOM Pricing Required?

    What is this?

    3) I have one table. In that some other third party application is inserting data in to it. But one stored procedure is looking in to that table and if any row inserted in to the table then this procedure should take the inserted or updated records and have to insert in some other tables. What is the best have to be implemented in the stored procedure?

    You need to clarify your question. you will again need to use trigger for this.

    How we know whether the record was inserted in to the main table?

    query the table.

    My Question:-

    Are they interview questions?


    Pradeep Singh

  • November 12, 2010 at 6:40 am

    #1249969

    srinadh.ramineni (11/12/2010)

    Hi for the below questions i need good and efficient answers.

    Homework? Interview? Exam?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 12, 2010 at 9:28 pm

    #1250324

    srinadh.ramineni (11/12/2010)

    Hi for the below questions i need good and efficient answers.

    Sorry, can't really help you there. What you need is the proper topic to research. This I can help you with.

    1) I am having one table and the customer is exported some data from an excel sheet to the table. After that some changes has been made and uploaded the excel sheet again in to the application. How we should decide which was updated and which was the new item was added in the excel sheet?

    MY ANSWER:

    Wht i told is : First i will take the excel data in to a temporary table and i will repeat the loop for every record and i will check whether the record was updated or not?

    But the interviewer wants me to compare all the records at atime. I don't know how it was compared?

    First, research Primary Key. Next, research MERGE command.

    2) BOM Pricing Required?

    BOM Pricing in the sense:

    One product is having sub products. Tha sub products are also having BOM. Like that i have so many levels of bom's are there. How i should find out the BOM priciong at that time?

    MY ANSWER:

    I told him to write a while loop by saying thast, loop thru which the product is not having the sub products and then take all the items and claculate the price.

    But he is not impressed with this answer.

    Nor should they be after SQL 2k5. There is now the ability to use Recursive CTEs, and you need to learn how to use those for a hierarchal join.

    3) I have one table. In that some other third party application is inserting data in to it. But one stored procedure is looking in to that table and if any row inserted in to the table then this procedure should take the inserted or updated records and have to insert in some other tables. What is the best have to be implemented in the stored procedure?

    How we know whether the record was inserted in to the main table?

    MY ANSWER:

    For the main table take one cursor. If any record was inserted in the main table then trigger will insert/update an item in some other table. Stored procedure will look in to the table and decide whether the record was inserted or not?

    But he told me that i understood the logic. Totally He is not satisfied with my answers.

    There are multiple methods to dealing with this. An AFTER TRIGGER is a satisfactory way of dealing with this in my mind. However, another way to deal with this is an indicator field that the proc would update when done. Yet another would be to use service broker. A cursor is definately NOT the way to approach this.

    One of the things I'm noticing from your answers is you approach things from a programming mindset. You need to truely embrace set methodology if you intend to pursue a career in databasing. Everything you mentioned above uses a loop. These are bad as a starting point. They should always be the final resort when everything else fails.

    EDIT: A BOM is usually a Bill of Materials. It breaks down the final product by major component, and then subcomponents of that. Think of a car. One of the components is a door. The components of the door are sheet metal, window crank, window, handle, etc. The components of the window crank are bar, knob, and gear. It's a term primarily used in manufacturing, obviously, but has other uses.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 12, 2010 at 10:28 pm

    #1250325

    Please folks... these aren't difficult questions that the OP has been asked. And this is for a job in a competative world. The OP should know the answers to these questions as a matter of rote. Let the best man win without our help.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 12, 2010 at 10:34 pm

    #1250326

    srinadh.ramineni (11/12/2010)

    Hi for the below questions i need good and efficient answers. Below are the inrterview questions asked in one interview. I am having the real time exp. But the interviewer is not satisfied with my answers. I will furnish my answers also.

    1) I am having one table and the customer is exported some data from an excel sheet to the table. After that some changes has been made and uploaded the excel sheet again in to the application. How we should decide which was updated and which was the new item was added in the excel sheet?

    MY ANSWER:

    Wht i told is : First i will take the excel data in to a temporary table and i will repeat the loop for every record and i will check whether the record was updated or not?

    But the interviewer wants me to compare all the records at atime. I don't know how it was compared?

    2) BOM Pricing Required?

    BOM Pricing in the sense:

    One product is having sub products. Tha sub products are also having BOM. Like that i have so many levels of bom's are there. How i should find out the BOM priciong at that time?

    MY ANSWER:

    I told him to write a while loop by saying thast, loop thru which the product is not having the sub products and then take all the items and claculate the price.

    But he is not impressed with this answer.

    3) I have one table. In that some other third party application is inserting data in to it. But one stored procedure is looking in to that table and if any row inserted in to the table then this procedure should take the inserted or updated records and have to insert in some other tables. What is the best have to be implemented in the stored procedure?

    How we know whether the record was inserted in to the main table?

    MY ANSWER:

    For the main table take one cursor. If any record was inserted in the main table then trigger will insert/update an item in some other table. Stored procedure will look in to the table and decide whether the record was inserted or not?

    But he told me that i understood the logic. Totally He is not satisfied with my answers.

    Please answer these questions as soon as possible.

    Srinadh

    My good man. While I appreciate the fact that you're going for a job, these are not difficult questions. Please spend some time studying both on the internet and in Books Online to show that you've earned the stripes to have such a job above others instead of asking for handouts. And, no... I'm not being mean. I'm trying to give you some decent advice because I've seen what's happened before. If you take a job under such false experience, it will quickly show and you'll earn a very bad reputation very quickly that will spread like wild fire. Even after you study up for these questions and actually learn how to do them, the bad reputation that's spread about you means that you either won't be able to find a job in the future because no one will want you or you'll need to take a job for less than you're worth for the same reason.

    For your own sake, hit the books man!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 12, 2010 at 10:36 pm

    #1250327

    First, we aren't going to give the answers to these interview questions, you need to research them yourself and determine what other methods you could apply to solve the problem. While you are doing this and you run into problems, we would be more than willing to help you understand the concepts, but you are going to have to do the heavy lifting and learn.

    The one common theme running through all your answers is RBAR, Row By Agonizing Row, processing. You talk about cursors and loops in all the answers. In a relational database like MS SQL Server, Oracle, MySQL, PostgreSQL, you really need to start thinking in sets. One way to start this paradigm shift to stop asking what I need to do to this row and ask what do I need to do to this column.

    Every one of those questions has a set-based solution that will out perform a cursor or loop based process.

  • November 13, 2010 at 1:29 am

    #1250340

    Lynn Pettis (11/12/2010)

    First, we aren't going to give the answers to these interview questions, you need to research them yourself and determine what other methods you could apply to solve the problem. While you are doing this and you run into problems, we would be more than willing to help you understand the concepts, but you are going to have to do the heavy lifting and learn.

    The one common theme running through all your answers is RBAR, Row By Agonizing Row, processing. You talk about cursors and loops in all the answers. In a relational database like MS SQL Server, Oracle, MySQL, PostgreSQL, you really need to start thinking in sets. One way to start this paradigm shift to stop asking what I need to do to this row and ask what do I need to do to this column.

    Every one of those questions has a set-based solution that will out perform a cursor or loop based process.

    That was actually a part of the help I was hoping no one would reveal on this particular thread. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 13, 2010 at 1:56 am

    #1250341

    I told my experiences. I am not saying that what ever i told was correct. But i want to know, how people will do the thing like he asked. If any mechanism is there to do the things. I am very much interested on doing these tasks. In my company i have the responsibility of writing JAVA, .NET and SQL Server code. And at the same time i should be responsible for the Project delivery.

    Don't think i was asked these replies for my next job hunt.

    Srinadh.

  • November 13, 2010 at 11:57 am

    #1250374

    First, Jeff, I thought that giving the OP some guidance in what direction he should look was appropriate. Sorry if that disappointed you, seemed the right thing to do.

    Second, srinadh.ramineni, we still aren't going to just give you the answers to these questions. What would you learn from that? I know from personal experience that I learn better if I have to work to find the solution than just having it given to me. It is the difference of learning to fish to having a fish given to you; one feeds you for a life time the other for a day.

    We are willing help you on this journey, that is why we volunteer our own time on this (and other forums as well). if you truely want to learn how to accomplish these tasks, the first step is for you to start doing some research and playing with code in a sandbox database of some kind, such as AdventureWorks or one of your own device.

    As you go down this path and run into problems, come back and post specific questions (be sure to start a new thread for each question).

    Also, before posting any questions, be sure to read the first article I reference below in my signature block regarding asking for help. it will help you post better questions.

  • November 13, 2010 at 12:49 pm

    #1250379

    I wouldn't waste my time posting the answers for OP.He is applying for a job he is not eligible for as simple as that.

    He should get the required experience or enhance his knowledge then only should think of switching for a better position.

    I have seen so many incompetent developers sitting at higher positions for which they are least qualified for.Seems OP to is trying to do that.

    OP mentioned he is responsible for writing SQL code with project delivery.

    I wonder with that level of knowledge how can anyone give him the responsibility for project delivery?

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • November 13, 2010 at 5:17 pm

    #1250392

    Lynn Pettis (11/13/2010)

    First, Jeff, I thought that giving the OP some guidance in what direction he should look was appropriate. Sorry if that disappointed you, seemed the right thing to do.

    I truly hope you don't mind if I continue to disagree with that sentiment in this case. Heh... and please don't put words in my mouth. I really appreciate what you're trying to do and just happen to disagree this time but I'm not disappointed in you. I fully agree with helping people... just not this way.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 13, 2010 at 6:23 pm

    #1250394

    Jeff Moden (11/13/2010)

    Lynn Pettis (11/13/2010)

    First, Jeff, I thought that giving the OP some guidance in what direction he should look was appropriate. Sorry if that disappointed you, seemed the right thing to do.

    I truly hope you don't mind if I continue to disagree with that sentiment in this case. Heh... and please don't put words in my mouth. I really appreciate what you're trying to do and just happen to disagree this time but I'm not disappointed in you. I fully agree with helping people... just not this way.

    I wasn't trying to put words in your mouth, Jeff. It just seemed that you were disappointed in me having given the OP a direction to go. We'll have to wait and see if he takes my advice. I personally will not give more until I see some effort on his side to learn.

  • November 13, 2010 at 9:25 pm

    #1250404

    Hi,

    Thanks for your suggestion and i will practice myself.

    Thanks to all

    Srinadh

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy