February 27, 2009 at 3:57 pm
1. An application has 6 text boxes(say 1,2,3,4,5,6), and these 6 textboxes corresponds to 6 columns in 6 different tables, Now the problem is user can input any combination of the textboxes(such as 1,2,3 or 1,2 or 3,4,5 or 1,5,6), and the data should be retrieved. what is the best approach in solving this problem...
2. An application has front end as java and backend as SQL 2005, there is a java code that is to be implemented in multiple stored procedures, Now the problem is whenever i change the java code. it has to be reflected in all the stored procedures, it is always headache to go and change in all the stored procedures. What will be the easy solution in performing this task.
3. A parent table has primary key as pid, and has 3 child tables with foreign key relationship with pid. Now the problem is 3 years back data in the parent table has been lost. (say data from 2006 is lost,). is there any solution to recover the parent table.
please suggest some solution approaches to these problems....thanks in advance..
February 27, 2009 at 4:36 pm
1. Try looking into Bitwise operator.
2. Create a function or a procedure which has the jave code and make a call to that function/ procedure in all other procedure.
3. How can the data will lost from parent table if there was a relationship exists? For any reason if there was no physical relationship, you will able to determine which rows are deleted but nothing more than that (use left join). You may need to restore from backup.
Amit Lohia
February 27, 2009 at 5:22 pm
Since, I'm guessing, this is homework, why don't you tell us your ideas about how to accomplish these things and we'll provide guidance as needed. Most people are happy to help but not so willing to do your work for you.
February 27, 2009 at 6:23 pm
Thanks David Webb for you comments...I would appreciate if you can solve the problem. And to let you know I am not in any college to do Homework or any tranning institute. I am Microsoft Certified professional, with 4 plus years of SQL Server experience.
These are the questions that I faced during a recent interview. I have my own solutions, I just wanted SQL community to know these questions and wanted to compare with mine. My solutions which I answered during the face-to-face interview are:-
1. Using Joins or Bitwise operators like AND and OR. or using indexed views, with clustered index being the field that is frequently used in testing environment and other fields included in a non-clustered index(this will give better performance)
2. Using function or stored procedures depending upon the value that is returned by the java code. and calling this function in all the stored procedures, in which the java code is used(FUNCTION if java code returns a single value, STORED PROCEDURE if java code returns a set of values).
3. Using the attributes of the child table and from the Datawarehouse, we can create the parent table.
thanks for all the suggestions.
March 1, 2009 at 8:39 am
You would need to provide more detail, or ask more questions for these, IMHO.
We are happy to help, but we want to see you do work. Thanks for putting in your responses.
1. What do you mean by best way to retrieve the data? If there are 6 text boxes, you would use 6 parameters passed back. I'm not sure where the bitwise thing comes in. It doesn't matter if it's 1 table or 6, you can pass the 6 pieces of data to a stored proc or retrieve it from a stored proc easily. You can have NULL values, so you'd need more detail if there was something else to the question.
2. Are you talking stored procedures here? What types of changes? If you have split logic, you need to change both. The Java piece, IMHO, is harder to change because of the deployment. You want to keep a good API moving between client and server. I'd look to rev the stored procs, not removing the old ones, to make sure things work.
3. You can't recreate the parent table if this is a properly normalized table. You can get the PIDs back, but you can't recreate the other missing columns unless the data is stored in the children. Then it wouldn't be designed well. And if you had FKs, you couldn't "lose" the data in the parent since a DELETE wouldn't take. I suppose you could have corruption, but you'd need a backup then.
March 2, 2009 at 9:13 am
I'm guessing that #1 has to do with how you would handle unpredictable combinations of search criteria being entered by the user. The answer to this could be successive refining of a result set based on each criteria, or it could be dynamic SQL.
Steve is absolutely correct about #3. In a properly normalized schema, you'd be able to recover the keys used to reference the child table and nothing else. Time to go back and see if those 2006 backups are still usable.
What about the rest of the questions?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply