Consultes SQL bàsiques per a estudiants: guia completa

1. Mostrar DNI, nom i edat per a cada estudiant

SELECT dni, nom, edat FROM dadesestudiant;

2. Mostrar ordenat per nom, el DNI, nom i edat de cada estudiant

SELECT dni, nom, edat FROM dadesestudiant ORDER BY nom;

3. Mostrar DNI, nom i edat per a cada estudiant major de 25 anys

SELECT dni, nom, edat FROM dadesestudiant WHERE edat > 25;

4. Mostrar la quantitat d'estudiants en la base de dades

SELECT count(*) AS number_of_estudiants FROM dadesestudiant;

5. Mostrar la quantitat d'estudiants en la base de dades que tinguen més de 25 anys

SELECT count(*) AS estudiants_majors_de_25 FROM dadesestudiant WHERE edat > 25;

6. Mostrar la quantitat de telèfons en la base de dades

SELECT count(*) AS number_of_telèfons FROM telefon;

7. Mostrar la suma i la mitjana d'edat, usa ALIAS

SELECT sum(edat) AS edat_sum, avg(edat) AS edat_mitjana FROM dadesestudiant;

8. Mostrar només els 3 estudiants més majors

SELECT * FROM dadesestudiant ORDER BY edat DESC LIMIT 3;

9. Mostrar només el 2n estudiant més major

SELECT * FROM dadesestudiant ORDER BY edat DESC LIMIT 1, 1;

10. Mostrar només en una fila (usant només una select) l'edat més menuda, la més gran, comptar la quantitat d'estudiants en la base de dades, i la mitjana d'edat. Usa ALIAS

SELECT min(edat) AS edat_min, max(edat) AS edat_max, count(*) AS estudiant_nombre, avg(edat) AS edat_mitjana FROM dadesestudiant;

11. Mostrar la quantitat d'estudiants que viuen en cada ciutat (GROUP BY)

SELECT count(*), cp FROM dadesestudiant GROUP BY cp;

12. Mostrar tots els estudiants que viuen en Barcelona (Producte cartesià)

SELECT * FROM dadesestudiant, poblacio WHERE dadesestudiant.cp = poblacio.cp AND ciutat = 'Barcelona';

13. Mostrar tota la informació de tots els estudiants, incloent-hi els telèfons però no la ciutat

SELECT * FROM dadesestudiant, telefon WHERE dadesestudiant.exp = telefon.exp;

14. Mostrar tota la informació de tots els estudiants, incloent-hi la ciutat però no els telèfons

SELECT * FROM dadesestudiant, poblacio WHERE dadesestudiant.cp = poblacio.cp;

15. Mostrar tota la informació continguda en la base de dades de l'estudiant amb DNI = '12345678A' (no mostrar les notes)

SELECT * FROM dadesestudiant, poblacio, telefon WHERE dadesestudiant.cp = poblacio.cp AND dadesestudiant.exp = telefon.exp AND dni = '12345678A';

16. Mostrar tots els estudiants amb telèfon

SELECT exp FROM telefon WHERE telefon IS NOT NULL;

o

SELECT * FROM dadesestudiant, telefon WHERE dadesestudiant.exp = telefon.exp AND telefon IS NOT NULL;

17. Mostrar el nom dels estudiants que no tinguen telèfon

SELECT nom FROM dadesestudiant, telefon WHERE dadesestudiant.exp = telefon.exp AND telefon IS NULL;

18. Mostrar exp i nom de tots els estudiants que viuen en London i siguen majors d'edat

SELECT exp, nom FROM dadesestudiant, poblacio WHERE dadesestudiant.cp = poblacio.cp AND ciutat = 'London' AND edat >= 18;

Funcions de cadenes de caràcters: CONCAT, UCASE, UPPER, LCASE, LOWER, TRIM, LTRIM, RTRIM, LENGTH, RIGHT, LEFT, REPLACE, ASCII, CHAR, SPACE, REPEAT, STRCMP

Use of [NOT] LIKE, % i _

19. Mostrar tots els estudiants que el seu nom comence per 'Ja'

SELECT * FROM dadesestudiant WHERE nom LIKE 'Ja%';

20. Mostrar tots els estudiants que el seu nom acabe amb 'as'

SELECT * FROM dadesestudiant WHERE nom LIKE '%as';

21. Mostrar tots els estudiants que el seu nom continga 'vi'

SELECT * FROM dadesestudiant WHERE nom LIKE '%vi%';

22. Mostrar tots els telèfons que comencen per '96'

SELECT * FROM telefon WHERE telefon LIKE '96%';

23. Mostrar tots els telèfons que NO comencen per '965'

SELECT * FROM telefon WHERE telefon NOT LIKE '965%';

24. Mostrar tots els telèfons que NO acaben amb '00'

SELECT * FROM telefon WHERE telefon NOT LIKE '%00';

25. Mostrar tots els telèfons que continguen '00'

SELECT * FROM telefon WHERE telefon LIKE '%00%';

26. Mostrar tots els telèfons que comencen per '96', després tinguen un altre número, que el 4t siga un 5, i que l'últim número siga un 1

SELECT * FROM telefon WHERE telefon LIKE '96_5%1';

27. Mostrar l'adreça completa (carrer, número i codi postal) en un únic camp. (Usa CONCAT)

SELECT concat(carrer, ',', number, ' ', cp) AS complete_adreça FROM dadesestudiant;

28. Mostrar l'adreça completa: carrer, número, cp, ciutat en un únic camp

SELECT concat(carrer, ',', number, ' ', poblacio.cp, '-', ciutat) AS complete_adreça FROM dadesestudiant, poblacio WHERE dadesestudiant.cp = poblacio.cp;

Ús de UCASE, UPPER, LCASE, LOWER

29. Mostrar nom en majúscules

SELECT UPPER(nom) FROM dadesestudiant; o SELECT UCASE(nom) FROM dadesestudiant;

30. Mostrar nom en minúscules

SELECT LOWER(nom) FROM dadesestudiant; o SELECT LCASE(nom) FROM dadesestudiant;

Use of TRIM, LTRIM, RTRIM

31. Mostrar els noms dels estudiants sense espais blancs a l'esquerre

SELECT LTRIM(nom) FROM dadesestudiant;

32. Mostrar els noms dels estudiants sense espais blancs a la dreta

SELECT RTRIM(nom) FROM dadesestudiant;

33. Mostrar els noms dels estudiants sense espais blancs ni davant ni darrere

SELECT TRIM(nom) FROM dadesestudiant;

Ús de LENGTH

34. Mostrar la longitud del nom de l'estudiant amb DNI = '12345678A'

SELECT LENGTH(nom) AS length_nom FROM dadesestudiant WHERE dni = '12345678A';

35. Mostrar el nom i l'edat dels estudiants que la seua edat tinga més d'un dígit

SELECT nom, edat FROM dadesestudiant WHERE LENGTH(edat) > 1; o SELECT nom, edat FROM dadesestudiant WHERE edat >= 10;

36. Usa 'SUBSTRING(str,n)' i 'SUBSTRING(str,n,m)' en dos consultes. Abans de començar, busca informació en el manual de referència (string functions). Explica el seu funcionament.

- select els 5 primers caràcters del nom de cada estudiant

SELECT substring(nom, 1, 5) FROM dadesestudiant;

- select els 5 últims caràcters del nom de cada estudiant

SELECT substring(nom, -5) FROM dadesestudiant;

Usa RIGHT, LEFT i REPLACE

37. Substitueix el nom 'Mike' per 'Miquel'

SELECT REPLACE(nom, 'Mike', 'Miquel') FROM dadesestudiant;

NOTA: executa: select nom from dadesestudiant;, i observa els resultats.

38. Mostrar només els 3 primers caràcters de tots els noms

SELECT LEFT(nom, 3) FROM dadesestudiant;

39. Mostrar només els 3 últims caràcters del nom del primer estudiant

SELECT RIGHT(nom, 3) FROM dadesestudiant LIMIT 1;

Investiga sobre les següents funcions i posa un exemple per a cada una

•ASCII(x), CHAR(n), SPACE(n), REPEAT(str,n), STRCMP(str1,str2)

Usa GROUP BY .. HAVING

40. Mostrar quants estudiants viuen en cada població, també el nom de la població

SELECT poblacio.ciutat, poblacio.cp, count(*) AS quantitat_estudiants FROM poblacio, dadesestudiant WHERE poblacio.cp = dadesestudiant.cp GROUP BY poblacio.cp;

41. Mostrar quants estudiants viuen en cada ciutat sempre i quant aquesta quantitat siga major de 100

SELECT cp, count(*) AS num_est_pob FROM dadesestudiant GROUP BY cp HAVING count(*) > 100;

42. Mostrar el major expedient de cada població

SELECT cp, max(exp) AS max_exp FROM dadesestudiant GROUP BY cp;

43. Mostrar el major i el menor 'exp' de cada població, només per a estudiants majors de 20 anys

SELECT cp, max(exp) AS max_exp, min(exp) AS min_exp FROM dadesestudiant WHERE edat > 20 GROUP BY cp;

SUBSELECT (unió de més d'una relació)

44. Mostrar tots els estudiants que viuen en Paris

SELECT * FROM dadesestudiant WHERE cp IN (SELECT cp FROM poblacio WHERE ciutat = 'Paris');

* Aquest subselect pot donar error, que es pot resoldre usant: '... cp IN (SELECT ...)' en compte de '... cp = (SELECT ...)'

45. Mostrar quants estudiants viuen en Paris

SELECT count(*) AS Paris_num_estudiants FROM dadesestudiant WHERE cp IN (SELECT cp FROM poblacio WHERE ciutat = 'Paris');

46. Mostrar el nom de la ciutat i el nombre d'estudiants que viuen en Barcelona

SELECT STI.cp, ciutat, count(*) AS Population FROM dadesestudiant STI, poblacio WHERE STI.cp = poblacio.cp AND ciutat = 'Barcelona';

47. Mostrar tots els telèfons de l'estudiant 'Toni P'

SELECT telefon FROM telefon WHERE exp IN (SELECT exp FROM dadesestudiant WHERE nom = 'Toni P');

48. Mostrar l'adreça completa de 'Toni P'

SELECT carrer, number, STI.cp, ciutat FROM dadesestudiant STI, poblacio WHERE STI.cp = poblacio.cp AND nom = 'Toni P';

Ús de [NOT] IN

49. Mostrar tots els estudiants amb nota entre 4 i 5

SELECT * FROM dadesestudiant WHERE exp IN (SELECT exp FROM estudiant WHERE nota BETWEEN 4 AND 5);

50. Mostrar tots els estudiants amb nota: 8, 9 o 10

SELECT * FROM dadesestudiant WHERE exp IN (SELECT exp FROM estudiant WHERE nota IN (8, 9, 10));

51. Mostrar els estudiants que no tinguen cap nota igual a 1, 2, 3 o 4

SELECT * FROM dadesestudiant WHERE exp IN (SELECT exp FROM estudiant WHERE nota NOT IN (1, 2, 3, 4));

Ús de DISTINCT

52. Mostrar tots els carrers on visca algun estudiant. No mostrar carrers duplicats

SELECT DISTINCT carrer FROM dadesestudiant;

comprova:

DISTINCT (carrer)

DISTINCT carrer, cp

DISTINCT (carrer, cp)

Funcions sobre números: FORMAT(n,d), AVG(col), CEIL(n), FLOOR(n), ROUND(), Ri(), ABS(), EXP(), SIGN(), TRUNCATE(n,d)

Operator: DIV

53. Mostrar totes les notes sense decimals

SELECT TRUNCATE(nota, 0) FROM estudiant;

54. Mostrar la nota mitjana de l'estudiant amb DNI = 12345678A

SELECT AVG(nota) FROM estudiant WHERE dni = '12345678A';

55. Mostrar notes i notes arredonides al primer enter superior d'aquelles notes majors de 8

SELECT nota, CEIL(nota) FROM estudiant WHERE nota > 8;

56. Mostrar notes i notes arredonides al primer enter inferior d'aquelles notes majors que 8

SELECT nota, FLOOR(nota) FROM estudiant WHERE nota > 8;

57. Mostrar notes, notes arredonides al primer enter superior i notes arredonides al primer enter inferior d'aquelles notes majors de 8

SELECT nota, CEIL(nota), FLOOR(nota) FROM estudiant WHERE nota > 8;

58. Mostrar les notes majors de 8 arredonides

SELECT ROUND(nota) FROM estudiant WHERE nota > 8;

59. Mostrar notes, notes arredonides al primer enter superior, notes arredonides al primer enter inferior i notes arredonides, només per a notes majors de 8

SELECT nota, CEIL(nota), FLOOR(nota), ROUND(nota) FROM estudiant WHERE nota > 8;

60. Mostrar un número aleatori

SELECT PI();

61. Mostrar 3 números aleatoris

SELECT PI(), PI(), PI();

62. Mostrar un número aleatori entre 0 i 10

SELECT PI() * 10;

63. Mostrar un número enter aleatori entre 0 i 10. Usa format(n,d)

SELECT format(PI() * 10, 0);

64. Mostrar nota i nota sense decimals

SELECT pes, format(nota, 0) from estudiant;

65. Observar el resultat de:

1.select truncate(nota, -1) from estudiant;

2.select truncate(nota, 0) from estudiant;

3.select truncate(nota, 1) from estudiant;

4.select truncate(nota, 2) from estudiant;

5.select nota, nota DIV 1 from estudiant;

6.select nota, nota DIV 2 from estudiant;

Date functions: now(), curdate(), hour(), year(date), month(date), day(date), monthnom(date), daynom(date), extract(unit from date), adddate(date, INTERVAL n unit), subdate(date, INTERVAL n unit), curtime(), datediff(date1, date2), timestamp(date), timestampdiff(unit, date1, date2), date_format(date, format)

Operators: localtime, current_time, current_date

Observe that:

•NOW() = LOCALTIME() = LOCALTIME

•ADDDATE() = DATE_ADD()

•SUBDATE() = DATE_DIFF()

•CURTIME() = CURRENT_TIME() = current_time

•CURDATE()= CURRENT_DATE()

66. Mostrar data i hora actual

SELECT now();

67. Observa l'eixida de:

SELECT now(), localtime, curdate(), curtime();

68. Mostrar només la data actual

SELECT curdate();

69. Extreu de la data actual només l'hora

SELECT hour(localtime);

70. Extrau de la data actual, el número i el nom del dia

SELECT day(localtime), daynom(localtime);

71. Extrau de la data actual només el número i el nom del mes

SELECT month(localtime), monthnom(localtime);

72. Mostrar només l'any actual

SELECT YEAR(localtime);

73. Incrementar en una unitat l'any de naixement de l'estudiant '12345678A'

UPDATE dadesestudiant SET data_naix = ADDDATE(data_naix, INTERVAL 1 year) WHERE dni = '12345678A';

74. Incrementar en una unitat el mes de la data de naixement de l'estudiant '12345678A'

UPDATE dadesestudiant SET data_naix = ADDDATE(data_naix, INTERVAL 1 month) WHERE dni = '12345678A';

75. Incrementar en 15 unitats el dia de la data de naixement de l'estudiant '12345678A'

UPDATE dadesestudiant SET data_naix = ADDDATE(data_naix, INTERVAL 15 days) WHERE dni = '12345678A';

76. Disminuir en 1 unitat l'any de la data de naixement de l'estudiant '12345678A'

UPDATE dadesestudiant SET data_naix = SUBDATE(data_naix, INTERVAL 1 year) WHERE dni = '12345678A';

77. Disminuir en un mes la data de naixement de l'estudiant '12345678A'

UPDATE dadesestudiant SET data_naix = SUBDATE(data_naix, INTERVAL 1 month) WHERE dni = '12345678A';

78. Decrease in 15 units el dia of the data_naix to the estudiant '12345678A'

UPDATE dadesestudiant SET data_naix = SUBDATE(data_naix, INTERVAL 15 days) WHERE dni = '12345678A';

79. Mostrar la diferència entre hui i '2015-10-10', en dies

SELECT DATEDIFF(localtime, '2015-10-10'); o SELECT TIMESTAMPDIFF(DAY, '2015-10-10', localtime);

80. Mostrar la diferència entre hui i '2014-10-10', en mesos complets

SELECT TIMESTAMPDIFF(month, '2015-10-10', localtime);

81. Mostrar la diferència entre hui i '2013-10-10', en anys

SELECT TIMESTAMPDIFF(YEAR, '2013-10-10', localtime);

82. Investigar sobre DATE_FORMAT(date, format) i posar alguns exemples

82.1 SELECT DATE_FORMAT(localtime, '%d %M, %Y');

82.2 SELECT DATE_FORMAT(data_naix, '%Y, %D %M') FROM dadesestudiant WHERE dni = '12345678A';