• Author / Uploaded
• Javier Willian Huamán Huayllani

• Categories
• SQL
• Software de la aplicacion
• Informática
• Tecnología
• Software

Citation preview

CURSO: SISTEMA DE BASE DE DATOS I ALUMNA: Ttito Ugarte, Lesly Shessira CODIGO: 110287 TAREA DE LABORATORIO EJERCICIOS COMPLEMENTARIOS 1.- Número de alumnos matriculados por semestre, y por carrera profesional SELECT Semestre, Cod_CP, COUNT(Cod_Alumno) AS NroAlumnos FROM Matricula GROUP BY Semestre,Cod_CP

2.-Relacion de alumnos con su respectivo número de créditos acumulados ---Se supone que para acumular créditos se necesita aprobar el curso, por lo tanto seleccionamos las tuplas con notas aprobadas ---#TABLA1: relación de tuplas con notas aprobadas #TABLA1(Semestre, Cod_Asignatura, Cod_Alumno) SELECT Semestre, Cod_Asignatura, Cod_Alumno INTO #TABLA1 FROM Matricula WHERE Nota in ('11','12','13','14','15','16','17','18','19','20') -----Aplicamos JOIN a #TABLA1 y Asignatura, luego sumamos los créditos de cada alumno R(Cod_ALumno, Total_Creditos) SELECT Cod_Alumno, SUM(Creditos) AS Total_Creditos FROM #TABLA1 M INNER JOIN Asignatura A ON M.Cod_Asignatura = A.Cod_Asignatura GROUP BY Cod_Alumno

……

3.- Relación de alumnos que hayan aprobado todas sus asignaturas en algún semestre ---Primero cuento cuantos cursos llevaron cada alumno en un semestre #TOTAL_ASIGNATURAS(Semestre, Cod_Alumno, TotalAsignaturas) y luego lo intersectamos con el número de cursos aprobados en cada semestre SELECT Semestre, Cod_Alumno, COUNT(Cod_Asignatura) AS TotalAsignaturas INTO #TOTAL_ASIGNATURAS FROM Matricula GROUP BY Semestre, Cod_Alumno INTERSECT SELECT Semestre, Cod_Alumno, COUNT(Cod_Asignatura) AS TotalAsignaturas FROM Matricula WHERE Nota IN ('11','12','13','14','15','16','17','18','19','20') GROUP BY Semestre,Cod_Alumno ---Relación de alumnos que aprobaron todos sus cursos en algún semestre R(Cod_Alumno, Paterno, Materno, Cod_CP) SELECT A.Cod_Alumno,Paterno,Materno, Nombres, Cod_CP FROM Alumno A INNER JOIN #TOTAL_ASIGNATURAS TA ON A.Cod_Alumno = TA.Cod_Alumno

4.- Relación de alumnos que hayan aprobado todas sus asignaturas en todos los semestres ---Primero cuento cuantos cursos llevaron cada alumno en un semestre #TOTAL_ASIGNATURAS(Semestre, Cod_Alumno, TotalAsignaturas) y luego lo intersectamos con el número de cursos aprobados en cada semestre. SELECT Semestre, Cod_Alumno, COUNT(Cod_Asignatura) AS TotalAsignaturas INTO #TOTAL_ASIGNATURAS FROM Matricula GROUP BY Semestre, Cod_Alumno INTERSECT SELECT Semestre, Cod_Alumno, COUNT(Cod_Asignatura) AS TotalAsignaturas FROM Matricula WHERE Nota IN ('11','12','13','14','15','16','17','18','19','20') GROUP BY Semestre,Cod_Alumno ---Intersecto el Numero_semestres con el Numero de semestres invictos de cada alumno SELECT Cod_Alumno, COUNT(Semestre) AS NroSemestres INTO #NUMERO_SEMESTRES FROM Matricula GROUP BY Cod_Alumno INTERSECT SELECT Cod_Alumno, COUNT(Semestre) AS NroSemestres FROM #TOTAL_ASIGNATURAS GROUP BY Cod_Alumno ---Relacion de alumnos que aprobaron todos sus cursos en todos los semestres SELECT A.Cod_Alumno,Paterno,Materno, Nombres, Cod_CP INTO #ALUMNOS_INVICTOS /*ALUMNOS INVICTOS EN TODOS SUS SEMESTRE*/ FROM Alumno A INNER JOIN #NUMERO_SEMESTRES NS ON A.Cod_Alumno = NS.Cod_Alumno

5.-Relacion de alumnos que hayan obtenido promedio ponderado en todos los semestres ---Con la relación #ALUMNOS_INVICTOS del anterior ejercicio aplicamos join para recuperar sus notas SELECT Semestre, M.Cod_Alumno, Cod_Asignatura, M.Cod_CP, Nota INTO #NOTAS_ALUMNOS_INVICTOS FROM #ALUMNOS_INVICTOS AI inner join Matricula M on AI.Cod_Alumno = M.Cod_Alumno ---Calculamos el promedio de los alumnos SELECT NAI.Cod_Alumno,Semestre,(SUM(CAST(NAI.Nota as integer)*A.Creditos)/SUM(Creditos)) as Promedio INTO #ALUMNOS_PROMEDIO/*Alumnos que Aprobados todas sus asignaturas en todos los semestres*/ FROM #NOTAS_ALUMNOS_INVICTOS NAI, Asignatura A WHERE (NAI.Cod_Asignatura = A.Cod_Asignatura) GROUP BY NAI.Cod_Alumno,Semestre ---Seleccionamos a los que están ponderando SELECT * INTO #ALUMNOS_PONDERADOS /*Alumnos que ponderaron todos los semestres*/ FROM #ALUMNOS_INVICTOS WHERE Cod_Alumno not in (SELECT Distinct Cod_Alumno, FROM #ALUMNOS_PROMEDIO WHERE Promedio < 13)

6.- Los Mejores estudiantes de cada Carrera son los estudiantes que hayan obtenido el promedio ponderado más alto en los dos últimos semestres. Determinar los mejores estudiantes en cada carrera profesional ---Primeramente seleccionamos los dos últimas semestres SELECT TOP 2 Semestre INTO #ULTIMOS_SEMESTRES FROM Matricula GROUP BY Semestre ORDER BY Semestre Desc --- Del ejercicio 5 se tiene los alumnos que invictos en todos los semestres entonces, SELECT AP.Cod_Alumno, M.Cod_CP, AP.Promedio FROM #ALUMNOS_PROMEDIO AP inner join Matricula M ON (AP.Cod_Alumno = M.Cod_Alumno), #ULTIMOS_SEMESTRES US WHERE M.Semestre = US.Semestre GROUP BY AP.Cod_Alumno, M.Cod_CP, AP.Promedio HAVING (MAX(CAST(AP.Promedio AS integer))> 13)

7.- Determinar los tres mejores estudiantes de la universidad. ---Del ejercicio 5 recuperamos la relación #ALUMNOS_PROMEDIO (Alumnos que Aprobados todas sus asignaturas en todos los semestre) SELECT TOP 3 FROM #ALUMNOS_PROMEDIO ORDER BY Promedio Desc

8.- Determinar las tres carreras profesionales con menor número de ingresantes en el último año ---Dos ultimos semestres DROP TABLE #ULTIMOS_SEMESTRES SELECT TOP 2 Semestre INTO #ULTIMOS_SEMESTRES FROM Matricula GROUP BY Semestre ORDER BY Semestre Desc ---Alumnos de los dos ultimos semestres, ojo solo cachimbos SELECT TOP 3 Cod_CP, COUNT(Cod_Alumno) AS Nro_Alumnos FROM Matricula M,#ULTIMOS_SEMESTRES US WHERE (M.Semestre = US.Semestre) and Cod_Alumno not in ( SELECT Distinct Cod_Alumno /*Alumnos Regulares*/ FROM Matricula WHERE Semestre in (SELECT Distinct Semestre /*semestres sin consider el ultimo año*/ from Matricula Group by semestre EXCEPT SELECT * FROM #ULTIMOS_SEMESTRES)) GROUP BY Cod_CP ORDER BY Nro_Alumnos Asc

9.- Determinar las asignaturas con mas del 70% de desaprobados en el ultimo año ---Dos ultmos semestres SELECT TOP 2 Semestre INTO #ULTIMOS_SEMESTRES FROM Matricula GROUP BY Semestre ORDER BY Semestre Desc ---Seleccionamos solo matriculas del los dos ultimos semestres SELECT Cod_Asignatura, COUNT(Cod_Alumno) as Total_Alumnos_Curso INTO #TOTAL_ALUMNOS_ASIGNATURA FROM Matricula M, #ULTIMOS_SEMESTRES US WHERE M.Semestre = US.Semestre GROUP BY Cod_Asignatura ---Seleccionamos los alumnos que reprobaron SELECT Cod_Asignatura, COUNT(Cod_Alumno) as Nro_Alumnos_Reprobados INTO #TOTAL_REPROBADOS FROM Matricula M, #ULTIMOS_SEMESTRES US WHERE M.Semestre = US.Semestre and Nota not in ('11','12','13','14','15','16','17','18','19','20') GROUP BY Cod_Asignatura ---Asignaturas con su respectivo porcentaje de reprobados mayor al 70% SELECT TAA.Cod_Asignatura,A.Nombre_Asignatura, ((Nro_Alumnos_Reprobados *100)/Total_Alumnos_Curso) as Porcentaje_Reprobados FROM #TOTAL_ALUMNOS_ASIGNATURA TAA,#TOTAL_REPROBADOS TR,Asignatura A WHERE TAA.Cod_Asignatura = TR.Cod_Asignatura and TR.Cod_Asignatura= A.Cod_Asignatura AND ((Nro_Alumnos_Reprobados *100)/Total_Alumnos_Curso) > 7

10.- Determinar la relación de alumnos de Informática que hayan desaprobado o reprobado más de tres veces alguna materia ---Primero aprupamos en las matriculas SELECT Cod_Alumno,Cod_Asignatura, COUNT( Cod_Asignatura) AS Nro_Asig_Reprobadas FROM Matricula WHERE Nota not in ('11','12','13','14','15','16','17','18','19','20') GROUP BY Cod_Alumno,Cod_Asignatura HAVING COUNT( Cod_Asignatura) >=3

11.- Determinar la relación de estudiantes que hayan obtenido promedio ponderado en el último semestre ---El ultimo semestre SELECT TOP 1 Semestre INTO #ULTIMOS_SEMESTRE FROM Matricula GROUP BY Semestre ORDER BY Semestre Desc --- De la relacion #ALUMNOS_PONDERADOS son los alumnos ponderados en todos los semestres SELECT A.Cod_Alumno, Paterno,Materno,Nombres, Promedio FROM #ALUMNOS_PROMEDIO AP, #ULTIMOS_SEMESTRE US, Alumno A WHERE AP.Semestre = US.Semestre AND AP.Cod_Alumno = A.Cod_Alumno AND Promedio > 13

12.- Determinar la relacion de estudiantes que en su último semestre hayan aprobado menos que en su penultimo semestre ---Seleccionamos Alumnos de los dos ultimos semestres SELECT US.Semestre, A.Cod_Asignatura, Cod_Alumno, Nota, Creditos INTO #MATRICULAS_DOS_ULTIMOS_SEMESTRES FROM #ULTIMOS_SEMESTRES US, Matricula M, Asignatura A WHERE US.Semestre = M.Semestre AND M.Cod_Asignatura = A.Cod_Asignatura SELECT TOP 1 Semestre INTO #ULT_SEMESTRE FROM #ULTIMOS_SEMESTRE SELECT TOP 1 Semestre INTO #PENULTIMO_SEMESTRE FROM #ULTIMOS_SEMESTRES ORDER by Semestre ASC --- Creditos ultimo semestre SELECT Cod_Alumno, SUM(Creditos) as Creditos_Ult_Semestre INTO #PROMEDIO_ULTIMOS_SEMESTRE FROM #MATRICULAS_DOS_ULTIMOS_SEMESTRES MDUS, #ULTIMOS_SEMESTRE US WHERE Nota IN ('11','12','13','14','15','16','17','18','19','20') AND MDUS.Semestre = US.Semestre GROUP BY US.Semestre, Cod_Alumno --- Creditos penultimo semestre SELECT Cod_Alumno, SUM(Creditos) as Creditos_Penult_Semestre INTO #PROMEDIO_PENULTIMO_SEMESTRE FROM #MATRICULAS_DOS_ULTIMOS_SEMESTRES MDUS, #PENULTIMO_SEMESTRE PS

WHERE Nota IN

('11','12','13','14','15','16','17','18','19','20') AND MDUS.Semestre = PS.Semestre GROUP BY PS.Semestre, Cod_Alumno --- Alumnos que arobaron menos créditos que el penultim semestre SELECT PPS.Cod_Alumno, Paterno, Materno, Nombres, Cod_CP FROM #PROMEDIO_ULTIMOS_SEMESTRE PUS, #PROMEDIO_PENULTIMO_SEMESTRE PPS, Alumno A WHERE PUS.Cod_Alumno = PPS.Cod_Alumno AND (PUS.Creditos_Ult_Semestre - PPS.Creditos_Penult_Semestre)>0 AND PUS.Cod_Alumno = A.Cod_Alumno

13.-Determinar el rendimiento de cada carrera profesional, es decir, calcular el promedio aritmético de cada carrera en función de los promedios aritméticos de sus respectivos alumnos.

-- de los promedios aritmeticos de sus alumnos SELECT Semestre, Cod_CP, Cod_Alumno, AVG(case when Nota = 'NSP' then 0 else cast (Nota as int) end) as Promedio_Semestre INTO #PROMEDIO_SEMESTRAL FROM Matricula GROUP BY Semestre,Cod_CP,Cod_Alumno ---sacando promedios de alumnos SELECT Cod_CP, Cod_Alumno, AVG(Promedio_Semestre) AS PROMEDIO INTO #PROMEDIO FROM #PROMEDIO_SEMESTRAL GROUP BY Cod_CP, Cod_Alumno ---Rendimiento de cada carrera profesional SELECT Cod_CP,AVG(PROMEDIO) AS Rendimiento_Carrera_Profesional FROM #PROMEDIO GROUP BY Cod_CP

14.- Relacion de numeros de ingresantes por carrera profesional en los ultimos 5 años ---R(Cod_CP,NomCP, A1996,A1997,A1998,A1999,A2000) ---Primero identifico de que codigo son BEGIN SELECT Cod_Alumno,Cod_CP, CAST(Cod_Alumno AS INTEGER)/10000 AS CODIGO INTO #R_CODIGO FROM Matricula GROUP BY Cod_Alumno,Cod_CP SELECT CP.Cod_CP,Nombre_CP,CODIGO, COUNT (RC.Cod_CP) as Numero_Ingresantes INTO #TOTAL_POR_CARRERA FROM #R_CODIGO RC, Carrera_Profesional CP WHERE RC.Cod_CP = CP.Cod_CP group by CP.Cod_CP,Nombre_CP, CODIGO

SELECT Cod_CP, Nombre_CP, "A1998" = CASE WHEN CODIGO = 98 THEN Numero_Ingresantes ELSE 0 END, "A1999" = CASE WHEN CODIGO = 99 THEN Numero_Ingresantes ELSE 0 END, "A2000" = CASE WHEN CODIGO = 0 THEN Numero_Ingresantes ELSE 0 END FROM #TOTAL_POR_CARRERA GROUP BY Cod_CP, Nombre_CP END; EJERCICIO EXTRA PARA 2 PUNTOS : 15.- DETERMINAR LOS TRES MEJORES ESTUDIANTES DE CADA CARRERA PROFESIONAL. (MEJORES PROMEDIOS E INVICTOS) EN EL ULTIMO SEMESTRE. --Primero determinamos el último semestre SELECT MAX(Semestre) AS Ultimo_Semestre INTO #TABLA1 FROM Matricula --Determinamos la relación de alumnos invictos -- Para esto determinamos la relación de alumnos matriculados en el ultimo semestre SELECT DISTINC A.* INTO #TABLA2 FROM Matricula A INNER JOIN #TABLA1 B ON A.Semestre = B.Semestre -- Determinamos cuantos cursos llevo cada alumno en el último semestre y lo intersectamos con el número de cursos aprobados en cada semestre asi obtendremos la relación de alumnos invictos. SELECT Cod_Alumno, COUNT(Cod_Asignatura) AS TotalAsignaturas INTO #TOTAL_ASIGNATURAS FROM #TABLA2 GROUP BY Cod_Alumno INTERSECT SELECT Cod_Alumno, COUNT(Cod_Asignatura) AS TotalAsignaturas FROM #TABLA2 WHERE Nota IN ('11','12','13','14','15','16','17','18','19','20') GROUP BY Cod_Alumno --Calculamos el promedio de cada alumno invicto SELECT Cod_Alumno, SUM(case when Nota = 'NSP' then 0 else cast (Nota as int) end)/ TotalAsignaturas AS Promedio INTO #ALUMNOS_PROMEDIO FROM #TOTAL_ASIGNATURAS --Determinamos los tres promedios más altos de cada carrera profesional SELECT TOP 3 FROM #ALUMNOS_PROMEDIO ORDER BY Promedio, Cod_CP Desc

Comando TOP (Transact-SQL) La sentencia SQL TOP se utiliza para especificar el número de filas a mostrar en el resultado. Esta cláusula SQL TOP es útil en tablas con muchos registros, para limitar el número de filas a mostrar en la consulta, y así sea más rápida la consulta, consumiendo también menos recursos en el sistema. Esta cláusula se especifica de forma diferente según el sistema de bases de datos utilizado. Cláusula SQL TOP para SQL SERVER SELECT TOP número PERCENT nombre_columna FROM nombre_tabla Cuando se usa TOP junto con la cláusula ORDER BY, el conjunto de resultados se limita al primer número N de filas ordenadas; de lo contrario, devuelve el primer número N de filas en un orden sin definir. Utilice esta cláusula para especificar el número de filas devueltas de una instrucción SELECT o afectadas por una instrucción INSERT, UPDATE, MERGE o DELETE. En una instrucción SELECT, utilice siempre una cláusula ORDER BY con la cláusula TOP. Esta es la única manera de indicar previsiblemente a qué filas afecta TOP. Utilice OFFSET y FETCH en la cláusula ORDER BY en lugar de la cláusula TOP para implementar una solución de paginación de consulta. Una solución de paginación (es decir, el envío de fragmentos o "páginas" de datos al cliente) es más fácil de implementar mediante OFFSET y FETCH. Para obtener más información, Utilice TOP (o bien, OFFSET y FETCH) en lugar de SET ROWCOUNT para limitar el número de filas devueltas. Estos métodos son preferibles a utilizar SET ROWCOUNT por las siguientes razones: 

Como parte de una instrucción SELECT, el optimizador de consultas puede considerar el valor de expression en las cláusulas TOP o FETCH durante la optimización de la consulta. Dado que SET ROWCOUNT se usa fuera de una instrucción que ejecuta una consulta, su valor no se puede considerar en un plan de consulta.