Practica Nº5

create table distrito(
dis_cod char(2) primary key,
dis_des varchar(20));

insert into distrito values('00','por averiguar');
insert into distrito values('01','lima');
insert into distrito values('02','ancon');
insert into distrito values('03','ate');
insert into distrito values('04','breña');
insert into distrito values('05','comas');
insert into distrito values('06','los olivos');

create table alumno(
alu_cod char(8) primary key,
alu_pat varchar(20),
alu_mat varchar(20),
alu_nom varchar(20),
alu_dir varchar(30),
dis_cod char(2) references distrito,
alu_sex char(1),
alu_mail varchar(20),
fec_nac datetime);

insert into alumno values('sogac001','soto','garcia','cesar','jr.ica','01','m','car@hotmail.com','19780512');
insert into alumno values('rilom001','rivera','roca','maria','av. grau 123','01','f','ril@hotmail.com','19820516');
insert into alumno values('soquk001','sotelo','quispe','karina','jr. pando','03','f','car@yahoo.es','19780515');
insert into alumno values('buqul001','bueno','quino','luis','jr.tumbes 234','04','m','lbueno@mixmail.com','19780316');
insert into alumno values('riocm001','rivas','rocha','marcial','av. laureles 432','05','m','mrivas@hotmail.com','19830612');
insert into alumno values('mequk001','menacho','quino','karina','jr. pando','03','f','car@hotmail.com','19790118');insert into alumno values('cacac001','casas','casas','ana','av. a. ugarte','06','f','acasas@gmail.com','19750816');
insert into alumno values('galom001','garcia','sosa','marta','av. luces 231','03','f','ksoto@yahoo.com','19820516');
insert into alumno values('sucoc001','suca','contreras','cesar','jr. pando','03','f','car@yahoo.es','19720925');
insert into alumno values('cacoc001','castro','soto','cesar','jr.ica','01','m','car@hotmail.com','19760612');
insert into alumno values('rilom002','rivera','loayza','marta','av. grau 123','01','f','ril@hotmail.com','19820516');
insert into alumno values('soquk002','soto','quispe','karina','jr. pando','03','f','car@hotmail.com','19780116');

create table curso(
cur_cod char(5) primary key,
cur_des 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');

create table matricula(
mat_cod char(9) primary key,
alu_cod char(8) references alumno,
mat_fec datetime,
grado char(2),
año_lec char(4));

insert into matricula values('200500001','sogac001','20040712','1p','2004');
insert into matricula values('200500002','rilom001','20040715','1p','2004');
insert into matricula values('200500003','soquk001','20040816','1p','2004');
insert into matricula values('200500004','buqul001','20040810','2p','2004');
insert into matricula values('200500005','riocm001','20040812','2p','2004');
insert into matricula values('200500006','mequk001','20040815','3p','2004');
insert into matricula values('200500007','cacac001','20040816','4p','2004');
insert into matricula values('200500008','galom001','20040816','4p','2004');
insert into matricula values('200500009','sucoc001','20040817','4p','2004');
insert into matricula values('200500010','cacoc001','20040815','4p','2004');
insert into matricula values('200500011','rilom002','20040815','5p','2004');
insert into matricula values('200500012','soquk002','20040816','5p','2004');
insert into matricula values('200500013','sogac001','20050212','2p','2005');
insert into matricula values('200500014','rilom001','20050315','2p','2005');
insert into matricula values('200500015','soquk001','20050416','2p','2005');
insert into matricula values('200500016','buqul001','20050310','3p','2005');
insert into matricula values('200500017','riocm001','20050412','3p','2005');
insert into matricula values('200500018','mequk001','20050315','4p','2005');
insert into matricula values('200500019','cacac001','20050416','5p','2005');
insert into matricula values('200500020','galom001','20050516','5p','2005');
insert into matricula values('200500021','sucoc001','20050617','5p','2005');
insert into matricula values('200500022','cacoc001','20050415','5p','2005');
insert into matricula values('200500023','rilom002','20050315','6p','2005');
insert into matricula values('200500024','soquk002','20050416','6p','2004');

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

insert into nota values('200500001','ma100','1',20);
insert into nota values('200500002','ma100','1',16);
insert into nota values('200500001','ma100','2',17);
insert into nota values('200500002','ma100','2',16);
insert into nota values('200500001','ma100','3',18);
insert into nota values('200500002','ma100','3',15);
insert into nota values('200500001','dg100','1',16);
insert into nota values('200500003','dg100','1',18);
insert into nota values('200500001','dg100','2',15);
insert into nota values('200500002','dg100','2',16);
-- 1.- indicar la lista de alumnos cuyo correo este en yahoo
-- 2.- alumnos cuyo apellido paterno y materno inicie con r
-- 3.- alumnos cuyo apellido paterno o materno inicia con s
-- 4.- indicar las cantidades de hombres y mujeres.
-- 5.- indicar las cantidades de alumno por distrito.
-- 6.- numero de alumnos por grado y año lectivo
-- 7.- indicar que alumnos estan desaprobados
-- 8.- indicar los alumno cuya nota esta entre 17 y 20