Nueva Practica Nº2

--Los datos son informacion de las diferentes alturas de las que se suelta un cuerpo.

Create table datos(
iddato char(3) primary key,
altura float
);
insert into datos values('001',120);
insert into datos values('002',130);
insert into datos values('003',140);
insert into datos values('004',150);
insert into datos values('005',160);
insert into datos values('006',170);
insert into datos values('007',180);
insert into datos values('008',200);

-- Los datos son de caida libre(g=9.8m/s2) al soltar un cuerpo de alturas diferentes
--1.- Mostrar además de la altura el tiempo que demora en caer y la velocidad
--2.- Mostrar el tiempo de caida si la velocidad inicial es 120m/s

Nueva Practica Nº1

Correr el siguiente script y realizar las consultas indicadas:

create table triangulo (
idtriangulo char(5) primary key,
lado1 float,
lado2 float,
lado3 float
);
insert into triangulo values('001',120,48,136);
insert into triangulo values('002',121,58,166);
insert into triangulo values('003',5,4,3);
insert into triangulo values('004',12,48,36);
insert into triangulo values('005',120,48,136);
insert into triangulo values('006',12,12,6);
insert into triangulo values('007',12,12,12);
insert into triangulo values('008',4,4,3);
insert into triangulo values('009',14,14,14);
insert into triangulo values('010',12,13,8);

--1.- Indicar los lados del triangulo y el area correspondiente
--2.- Indicar los lados del triangulo y los angulos-
-3.- Indicar los lados de los triangulos rectangulos
--4.- Indicar los lados de los triangulos isosceles
--5.- Indicar los lados de aquellos que no son triangulos

EXAMEN FINAL Nº4

Correr el script Siguiente y realizar las consultas

1. Listado de alumnos cuyo correo no esta en yahoo
2. Listado de alumnos que nunca se han matriculado
3. Listado de alumnos que no tienen nota
4. Listado de alumnos matriculados Hoy dia
5. Listado de alumnos matriculados el dia de ayer
6. 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)
7. Promedio de notas por semestre y curso.
8. La cantidad alumnos por sexo de cada semestre (El sexo como nombre de columna)
9. La cantidad de alumnos por semestre y en cada ciclo indicar cuantos varones y mujeres hay (como nombre de columna)
10. Cantidad de alumnos matriculados por semestre en cada mes (Los meses como nombre de columna)

create table alumno(
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');

PRACTICA CON SOLUCION

-- Correr el script y responder utilizando SQL
create table alumno(
idalumno char(4) primary key,
paterno varchar(20),
materno varchar(20),
nombre varchar(20));

insert into alumno values('0001','Quispe','Rivas','Juan');
insert into alumno values('0002','Soto','Castro','Luis');
insert into alumno values('0003','Rivas','Quino','Ana');
insert into alumno values('0004','Castro','Rojas','Maria');

create table curso(
idcurso char(5) primary key,
curso varchar(20));

insert into curso values('MA100','MATEMATICA');
insert into curso values('FI100','FISICA');
insert into curso values('QU100','QUIMICA');
insert into curso values('FI101','FILOSOFIA');
create table nota (
idalumno char(4),
idcurso char(5),
nota numeric);

insert into nota values('0001','MA100',12);
insert into nota values('0001','FI100',14);
insert into nota values('0001','QU100',08);
insert into nota values('0001','FI101',16);
insert into nota values('0002','MA100',11);
insert into nota values('0002','FI100',08);
insert into nota values('0002','QU100',09);
insert into nota values('0002','FI101',10);
insert into nota values('0003','MA100',17);
insert into nota values('0003','FI100',13);
insert into nota values('0003','QU100',18);
insert into nota values('0003','FI101',19);
insert into nota values('0004','MA100',14);
insert into nota values('0004','FI100',15);
insert into nota values('0004','QU100',13);
insert into nota values('0004','FI101',15);

-------Listado de notas maximas por curso-----------
SELECT idcurso,max(nota) as notamax from nota group by idcurso

-------Listado de notas máximas por curso indicando a que alumno corresponde ------
select m.idcurso, paterno,materno,nombre,notamax from (SELECT idcurso,max(nota) as notamax from nota group by idcurso) m, nota n, alumno a where m.idcurso=n.idcurso and m.notamax=n.nota and n.idalumno=a.idalumno

Practica Nº13

create table alumno(
idalumno char(4) primary key,
paterno varchar(20),
materno varchar(20),
nombre varchar(20),
fecha_nac datetime);

insert into alumno values('0001','Soto','Quispe','Cesar','19900217');
insert into alumno values('0002','Sotelo','Menacho','Pedro','19940215');
insert into alumno values('0003','Castro','Soca','Juana','19870416');
insert into alumno values('0004','Rojas','Contreras','Maria','198980214');
insert into alumno values('0005','Castillo','Luque','Ana','19890512');
insert into alumno values('0006','Garcia','Luna','Juan','19900613');
--1.- Mostrar la lista de los que han nacido en el mes de Febrero
--2.- Mostrar los nombres y apellidos con la fecha de nacimiento en el formato siguiente:-- ejemplo:12/05/07, debe mostrar : 12 de Mayo del 2007
--3.- Mostrar la lista con las fechas agregadas en 20 dias solo a los que han nacido en el año 1989
--4.- Mostrar la lista con la edad de los alumnos (utilizar solo deferencia de años)
--5.- Mostrar la lista con las fechas disminuidas en 15 dias solo a los que no han nacido en el mes de Febrero
--6.- Mostrar las edades exactas de las persona considerar si cumplio o no años

Practica N° 12

create table alumno(
idalumno char(4) primary key,
paterno varchar(20),
materno varchar(20),
nombre varchar(20));

insert into alumno values('001','Rivas','Quintana','Cesar');
insert into alumno values('002','Rivera','Quino','Ana');
insert into alumno values('003','Castro','Roca','Karina');
insert into alumno values('004','Lopez','Hidalgo','Marina');
insert into alumno values('005','Rivera','Cardenas','Pedro');
insert into alumno values('006','Rojas','Lopez','Juan');
insert into alumno values('007','Malca','Lujan','Maria');

create table curso(
idcurso char(4) primary key,
curso varchar(20));

insert into curso values('01','Matematica');
insert into curso values('02','Fisica');
insert into curso values('03','Quimica');
insert into curso values('04','Literatura');
insert into curso values('05','Biologia');
insert into curso values('06','Filosofia');

create table nota(
idcurso char(4),
idalumno varchar(20),
nota numeric,
unidad TinyInt);

insert into nota values('01','001',15,1);insert into nota values('02','001',13,1);
insert into nota values('03','001',14,1);insert into nota values('04','001',16,1);
insert into nota values('05','001',10,1);insert into nota values('01','002',11,1);
insert into nota values('02','002',08,1);insert into nota values('03','002',12,1);
insert into nota values('04','002',09,1);insert into nota values('05','002',17,1);
insert into nota values('01','003',11,1);insert into nota values('02','003',18,1);
insert into nota values('03','003',17,1);insert into nota values('04','003',14,1);
insert into nota values('05','003',12,1);insert into nota values('01','004',14,1);
insert into nota values('02','004',15,1);insert into nota values('03','004',19,1);
insert into nota values('04','004',12,1);insert into nota values('05','004',14,1);
insert into nota values('01','005',09,1);insert into nota values('02','005',19,1);
insert into nota values('03','005',12,1);insert into nota values('04','005',13,1);
insert into nota values('05','005',14,1);insert into nota values('01','006',09,1);
insert into nota values('02','006',08,1);insert into nota values('03','006',12,1);
insert into nota values('04','006',16,1);insert into nota values('05','006',15,1);
insert into nota values('01','001',11,2);insert into nota values('02','001',12,2);
insert into nota values('03','001',14,2);insert into nota values('04','001',15,2);
insert into nota values('05','001',16,2);insert into nota values('01','002',12,2);
insert into nota values('02','002',18,2);insert into nota values('03','002',13,2);
insert into nota values('04','002',19,2);insert into nota values('05','002',12,2);
insert into nota values('01','003',14,2);insert into nota values('02','003',15,2);
insert into nota values('03','003',16,2);insert into nota values('04','003',17,2);
insert into nota values('05','003',13,2);insert into nota values('01','004',14,2);
insert into nota values('02','004',16,2);insert into nota values('03','004',14,2);
insert into nota values('04','004',16,2);insert into nota values('05','004',17,2);
insert into nota values('01','005',19,2);insert into nota values('02','005',13,2);
insert into nota values('03','005',14,2);insert into nota values('04','005',17,2);
insert into nota values('05','005',09,2);insert into nota values('01','006',07,2);
insert into nota values('02','006',06,2);insert into nota values('03','006',13,2);
insert into nota values('04','006',14,2);insert into nota values('05','006',16,2);
-- Listado de las notas maximas por curso.-- Indicar a quien pertence dichas notas maximas-- Listado del promedio maximo por curso-- Indicar a quienes pertenecen los promedio maximos -- Listado de alumnos indicando su nota por curso: los cursos va como nombre de columna

Practica Nº 11

create table alumno1(
idalumno char(4) primary key,
paterno varchar(20),
materno varchar(20),
nombre varchar(20),
telefono varchar(20)
);

insert into alumno1 values('001','Rivas','Quintana','Cesar','4425698');
insert into alumno1 values('002','Rivera','Quino','Ana','5425698');
insert into alumno1 values('003','Castro','Roca','Karina','3225698');
insert into alumno1 values('004','Lopez','Hidalgo','Marina','5623569');

create table alumno2(
idalumno char(4) primary key,
paterno varchar(20),
materno varchar(20),
nombre varchar(20),
correo varchar(20)
);
insert into alumno2 values('005','Rivera','Cardenas','Pedro','privera@hotmail.com');
insert into alumno2 values('006','Rojas','Lopez','Juan','jrojas@gmail.com');
insert into alumno2 values('007','Malca','Lujan','Maria','mmalca@hotmail.com');
insert into alumno2 values('008','Cardenas','Menacho','Carolina','ccardenas@yahoo.es');
insert into alumno2 values('009','Rivas','Rojas','Ana','arivas@hotmail.com');
insert into alumno2 values('010','Lopez','Ayma','Erika','elopez@yahoo.com');
insert into alumno2 values('011','Castro','Lizarme','Juli','jcastro@hotmail.com');
insert into alumno2 values('012','Llosa','Luque','Marta','mllosa@yahoo.com');

--1.- Listado de todos los alumnos de ambas tablas
--2.- Listado de todos los alumnos cuyo apellido paterno inicia con 'R' de ambas tablas
--3.- Agregar a la tabla alumno1 todos los alumnos que tienen correo en yahoo de alumno2
--4.- Listado de todos los alumnos que estan en alumno1, además mostrar los correos de los que tienen
-- por que estan en alumno2

--5.- Agregar a la tabla alumno2 todos los alumnos de alumno1
--6.- listado de todos los alumnos con sus correos que estan en alumno2 y los telefonos de los que tienen
-- en alumno1
--7.- Indicar cuantos alumnos tienen en su apellido materno al menos una letra n

Practica Nº 10

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','ANCON');
INSERT INTO distrito values('04','LOS OLIVOS');
INSERT INTO distrito values('05','COMAS');
INSERT INTO distrito values('06','MIRAFLORES');
INSERT INTO distrito values('07','SAN ISIDRO');
INSERT INTO distrito values('08','SAN JUAN DE MIRAFLORES');

CREATE TABLE alumno(
idalumno char(4) primary key,
paterno varchar(30),
materno varchar(20),
nombre varchar(20),
iddistrito char(2) references distrito,
Sexo char(1));

INSERT INTO alumno values('0001','SOTO','CAMPOS','CESAR','02','M');
INSERT INTO alumno values('0002','RIVAS','ROJAS','CARLOS','03','M');
INSERT INTO alumno values('0003','MENACHO','QUISPE','MARIA','04','F');
INSERT INTO alumno values('0004','QUINTANA','VARGAS','LUIS','02','M');
INSERT INTO alumno values('0005','ZAPATA','CUETO','ANA','01','F');
INSERT INTO alumno values('0006','CORREA','LLOSA','JUAN','01','M');
INSERT INTO alumno values('0007','LOPEZ','LEE','LEANDRO','03','M');
INSERT INTO alumno values('0008','SOTELO','CARDENAS','CHARLES','02','M');
INSERT INTO alumno values('0010','SOTO','RIVERA','PEDRO','02','M');
INSERT INTO alumno values('0011','SOTOMAYOR','RIVAS','KARINA','03','F');
INSERT INTO alumno values('0012','ROJAS','LUNA','CARMEN','04','F');
INSERT INTO alumno values('0013','HIDALGO','LOPEZ','JULI','02','F');

CREATE TABLE matricula(
idmatricula CHAR(4) primary key,
idalumno CHAR(4) references alumno,
semestre char(7),
ciclo char(2), seccion char(1),
fecha datetime);

INSERT INTO matricula values('0001','0001','2004-01','01','A','20040212');
INSERT INTO matricula values('0002','0002','2004-01','01','A','20040213');
INSERT INTO matricula values('0003','0003','2004-01','01','B','20040214');
INSERT INTO matricula values('0004','0007','2004-01','01','A','20040416');
INSERT INTO matricula values('0005','0005','2004-01','01','B','20040417');
INSERT INTO matricula values('0006','0006','2004-01','01','B','20040422');
INSERT INTO matricula values('0007','0001','2004-02','02','B','20040813');
INSERT INTO matricula values('0008','0007','2004-02','02','B','20040914');
INSERT INTO matricula values('0009','0008','2004-02','02','A','20041015');
INSERT INTO matricula values('0010','0009','2004-02','02','A','20040816');
INSERT INTO matricula values('0011','0005','2004-02','02','A','20040812');
INSERT INTO matricula values('0012','0006','2004-02','02','B','20040812');
INSERT INTO matricula values('0013','0002','2005-01','03','B','20040212');
INSERT INTO matricula values('0014','0006','2005-01','03','A','20040312');
INSERT INTO matricula values('0015','0005','2005-01','03','A','20040313');
INSERT INTO matricula values('0016','0007','2005-01','03','A','20040409');
INSERT INTO matricula values('0017','0008','2005-01','03','A','20040312');
INSERT INTO matricula values('0018','0006','2005-01','03','B','20040314');
INSERT INTO matricula values('0019','0003','2005-02','04','A','20040812');
INSERT INTO matricula values('0020','0005','2005-02','04','A','20040812');
INSERT INTO matricula values('0021','0006','2005-02','04','B','20040812');
INSERT INTO matricula values('0022','0007','2005-02','04','A','20040812');
INSERT INTO matricula values('0023','0008','2005-02','04','B','20040812');
INSERT INTO matricula values('0024','0006','2005-02','04','B','20040812');
INSERT INTO matricula values('0025','0002','2006-01','05','A','20040212');
INSERT INTO matricula values('0026','0003','2006-01','05','C','20040313');
INSERT INTO matricula values('0027','0006','2006-01','05','B','20040313');
INSERT INTO matricula values('0028','0007','2006-01','05','A','20040414');
INSERT INTO matricula values('0029','0005','2006-01','05','C','20040215');
INSERT INTO matricula values('0030','0008','2006-01','05','C','20040212');
INSERT INTO matricula values('0031','0001','2006-02','06','C','20040608');
INSERT INTO matricula values('0032','0002','2006-02','06','B','20040612');
INSERT INTO matricula values('0033','0003','2006-02','06','B','20040713');
INSERT INTO matricula values('0034','0005','2006-02','06','C','20040813');
INSERT INTO matricula values('0035','0006','2006-02','06','B','20040814');
INSERT INTO matricula values('0036','0007','2006-02','06','C','20040915');
-- 1.- Cantidad de alumnos por Distrito y sexo: El sexo debe estar como nombre de columna
-- 2.- Cantidad de alumnos matriculados por semestre segun ciclo: El semestre debe estar como nombre de columna--
3.- Cantidad de alumnos matriculados por semestre segun ciclo: El Ciclo debe estar como nombre de columna
-- 4.- Cantidad de alumnos matriculados por sexo: Los semestres deben estar como nombre de columna
-- 5.- Cantidad de alumnos matricuolados por semestre segun sexo: El sexo debe estar como nombre de columna
-- 6.- Cantidad de alumnos matriculados por seccion segun semestre: El semestre debe estar como nombre de columna
-- 7.- Cantidad de alumnos matriculados por mes y semestre: El mes debe estar como nombre de columna
-- 8.- Cantidad de alumnos por seccion y ciclo segun semestre: La seccion debe estar como nombre de columna
-- 9.- Cantidad de alumnos por sexo, semestre y seccion segun ciclo: sexo, semestre y seccion como nombre de columna

Practica Nº9

CREATE TABLE distrito(
iddisstrito char(2) primary key,
distrito varchar(30)
);
INSERT INTO distrito values('01','LIMA');
INSERT INTO distrito values('02','ATE');
INSERT INTO distrito values('03','ANCON');
INSERT INTO distrito values('04','LOS OLIVOS');
INSERT INTO distrito values('05','COMAS');
INSERT INTO distrito values('06','MIRAFLORES');
INSERT INTO distrito values('07','SAN ISIDRO');
INSERT INTO distrito values('08','SAN JUAN DE MIRAFLORES');


CREATE TABLE alumno(
idalumno char(4) primary key,
paterno varchar(30),
materno varchar(20),
nombre varchar(20),
iddistrito char(2) references distrito,
sexo char(1),
fec_nac datetime
);

INSERT INTO alumno values('0001','SOTO','CAMPOS','CESAR','02','M','19910312');
INSERT INTO alumno values('0002','RIVAS','ROJAS','CARLOS','03','M','19930312');
INSERT INTO alumno values('0003','MENACHO','QUISPE','MARIA','04','F','19960312');
INSERT INTO alumno values('0004','QUINTANA','VARGAS','LUIS','02','M','19840312');
INSERT INTO alumno values('0005','ZAPATA','CUETO','ANA','01','F','19820312');
INSERT INTO alumno values('0006','CORREA','LLOSA','JUAN','01','M','19880312');
INSERT INTO alumno values('0007','LOPEZ','LEE','LEANDRO','03','M','19800312');
INSERT INTO alumno values('0008','SOTELO','CARDENAS','CHARLES','02','M','19990312');
INSERT INTO alumno values('0009','SOTO','RIVERA','PEDRO','02','M','19970312');
INSERT INTO alumno values('0010','CASTRO','MENDOZA ','KARINA ','02','M','19970312');

CREATE TABLE matricula(
idmatricula CHAR(4) primary key,
idalumno CHAR(4) references alumno,
semestre char(7),
ciclo char(2),
seccion char(1),
fecha datetime
);

INSERT INTO matricula values('0001','0001','2004-01','01','A','20040212');
INSERT INTO matricula values('0002','0002','2004-01','01','A','20040213');
INSERT INTO matricula values('0003','0003','2004-01','01','B','20040214');
INSERT INTO matricula values('0004','0007','2004-01','01','A','20040416');
INSERT INTO matricula values('0005','0005','2004-01','01','B','20040417');
INSERT INTO matricula values('0006','0006','2004-01','01','B','20040422');

INSERT INTO matricula values('0007','0001','2004-02','02','B','20040813');
INSERT INTO matricula values('0008','0007','2004-02','02','B','20040914');
INSERT INTO matricula values('0009','0008','2004-02','02','A','20041015');
INSERT INTO matricula values('0010','0009','2004-02','02','A','20040816');
INSERT INTO matricula values('0011','0005','2004-02','02','A','20040812');
INSERT INTO matricula values('0012','0006','2004-02','02','B','20040812');

INSERT INTO matricula values('0013','0002','2005-01','03','B','20040212');
INSERT INTO matricula values('0014','0006','2005-01','03','A','20040312');
INSERT INTO matricula values('0015','0005','2005-01','03','A','20040313');
INSERT INTO matricula values('0016','0007','2005-01','03','A','20040409');
INSERT INTO matricula values('0017','0008','2005-01','03','A','20040312');
INSERT INTO matricula values('0018','0006','2005-01','03','B','20040314');

INSERT INTO matricula values('0019','0003','2005-02','04','A','20040812');
INSERT INTO matricula values('0020','0005','2005-02','04','A','20040812');
INSERT INTO matricula values('0021','0006','2005-02','04','B','20040812');
INSERT INTO matricula values('0022','0007','2005-02','04','A','20040812');
INSERT INTO matricula values('0023','0008','2005-02','04','B','20040812');
INSERT INTO matricula values('0024','0006','2005-02','04','B','20040812');

INSERT INTO matricula values('0025','0002','2006-01','05','A','20040212');
INSERT INTO matricula values('0026','0003','2006-01','05','C','20040313');
INSERT INTO matricula values('0027','0006','2006-01','05','B','20040313');
INSERT INTO matricula values('0028','0007','2006-01','05','A','20040414');
INSERT INTO matricula values('0029','0005','2006-01','05','C','20040215');
INSERT INTO matricula values('0030','0008','2006-01','05','C','20040212');

INSERT INTO matricula values('0031','0001','2006-02','06','C','20040608');
INSERT INTO matricula values('0032','0002','2006-02','06','B','20040612');
INSERT INTO matricula values('0033','0003','2006-02','06','B','20040713');
INSERT INTO matricula values('0034','0005','2006-02','06','C','20040813');
INSERT INTO matricula values('0035','0006','2006-02','06','B','20040814');
INSERT INTO matricula values('0036','0007','2006-02','06','C','20040915');

--1.- Listado de alumno indicando en una sola columna paterno, materno y nombres
--Sug.
--Usar: Concat(cad1, cad2, cad3,....) para concatenar las columnas

--2.- Indicar la lista de alumno con tres columnas: la primera los dos primeros caracteres del
-- apellido paterno, la segunda con los tres caracteres del apellido materno y la tercera con el
-- primer caracter del nombre.
--------- Sug:
--------- Substring(cadena,ini,num) extrae una parte de la cadena desde la posicion "ini"
--------- la cantidad de num caracrteres.

--3.- Listado de alumnos con el apellido paterno todo con minuscula, el apellido materno con
-- Mayuscula y el nombre todo con minusculas
--------- Sug:
--------- upper(cadena) convierte cadena a mayuscula
--------- lower(cadena) convierte cadena a minusculas
--4.- Indicar los apellidos y nombres de los alumnos varones en minuscula sólo con las iniciales de cada
-- nombre y apellidos con mayuscula.
--5.- Realizar una consulta para mostrar los nombre y apellidos de los matriculados y la seccion
-- del siguiente modo: si es seccion A debe salir 'SECCION A'
--6.- Indicar la lista de todos los matriculados en el ciclo '03', seccion 'A' y semestre '2005-01'
--7.- Realizar una consulta para indicar el número de alumnos matriculados por distrito.
-- Cuando exista menos de 5 alumno debe mostrar asteriscos caso contrario la cantidad respectiva
--8.- Realizar una consulta para indicar el número de alumnos matriculados por sexo
-- Debe indicar 'VARON' O 'MUJER' segun corresponda
--9.- Realizar una consulta para indicar el número de alumnos matriculados por seccion indicando
-- 'Seccion A' , 'Seccion B' o 'Seccion C' según corresponda

EXAMEN 3ra Unidad

Correr el script y Resolver las siguientes consultas Con SQL

1. Que productos nunca se han vendido.
2. A que cliente se le vendió más.
3. Que cliente nunca compro.
4. Que producto tiene más demanda.
5. Listado de la recaudación diaria por cajero
6. Total comprado por proveedor
7. Al proveedor PLASTICOS SAC que productos se le compro el 2006
8. Los clientes cuyo correo no sea de Hotmail
9. En que distrito viven más clientes
10. En que distrito no viven clientes

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','CHACLACAYO');
insert into distrito values('08','CHORRILLOS');
insert into distrito values('09','CIENEGUILLA');
insert into distrito values('10','COMAS');


create Table Cliente(
IdCliente char(4) primary key,
Paterno varchar(20),
Materno varchar(20),
Nombre varchar(20),
correo varchar(50),
direccion varchar(50),
telefono varchar(20),
IdDistrito char(2) references distrito);

insert into Cliente values('1000','SOTO','QUISPE','CESAR','CQUISPE@HOTMAIL.COM','JR ICA 12','4506231','03');
insert into Cliente values('1001','RIVAS','MENACHO','CARLOS','CRIVAS@HOTMAIL.COM','JR TACNA 21','5566241','05');
insert into Cliente values('1002','GARCIA','HUMAN','MARIA','MGARCIA@GMAIL.COM','AV ICA 123','6503431','08');
insert into Cliente values('1003','CARDENAS','SOTELO','ANA','ACARDENAS@MIXMAIL.COM','AV PERU 1232','9506561','07');
insert into Cliente values('1004','ALVAREZ','CASTRO','ALICIA','AALVAREZ@HOTMAIL.COM','JR IQUITOS 654','9506238','04');
insert into Cliente values('1005','FACUNDO','QUISPE','CESAR','CQUISPE@HOTMAIL.COM','JR ICA 12','4506231','03');
insert into Cliente values('1006','BULEJE','CONTRERAS','LUIS','LBULEJE@HOTMAIL.COM','JR MOQUEGUA 56','7506235','07');
insert into Cliente values('1007','CARRILLO','BUENO','PEDRO','PCARRILLO@HOTMAIL.COM','JR ICA 124','4506231','02');
insert into Cliente values('1008','CASTRO','LOPEZ','KARINA','KCASTRO@YAHOO.COM','JR ICA 245','4506231','03');
insert into Cliente values('1009','DIAZ','LUJAN','AUGUSTO','ADIAZ@HOTMAIL.COM','JR ICA 221','4506231','05');


create Table Producto(
IdProducto char(4) primary key,
producto varchar(30),
cantidad numeric,
precio_lista numeric,
stock_min numeric);
insert into Producto values('1000','PAPEL BOND A4',2000,0.020,100);
insert into Producto values('1001','PAPEL PERIODICO A4',400,0.0015,400);
insert into Producto values('1002','BORRADOR',20,0.5,50);
insert into Producto values('1003','LAPICERO',540,0.8,10);
insert into Producto values('1004','CUADERNO DE 100 HOJAS',200,2.5,50);
insert into Producto values('1005','CUADERNO DE 50 HOJAS',600,2.0,800);
insert into Producto values('1006','CUADERNO ESPIRALADO',640,3.5,100);
insert into Producto values('1007','FOLDER DE MANILA A4',1600,0.8,10);
insert into Producto values('1008','SOBRE DE MANILA A4',700,0.5,50);
insert into Producto values('1009','GOMA SINTETICA',200,4,10);
insert into Producto values('1010','GOMA EN BARRA',800,1.5,1000);
insert into Producto values('1011','MICA A4',100,1,50);
insert into Producto values('1012','PORTA CARNET',2000,0.5,40);
insert into Producto values('1013','VINIFAN NORMAL',80,2.4,40);
insert into Producto values('1014','VINIFAN TAMAÑO OFICIO',84,3,20);
insert into Producto values('1015','PAPEL LUSTRE',2000,0.1,100);


create Table usuario(
IdUsuario char(4) primary key,
nombre varchar(20),
pasword varchar(20));
insert into usuario values('0001','CARLOS','1234');
insert into usuario values('0002','CESAR','4321');
insert into usuario values('0003','MARIA','5678');

create Table venta(
num_recibo char(11) primary key,
IdCliente char(4) references cliente,
Fecha date,
IdUsuario char(4) references usuario);
insert into venta values('00000000001','1000','20060315','0001');
insert into venta values('00000000002','1001','20060316','0002');
insert into venta values('00000000003','1002','20060418','0002');
insert into venta values('00000000004','1004','20060520','0001');
insert into venta values('00000000005','1006','20060521','0003');
insert into venta values('00000000006','1007','20060625','0003');
insert into venta values('00000000007','1003','20060816','0001');

create Table detalle_venta(
IdProducto char(4) references producto,
Num_recibo char(11) references venta,
cantidad numeric,
Precio_venta numeric,
constraint pk_detalle_venta primary key(IdProducto,num_recibo));

insert into Detalle_venta values('1000','00000000001',50,0.020);
insert into Detalle_venta values('1001','00000000001',40,0.0015);
insert into Detalle_venta values('1002','00000000001',8,0.5);
insert into Detalle_venta values('1001','00000000002',4,0.0015);
insert into Detalle_venta values('1002','00000000002',5,0.5);
insert into Detalle_venta values('1003','00000000002',6,0.8);
insert into Detalle_venta values('1004','00000000002',20,2.5);
insert into Detalle_venta values('1005','00000000003',60,2.0);
insert into Detalle_venta values('1006','00000000003',4,3.5);
insert into Detalle_venta values('1007','00000000003',16,0.8);
insert into Detalle_venta values('1008','00000000004',7,0.5);
insert into Detalle_venta values('1009','00000000004',2,4);
insert into Detalle_venta values('1010','00000000005',8,1.5);
insert into Detalle_venta values('1011','00000000006',10,1);
insert into Detalle_venta values('1006','00000000006',4,3.5);
insert into Detalle_venta values('1012','00000000006',2,0.5);
insert into Detalle_venta values('1004','00000000007',20,2.5);


create Table proveedor(
IdProveedor char(4) primary key,
Razon_Social varchar(40),
correo varchar(50),
direccion varchar(50),
telefono varchar(20),
IdDistrito char(2) references distrito);

insert into Proveedor values('1000','METRO SAC','METRO@HOTMAIL.COM','JR CUZCO 121','4306221','04');
insert into Proveedor values('1001','LOS BODEGUEROS SAC','BODEG@HOTMAIL.COM','JR LIMA 221','5568241','06');
insert into Proveedor values('1002','PLASTICOS SAC','PLASTICOS@GMAIL.COM','AV PERU 23','6543431','07');
insert into Proveedor values('1003','LAUCHUN','LAUCHUN@MIXMAIL.COM','AV ANDAHUAYLAS 232','8506561','05');
insert into Proveedor values('1004','LORO SAC','LORO@HOTMAIL.COM','JR AMAZONAS 654','9404238','04');
insert into Proveedor values('1005','ATLAS SAC','ATLAS@HOTMAIL.COM','JR ICA 12','4534231','03');

create Table compra(
num_recibo char(11) primary key,
IdProveedor char(4) references cliente,
Fecha date,
IdUsuario char(4) references usuario);
insert into compra values('00000000001','1001','20050314','0001');
insert into compra values('00000000002','1002','20050316','0001');
insert into compra values('00000000003','1001','20050518','0002');
insert into compra values('00000000004','1004','20051220','0001');
insert into compra values('00000000005','1004','20060522','0002');
insert into compra values('00000000006','1005','20060724','0003');
insert into compra values('00000000007','1003','20060826','0003');

create Table detalle_compra(
IdProducto char(4) references producto,
Num_recibo char(11) references venta,
cantidad numeric,
Precio_compra numeric,
constraint pk_detalle_compra primary key(IdProducto,num_recibo));

insert into Detalle_compra values('1000','00000000001',150,0.015);
insert into Detalle_compra values('1001','00000000001',100,0.0010);
insert into Detalle_compra values('1002','00000000001',80,0.45);
insert into Detalle_compra values('1003','00000000001',40,0.0005);
insert into Detalle_compra values('1002','00000000002',50,0.4);
insert into Detalle_compra values('1003','00000000002',60,0.7);
insert into Detalle_compra values('1004','00000000003',10,2);
insert into Detalle_compra values('1005','00000000003',20,1.5);
insert into Detalle_compra values('1006','00000000003',40,3.2);
insert into Detalle_compra values('1007','00000000004',20,0.75);
insert into Detalle_compra values('1008','00000000004',20,0.4);
insert into Detalle_compra values('1009','00000000005',20,3.5);
insert into Detalle_compra values('1010','00000000005',80,1.25);
insert into Detalle_compra values('1011','00000000006',10,0.8);
insert into Detalle_compra values('1006','00000000006',40,3.25);
insert into Detalle_compra values('1012','00000000007',20,0.4);
insert into Detalle_compra values('1004','00000000007',10,2.4);

Fin Script
________________________________________________

Practica Nº8

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 sexo(
idSexo char(1) primary key,
Sexo varchar(20));

insert into sexo values('M','Masculino');
insert into sexo values('F','Femenino');

create table Alumno(
idAlumno char(5) primary key,
paterno varchar(20),
materno varchar(20),
nombre varchar(20),
idDistrito char(2) references Distrito,
idSexo Char(1) references sexo);

insert into Alumno values('00001','Soto','Quispe','Luis Agusto','02','M');
insert into Alumno values('00002','Buleje','Cuba','Cesar','03','M');
insert into Alumno values('00003','Quino','Rios','Cesar Luis','08','M');
insert into Alumno values('00004','Perez','Menacho','Luis Alberto','08','M');
insert into Alumno values('00005','Mallma','Quintana','Karina','03','F');
insert into Alumno values('00006','Villafuerte','Supo','Juan','01','M');
insert into Alumno values('00007','Guzman','Arroyo','Jose Luis','03','M');
insert into Alumno values('00008','Rivas','Lujan','Carlos','06','M');
insert into Alumno values('00009','Castro','Lima','Maria','08','F');
insert into Alumno values('00010','Sotelo','Baca','Ana','08','F');
insert into Alumno values('00011','Soto','Lopez','Adrian','05','M');
insert into Alumno values('00012','Supo','Castro','Karen','07','F');
insert into Alumno values('00013','Garcia','Carrillo','Juan','05','M');

create table Inicio(
idInicio char(5) primary key,
ciclo char(2),
semestre char(7),
fec_inicio datetime);

insert into Inicio values('00001','01','2003-01','20030305');
insert into Inicio values('00002','02','2003-02','20030805');
insert into Inicio values('00003','03','2004-01','20040305');
insert into Inicio values('00004','04','2004-02','20040805');
insert into Inicio values('00005','05','2005-01','20050305');
insert into Inicio values('00006','06','2005-02','20050805');
insert into Inicio values('00007','07','2006-01','20060305');
insert into Inicio values('00008','08','2006-02','20060805');

create table matricula(
idmatricula char(6) primary key,
idAlumno char(5) references alumno,
idInicio char(5) references Inicio,
fecha datetime);

insert into matricula values('030001','00001','00001','20030112');
insert into matricula values('030002','00002','00001','20030112');
insert into matricula values('030003','00003','00001','20030112');
insert into matricula values('030004','00004','00001','20030113');
insert into matricula values('030005','00005','00001','20030113');
insert into matricula values('030006','00006','00001','20030114');
insert into matricula values('030007','00007','00001','20030115');
insert into matricula values('030008','00008','00001','20030116');
insert into matricula values('030009','00009','00001','20030118');
insert into matricula values('030010','00010','00001','20030122');
insert into matricula values('030011','00001','00002','20030713');
insert into matricula values('030012','00002','00002','20030713');
insert into matricula values('030013','00003','00002','20030714');
insert into matricula values('030014','00004','00002','20030814');
insert into matricula values('030015','00005','00002','20030814');
insert into matricula values('030016','00006','00002','20030815');
insert into matricula values('030017','00007','00002','20030816');
insert into matricula values('030018','00008','00002','20030817');
insert into matricula values('030019','00009','00002','20030918');
insert into matricula values('030020','00010','00002','20030924');
insert into matricula values('030021','00001','00003','20040213');
insert into matricula values('030022','00002','00003','20040213');
insert into matricula values('030023','00003','00003','20040214');
insert into matricula values('030024','00004','00003','20040214');
insert into matricula values('030026','00006','00003','20040215');
insert into matricula values('030027','00007','00003','20040216');
insert into matricula values('030028','00008','00003','20040217');
insert into matricula values('030029','00009','00003','20040308');
insert into matricula values('030030','00010','00003','20040309');
insert into matricula values('030031','00001','00004','20040613');
insert into matricula values('030032','00002','00004','20040613');
insert into matricula values('030033','00003','00004','20040614');
insert into matricula values('030034','00004','00004','20040714');
insert into matricula values('030035','00005','00004','20040714');
insert into matricula values('030036','00006','00004','20040715');
insert into matricula values('030037','00007','00004','20040811');
insert into matricula values('030038','00008','00004','20040813');
insert into matricula values('030039','00009','00004','20040805');
insert into matricula values('030040','00010','00004','20040817');
insert into matricula values('030041','00001','00005','20050211');
insert into matricula values('030042','00002','00005','20050211');
insert into matricula values('030043','00003','00005','20050211');
insert into matricula values('030044','00004','00005','20050212');
insert into matricula values('030045','00005','00005','20050213');
insert into matricula values('030046','00006','00005','20050213');
insert into matricula values('030047','00007','00005','20050214');
insert into matricula values('030048','00008','00005','20050214');
insert into matricula values('030049','00009','00005','20050314');
insert into matricula values('030050','00010','00005','20050315');
insert into matricula values('030051','00001','00006','20050711');
insert into matricula values('030052','00002','00006','20050711');
insert into matricula values('030055','00005','00006','20050803');
insert into matricula values('030056','00006','00006','20050813');
insert into matricula values('030057','00007','00006','20050814');
insert into matricula values('030058','00008','00006','20050814');
insert into matricula values('030059','00009','00006','20050814');
insert into matricula values('030060','00010','00006','20050815');
insert into matricula values('030061','00001','00007','20060212');
insert into matricula values('030062','00002','00007','20060212');
insert into matricula values('030063','00003','00007','20060213');
insert into matricula values('030064','00004','00007','20060213');
insert into matricula values('030065','00005','00007','20060213');
insert into matricula values('030066','00006','00007','20060214');
insert into matricula values('030067','00007','00007','20060215');
insert into matricula values('030068','00008','00007','20060216');
insert into matricula values('030069','00009','00007','20060316');
insert into matricula values('030070','00010','00007','20060316');
insert into matricula values('030071','00001','00008','20060612');
insert into matricula values('030072','00002','00008','20060612');
insert into matricula values('030073','00003','00008','20060613');
insert into matricula values('030074','00004','00008','20060713');
insert into matricula values('030075','00005','00008','20060713');
insert into matricula values('030076','00006','00008','20060714');
insert into matricula values('030077','00007','00008','20060715');
insert into matricula values('030079','00009','00008','20060813');
insert into matricula values('030080','00010','00008','20060815');

create table curso(
idcurso char(5) primary key,
curso 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');
insert into curso values('BD101','BASE DE DATOS II');

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

insert into nota values('030001','MA100','1',20);
insert into nota values('030002','MA100','1',16);
insert into nota values('030001','MA100','2',17);
insert into nota values('030002','MA100','2',16);
insert into nota values('030001','MA100','3',18);
insert into nota values('030002','MA100','3',15);
insert into nota values('030001','DG100','1',16);
insert into nota values('030002','DG100','1',18);
insert into nota values('030001','DG100','2',15);
insert into nota values('030002','DG100','2',16);
insert into nota values('030001','MA101','1',08);
insert into nota values('030002','MA101','1',06);
insert into nota values('030001','MA101','2',16);
insert into nota values('030002','MA101','2',12);
insert into nota values('030001','MA101','3',13);
insert into nota values('030002','MA101','3',14);
insert into nota values('030001','PG101','1',09);
insert into nota values('030002','PG101','1',10);
insert into nota values('030001','PG101','2',11);
insert into nota values('030002','PG101','2',13);
insert into nota values('030003','MA100','1',12);
insert into nota values('030004','MA100','1',12);
insert into nota values('030003','MA100','2',13);
insert into nota values('030004','MA100','2',14);
insert into nota values('030003','MA100','3',08);
insert into nota values('030004','MA100','3',05);
insert into nota values('030003','DG100','1',12);
insert into nota values('030004','DG100','1',11);
insert into nota values('030003','DG100','2',14);
insert into nota values('030004','DG100','2',14);
insert into nota values('030003','MA101','1',18);
insert into nota values('030004','MA101','1',09);
insert into nota values('030003','MA101','2',12);
insert into nota values('030004','MA101','2',11);
insert into nota values('030003','MA101','3',13);
insert into nota values('030004','MA101','3',15);
insert into nota values('030003','PG101','1',19);
insert into nota values('030004','PG101','1',20);
insert into nota values('030003','PG101','2',13);
insert into nota values('030004','PG101','2',14);

--obs. Considera los datos de los alumnos paterno, materno y nombres
--1.- Listado de alumnas mujeres que viven en 'Chaclacayo'
--2.- Cantidad de alumnos por sexo
--3.- Cantidad de alumnos por distrito
--4.- Cantidad de alumnos matriculados por sexo
--5.- Cantidad de alumnos matriculados por distrito
--6.- Listado de alumnos que al menos uno de sus nombres sea 'Luis'
--7.- Listado de alumnos matriculados en el semestre 2005-01
--8.- Cantidad de alumnos matriculados por semestre
--9.- Cantidad de alumnos desaprobados por ciclo y semestre
--10.-Listado de alumnos con promedio de notas por curso
--11.-Alumnos que nunca se han matriculado
--12.-Alumnos que no tienen ninguna nota
--13.-En que distritos no hay alumnos
--14.-En que Inicio no hay alumnos
--15.-Alumnas mujeres que no tienen nota en ningun curso
--16.-En que cursos no hay nota
--17.-Listado de alumnos que viven en Breña y nunca se han matriculado
--18.-Alumnos que se han matriculado el 12 de enero del 2003
--19.-Alumnos que no se han matriculado en el semestre '2005-02'
--20.-Alumnas mujeres que se han matriculado en el semestre
-- '2005-02', '2005-01' y '2004-02'

Referencia Cruzada

Create table notas(
idMatricula Char(3) primary key,
idCurso char(5),
nota numeric,
unidad
);
insert into notas values('001','MA100',10,1);
insert into notas values('001','MA100',14,2);
insert into notas values('001','MA100',11,3);
insert into notas values('002','MA100',08,4);
insert into notas values('002','MA100',15,3);
insert into notas values('002','MA100',6,4);
insert into notas values('002','MA100',10,4);
insert into notas values('003','MA100',19,4);
insert into notas values('003','MA100',20,5);
insert into notas values('001','FI100',11,1);
insert into notas values('001','FI100',15,2);
insert into notas values('001','FI100',16,3);
insert into notas values('002','FI100',09,4);
insert into notas values('002','FI100',14,3);
insert into notas values('002','FI100',9,4);
insert into notas values('002','FI100',08,4);
insert into notas values('003','FI100',16,4);
insert into notas values('003','FI100',13,5);
insert into notas values('001','QI100',10,1);
insert into notas values('001','QI100',11,2);
insert into notas values('001','QI100',12,3);
insert into notas values('002','QI100',14,4);
insert into notas values('002','QI100',08,3);
insert into notas values('002','QI100',12,4);
insert into notas values('002','QI100',18,4);
insert into notas values('003','QI100',17,4);
insert into notas values('003','QI100',04,5);

-- Cantidad de alumnos por Curso. Donde El nombre de curso sea la columna

Practica Nº7

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 sexo(
idSexo char(1) primary key,
Sexo varchar(20));
insert into sexo values('M','Masculino');
insert into sexo values('F','Femenino');

create table Alumno(
idAlumno char(5) primary key,
paterno varchar(20),
materno varchar(20),
nombre varchar(20),
idDistrito char(2) references Distrito,
idSexo Char(1) references sexo);

insert into Alumno values('00001','Soto','Quispe','Luis Agusto','02','M');
insert into Alumno values('00002','Buleje','Cuba','Cesar','03','M');
insert into Alumno values('00003','Quino','Rios','Cesar Luis','08','M');
insert into Alumno values('00004','Perez','Menacho','Luis Alberto','08','M');
insert into Alumno values('00005','Mallma','Quintana','Karina','03','F');
insert into Alumno values('00006','Villafuerte','Supo','Juan','01','M');
insert into Alumno values('00007','Guzman','Arroyo','Jose Luis','03','M');
insert into Alumno values('00008','Rivas','Lujan','Carlos','06','M');
insert into Alumno values('00009','Castro','Lima','Maria','08','F');
insert into Alumno values('00010','Sotelo','Baca','Ana','08','F');

create table Inicio(
idInicio char(5) primary key,
ciclo char(2),
semestre char(7),
fec_inicio date);

insert into Inicio values('00001','01','2003-01','20030305');
insert into Inicio values('00002','02','2003-02','20030805');
insert into Inicio values('00003','03','2004-01','20040305');
insert into Inicio values('00004','04','2004-02','20040805');
insert into Inicio values('00005','05','2005-01','20050305');
insert into Inicio values('00006','06','2005-02','20050805');
insert into Inicio values('00007','07','2006-01','20060305');
insert into Inicio values('00008','08','2006-02','20060805');

create table matricula(
idmatricula char(6) primary key,
idAlumno char(5) references alumno,
idInicio char(5) references Inicio,
fecha date);

insert into matricula values('030001','00001','00001','20030112');
insert into matricula values('030002','00002','00001','20030112');
insert into matricula values('030003','00003','00001','20030112');
insert into matricula values('030004','00004','00001','20030113');
insert into matricula values('030005','00005','00001','20030113');
insert into matricula values('030006','00006','00001','20030114');
insert into matricula values('030007','00007','00001','20030115');
insert into matricula values('030008','00008','00001','20030116');
insert into matricula values('030009','00009','00001','20030118');
insert into matricula values('030010','00010','00001','20030122');
insert into matricula values('030011','00001','00002','20030713');
insert into matricula values('030012','00002','00002','20030713');
insert into matricula values('030013','00003','00002','20030714');
insert into matricula values('030014','00004','00002','20030814');
insert into matricula values('030015','00005','00002','20030814');
insert into matricula values('030016','00006','00002','20030815');
insert into matricula values('030017','00007','00002','20030816');
insert into matricula values('030018','00008','00002','20030817');
insert into matricula values('030019','00009','00002','20030918');
insert into matricula values('030020','00010','00002','20030924');
insert into matricula values('030021','00001','00003','20040213');
insert into matricula values('030022','00002','00003','20040213');
insert into matricula values('030023','00003','00003','20040214');
insert into matricula values('030024','00004','00003','20040214');
insert into matricula values('030026','00006','00003','20040215');
insert into matricula values('030027','00007','00003','20040216');
insert into matricula values('030028','00008','00003','20040217');
insert into matricula values('030029','00009','00003','20040308');
insert into matricula values('030030','00010','00003','20040309');
insert into matricula values('030031','00001','00004','20040613');
insert into matricula values('030032','00002','00004','20040613');
insert into matricula values('030033','00003','00004','20040614');
insert into matricula values('030034','00004','00004','20040714');
insert into matricula values('030035','00005','00004','20040714');
insert into matricula values('030036','00006','00004','20040715');
insert into matricula values('030037','00007','00004','20040811');
insert into matricula values('030038','00008','00004','20040813');
insert into matricula values('030039','00009','00004','20040805');
insert into matricula values('030040','00010','00004','20040817');
insert into matricula values('030041','00001','00005','20050211');
insert into matricula values('030042','00002','00005','20050211');
insert into matricula values('030043','00003','00005','20050211');
insert into matricula values('030044','00004','00005','20050212');
insert into matricula values('030045','00005','00005','20050213');
insert into matricula values('030046','00006','00005','20050213');
insert into matricula values('030047','00007','00005','20050214');
insert into matricula values('030048','00008','00005','20050214');
insert into matricula values('030049','00009','00005','20050314');
insert into matricula values('030050','00010','00005','20050315');
insert into matricula values('030051','00001','00006','20050711');
insert into matricula values('030052','00002','00006','20050711');
insert into matricula values('030055','00005','00006','20050803');
insert into matricula values('030056','00006','00006','20050813');
insert into matricula values('030057','00007','00006','20050814');
insert into matricula values('030058','00008','00006','20050814');
insert into matricula values('030059','00009','00006','20050814');
insert into matricula values('030060','00010','00006','20050815');
insert into matricula values('030061','00001','00007','20060212');
insert into matricula values('030062','00002','00007','20060212');
insert into matricula values('030063','00003','00007','20060213');
insert into matricula values('030064','00004','00007','20060213');
insert into matricula values('030065','00005','00007','20060213');
insert into matricula values('030066','00006','00007','20060214');
insert into matricula values('030067','00007','00007','20060215');
insert into matricula values('030068','00008','00007','20060216');
insert into matricula values('030069','00009','00007','20060316');
insert into matricula values('030070','00010','00007','20060316');
insert into matricula values('030071','00001','00008','20060612');
insert into matricula values('030072','00002','00008','20060612');
insert into matricula values('030073','00003','00008','20060613');
insert into matricula values('030074','00004','00008','20060713');
insert into matricula values('030075','00005','00008','20060713');
insert into matricula values('030076','00006','00008','20060714');
insert into matricula values('030077','00007','00008','20060715');
insert into matricula values('030079','00009','00008','20060813');
insert into matricula values('030080','00010','00008','20060815');

-- 1.- Listado de alumnos que no viven en 'Lima'
-- 2.- Listado de Alumnos varones que viven en 'Ate'
-- 3.- Listado de alumnas mujeres que nunca se han matriculado
-- 4.- Listado de todos los Alumnos que nunca se han matriculado
-- 5.- Listado de Distrito donde no hay alumnos
-- 6.- Listado de Distritos donde no hubo ningun matriculado
-- 7.- Listado de Inicios donde no hay matriculados
-- 8.- Listado de Matriculados cuyos alumnos no viven en 'Lima'

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
________________________________________

Practica Nº6

--Ejecutar el siguiente Script en MySQL
create table alumno(
idalumno char(4) primary key,
paterno varchar(30),
materno varchar(30),
nombre char(30),
sexo char(1));

insert into alumno values('0001','RIVAS','MAMANI','CESAR','M');
insert into alumno values('0002','ROJAS','MENACHO','CARLOS','M');
insert into alumno values('0003','QUISPE','MENACHO','LUIS','M');
insert into alumno values('0004','CARRILLO','MENACHO','MARIA','F');
insert into alumno values('0005','RIVERA','MENACHO','CESAR','M');
insert into alumno values('0006','SOTIL','MENACHO','KARINA','F');
insert into alumno values('0007','BULEJE','MAMANI','CESAR','M');
insert into alumno values('0008','QUISPE','MUNARES','GENARO','M');
insert into alumno values('0009','QUINTEROS','MANCO','LUIS','M');
insert into alumno values('0010','CASTRO','ZUÑIGA','MARTA','F');
insert into alumno values('0011','RIVELINO','MELQUIADES','JUAN','M');
insert into alumno values('0012','SOTO','MELENDEZ','CESAR','M');

create table curso(
idcurso char(2) primary key,
curso varchar(20));

insert into curso values('01','Matematicas');
insert into curso values('02','Fisica');
insert into curso values('03','Quimica');
insert into curso values('04','Historia del Perú');
insert into curso values('05','Historia Universal');
insert into curso values('06','Lenguaje');
insert into curso values('07','Literatura');
insert into curso values('08','Filosofia');

create table nota(
idalumno char(4),
idcurso char(2),
unidad numeric,
grado char(1),
nota numeric);

insert into nota values('0001','01','1','1',12);
insert into nota values('0002','01','1','1',14);
insert into nota values('0007','01','1','1',16);
insert into nota values('0008','01','1','1',10);
insert into nota values('0009','01','1','1',08);
insert into nota values('0010','01','1','1',20);
insert into nota values('0011','01','1','1',14);
insert into nota values('0012','01','1','1',13);
insert into nota values('0001','03','1','1',19);
insert into nota values('0002','03','1','1',20);
insert into nota values('0004','03','1','1',05);
insert into nota values('0005','03','1','1',07);
insert into nota values('0006','03','1','1',09);
insert into nota values('0010','03','1','1',16);
insert into nota values('0011','03','1','1',04);
insert into nota values('0012','03','1','1',10);
insert into nota values('0001','04','1','1',14);
insert into nota values('0002','04','1','1',16);
insert into nota values('0003','04','1','1',17);
insert into nota values('0004','04','1','1',18);
insert into nota values('0008','04','1','1',19);
insert into nota values('0009','04','1','1',12);
insert into nota values('0001','06','1','1',15);
insert into nota values('0002','06','1','1',16);
insert into nota values('0003','06','1','1',16);
insert into nota values('0004','06','1','1',17);
insert into nota values('0005','06','1','1',19);
insert into nota values('0008','06','1','1',07);
insert into nota values('0009','06','1','1',06);
insert into nota values('0012','06','1','1',14);
insert into nota values('0001','07','1','1',13);
insert into nota values('0002','07','1','1',15);
insert into nota values('0003','07','1','1',17);
insert into nota values('0004','07','1','1',11);
insert into nota values('0009','07','1','1',15);
insert into nota values('0011','07','1','1',16);
insert into nota values('0012','07','1','1',17);
insert into nota values('0001','09','1','1',16);
insert into nota values('0002','09','1','1',12);
insert into nota values('0003','09','1','1',11);
insert into nota values('0007','09','1','1',10);
insert into nota values('0008','09','1','1',10);
insert into nota values('0011','09','1','1',17);
insert into nota values('0012','09','1','1',16);
insert into nota values('0001','10','1','1',19);
insert into nota values('0002','10','1','1',20);
insert into nota values('0003','10','1','1',12);
insert into nota values('0004','10','1','1',13);
insert into nota values('0007','10','1','1',14);
insert into nota values('0008','10','1','1',15);
insert into nota values('0009','10','1','1',16);
insert into nota values('0012','10','1','1',17);
insert into nota values('0001','01','2','1',08);
insert into nota values('0002','01','2','1',07);
insert into nota values('0003','01','2','1',06);
insert into nota values('0004','01','2','1',11);
insert into nota values('0007','01','2','1',14);
insert into nota values('0008','01','2','1',01);
insert into nota values('0009','01','2','1',03);
insert into nota values('0010','01','2','1',09);
insert into nota values('0011','01','2','1',07);
insert into nota values('0012','01','2','1',15);
insert into nota values('0001','06','2','1',16);
insert into nota values('0002','06','2','1',13);
insert into nota values('0003','06','2','1',12);
insert into nota values('0004','06','2','1',14);
insert into nota values('0005','06','2','1',12);
insert into nota values('0006','06','2','1',13);
insert into nota values('0008','06','2','1',12);
insert into nota values('0009','06','2','1',14);
insert into nota values('0010','06','2','1',12);
insert into nota values('0011','06','2','1',13);
insert into nota values('0012','06','2','1',11);
insert into nota values('0001','06','2','1',13);
insert into nota values('0002','06','2','1',14);
insert into nota values('0003','06','2','1',16);
insert into nota values('0004','06','2','1',19);
insert into nota values('0005','06','2','1',12);
insert into nota values('0006','06','2','1',14);
insert into nota values('0007','06','2','1',16);
insert into nota values('0008','06','2','1',15);
insert into nota values('0009','06','2','1',14);
insert into nota values('0010','06','2','1',15);
insert into nota values('0011','06','2','1',16);
insert into nota values('0012','06','2','1',15);
insert into nota values('0002','07','2','1',12);
insert into nota values('0003','07','2','1',11);
insert into nota values('0004','07','2','1',14);
insert into nota values('0005','07','2','1',13);
insert into nota values('0006','07','2','1',16);
insert into nota values('0007','07','2','1',05);
insert into nota values('0008','07','2','1',14);
insert into nota values('0009','07','2','1',15);
insert into nota values('0010','07','2','1',14);
insert into nota values('0011','07','2','1',14);
insert into nota values('0012','07','2','1',11);
insert into nota values('0001','01','1','2',14);
insert into nota values('0002','01','1','2',15);
insert into nota values('0003','01','1','2',15);
insert into nota values('0004','01','1','2',16);
insert into nota values('0005','01','1','2',13);
insert into nota values('0006','01','1','2',11);
insert into nota values('0007','01','1','2',08);
insert into nota values('0008','01','1','2',14);
insert into nota values('0009','01','1','2',17);
insert into nota values('0010','01','1','2',14);
insert into nota values('0011','01','1','2',12);
insert into nota values('0012','01','1','2',14);
insert into nota values('0001','06','1','2',11);
insert into nota values('0002','06','1','2',12);
insert into nota values('0003','06','1','2',14);
insert into nota values('0004','06','1','2',13);
insert into nota values('0005','06','1','2',12);
insert into nota values('0006','06','1','2',14);
insert into nota values('0008','06','1','2',15);
insert into nota values('0009','06','1','2',14);
insert into nota values('0010','06','1','2',16);
insert into nota values('0011','06','1','2',17);
insert into nota values('0012','06','1','2',14);
insert into nota values('0001','09','1','3',11);
insert into nota values('0002','09','1','3',12);
insert into nota values('0003','09','1','3',14);
insert into nota values('0004','09','1','3',14);
insert into nota values('0005','09','1','3',15);
insert into nota values('0006','09','1','3',10);
insert into nota values('0007','09','1','3',13);
insert into nota values('0008','09','1','3',13);
insert into nota values('0009','09','1','3',13);
insert into nota values('0010','09','1','3',13);
insert into nota values('0011','09','1','3',11);
insert into nota values('0012','09','1','3',14);
insert into nota values('0001','01','1','3',11);
insert into nota values('0002','01','1','3',12);
insert into nota values('0003','01','1','3',12);
insert into nota values('0004','01','1','3',13);
insert into nota values('0005','01','1','3',12);
insert into nota values('0006','01','1','3',11);
insert into nota values('0007','01','1','3',11);
insert into nota values('0008','01','1','3',14);
insert into nota values('0009','01','1','3',13);
insert into nota values('0010','01','1','3',13);
insert into nota values('0011','01','1','3',11);
insert into nota values('0012','01','1','3',11);
insert into nota values('0001','01','1','4',12);
insert into nota values('0002','01','1','4',11);
insert into nota values('0003','01','1','4',11);
insert into nota values('0004','01','1','4',12);
insert into nota values('0005','01','1','4',12);
insert into nota values('0006','01','1','4',14);
insert into nota values('0007','01','1','4',14);
insert into nota values('0008','01','1','4',14);
insert into nota values('0009','01','1','4',13);
insert into nota values('0010','01','1','4',17);
insert into nota values('0011','01','1','4',12);
insert into nota values('0012','01','1','4',11);
insert into nota values('0001','05','3','4',11);
insert into nota values('0002','05','3','4',12);
insert into nota values('0003','05','3','4',12);
insert into nota values('0004','05','3','4',13);
insert into nota values('0005','05','3','4',14);
insert into nota values('0006','05','3','4',16);
insert into nota values('0007','05','3','4',17);
insert into nota values('0008','05','3','4',18);
insert into nota values('0009','05','3','4',19);
insert into nota values('0010','05','3','4',13);
insert into nota values('0011','05','3','4',11);
insert into nota values('0012','05','3','4',16);

-- Contestar las siguientes preguntas
--1.- El numero de alumnos desaprobados en la primera unidad por curso
--2.- El numero de alumnos aprobados por unidad y ciclo
--3.- El promedio de notas por curso de cada alumno en el grado 1
--4.- La nota máxima por curso
--5.- La nota máxima por unidad.
--6.- El promedio de notas por sexo
--7.- El promedio de notas por alumno indicar sólo los aprobados
--8.- El promedio de notas por unidad
--9.- La nota máxima por sexo.
--10..El numero de alumnos cuyo promedio de notas sea mayor que 15

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