Practica Nº7

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');

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

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 date);

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');

-- 1.- Listado de alumnos que no viven en 'Lima'
-- 2.- Listado de Alumnos varones que viven en 'Ate'
-- 3.- Listado de alumnas mujeres que nunca se han matriculado
-- 4.- Listado de todos los Alumnos que nunca se han matriculado
-- 5.- Listado de Distrito donde no hay alumnos
-- 6.- Listado de Distritos donde no hubo ningun matriculado
-- 7.- Listado de Inicios donde no hay matriculados
-- 8.- Listado de Matriculados cuyos alumnos no viven en 'Lima'