Consultes Postgres

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)

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

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;

Imatge de Tots els Joins

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;

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;

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;

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;

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;

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;

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;

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

Captura amb LIMIT

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%';

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;

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;