Level of difficulty for SQL question?

I took a SQL test for a job interview a month back which I obviously didnt get so I was wondering how close my answers were and the level of difficulty these questions are.

I dont know anyone that knows SQL so I thought I would post it and see if anyone cared to look and maybe even correct or critique my answers.

DATABASE

Table: AttendingPhysician
Name - Type
AttendingPhysicianID - Long Integer
AttendingPhysicianName - Text
Service - Text
ReportingService - Text
Active - Yes/No

Table: Registration
MedicalRecordNumber - Text
PatientLastName - Text
PatientFirstName - Text
DateOfBirth - Date/Time
SocialSecurityNumber - Text
PatientAddressLine1 - Text
PatientAddressLine2 - Text
PatientCity - Text
PatientState - Text
PatientZipCode - Text
PatientCounty - Text
PatientPhone - Text
PatientRace - Text
PatientSex - Text
PatientEmployer - Text
PCPID - Long Integer
Deceased - Yes/No

Table: VisitTable
MedicalRecordNumber - Text
VisitNumber - Long Integer
PreAdmitDate - Date/Time
AdmitDate - Date/Time
DischargeDate - Date/Time
TotalCharges - Currency
TotalPayments - Currency
ARBalance - Currency
PatientTypeMnemonic - Text
DRGNumber - Long Integer
DRGGrouper - Text
ProgramMnemonic - Text
AdmissionSource - Text
AdmittingNurseStation - Text
DischargeLocation - Text
RevenueFSC - Long Integer
FinalDischargeDisposition - Text
AttendingPhysicianID - Long Integer

QUESTIONS/ANSWERS

  1. Write a query to remove duplicate physicians from the AttendingPhysician table.

DELETE AttendingPhysicianID FROM AttendingPhysician
WHERE AttendingPhysicianID IN(SELECT DISTINCT (AttendingPhysicianID) FROM AttendingPhysician GROUP BY AttendingPhysicianID HAVING COUNT(*)>1)

  1. Write a query to return the average age and count of all patients discharged in October 2009.

SELECT COUNT(DateOfBirth), DATEDIFF(year,CURRENTDATE,DateOfBirth) AS AgeInYears
AVG(AgeInYears)
FROM Registration INNER JOIN VisitTable
ON Registration.MedicalRecordNumber= VisitTable.MedicalRecordNumber
WHERE VisitTable.DischargeDate =‘October 2009’

  1. Write a query to list all patients (medical record number,last name,first name) who stayed in the hospital longer than 5 days only showing each patient one time.

SELECT DISTINCT Registration.MedicalRecordNumber, Registration.PatientFirstName,
Registration.PatientLastName, SUM(VisitTable.DischargeDate - VisitTable.AdmitDate ) AS NumberOfDays
FROM Registration INNER JOIN VisitTable
ON Registration.MedicalRecordNumber= VisitTable.MedicalRecordNumber
GROUP BY NumberOfDays
WHERE NumberOfDays>5

  1. Write two different queries to list the patient county of all patients that were not seen in 2009, but have been seen in 2010.
    Query 1. Write this query using the IN statement

SELECT PatientCounty
FROM Registration
Where IN (SELECT AdminDate FROM VisitTable WHERE AdminDate< = ’01 Jan 2009’ OR AdminDate>=’01 Jan 2010’)

Query 2. Write this query using a JOIN

SELECT PatientCounty
FROM Registration InnerJoin VisitTable
ON Registration.MedicalRecordNumber=VisitTable.MedicalRecordNumber
Where AdminDate< = ’01 Jan 2009’ OR AdminDate>=’01 Jan 2010’

  1. Your task is the write a report to show the count of patients seen by attending physician/patient age. The patient age is to be grouped in the following year buckets: 0-5, 6-10, 10-15, 15+.
    a. Write the query below.

SELECT COUNT(*) AS Num, VisitTable.AttendingPhysicianID
DATEDIFF(year, DateOfBirth,CURRENTDATE) AS AgeInYears
FROM Registration INNER JOIN VisitTable
ON VisitTable.MedicalRecordNumber= Registration.MedicalRecordNumber
GROUP BY AgeInYears Where AgeInYears IN(<=5,>5 AND <11,>9 AND <16,=>15)
ORDER BY AttendingPhysicianID, AgeInYears

b. Would you develop this report using a crosstab or a list? List

your answers have multiple errors, and the level of difficulty of the test questions was basic to intermediate

here are just a few errors to get started …

  1. DELETE AttendingPhysicianID FROM …

you cannot delete a column

SELECT DISTINCT (AttendingPhysicianID) … GROUP BY AttendingPhysicianID

DISTINCT is redundant with GROUP BY, and the parentheses are not required

  1. SELECT COUNT(DateOfBirth), DATEDIFF(year,CURRENTDATE,DateOfBirth) AS AgeInYears
    AVG(AgeInYears) …

comma missing

from the DATEDIFF function, i would conclude that you’re using microsoft sql server, and so CURRENTDATE is wrong

WHERE VisitTable.DischargeDate =‘October 2009’

that’s not gonna work either

  1. totally messed up GROUP BY clause, sitting (incorrectly) in front of the WHERE clause

  2. Where IN (SELECT …

syntax error, missing expression for the IN

FROM Registration InnerJoin

syntax error, it should be INNER JOIN, not INNERJOIN

i’m gonna stop here

So I bombed it. It was hard writing it in word without being able to run the query. My SQL is intermediate at best at this point. Thanks for the comments.

what’s your experience, in terms of building stuff? keep at it and build a project of some sort, if needs be.

The more familiar you become with writing queries, the more easy you will find it, to write in Word (pah!), and see when some syntax errors need to be fixed.

Word is a give away imv. use a proficient text editor.

hth

bazz

Thanks for the advice I am definitely going to create some self projects to improve my skills. I actually answered the questions on a written test then later I just transferred my answers to see how I did.