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 )