Practica Nº6

--Ejecutar el siguiente Script en MySQL
create table alumno(
idalumno char(4) primary key,
paterno varchar(30),
materno varchar(30),
nombre char(30),
sexo char(1));

insert into alumno values('0001','RIVAS','MAMANI','CESAR','M');
insert into alumno values('0002','ROJAS','MENACHO','CARLOS','M');
insert into alumno values('0003','QUISPE','MENACHO','LUIS','M');
insert into alumno values('0004','CARRILLO','MENACHO','MARIA','F');
insert into alumno values('0005','RIVERA','MENACHO','CESAR','M');
insert into alumno values('0006','SOTIL','MENACHO','KARINA','F');
insert into alumno values('0007','BULEJE','MAMANI','CESAR','M');
insert into alumno values('0008','QUISPE','MUNARES','GENARO','M');
insert into alumno values('0009','QUINTEROS','MANCO','LUIS','M');
insert into alumno values('0010','CASTRO','ZUÑIGA','MARTA','F');
insert into alumno values('0011','RIVELINO','MELQUIADES','JUAN','M');
insert into alumno values('0012','SOTO','MELENDEZ','CESAR','M');

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

insert into curso values('01','Matematicas');
insert into curso values('02','Fisica');
insert into curso values('03','Quimica');
insert into curso values('04','Historia del Perú');
insert into curso values('05','Historia Universal');
insert into curso values('06','Lenguaje');
insert into curso values('07','Literatura');
insert into curso values('08','Filosofia');

create table nota(
idalumno char(4),
idcurso char(2),
unidad numeric,
grado char(1),
nota numeric);

insert into nota values('0001','01','1','1',12);
insert into nota values('0002','01','1','1',14);
insert into nota values('0007','01','1','1',16);
insert into nota values('0008','01','1','1',10);
insert into nota values('0009','01','1','1',08);
insert into nota values('0010','01','1','1',20);
insert into nota values('0011','01','1','1',14);
insert into nota values('0012','01','1','1',13);
insert into nota values('0001','03','1','1',19);
insert into nota values('0002','03','1','1',20);
insert into nota values('0004','03','1','1',05);
insert into nota values('0005','03','1','1',07);
insert into nota values('0006','03','1','1',09);
insert into nota values('0010','03','1','1',16);
insert into nota values('0011','03','1','1',04);
insert into nota values('0012','03','1','1',10);
insert into nota values('0001','04','1','1',14);
insert into nota values('0002','04','1','1',16);
insert into nota values('0003','04','1','1',17);
insert into nota values('0004','04','1','1',18);
insert into nota values('0008','04','1','1',19);
insert into nota values('0009','04','1','1',12);
insert into nota values('0001','06','1','1',15);
insert into nota values('0002','06','1','1',16);
insert into nota values('0003','06','1','1',16);
insert into nota values('0004','06','1','1',17);
insert into nota values('0005','06','1','1',19);
insert into nota values('0008','06','1','1',07);
insert into nota values('0009','06','1','1',06);
insert into nota values('0012','06','1','1',14);
insert into nota values('0001','07','1','1',13);
insert into nota values('0002','07','1','1',15);
insert into nota values('0003','07','1','1',17);
insert into nota values('0004','07','1','1',11);
insert into nota values('0009','07','1','1',15);
insert into nota values('0011','07','1','1',16);
insert into nota values('0012','07','1','1',17);
insert into nota values('0001','09','1','1',16);
insert into nota values('0002','09','1','1',12);
insert into nota values('0003','09','1','1',11);
insert into nota values('0007','09','1','1',10);
insert into nota values('0008','09','1','1',10);
insert into nota values('0011','09','1','1',17);
insert into nota values('0012','09','1','1',16);
insert into nota values('0001','10','1','1',19);
insert into nota values('0002','10','1','1',20);
insert into nota values('0003','10','1','1',12);
insert into nota values('0004','10','1','1',13);
insert into nota values('0007','10','1','1',14);
insert into nota values('0008','10','1','1',15);
insert into nota values('0009','10','1','1',16);
insert into nota values('0012','10','1','1',17);
insert into nota values('0001','01','2','1',08);
insert into nota values('0002','01','2','1',07);
insert into nota values('0003','01','2','1',06);
insert into nota values('0004','01','2','1',11);
insert into nota values('0007','01','2','1',14);
insert into nota values('0008','01','2','1',01);
insert into nota values('0009','01','2','1',03);
insert into nota values('0010','01','2','1',09);
insert into nota values('0011','01','2','1',07);
insert into nota values('0012','01','2','1',15);
insert into nota values('0001','06','2','1',16);
insert into nota values('0002','06','2','1',13);
insert into nota values('0003','06','2','1',12);
insert into nota values('0004','06','2','1',14);
insert into nota values('0005','06','2','1',12);
insert into nota values('0006','06','2','1',13);
insert into nota values('0008','06','2','1',12);
insert into nota values('0009','06','2','1',14);
insert into nota values('0010','06','2','1',12);
insert into nota values('0011','06','2','1',13);
insert into nota values('0012','06','2','1',11);
insert into nota values('0001','06','2','1',13);
insert into nota values('0002','06','2','1',14);
insert into nota values('0003','06','2','1',16);
insert into nota values('0004','06','2','1',19);
insert into nota values('0005','06','2','1',12);
insert into nota values('0006','06','2','1',14);
insert into nota values('0007','06','2','1',16);
insert into nota values('0008','06','2','1',15);
insert into nota values('0009','06','2','1',14);
insert into nota values('0010','06','2','1',15);
insert into nota values('0011','06','2','1',16);
insert into nota values('0012','06','2','1',15);
insert into nota values('0002','07','2','1',12);
insert into nota values('0003','07','2','1',11);
insert into nota values('0004','07','2','1',14);
insert into nota values('0005','07','2','1',13);
insert into nota values('0006','07','2','1',16);
insert into nota values('0007','07','2','1',05);
insert into nota values('0008','07','2','1',14);
insert into nota values('0009','07','2','1',15);
insert into nota values('0010','07','2','1',14);
insert into nota values('0011','07','2','1',14);
insert into nota values('0012','07','2','1',11);
insert into nota values('0001','01','1','2',14);
insert into nota values('0002','01','1','2',15);
insert into nota values('0003','01','1','2',15);
insert into nota values('0004','01','1','2',16);
insert into nota values('0005','01','1','2',13);
insert into nota values('0006','01','1','2',11);
insert into nota values('0007','01','1','2',08);
insert into nota values('0008','01','1','2',14);
insert into nota values('0009','01','1','2',17);
insert into nota values('0010','01','1','2',14);
insert into nota values('0011','01','1','2',12);
insert into nota values('0012','01','1','2',14);
insert into nota values('0001','06','1','2',11);
insert into nota values('0002','06','1','2',12);
insert into nota values('0003','06','1','2',14);
insert into nota values('0004','06','1','2',13);
insert into nota values('0005','06','1','2',12);
insert into nota values('0006','06','1','2',14);
insert into nota values('0008','06','1','2',15);
insert into nota values('0009','06','1','2',14);
insert into nota values('0010','06','1','2',16);
insert into nota values('0011','06','1','2',17);
insert into nota values('0012','06','1','2',14);
insert into nota values('0001','09','1','3',11);
insert into nota values('0002','09','1','3',12);
insert into nota values('0003','09','1','3',14);
insert into nota values('0004','09','1','3',14);
insert into nota values('0005','09','1','3',15);
insert into nota values('0006','09','1','3',10);
insert into nota values('0007','09','1','3',13);
insert into nota values('0008','09','1','3',13);
insert into nota values('0009','09','1','3',13);
insert into nota values('0010','09','1','3',13);
insert into nota values('0011','09','1','3',11);
insert into nota values('0012','09','1','3',14);
insert into nota values('0001','01','1','3',11);
insert into nota values('0002','01','1','3',12);
insert into nota values('0003','01','1','3',12);
insert into nota values('0004','01','1','3',13);
insert into nota values('0005','01','1','3',12);
insert into nota values('0006','01','1','3',11);
insert into nota values('0007','01','1','3',11);
insert into nota values('0008','01','1','3',14);
insert into nota values('0009','01','1','3',13);
insert into nota values('0010','01','1','3',13);
insert into nota values('0011','01','1','3',11);
insert into nota values('0012','01','1','3',11);
insert into nota values('0001','01','1','4',12);
insert into nota values('0002','01','1','4',11);
insert into nota values('0003','01','1','4',11);
insert into nota values('0004','01','1','4',12);
insert into nota values('0005','01','1','4',12);
insert into nota values('0006','01','1','4',14);
insert into nota values('0007','01','1','4',14);
insert into nota values('0008','01','1','4',14);
insert into nota values('0009','01','1','4',13);
insert into nota values('0010','01','1','4',17);
insert into nota values('0011','01','1','4',12);
insert into nota values('0012','01','1','4',11);
insert into nota values('0001','05','3','4',11);
insert into nota values('0002','05','3','4',12);
insert into nota values('0003','05','3','4',12);
insert into nota values('0004','05','3','4',13);
insert into nota values('0005','05','3','4',14);
insert into nota values('0006','05','3','4',16);
insert into nota values('0007','05','3','4',17);
insert into nota values('0008','05','3','4',18);
insert into nota values('0009','05','3','4',19);
insert into nota values('0010','05','3','4',13);
insert into nota values('0011','05','3','4',11);
insert into nota values('0012','05','3','4',16);

-- Contestar las siguientes preguntas
--1.- El numero de alumnos desaprobados en la primera unidad por curso
--2.- El numero de alumnos aprobados por unidad y ciclo
--3.- El promedio de notas por curso de cada alumno en el grado 1
--4.- La nota máxima por curso
--5.- La nota máxima por unidad.
--6.- El promedio de notas por sexo
--7.- El promedio de notas por alumno indicar sólo los aprobados
--8.- El promedio de notas por unidad
--9.- La nota máxima por sexo.
--10..El numero de alumnos cuyo promedio de notas sea mayor que 15