Practica Nº8

create table Distrito(
idDistrito char(2) primary key,
distrito varchar(20));

insert into distrito values('01','Lima');
insert into distrito values('02','Ancon');
insert into distrito values('03','Ate');
insert into distrito values('04','Barranco');
insert into distrito values('05','Breña');
insert into distrito values('06','Carabayllo');
insert into distrito values('07','Comas');
insert into distrito values('08','Chaclacayo');
insert into distrito values('09','Chorrillo');
insert into distrito values('10','El Agustino');

create table sexo(
idSexo char(1) primary key,
Sexo varchar(20));

insert into sexo values('M','Masculino');
insert into sexo values('F','Femenino');

create table Alumno(
idAlumno char(5) primary key,
paterno varchar(20),
materno varchar(20),
nombre varchar(20),
idDistrito char(2) references Distrito,
idSexo Char(1) references sexo);

insert into Alumno values('00001','Soto','Quispe','Luis Agusto','02','M');
insert into Alumno values('00002','Buleje','Cuba','Cesar','03','M');
insert into Alumno values('00003','Quino','Rios','Cesar Luis','08','M');
insert into Alumno values('00004','Perez','Menacho','Luis Alberto','08','M');
insert into Alumno values('00005','Mallma','Quintana','Karina','03','F');
insert into Alumno values('00006','Villafuerte','Supo','Juan','01','M');
insert into Alumno values('00007','Guzman','Arroyo','Jose Luis','03','M');
insert into Alumno values('00008','Rivas','Lujan','Carlos','06','M');
insert into Alumno values('00009','Castro','Lima','Maria','08','F');
insert into Alumno values('00010','Sotelo','Baca','Ana','08','F');
insert into Alumno values('00011','Soto','Lopez','Adrian','05','M');
insert into Alumno values('00012','Supo','Castro','Karen','07','F');
insert into Alumno values('00013','Garcia','Carrillo','Juan','05','M');

create table Inicio(
idInicio char(5) primary key,
ciclo char(2),
semestre char(7),
fec_inicio datetime);

insert into Inicio values('00001','01','2003-01','20030305');
insert into Inicio values('00002','02','2003-02','20030805');
insert into Inicio values('00003','03','2004-01','20040305');
insert into Inicio values('00004','04','2004-02','20040805');
insert into Inicio values('00005','05','2005-01','20050305');
insert into Inicio values('00006','06','2005-02','20050805');
insert into Inicio values('00007','07','2006-01','20060305');
insert into Inicio values('00008','08','2006-02','20060805');

create table matricula(
idmatricula char(6) primary key,
idAlumno char(5) references alumno,
idInicio char(5) references Inicio,
fecha datetime);

insert into matricula values('030001','00001','00001','20030112');
insert into matricula values('030002','00002','00001','20030112');
insert into matricula values('030003','00003','00001','20030112');
insert into matricula values('030004','00004','00001','20030113');
insert into matricula values('030005','00005','00001','20030113');
insert into matricula values('030006','00006','00001','20030114');
insert into matricula values('030007','00007','00001','20030115');
insert into matricula values('030008','00008','00001','20030116');
insert into matricula values('030009','00009','00001','20030118');
insert into matricula values('030010','00010','00001','20030122');
insert into matricula values('030011','00001','00002','20030713');
insert into matricula values('030012','00002','00002','20030713');
insert into matricula values('030013','00003','00002','20030714');
insert into matricula values('030014','00004','00002','20030814');
insert into matricula values('030015','00005','00002','20030814');
insert into matricula values('030016','00006','00002','20030815');
insert into matricula values('030017','00007','00002','20030816');
insert into matricula values('030018','00008','00002','20030817');
insert into matricula values('030019','00009','00002','20030918');
insert into matricula values('030020','00010','00002','20030924');
insert into matricula values('030021','00001','00003','20040213');
insert into matricula values('030022','00002','00003','20040213');
insert into matricula values('030023','00003','00003','20040214');
insert into matricula values('030024','00004','00003','20040214');
insert into matricula values('030026','00006','00003','20040215');
insert into matricula values('030027','00007','00003','20040216');
insert into matricula values('030028','00008','00003','20040217');
insert into matricula values('030029','00009','00003','20040308');
insert into matricula values('030030','00010','00003','20040309');
insert into matricula values('030031','00001','00004','20040613');
insert into matricula values('030032','00002','00004','20040613');
insert into matricula values('030033','00003','00004','20040614');
insert into matricula values('030034','00004','00004','20040714');
insert into matricula values('030035','00005','00004','20040714');
insert into matricula values('030036','00006','00004','20040715');
insert into matricula values('030037','00007','00004','20040811');
insert into matricula values('030038','00008','00004','20040813');
insert into matricula values('030039','00009','00004','20040805');
insert into matricula values('030040','00010','00004','20040817');
insert into matricula values('030041','00001','00005','20050211');
insert into matricula values('030042','00002','00005','20050211');
insert into matricula values('030043','00003','00005','20050211');
insert into matricula values('030044','00004','00005','20050212');
insert into matricula values('030045','00005','00005','20050213');
insert into matricula values('030046','00006','00005','20050213');
insert into matricula values('030047','00007','00005','20050214');
insert into matricula values('030048','00008','00005','20050214');
insert into matricula values('030049','00009','00005','20050314');
insert into matricula values('030050','00010','00005','20050315');
insert into matricula values('030051','00001','00006','20050711');
insert into matricula values('030052','00002','00006','20050711');
insert into matricula values('030055','00005','00006','20050803');
insert into matricula values('030056','00006','00006','20050813');
insert into matricula values('030057','00007','00006','20050814');
insert into matricula values('030058','00008','00006','20050814');
insert into matricula values('030059','00009','00006','20050814');
insert into matricula values('030060','00010','00006','20050815');
insert into matricula values('030061','00001','00007','20060212');
insert into matricula values('030062','00002','00007','20060212');
insert into matricula values('030063','00003','00007','20060213');
insert into matricula values('030064','00004','00007','20060213');
insert into matricula values('030065','00005','00007','20060213');
insert into matricula values('030066','00006','00007','20060214');
insert into matricula values('030067','00007','00007','20060215');
insert into matricula values('030068','00008','00007','20060216');
insert into matricula values('030069','00009','00007','20060316');
insert into matricula values('030070','00010','00007','20060316');
insert into matricula values('030071','00001','00008','20060612');
insert into matricula values('030072','00002','00008','20060612');
insert into matricula values('030073','00003','00008','20060613');
insert into matricula values('030074','00004','00008','20060713');
insert into matricula values('030075','00005','00008','20060713');
insert into matricula values('030076','00006','00008','20060714');
insert into matricula values('030077','00007','00008','20060715');
insert into matricula values('030079','00009','00008','20060813');
insert into matricula values('030080','00010','00008','20060815');

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

insert into curso values('MA100','MATEMÁTICAS-ALGORITMOS');
insert into curso values('DG100','DISEÑO GRÁFICO');
insert into curso values('OF100','DIGITACION');
insert into curso values('MA101','ESTRUCTURA DE DATOS');
insert into curso values('PG101','PROGRAMACION I');
insert into curso values('BD100','BASE DE DATOS I');
insert into curso values('BD101','BASE DE DATOS II');

create table nota(
idmatricula char(9) references matricula,
idcurso char(5) references curso,
periodo char(1),
nota numeric);

insert into nota values('030001','MA100','1',20);
insert into nota values('030002','MA100','1',16);
insert into nota values('030001','MA100','2',17);
insert into nota values('030002','MA100','2',16);
insert into nota values('030001','MA100','3',18);
insert into nota values('030002','MA100','3',15);
insert into nota values('030001','DG100','1',16);
insert into nota values('030002','DG100','1',18);
insert into nota values('030001','DG100','2',15);
insert into nota values('030002','DG100','2',16);
insert into nota values('030001','MA101','1',08);
insert into nota values('030002','MA101','1',06);
insert into nota values('030001','MA101','2',16);
insert into nota values('030002','MA101','2',12);
insert into nota values('030001','MA101','3',13);
insert into nota values('030002','MA101','3',14);
insert into nota values('030001','PG101','1',09);
insert into nota values('030002','PG101','1',10);
insert into nota values('030001','PG101','2',11);
insert into nota values('030002','PG101','2',13);
insert into nota values('030003','MA100','1',12);
insert into nota values('030004','MA100','1',12);
insert into nota values('030003','MA100','2',13);
insert into nota values('030004','MA100','2',14);
insert into nota values('030003','MA100','3',08);
insert into nota values('030004','MA100','3',05);
insert into nota values('030003','DG100','1',12);
insert into nota values('030004','DG100','1',11);
insert into nota values('030003','DG100','2',14);
insert into nota values('030004','DG100','2',14);
insert into nota values('030003','MA101','1',18);
insert into nota values('030004','MA101','1',09);
insert into nota values('030003','MA101','2',12);
insert into nota values('030004','MA101','2',11);
insert into nota values('030003','MA101','3',13);
insert into nota values('030004','MA101','3',15);
insert into nota values('030003','PG101','1',19);
insert into nota values('030004','PG101','1',20);
insert into nota values('030003','PG101','2',13);
insert into nota values('030004','PG101','2',14);

--obs. Considera los datos de los alumnos paterno, materno y nombres
--1.- Listado de alumnas mujeres que viven en 'Chaclacayo'
--2.- Cantidad de alumnos por sexo
--3.- Cantidad de alumnos por distrito
--4.- Cantidad de alumnos matriculados por sexo
--5.- Cantidad de alumnos matriculados por distrito
--6.- Listado de alumnos que al menos uno de sus nombres sea 'Luis'
--7.- Listado de alumnos matriculados en el semestre 2005-01
--8.- Cantidad de alumnos matriculados por semestre
--9.- Cantidad de alumnos desaprobados por ciclo y semestre
--10.-Listado de alumnos con promedio de notas por curso
--11.-Alumnos que nunca se han matriculado
--12.-Alumnos que no tienen ninguna nota
--13.-En que distritos no hay alumnos
--14.-En que Inicio no hay alumnos
--15.-Alumnas mujeres que no tienen nota en ningun curso
--16.-En que cursos no hay nota
--17.-Listado de alumnos que viven en Breña y nunca se han matriculado
--18.-Alumnos que se han matriculado el 12 de enero del 2003
--19.-Alumnos que no se han matriculado en el semestre '2005-02'
--20.-Alumnas mujeres que se han matriculado en el semestre
-- '2005-02', '2005-01' y '2004-02'