DML / SELECT

Consultes unitaula

L’estructura bàsica d’una consulta (query) és:

SELECT * FROM nomTaula WHERE condició;

Select * From comandes where data_comanda > 20210101;

Quan no necessitem totes les columnes es diu que projectem els atributs i per tant escurcem la taula de forma horitzontal. Si a més a més la volem escurçar horitzontalment perquè volem registrar la sortida de registres, ho podem fer amb la clàusula LIMIT (offset, long) o simplement LIMIT(long) iniciant des de 0

Select num_comanda, data_comanda, clie, ven From comandes where data_comanda > 20210101 LIMIT 3;

Consultes multi-taula

Podem completar-la amb l’accés a dues o més taules (multi-taula)

SELECT * FROM taula1 t1 JOIN taula2 t2 ON(t1.column1=t2.column2) WHERE condicióGeneral GROUP BY taula.columna HAVING condicióAgrupació;

SELECT ven_clie, societat, idioma, num_comanda, data_comanda, clie, ven, COUNT(*) AS NumComandes FROM comandes co JOIN clients cl ON(co.ven = cl.ven_clie) WHERE YEAR(data_comanda) > 2020 GROUP BY ven_clie;

Subconsultes

A vegades necessitarem accedir a més d’una taula, però per obtenir només alguna dada que necessitem en la nostra consulta, per tant tindrem una consulta general on hi podrem tenir subconsultes.

SELECT * FROM taula1 t1 JOIN taula2 t2 ON(t1.column1=t2.column2) WHERE condició1 AND atribut IN (condició2 com sub-consulta) GROUP BY taula.columna HAVING condicióAgrupació;

En la següent consulta, basada en l’anterior, voldrem obtenir el resultat que teníem però només d’aquelles comandes on el venedor assignat NO té el títol de ‘Venedor’. Necessitem l’operador lògic AND per ampliar la condició i una subconsulta que torni els codis de venedors que el seu títol sigui diferent (<>) de ‘Venedor’. La clàusula IN funciona com un OR múltiple sobre les dades que rep de la subconsulta i que compara amb l’atribut ven_clie.

SELECT ven_clie, societat, idioma, num_comanda, data_comanda, clie, ven, COUNT(*) AS NumComandes FROM comandes co JOIN clients cl ON(co.ven = cl.ven_clie) WHERE YEAR(data_comanda) > 2020 AND ven_clie IN (Select num_ven FROM venedors WHERE titol <> 'Venedor') GROUP BY ven_clie;

Consultes amb BBDD hogwarts

Estudiants de tercer any de la casa Gryffindor.

Select * From students where house_id = (Select id From houses where name = 'Gryffindor') AND year = 3;
El professor/a responsable i l'assignatura que imparteix dels estudiants de tercer any de la casa Gryffindor

SELECT DISTINCT t.name, c.subject FROM teachers t JOIN (Select * From students where house_id = (Select id From houses where name = 'Gryffindor') AND year = 3) as sh USING(house_id) JOIN classes c ON(t.id=c.teacher_id);
Assignatures que estudien els estudiants de tercer any de la casa 'Gryffindor'.

SELECT DISTINCT subject From class_rosters cr JOIN (Select * From students where house_id = (Select id From houses where name = 'Gryffindor') AND year = 3) as sh ON(cr.student_id=sh.id) JOIN classes c ON(cr.class_id=c.id);
Assignatures que estudien els estudiants de tercer any de la casa 'Gryffindor' pero que no sigui la que imparteix el professorat responsable de Gryffindor.

SELECT DISTINCT subject From class_rosters cr \
  JOIN \
    (Select * From students where house_id = (Select id From houses where name = 'Gryffindor') AND year = 3) AS sh \
    ON(cr.student_id = sh.id) \
  JOIN classes c ON(cr.class_id=c.id) \
  WHERE c.id NOT IN (Select id FROM classes where teacher_id = \
    (Select id From teachers where house_id = \
      (Select id FROM houses where name = 'Gryffindor')
  )
);
Projecta el nom de cada casa de Hogwarts i el numero d'alumnes que tenen.

Select h.name, COUNT(*) AS Estudiants FROM students s JOIN houses h ON(s.house_id=h.id) GROUP BY s.house_id;

Consultes amb BBDD starwars

Nom de l'episodi i títol de les pel·lícules on apareix 'Luke Skywalker'.

Select episode, title FROM people p JOIN people_films pf ON(p.id=pf.id_people) JOIN films f ON(pf.id_film=f.id AND pf.id_people=p.id) WHERE p.name LIKE 'Luke%';
Nom de l'episodi i títol de les pel·lícules on apareix 'Luke Skywalker', però només utilitzant subconsultes.

Select episode,title From films where id IN (Select id_film From (Select * From people_films where id_people = (Select id From people where name like 'Luke%')) AS t1);
En quins episodis i títols de pel·lícules apareixen Luke i Chewbacca.

La consulta prèvia seria:

Select episode, title, name FROM people p JOIN people_films pf ON(p.id=pf.id_people) JOIN films f ON(pf.id_film=f.id AND pf.id_people=p.id) WHERE p.name LIKE 'Luke%' OR name LIKE 'Chew%';
La resposta final és:

SELECT * From (SELECT episode, title, name FROM people p JOIN people_films pf ON(p.id=pf.id_people) JOIN films f ON(pf.id_film=f.id AND pf.id_people=p.id) WHERE p.name LIKE 'Luke%') AS f1 JOIN (SELECT episode, title, name FROM people p JOIN people_films pf ON(p.id=pf.id_people) JOIN films f ON(pf.id_film=f.id AND pf.id_people=p.id) WHERE p.name LIKE 'Chew%') AS f2 USING(episode, title);
En quins episodis i títols de pel·lícules apareix Chewbacca però no apareix Luke Skywalker.

SELECT * From (SELECT episode, title, name FROM people p JOIN people_films pf ON(p.id=pf.id_people) JOIN films f ON(pf.id_film=f.id AND pf.id_people=p.id) WHERE p.name LIKE 'Luke%') AS f1 RIGHT JOIN (SELECT episode, title, name FROM people p JOIN people_films pf ON(p.id=pf.id_people) JOIN films f ON(pf.id_film=f.id AND pf.id_people=p.id) WHERE p.name LIKE 'Chew%') AS f2 USING(episode, title) WHERE f1.name IS NULL;
Disposant de la següent informació de temps (any d'estrena dels films i la seva durada) actualitza la taula films
Font: https://www.radiotimes.com/movies/how-to-watch-the-star-wars-movies-in-the-right-order/

En primer lloc hem d’afegir les columnes release_date i minutes, les dues de tipus INTEGER i no negatiu

ALTER TABLE films ADD COLUMN release_date INT UNSIGNED, ADD COLUMN minutes INT UNSIGNED;

UPDATE films SET release_date = 1999 WHERE id=1;
UPDATE films SET minutes = 136 WHERE id=1;

etc…

O tot de cop amb CASE

També podem fer servir l’opció REPLACE que equival a un INSERT amb substitució.

Crearem una taula clonada de films a la que anomenarem temp

CREATE table temp like films;
INSERT INTO temp (id,episode,title) Select id,episode,title from films;

Opció A

REPLACE INTO temp VALUES
(1, 'Episode I', 'The Phantom Menace' ,1999,136),
(2, 'Episode II', 'Attack of the Clones' ,2002,142)
-- i així successivament
;

Opció B

També podem fer servir una consulta amb UNION sobre la mateixa taula.

Deixa un comentari