- Listado de alumnos cuyo correo no esta en yahoo
- Listado de alumnos que nunca se han matriculado
- Listado de alumnos que no tienen nota
- Listado de alumnos matriculados Hoy dia
- Listado de alumnos matriculados el dia de ayer
- Listado de alumnos con dos columnas: En la primera indicar el nombre completo, en la segunda el sexo (si es F indicar Mujer, si es M indicar Varon)
- Promedio de notas por semestre y curso.
- La cantidad alumnos por sexo de cada semestre (El sexo como nombre de columna)
- La cantidad de alumnos por semestre y en cada ciclo indicar cuantos varones y mujeres hay (como nombre de columna)
- Cantidad de alumnos matriculados por semestre en cada mes (Los meses como nombre de columna)
idalumno char(4) primary key,
paterno varchar(30),
materno varchar(30),
nombre varchar(30),
sexo char(2),
fec_nac date,
correo varchar(30));
insert into alumno values('0001','RIVERA','SOTO','CARLOS','M','19890512','crivera@hotmail.com');
insert into alumno values('0002','RIVAS','SOTO','LUIS','M','19870515','lrivas@yahoo.com');
insert into alumno values('0003','CARDENAS','SOTO','MARTA','F','19841216','mcardenas@gmail.com');
insert into alumno values('0004','LOPEZ','SOTO','AUGUSTO','M','19900819','alopez@yahoo.es');
insert into alumno values('0005','CASTRO','GARCIA','JOSE','M','19880613','jcastro@hotmail.com');
insert into alumno values('0006','GARCIA','QUINO','ROBERTO','M','19890113','rgarcia@yahoo.es');
insert into alumno values('0007','SOTO','QUISPE','CESAR','M','19910913','csoto@yahoo.com');
insert into alumno values('0008','SOTELO','CARRILLO','MARIA','F','19890505','msotelo@hotmail.com');
insert into alumno values('0009','RIVERA','TOLEDO','ANA','F','19830608','arivera@gmail.com');
insert into alumno values('0010','RIVERA','CARPIO','KARINA','M','19810613','krivera@gmail.com');
insert into alumno values('0011','CONTRERAS','SOTELO','JUAN','M','19810506','jcontreras@gmail.com');
insert into alumno values('0012','CASTRO','ROJAS','Marcelina','F','19800714','mcastro@gmail.com');
create table matricula(
idmatricula char(5),
idalumno char(4) references alumno,
semestre char(7),
fecha date, ciclo char(2) );
insert into matricula values('05001','0001','2004-01','20040101','01');
insert into matricula values('05002','0002','2004-01','20040201','01');
insert into matricula values('05003','0003','2004-01','20040302','01');
insert into matricula values('05004','0004','2004-01','20040303','02');
insert into matricula values('05005','0005','2004-01','20040401','02');
insert into matricula values('05006','0006','2004-01','20040404','02');
insert into matricula values('05007','0007','2004-01','20040405','03');
insert into matricula values('05008','0008','2004-01','20040302','03');
insert into matricula values('05009','0009','2004-01','20040403','03');
insert into matricula values('05010','0010','2004-01','20040101','03');
insert into matricula values('05011','0001','2004-02','20040801','02');
insert into matricula values('05012','0002','2004-02','20040801','02');
insert into matricula values('05013','0003','2004-02','20040702','02');
insert into matricula values('05014','0004','2004-02','20040806','03');
insert into matricula values('05015','0005','2004-02','20040805','03');
insert into matricula values('05016','0006','2004-02','20040703','03');
insert into matricula values('05017','0007','2004-02','20040704','04');
insert into matricula values('05018','0008','2004-02','20040802','04');
insert into matricula values('05019','0009','2004-02','20040806','04');
insert into matricula values('05020','0010','2004-02','20040701','04');
insert into matricula values('05021','0001','2005-01','20050802','03');
insert into matricula values('05022','0002','2005-01','20050804','03');
insert into matricula values('05023','0003','2005-01','20050706','03');
insert into matricula values('05024','0004','2005-01','20050807','04');
insert into matricula values('05025','0005','2005-01','20050809','04');
insert into matricula values('05026','0006','2005-01','20050713','04');
insert into matricula values('05027','0007','2005-01','20050714','05');
insert into matricula values('05028','0008','2005-01','20050812','05');
insert into matricula values('05029','0009','2005-01','20050816','05');
insert into matricula values('05030','0010','2005-01','20050711','05');
insert into matricula values('05031','0001','2005-02','20050802','04');
insert into matricula values('05032','0002','2005-02','20050804','04');
insert into matricula values('05033','0003','2005-02','20050706','04');
insert into matricula values('05034','0004','2005-02','20050807','05');
insert into matricula values('05035','0005','2005-02','20050809','05');
insert into matricula values('05036','0006','2005-02','20050713','05');
insert into matricula values('05037','0007','2005-02','20050714','06');
insert into matricula values('05038','0008','2005-02','20050812','06');
insert into matricula values('05039','0009','2005-02','20050816','06');
insert into matricula values('05040','0010','2005-02','20050711','06');
insert into matricula values('05041','0001','2006-01','20060102','05');
insert into matricula values('05042','0002','2006-01','20060205','05');
insert into matricula values('05043','0003','2006-01','20060306','05');
insert into matricula values('05044','0004','2006-01','20060206','06');
insert into matricula values('05045','0005','2006-01','20060203','06');
insert into matricula values('05046','0006','2006-01','20060314','06');
insert into matricula values('05047','0007','2006-01','20060215','07');
insert into matricula values('05048','0008','2006-01','20060216','07');
insert into matricula values('05049','0009','2006-01','20060316','07');
insert into matricula values('05050','0010','2006-01','20060412','07');
insert into matricula values('05051','0001','2006-02','20060801','06');
insert into matricula values('05052','0002','2006-02','20060705','06');
insert into matricula values('05053','0003','2006-02','20060806','06');
insert into matricula values('05054','0004','2006-02','20060705','07');
insert into matricula values('05055','0005','2006-02','20060704','07');
insert into matricula values('05056','0006','2006-02','20060815','07');
insert into matricula values('05057','0007','2006-02','20060816','08');
insert into matricula values('05058','0008','2006-02','20060815','08');
insert into matricula values('05059','0009','2006-02','20060714','08');
insert into matricula values('05060','0010','2006-02','20060813','08');
insert into matricula values('05061','0001','2007-02','20070801','07');
insert into matricula values('05062','0002','2007-02','20070705','07');
insert into matricula values('05063','0003','2007-02','20070806','07');
insert into matricula values('05064','0004','2007-02','20070705','08');
insert into matricula values('05065','0005','2007-02','20070704','08');
insert into matricula values('05066','0006','2007-02','20071212','08');
insert into matricula values('05067','0007','2007-02','20071213','09');
insert into matricula values('05068','0008','2007-02','20071213','09');
insert into matricula values('05069','0009','2007-02','20071212','09');
insert into matricula values('05070','0010','2007-02','20071213','09');
create table curso(
idcurso char(2) primary key,
curso varchar(30));
insert into curso values('01','MATEMATICA');
insert into curso values('02','FISICA');
insert into curso values('03','QUIMEICA');
insert into curso values('04','BIOLOGIA');
insert into curso values('05','PSICOLOGIA');
insert into curso values('06','FILOSOFIA');
insert into curso values('07','HISTORIA DEL PERU');
insert into curso values('08','LITERATURA');
insert into curso values('09','HISTORIA UNIVERSAL');
insert into curso values('10','LENGUAJE');
create table nota(
idmatricula char(5),
idcurso char(2),
nota numeric,
unidad char(1));
insert into nota values('05021','01',12,'1');
insert into nota values('05021','02',13,'1');
insert into nota values('05021','03',08,'1');
insert into nota values('05021','01',12,'2');
insert into nota values('05021','02',13,'2');
insert into nota values('05021','03',08,'2');
insert into nota values('05022','01',11,'1');
insert into nota values('05022','02',10,'1');
insert into nota values('05022','03',08,'1');
insert into nota values('05022','01',15,'2');
insert into nota values('05022','02',11,'2');
insert into nota values('05022','03',14,'2');
insert into nota values('05023','01',19,'1');
insert into nota values('05023','02',10,'1');
insert into nota values('05023','03',11,'1');
insert into nota values('05023','01',16,'2');
insert into nota values('05023','02',12,'2');
insert into nota values('05023','03',09,'2');
insert into nota values('05024','01',08,'1');
insert into nota values('05024','02',10,'1');
insert into nota values('05024','03',17,'1');
insert into nota values('05024','01',18,'2');
insert into nota values('05024','02',12,'2');
insert into nota values('05024','03',14,'2');
insert into nota values('05031','04',10,'1');
insert into nota values('05031','05',11,'1');
insert into nota values('05031','06',13,'1');
insert into nota values('05031','04',15,'2');
insert into nota values('05031','05',08,'2');
insert into nota values('05031','06',19,'2');
insert into nota values('05032','04',12,'1');
insert into nota values('05032','05',11,'1');
insert into nota values('05032','06',12,'1');
insert into nota values('05032','04',14,'2');
insert into nota values('05032','05',13,'2');
insert into nota values('05032','06',09,'2');
insert into nota values('05033','04',11,'1');
insert into nota values('05033','05',14,'1');
insert into nota values('05033','06',16,'1');
insert into nota values('05033','04',18,'2');
insert into nota values('05033','05',05,'2');
insert into nota values('05033','06',06,'2');
insert into nota values('05034','04',12,'1');
insert into nota values('05034','05',11,'1');
insert into nota values('05034','06',15,'1');
insert into nota values('05034','04',13,'2');
insert into nota values('05034','05',17,'2');
insert into nota values('05034','06',09,'2');
insert into nota values('05035','04',16,'1');
insert into nota values('05035','05',13,'1');
insert into nota values('05035','06',14,'1');
insert into nota values('05035','04',15,'2');
insert into nota values('05035','05',16,'2');
insert into nota values('05035','06',12,'2');
insert into nota values('05036','04',11,'1');
insert into nota values('05036','05',08,'1');
insert into nota values('05036','06',04,'1');
insert into nota values('05036','04',06,'2');
insert into nota values('05036','05',07,'2');
insert into nota values('05036','06',12,'2');
insert into nota values('05037','04',14,'1');
insert into nota values('05037','05',15,'1');
insert into nota values('05037','06',16,'1');
insert into nota values('05037','04',08,'2');
insert into nota values('05037','05',09,'2');
insert into nota values('05037','06',10,'2');
insert into nota values('05038','04',16,'1');
insert into nota values('05038','05',17,'1');
insert into nota values('05038','06',08,'1');
insert into nota values('05038','04',09,'2');
insert into nota values('05038','05',10,'2');
insert into nota values('05038','06',11,'2');
insert into nota values('05039','04',11,'1');
insert into nota values('05039','05',10,'1');
insert into nota values('05039','06',10,'1');
insert into nota values('05039','04',09,'2');
insert into nota values('05039','05',08,'2');
insert into nota values('05039','06',10,'2');
insert into nota values('05040','04',10,'1');
insert into nota values('05040','05',11,'1');
insert into nota values('05040','06',15,'1');
insert into nota values('05040','04',05,'2');
insert into nota values('05040','05',08,'2');
insert into nota values('05040','06',09,'2');
insert into nota values('05051','07',11,'1');
insert into nota values('05051','08',12,'1');
insert into nota values('05051','09',11,'1');
insert into nota values('05051','07',11,'2');
insert into nota values('05051','08',13,'2');
insert into nota values('05051','09',14,'2');
insert into nota values('05052','07',15,'1');
insert into nota values('05052','08',15,'1');
insert into nota values('05052','09',15,'1');
insert into nota values('05052','07',15,'2');
insert into nota values('05052','08',15,'2');
insert into nota values('05052','09',15,'2');
insert into nota values('05053','07',13,'1');
insert into nota values('05053','08',11,'1');
insert into nota values('05053','09',15,'1');
insert into nota values('05053','07',16,'2');
insert into nota values('05053','08',10,'2');
insert into nota values('05053','09',11,'2');
insert into nota values('05054','07',11,'1');
insert into nota values('05054','08',12,'1');
insert into nota values('05054','09',12,'1');
insert into nota values('05054','07',13,'2');
insert into nota values('05054','08',13,'2');
insert into nota values('05054','09',06,'2');
insert into nota values('05055','07',11,'1');
insert into nota values('05055','08',15,'1');
insert into nota values('05055','09',14,'1');
insert into nota values('05055','07',13,'2');
insert into nota values('05055','08',12,'2');
insert into nota values('05055','09',10,'2');
insert into nota values('05056','07',08,'1');
insert into nota values('05056','08',10,'1');
insert into nota values('05056','09',11,'1');
insert into nota values('05056','07',13,'2');
insert into nota values('05056','08',10,'2');
insert into nota values('05056','09',09,'2');
insert into nota values('05057','07',15,'1');
insert into nota values('05057','08',11,'1');
insert into nota values('05057','09',12,'1');
insert into nota values('05057','07',13,'2');
insert into nota values('05057','08',10,'2');
insert into nota values('05057','09',14,'2');
insert into nota values('05058','07',19,'1');
insert into nota values('05058','07',15,'1');
insert into nota values('05058','07',16,'1');
insert into nota values('05058','07',13,'2');
insert into nota values('05058','07',14,'2');
insert into nota values('05058','07',10,'2');
insert into nota values('05059','07',15,'1');
insert into nota values('05059','08',14,'1');
insert into nota values('05059','09',15,'1');
insert into nota values('05059','07',11,'2');
insert into nota values('05059','08',12,'2');
insert into nota values('05059','09',13,'2');
insert into nota values('05060','07',12,'1');
insert into nota values('05060','08',13,'1');
insert into nota values('05060','09',14,'1');
insert into nota values('05060','07',15,'2');
insert into nota values('05060','08',16,'2');
insert into nota values('05060','09',17,'2');
No hay comentarios:
Publicar un comentario