1. Pokaż identyfikatory i nazwy przedmiotów prowadzonych na 1. roku studiów. Uporządkuj
alfabetycznie według nazw przedmiotów.

select cno, cname
from courses
where studyear=1
order by cname

2. Ilu studentów ma nazwiska rozpoczynające się na literę A?

select count(*)
from students
where sname like "A%"

3. Dla każdego miasta podaj liczbę studentów, którzy pochodzą z tego miasta.

select city, count(*)
from students
group by city

4. Dla każdego nauczyciela (identyfikator, tytuł i nazwisko) podaj przedmioty (identyfikatory i
nazwy), które prowadzi.

select distinct teachers.tno, title, tname, cno, cname
from tsc,teachers,courses
where tsc.tno=teachers.tno and tsc.cno=teachers.cno

5. Dla każdej pary (ocena, rok studiów) podaj liczbę wystawionych takich ocen.

select grade,studyear,count(grade)
from courses,tsc
where tsc.cno=courses.cno
group by grade,studyear
order by 1

6. Pokaż identyfikatory tych nauczycieli. którzy nie prowadzą zajęć na 1. roku.

select tno
from teachers
where tno not in (select tsc.tno
                  from tsc, courses
                  where tsc.cno=courses.cno and tsc.tno=teachers.tno and courses.studyear=1)

7. Na którym roku studiów jest najwięcej przedmiotów?

select top 1 studyear,count(*)
from courses
group by studyear
order by 2

8. Na którym roku jest największa średnia ocen?

select top 1 studyear,avg(grade)
from courses, tsc
where tsc.cno=courses.cno
group by studyear
order by 2 desc

9. Ilu nauczycieli nie miało w ogóle zajęć?

select count(*)
from teachers
where tno not in (select tno from tsc)

10. Dla każdego roku studiów pokaż sumę godzin zajęć prowadzonych na tym roku dla
studentów. Wynik uporządkuj według lat studiów.

select studyear, sum(hours)
from tsc,courses
where tsc.cno=courses.cno
group by studyear
order by 2

11. Który student (identyfikator i nazwisko) ma największą średnią ocen?

select top 1 students.sno, sname ,AVG(grade)
from tsc,students
where tsc.sno=students.sno
group by students.sno,sname
order by 2 desc

12. Pokaż listę wszystkich studentów (identyfikatory i nazwiska) uporządkowaną według
malejących średnich ocen.

select students.sno, sname ,AVG(grade)
from tsc,students
where tsc.sno=students.sno
group by students.sno,sname
order by 3 desc

13. Pokaż nauczyciela (identyfikator, tytuł, nazwisko), który ma najwięcej podwładnych.

select t1.tno, t1.title, t1.tname
from teachers t1
where t1.tno = (select top 1 supno
                from teachers
                group by supno
                order by COUNT(*) desc)

14. Pokaż nauczyciela (identyfikator, tytuł, nazwisko), który nie ma szefa.

select t1.tno, t1.title, t1.tname
from teachers t1
where supno is null

15. Którzy nauczyciele (identyfikatory i nazwiska) prowadzą więcej niż 3 przedmioty i ile jest tych
przedmiotów?

select tsc.tno,tname
from tsc,teachers
where teachers.tno=tsc.tno
group by tsc.tno,tname
having COUNT(*)>3

16. Podaj identyfikatory i nazwiska studentów, którzy otrzymali oceny 5.0 z jakiegokolwiek
przedmiotu.

select distinct students.sno,sname
from students,tsc
where students.sno=tsc.sno and grade=5

17. Podaj identyfikatory i nazwiska studentów, którzy z jakiegokolwiek przedmiotu otrzymali ocenę
równą ocenie maksymalnej otrzymanej przez studentów. Następnie pokaż, ilu jest takich
studentów.

select distinct students.sno,sname
from students,tsc
where students.sno=tsc.sno and grade=(select top 1 grade from tsc order by grade desc)

select COUNT(*)
from tsc
where grade=(select top 1 grade from tsc order by grade desc)

18. Ilu studentów uczyło się matematyki na drugim roku studiów?

select COUNT(*)
from tsc,courses
where cname='Mathematics' and studyear=2 and tsc.cno=courses.cno

19. Utwórz widok zawierający wszystkie informacje z tablicy TSC rozszerzone i nazwiska
studentów, tytuły i nazwiska nauczycieli oraz nazwy przedmiotów.

create view tscex as
select tsc.*, sname, title, tname, cname
from tsc,courses,students,teachers
where tsc.cno=courses.cno and tsc.sno=students.sno and tsc.tno=teachers.tno

20. Dla każdego miasta podaj łączną liczbę studentów i nauczycieli, którzy pochodzą z tego
miasta.

select city,COUNT(city)
from (select city from teachers
      union all
      select city from students) cs
group by city

21. Pokaż nauczyciela (identyfikator i nazwisko), który postawił studentom najgorsze oceny (tzn.
wystawiona przezeń średnia ocen jest najniższa).

select teachers.tno,tname
from tsc,teachers
where teachers.tno=tsc.tno
group by teachers.tno,tname
having AVG(grade)= (select top 1 AVG(t2.grade)
                    from tsc t2
                    group by t2.tno
                    order by 1 asc)

22. Utwórz archiwum dla tablicy TSC. Archiwum ma zawierać: identyfikatory i nazwiska studentów
i nauczycieli, identyfikatory i nazwy przedmiotów oraz oceny. Następnie przepisz do tego
archiwum dane z tablicy TSC, po czym usuń dane z TSC.

drop tscold

select tsc.sno ,sname, tsc.tno, tname, tsc.cno, cname, grade
into tscold
from tsc,courses,students,teachers
where tsc.cno=courses.cno and tsc.sno=students.sno and tsc.tno=teachers.tno

delete from tsc

select * from tscold

23. Dla każdego przedmiotu oblicz średnią ocen. Następnie pokaż te przedmioty (identyfikatory i
nazwy), z których średnia ocena przekracza średnią z tych średnich.

create view t_avg as
select tsc.cno,cname,AVG(grade) sr
from tsc,courses
where tsc.cno=courses.cno
group by tsc.cno, cname

select * from t_avg

select cno,cname
from t_avg
where sr>(select AVG(sr) from t_avg)

drop view t_avg

24. Pokaż tych nauczycieli (identyfikatory, tytuły i nazwiska), którzy na pierwszym roku studiów
uczyli tych studentów, którzy pochodzą z tego samego miasta co oni.

select tsc.tno,title,tname
from teachers,tsc,students,courses
where tsc.tno=teachers.tno and tsc.sno=students.sno and tsc.cno=courses.cno
      and studyear=1 and teachers.city=students.city

25. Pokaż przedmiot (identyfikator, nazwa), na który uczęszczało najmniej studentów.

select top 1 tsc.cno,cname
from tsc,courses
where tsc.cno=courses.cno
group by tsc.cno,cname
order by COUNT(sno) asc

26. Dla każdego roku rozpoczęcia studiów podaj średnią ocen studentów, którzy rozpoczęli studia
w tym roku. Wynik uporządkuj malejącą według średnich ocen.

select syear,AVG(grade)
from students, tsc,courses
where tsc.sno=students.sno and tsc.cno=courses.cno
group by syear
order by 2 desc