RESTAURAR LA BASE DE DATOS MATRICULA2.SQL Nota: tener en cuenta todos los comandos vistos hasta el momento. Debe dise?ar el modelo entidad relaci?n y el modelo relacional Realizar el an?lisis en cada uno de los putos (vale el 50% de cada punto) VISUALIZAR CADA TABLA CON SU INFORMACI? ALUMNO +-----------+------------------+-----------+----------+ | codigoest | nombre | direccion | telefono | +-----------+------------------+-----------+----------+ | 001 | Juan Tobon | Cra 59 | 2335698 | | 002 | Mario Gonzales | Cra 89 | 6325984 | | 003 | Federico Aguilar | Cra 26 | 4569782 | | 004 | Angel Cuadrado | Cra 44 | 6398521 | | 005 | Catalina Escobar | Cra 78 | 4652300 | | 006 | Paulina Borja | Cra 45 | 4599632 | +-----------+------------------+-----------+----------+ CARRERA +---------+-------------------------+ | codcarr | descripcion | +---------+-------------------------+ | 001 | Ingenieria de Sistemas | | 002 | Contaduria | | 003 | Economia | | 004 | Derecho | | 005 | Ingenieria Agropecuaria | | 006 | Agronomia | | 007 | Ciencias de la salud | | 008 | Veterinaria | +---------+-------------------------+ PROFESOR +---------+----------------------+-----------+----------+ | codprof | nombre | direccion | telefono | +---------+----------------------+-----------+----------+ | 001 | Pablo Juan Gutierrez | cra 45-96 | 2569856 | | 002 | Enrique Saltamontes | cra 25-63 | 2365914 | | 003 | Portacio Cartagena | cra 36-01 | 4596321 | | 004 | Federico Aguilar | cra 56-41 | 7895624 | | 005 | Alberto Cifuentes | cra 20-30 | 7895002 | | 006 | Pascual Bravo | cra 56-41 | 5698741 | +---------+----------------------+-----------+----------+ ALUMCAR +-----------+---------+ | codigoest | codcarr | +-----------+---------+ | 001 | 002 | | 003 | 005 | | 005 | 001 | | 001 | 005 | +-----------+---------+ MATRICULA +-----------+-----------+---------+---------+---------------+ | codmatric | codigoest | codcarr | codprof | valorsemestre | +-----------+-----------+---------+---------+---------------+ | 001 | 003 | 004 | 004 | 1800000 | | 002 | 001 | 008 | 003 | 3500000 | | 003 | 004 | 007 | 006 | 2800000 | | 004 | 002 | 007 | 006 | 1950000 | | 005 | 005 | 004 | 001 | 1800000 | | 006 | 003 | 008 | 003 | 3500000 | +-----------+-----------+---------+---------+---------------+ PROFCAR +---------+---------+ | codprof | codcarr | +---------+---------+ | 005 | 003 | | 002 | 006 | | 005 | 005 | | 003 | 008 | | 005 | 001 | | 004 | 002 | | 003 | 001 | | 004 | 004 | | 001 | 004 | | 006 | 007 | +---------+---------+ 1.Que profesores dictan en la carrera de ingenier?a de sistemas. Analisis: *.Que deseamos mostrar Profcar.codprof, profesor.nombre ,profcar.codcarr carrera.descripcion *.Tablas involucradas(profesor, profcar,carrera) *.Condiciones en particular(carrera=’ingenieria de sistemas’ *.como se relaciona las tablas (profcar.codprof=profesor.codprof, profcar.codcarr=carrera.codcarr mysql> select profcar.codprofe, profesor.nombre,profcar.codcarre, carrera.carrera from profcar, prof esor,carrera where carrera.carrera='ingenieria de sistemas' and profcar.codprofe=profesor.codprofe a nd profcar.codcarre=carrera.codcarre; +----------+--------------------+----------+------------------------+ | codprofe | nombre | codcarre | carrera | +----------+--------------------+----------+------------------------+ | 003 | Portacio Cartagena | 001 | Ingenieria de Sistemas | | 005 | Alberto Cifuentes | 001 | Ingenieria de Sistemas | +----------+--------------------+----------+------------------------+ 2 rows in set (0.00 sec) mysql> 2. Que alumnos estan matriculados en la carrera de Derecho y cuales profesores dictan en esa carrera. Analisis *.Que deseamos mostrar Alumno.nombre, alumcar.codestu, matricula.codmatri, carrera.carrera,profcar=codcarre profesor.nombre, profcar.codprofe *.Tablas involucradas(alumno,profesor, profcar,carrera, matricula,alumcar) *.Condiciones en particular(carrera=’derecho’) *.como se relaciona las tablas (alumno.codestu=matricula.codestu, profesores.codprofe=profcar.codprofe, profcar.codcarre=carrera.codcarre, alumcar.codestu=alumno.codestu mysql> select distinct alumcar.codestu, alumno.nombre, matricula.codmatri,profcar.codcarre, carrera. carrera,profcar.codprofe,profesor.nombre from alumno,alumcar,matricula,carrera,profcar,profesor wher e carrera='Derecho'and alumno.codestu=alumcar.codestu and alumno.codestu=matricula.codestu and profe sor.codprofe=profcar.codprofe and profcar.codcarre=carrera.codcarre and alumno.codestu=alumcar.codes tu; +---------+------------------+----------+----------+---------+----------+----------------------+ | codestu | nombre | codmatri | codcarre | carrera | codprofe | nombre | +---------+------------------+----------+----------+---------+----------+----------------------+ | 001 | Juan Tobon | 002 | 004 | Derecho | 004 | Federico Aguilar | | 003 | Federico Aguilar | 001 | 004 | Derecho | 004 | Federico Aguilar | | 003 | Federico Aguilar | 006 | 004 | Derecho | 004 | Federico Aguilar | | 005 | Catalina Escobar | 005 | 004 | Derecho | 004 | Federico Aguilar | | 001 | Juan Tobon | 002 | 004 | Derecho | 001 | Pablo Juan Gutierrez | | 003 | Federico Aguilar | 001 | 004 | Derecho | 001 | Pablo Juan Gutierrez | | 003 | Federico Aguilar | 006 | 004 | Derecho | 001 | Pablo Juan Gutierrez | | 005 | Catalina Escobar | 005 | 004 | Derecho | 001 | Pablo Juan Gutierrez | +---------+------------------+----------+----------+---------+----------+----------------------+ 8 rows in set (0.00 sec) mysql> 3. Cu?nto les cost? el semestre de los alumnos que se matricularon en Veterinaria. *.Que deseamos mostrar (alumcar.codestu, alumno.nombre,alumcar.codcarre, carrera.carrera,matricula.valorsemestre, matricula.codmatri) *.Tablas involucradas(matricula,alumno,alumcar,carrera) *.Condiciones en particular(carrera=’veterinaria) *.como se relaciona las tablas (alumcar.codestu=alumno.codestu and matricula.codestu=alumno.codestu) mysql> select distinct alumcar.codestu, alumno.nombre,alumcar.codcarre, carrera.carrera,matricula.valorsemestre, matricula.codmatri from matricula,alumno,alumcar,carrera where carrera.carrera='Veterinaria' and alumcar.codestu=alumno.codestu and matricula.codestu=alumno.codestu; +---------+------------------+----------+-------------+---------------+----------+ | codestu | nombre | codcarre | carrera | valorsemestre | codmatri | +---------+------------------+----------+-------------+---------------+----------+ | 001 | Juan Tobon | 002 | Veterinaria | 3500000 | 002 | | 003 | Federico Aguilar | 005 | Veterinaria | 1800000 | 001 | | 003 | Federico Aguilar | 005 | Veterinaria | 3500000 | 006 | | 005 | Catalina Escobar | 001 | Veterinaria | 1800000 | 005 | | 001 | Juan Tobon | 005 | Veterinaria | 3500000 | 002 | +---------+------------------+----------+-------------+---------------+----------+ 5 rows in set (0.00 sec) 4. A que alumnos les tocar?a ver clases con el profesor Portacio Cartagena. *.Que deseamos mostrara alumcar.codestu,alumno.nombre , profcar.codprofe,profesor.nombre, alumcar.codcarre,carrera.carrera *.Tablas involucradas(alumno,profesor,carrera,alumcar ,profcar) *.Condiciones en particular(profesor=’Portacio Cartagena’ *.como se relaciona las tablas Alumcar.codestu=alumno.codestu , profcar.codprofe=profesor.codprofe , carrera.codcarre=alumcar.codcarre mysql> select distinct alumcar.codestu, alumno.nombre, profcar.codprofe,profesor.nombre, alumcar.codcarre, carrera.carrera from alumno,carrera, profesor, alumcar,profcar where profesor.nombre='Portacio Cartagena' and alumcar.codestu=alumno.codestu and profcar.codprofe=profesor.codprofe and alumcar.c odcarre=carrera.codcarre and carrera.codcarre=alumcar.codcarre; +---------+------------------+----------+--------------------+----------+-------------------------+ | codestu | nombre | codprofe | nombre | codcarre | carrera | +---------+------------------+----------+--------------------+----------+-------------------------+ | 001 | Juan Tobon | 003 | Portacio Cartagena | 002 | Contaduria | | 003 | Federico Aguilar | 003 | Portacio Cartagena | 005 | Ingenieria Agropecuaria | | 005 | Catalina Escobar | 003 | Portacio Cartagena | 001 | Ingenieria de Sistemas | | 001 | Juan Tobon | 003 | Portacio Cartagena | 005 | Ingenieria Agropecuaria | +---------+------------------+----------+--------------------+----------+-------------------------+ 4 rows in set (0.00 sec) 5. cu?l es el valor del semestre m?s alto. *.Que deseamos mostrar (valor semestre) *.Tablas involucradas(matricula) *.Condiciones en particular(mas alto) mysql> select max(valorsemestre) from matricula; | max(valorsemestre) | +--------------------+ | 3500000 | +--------------------+ 1 row in set (0.00 sec) 6. Cu?l es el promedio del valor de semestre que ha entrado a la cartera de la universidad. *.Que deseamos mostrar (Avg.valorsemestre) *.Tablas involucradas(matricula) *.Condiciones en particular(en la universidad) mysql> select avg(valorsemestre) from matricula; +--------------------+ | avg(valorsemestre) | +--------------------+ | 2558333.3333333335 | +--------------------+ 1 row in set (0.00 sec) mysql> 7. Mostrar los nombres de los estudiantes que comiencen por A o que terminen en R. *.Que deseamos mostrar (alumno.nombre) *.Tablas involucradas(alumno) *.Condiciones en particular(que comiencen por la a y terminen en laR mysql> select * from alumno where nombre like 'a%' or nombre like '%R'; +---------+------------------+-----------+----------+ | codestu | nombre | direccion | telefono | +---------+------------------+-----------+----------+ | 003 | Federico Aguilar | Cra 26 | 4569782 | | 004 | Angel Cuadrado | Cra 44 | 6398521 | | 005 | Catalina Escobar | Cra 78 | 4652300 | +---------+------------------+-----------+----------+ 3 rows in set (0.00 sec) 8. Total valorsemestre pagado por carrera; *.Que deseamos mostrar Total valor (sum) *.Tablas involucradas(matricula) *.Condiciones en particular(pagado por carrera) mysql> select codcarre, sum(valorsemestre)totalvalor from matricula group by codcarre; +----------+------------+ | codcarre | totalvalor | +----------+------------+ | 004 | 3600000 | | 007 | 4750000 | | 008 | 7000000 | +----------+------------+ 3 rows in set (0.00 sec) 9. Visualizar los estudiantes que no estan matriculados. *.Que deseamos mostrar Matricula.codmatricula, alumno.nombre *.Tablas involucradas(matricula, alumno) *.Condiciones en particular(alumnos que no esten matriculados) *.como se relaciona las tablas(matricula.codestu=alumno.codestu) mysql> select * from alumno left join matricula on matricula.codestu=alumno.codestu where matricula.codmatri is null; | codestu | nombre | direccion | telefono | codmatri | codestu | codcarre | codprofe | valorsemestre | +---------+---------------+-----------+----------+----------+---------+----------+----------+---------------+ | 006 | Paulina Borja | Cra 45 | 4599632 | NULL | NULL | NULL | NULL | NULL | +---------+---------------+-----------+----------+----------+---------+----------+----------+---------------+ 1 row in set (0.00 sec) Nota: debe entregar en una carpeta con su nombre: modelo entidad relaci?n copia de seguridad de la base de datos. En el blog de notas los caomando que utiliz? para dar soluci?n al parcial con sus respectivos resultados