Sqlite - Select

La instrucció SQL `select` s'utilitza per seleccionar dades d'una taula.

• Introducció
• Entorn de treball
• select
• order by
• limit
• Funcions d’agregació
• Operacions amb columnes
• Funcions matemàtiques
• Activitat
• Pendent

Introducció

Crea una base de dades SQLite nova anomenada world.db

Entorn de treball

Descarrega el fitxer population.csv

Importa el fitxer a la base de dades SQLite: File > Import > Table from CSV file…

Recorda seleccionar l’opció “Column names in first line”.

select

La forma més simple de la sentència select que et permet consultar dades d’una sola taula:

select column_list
from table;

Tot i que la clàusula select apareix abans de la clàusula from, SQLite avalua primer la clàusula from i després la clàusula select, per tant:

• Primer, especifica la taula de la qual vols obtenir dades a la clàusula from.
• Segon, especifica una columna o una llista de columnes separades per comes a la clàusula select.

Utilitzes el punt i coma (;) per acabar la sentència.

La taula population conté columnes i files. Sembla un full de càlcul.

Pots utilitzar la sentència select per obtenir dades de la taula population.

You can use the asterisk (*), which is the shorthand for all columns of the table as follows:

select *
from population;

Si només vols algunes columnes, pots especificar els noms de les columnes separats per comes:

select "Country/Territory", "2022 Population"
from population;

Especifiques una llista de noms de columnes, de les quals vols obtenir dades, a la clàusula select i la taula population a la clàusula from.

SQLite retorna el següent resultat:

També pots modificar el nom de les columnes que vols veure amb as:

select "Country/Territory" as country, "2022 Population" as population
from population;

select "Country/Territory", Capital from population;

select "Country/Territory", Area from population;

order by

SQLite emmagatzema les files d’una taula en un ordre no especificat. Això significa que les files de la taula poden estar o no en l’ordre en què es van inserir.

Si utilitzes la instrucció select per obtenir files d’una taula, l’ordre de les files del conjunt de resultats no està especificat.

Per ordenar les files d’un conjunt de resultats, afegeixes la clàusula order by a la instrucció select de la següent manera:

select * from table
order by colum column_1 asc, column_2 desc;

La clàusula order by va després del from. Et permet ordenar el conjunt de resultats basat en una o més columnes en ordre ascendent o descendent.

En aquesta sintaxi, col·loques el nom de la columna per la qual vols ordenar després de la clàusula order by seguida de la paraula clau asc o desc.

• La paraula clau asc significa ascendent.
• La paraula clau desc significa descendent.

Si no especifiques la paraula clau asc o desc, SQLite ordena el conjunt de resultats utilitzant l’opció asc. En altres paraules, ordena el conjunt de resultats en ordre ascendent per defecte.

Si vols ordenar el conjunt de resultats per múltiples columnes, utilitzes una coma (,) per separar dues columnes.

La clàusula order by ordena les files utilitzant columnes o expressions d’esquerra a dreta. En altres paraules, la clàusula order by ordena les files utilitzant la primera columna de la llista. Després, ordena les files ordenades utilitzant la segona columna, i així successivament.

Suposem que vols ordenar el conjunt de resultats basat en la columna 2022 Population en ordre descendent, utilitzes la següent instrucció:

select "Country/Territory", "2022 Population"
from population
order by "2022 Population" desc;

El conjunt de resultats ara està ordenat per la columna 2022 Population en ordre descendent:

SQLite utilitza asc per defecte, així que pots ometre-ho a la instrucció anterior de la següent manera:

select "Country/Territory", Area from population order by Area;

limit

La clàusula limit és una part opcional de la instrucció select. Utilitzes la clàusula limit per restringir el nombre de files retornades per la consulta.

Per exemple, una instrucció select pot retornar un milió de files. No obstant això, si només necessites les primeres 10 files del conjunt de resultats, pots afegir la clàusula limit a la instrucció select per obtenir 10 files.

La següent il·lustra la sintaxi de la clàusula limit:

select column_list
from table
limit row_count;

El row_count és un nombre enter positiu que especifica el nombre de files retornades.

Per exemple, per obtenir les primeres 2 files de la taula population, utilitzes la següent instrucció:

select "Country/Territory", "2022 Population"
from population
order by "2022 Population" desc
limit 2 offset 30;

Si vols obtenir les primeres 2 files començant des de la fila 30 del conjunt de resultats, utilitzes la paraula clau offset de la següent manera:

select "Country/Territory", "2022 Population"
from population
order by "2022 Population" desc
limit 2 offset 30;

Funcions d’agregació

Les funcions d’agregació operen sobre un conjunt de files i retornen un únic resultat.

SQLite proporciona les següents funcions d’agregació:

avg

La funció avg és una funció d’agregació que calcula el valor mitjà de tots els valors no NULL dins d’un grup.

Per defecte, la funció avg utilitza la clàusula all tant si l’especifiques com si no. Això significa que la funció avg prendrà tots els valors no NULL quan calculi el valor mitjà.

El valor de la funció avg és sempre un valor en coma flotant o un valor null. La funció avg només retorna un valor null si i només si tots els valors del grup són valors null.

Primer, crea una nova taula anomenada student utilitzant la següent instrucció:

create table student (name text, score integer);

A continuació, insereix alguns valors d’estudiants a la taula student.

insert into student (name, score) values
    ('Anna Garcia', 85),
    ('Marc López', 92),
    ('Laura Martí', 78),
    ('Pol Fernández', 88),
    ('Júlia Sánchez', null),
    ('David Romero', 88);

Pots utilitzar la funció avg per calcular la puntuació mitjana de tots els estudiants.

select avg(score)
from student;

Com que la “Júlia Sánchez” no té cap puntuació assignada, quan es calcula la mitjana, la funció avg l’ignora en el càlcul.

En cas que vulguis calcular el valor mitjà de valors distincts (o únics), necessites especificar la clàusula distinct explícitament en l’expressió.

select avg(distinct score)
from student;

count

La funció count retorna el nombre d’elements en un grup.

La funció count(*) retorna el nombre de files en una taula, incloent les files que inclouen null i duplicats.

select count(*)
from student;

Com pots veure clarament de la sortida, el conjunt de resultats inclou valors null i files duplicades.

Utilitza count(score) per obtenir el nombre de valors no nuls a la columna score:

select count(score)
from student;

En aquest exemple, count(score) retorna el nombre de valors no nuls. Compta les files duplicades com a files separades.

Utilitza count(distinct score) per obtenir el nombre de valors únics i no nuls a la columna score:

select count(distinct score)
from student;

max

La funció max retorna el valor màxim de tots els valors en un grup. Pots utilitzar la funció max per aconseguir moltes coses.

Per exemple, pots utilitzar la funció max per trobar els productes més cars, trobar l’element més gran del seu grup, etc.

Hi ha algunes notes importants sobre la funció max:

• Primer, la funció max ignora els valors null.
• Segon, a diferència de les funcions avg i count, la clàusula distinct no és rellevant per a la funció max.

Per obtenir la puntuació (score) més gran, apliques la funció max a la columna score com la següent instrucció:

select max(score)
from student;

select score
from student
order by score desc
limit 1;

min

La funció min utilitza all per defecte. Similar a la funció max, la funció min ignora els valors null. Per tant, retorna el valor mínim no NULL en un conjunt de valors.

La funció min retorna un valor null si i només si hi ha només valors null en el conjunt.

select min(score)
from student;

sum

La funció sum retorna la suma dels valors no NULL o només els valors distints en un grup.

El resultat de la funció sum és un nombre enter si tots els valors d’entrada no NULL són enters. Si algun valor d’entrada no és ni un enter ni un valor null, el resultat de la funció sum és un valor en coma flotant.

El resultat de la funció sum és null si i només si tots els valors d’entrada són null.

En cas que es produeixi un error de desbordament d’enters i tots els valors d’entrada siguin null o enters, la funció sum llança una excepció de desbordament d’enters.

Per obtenir les puntuacions totals, utilitzes la funció sum com la següent instrucció:

select sum(score)
from student;

Activitat

A partir de la taula population,

select avg("2022 Population") as average
from population;

select sum("2022 Population") as total
from population;

select sum("2020 Population") as "2020" , sum("2010 Population") as "2010", sum("2000 Population") as "2000",
sum("1990 Population") as "1990"
from population;

select count(*) as countries
from population;

Operacions amb columnes

Una base de dades permet fer operacions matemàtiques amb totes les columnes seleccionades a la vegada, en lloc de fer-ho fila per fila.

Per exemple, per calcular el creixement de la població entre els anys 2000 i 2020 per a cada país, pots restar la columna 2000 Population de la columna 2020 Population.

I pots ordenar els resultats per la nova columna virtual growth:

select "Country/Territory" , "2020 Population" - ("2000 Population")  as growth
from population
order by growth;

create table sale (
product text,
price real,
quantity integer
);

insert into sale values
('Laptop', 899.99, 5),
('Mouse', 25.50, 15),
('Keyboard', 79.99, 10),
('Monitor', 249.00, 8),
('USB Cable', 12.99, 25),
('Webcam', 65.00, 12),
('Headphones', 45.99, 20),
('USB Hub', 35.50, 18);

select product, quantity, (price * quantity) as total
from sale
order by product;

select sum(price * quantity) as total
from sale;

Funcions matemàtiques

Math Functions

trunc

trunc

Activitat

Pendent

• LearnSQL - Blog
• SQLime
• Android - Best practices for SQLite performance
• SQLBolt
• https://www.sqlitetutorial.net
• https://dev.to/mliakos/quick-post-about-the-sqlite-upsert-and-the-new-returning-clause-5fhl