¡Descarga consultas realizadas para sql y más Apuntes en PDF de Sistemas de Gestión de Bases de Datos solo en Docsity!
Consultas SQL (SELECT)
Consultas sencillas
*Devolver todos los campos de una tabla (SELECT )
SELECT * FROM CLIENTES Con el * indicamos que queremos devolver todos los campos. Si CLIENTES dispone de los campos idCliente , nombre y descripcion , lo anterior sería equivalente a:
SELECT idCliente, nombre, descripcion FROM CLIENTES Obviamente, al querer todos los campos, esto es innecesario y es por tanto más conveniente emplear el asterisco (*). También sería equivalente emplear la notación completa:
SELECT CLIENTES.idCliente, CLIENTES.nombre, CLIENTES.descripcion FROM CLIENTES Al tener únicamente una tabla involucrada, podemos referirnos a los campos sin calificar, dado que no hay duda de a qué tabla se refiere. Cuando veamos consultas sobre varias tablas comprenderemos la necesidad de incluir esta notación calificada (TABLA.campo).
Devolver un subconjunto de los campos de una tabla (SELECT DISTINCT)
SELECT cp, ciudad FROM DIRECCION Esta consulta devolverá únicamente los campos cp (código postal) y ciudad de la tabla DIRECCION. Al tener un subconjunto de los campos, éstos no tienen por qué incluir a la clave de la tabla, por lo que no tienen por qué ser únicos. Así, si tenemos muchos registros referidos a distintas calles y números de ese mismo código postal y ciudad, nos encontraremos muchos registros repetidos. Esto puede evitarse haciendo:
SELECT DISTINCT cp, ciudad FROM CLIENTES Así se eliminan los registros repetidos, devolviendo únicamente una vez cada par cp , ciudad. Esta selección de un subconjunto de los datos de la tabla y excluyendo repetidos se denomina en álgebra relacional proyección.
Devolver un subconjunto de los registros de una tabla (WHERE)
SELECT numero, calle FROM DIRECCION WHERE ciudad = ‘Sevilla’ Esta consulta devolvería el número y la dirección de todas las direcciones pertenecientes a la ciudad de Sevilla. Como vemos, con WHERE indicamos la condición que deben cumplir los registros de la tabla para ser devueltos en la consulta. En este caso tenemos una condición
simple dada por la comparación de igualdad (=) entre al campo ( ciudad ) y un literal de tipo cadena, entre comillas simples (‘Sevilla’). SELECT calle, ciudad FROM DIRECCION WHERE numero = 12 Esta otra consulta devolvería la calle y ciudad de todos los registros de la tabla con el número 12, en este caso un literal numérico. Las condiciones empleadas pueden ser mucho más complejas incluyendo otro tipo de operadores y combinaciones de los mismos.
Operadores relacionales Al margen del signo de igualdad empleado anteriormente, se pueden usar n las condiciones simples de las consultas los operadores relacionales habituales, devolviendo siempre un valor booleano (lógico):
Operador Significado < Menor que
Mayor que <> Distinto de <= Menor ó Igual que = Mayor ó Igual que = Igual que
SELECT nombre FROM CLIENTES WHERE edad <= 32
Adicionalmente, disponemos de operadores de comparación adicionales, también devolviendo valores booleanos (lógicos) True o False según si se cumplen o no las condiciones:
BETWEEN: para indicar un intervalo de valores. SELECT nombre FROM CLIENTES WHERE edad BETWEEN 20 AND 35
LIKE: empleado para comparar patrones de texto pudiendo incluir comodines como los siguientes: Comodín Descripción % Sustituto para cero o más caracteres. _ Sustituto para exactamente un carácter [lista caracteres] Cualquier carácter de la lista [^lista caracteres] Cualquier carácter que no esté en la lista [!lista caracteres] Cualquier carácter que no esté en la lista
SELECT num, calle, cp FROM DIRECCION WHERE ciudad LIKE ‘Val%’ Esta consulta devolvería los datos de las direcciones de toda ciudad que comience por Val y siga por cualquier número de caracteres, incluyendo cero caracteres. Por ejemplo, Valladolid o
de habitantes. Si no indicamos ASC ni DESC, el comportamiento por defecto será el orden ascendente (ASC).
Devolución de expresiones
Asignación de un alias a un dato devuelto (AS) SELECT idCliente AS id, nombre AS cliente, descripcion AS desc FROM CLIENTES
Uso de expresiones empleando operadores y/o funciones Podemos practicar en SQLzoo a usar expresiones con operadores y funciones. Por ejemplo:
SELECT MOD(DAY(NOW()),7) AS numSemana, POW(2,3) AS potencia Devuelve el número de semana en la que nos encontramos dentro del mes, ya que NOW() nos devuelve la fecha/hora actual, de la cual extraemos el día con DAY, y posteriormente calculamos el módulo 7 de dicho día (de modo que para un día 26 devolvería un 5, por ejemplo). El dato con alias potencia8 devolvería 8 (2 elevado a 3, POW es power, potencia).
Se puede combinar naturalmente la potencia de este lenguaje de expresiones usando operadores y funciones sobre los datos de los registros de una tabla: SELECT DAY(fechaLinea) AS dia, FLOOR(precioLinea * 0.85) AS precioDtoRedondeado FROM LINEAPEDIDO
Consultas agrupadas (GROUP BY)
Las consultas anteriores recuperaban, trabajaban con, y mostraban información a nivel de cada registro individual de la base de datos. Así, si tenemos un producto con un determinado precio, podemos devolver el precio mediante SELECT precioLinea o bien operar sobre él como en SELECT precioLinea * 0..
Ahora bien, podemos querer obtener información que no proviene de un registro individual sino de la agrupación de información, como es el caso de contar el número de líneas de pedido, sumar el precio de todas las líneas por cada pedido, etc. Para ello, debemos emplear funciones agregadas y en la mayoría de los casos agrupar por algún campo.
Así, para ver el número total de registros podemos hacer: SELECT COUNT(*) FROM LINEAPEDIDO
Si por el contrario deseamos obtener el total de líneas por pedido, debemos indicar que agrupe por idPedido, lo que contará todos los registros con el mismo idPedido y calculará su cuenta: SELECT idPedido, COUNT(*) FROM LINEAPEDIDO GROUP BY idPedido
Lo mismo se puede aplicar a otras funciones como la suma, indicando en ese caso aparte de la agrupación el campo que queremos sumar: SELECT idPedido, SUM(precioLinea) FROM LINEAPEDIDO GROUP BY idPedido
¿Y si queremos hallar la media de los precios por cada pedido? En ese caso necesitamos de nuevo agrupar (GROUP BY) por pedido. SELECT idPedido, AVG(precioLinea) FROM LINEAPEDIDO GROUP BY idPedido
Igualmente, podríamos aplicar un redondeo (ROUND) sobre la media, para dejar 4 decimales, y aplicarle un alias (AS) para el nombre del dato de salida. SELECT idPedido, ROUND(AVG(precioLinea),4) AS media FROM LINEAPEDIDO GROUP BY idPedido
O podríamos establecer una condición sobre el dato agrupado ( HAVING ), de forma que solamente se muestren las medias menores o iguales que 10. Existe una gran cantidad de funciones de agregación definidas en SQL, pero hay que tener precaución porque pueden diferir de un SGBD a otro.
SELECT idPedido, ROUND(AVG(precioLinea),4) FROM LINEAPEDIDO GROUP BY idPedido HAVING AVG(precioLinea) < 10
Para practicar un poco con las más comunes es muy recomendable este tutorial interactivo. En MySQL tendríamos las que aparecen en este enlace. Este es el desglose completo de las funciones de agregación estándar:
Function Usage
AVG(expression) Computes the average value of a column given by expression.
CORR(dependent, independent) Computes a correlation coefficient.
COUNT(expression) Counts the rows defined by the expression. COUNT(*) Counts all rows in the specified table or view.
COVAR_POP (dependent, independent) Computes population covariance.
COVAR_SAMP(dependent, independent) Computes sample covariance.
CUME_DIST(value_list) WITHIN GROUP (ORDER BY sort_list)
Computes the relative rank of a hypothetical row within a group of rows, where the rank is equal to the number of rows less than or equal to the hypothetical row divided by the number of rows in the group.
DENSE_RANK(value_list) WITHIN GROUP (ORDER BY sort_list)
Generates a dense rank (no ranks are skipped) for a hypothetical row (value_list) in a group of rows generated by GROUP BY.
Devolución de filas nulas/no nulas (IS NULL / IS NOT NULL)
SELECT idPedido FROM PEDIDO WHERE direccionEnvio IS NULL Devolvería los pedidos que no tienen dirección de envío asociada.
Consultas por intersecciones entre tablas (JOIN)
Podemos generar una consulta que obtenga datos de varias tablas, pudiendo establecer a su vez criterios sobre otras. Veamos los tipos fundamentales (una buena explicación se puede encontrar aquí). Por ejemplo, dados los clientes de nuestro concesionario y nuestras ventas de coches a clientes en los diversos concesionarios,
VENTA
CLIENTE
podemos obtener una consulta que obtenga datos del cliente y de la venta. Por ejemplo, nombre, apellidos, codcoche y color, mediante una consulta del tipo:
SELECT nombre, apellidos, codcoche, color FROM CLIENTE, VENTA WHERE CLIENTE.cifcl = VENTA.idCliente
INNER JOIN
INNER JOIN implícito Por ejemplo, para obtener los datos del cliente y el pedido en la misma consulta: SELECT nombreCliente, idPedido, fechaPedido FROM CLIENTE, PEDIDO WHERE CLIENTE.idCliente = PEDIDOpedido.idCliente Como vemos, podemos empezar escribiendo tal cuál qué datos nos piden (SELECT), de dónde podemos obtenerlos (FROM) y qué criterio (WHERE). Esta es la versión más antigua de SQL, aunque se sigue empleando, conociéndose como JOIN implícito ya que no se usa por ningún lado la palabra JOIN, pero se está haciendo la intersección por la foreign key, en este caso la columna idCliente.
Si omitimos en el caso anterior el WHERE: SELECT nombreCliente, idPedido, fechaPedido FROM CLIENTE, PEDIDO Obtendremos los datos solicitados para todos los pares de registros de CLIENTE y PEDIDO, el producto cartesiano, de forma que si hay 3 registros en CLIENTE y 4 en PEDIDO devolveremos 12 registros. Puede probar estos comportamientos en este enlace. La explicación detallada de un ejemplo similar con coches y marcas se puede encontrar en esta entrada de blog. Este tipo de join se conoce como CROSS JOIN , y en este caso se ha hecho de forma implícita.
INNER JOIN natural (NATURAL JOIN) SELECT nombreCliente, idPedido, fechaPedido FROM CLIENTE NATURAL JOIN PEDIDO Esta consulta obtiene lo mismo que el join implícito inicial, de forma que NATURAL JOIN establece la condición de igualdad entre los campos con el mismo nombre.
INNER JOIN explícito La forma más habitual de INNER JOIN es la que intersecta las tablas indicadas en con INNER JOIN por el campo indicado por ON. SELECT nombreCliente, idPedido, fechaPedido FROM CLIENTE INNER JOIN PEDIDO ON cliente.idCliente = pedido.idCliente Este comportamiento se explica y puede probar aquí, en este otro enlace o finalmente aquí. Como vemos, es equivalente al INNER JOIN implícito, pero en lugar de establecer una condición sobre los campos de las relaciones en el WHERE, establece la condición asociada a la relación entre ambas tablas a través de la FOREIGN KEY, en este caso desde el atributo (campo) idCliente en la relación (tabla) PEDIDO, FOREIGN KEY que hace referencia al atributo idCliente de la relación CLIENTE.
Con la notación explícita también podemos expresar un CROSS JOIN como se indicó en el caso explícito:
SELECT nombreCliente, idPedido, fechaPedido FROM CLIENTE CROSS JOIN PEDIDO
Nota : aunque lo más frecuente es que la condición del JOIN sea en términos de igualdad entre las claves, también se podrían establecer condiciones empleando otros operadores relacionales (operadores para comparar dos valores, devolviendo un resultado booleano, es decir, cierto o falso):
SELECT nombreCliente, idPedido, fechaPedido FROM CLIENTE INNER JOIN PEDIDO ON cliente.idCliente < pedido.idCliente
El hecho de unir los registros de las consultas de LEFT y JOIN, ambas devolviendo los mismos tipos de campos (EMPLEADO.nombre, EMPLEADO.apellidos, EMPRESA.nombre) puede ser expresado en SQL a través de la cláusula UNION:
SELECT EMPLEADO.nombre, EMPLEADO.apellidos, EMPRESA.nombre FROM EMPLEADO LEFT JOIN EMPRESA ON EMPLEADO.empresa = EMPRESA.cif UNION SELECT EMPLEADO.nombre, EMPLEADO.apellidos, EMPRESA.nombre FROM EMPLEADO RIGHT JOIN EMPRESA ON EMPLEADO.empresa = EMPRESA.cif
Para devolver los registros de la consulta anterior excluyendo los repetidos haríamos:
SELECT DISTINCTROW
FROM (SELECT EMPLEADO.nombre, EMPLEADO.apellidos, EMPRESA.nombre FROM EMPLEADO LEFT JOIN EMPRESA ON EMPLEADO.empresa = EMPRESA.cif UNION SELECT EMPLEADO.nombre, EMPLEADO.apellidos, EMPRESA.nombre FROM EMPLEADO RIGHT JOIN EMPRESA ON EMPLEADO.empresa = EMPRESA.cif)
Ahora bien, podríamos haber evitado los duplicados mediante una solución más ingeniosa, devolviendo en una de las dos consultas (LEFT o RIGHT) solamente los registros de una de las partes, excluyendo así los que cumplen la condición del JOIN, que serán devueltos por la otra:
SELECT EMPLEADO.nombre, EMPLEADO.apellidos, EMPRESA.nombre FROM EMPLEADO LEFT JOIN EMPRESA ON EMPLEADO.empresa = EMPRESA.cif UNION SELECT EMPLEADO.nombre, EMPLEADO.apellidos, EMPRESA.nombre FROM EMPLEADO RIGHT JOIN EMPRESA ON EMPLEADO.empresa = EMPRESA.cif WHERE EMPLEADO.empresa IS NULL
Algunos ejercicios adicionales sobre JOIN pueden encontrarse en este tutorial y los primeros 4 apartados de este otro (los restantes ejercicios emplean conceptos que no hemos visto), ambos en SQLZOO.
Consultas anidadas
A veces se han de utilizar en una consulta los resultados de otra consulta, llamada subconsulta o consulta anidada. Podemos ver muchos ejemplos de ello aquí. Veamos que existen diversas variantes:
Empleando IN Obtener el identificador de los clientes que han comprado algún coche a un concesionario de Madrid. SELECT DISTINCT cifcl FROM VENTA WHERE cifc IN (SELECT cifc FROM CONCESIONARIO WHERE ciudad = 'Madrid')
Obtener el código de coche de los coches vendidos por algún concesionario de Madrid. SELECT DISTINCT codcoche FROM VENTA WHERE cifc IN (SELECT cifc FROM CONCESIONARIO WHERE ciudad = 'Madrid')
Obtener el nombre y el modelo de los coches vendidos por algún concesionario de Barcelona. SELECT nombre, modelo FROM COCHE WHERE codcoche IN (SELECT codcoche FROM VENTA WHERE cifc IN (SELECT cifc FROM CONCESIONARIO WHERE ciudad = 'Barcelona'))
Empleando operadores relacionales Un ejemplo típico sería conocer los nombres de los empleados cuyo salario está por encima de la media: SELECT nombre FROM EMPLEADOS WHERE salario > (select avg(salario) from emp); Obtener el nombre y apellidos de los clientes cuyo identificador de cliente es menor que el de Juan Martín.
SELECT nombre, apellidos FROM CLIENTE WHERE cifcl < (SELECT cifcl FROM CLIENTE WHERE nombre = 'Juan' AND apellido = 'Martín')
