Consultas complejas
Table Of Contents
Uno de los temas que más cuesta a los que empiezan a aprender SQL son las consultas en las que se recogen diferentes tipos de datos de una ó múltiples tablas. Este artículo es una introducción a como definir consultas de este tipo en PostgreSQL.
Unos conocimientos básicos de normalización de datos y un poco de álgebra relacional no vienen mal para entender mejor algunos de los términos que vamos a usar en este artículo. La normalización de datos es tema para otro artículo, pero en este veremos brevemente algunos conceptos de álgebra relacional que nos pueden ayudar a entender mejor el tema que estamos tratando.
Algebra relacional
El álgebra relacional es un tipo de álgebra con una serie de operadores que trabajan sobre una ó varias relaciones para obtener una relación resultado. Es la base indispensable para poder escribir buenas consultas en SQL.
Las operaciones más importantes disponibles en álgebra relacional son:
- Las operaciones de conjunto aplicadas a relaciones: unión(∪), intersección(∩) y diferencia(-)
- Operaciones que eliminan una parte de las relaciones: selección(σ) y proyección(Π)
- Operaciones que combinan las tuplas de dos relaciones: producto cartesiano(x), combinacion natural (><) y theta
- Operación que cambia el nombre de los atributos ó relación: renombre(ρ)
A continuación vamos a dar una breve introducción sobre estas operaciones:
Unión [R∪S]
La unión de R y S es el conjunto de elementos que existen en R, ó en S, ó en las dos. Un elemento que existe tanto en R como en S aparece solamente una vez en la unión. En el lenguaje SQL este tipo de operación se puede realizar con la clausula UNION
Intersección [R∩S]
La intersección de R y S es el conjunto de elementos que existen en R y en S. En el lenguaje SQL este tipo de operación se puede realizar con la clausula INTERSECT
Diferencia [R-S]
La diferencia de R y S es el conjunto de elementos que existen en R pero no en S. R-S es diferente a S-R, S-R seria el conjunto de elementos que existen en S pero no en R. En el lenguaje SQL este tipo de operación se puede realizar con la clausula EXCEPT
Selección [σc(R)]
Esta operación aplicada a una relacion R, produce una nueva relación con un subconjunto de tuplas de R. Este subconjunto de tuplas satisface y cumple cierta condición(c)
Proyección [Πa1,a2,…,an(R)]
Esta operación aplicada a una relación R, produce una nueva relación con solamente los atributos(columnas) especificados por a1,a2,…,an
Producto cartesiano [RxS]
El producto cartesiano de dos relaciones R y S es la relación que se obtiene de la combinación de todas las tuplas de R con todas las tuplas de S. Las tuplas de la relación que se obtiene están formadas por todos los atributos de R seguidos de todos los atributos de S. En el lenguaje SQL este tipo de operación se puede realizar con la cláusula CROSS JOIN ó separando las relaciones usadas en el producto con comas, en el FROM de la sentencia SQL.
Combinaciones
Por medio del operador combinación (JOIN) podemos combinar dos relaciones segun una condición para obtener tuplas compuestas por atributos de las dos relaciones combinadas.
En el lenguaje SQL existen diferentes maneras de combinar dos relaciones. A continuación teneis un resumen de las existentes en PostgreSQL:
Combinaciones internas (R INNER JOIN S): Un INNER JOIN entre dos relaciones R y S, es el resultado que se obtiene despues de aplicar al producto cartesiano de las dos relaciones R y S, una condición para acotar dicho producto. Existen un par de casos especiales:
- De equivalencia (Equi-join): Es un caso particular de INNER JOIN en el que la condicion que acota el resultado es una comparación de igualdad.
- R NATURAL JOIN S: Es un caso especial de equi-join en el que en el caso de existir columnas con el mismo nombre en las relaciones que se combinan, solo se incluirá una de ellas en el resultado de la combinación.
Combinaciones externas (OUTER JOINS): Un OUTER JOIN entre dos relaciones R y S contiene todas las tuplas que un INNER JOIN devolveria, más una serie de tuplas que no tienen atributos en común en las dos relaciones. Los diferentes tipos son:
- R LEFT OUTER JOIN S: Un LEFT OUTER JOIN entre dos relaciones R y S, retorna todas las tuplas de la combinación que tengan un atributo común, más todas las tuplas de la relación de la izquierda (R) que no tengan un equivalente en la relación de la derecha (S).
- R RIGHT OUTER JOIN S: Un RIGHT OUTER JOIN entre dos relaciones R y S, retorna todas las tuplas de la combinación que tengan un atributo común, más todas las tuplas de la relación de la derecha (S) que no tengan un equivalente en la relación de la izquierda (R).
- R FULL OUTER JOIN S: Un FULL OUTER JOIN entre dos relaciones R y S, retorna todas las tuplas de la combinación que tengan un atributo común, más todas las tuplas de la relación de la izquierda (R) que no tenga un equivalente en la relación de la derecha (S) y todas las tuplas de la relación de la derecha (S) que no tenga un equivalente en la relación de la izquierda (R).
Renombre [ρa/b(R)]
Esta operación aplicada a una relación R, produce una nueva relación identica a R en donde el atributo ‘b’ ha sido renombrado a ‘a’.
El uso combinado de todas estas operaciones aplicado a nuestras relaciones dará lugar a consultas más ó menos complejas.
Utilizando los operadores definidos en algebra relacional, podemos definir de manera gráfica (árboles) ó lineal la representación algebraica de nuestra consulta. En consultas muy complicadas es lo que se deberia de hacer antes de empezar a escribir el codigo SQL, pero este es un tema para otro artículo.
Ejemplos prácticos
Nada mejor que unos ejemplos básicos para ver como se aplica la teoria que hemos visto. Lo primero que vamos a hacer es definir un par de tablas que utilizaremos en nuestros ejemplos:
postgres=# SELECT * FROM relacion_r;
a | b | c
---+---+---
1 | 2 | 3
4 | 5 | 6
(2 rows)
postgres=# SELECT * FROM relacion_s;
c | d | e
---+---+---
4 | 5 | 6
7 | 8 | 9
(2 rows)
En nuestro primer ejemplo realizamos una unión de las dos relaciones, el resultado obtenido seria:
postgres=# SELECT * FROM relacion_r UNION SELECT * FROM relacion_s;
a | b | c
---+---+---
4 | 5 | 6
1 | 2 | 3
7 | 8 | 9
(3 rows)
A continuación realizamos una intersección entre las dos relaciones:
postgres=# SELECT * FROM relacion_r INTERSECT SELECT * FROM relacion_s;
a | b | c
---+---+---
4 | 5 | 6
(1 row)
La diferencia entre estas dos relaciones daria el siguiente resultado. Como podeis ver, y por definición, no es lo mismo la diferencia entre relacion_r y relacion_s, que entre relacion_s y relacion_r
postgres=# SELECT * FROM relacion_r EXCEPT SELECT * FROM relacion_s;
a | b | c
---+---+---
1 | 2 | 3
(1 row)
postgres=# SELECT * FROM relacion_s EXCEPT SELECT * FROM relacion_r;
c | d | e
---+---+---
7 | 8 | 9
(1 row)
Vamos a definir una nueva fila (3,4,5) en la tabla relacion_s para ver unos ejemplos de como combinar estas dos relaciones mediante JOINs.
postgres=# SELECT * FROM relacion_r;
a | b | c
---+---+---
1 | 2 | 3
4 | 5 | 6
(2 rows)
postgres=# SELECT * FROM relacion_s;
c | d | e
---+---+---
4 | 5 | 6
7 | 8 | 9
3 | 4 | 5
(3 rows)
La manera más simple de combinar estas dos relaciones es realizar el producto cartesiano de ambas. Esto se puede realizar de dos maneras, ó bien definiendo las dos relaciones separadas por comas despues del FROM.
postgres=# SELECT * FROM relacion_r,relacion_s;
a | b | c | c | d | e
---+---+---+---+---+---
1 | 2 | 3 | 4 | 5 | 6
1 | 2 | 3 | 7 | 8 | 9
1 | 2 | 3 | 3 | 4 | 5
4 | 5 | 6 | 4 | 5 | 6
4 | 5 | 6 | 7 | 8 | 9
4 | 5 | 6 | 3 | 4 | 5
(6 rows)
O utilizando la cláusula CROSS JOIN entre las dos relaciones.
postgres=# SELECT * FROM relacion_r CROSS JOIN relacion_s;
a | b | c | c | d | e
---+---+---+---+---+---
1 | 2 | 3 | 4 | 5 | 6
1 | 2 | 3 | 7 | 8 | 9
1 | 2 | 3 | 3 | 4 | 5
4 | 5 | 6 | 4 | 5 | 6
4 | 5 | 6 | 7 | 8 | 9
4 | 5 | 6 | 3 | 4 | 5
(6 rows)
En realidad un CROSS JOIN es el equivalente a un INNER JOIN ON (true). Esto porque el INNER JOIN es por definición un producto cartesiano al que se le aplica una condición para acotar el resultado. En este caso particular la condición siempre se cumple para todas las tuplas al utilizar el valor TRUE, con lo que obtendremos todas las tuplas del producto cartesiano.
postgres=# SELECT * FROM relacion_r INNER JOIN relacion_s ON (true);
a | b | c | c | d | e
---+---+---+---+---+---
1 | 2 | 3 | 4 | 5 | 6
1 | 2 | 3 | 7 | 8 | 9
1 | 2 | 3 | 3 | 4 | 5
4 | 5 | 6 | 4 | 5 | 6
4 | 5 | 6 | 7 | 8 | 9
4 | 5 | 6 | 3 | 4 | 5
(6 rows)
A continuación podeis ver como definir un INNER JOIN con la condición definida dentro de ON(). Este ejemplo es un Equi-join al utilizarse una comparación de igualdad en la condición.
postgres=# SELECT * FROM relacion_r AS r INNER JOIN relacion_s AS s ON (r.c = s.c);
a | b | c | c | d | e
---+---+---+---+---+---
1 | 2 | 3 | 3 | 4 | 5
(1 rows)
El mismo resultado obtenido con la clausula INNER JOIN se podria haber conseguido obteniendo el producto cartesiano de las dos relaciones y aplicando una condición con WHERE (definicion de INNER JOIN).
postgres=# SELECT * FROM relacion_r AS r, relacion_s AS s WHERE r.c = s.c;
a | b | c | c | d | e
---+---+---+---+---+---
1 | 2 | 3 | 3 | 4 | 5
(1 rows)
postgres=# SELECT * FROM relacion_r as r CROSS JOIN relacion_s as s WHERE r.c = s.c;
a | b | c | c | d | e
---+---+---+---+---+---
1 | 2 | 3 | 3 | 4 | 5
(1 row)
Un NATURAL JOIN retorna el mismo resultado que un equi-join, pero sin repetir las columnas comunes.
postgres=# SELECT * from relacion_r natural join relacion_s;
c | a | b | d | e
---+---+---+---+---
3 | 1 | 2 | 4 | 5
postgres=# SELECT a,b,c,d,e from relacion_r natural join relacion_s;
a | b | c | d | e
---+---+---+---+---
1 | 2 | 3 | 4 | 5
(1 row)
Como podeis ver en lo que llevamos de artículo, existen diferentes maneras de obtener un mismo resultado utilizando diferentes tipos de consultas. Teniendo los conceptos claros y con práctica, os aseguro que todos estos tipos de consultas os saldrán de forma natural despues de un tiempo.
A continuación vamos a ver las combinaciones de tipo OUTER JOIN. En PostgreSQL el uso de la palabra OUTER es opcional, a mi me gusta utilizarla aunque no se necesite.
La primera es un LEFT OUTER JOIN.
postgres=# SELECT * FROM relacion_r AS r LEFT OUTER JOIN relacion_s AS s ON (r.c = s.c);
a | b | c | c | d | e
---+---+---+---+---+---
1 | 2 | 3 | 3 | 4 | 5
4 | 5 | 6 | | |
(2 rows)
Seguido de un RIGHT OUTER JOIN:
postgres=# SELECT * FROM relacion_r AS r RIGHT OUTER JOIN relacion_s AS s ON (r.c = s.c);
a | b | c | c | d | e
---+---+---+---+---+---
1 | 2 | 3 | 3 | 4 | 5
| | | 4 | 5 | 6
| | | 7 | 8 | 9
(3 rows)
Y para terminar un FULL OUTER JOIN:
postgres=# SELECT * FROM relacion_r AS r FULL OUTER JOIN relacion_s AS s ON (r.c = s.c);
a | b | c | c | d | e
---+---+---+---+---+---
1 | 2 | 3 | 3 | 4 | 5
| | | 4 | 5 | 6
4 | 5 | 6 | | |
| | | 7 | 8 | 9
(4 rows)
Un ejemplo casi real
Los ejemplos que hemos visto hasta ahora, nos han mostrado la sintaxis básica de las operaciones que se pueden utilizar para obtener resultados con datos de múltiples relaciones. En la vida real nos encontraremos con casos muchos más complicados en los que tendremos que combinar todas estas operaciones junto con el resto de operadores y cláusulas SQL disponibles.
En casos complicados es importante pensar antes de empezar a escribir nuestra consulta SQL. A continuación vamos a ver un ejemplo un poco más complicado para ver como podemos desglosar y resolver la consulta que necesitamos para obtener el resultado deseado.
Utilizaremos unas tablas creadas únicamente para este ejemplo y no representativas de un sistema real. Tenemos una tabla principal llamada ‘PC’, con diferentes columnas conteniendo cadenas de identificación de los diferentes componentes usados para el ensamblado de diferentes modelos de PCs. Las columnas vacias de la tabla ‘PC’ significan componentes no presentes en dichos modelos. El resto de tablas contienen informacion adicional sobre los diferentes componentes usados para construir un PC.
postgres=# SELECT * FROM pc;
pcid | memoria | cpu | disco | tgrafica | precio
------+---------+---------+-----------+-----------+--------
1 | mem0001 | cpu0001 | disco0001 | ati001 | 1000
2 | mem0001 | cpu0001 | disco0002 | ati001 | 1100
3 | mem0002 | cpu0002 | disco0003 | nvidia001 | 1400
4 | mem0004 | cpu0003 | disco0004 | nvidia001 | 1600
5 | | cpu0001 | disco0001 | ati001 | 900
6 | | | | ati001 | 400
(6 rows)
postgres=# SELECT * FROM cpu ;
cpu_id | cpu_fabricante | cpu_tipo
---------+----------------+------------
cpu0001 | intel | Core2 duo
cpu0002 | intel | Core2 Quad
cpu0003 | amd | Athlon X2
(3 rows)
postgres=# SELECT * FROM memoria ;
mem_id | mem_capacidad | mem_tipo
---------+---------------+------------
mem0001 | 1024 | DDR SDRAM
mem0002 | 1024 | DDR2 SDRAM
mem0003 | 1024 | DDR3 SDRAM
mem0004 | 2048 | DDR3 SDRAM
(4 rows)
postgres=# SELECT * FROM disco ;
disco_id | disco_fabricante | disco_capacidad
-----------+------------------+-----------------
disco0001 | seagate | 350
disco0002 | seagate | 500
disco0003 | seagate | 1024
disco0004 | samsung | 500
(4 rows)
postgres=# SELECT * FROM tgrafica ;
tgraf_id | tgraf_fabricante
-----------+------------------
ati001 | ati
nvidia001 | nvidia
Utilizando estas tablas vamos a realizar varias consultas:
*Consulta 1
Obtener una relación de solo los modelos de PC ‘completos’ a la venta. Queremos toda la información disponible sobre los componentes que lo forman. Ordenar el resultado de mayor a menor precio.
Sabemos que al tener que coger datos de diferentes tablas, necesitaremos algun tipo de clausula JOIN. En esta consulta nos piden solamente, PCs completos, con todos sus componentes. Por ello descartamos todas las combinaciones de tipo OUTER JOIN y utilizamos INNER JOIN para obtener solamente las tuplas con atributos en todas las relaciones combinadas.
Cada PC tiene 4 componentes y la información de cada componente se encuentra en una tabla separada. Con estos datos sabemos que tendremos que realizar 4 INNER JOIN entre todas las tablas involucradas en la consulta.
Vamos a empezar a escribir la consulta SQL. Primero realizamos los INNER JOIN y declaramos las condiciones que acotarán el resultado. Tendremos que emparejar los atributos de componentes presentes en la tabla ‘PC’ con los correspondientes atributos en el resto de tablas de componentes.
SELECT *
FROM pc AS a
INNER JOIN memoria AS b ON (a.memoria = b.mem_id)
INNER JOIN cpu AS c ON (a.cpu = c.cpu_id)
INNER JOIN disco AS d ON (a.disco = d.disco_id)
INNER JOIN tgrafica AS e ON (a.tgrafica = e.tgraf_id);
Una vez que hemos combinado todas las tablas, vamos a definir los atributos que queremos presentar en nuestro resultado. Utilizaremos los prefijos de tablas definidos en la consulta anterior (a,b,c,d,e), para acceder a los atributos de cada tabla:
SELECT
a.pcid,
b.mem_tipo,
b.mem_capacidad AS mem_MB,
c.cpu_fabricante AS cpu_fab,
c.cpu_tipo,
d.disco_fabricante AS disco_fab,
d.disco_capacidad AS disco_GB,
e.tgraf_fabricante AS tgraf_fab,
a.precio
FROM pc AS a
INNER JOIN memoria AS b ON (a.memoria = b.mem_id)
INNER JOIN cpu AS c ON (a.cpu = c.cpu_id)
INNER JOIN disco AS d ON (a.disco = d.disco_id)
INNER JOIN tgrafica AS e ON (a.tgrafica = e.tgraf_id);
Y para terminar ordenamos el resultado:
SELECT
a.pcid,
b.mem_tipo,
b.mem_capacidad AS mem_MB,
c.cpu_fabricante AS cpu_fab,
c.cpu_tipo,
d.disco_fabricante AS disco_fab,
d.disco_capacidad AS disco_GB,
e.tgraf_fabricante AS tgraf_fab,
a.precio
FROM pc AS a
INNER JOIN memoria AS b ON (a.memoria = b.mem_id)
INNER JOIN cpu AS c ON (a.cpu = c.cpu_id)
INNER JOIN disco AS d ON (a.disco = d.disco_id)
INNER JOIN tgrafica AS e ON (a.tgrafica = e.tgraf_id)
ORDER BY precio DESC;
El resultado de nuestra consulta presentará las características de solo los PC completos:
pcid | mem_tipo | mem_mb | cpu_fab | cpu_tipo | disco_fab | disco_gb | tgraf_fab | precio
------+------------+--------+---------+------------+-----------+----------+-----------+--------
4 | DDR3 SDRAM | 2048 | amd | Athlon X2 | samsung | 500 | nvidia | 1600
3 | DDR2 SDRAM | 1024 | intel | Core2 Quad | seagate | 1024 | nvidia | 1400
2 | DDR SDRAM | 1024 | intel | Core2 duo | seagate | 500 | ati | 1100
1 | DDR SDRAM | 1024 | intel | Core2 duo | seagate | 350 | ati | 1000
(4 rows)
*Consulta 2
Obtener una relación de todos los modelos de PC a la venta. Queremos toda la informacion disponible sobre los componentes que lo forman. Ordenar el resultado de mayor a menor precio.
En esta consulta nos piden lo mismo que la consulta 1 pero de todos los modelos de PC, los completos y los que se venden sin algun componente. La tabla PC es la tabla principal de nuestra combinación, y la tabla a la que le faltan valores en ciertos atributos en algunas tuplas. Esta tabla es la primera que se define cuando definimos las cláusulas JOIN y por definición es la que se encuentra más a la izquierda. Por ello utilizaremos el tipo LEFT OUTER JOIN para conseguir el resultado de la consulta 1, mas todos los PC a los que le falta algún componente.
Lo único que tenemos que hacer es cambiar INNER JOIN por LEFT OUTER JOIN. La consulta quedaria asi:
SELECT
a.pcid,
b.mem_tipo,
b.mem_capacidad AS mem_MB,
c.cpu_fabricante AS cpu_fab,
c.cpu_tipo,
d.disco_fabricante AS disco_fab,
d.disco_capacidad AS disco_GB,
e.tgraf_fabricante AS tgraf_fab,
a.precio
FROM pc AS a
LEFT OUTER JOIN memoria AS b ON (a.memoria = b.mem_id)
LEFT OUTER JOIN cpu AS c ON (a.cpu = c.cpu_id)
LEFT OUTER JOIN disco AS d ON (a.disco = d.disco_id)
LEFT OUTER JOIN tgrafica AS e ON (a.tgrafica = e.tgraf_id)
ORDER BY precio DESC;
Y el resultado de nuestra consulta presentará las características de todos los PC:
pcid | mem_tipo | mem_mb | cpu_fab | cpu_tipo | disco_fab | disco_gb | tgraf_fab | precio
------+------------+--------+---------+------------+-----------+----------+-----------+--------
4 | DDR3 SDRAM | 2048 | amd | Athlon X2 | samsung | 500 | nvidia | 1600
3 | DDR2 SDRAM | 1024 | intel | Core2 Quad | seagate | 1024 | nvidia | 1400
2 | DDR SDRAM | 1024 | intel | Core2 duo | seagate | 500 | ati | 1100
1 | DDR SDRAM | 1024 | intel | Core2 duo | seagate | 350 | ati | 1000
5 | | | intel | Core2 duo | seagate | 350 | ati | 900
6 | | | | | | | ati | 400
(6 rows)
Consulta 3
Obtener una relación de solo los modelos de ‘PC NO completos’ a la venta. Queremos toda la información disponible sobre los componentes que lo forman
Si os fijais esta consulta la podriamos obtener si al resultado que muestra todos los PCs, le ‘restamos’ el resultado con solo los PCs completos. Esto lo podriamos realizar combinando la consulta 1 con la consulta 2 mediante el operador EXCEPT (consulta 2 EXCEPT consulta 1):
(
SELECT
a.pcid,
b.mem_tipo,
b.mem_capacidad AS mem_MB,
c.cpu_fabricante AS cpu_fab,
c.cpu_tipo,
d.disco_fabricante AS disco_fab,
d.disco_capacidad AS disco_GB,
e.tgraf_fabricante AS tgraf_fab,
a.precio
FROM pc AS a
LEFT OUTER JOIN memoria AS b ON (a.memoria = b.mem_id)
LEFT OUTER JOIN cpu AS c ON (a.cpu = c.cpu_id)
LEFT OUTER JOIN disco AS d ON (a.disco = d.disco_id)
LEFT OUTER JOIN tgrafica AS e ON (a.tgrafica = e.tgraf_id)
)
EXCEPT
(
SELECT
a.pcid,
b.mem_tipo,
b.mem_capacidad AS mem_MB,
c.cpu_fabricante AS cpu_fab,
c.cpu_tipo,
d.disco_fabricante AS disco_fab,
d.disco_capacidad AS disco_GB,
e.tgraf_fabricante AS tgraf_fab,
a.precio
FROM pc AS a
INNER JOIN memoria AS b ON (a.memoria = b.mem_id)
INNER JOIN cpu AS c ON (a.cpu = c.cpu_id)
INNER JOIN disco AS d ON (a.disco = d.disco_id)
INNER JOIN tgrafica AS e ON (a.tgrafica = e.tgraf_id)
)
ORDER BY precio DESC;
El resultado seria el esperado:
pcid | mem_tipo | mem_mb | cpu_fab | cpu_tipo | disco_fab | disco_gb | tgraf_fab | precio
------+----------+--------+---------+-----------+-----------+----------+-----------+--------
5 | | | intel | Core2 duo | seagate | 350 | ati | 900
6 | | | | | | | ati | 400
(2 rows)
Consulta 4
Obtener una relación de todos los PC que tengan CPUs de AMD y discos Samsung. Queremos toda la información disponible sobre los componentes que lo forman
El trabajo para definir esta consulta está casi hecho. Lo único que tenemos que hacer es aplicar mediante la sentencia WHERE, las dos condiciones que nos piden, a la consulta 2:
SELECT
a.pcid,
b.mem_tipo,
b.mem_capacidad AS mem_MB,
c.cpu_fabricante AS cpu_fab,
c.cpu_tipo,
d.disco_fabricante AS disco_fab,
d.disco_capacidad AS disco_GB,
e.tgraf_fabricante AS tgraf_fab,
a.precio
FROM pc AS a
LEFT OUTER JOIN memoria AS b ON (a.memoria = b.mem_id)
LEFT OUTER JOIN cpu AS c ON (a.cpu = c.cpu_id)
LEFT OUTER JOIN disco AS d ON (a.disco = d.disco_id)
LEFT OUTER JOIN tgrafica AS e ON (a.tgrafica = e.tgraf_id)
WHERE c.cpu_fabricante = 'amd'
AND d.disco_fabricante = 'samsung'
ORDER BY precio DESC;
Y el resultado quedaria asi:
pcid | mem_tipo | mem_mb | cpu_fab | cpu_tipo | disco_fab | disco_gb | tgraf_fab | precio
------+------------+--------+---------+-----------+-----------+----------+-----------+--------
4 | DDR3 SDRAM | 2048 | amd | Athlon X2 | samsung | 500 | nvidia | 1600
(1 row)
Consulta 5
Obtener una relación del numero de PCs que tienen CPUs de Intel y de AMD. Ordenar de mayor a menor.
Esta consulta es un poco diferente a las anteriores. Aquí tenemos que agrupar los resultados según el fabricante de la CPU utilizada, y contar cuantas tuplas existen en cada grupo. Al necesitar solamente información contenida en la tabla ‘CPU’, solo tendremos que combinar la tabla ‘PC’ con la tabla ‘CPU’.
Como queremos obtener solamente los PC que tienen algun tipo de CPU, utilizaremos un INNER JOIN para descartar los PCs sin CPU definida.
SELECT *
FROM pc AS a
INNER JOIN cpu AS b ON (a.cpu = b.cpu_id);
A continuación vamos a agrupar el resultado obtenido segun el fabricante de la CPU y contaremos el número de tuplas en cada grupo:
SELECT
b.cpu_fabricante,
count(*) AS total
FROM pc AS a
INNER JOIN cpu AS b ON (a.cpu = b.cpu_id)
GROUP BY b.cpu_fabricante;
Y ordenamos el resultado:
SELECT
b.cpu_fabricante,
count(*) AS total
FROM pc AS a
INNER JOIN cpu AS b ON (a.cpu = b.cpu_id)
GROUP BY b.cpu_fabricante
ORDER BY total DESC;
El resultado obtenido seria:
cpu_fabricante | total
----------------+-------
intel | 4
amd | 1
(2 rows)
En fin, espero que os hagais una idea de como funciona el tema de combinar diferentes tablas para obtener un resultado. En la vida real os encontrareis con ejemplos bastantes complicados, lo importante es pensar la estrategia a seguir antes de empezar a escribir la consulta SQL. También es importante hacerlo paso a paso, aplicando las restricciones necesarias hasta conseguir lo que queremos. Solamente la práctica os ayudará a entender y escribir con soltura consultas complejas.
Enlaces: