• Author / Uploaded
• Miguel Angel

Citation preview

Universidad Católica del Maule Facultad de Ciencias de la Ingeniería Escuela de Ingeniería Civil Informática

Tarea 1 Base de Datos ‘Consultas’

Alumno: Miguel Ortega Urrutia Profesora: Dra. Angélica Urrutia Fecha: 11 de Septiembre de 2014

Script Base de Datos DROP TABLE Libros_Pedidos; DROP TABLE Proveedores; DROP TABLE Usuarios; CREATE TABLE Usuarios( ID_Lector NUMBER NOT NULL, Apellido VARCHAR(20), Nombre VARCHAR(20), Telefono NUMBER, CONSTRAINT PK_USUARIO PRIMARY KEY(ID_Lector) ); CREATE TABLE Proveedores( NIF NUMBER NOT NULL, Empresa VARCHAR(30), CONSTRAINT PK_PROVEEDORES PRIMARY KEY(NIF) ); CREATE TABLE Libros_Pedidos( ID_Libro NUMBER NOT NULL, Titulo VARCHAR(30), Autor VARCHAR(30), ISBN NUMBER, SolicitadoPor NUMBER NOT NULL, Proveedor NUMBER NOT NULL, FechaP DATE, FechaR DATE, Precio NUMBER, CONSTRAINT PK_LIBROSPEDIDOS PRIMARY KEY(ID_libro), CONSTRAINT FK_LIBROS FOREIGN KEY(SolicitadoPor) REFERENCES Usuarios(ID_Lector), CONSTRAINT FK_LIBROS2 FOREIGN KEY(Proveedor) REFERENCES Proveedores(NIF));

Antes de comenzar con el análisis de las consultas es de suma importancia agregar que cada una de las consultas están creadas bajo ‘Vistas’, estas vistas se comportan como tablas virtuales, las cuales guardan el esquema de una tabla, pero no la tabla en sí. Las vistas se pueden crear, actualizar y eliminar, para este caso práctico solo usamos creación de vistas.

Consultas Simples Uso del LIKE: Usamos esta expresión para encontrar ciertas columnas de una tabla que comience, termine o contenga una letra o parte de una palabra.

Tiempos de Ejecución: Cantidad 5000 50000 100000

Tiempo 0.062 0.256 0.47

Grafico de Comparación:

LIKE 0,5 0,4 0,3 0,2

Tiempo (s)

0,1 0 5000

50000

100000

Uso del BETWEEN: Usamos esta expresión para encontrar los valores de las columnas indicadas que estén entre ciertos parámetros.

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo 0.109 0.967 1.045

Grafico de Comparación:

BETWEEN 1,2 1 0,8 0,6 0,4 0,2 0

Tiempo

5000

50000

100000

Uso de ORDER BY, con Operadores de Comparación: usamos ‘ORDER BY’, para que el resultado de la consulta se nos entregue ordenado según uno de los atributos de la tabla a la cual se le está haciendo la consulta. Para esta consulta también usamos operadores de comparación ‘>’ (mayor que) y además la función ‘EXTRACT’, la cual puede extraer ya sea el día, el mes o el año de una fecha.

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo 0.661 2.739 3.205

Grafico de Comparación:

ORDER BY 3,5 3 2,5 2 1,5 1 0,5 0

Tiempo(s)

5000

50000

100000

Consultas con Funciones Uso de GROUP BY y COUNT: Usamos ‘GROUP BY’, para literalmente agrupar los resultados de una consulta según un atributo determinado. Además usamos ‘COUNT’ para contar la cantidad de apariciones de una determinada columna.

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo 0.016 0.109 0.141

Grafico de Comparación:

COUNT & GROUP BY 0,15 0,1 Tiempo(s)

0,05 0 5000

50000

100000

Uso de función MIN y EXTRACT: la función ‘MIN’, devuelve el mínimo valor de una columna, y la función ‘EXTRACT’ retorna el día, mes o año de la fecha que se le pasa como parámetro.

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo 0.016 0.047 0.063

Grafico de Comparación:

MIN & EXTRACT 0,07 0,06 0,05 0,04 0,03 0,02 0,01 0

Tiempo(s)

5000

50000

100000

Uso de función AVG: La función ‘AVG’, saca el promedio de una columna pasada por parámetro, esta función no toma en cuenta los valores nulos que puedan existir a lo largo de la columna.

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo 0.007 0.015 0.023

Grafico de Comparación:

AVG & WHERE 0,025 0,02 0,015 Tiempo(s)

0,01 0,005 0 5000

50000

100000

Consultas con tipos de JOIN

Uso de JOIN: Utilizamos esta clausula para comparar dos o más tablas que tienen un campo en común. Para la siguiente consulta hacemos que la consulta busque las correspondencias entre las tablas Libros_Pedidos y Usuarios.

La consulta anterior, también se puede hacer usando la clausula ‘WHERE’

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo JOIN 0.705 5.087 16.214

Tiempo WHERE 0.502 4.609 16.178

Grafico de Comparación:

JOIN VS WHERE 18 16 14 12 10 8 6 4 2 0

Tiempo JOIN(s) Tiempo WHERE(s)

5000

50000

100000

De acuerdo al grafico podemos observar que la sentencia ‘WHERE’, es un poco más efectiva que usar ‘JOIN’, sobre dos tablas. Ahora bien, esto ocurre por la forma en que ambas clausulas tienen para trabajar sobre las tablas, ya que la sentencia ‘where’ actúa sobre la parte de la tabla que se indica, mientras que el ‘join’, actúa sobre la tabla completa haciendo que la consulta sea mas lenta que con su contraparte ‘where’.

Uso de INNER JOIN: Alternativo al ‘JOIN’ también está el ‘INNER JOIN’, este devuelve todas las filas cuando hay por lo menos una coincidencia en ambas tablas.

La consulta anterior, también se puede hacer usando la clausula ‘WHERE’

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo INNER JOIN 0.422 2.028 9.323

Tiempo WHERE 0.332 1.954 7.927

Grafico de Comparación:

10 9 8 7 6 5

Tiempo INNER JOIN

4

Tiempo WHERE

3 2 1 0 5000

50000

100000

Al igual que en el caso anterior, de acuerdo al grafico podemos observar que la sentencia ‘WHERE’, es un poco más efectiva que usar ‘INNER JOIN’, sobre dos tablas. Ahora bien, esto ocurre por la forma en que ambas clausulas tienen para trabajar sobre las tablas, ya que la sentencia ‘where’ actúa sobre la parte de la tabla que se indica, mientras que el ‘join’, actúa sobre la tabla completa haciendo que la consulta sea más lenta que con su contraparte ‘where’.

Uso de LEFT JOIN: Este tipo JOIN devuelve todas las filas de la tabla de la izquierda, y las filas coincidentes de la tabla derecha.

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo 0.811 6.456 22.414

Grafico de Comparación:

LEFT JOIN 25 20 15 Tiempo (s)

10 5 0 5000

50000

100000

Uso de RIGHT JOIN: Este tipo de JOIN devuelve todas las filas de la tabla de la derecha, y las filas coincidentes de la tabla izquierda.

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo 0.825 6.672 22.96

Grafico de Comparación:

RIGHT JOIN 25 20 15 Tiempo (s)

10 5 0 5000

50000

100000

Algo muy importante que debemos destacar es sobre el uso de ‘LEFT JOIN’ Y ‘RIGHT JOIN’, ambas consultas no generan las mismas tuplas pero sí en cambio representan los mismos conjuntos.

SUBQUERIES Una subconsulta es una consulta SELECT que devuelve un único valor y esta ANIDADA en las instrucciones SELECT, INSERT, UPDATE o DELETE, o dentro de otra subconsulta. En general una subconsulta se puede utilizar en cualquier parte en la que se permita una expresión. La siguiente subconsulta está hecha sobre la misma tabla, esta devuelve los precios de los libros que están por sobre el promedio.

Tiempos de Ejecución:

Cantidad 5000 50000 100000 Grafico de Comparación:

Tiempo 0.159 1.05 2.297

SUBQUERY & AVG 2,5 2 1,5 Tiempo(s)

1 0,5 0 5000

50000

100000

La siguiente subconsulta está hecha sobre dos tablas distintas, esta devuelve todos los usuarios cuyo id sea distinto de 3 y el id del proveedor sea menor que 3.

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Grafico de Comparación:

Tiempo 0.391 1.767 2.637

SUBQUERY & AND 3 2,5 2 1,5 Tiempo(s)

1 0,5 0 5000

50000

100000

HAVING La consulta HAVING se añadió a SQL, ya que la clausula WHERE no admitía funciones tales como AVG, MIN, MAX, etc. Generalmente es usada después de la clausula GROUP BY, para agrupar los resultados obtenidos de GROUP BY que cumplen las condiciones de HAVING. La siguiente consulta entrega los datos de los libros que fueron pedidos más de una vez.

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo 0.068 0.154 0.266

Grafico de Comparación:

HAVING 0,3 0,25 0,2 0,15 Tiempo(s)

0,1 0,05 0 5000

50000

100000

En la siguiente consulta, pedimos las ganancias por libro vendido, agrupados por Titulo, Precio y Autor, con la condición de que precio sea menor que $11000 y el Autor sea igual a ‘Leiva’.

Tiempos de Ejecución:

Cantidad 5000 50000 100000 Grafico de Comparación:

Tiempo 0.003 0.312 1.111

HAVING & COMPARACIÓN 1,2 1 0,8 0,6 Tiempo(s)

0,4 0,2 0 5000

50000

100000

UNION Y CONSULTA SOBRE VISTA El operador UNION se usa para combinar el resultado conjunto de dos o más instrucciones SELECT. Se debe tener en cuenta que cada sentencia SELECT dentro de la Unión debe tener el mismo número de columnas. Las a su vez deben tener tipos de datos similares. Además, las columnas de cada instrucción SELECT deben estar en el mismo orden. La siguiente consulta devuelve los libros cuyo autor su nombre comience con ‘T’ y además todos los libros cuyo autor sea ‘Larson’ notemos también que se pueden hacer consultas por sobre la vista, y para este caso filtramos aun más el resultado de de la consulta que se pide dentro de la vista, y pedimos además que el precio de los libros no supere los $15000.

El hecho de usar vistas, simplifica en parte las consultas que contienen ‘SELECT’ complejos, además que permite personalizar la BD para los distintos usuarios, de forma que presenten los datos con una estructura lógica para los mismos. Además podemos destacar que sobre una vista también podemos hacer consultas, esto quiere decir que el resultado que está dentro de la vista podemos filtrar aun más la información que ella contiene.

Tiempos de Ejecución:

Cantidad 5000 50000 100000

Tiempo 0.039 0.127 1.215

Grafico de Comparación:

UNION & CONSULTA SOBRE VISTA 1,4 1,2 1 0,8 0,6

Tiempo(s)

0,4 0,2 0 5000

50000

100000

Introducción

Como ya se ha visto en curso anterior, el trabajo con bases de daos es vital al momento de mantener la información ordenada y poder manipular los datos, realizando consultas, agregando datos. Y esto pasa principalmente en nuestro rubro, ya que estamos constantemente trabajando con un sinfín de datos, que nos ayudan ya sean a resolver problemas o simplemente almacenar para su posterior uso. El problema ocurre cuando tenemos que manipular estos datos, para los cuales la cantidad puede variar. Ahora bien, cuando tenemos tal magnitud de datos que no podemos controlarlos mediante métodos básicos, y en nuestro caso cuando los datos están sobre una base de datos relacional tenemos una gran gama posibilidades para manipular los datos que allí se encuentran. El fin de este informe es trabajar con las más importantes funciones y clausulas del lenguaje SQL y Oracle, haciendo comparaciones con tiempos de ejecución para distintas cantidades de datos (5000,50000 y 100000). La herramienta utilizada para la realización de este trabajo es Oracle 10G Express Edition, con un entorno de desarrollo llamado SQLDeveloper.

Conclusión:

El hecho de reforzar lo aprendido en clases, haciendo este informe, hace que podamos adquirir un mayor dominio de estos temas tan importantes en nuestra carrera como lo son las Bases de Datos y por supuesto su manipulación. También es posible apreciar que existen algunos tipos de consultas que hacen lo mismo que otras pero que su tiempo de ejecución es mayor. También debemos destacar el uso de vistas, las cuales simplifican bastante el entendimiento de la Base de Datos. Este trabajo ha sido muy importante para demostrar la rapidez que se tiene al manejar conocimientos en Oracle y SQL. Es importante destacar que el tiempo de ejecución de una consulta también depende de la maquina en la que se esté trabajando, pues, en una computadora común y corriente no es posible trabajar con grandes cantidades de datos. Es destacable que para trabajar fue necesario evitar tener otra aplicaciones que utilizaran muchos recursos de la maquina, pues se ponía más lento el equipo.