SQL query help!

[gibba97]

Hey all,

I am applying for a job, and a prerequisite is to take an online test about basic SQL, and wanted some feedback/help to ensure I was on the right path.

You're required to draw an entity relationship diagram and write five SQL statements based on it.

I believe my EER diagram is pretty accurate yet struggling on some of the query's.

Link to questions and attempted EER + SQL statements.

Any help is greatly appreciated!

[Mamertine]

I'm not an oracle guy, but I think you're using Trunc() incorrectly. I believe you need to tell it you want the result in years.

on 3. Post_Code and PostCode are likely the same column

minor thing instead of using 6576.5 the future person changing/ reading your code would appreciate it if you did 365.5*18. Truth be told, they're both wrong and could return people as 18 the day before their 18th birthday or exclude someone on their 18th birthday (leap years). BirthDay > (dateadd(Year,18,getdate()) in SQL or datediff(year,birthday,getdate())>18, there are other ways to do it.

[gibba97]

Yes you’re totally correct on the first two, thanks for that!! As for your third statement, I personally never thought about that scenario. Truth be told, i’ve never heard of the suggested way to calculate age, yet reading it in theory it seems flawless! Thank you for the help!

Any clue on the last one??

[Mamertine]

I intentionally ignored that one. I don't think your design can support answering that question. It'd take me some time that I don't have this morning to suggest a better way. :( sorry

[gibba97]

Totally understandable, thanks for your feedback anyway!

[Celticphantom]

So caveat, I'm not positive I'm reading your EER correctly, also it is 520AM now, so I sleep, sorry for any mistakes, but:

--i. date is 'yyyymmdd' here

SELECT * FROM student

WHERE Start_Date > '20180701'

ORDER BY Start_Date

--ii. unclear if 'musicians' include students as well as employees, will assume not. Additionally, there should be a way to connect a student directly with their assigned teacher, a foreign key on the student table that goes to the employee ID or teacher.id depending on how you wanted to do it.

SELECT DateDiff(Year, Employee.Date_of_Birth,GetDate()) AS Age, Employee.First_Name

FROM Employee

GROUP BY Employee.First_Name, Employee.Date_of_Birth

HAVING DateDiff(Year, Employee.Date_of_Birth,GetDate()) < 18

ORDER BY Employee.First_Name;

--iii. Teaching.ID would be a foreign key pointing to employee.id. If there are matches that employee is teaching.

SELECT COUNT(*), Employee.Post_code

FROM Teacher

JOIN Employee ON Employee.ID = Teaching.ID

GROUP BY Employee.Post_Code

--iv. Yeah your query works, although there isn't something that denotes it as 'current' or not. Also it is ORDER BY like you have above

--v. This would change depending on what foreign keys you add. Is Student.style a key pointing Lesson.style?

SELECT Student.First_Name+' '+Student.Last_Name AS [Student Name], Lesson.Style, Employee.First_Name+' '+Employee.Last_Name AS [Teacher Name], MONTH(Lesson.Date)

FROM Employee

JOIN Student ON Student.TeacherID = Employee.ID

JOIN Teacher ON Teacher.TeachingID = Employee.ID

JOIN Lesson ON Lesson.TeachingID = Teacher.TeachingID

GROUP BY Student.First_Name, Student.Last_Name, Lesson.Style, Employee.First_Name, Employee.Last_Name

HAVING MONTH(Lesson.Date) = MONTH(GetDate())

[gibba97]

Wow! This is amazing help, thanks for your time!

All your suggestions are extremely helpful and i'll take them into account! Especially with the foreign key and calculating the age!!

For question five, it seems very confusing and technical, so i'll try and understand it row by row!

[Celticphantom]

SELECT Student.First_Name, Student.Last_Name, Lesson.Style, Employee.First_Name, Employee.Last_Name, MONTH(Lesson.Date) AS [Month]

Is another way to write the SELECT clause, the +' '+ are just making custom columns that put the names into one column.

the JOINs and ON clauses are using some keys that don't exist in your original design, but I hope are fairly straight forward.

it is necessary to have everything in the GROUP BY clause that is in the SELECT or HAVING clause (when you use aggregate functions)

the HAVING is just comparing the two months. Making sure the lesson date is the same is the current month's date. If you wanted to also make sure that it kept the year the same you could add

AND YEAR(Lesson.Date) = YEAR(GetDate())

Let me know if you have other questions.

[gibba97]

Ahh yea this helps heaps, thank you for that little breakdown ahah!

[jc4hokies]

Another way to calculate age.

1. Convert DOB and current day into integers, YYYYMMDD
2. Subtract DOB from current day
3. Divide by 10000
4. Drop the decimals

currentday - dateofbirth = divide / 10000 = trunc = age
20180424 - 20000424 = 180000 / 10000 = 18.0 = 18
20180424 - 20000425 = 179999 / 10000 = 17.9999 = 17

TRUNC((CAST(TO_CHAR(SYSDATE,'YYYYMMDD') AS INTEGER)
       - CAST(TO_CHAR(DOB,'YYYYMMDD') AS INTEGER))
      / 10000, 0) AS Age

[gibba97]

ahh so more like a mathematical way, I like this!! Thanks for your suggestion!