Consultes Postgres

De FFAWiki

Postgres

Composició d'una consulta:
SELECT camp1/expressió, camp2/expressió, camp3/expressió, ..,campn/expressió FROM taula WHERE condició_cerca GROUP BY camp1, camp2 , ..., campn HAVING condicio_sobre_grups ORDER BY camp1 [ASC|DESC], camp2 [ASC|DESC], ..., campm [ASC|DESC] LIMIT n

Links de descarga de les bases de dades en les que es fan els exemples

Descargar base de dades sanitat

Descargar base de dades sanitat2 (normalitzada)

Descargar base de dades videoclub

Descargar base de dades toctag (posa sql pero es un dump)

Descargar base de dades toctag (Amb mes dades)

Descargar base de dades empresa

Importar Base de Dades

SELECT

Definició:
Mostra els camps que volem que ens tregui per pantalla, s'utilitza per seleccionar dades d'una base de dades. Les dades retornades s’emmagatzemen en una taula de resultats, anomenada conjunt de resultats o result-set en anlges.
Exemple: (Base de dades sanitat)
SELECT * FROM doctor;
En aquest cas no tenim filtre en el SELECT per lo que mostra tots els camps de la taula doctor

Eines que es poden utilitzar:

COUNT(*)

Definició:
Compta les files que s’imprimeixen, en general la funció COUNT() retorna el nombre de files que coincideix amb un criteri especificat.
Exemple: (Base de dades sanitat)
SELECT count(especialitat) FROM doctor WHERE especialitat='Pediatria';
Conta totes aquelles especialitats dels doctors les quals son Pediatria.


DISTINCT

Definició:
Agrupa el camp que s’especifiqui traient els que estan repetits (si s’han de mostrar més d’un camp no es molt recomanat, millor GROUP BY)
Exemple: (Base de dades sanitat)
SELECT DISTINCT(especialitat) FROM doctor;
Mostra les especialitats de la taula doctors que son diferents (Per dir d'alguna manera les agrupa).


DATE_PART(,)

Definició:
Extreu de tot aquell camp o valor de tipus (datetime, date, time...).
Maneres de fer-lo servir:
date_part('field', source)
field = microseconds | milliseconds | second | minute | hour | day | week | month | quarter (Quart d'un any) | year | decade (Decada, cada 10) | century (secle, cada 100) | millennium (mileni, cada 1000)
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); -> Retorna: 16 (Quan no li fiques un camp de la base de dades s'ha d'especificar que és el valor que li poses en aquest cas un temps)
SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); -> Retorna: 4 (Quan no li fiques un camp de la base de dades s'ha d'especificar que és el valor que li poses en aquest cas un interval)
Exemple: (Base de dades empresa)
SELECT DATE_PART('YEAR',com_data) FROM comanda; -> Retorna: tots els anys de la columne com_data de la taula comanda (En aquest cas s'utilitzen dades d'una taula)
Expressions:
CURRENT_DATE → Data actual
current_time → Hora actual
current_timestamp → Data i hora actual
AGE() → Treu un interval amb l'edat, despres amb el DATE_PART('YEAR', AGE(nom_camp)), pots treure els anys de l'edat (si el camp no esta definit com a data hs de posar DATE_PART('YEAR', AGE('8/7/2002')::date)).

COALESCE

Definició:
Mostra el primer valor de la llista (COALESCE(comissio,0), el que esta en nagreta) es la llista separats per coma que no sigui null
Exemple: (base de dades empresa)
Coalesce-ex.jpeg















En aquest cas volem sumar el salari mes la comisio, pero no tots tenen comisio (son camps nulls) per tan utilitzarem COALESCE que el que fa es agafar el pimer valor que no sigui null de la llista COALESCE(comissio,0), el que esta en nagreta es la llista.
SELECT cognom,salari+COALESCE(comissio,0) as sou FROM emp;

UPPER()

Definició:
Passa tot el text entre parèntesis a majúscules, també poden ser camps d’una taula
Exemple:
Exemple amb un text qualsevol:
SELECT UPPER('hola, estas a ffawiki') as TEXT; -> Resultat: HOLA, ESTAS A FFAWIKI
Exemple amb un camp d'una taula: (base de dades empresa)
SELECT UPPER(cognom) as COGNOM FROM emp;

LOWER()

Definició:
Passa tot el text entre parèntesis a minúscules, també poden ser camps d’una taula
Exemple:
Exemple amb un text qualsevol:
SELECT UPPER('HOLA, ESTAS A FFAWIKI') as TEXT; -> Resultat: hola, estas a ffawiki
Exemple amb un camp d'una taula: (base de dades empresa)
SELECT LOWER(cognom) as COGNOM FROM emp;

CASE (CASE camp1 WHEN valor1 THEN mostra1 WHEN valor2 THEN mostra2 ELSE mostra3 END)

Definició:
Si cambia un valor del camp especificat si surt entre els valors especificats (when valor) per el que se li digui (then valor) , per acabar s’ha de posar END i tancar parèntesis. L'ELSE no es obligatori
Exemple: (Base de dades videoclub)
SELECT codipeli,titol,(case codigenere when 1 then 'Brus Lee' when 2 then 'Stalone' when 3 then 'Al pacino' END) from pelicula;
Enves de dirte un numero et diu el nom d'un actor
Case-ex.jpeg










SUM()

Definició:
Suma tots els camps o valors entre parèntesis separats per comes.
Exemple: (Base de dades Sanitat)
Sumarem els llits que te cada hospital
SELECT hospital_cod,sum(qtat_llits) as llits FROM sala GROUP BY hospital_cod;

AVG()

Definició:
Fa la mitjana dels camps o valors entre parèntesis separats per comes
Exemple: (Base de dades empresa)
Fa la mitjana dle que cobren tots els empleats
SELECT ofici,AVG(salari) FROM emp GROUP BY ofici;

AS

Definició:
A l’hora de mostrar camps que tinguin alguna funció d’agregació si es posa (camp as alies) quan mostri les files el nom de la fila serà l’alies especificat
Exemple: (Base de dades empresa)
Posem un alies (mitjana_sou) a la mitjana dels salaris
SELECT ofici,AVG(salari) AS mitjana_sou FROM emp GROUP BY ofici;

FROM

Definició:
Taula a on bucar els camps
Exemple: (Base de dades empresa)
Mostra els cognoms de la taula emp.
SELECT cognom FROM emp;

JOIN

Definició:
Ajunta taules de diferentes maneres: Left Join, Left Outer Join, Inner Join, Right Join, Right Outer Join, Full Join, Full Outer Join.
Exemple: (Base de dades sanitat)
Mostra per els noms de l'hospital els nomes de totes les seves sales
SELECT hospital.nom,sala.nom FROM sala INNER JOIN hospital ON sala.hospital_cod=hospital.hospital_cod;
Join-ex.jpeg













Imatge de Tots els Joins
Join.png
























Inner Join

Definició:
Uneix dos (pots fer de dos en dos) taules pels camps que tenen iguals (tants iguals com vulguis), i nomes s'utilitzen els tu especifiques en el SELECT.
Exemple: (Base de dades toctag)
Mostra el nom del cicle (c) i el nom de la família professional (f) on la id de la família coincideixi amb la id de la manilia de cicle
SELECT c.descripcio,f.descripcio FROM cicle_formatiu c INNER JOIN familia_professional f ON c.familia_id=f.familia_id;
Inner-Join-ex.jpeg













NATURAL Join

Definició:
Substitueix l'ON del INER JOIN fen la compraració per si sol dels camps que hi han en comu (perque son iguals).
Exemple: (Base de dades toctag)
Mostra la relació entre els alumnes i les axpulsions.
SELECT * FROM alumnes NATURAL JOIN expulsions;
NATURAL-join-ex.jpeg


















Right Join

Definició:
Agafa la taula de la dreta i els camps en comú
Exemple: (Base de dades toctag)
Mostra el titol de la pelicula i si esta llogada o no.
SELECT pelicula.titol,(CASE count(prestec.codipeli) WHEN 0 THEN 'No Llogada' ELSE 'Llogada' END) as prestec FROM prestec RIGHT JOIN pelicula ON prestec.codipeli=pelicula.codipeli GROUP BY pelicula.titol;
Right-Join-ex.jpeg



















Left Join

Definició:
Agafa la taula de la esquerra i els camps en comú
Exemple: (Base de dades toctag)
Mostra el titol de la pelicula i si esta llogada o no.
SELECT titol FROM pelicula p LEFT JOIN prestec t ON p.codipeli=t.codipeli WHERE t.codipeli IS NULL;
Left-Join-is-NULL-ex.jpeg























Right Join is NULL

Definició:
Agafa la taula de la dreta pero no els camps en comú
Exemple: (Base de dades toctag)
Mostra les pelicules no llogades
SELECT titol FROM prestec t RIGHT JOIN pelicula p ON p.codipeli=t.codipeli WHERE t.codipeli IS NULL;
Right-Join-is-NULL-ex.jpeg























Left Join is NULL

Definició:
Agafa la taula de la esquerra pero no els camps en comú
Exemple: (Base de dades toctag)
Mostra les pelicules no llogades
SELECT titol FROM pelicula p LEFT JOIN prestec t ON p.codipeli=t.codipeli WHERE t.codipeli IS NULL;
Left-Join-is-NULL-ex.jpeg























Full Join

Definició:
Agafa totes les taules
Exemple: (Base de dades toctag)
Mostra les dos taules, si esta a les dos et fa una linea amb les dades de les dos taules, i si esta només en una mostra una linea amb les dades d'aquella unica taula en la qual esta. (El LIMIT es per que no surti una gran llista)
SELECT * FROM expulsions ex FULL JOIN entrades_tard et ON ex.dni=et.dni LIMIT 15;
Full-Join-ex.jpeg
















WHERE

Definició:
Condicions que han de complir les files per a que les mostri per pantalla
Exemple: (Base de dades empresa)
Mostra els cognoms i el salari de aquella gent que el seu salari es superior a 200.000
SELECT cognom,salari FROM emp WHERE salari>200000;

Eines que es poden utilitzar:

Subconsultes

Definició:
Es pot utilitzar una consulta per tal de fer un filtre o condicio amb dades d'alguna altre taula relacionada o de la propia
Exemple:
Subconsulta taula relacionada (Base de dades toctag)
Mostra tots aquells dni i noms d'alumnes de la taula algumnes que coincideixen amb els dni de la taula d'expulsions.
SELECT dni,alumne FROM alumnes WHERE dni IN(SELECT DISTINCT(dni) FROM expulsions);
Subconsulta en la mateixa taula (Base de dades empresa)
Mostra els treballadors que el seu numero d'empleat de la taula emp coincideix amb la llista de numeros de empleats que son caps.
SELECT cognom FROM emp WHERE emp_no IN(SELECT distinct(cap) FROM emp);

AND

Definició:
El seu us es unir varies conducions o filtres.
Exemple: (Base de dades empresa)
Mostra aquells salaris que son mes grans de 200.000 i mes petits a 300.000
SELECT cognom,salari FROM emp WHERE salari>200000 AND salari<300000;

IN/NOT IN

Definició:
Comproba si esta dins de la llista que se li especifiqui entre parèntesi IN(), on esta la nagreta es on va la llista.
Exemple: (Base de dades toctag)
IN
Exemple amb subcunsulta
Mostra tots aquells dni i noms d'alumnes de la taula algumnes que coincideixen amb els dni de la taula d'expulsions.
SELECT dni,alumne FROM alumnes WHERE dni IN(SELECT DISTINCT(dni) FROM expulsions);


Exemple amb la mateixa taula
Mostra tots aquells treballadors que tenen la categoria empleat i venedor
SELECT cognom,ofici FROM emp WHERE ofici IN('empleat','venedor');
NOT IN
Exemple amb la mateixa taula
Mostra tots aquells treballadors que no tenen la categoria empleat i venedor
SELECT cognom,ofici FROM emp WHERE ofici NOT IN('empleat','venedor');

EXISTS/NOT EXISTS

Definició:
Fa una subconsulta i l'exists segons si troba algo o no, torna true o false i en el cas que dongui true imprimeix. (S'utilitza en subconsultes)
Exemple:
Mostra totes les columnes de la taula expulsions, pero nomes mostra les dades quan el dni de la taula alumnes el qual esta fent quart de l'ESO i si auqest mateix dni coincideix amb el dni de la taula d'expulsions.
SELECT * FROM expulsions WHERE EXISTS (SELECT dni FROM alumnes WHERE estudis like 'ESO4%' and dni=expulsions.dni);

Operadors

Definició:
És una funció que realitza algun tipus d'operació en un nombre, variable o funció
+
+
Definició:
suma
-
-
Definició:
resta
=
=
Definició:
igual
<
<
Definició:
Més petit que
>
>
Definició:
Més gran que
!=
!=
Definició:
No igual
LIKE / NOT LIKE
LIKE / NOT LIKE
Definició:
Compara un camp o un valor amb un patro. (no va amb integers)
Exemple:
Mostra els alumnes que estan cursant l'ESO.
SELECT dni,alumne FROM alumnes WHERE estudis LIKE 'ESO%';
Eines: _ / %
_ / %
_ : S'utilitza per calcular quin caracter exacte del parametre vols que sigui igual
% : S'ho traga tot, per exemple si vols bucar una parula que conte una x seria '%x%'

ALL

Definició:
L'ordre retorna true si tots els valors de la subqueria compleixen la condició. Necessita sempre un comarador devant.
Exemple: (Base de dades empresa)
Mostra cognom d'aquella persona que te el salari mes alt de tots.
SELECT cognom FROM EMP WHERE salari >= ALL(SELECT salari FROM emp);

ANY/SOME

Definició:
ANY: qualsevol
SOME: algun
Si (qualsevol/algun) coincideix amb la llista s'imprimeix. ANY() SOME(), en la nagreta es on es posa la llista.
Exemple:
Mostra els alumnes amb estudis DAM
SELECT alumne,estudis FROM alumnes WHERE estudis = SOME(SELECT estudis FROM alumnes WHERE estudis like 'DAM%');
SELECT alumne,estudis FROM alumnes WHERE estudis = ANY(SELECT estudis FROM alumnes WHERE estudis like 'DAM%');

GROUP BY

Definició:
Agrupa els valors que coincideixen amb la mateixa columna.
Exemple: (Base de dades sanitat)
Agrupa els codis de l'hopital iguals i conta quantes sales hi ha per hospital.
SELECT hospital_cod,count(sala_cod) FROM sala GROUP BY hospital_cod;

HAVING

Definició:
Condició que s’aplica un cop agrupats els camps.
Exemple: (Base de dades sanitat)
Agrupa els codis de l'hopital iguals i conta quantes sales hi ha per hospital pero nomes mostra si hi ha mes de 2 sales.
SELECT hospital_cod,count(*) FROM sala GROUP BY hospital_cod HAVING count(*)>2;

ORDER BY

Definició:
Mostra les files ordenades per els camps que s’hi posin.
Exemple: (Base de dades sanitat)
Ordena els codis hospital
SELECT hospital_cod,sala_cod FROM sala ORDER BY hospital_cod;

DESC

Definició:
Fa que l’ordre sigui descendent.
Exemple:
Ordena de manera descendent els codis hospital
SELECT hospital_cod,sala_cod FROM sala ORDER BY hospital_cod DESC;

LIMIT

Definició:
Mostra les files fins a arribar al número de files que hi especifiquem. Es sol fer servir amb l'ORDER BY
Exemple: (Base de dades sanitat)
SELECT hospital_cod,count(*) FROM sala GROUP BY hospital_cod ORDER BY hospital_cod DESC LIMIT 1;
Captura sense LIMIT
Sense-limit-ex.jpeg









Captura amb LIMIT
Limit-ex.jpeg






UNION

Definició:
L'UNIN l'ajunta com el FULL JOIN pero no son el mateix (fa falta el mateix camp a les dos taules)
Exemple: (Base de dades toctag)
Mostra els alumnes de batxillerat i els alumnes de ESO
SELECT alumne,estudis FROM alumnes WHERE estudis like 'ESO%' UNION SELECT alumne,estudis FROM alumnes WHERE estudis like 'BAT%';
UNION-ex.jpeg












INTERSECT

Definició:
L'INTERSECT l'ajunta com el INNER JOIN pero no son el mateix (fa falta el mateix camp a les dos taules)
Exemple: (Base de dades toctag)
Mostra aquelles persones que han arribat tard i tambe han sigut expulsades.
SELECT dni FROM entrades_tard INTERSECT SELECT dni FROM expulsions;
INTERSECT-ex.jpeg




















EXCEPT

Definició:
Agafa la primera consulta i li treu a la segona consulta (Una resta)
Exemple: (Base de dades toctag)
Agafa totes els expulsats i treu els que han arribat tard.
SELECT dni FROM expulsions EXCEPT SELECT dni from entrades_tard;
EXCEPT-ex.jpeg