locked
2 questions RRS feed

  • Question


  • 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

    1. Students: contain the details for the student in the University

    StudentID

    StudentName

    StudentStatus

    1. 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

    1. 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() ) )

    1. 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

    Wednesday, May 13, 2020 7:19 AM