changed 3 months ago
Linked with GitHub

PROYECTO FORMATIVO

ORCE-UNI

Integrantes:

  • Aldo Luna
  • José Joaquin Tello Leon
  • Pablo A. Quispe Olaechea
  1. Crea la base de datos orce_uni. A continuación conectarse a la orce_uni.

    ​​​CREATE DATABASE orce_uni;
    
  2. Crea las tablas para la orce_uni:

    ​​​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

    ​​​   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');
    
    
    
    
  4. 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';
​​​​```
  1. 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
​​​​
​​​​```
  1. Mostrar la facultad , nombre, especialidad de aquellos estudiantes que no tengan un curso registrado(los que postergaron matricula).
    ​​​​
    
  2. Averiguar el codigo de estudiante, nombre, especialidad de estudiantes que tengan un curso matriculado.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  3. Listar aquellos profesores que tiene calificacion destacada (su promedio va de 15 años a 20 puntos)
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  4. Desplegar aquellos estudiantes cuyo nombre comience con A
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  5. Listar aquellos estudiantes cuyo nombre NO comience con A
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  6. 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.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  7. Inserte al profesor CARLOS DIAZ DOMINGUEZ, de promedio 15, de la facultad de ciencias(FC) en la especialidad de Matematicas(N6).
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  8. Actualice su promedio a 16 años y su especialidad a Ciencias de la Computacion.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  9. Inserte al estudiante PABLO QUISPE OLAECHEA 20194153G, de ciencias de la computacion FC N6.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  10. Actualice su nombre a ALEJANDRO.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  11. Borre todas las tuplas de la tabla Info_prev.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  12. Proceda a eliminar la tabla Info_prev.¿Qué comando utilizó?.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  13. Liste todos los datos de los estudiantes cuya especialidad es Fisica o Quimica. Presente dos soluciones.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  14. Utiliza una instrucción que retorne la cantidad de Estudiantes.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  15. Mostrar el promedio de los promedios de los estudiantes registrados.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  16. Mostrar la edad promedio de los estudiantes matriculados en almenos un curso.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  17. Mostrar el estudiante de mayor promedio.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  18. Mostrar el estudiante en el estudiante más cercano de egresar(ciclo_rel_aprob).
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  19. Mostrar cuantos cursos no tienen asignado un profesor.
    ​​​​ALTER TABLE Facultad ADD COLUMN cod_pa char(10);
    
  20. Elabore un reporte que presente la cantidad de estudiantes según su especialidad.
    ​​​​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
    ​​​​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
    ​​​​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
    ​​​​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.

    ​​​​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.

    ​​​​SELECT cod_pa
    ​​​​  FROM alumno
    ​​​​ WHERE ap_pat LIKE 'A%'
    
    ​​​​INTERSECT
    
    ​​​​SELECT cod_pa
    ​​​​  FROM alumno
    ​​​​ WHERE ap_pat LIKE 'C%';
    
  5. Diferencia

    ​​​​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» .

    ​​​​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

    ​​​​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.

    ​​​​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.

    ​​​​SELECT nom_p, ap_pa, ap_ma, cod_curso, nom_curso
    ​​​​  FROM Cursos
    ​​​​    RIGHT JOIN Profesores
    ​​​​    USING(cod_p)
    ​​​​ WHERE ap_pa LIKE 'A%';
Select a repo