EXAMEN FINAL

Correr el script proporcionado por el profesor y realizar las siguientes consultas:

1. Listado de alumnos matriculados
2. Listado de distritos cuya descripcion inicia con A
3. Alumnos cuyo correo esta mal escrito
4. Alumnos cuyo correo sea de yahoo.com
5. Alumnos que viven en Ate
6. Alumnos que viven en Comas cuyo correo sea de hotmail y sea mujer
7. Notas de todos los alumnos indicando el curso
8. Notas de todos los alumnos del curso de matemáticas
9. Listado de alumnos aprobados en Fisica
10. Listado de alumnos varones

CREATE TABLE distrito(
iddistrito CHAR(2) PRIMARY KEY,
distrito VARCHAR(30));

INSERT INTO distrito values('01','Lima');
INSERT INTO distrito values('02','Ate');
INSERT INTO distrito values('03','Breña');
INSERT INTO distrito values('04','Comas');
INSERT INTO distrito values('05','Los Olivos');
INSERT INTO distrito values('06','Surquillo');
INSERT INTO distrito values('07','Miraflores');
INSERT INTO distrito values('08','Jesus Maria');
INSERT INTO distrito values('09','Surco');
INSERT INTO distrito values('10','Lince');
INSERT INTO distrito values('11','San Borja');
INSERT INTO distrito values('12','Ancon');


create table alumno(
idalumno char(4) primary key,
paterno varchar(30),
materno varchar(30),
nombre varchar(30),
sexo char(2),
fec_nac datetime,
correo varchar(30),
iddistrito char(2) references distrito);
insert into alumno values('0001','RIVERA','SOTO','CARLOS','M','19890512','crivera@hotmail.com','02');
insert into alumno values('0002','RIVAS','SOTO','LUIS','M','19870515','lrivas@yahoo.com','01');
insert into alumno values('0003','CARDENAS','SOTO','MARTA','F','19841216','mcardenasgmail.com','02');
insert into alumno values('0004','LOPEZ','SOTO','AUGUSTO','M','19900819','alopez@yahoo.es','03');
insert into alumno values('0005','CASTRO','GARCIA','JOSE','M','19880613','jcastro@hotmail.com','02');
insert into alumno values('0006','GARCIA','QUINO','ROBERTO','M','19890113','rgarcia@yahoo.es','04');
insert into alumno values('0007','SOTO','QUISPE','CESAR','M','19910913','csoto@yahoo.com','03');
insert into alumno values('0008','SOTELO','CARRILLO','MARIA','F','19890505','msotelohotmail.com','05');
insert into alumno values('0009','RIVERA','TOLEDO','ANA','F','19830608','arivera@gmail.com','02');
insert into alumno values('0010','RIVERA','CARPIO','KARINA','F','19810613','krivera@gmail.com','04');
insert into alumno values('0011','CONTRERAS','SOTELO','JUAN','M','19810506','jcontreras@gmail.com','02');
insert into alumno values('0012','CASTRO','ROJAS','Marcelina','F','19800714','mcastrogmail.com','03');
insert into alumno values('0013','CARRILLO','LOPEZ','Marcel','M','19850817','mcarrillo@gmail.com','08');
insert into alumno values('0014','CONTRERAS','CACERES','Juan','M','19880915','mjcontreras@gmail.com','06');
insert into alumno values('0015','CASTILLO','LUJAN','Mario','M','19860917','mcastillo@gmail.com','08');


create table matricula(
idmatricula char(5) primary key,
idalumno char(4) references alumno,
semestre char(7),
fecha datetime,
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) references matricula,
idcurso char(2) references curso,
nota numeric,
unidad char(1),
primary key(idmatricula,idcurso,unidad));

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','08',15,'1');
insert into nota values('05058','09',16,'1');
insert into nota values('05058','07',13,'2');
insert into nota values('05058','08',14,'2');
insert into nota values('05058','09',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');

Examen Parcial

Correr el script y contestar las preguntas

1.- Listado de alumnos matriculados que viven an Comas
2.- Listado de alumnos varones cuyo correo esta en yahoo
3.- Listado de alumnos aprobados ordenados alfabeticamente
4.- Listado de matriculados que viven en Ate
5.- Listados de alumnos con nota aprobatoria indicando el distrito donde viven
6.- Listado de alumnos indicando el nombre del curso
7.- Listado de alumnos cuya nota este entre 12 y 18
8.- Listado de alumnos matriculados en el semestre '2004-02' que viven an Ate
9.- Listado de alumnos matriculados en el segundo ciclo
10.-Listado de alumnos que no tienen nota

---------------------------------------------------------------
CREATE TABLE distrito(
iddistrito CHAR(2) PRIMARY KEY,
distrito VARCHAR(30));

INSERT INTO distrito values('01','Lima');
INSERT INTO distrito values('02','Ate');
INSERT INTO distrito values('03','Breña');
INSERT INTO distrito values('04','Comas');
INSERT INTO distrito values('05','Los Olivos');
INSERT INTO distrito values('06','Surquillo');
INSERT INTO distrito values('07','Miraflores');
INSERT INTO distrito values('08','Jesus Maria');

create table alumno(
idalumno char(4) primary key,
paterno varchar(30),
materno varchar(30),
nombre varchar(30),
sexo char(2),
fec_nac datetime,
correo varchar(30),
iddistrito char(2) references distrito);
insert into alumno values('0001','RIVERA','SOTO','CARLOS','M','19890512','crivera@hotmail.com','02');
insert into alumno values('0002','RIVAS','SOTO','LUIS','M','19870515','lrivas@yahoo.com','01');
insert into alumno values('0003','CARDENAS','SOTO','MARTA','F','19841216','mcardenas@gmail.com','02');
insert into alumno values('0004','LOPEZ','SOTO','AUGUSTO','M','19900819','alopez@yahoo.es','03');
insert into alumno values('0005','CASTRO','GARCIA','JOSE','M','19880613','jcastro@hotmail.com','02');
insert into alumno values('0006','GARCIA','QUINO','ROBERTO','M','19890113','rgarcia@yahoo.es','04');
insert into alumno values('0007','SOTO','QUISPE','CESAR','M','19910913','csoto@yahoo.com','03');
insert into alumno values('0008','SOTELO','CARRILLO','MARIA','F','19890505','msotelo@hotmail.com','05');
insert into alumno values('0009','RIVERA','TOLEDO','ANA','F','19830608','arivera@gmail.com','02');
insert into alumno values('0010','RIVERA','CARPIO','KARINA','M','19810613','krivera@gmail.com','04');
insert into alumno values('0011','CONTRERAS','SOTELO','JUAN','M','19810506','jcontreras@gmail.com','02');
insert into alumno values('0012','CASTRO','ROJAS','Marcelina','F','19800714','mcastro@gmail.com','03');

create table matricula(
idmatricula char(5) primary key,
idalumno char(4) references alumno,
semestre char(7),
fecha datetime,
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) references matricula,
idcurso char(2) references curso,
nota numeric,
unidad char(1),
primary key(idmatricula,idcurso,unidad));

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','08',15,'1');
insert into nota values('05058','09',16,'1');
insert into nota values('05058','07',13,'2');
insert into nota values('05058','08',14,'2');
insert into nota values('05058','09',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');
-------------------------------------------------------------------------
Ejemplos
1.- Listado de alumnos matriculadosSELECT distinct paterno,materno,nombre FROM alumno, matricula WHERE matricula.idalumno=alumno.idalumno
2.- Listado de alumnos que nunca se han matriculadoEn otras palabras listado de alumnos que estan en la tabla alumno pero que no estana en la tabla matriculaSELECT paterno,materno,nombre FROM alumno WHERE idalumno NOT IN (select idalumno from matricula)
3.-Listado de distritos donde no hay alumnos select * from distrito where iddistrito not in(select iddistrito from alumno )

Examen Parcial

CREATE TABLE SEXO(
idsexo char(1) primary key,
sexo varchar(20));

INSERT INTO sexo values('1','Varon');
INSERT INTO sexo values('2','Mujer');

CREATE TABLE distrito(
iddistrito CHAR(2) PRIMARY KEY,
distrito VARCHAR(30));

INSERT INTO distrito values('01','Lima');
INSERT INTO distrito values('02','Ate');
INSERT INTO distrito values('03','Breña');
INSERT INTO distrito values('04','Comas');
INSERT INTO distrito values('05','Los Olivos');
INSERT INTO distrito values('06','Surquillo');
INSERT INTO distrito values('07','Miraflores');
INSERT INTO distrito values('08','Jesus Maria');

create table alumno(
idalumno char(5) primary key,
paterno varchar(20) ,
materno varchar(20) ,
nombre varchar (20),
correo varchar(40),
telefono varchar(20),
fecha_n date,
deuda numeric,
iddistrito CHAR(2) references distrito,
idsexo char(1) references sexo
);

insert into alumno values('00001', 'vazquez', 'acevedo', 'lizeth','liz_kelin@yahoo.es', '5864253','19900812',1200,'03','2');
insert into alumno values('00002', 'Rodriguez', 'Cahuana', 'Beto','rafa_15@gmail.com', '5784623','19850614',750,'02','1');
insert into alumno values('00003', 'Quispe', 'Tarazona', 'fernando','luis_jl2@hotmail.com', '3876280','19700524',3500,'05','1');
insert into alumno values('00004', 'Alayo', 'Rojas', 'Alberto','luchoelin@yahoo.com', '3877755','19900829',2500,'03','1');
insert into alumno values('00005', 'Nuñez', 'Alvarado', 'Beatriz','tita@hotmail.com', '5858593','19801014',1200,'06','2');

EJEMPLOS DE CONSULTAS CON DOS O MAS TABLAS
1.- Listado de alumnos indicando el distrito donde viveSELECT paterno,materno,nombre, distrito FROM alumno, distrito WHERE alumno.iddistrito=distrito.iddistrito
SELECT * FROM alumno, distrito WHERE alumno.iddistrito=distrito.iddistrito
EXAMEN PARCIAL
1.- Listado de alumnos en cuyo apellido paterno exista la letra 'r'
2.- Listado de alumnos que deben mas de 1000 pero menos que 2000
3.- Listado de alumnos cuyo correo sea de yahoo, tenga una deuda de mas de 2500 y en su numero de telefono tenga el numero 8
4.- Listado de alumnos que viven en 'Surquillo'
5.- Listado de alumnos que viva en 'Breña' y tenga una deuda de mas de 2000 soles
6.- Mostrar el apellido paterno, materno, nombres y el nombre del distrito de aquellos alumnos en cuyo telefono exista los numero 2 o 3
7.- Listado de alumnos varones
8.- Mostrar los apellidos, nombres y la descripcion del sexo de todos los alumno varones y que vivan en 'Breña'
9.- Mostrar el apellido paterno, materno, nombres, el nombre del distrito y la descripcion del sexo
10.- Mostrar todos los campos de las tres tablas

Practica

--Correr el script y desarrollar las siguientes consultas
-- 1.-Listado de alumnos que viven en el distrito '03' y sean varones
-- 2.-Listado de alumnas mujeres que deben mas de 500 soles
-- 3.-Listado de alumnos varones cuyo promedio de deuda sea mas de 400
-- 4.-Listado de alumnos que viven en el distrito '04' y que deben mas de 500 soles
-- 5.-Listado de alumnos indicando los apellidos y nombres y el promedio de deuda
-- 6.-Listado de alumnos ordenados por deuda total
-- 7.-Listado de alumnos cuyo apellido paterno inicia con 'CA'
-- 8.-Listado de alumnos en cuyo apellido exista 'RR'
-- 9.-Listado de alumnos que viven en el distrito '02', '04' y '05'
--10.-Listado de alumnos cuya deuda1 este entre 200 y 500, deuda2 entre 100 y 1000, deuda3 entre 400 y 800



CREATE TABLE distrito(
iddistrito CHAR(2) PRIMARY KEY,
distrito VARCHAR(30));

INSERT INTO distrito values('01','Lima');
INSERT INTO distrito values('02','Ate');
INSERT INTO distrito values('03','Breña');
INSERT INTO distrito values('04','Comas');
INSERT INTO distrito values('05','Los Olivos');
INSERT INTO distrito values('06','Surquillo');
INSERT INTO distrito values('07','Miraflores');
INSERT INTO distrito values('08','Jesus Maria');

CREATE TABLE alumno(
idalumno char(5) PRIMARY KEY,
paterno varchar(30),
materno varchar(30),
nombre varchar(30),
sexo char(1),
fnac datetime,
deuda1 numeric,
deuda2 numeric,
deuda3 numeric,
iddistrito char(2) REFERENCES distrito
);
INSERT INTO alumno values('10001','SOTO','QUISPE','CESAR','M','20080315',200,800,600,'02');
INSERT INTO alumno values('10002','ROJAS','QUINO','DIANA','F','20080315',1500,200,650,'01');
INSERT INTO alumno values('10003','ALVA','ROJAS','ERIKA','F','20080315',250,300,620,'03');
INSERT INTO alumno values('10004','MEDINA','CASTILLO','KARINA','F','20080315',1250,800,600,'04');
INSERT INTO alumno values('10005','LOSA','CARRILLO','ROGELIO','M','20080315',20,80,620,'05');
INSERT INTO alumno values('10006','SOTELO','MEDINA','FELIPE','M','20080315',1200,800,320,'03');
INSERT INTO alumno values('10007','GARCIA','QUIÑONES','RICHARD','M','20080315',1200,820,210,'02');
INSERT INTO alumno values('10008','CASTRO','ROJAS','LUIS','M','20080315',1280,800,320,'01');
INSERT INTO alumno values('10009','LUCA','BUENO','PEDRO','M','20080315',220,500,240,'03');
INSERT INTO alumno values('10010','CARDENAS','GUERRA','LUIS','M','20080315',125,840,680,'02');
INSERT INTO alumno values('10011','CASTRO','LUCAS','MARIA','F','20080315',100,820,460,'01');
INSERT INTO alumno values('10012','BULEJE','CASTRO','ANA','F','20080315',204,320,360,'07');
INSERT INTO alumno values('10013','LOPEZ','MARTOS','JUAN','M','20080315',216,840,600,'06');

--EJEMPLOS
--1 Listado de alumnos que viven en el distrito '03'
SELECT * FROM alumno WHERE iddistrito='03'
--2 Listado de alumnas mujeres
SELECT * FROM alumno WHERE sexo='F'
--3 Listado de alumnos varones que viven en el distrito '02'
SELECT * FROM alumno WHERE sexo= 'M' and iddistrito='02'
--4 Listado de alumnos cuya deuda1 este entre 100 y 500
select * from alumno where deuda1>100 and deuda1<500
--5 Listado de alumnos mostrando los apellidos, nombres y la deuda total
select paterno,materno,nombre,deuda1+deuda2+deuda3 as total from alumno
--6 Listado de alumnos ordenados alfabeticamente por sexo
select * from alumno ORDER BY sexo,paterno,materno,nombre
--7 Listado de alumnos cuyo nombre inicia con 'L'
select * from alumno where nombre LIKE 'L%'
--8.-Listado de alumnos que viven en el distrito '01' y '02'
select * from alumno where iddistrito='01' or iddistrito='02'