consultas en sql

Click here to load reader

Post on 24-Oct-2014

135 views

Category:

Documents

3 download

Embed Size (px)

TRANSCRIPT

UNIVERSIDAD AUSTRAL DE CHILE FACULTAD DE CIENCIAS ECONMICAS Y ADMINISTRATIVAS INSTITUTO DE ADMINISTRACIN

Sistemas de Informacin Empresarial

Sistemas de Informacin Empresarial Nombre: Valeria Muoz Ortiz

21 de mayo de 2012, Valdivia Actividades

Supuestos: La base de datos no guarda el histrico de los prstamos realizados, es decir, que al entregar un libro, ese prstamo se elimina de la tabla PRESTAMO.

1.- Se solicita realizar una consulta que entregue a los estudiantes atrasados en la entrega de sus prstamos de libros, e indicar la cantidad de das de atraso para cada prstamo. Los campos a mostrar son: Rut del estudiante, Nombres, Apellidos y el N de das de atraso. Ayuda: Usar la fecha del sistema denominada por SYSDATE, y usar la funcin TRUNC para quitar las horas, minutos y segundos de las fechas. select p.rut_est, e.nombres, e.apellidos, l.titulo, (trunc(sysdate) - trunc(p.fecha_e)) as ATRASO from prestamo p, estudiantes e, libros l where p.rut_est = e.rut_est and p.cod_libro = l.cod_libro and trunc(sysdate) > trunc(p.fecha_e)

239 Datos.

2.- Si cada da efectivo de atraso tiene un valor de $1250, entregue los mismo que en (1), pero agregando una nueva columna con la deuda adquirida por cada estudiante. Ayuda: Para multiplicar se usa el *, entonces si se desea multiplicar A por B, sera A*B. select p.rut_est, e.nombres, e.apellidos, l.titulo, (trunc(sysdate) - trunc(p.fecha_e)) as ATRASO, (trunc(sysdate) - trunc(p.fecha_e))*1250 as DEUDA from prestamo p, estudiantes e, libros l where p.rut_est = e.rut_est and p.cod_libro = l.cod_libro and trunc(sysdate) > trunc(p.fecha_e)

239 datos

3.- Entregue la suma de dinero que ganar la Biblioteca acumulada al da de hoy. Ayuda: Para hacer la suma se usa la funcin SUM y la funcin GROUP BY. select SUM (trunc(sysdate) - trunc(p.fecha_e))*1250 AS INGRESO_BIBLIO from prestamo p, estudiantes e, libros l where p.rut_est = e.rut_est and p.cod_libro = l.cod_libro and trunc(sysdate) > trunc(p.fecha_e)

1188750 datos

4.- Entregue el promedio de deuda que tienen los estudiantes al da de hoy. Ayuda: Para calcular el promedio se usa la funcin AVG y la funcin GROUP BY. select ROUND(AVG ((trunc(sysdate) - trunc(p.fecha_e))*1250)) AS PROM_DEUDA from prestamo p, estudiantes e, libros l where p.rut_est = e.rut_est and p.cod_libro = l.cod_libro and trunc(sysdate) > trunc(p.fecha_e)

4974 Datos

5.- Entregue la mnima deuda acumulada al da de hoy. Ayuda: Utilice la funcin MIN y la funcin GROUP BY. select MIN ((trunc(sysdate) - trunc(p.fecha_e))*1250) AS MIN_DEUDA from prestamo p, estudiantes e, libros l where p.rut_est = e.rut_est and p.cod_libro = l.cod_libro and trunc(sysdate) > trunc(p.fecha_e)

1250 Datos

6.- Entregue la mxima deuda acumulada al da de hoy. Ayuda: utilice la funcin MAX y la funcin GROUP BY. select MAX ((trunc(sysdate) - trunc(p.fecha_e))*1250) AS MIN_DEUDA from prestamo p, estudiantes e, libros l where p.rut_est = e.rut_est and p.cod_libro = l.cod_libro and trunc(sysdate) > trunc(p.fecha_e)

23750 Datos

7.- Suponiendo que ninguno de los estudiantes que se encuentran con prstamo entrega sus libros, cual ser la deuda acumulada para 4 das ms. Ayuda: Para sumar un das a una fecha se hace de la siguiente forma: FECHA + N, donde N es la cantidad de das a sumar y FECHA es la fecha a la cual le estamos sumandos das. Para el caso de la fecha de hoy, sera SYSDATE + N. select SUM((trunc(sysdate+4) - trunc(p.fecha_e))*1250) AS DEUDA_4_DIAS from prestamo p, estudiantes e, libros l where p.rut_est = e.rut_est and p.cod_libro = l.cod_libro and trunc(sysdate+4) > trunc(p.fecha_e)

2643750 Datos

8.- En su sistema de Base de Datos se encuentran 2 Vistas (Views), una llamada MAXIMO y otra llamada MINIMO. Describa claramente que entregan cada una de ellas, haciendo anlisis de cada parte de la consulta. Ayuda: Busque informacin sobre Sub-Consultas. Definicin de subconsultas. Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia SELECT. Normalmente se utilizan para filtrar una clausula WHERE o HAVING con el conjunto de resultados de la subconsulta, aunque tambin pueden utilizarse en la lista de seleccin.Por ejemplo podramos consultar el alquiler ltimo de un cliente.

SELECT CO_CLIENTE, NOMBRE, MARCA, MODDELO FROM ALQUILERES WHERE CO_CLIENTE = 1 AND FECHA_ALQUILER = (SELECT MAX(FECHA_ALQUILER) FROM ALQUILERES WHERE CO_CLIENTE = 1)

En este caso, la subconsulta se ejecuta en primer lugar, obteniendo el valor de la mxima fecha de alquiler, y posteriormente se obtienen los datos de la consulta principal. Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que aparece encerrada entre parntesis. La subconsulta se puede encontrar en la lista de seleccin, en la clusula WHERE o en la clusula HAVING de la consulta principal. Tiene las siguientes restricciones: No puede contener la clusula ORDER BY No puede ser la UNION de varias sentencias SELECT Si la subconsulta aparece en la lista de seleccin, o esta asociada a un operador igual "=" solo puede devolver un nico registro.

Referencias externasA menudo, es necesario, dentro del cuerpo de una subconsulta, hacer referencia al valor de una columna de la fila actual en la consulta principal, ese nombre de columna se denomina referencia externa. Una referencia externa es un campo que aparece en la subconsulta pero se refiere a la una de las tablas designadas en la consulta principal.

Cuando se ejecuta una consulta que contiene una subconsulta con referencias externas, la subconsulta se ejecuta por cada fila de la consulta principal. En este ejemplo la subconsulta aparece en la lista de seleccin, ejecutndose una vez por cada fila que devuelve la consulta principal.

SELECT CO_EMPLEADO, NOMBRE, (SELECT MIN(FECHA_NOMINA) FROM NOMINAS WHERE CO_EMPLEADO = EMPLEADOS.CO_EMPLEADO) PRIMERA_NOMINA FROM EMPLEADOS;

9.- Entregue el nombre de la carrera y la cantidad de estudiantes por cada una de ellas que tiene libros atrasados. Ayuda: Use la funcin COUNT y la funcin GROUP BY. select COUNT(e.rut_est), c.nombre from prestamo p, estudiantes e, carreras c where p.rut_est = e.rut_est AND e.id_carrera = c.id_carrera and trunc(sysdate) > trunc(p.fecha_e) group by c.nombre

45 Datos 10.- Los mismo que en (9) pero esta vez que entregue la cantidad de estudiantes que tiene un libro en prstamo ya sea atrasado o al da. select COUNT(e.rut_est), c.nombre from prestamo p, estudiantes e, carreras c where p.rut_est = e.rut_est AND e.id_carrera = c.id_carrera group by c.nombre

45 Datos 11.- Investigue como entregar las 10 carreras que tienen mayor cantidad de libros en prstamo. Ayuda: Se usa un tipo de funcin llamada ROWNUM y la funcin ORDER BY. select * from (select COUNT(e.rut_est), c.nombre from prestamo p, estudiantes e, carreras c where p.rut_est = e.rut_est AND e.id_carrera = c.id_carrera group by c.nombre order by COUNT(e.rut_est) desc) where rownum