May 13, 2020 at 8:26 am
Hi Eeveryone. I have a pretty simple test to do, just want to make sure i got it right!
Attaching the test with my results, please confirm that i have it correctly!
THANK YOU!
Given you have 2 tables
Students: contain the details for the student in the University
StudentID
StudentName
StudentStatus
Registrations: contain the course registration details for students
RegistrationID
RegistrationDate
RegistrationName
RegistrationStatus
StudentID
StudentID is key column from the Students table
RegistrationID is key column from the Registrations table
StudentID is the foreign key to the Registrations table from the Students table
Please write a SQL query that reruns the list of Active Students that don’t have any Active course registrations in the last 3 month relative to today.
Active Students = Student with StudentStatus = “Active” Active Registration = Registration with RegistrationStatus = “Active”
Select a.StudentID, a.StudentName
From Students a
Inner join Registrations b on a.StudentID = b.StudentID
Where a.StudentStatus = ‘Active’
And (b.RegistrationStatus <> ‘Active’
and b.RegistrationDate < DATEADD (month, -3, getdate() ) )
Given the same 2 tables please write a SQL query the returns the last registration name, registration status and registration date and the number of active registration per student.
Display “No Date” when a student does not have a last registration.
Last registration = the registration with the latest Registration Date value
Select a.StudentName, b.RegistrationName, b.registrationStatus, count(b.registrationID) as ‘active
registrations per student’,
Case when b.RegistrationDate is null then ‘No Date’
Else max(b.RegistrationDate)
End as ‘Registration Date’
From Students a
Inner join Registrations b on a.StudentID = b.StudentID
where a.StudentStatus = ‘active’
group by b.registrationDate, .StudentName, b.RegistrationName, b.registrationStatus
May 13, 2020 at 9:02 am
Do you not have the table DDL and sample data to check whether your queries work and return the correct information?
The first query's not right. It'll give you all inactive registrations over three months old for active students. That's not what the question asked for.
The second one's wrong because the inner join means that students without registrations will not be displayed. You need an outer (left) join instead. Also, although it's not wrong to use the CASE expression like you did, it's simpler to use ISNULL or COALESCE to replace a NULL.
John
May 13, 2020 at 9:39 am
Hi John. No, i do not have the table, everything i have is posted here.
Regarding 1) telling me I'm wrong doesn't really help, since i still don't know the correct answer...
Regarding 2) Just replace the inner with a left join?
Thanks!
May 13, 2020 at 9:49 am
And giving you the correct answer wouldn't help, either. It wouldn't be right for me to do your homework for you. If you don't have the sample data, then how about mocking up some CREATE TABLE and INSERT statements so that you've got something to test against? If you really don't understand this stuff, it might be worth asking your tutor to go over the basics again. Or there are plenty of step-by-step guides to query-writing if you search for them.
John
May 13, 2020 at 10:18 am
Ok. How about now? 🙂
Select a.StudentID, a.StudentName
From Students a
Inner join Registrations b on a.StudentID = b.StudentID
Where a.StudentStatus = ‘Active’
And b.RegistrationStatus not in (select RegistrationStatus
From Registrations
Where RegistrationStatus = ‘Active’
And RegistrationDate > DATEADD (month, -3, getdate() )
May 13, 2020 at 10:27 am
No, because your WHERE clause only filters out a certain type of registration. What the question asks is to filter out a certain type of student. I think you need the join in the subquery, not the main part of the query.
Of course, I could be wrong. If you don't have any sample data to try it out on, you can never be sure of that.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply