miércoles, 3 de octubre de 2007

Examen Final Nº2

Correr el script y realizar las siguiente consultas

  • Listado de alumnos que viven en ‘ATE’ y cuyo apellido inicia con la letra A
  • Listado de alumnos desaprobados. Indicar los nombres de los alumnos y su nota correspondiente
  • Listado de alumnos cuyo apellido paterno, materno o nombre exista la letra ‘S’
  • Cantidad de alumnos por distrito
  • Cantidad de alumnos por sexo
  • Promedio de notas de cada alumno por ciclo
  • Listado de alumnos indicando los cursos que lleva y su nota correspondiente del semestre 2005-01
  • Cantidad de alumnos por distrito, sexo y curso
  • Número de alumnos cuyo promedio de notas esta entre 11 y 16
  • Puntaje total por alumno (puntaje =suma de todas sus notas)

Inicio Script
________________________________________

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 alumno(
idalumno char(4) primary key,
paterno varchar(30),
materno varchar(30),
nombre varchar(30),
sexo char(2),
fec_nac date,
idDistrito char(2)
);

insert into alumno values('0001','RIVERA','SOTO','CARLOS','M','19890512','02');
insert into alumno values('0002','ARIAS','SOTO','LUIS','M','19870515','03');
insert into alumno values('0003','CARDENAS','SOTO','MARTA','F','19841216','04');
insert into alumno values('0004','LOPEZ','SOTO','AUGUSTO','M','19900819','02');
insert into alumno values('0005','CASTRO','GARCIA','JOSE','M','19880613','06');
insert into alumno values('0006','GARCIA','QUINO','ROBERTO','M','19890113','02');
insert into alumno values('0007','SOTO','QUISPE','CESAR','M','19910913','03');
insert into alumno values('0008','SOTELO','CARRILLO','MARIA','F','19890505','02');
insert into alumno values('0009','RIVERA','TOLEDO','ANA','F','19830608','04');
insert into alumno values('0010','RIVERA','CARPIO','KARINA','M','19810613','06');


create table matricula(
idmatricula char(5),
idalumno char(4) references alumno,
semestre char(7),
fecha date
);
insert into matricula values('05001','0001','2004-01','20040101');
insert into matricula values('05002','0002','2004-01','20040201');
insert into matricula values('05003','0003','2004-01','20040302');
insert into matricula values('05004','0004','2004-01','20040303');
insert into matricula values('05005','0005','2004-01','20040401');
insert into matricula values('05006','0006','2004-01','20040404');
insert into matricula values('05007','0007','2004-01','20040405');
insert into matricula values('05008','0008','2004-01','20040302');
insert into matricula values('05009','0009','2004-01','20040403');
insert into matricula values('05010','0010','2004-01','20040101');

insert into matricula values('05011','0001','2004-02','20040801');
insert into matricula values('05012','0002','2004-02','20040801');
insert into matricula values('05013','0003','2004-02','20040702');
insert into matricula values('05014','0004','2004-02','20040806');
insert into matricula values('05015','0005','2004-02','20040805');
insert into matricula values('05016','0006','2004-02','20040703');
insert into matricula values('05017','0007','2004-02','20040704');
insert into matricula values('05018','0008','2004-02','20040802');
insert into matricula values('05019','0009','2004-02','20040806');
insert into matricula values('05020','0010','2004-02','20040701');

insert into matricula values('05021','0001','2005-01','20050802');
insert into matricula values('05022','0002','2005-01','20050804');
insert into matricula values('05023','0003','2005-01','20050706');
insert into matricula values('05024','0004','2005-01','20050807');
insert into matricula values('05025','0005','2005-01','20050809');
insert into matricula values('05026','0006','2005-01','20050713');
insert into matricula values('05027','0007','2005-01','20050714');
insert into matricula values('05028','0008','2005-01','20050812');
insert into matricula values('05029','0009','2005-01','20050816');
insert into matricula values('05030','0010','2005-01','20050711');

insert into matricula values('05031','0001','2005-02','20050802');
insert into matricula values('05032','0002','2005-02','20050804');
insert into matricula values('05033','0003','2005-02','20050706');
insert into matricula values('05034','0004','2005-02','20050807');
insert into matricula values('05035','0005','2005-02','20050809');
insert into matricula values('05036','0006','2005-02','20050713');
insert into matricula values('05037','0007','2005-02','20050714');
insert into matricula values('05038','0008','2005-02','20050812');
insert into matricula values('05039','0009','2005-02','20050816');
insert into matricula values('05040','0010','2005-02','20050711');

insert into matricula values('05041','0001','2006-01','20060102');
insert into matricula values('05042','0002','2006-01','20060205');
insert into matricula values('05043','0003','2006-01','20060306');
insert into matricula values('05044','0004','2006-01','20060206');
insert into matricula values('05045','0005','2006-01','20060203');
insert into matricula values('05046','0006','2006-01','20060314');
insert into matricula values('05047','0007','2006-01','20060215');
insert into matricula values('05048','0008','2006-01','20060216');
insert into matricula values('05049','0009','2006-01','20060316');
insert into matricula values('05050','0010','2006-01','20060412');

insert into matricula values('05051','0001','2006-02','20060801');
insert into matricula values('05052','0002','2006-02','20060705');
insert into matricula values('05053','0003','2006-02','20060806');
insert into matricula values('05054','0004','2006-02','20060705');
insert into matricula values('05055','0005','2006-02','20060704');
insert into matricula values('05056','0006','2006-02','20060815');
insert into matricula values('05057','0007','2006-02','20060816');
insert into matricula values('05058','0008','2006-02','20060815');
insert into matricula values('05059','0009','2006-02','20060714');
insert into matricula values('05060','0010','2006-02','20060813');

insert into matricula values('05061','0001','2007-02','20070801');
insert into matricula values('05062','0002','2007-02','20070705');
insert into matricula values('05063','0003','2007-02','20070806');
insert into matricula values('05064','0004','2007-02','20070705');
insert into matricula values('05065','0005','2007-02','20070704');
insert into matricula values('05066','0006','2007-02','20070815');
insert into matricula values('05067','0007','2007-02','20070816');
insert into matricula values('05068','0008','2007-02','20070815');
insert into matricula values('05069','0009','2007-02','20070714');
insert into matricula values('05070','0010','2007-02','20070813');

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

Fin Script
________________________________________
Publicado por en

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)