October 9, 2012 at 3:44 pm
Hi,, I have been asked this question and am clueless, Can someone help?
Assume we have loaded a flat file with patient diagnosis data into a table called “patientdata”. The table structure is:
Create table patientdata (
Firstname varchar(50),
Lastname varchar(50),
Date_of_birth datetime,
Medical_record_number varchar(20),
Diagnosis_location varchar(20),
Diagnosis_date datetime,
Diagnosis_code varchar(20))
The data in the flat file looks like this:
First Last date_of_birth Med Rec No Diag Loc Diag Date Diag Code
------- ------ ------------ ---------- ------------- ---------- ---------
'jane','jones','2/2/2001', 'MRN-11111','Mayo Clinic', '3/3/2009','diabetes'
'jane','jones','2/2/2001', 'MRN-11111','Mayo Clinic', '1/3/2009','asthma'
'jane','jones','5/5/1975', 'MRN-88888','Lahey Clinic','2/17/2009','flu'
'tom','smith','4/12/2002', 'MRN-22222','Lahey Clinic','3/3/2009','diabetes'
'tom','smith','4/12/2002', 'MRN-33333','Mayo Clinic', '1/3/2009','asthma'
'tom','smith','4/12/2002', 'MRN-33333','Mayo Clinic', '2/7/2009','asthma'
'jack','thomas','8/10/1991','MRN-44444','Mayo Clinic', '3/7/2009','asthma'
'jack','thomas','8/10/1991','MRN-44444','Lahey Clinic','3/16/2009','flu'
You can assume that no two patients have the same firstname, lastname, and date of birth combination.
Write a query that shows which of these patients were diagnosed in more than one location.
Thank you for your help!:w00t:
October 9, 2012 at 3:51 pm
This sounds a lot like a homework question. If you post what you've tried so far, people will be more inclined to help. If you haven't tried anything yet, I'd suggest looking up how count() works along with the group by and having statements.
October 9, 2012 at 4:14 pm
its actually a,,,Im trying to get a job question. part of a series of questions.
thanks for pointing me in the right direction.
still would appreciate help, its a tough market out there
October 9, 2012 at 4:26 pm
I'd be glad to help, I'd just like to see you give it a shot first. Let's see what you can come up with and even if it gives you an error, I can help you from there.
October 9, 2012 at 4:37 pm
As Rory mentioned above, this seems like homework. You want to inspect SELECT DISTINCT, GROUP BY, COUNT(*) and HAVING clauses for select statements. If you tell us what chapter it's from we can hint you further along according to what they're trying to teach for that.
If this, however, is actual real life work, then we'll need more from you to provide tested code to assist you. Please check the first link in my signature to help you setup your problem in a way we can consume and easily provide assistance.
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
October 9, 2012 at 4:39 pm
ian_bauer (10/9/2012)
its actually a,,,Im trying to get a job question. part of a series of questions.thanks for pointing me in the right direction.
still would appreciate help, its a tough market out there
Is this entry level? If not, you're going to crash and burn once you're there if this isn't second nature to you already and that's not going to help you for the next position. You will want to do some 'teach yourself SQL' courses online. There's a bunch of free ones out there, just google up a few. Even a bad one will help for the entry level stuff.
If it's entry level, be honest with them. I've hired complete newbies before and taught them, then had to RE teach them because they tried to impress me first and then never owned up to it and thought they had a lot of basic knowledge which they didn't.
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
October 9, 2012 at 5:05 pm
hi,
thank you for all the pointing in the right direction, especially the online courses. I also appreciate being told that I will crash and burn. I think that was the best advice. I am a harvard grad, 50 years old and my last job was a senior level management position with yahoo in the qa dept. I worked on the backend of the website they have up now. The market is tough and this particular job is asking through their online submission for the sql question be answered. I bought a book to learn from but wanted to get my foot in the door with the hopes that by the time the interview came I would be able to perform basic queries.
Like I said, the best advice you folks gave was that I would crash and burn. I am not opposed to learning non stop over the next week but if this is question is not simple and a beginner can not grasp it then I thank you for saving me the time.
Like I said, the market is tough and there is mortgage and mouths' to feed so I thought that asking for help would work.
Thank you again for taking the time to honestly give me advice.
have a great day
ian
October 9, 2012 at 5:30 pm
Well said. To address a few of your points:
ian_bauer (10/9/2012)
I bought a book to learn from but wanted to get my foot in the door with the hopes that by the time the interview came I would be able to perform basic queries.
What's the position for? Basic querying is the first step for most positions, and is generally considered the first of a number of skills. I'm sorry you took that so personally, but if you're willing to put in the work prior to the interview/position, if you tell us what the expected requirements of the job are we can offer suggestions to help you concentrate your immediate cramming.
I am not opposed to learning non stop over the next week but if this is question is not simple and a beginner can not grasp it then I thank you for saving me the time.
Unfortunately, my reaction was not to someone willing to do that much work for a position. Though I don't think a few weeks of intense studying will really prepare you for it, the majority of the people who come through here aren't even willing to do that and just want an easy door opener. That you aren't one of "them" didn't come through on your first post, and we get a lot of "them". I don't want to subject some other poor soul to one of "them" any more than I want to have to work with one.
The question itself is not that complex, and as I mentioned above you'll need to distinct the list and then test it via the having clause after a group by. You'll find the majority of it in Books Online under the SELECT clause information. If you don't currently have a copy of SQL Server available I recommend getting your hands on SQL Express 2k8 R2 as soon as is available, it's free and comes with all the necessary links to the online BoL.
However, your sarcasm aside, I would also recommend you speak with the people offering the position and ask them what in particular they're working with to study directly what they require when you first walk in the door. You want to avoid studying heavily on triggered views or calculated fields when they need someone who can primarily do index/execution plan optimization, and not being familiar with what book you're using I am unsure of what weight they give to any particular subset of skills.
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
October 9, 2012 at 5:52 pm
hi, thank you again for your input. The hardest thing about writing is that assumptions are made. I would apologize to you if you thought I was in any way shape or form being sarcastic. That certainly was not my intention. I truly was trying to let you know where I was coming from and that I was not a student trying to get you to do my homework. I wanted to convey that I am educated and a quick study. That I am stressed and willing to do whatever it takes to learn a skill and that I have grown up bills to pay. I also was sincere in saying thank you for telling me straight out that this is not something easily learned. While I am willing to work hard I do not want to hustle my way into a situation where I will fail or let down team members.
In closing, thank you again for your input.
Have a wonderful day
Ian
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply