--1. Show identifiers and names of courses held on the 1st year of study. Order them alphabetically
--according to the names of courses.
SELECT CNO, CNAME
FROM COURSES
WHERE STUDYEAR = 1
ORDER BY CNAME;

--2. How many students have their names starting from letter A?
SELECT COUNT(*)
FROM STUDENTS
WHERE SNAME LIKE "A%";

--3. For each city, give the numer of students that come from this city.
SELECT CITY, COUNT(*)
FROM STUDENTS
GROUP BY CITY;

--4. For each teacher (id, title and name), give courses (ids, names) that he/she conducted.
SELECT TEACHERS.TNO, TITLE, TNAME, CNO, CNAME
FROM TEACHERS, TSC
WHERE TEACHERS.TNO = TSC.TNO;

--5. Show ids of teachers that did not have classes on the 1st year of study.
SELECT TNO
FROM TEACHERS
WHERE NOT EXISTS
    (SELECT *
    FROM COURSES, TSC
    WHERE COURSES.CNO = TSC.CNO
    AND TEACHERS.TNO = TSC.TNO
    AND STUDYEAR = 1);

--6. On which year of study there were most courses?
CREATE TABLE YEARS AS
    SELECT STUDYEAR, COUNT(*) YSUM
    FROM COURSES
    GROUP BY STUDYEAR;

SELECT STUDYEAR
FROM YEARS
WHERE YSUM IN
    (SELECT MAX(YSUM)
    FROM YEARS);

DROP TABLE YEARS;

--7. On which year of study there was the greatest average grade?
CREATE TABLE AVGRADE AS
    SELECT STUDYEAR, AVG(GRADE) AVGGR
    FROM COURSES, TSC
    WHERE COURSES.CNO = TSC.CNO
    GROUP BY STUDYEAR;

SELECT STUDYEAR
FROM AVGRADE
WHERE AVGGR IN
    (SELECT MAX(AVGGR)
    FROM AVGRADE);

DROP TABLE AVGRADE;

--8. How many teachers did not have any classes?
SELECT COUNT(*)
FROM TEACHERS
WHERE NOT EXISTS
    (SELECT *
    FROM TSC
    WHERE TSC.TNO = TEACHERS.TNO)

9. FOR each YEAR OF study, SHOW the SUM OF teaching hours. ORDER the RESULT according TO the
--years of study.
SELECT STUDYEAR, SUM(HOURS)
FROM COURSES, TSC
WHERE COURSES.CNO = TSC.CNO
GROUP BY STUDYEAR;

--10. Which students (id, name) have the greatest average grade?
CREATE TABLE AVGRADE AS
    SELECT STUDENTS.SNO, AVG(GRADE) AVGGR
    FROM STUDENTS, TSC
    WHERE STUDENTS.SNO = TSC.SNO
    GROUP BY STUDENTS.SNO;

SELECT SNO, SNAME
FROM STUDENTS
WHERE SNO IN
    (SELECT SNO
    FROM AVGRADE
    WHERE AVGGR =
        (SELECT MAX(AVGGR)
        FROM AVGRADE));

DROP TABLE AVGRADE;

--11. Show the list of all students (ids, names) ordered according to decreasing average grades.
CREATE TABLE AVGRADE AS
    SELECT STUDENTS.SNO, SNAME, AVG(GRADE) AVGG
    FROM STUDENTS, TSC
    WHERE STUDENTS.SNO = TSC.SNO
    GROUP BY STUDENTS.SNO;

SELECT SNO, SNAME
FROM AVGRADE
ORDER BY AVGG DESC;

DROP TABLE AVGRADE;

--12. Which teachers (id, title, name) have the most numer of subordinates?
CREATE TABLE SUBS AS
    SELECT OVER.TNO, COUNT(UNDER.TNO) SUBNO
    FROM TEACHERS UNDER, TEACHERS OVER
    WHERE OVER.SUPNO = UNDER.TNO
    GROUP BY OVER.TNO;

SELECT TNO, TNAME
FROM TEACHERS
WHERE TNO IN
    (SELECT TNO
    FROM SUBS
    WHERE SUBNO =
        (SELECT MAX(SUBNO)
        FROM SUBS));

DROP TABLE SUBS;

--13. Which teachers (id, title, name) have no supervisor?
SELECT UNDER.TNO, UNDER.TITLE, UNDER.TNAME
FROM TEACHERS UNDER
WHERE NOT EXISTS
    (SELECT *
    FROM TEACHERS OVER
    WHERE OVER.SUPNO = UNDER.TNO);

--14. Which teachers (ids, names) conducted more than 3 courses and how many courses they
--conducted?
CREATE TABLE TC AS
    SELECT TEACHERS.TNO, TNAME , COUNT(*) CRSUM
    FROM TEACHERS, TSC
    WHERE TEACHERS.TNO = TCS.TNO
    GRUOP BY TEACHERS.TNO, TNAME;

SELECT TNO, TNAME
FROM TC
WHERE CRSUM > 3;

DROP TABLE TC;

--15. Show ids and names of those students who obtained at least one 5.0 from any subject.
CREATE TABLE STUDMAXGRADE AS
    SELECT STUDENTS.SNO, SNAME, MAX(GRADE) AS MAXGRADE
    FROM STUDENTS, TCS
    WHERE STUDENTS.SNO = TCS.SNO
    GRUOP BY STUDENTS.SNO, SNAME;

SELECT SNO, SNAME
FROM STUDMAXGRADE
WHERE MAXGRADE > 5;

DROP TABLE STUDMAXGRADE;

--16. Show ids and names of those students who obtained at least one grade that is maximal from
--all the grades obtained by anyone. How many students satisfy this condition?
CREATE TABLE BESTGRADE AS
    SELECT CNO, MAX(GRADE) AS BGRD
    FROM TSC
    GROUP BY CNO;

SELECT DISTINCT STUDENTS.SNO, SNAME
FROM STUDENTS, TSC
WHERE STUDENTS.SNO = TSC.SNO
AND TSC.CNO =
    (SELECT BESTGRADE.CNO
    FROM BESTGRADE
    WHERE BESTGRADE.CNO = COURSES.CNO);

DROP TABLE BESTGRADE;

--17. How many students had mathematics on the 2nd year of study?
SELECT COUNT(DISTINCT SNO)
FROM TCS, COURSES
AND COURSES.CNO = TCS.CNO
AND COURSES.CNAME = 'Mathematics'
AND COURSES.STUDYEAR = 2;

--18. Create a view that contains all the data from TSC extended by names of students, titles and
--names of teachers and names of courses.
CREATE VIEW TCSJoined
(TeachNo, StudNo, CourseNo, TeachHours, CourseGrade, StudName, TeachTitle, TeachName, CourseName)
AS SELECT TCS.TNO , TCS.SNO, TCS.CNO, TCS.HOURS, TCS.GRADE, STUDENTS.SNAME, TEACHERS.TITLE, TEACHERS.TNAME, COURSES.CNAME
FROM TCS, TEACHERS, STUDENTS, COURSES
WHERE TCS.TNO = TEACHERS.TNO
AND TCS.SNO = STUDENTS.SNO
AND TCS.CNO = COURSES.CNO;


--19. For each city, give the total numer of students and teachers that come from this city.

--nk

--20. Which teacher (id, name) gave the students the worst grades (i.e. the average grade from all
--the grades given by this teacher is the lowest).
CREATE TABLE AVGRADE AS
    SELECT TNO, AVG(GRADE) AVGGR
    FROM TCS
    GROUP BY TNO;

SELECT TEACHERS.TNO, TNAME
FROM TEACHERS, AVGRADE
WHERE TEACHERS.TNO IN
    (SELECT AVGRADE.TNO
    FROM AVGRADE
    WHERE AVGGR =
    (SELECT MIN(AVGGR)
    FROM AVGRADE));

DROP TABLE AVGRADE;

--21. Create an archive for table TSC. The archive should contain ids and names of students and
--teachers, ids and names of courses and grades. Next insert to this archive all the data from
--TSC and clear TSC.

--O chuj tu chodzi?


--22. For each course, show the average grade obtained by the students from this course. Next
--show these courses (ids, names) for which this average exceeds the average of all the
--averages.
CREATE TABLE AVGRADE AS
    SELECT CNO, AVG(GRADE) AVGG
    FROM TCS
    GROUP BY CNO;

SELECT COURSES.CNO, CNAME, AVGGR
FROM COURSES, AVGRADE
WHERE COURSES.CNO = AVGRADE.CNO;

SELECT COURSES.CNO, CNAME
FROM COURSES, AVGRADE
WHERE COURSES.CNO = AVGRADE.CNO
AND AVGRADE.AVGGR >
    (SELECT AVG(AVGGR)
    FROM AVGRADES);

DROP TABLE AVGRADE;

--23. Show those teachers (ids, titles, names) who on the 1st year of study taught students that
--come from the same city as the teacher.
SELECT DISTINCT TEACHERS.TNO, TITLE, TNAME
FROM TEACHERS, STUDENTS, COURSES, TCS
WEHERE COURSES.CNO = TCS.CNO
AND TEACHERS.TNO = TCS.TNO
AND STUDENTS.SNO = TCS.SNO
AND TEACHERS.CITY = STUDENTS.CITY
AND COURSES.STUDYEAR = 1;

--24. Show the course (id, name) that had the least numer of students (but was conducted on any
--year of study).
CREATE TABLE CRSTUD AS
    SELECT COURSES.CNO, COUNT(*) STUDAMT
    FROM COURSES, TCS
    WHERE COURSES.CNO = TCS.CNO
    GROUP BY COURSES.CNO, CNAME;

SELECT COURSES.CNO, CNAME
FROM COURSES, CRSTUD
WHERE COURSES.CNO = CRSTUD.CNO
GROUP BY COURSES.CNO, CNAME;
WHERE CRSTUD.STDAMT =
    (SELECT MIN(STUDAMT)
    FROM CRSTUD);

DROP TABLE CRSTUD;

--25. For each year of entry the studies, show the average grade of students that entered studies in
--this year. The result should be ordered according to decreasing averages.
SELECT SYEAR, AVG(GRADE) AS 'AVGRADE'
FROM STUDENTS, TCS
WHERE STUDENTS.SNO = TCS.SNO
GROUP BY SYEAR
ORDER BY 'AVGRADE' DESC;