PRACTICA CON SOLUCION

-- Correr el script y responder utilizando SQL
create table alumno(
idalumno char(4) primary key,
paterno varchar(20),
materno varchar(20),
nombre varchar(20));

insert into alumno values('0001','Quispe','Rivas','Juan');
insert into alumno values('0002','Soto','Castro','Luis');
insert into alumno values('0003','Rivas','Quino','Ana');
insert into alumno values('0004','Castro','Rojas','Maria');

create table curso(
idcurso char(5) primary key,
curso varchar(20));

insert into curso values('MA100','MATEMATICA');
insert into curso values('FI100','FISICA');
insert into curso values('QU100','QUIMICA');
insert into curso values('FI101','FILOSOFIA');
create table nota (
idalumno char(4),
idcurso char(5),
nota numeric);

insert into nota values('0001','MA100',12);
insert into nota values('0001','FI100',14);
insert into nota values('0001','QU100',08);
insert into nota values('0001','FI101',16);
insert into nota values('0002','MA100',11);
insert into nota values('0002','FI100',08);
insert into nota values('0002','QU100',09);
insert into nota values('0002','FI101',10);
insert into nota values('0003','MA100',17);
insert into nota values('0003','FI100',13);
insert into nota values('0003','QU100',18);
insert into nota values('0003','FI101',19);
insert into nota values('0004','MA100',14);
insert into nota values('0004','FI100',15);
insert into nota values('0004','QU100',13);
insert into nota values('0004','FI101',15);

-------Listado de notas maximas por curso-----------
SELECT idcurso,max(nota) as notamax from nota group by idcurso

-------Listado de notas máximas por curso indicando a que alumno corresponde ------
select m.idcurso, paterno,materno,nombre,notamax from (SELECT idcurso,max(nota) as notamax from nota group by idcurso) m, nota n, alumno a where m.idcurso=n.idcurso and m.notamax=n.nota and n.idalumno=a.idalumno