create table student
(
FIO varchar2(30),
Gruppa char(10),
Discipline char(10),
Ocenka integer,
ExamDate DATE
);
insert into student(FIO,Gruppa,Discipline,Ocenka,ExamDate)
values('Shamin Nikita Dmitrievich','IT0701','bd','4','15-NOV-2011');
insert into student(FIO,Gruppa,Discipline,Ocenka,ExamDate)
values('Malanin Aleksandr xz','IT0701','bd','5','15-NOV-2011');
insert into student(FIO,Gruppa,Discipline,Ocenka,ExamDate)
values('Kosikov Kirill xz','IT0701','bd','5','15-NOV-2011');
insert into student(FIO,Gruppa,Discipline,Ocenka,ExamDate)
values('Rubtsov Andrey xz','IT0701','bd','2','15-NOV-2011');
insert into student(FIO,Gruppa,Discipline,Ocenka,ExamDate)
values('Razbesov Konstantin xz','IT0702','bd','5','18-NOV-2011');
create table teacher
(
FIO varchar2(30),
Discipline char(10),
Examination date,
Telephone integer
);
insert into teacher(FIO,Discipline,Examination,Telephone)
values('Ivanov Ivan Ivanych','bd','15-NOV-2011',7775577);
insert into teacher(FIO,Discipline,Examination,Telephone)
values('Petrov Petr Petrovich','bd','18-NOV-2011',5557755);
insert into teacher(FIO,Discipline,Examination,Telephone)
values('Ivanov Ivan Ivanych','bd','18-NOV-2011',7775577);
insert into teacher(FIO,Discipline,Examination,Telephone)
values('Petrov Petr Petrovich','bd','15-NOV-2011',5557755);
select t.Discipline, t.FIO, avg(s.Ocenka) avg_ocenca from students s
inner join teacher t on t.Discipline = s.Discipline
where s.ExamDate = '2011-11-15'
group by t.Discipline, t.FIO
Discipline FIO avg_ocenca
bd Ivanov Ivan Ivanych 4
bd Petrov Petr Petrovich 4
pc Petrov Petr Petrovich 4
Если Вы уже зарегистрированы на Портале - войдите в систему, если Вы еще не регистрировались - пройдите простую процедуру регистрации.