owned this note
owned this note
Published
Linked with GitHub
# PROYECTO FORMATIVO
## ORCE-UNI
### Integrantes:
+ Aldo Luna Bueno
+ José Joaquin Tello Leon
+ Pablo A. Quispe Olaechea
1. Crea la base de datos ``orce_uni``. A continuación conectarse a la ``orce_uni``.
```sql
CREATE DATABASE orce_uni;
```
2. Crea las tablas para la ``orce_uni``:
```sql
CREATE TABLE Alumno(cod_a varchar(9),
nombre varchar(45),
ap_pat varchar(45),
ap_mat varchar(45),
cod_pa varchar(45));
CREATE TABLE Condicion_Academica(
promedio_a float,
situacion varchar(45),
egreso varchar(45),
ciclo_rel_aprob varchar(45),
ciclo_rel_matr varchar(45),
medida_disc varchar(45),
cod_a char(9));
CREATE TABLE Cursos(cod_curso varchar(10),
nom_curso varchar(45),
requisito varchar(45),
creditos integer,
cod_p char(10),
especialidad char(45));
CREATE TABLE Profesores(cod_p varchar(10),
nom_p varchar(45),
ap_pa varchar(45),
ap_ma varchar(45),
"e-mail" varchar(45),
cod_f varchar(45));
CREATE TABLE Programa_Academico(cod_pa varchar(45),
nombre_pa char(45),
cod_f varchar(45));
CREATE TABLE Datos_Personales(f_nac varchar(50),
genero varchar(50),
telefono varchar(50),
colegio_tipo varchar(50),
colegio_dep varchar(50),
lugar_nac varchar(50),
ingresos_econ integer,
cod_a varchar(9));
CREATE TABLE Matricula(cod_curso varchar(10),
cod_a varchar(9),
nota float);
CREATE TABLE Facultad(cod_f char(7),
nombre_f char(50),
sitio_web char(50),
decano char(50),
"e-mail" char(50));
CREATE TABLE Trabajador(cod_t varchar(45),
nom_t varchar(45),
ap_pa varchar(45),
ap_ma varchar(45),
function varchar(45),
telfono varchar(45),
cod_f varchar(45));
```
3. Proceda a insertar las tuplas en cada tabla
```sql
INSERT INTO Alumno VALUES
('20202131C', 'JOSE', 'TELLO', 'LEON', 'N6'),
('20194023F', 'CARLOS', 'ROJAS', 'FUSTAMANTE', 'N6'),
('20171016C', 'TATIANA', 'VILLALOBOS', 'RONCEROS', 'A1'),
('20181007G', 'VICTOR', 'ESPEJO', 'NUÑEZ', 'C1'),
('20190201G', 'JUAN', 'YNSIL', 'ALFARO', 'M3'),
('20215503A', 'DAVID', 'VALENCIA', 'CASTRO','N2'),
('20202580E', 'MIGUEL', 'GONZALES', 'PERLECHE', 'G3'),
('20181390H', 'JESUS', 'AGÜERO', 'VENTURA', 'N5'),
('20171287B', 'DIEGO', 'ALHUA', 'TORRES', 'M4'),
('20071249C', 'SEBASTIAN', 'RAMOS', 'CUYA', 'M5'),
('20122870B', 'GABRIEL', 'CONTRERA', 'RÍOS', 'M4'),
('20011249C', 'JUAN', 'MENDOZA', 'ESCUDERO', 'M5');
INSERT INTO Condicion_Academica VALUES
(14, 'REGULAR', NULL, '2021-1', '2021-2', 'NO', '20202131C'),
(16.9, 'REGULAR', NULL, '2020-2', '2021-2', 'SI', '20194023F'),
(18.036, 'REGULAR', NULL, '2021-1', '2021-2', 'NO', '20171016C'),
(17.448, 'REGULAR', NULL, '2021-1', '2021-2', 'NO', '20181007G'),
(18.293, 'REGULAR', NULL, '2021-1', '2021-2', 'NO', '20190201G'),
(15.9, 'REGULAR', NULL, '2020-2', '2021-2', 'SI', '20215503A'),
(13, 'REGULAR', NULL, '2020-1', '2021-2', 'NO', '20212580E'),
(18, 'REGULAR', NULL, '2021-1', '2021-2', 'NO', '20211390H'),
(13, 'REGULAR', NULL, '2021-1', '2021-2', 'SI', '20212580E'),
(18, 'REGULAR', NULL, '2019-2', '2021-2', 'NO', '20211390H'),
(14, 'EGRESADO', '2021-1', NULL, NULL, 'NO', '20122870B'),
(11, 'EGRESADO', '2021-1', NULL, NULL, 'SI', '20011249C');
INSERT INTO Datos_Personales VALUES
('26/01/2002', 'M', '923888925', 'publico', 'LIMA', 'LIMA', 3000, '20202131C'),
('17/09/1999', 'M', '924057888', 'publico', 'LIMA', 'ANCASH', 2500, '20194023F'),
('15/03/1997', 'F', '912345678', 'publico', 'LIMA', 'LIMA', 3000, '20171016C'),
('20/04/1998', 'M', '904635523', 'privado', 'LIMA', 'AREQUIPA', 4000, '20181007G'),
('02/04/2000', 'M', '910385637', 'privado', 'LIMA', 'ICA', 5000, '20190201G'),
('28/02/2001', 'M', '999488325', 'privado', 'LIMA', 'LIMA', 1000, '20215503A'),
('15/02/1997', 'M', '977348673', 'publico', 'AREQUIPA', 'LIMA', 1800, '20212580E'),
('25/03/1996', 'M', '985307740', 'publico', 'UCAYALI', 'LIMA', 1200, '20211390H'),
('15/04/2001', 'M', '947548993', 'privado', 'AREQUIPA', 'LIMA', 6000, '20211287B'),
('01/12/1999', 'M', '997388773', 'publico', 'LORETO', 'LIMA', 2000, '20211249C'),
('15/04/2001', 'M', '945528933', 'privado', 'LA LIBERTAD', 'LIMA', 6000, '20122870B'),
('01/12/1999', 'M', '997707753', 'publico', 'MADRE DE DIOS', 'LIMA', 800, '20011249C');
INSERT INTO Matricula VALUES
('CC202', '20202131C', 20),
('CQ221', '20202131C', 13),
('CQ221', '20202131C', 17),
('FA108', '20202131C', 15),
('EC712', '20202131C', 13),
('MC325', '20202131C', 15),
('CM142', '20215503A', 14),
('GE413', '20202580E', 19),
('IF311', '20181390H', 11),
('MB613', '20171287B', 12),
('MB613', '20071249C', 16);
INSERT INTO Cursos VALUES
( 'CC202','BASE DE DATOS', 'CC211', 4, '20008501C','N6'),
('CQ221', 'ELECTRICIDAD Y MAGNETISMO' ,'BMA02', 5, '06508249', 'N3'),
('FA108', 'TALLER DE DISEÑO 8' ,'FA107', 8, '19998061K', 'A1'),
('EC712', 'ANALISIS ESTRUCTURAL I' ,'EC121', 5, '19858016B', 'C1'),
('MC325', 'RESISTENCIA DE MATERIALES II' ,'MC324', 5, '19768007D', 'M3'),
('CM142', 'CALCULO VECTORIAL II', 'CM141', '5', '19938122C', 'N2'),
('GE413', 'CRISTALOGRAFIA', 'GE001', '3', ' 20078121E', 'G2'),
('IF311', 'CALCULO NUMERICO II', 'IF222', '4', '19898719D', 'N5'),
('MB613', 'ESTADISTICA Y PROBABILIDADES', 'MB147', '3', '19808719J', 'M4'),
('MN465', 'LABORATORIO DE INGENIERIA MECANICA', 'MN121', '1', '20008007I', 'M5');
INSERT INTO Profesores VALUES
('20008501C', 'VICTOR', 'MELCHOR', 'ESPINOZA', 'vmelchor@uni.edu.pe' ,'N'),
('19998061K', 'JULIA', 'BARRANTES', 'PEREZ', 'jbarrantes@uni.edu.pe','A'),
('19858016B', 'JAVIER', 'ARRIETA', 'FREYRE', 'jarrieta@uni.edu.pe','C'),
('19768007D', 'DUILIO', 'AGUILAR', 'VIZCARRA', 'daguilar@uni.edu.pe','M'),
('06508249', 'ROBERTO', 'SALAZAR', 'RODRIGUEZ', 'rsalazarr@uni.edu.pe','G'),
('19938122C', 'HENRI', 'CARVAJAL', 'CARRANZA', 'carvajalguy@outlook.com', 'N'),
('20078121E', 'ROBERTO', 'CELIS', 'ROSSI', 'ercelis@gmail.com', 'G'),
('19898719D', 'HERIBERTO', 'SÁNCHEZ', 'CÓRDOVA', 'hsanchez@uni.edu.pe', 'N'),
('19808719J', 'CARLOS', 'SOLANO', 'SALINAS', 'jsolano@uni.edu.pe', 'M'),
('20008007I', 'JORGE', 'AGUINAGA', 'MORENO', 'jalbertoamor@hotmail.com', 'M');
INSERT INTO Facultad VALUES
('N', 'FC', 'https://fc.uni.edu.pe/', 'Dr. PEDRO CANALES GARCIA', ' fc@uni.edu.pe'),
('A', 'FAUA', 'http://faua.uni.edu.pe/', 'Dra. SHIRLEY EMPERATRIZ CHILET CAMA', 'faua@uni.edu.pe'),
('C', 'FIC', 'http://fic.uni.edu.pe/', 'Dr. RAFAEL ROLANDO SALINAS BASUALDO', 'decano_fic@uni.edu.pe'),
('M', 'FIM', 'http://fim.uni.edu.pe/', 'Dr. ELMAR JAVIER FRANCO GONZALES', 'fim@uni.edu.pe'),
('G', 'FIGMM', 'http://fim.uni.edu.pe/', 'Dr. SANTIAGO GUALBERTO VALVERDE ESPINOZA', 'decano_figmm@uni.edu.pe');
INSERT INTO Programa_academico VALUES
('N6', 'Ciencias de la Computacion', 'N'),
('N5', 'Ingenieria Fisica', 'N'),
('N3', 'Quimica', 'N'),
('N2', 'Matematica', 'N'),
('A1', 'Arquitectura', 'A'),
('C1', 'Ingenieria Civil', 'C'),
('G3', 'Ingenieria de Minas', 'G'),
('M5', 'INGENIERIA NAVAL', 'M'),
('M4', 'INGENIERIA MECANICA Y ELECTRICA', 'M'),
('M3', 'Ingenieria Mecanica', 'M');
INSERT INTO Trabajador VALUES ('1', 'JUAN', 'RODRIGUEZ', 'OROZCO', 'ADMINISTRACION', '927461233','N'),
('2', 'LUCAS', 'CHAVEZ', 'PINTO', 'VIGILANCIA', '975836429','N'),
('3', 'EUGENIA', 'LOPUMO', 'VASQUEZ', 'ADMINISTRACION', '928375635','A'),
('4', 'JUAN', 'CALLE', 'RUIZ', 'VIGILANCIA', '928457003','A'),
('5', 'DENISSE', 'GUIZADO', 'OLIVARES', 'ADMINISTRACION', '902356382', 'C');
```
```
6. Revise la información de las tablas y luego proceda a crear el esquema para cada una de las tablas. ~~Tome como ejemplo el siguiente modelo.~~
#### Esquema de la base de datos *orce_uni*
| Atributos | Tipo | Longitud | Modificador |
| --------- | ------- | -------- | ----------- |
| CodHosp | Integer | | NOT NULL |
| NomHosp | VarChar | 40 | |
| Direccion | VarChar | 20 | |
##### Esquema de *estudiantes*
##### Esquema de *condicion_academica*
##### Esquema de *cursos*
##### Esquema de *profesores*
##### Esquema de *facultad*
##### Esquema de *info_prev*
##### Esquema de *programa_academico*
4. Obtener la Facultad y nombre de los estudiantes especialistas en Ingeniería Mecánica
```sql
SELECT nombre_f, e.nombre
FROM facultad AS f
INNER JOIN programa_academico AS pa
ON f.cod_f = pa.cod_f
INNER JOIN estudiantes AS e
ON e.especialidad = pa.cod_pa
AND pa.nombre_pa = 'Ingeniería Mecánica';
```
10. Averiguar el promedio total (promedio_a), nombre, especialidad y profesor de los estudiantes de la especialidad C3 (Ingeniería Civil).
> [Esta pregunta en realidad no tiene mucho sentido, ya que hay muchos profes por especialidad. Para hablar de un profesor relacionado con un alumno tendríamos que tener una tabla de matriculas. ¿La creamos? Yo creo que nos complicaríamos más y, además, no es una información propia de ORCE-UNI, sino de INTRANET-ALUMNOS]
```sql
```
11. Mostrar la facultad , nombre, especialidad de aquellos estudiantes que no tengan un curso registrado(los que postergaron matricula).
```sql
```
12. Averiguar el codigo de estudiante, nombre, especialidad de estudiantes que tengan un curso matriculado.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
13. Listar aquellos profesores que tiene calificacion destacada (su promedio va de 15 años a 20 puntos)
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
14. Desplegar aquellos estudiantes cuyo nombre comience con A
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
15. Listar aquellos estudiantes cuyo nombre NO comience con A
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
16. Mostrar el número de codigo de curso y especialidad para aquellos cursos ubicados en los pila facultad de Ingenieria civil y ciencias. Presente dos soluciones.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
17. Inserte al profesor CARLOS DIAZ DOMINGUEZ, de promedio 15, de la facultad de ciencias(FC) en la especialidad de Matematicas(N6).
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
18. Actualice su promedio a 16 años y su especialidad a Ciencias de la Computacion.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
19. Inserte al estudiante PABLO QUISPE OLAECHEA 20194153G, de ciencias de la computacion FC N6.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
20. Actualice su nombre a ALEJANDRO.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
21. Borre todas las tuplas de la tabla Info_prev.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
22. Proceda a eliminar la tabla Info_prev.¿Qué comando utilizó?.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
23. Liste todos los datos de los estudiantes cuya especialidad es Fisica o Quimica. Presente dos soluciones.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
24. Utiliza una instrucción que retorne la cantidad de Estudiantes.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
25. Mostrar el promedio de los promedios de los estudiantes registrados.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
26. Mostrar la edad promedio de los estudiantes matriculados en almenos un curso.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
27. Mostrar el estudiante de mayor promedio.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
28. Mostrar el estudiante en el estudiante más cercano de egresar(ciclo_rel_aprob).
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
29. Mostrar cuantos cursos no tienen asignado un profesor.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
30. Elabore un reporte que presente la cantidad de estudiantes según su especialidad.
```sql
ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
```
#### Consultas
1. Selección
Desplegar todos los datos de aquellos estudiantes cuyo nombre comience con D
##### Algebra relacional
$\sigma_{nombre\ empieza\ con\ "D"}(alumno)$
##### SQL
```sql
SELECT *
FROM alumno
WHERE nombre LIKE ‘D%’;
```
2. Proyección
Desplegar el código y el apellido paterno de los estudiantes.
##### Algebra relacional
$\pi_{cod\_a,\ ap\_pat}(alumno)$
##### SQL
```sql
SELECT cod_a, ap_pat
FROM alumno;
```
3. Unión
Muestre los alumnos que pertenezcan a la especialidad de Ciencias de la Computacion o no pertenezcan a la de Arquitectura
##### Algebra relacional
$\sigma_{cod\_pa="N6"}(alumno) \cup \sigma_{cod\_pa!="A1"}(alumno)$
##### SQL
```sql
SELECT *
FROM alumno
WHERE cod_pa = 'N6'
UNION
SELECT *
FROM alumno
WHERE cod_pa != 'A1';
```
Elabore un reporte que presente la cantidad de estudiantes según su especialidad.
```sql
SELECT pa.nombre_pa, COUNT(*)
FROM alumno AS a
INNER JOIN programa_academico AS pa
USING (cod_pa)
GROUP BY pa.nombre_pa;
```
4. Intersección
Mostrar los códigos de las especialidades en las que haya alumnos cuyo apellido empiece con A y otros con C.
```sql
SELECT cod_pa
FROM alumno
WHERE ap_pat LIKE 'A%'
INTERSECT
SELECT cod_pa
FROM alumno
WHERE ap_pat LIKE 'C%';
```
5. Diferencia
```sql
SELECT cod_pa, ap_pat
FROM alumno
WHERE cod_a LIKE '2020%'
INTERSECT
SELECT cod_pa, ap_pat
FROM alumno
WHERE cod_a NOT LIKE '%C';
```
6. División
Muestre los códigos de las especialidedes, dadas en la tabla temporal «anios_especialidad» a, a las que ingresaron alumnos en los años dados por la tabla temporal «anios_importantes» .
```sql
CREATE TEMP TABLE anios_importantes(
anio int
);
INSERT INTO anios_importantes VALUES (2019), (2020);
CREATE TEMP TABLE anios_especialidad AS
SELECT CAST(SUBSTR(cod_a, 1, 4) AS int) as anio, cod_pa
FROM alumno;
SELECT *
FROM anios_especialidad;
SELECT cod_pa
FROM anios_especialidad
WHERE anio IN (SELECT anio FROM anios_importantes)
GROUP BY cod_pa
HAVING COUNT(*) = (SELECT COUNT(*) FROM anios_importantes);
7. Producto Cartesiano
Muestre todas las posibles relaciones entre los alumnos y profesores de la misma facultad
```sql
SELECT cod_a, a.nombre, a.ap_pat, cod_p, profesores.nom_p, profesores.ap_pa
FROM (Alumno
JOIN Programa_academico USING(cod_pa)) as a
CROSS JOIN Profesores
WHERE a.cod_f = Profesores.cod_f;
```
8. Left join
Seleccione los los nombres todos los profesores junto a los codigos de los cursos que enseñan, aun si no tuvieran ninguno asignado actualmente.
```sql
SELECT nom_p, ap_pa, ap_ma, cod_curso, nom_curso
FROM Profesores
LEFT JOIN Cursos
USING(cod_p);
```
9. Right join
Seleccione codigos de cursos los junto a los nombres de todos los profesores que los enseñan y cuyo apellido paterno empiece con A, aun si no tuvieran ninguno asignado actualmente.
```sql
SELECT nom_p, ap_pa, ap_ma, cod_curso, nom_curso
FROM Cursos
RIGHT JOIN Profesores
USING(cod_p)
WHERE ap_pa LIKE 'A%';
