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