ALTER VIEW "DBA"."v_persons_atleast_4eap" (FirstName, LastName) AS
select DBA.Person.FirstName, DBA.Person.LastName from Person, Faculty, Course, Registration
where person.FacultyId = Faculty.Id and Faculty.Name = 'Matemaatika-informaatikateaduskond'
and person.id = Registration.PersonID and Registration.CourseID = Course.ID and Course.EAP > 4

ALTER VIEW "DBA"."v_mostA"(FirstName, LastName, NrOfA) AS
select DBA.Person.FirstName, DBA.Person.LastName, count(*) from Person, Registration, Faculty
where person.Id = Registration.PersonId and Registration.FinalGrade = 'A' and person.FacultyId = Faculty.Id
and Faculty.Name = 'Matemaatika-informaatikateaduskond'
Group BY FirstName, LastName

INSERT INTO Course
VALUES (102,9,'Andmebaaside teooria','MTAT.03.998',6,'Arvestus',null)

INSERT INTO registration (CourseId,PersonId,FinalGrade)
SELECT 102, p.id, NULL FROM Course as c
JOIN Registration as r ON (c.Id = r.CourseId)
JOIN Person as p ON (r.PersonId = p.Id)
WHERE c.Name = 'Andmebaasid' and (r.FinalGrade='A' or r.FinalGrade='B')



ALTER VIEW "DBA"."v_andmebaasideTeooria" (PersonId, FirstName, LastName) AS
select DBA.Person.Id, DBA.Person.FirstName, dba.Person.LastName from Person, Registration, Course
where Person.Id = Registration.PersonId and Registration.CourseId = Course.ID and Course.Name = 'Andmebaaside Teooria'

ALTER VIEW "DBA"."v_top20A" (FirstName, LastName, nrOfA) AS
select TOP 20 dba.Person.FirstName, dba.Person.LastName, count(*) from Person, Registration
where person.id = Registration.PersonId and Registration.FinalGrade = 'A'
Group BY FirstName, LastName
order by count() desc


ALTER VIEW "DBA"."v_top20Students"(FirstName, LastName, AvarageGrade) AS
SELECT TOP 20 FirstName, LastName,avg(
       CASE FinalGrade
            WHEN 'A' THEN 5
            WHEN 'B' THEN 4
            WHEN 'C' THEN 3
            WHEN 'D' THEN 2
            WHEN 'E' THEN 1
            WHEN 'F' THEN 0
        END) as keskmine
FROM Person key join Registration
group by FirstName, LastName
order by keskmine DESC