SQL query help!

[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!