¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. SQL
  3. Profundizando
Extrait - SQL Fundamentos del lenguaje (con ejercicios corregidos) - (4ª edición)
Extractos del libro
SQL Fundamentos del lenguaje (con ejercicios corregidos) - (4ª edición) Volver a la página de compra del libro

Profundizando

Las subconsultas

Es posible insertar los resultados de una consulta en otra. Esto se puede encontrar después de la cláusula WHERE o bien sustituir una constante detrás de una cláusula IN o EXISTS, por ejemplo.

Hay dos tipos de subconsultas: anidadas o correlacionadas.

1. Las subconsultas anidadas

En función de lo que puede devolver la sub-SELECT, esta no se podrá situar en cualquier sitio.

Si el resultado de la consulta situada en una sub-SELECT solo recupera una fila, se podrá utilizar la subconsulta en lugar de cualquier constante.

Por ejemplo, si queremos recuperar todas las habitaciones que tienen una cama individual con ducha, es necesario hacer una sub-SELECT que recupere el identificador de la tabla TIPOSHABITACION que se corresponde con la descripción 1 cama individual con ducha, y después comprobar que la columna TipoHabitacion de la tabla HABITACIONES se corresponde con el valor de la sub-SELECT.

Antes de probar la consulta completa, es preferible probar la subconsulta para comprobar su validez y que devuelve una única fila.

Ejemplo

SELECT idtipohabitacion FROM tiposhabitacion WHERE 
descripcion = '1 cama individual con ducha'; 

muestra:

IDTIPOHABITACION 
---------- 
         1 

A continuación, se puede incluir la subconsulta en la consulta principal de la siguiente manera:

SELECT hoteles.etiqueta, numhabitacion FROM Habitaciones 
INNER JOIN hoteles ON hoteles.idhotel =habitaciones.hotel 
where tipohabitacion = (SELECT idtipohabitacion FROM tiposhabitacion 
where descripcion = '1 cama individual con ducha'); 

Resultado

ETIQUETA                                            NUMHAB 
-------------------------------------------------- ------ 
Ski Hotel                                          1 
Art Hotel                                          1 ...

Las importaciones y exportaciones de datos

Según las bases de datos, hay herramientas de importación y exportación de datos, como SQL Loader para Oracle, que se describe a continuación. En SQL Server, es posible utilizar la herramienta bcp o la herramienta de importación/exportación a partir de SQL Server Management Studio. No vamos a describir estas herramientas; la última se utiliza de manera muy intuitiva.

La herramienta más adecuada para industrializar la importación o exportación de datos es un ETL (Extract Transform and Load) como SSIS del editor de Microsoft, incluido en la licencia de SQL Server (excepto la versión Express), Talend en código abierto, Oracle Data Integrator, Pentaho, Stambia, etc.

Desde el cliente Oracle SQL Developer, todo lo que tiene que hacer es hacer clic derecho en una tabla y elegir Exportar ... o Copiar a Oracle y seguir las instrucciones

1. Carga de datos masiva con SQL*Loader

Después de haber creado los esqueletos de las diferentes tablas, ahora hay que alimentarlas.

Cuando existe un histórico, puede ser interesante cargar rápidamente y de forma masiva todo este histórico.

Antes de nada, habrá que poner este histórico en el formato esperado para que la carga funcione con la herramienta elegida.

Retomemos por ejemplo la tabla PELICULAS que llenamos con múltiples INSERT en el capítulo La manipulación de los datos (LMD) - Ejercicios de aplicación.

TABLA PELICULAS

Consulta de creación de la tabla (sintaxis estándar):

CREATE TABLE PELICULAS (IDENT_PELICULAS INTEGER, 
                        TITULO          VARCHAR(50), 
                        GENERO1         VARCHAR(20), 
                        GENERO2         VARCHAR(20), 
                        FECHA_ESTRENO   DATE, ...

Algunos conceptos de rendimiento

En la utilización de una base de datos, a menudo se encuentran problemas de tiempos de respuesta grandes en una consulta u otra.

Las razones son múltiples, se puede tratar de una consulta que no utiliza ningún índice, de una tabla muy grande, de uniones múltiples, de problemas de acceso a disco o de capacidad de memoria, etc.

Lo que se denomina el « tuning » de una base de datos es muy complejo y necesita mucha experiencia y conocimientos de bases de datos y sistemas operativos. 

Las reglas básicas cuando se escribe una consulta son:

  • Comprobar que los criterios de búsqueda (WHERE) utilizan los índices.

  • Comprobar que las uniones entre tablas se hacen sobre las claves de las tablas y que los índices son correctos en estas tablas.

  • Comprobar que la selección no devuelve millones de filas.

  • Comprobar que las estadísticas de la base de datos están activadas y actualizadas regularmente (sobre todo en Oracle).

  • No utilizar muchas funciones en un mismo SELECT.

Las estadísticas son datos que sirven a la base de datos para saber qué camino es el más adecuado para obtener un dato.

1. Utilización de EXPLAIN PLAN

Existe un medio para saber el camino que utiliza el SGBDR para acceder a un elemento. Hay que utilizar el comando EXPLAIN PLAN que analiza el comando e indica el camino elegido. Para ello, se almacenan los elementos en una tabla: PLAN_table en Oracle.

La sintaxis a utilizar es esta:

EXPLAIN PLAN SET STATEMENT_ID='<identificador>' INTO PLAN_TABLE FOR 
SELECT ... ... ; 

Se indica al SGBDR que almacene en una tabla llamada «PLAN_TABLE» bajo el identificador elegido (STATEMENT_ID) los análisis realizados sobre la consulta que se indica después del SELECT.

Ejemplo con un SELECT sobre tres tablas, el identificador es ’TEST-REND’

DELETE FROM PLAN_TABLE WHERE STATEMENT_ID='TEST-REND'; 
 
EXPLAIN PLAN SET STATEMENT_ID='TEST-REND' INTO PLAN_TABLE FOR 
SELECT Hoteles.nombre 
, Habitaciones.NumHabitacion 
, TipoHabitacion.TipoCama 
, TipoHabitacion.NumeroCama 
, TipoHabitacion.Descripcion 
FROM Habitaciones, TipoHabitacion, Hoteles 
WHERE Habitaciones.TipoHabitacion = TipoHabitacion.idTipoHabitacion 
AND Hoteles.idhotel = Habitaciones.Hotel 
AND numerocama...

Las tablas del sistema

Los SGDBR utilizan para sus necesidades un conjunto de tablas para almacenar todos los elementos creados por un usuario. Todos los objetos se almacenan en las llamadas tablas del sistema.

Podemos acceder a ellas simplemente con el comando:

SELECT * FROM <Nombre tabla>; 

1. Tablas del sistema para tablas y columnas

a. Oracle

Tabla

Contenido

ALL_COL_COMMENTS

Lista todos los comentarios de las columnas de todas las tablas.

ALL_TABLES

Lista todas las tablas.

ALL_TAB_COLUMNS

Lista todas las columnas de todas las tablas.

b. MySQL

Tabla

Contenido

INFORMATION_SCHEMA.TABLES

Lista todas las tablas.

INFORMATION_SCHEMA.COLUMNS

Lista todas las columnas de todas las tablas.

c. SQL Server

SQL Server almacena estas tablas en una base de datos de sistema llamada master.

Tabla

Contenido

sys.tables

Lista todas las tablas.

Sys.all_columns

Lista todas las columnas de las tablas.

d. PostgreSQL

PostgreSQL almacena estas tablas en un esquema para cada base de datos llamado pg_catalog.

Tabla

Contenido

Pg_catalog.pg_class

Lista todas las tablas.

Pg_catalog.pg_attribute

Lista todas las columnas de las tablas.

2. Tablas del sistema para índices y vistas

a. Oracle

Tabla

Contenido

ALL_INDEXES

Lista todos los índices.

ALL_IND_COLUMNS

Lista todas las columnas de los índices.

ALL_VIEWS

Lista todas las vistas.

b. MySQL

Tabla

Contenido

INFORMATION_SCHEMA. STATISTICS

Lista toda la información sobre los índices.

INFORMATION_SCHEMA. VIEWS

Lista todas las vistas.

c. SQL Server

Tabla

Contenido

Sys.indexes

Lista toda la información de los índices.

Sys.views

Lista todas las vistas de usuario.

Sys.all_views

Lista todas las vistas.

d. PostgreSQL

Tabla

Contenido

Pg_catalog.pg_index

Lista toda la información del índice

Pg_catalog_pg_class

Lista todas las vistas.

3. El resto de tablas del sistema

a. Oracle

Tabla

Contenido

ALL_CATALOG

Lista todas las tablas, vistas, secuencias y sinónimos.

ALL_CONSTRAINTS

Lista las restricciones.

ALL_OBJECTS

Lista todos los objetos a los que puede acceder el usuario.

ALL_SEQUENCES

Lista las secuencias.

ALL_SYNONYMS

Lista los sinónimos.

ALL_TRIGGERS

Lista todos los triggers.

ALL_TRIGGERS_COLS

Lista todas las columnas de los triggers.

ALL_USERS

Lista los usuarios declarados.

b. MySQL

Tabla

Contenido

INFORMATION_ SCHEMA.SCHEMATA

Lista todas las tablas, vistas, secuencias y sinónimos.

INFORMATION_ SCHEMA.CONSTRAINTS

Lista todas las restricciones.

INFORMATION_SCHEMA....

Los metadatos, funciones y procedimientos de sistema de SQL Server

Procedimientos de sistema de descripción completa:

  • exec sp_helpdb

  • exec sp_help ’Hoteles’

  • exec sp_helpdb ’RESAHOTEL’

  • exec sp_linkedservers

Funciones de sistema:

  • select DB_NAME()

  • select DB_ID()

  • select DB_NAME(2)

  • select DB_ID(’RESAHOTEL’)

  • select SUSER_NAME()

  • select GETDATE()

  • select SYSDATETIME()

  • select HOST_NAME() --máquina

Variables de sistema:

  • select @@SERVERNAME --instancia

  • select @@VERSION

Algunos scripts útiles

1. Saber el tamaño real de una columna

En una columna declarada como VARCHAR, puede ser interesante saber el tamaño real de cada valor.

Esta consulta permite además ordenar el resultado.

Sintaxis:

SELECT <nombre de columna>, LENGTH (RTRIM(<nombre de columna>)) 
FROM <nombre tabla> WHERE    .. 
ORDER BY LENGTH (TRIM(<nombre de columna>)),<nombre de columna>  

Ejemplo Oracle:

SELECT LENGTH(TRIM(descripcion)) as longituddesc, descripcion 
FROM tiposhabitacion 
ORDER BY longituddesc; 

Exemple SQL Server

SELECT LEN(TRIM(descripcion)) as longituddesc, descripcion 
FROM tiposhabitacion 
ORDER BY longituddesc; 

Resultado

LONGITUDDESC DESCRIPCION 
------------ -------------------------------------------------- 
         24 1 cama individual con ducha 
         24 1 cama doble con ducha 
         25 2 camas doble con ducha 
         26 2 camas individuales con ducha 
         34 1 cama XL y 1 cama individual con baño 
         35 1 cama doble con baño y WC separados 
         36 2 camas doble con baño y WC separados 
         37 1 cama doble con ducha y WC separados 
         38 2 camas doble con ducha y WC separados 

2. Buscar y eliminar duplicados en una tabla

A menudo nos encontramos con filas duplicadas en una tabla después de una mala manipulación o de un error en la aplicación que no controle los duplicados.

Si retomamos la tabla TIPOSHABITACION y añadimos la fila n° 13 con 1 cama individual con ducha que existe ya en la fila 1.

INSERT INTO tiposhabitacion VALUES (13, 1, 'cama individual' ,'1 
cama individual con ducha'); 

Contenido de la tabla TIPOSHABITACION

IDTIPOHABITACION  NUMEROCAMA TIPOCAMA              DESCRIPCION 
---------------- ---------- -------------------- -------------------------- 
             ...

Ejercicios

Primer ejercicio

Crear una consulta que recoja todas las películas que tienen en su casting un actor francés.

Segundo ejercicio

Añada el actor Jean DUJARDIN a la base de datos y, a continuación, muestre los actores (toda la información) que tienen el mismo nombre que otro actor.

Soluciones a los ejercicios

Primer ejercicio

SELECT TITULO, APELLIDO NOMBRE
FROM   PELICULA    INNER JOIN CASTING ON PELICULA.IDENT_ PELICULA = 
CASTING.IDENT_PELICULA 
INNER JOIN ACTOR ON  CASTING.IDENT_ACTOR = ACTOR.IDENT_ACTOR 
WHERE NACIONALIDAD = (SELECT PAIS.IDENT_PAIS FROM PAIS WHERE 
                         PAIS.ETIQUETA = 'FRANCIA') 
ORDER BY PELICULA.TITULO, APELLIDO; 

Segundo ejercicio

INSERT INTO ACTOR VALUES (15, 'DUJARDIN', 'JEAN', '19/06/1972', 
57,1); (SQL Server) 
INSERT INTO ACTOR VALUES (15, 'DUJARDIN', 'JEAN', 
TO_DATE('19/06/1972','DD/MM/YYYY'),57,1); (Oracle) 
INSERT INTO ACTOR VALUES (15, 'DUJARDIN', 'JEAN', 
'19720619',57,1); (MySQL y PostgreSQL) 
SELECT * FROM ACTOR A1 
WHERE EXISTS( SELECT NOMBRE, COUNT(1) FROM ACTOR AS A2 WHERE 
A1.NOMBRE = A2.NOMBRE 
GROUP BY NOMBRE 
HAVING COUNT(1) > 1);