query auto

-- 04/03/22 --

SELECT nome_modello, cod_modello
from modelli
where cod_fabbrica='001'
order by nome_modello DESC;

select * from veicoli
where cilindrata = 1590
order by targa;

select * from veicoli
where cilindrata > cavalli_fiscali * 20
order by targa;

select * from veicoli
where cilindrata > 1000 AND cod_combustibile = '01'
order by targa;

select * from veicoli
where cod_modello = '001' OR cod_modello = '004'
order by targa;

select * from veicoli
where NOT cod_categoria = '01'
order by targa;

select * from veicoli
where not (cod_categoria = '01' and cilindrata > 1500)
order by targa;

-- BETWEEN --
select * from veicoli
where cilindrata BETWEEN 1500 and 1800
order by targa;

select * from veicoli
where (cilindrata >= 1500) and (cilindrata <= 1800)
order by targa;

-- IN --
select * from veicoli
where targa IN ('A123456X', 'D238765W', 'XF5789GY')
order by targa;

-- LIKE --
select * from veicoli
where targa LIKE 'C%'
order by targa;

select * from veicoli
where targa like '%C%'
order by targa;

-- Seleziono una determinata 'c' --
select * from veicoli
where targa like '_______C';

-- IS --
select * from veicoli
where cilindrata IS NULL
order by targa;

select * from veicoli
where cilindrata not between 1500 and 1800
order by targa;

select * from veicoli
where targa not in ('A123456X', 'D238765W', 'XF5789GY')
order by targa;

-- VALORE MAX & MIN --
select
    MAX(cilindrata) as "Cilindrata massima",
    MIN(cilindrata) as "Cilindrata minima"
from veicoli;

-- SOMMA --
select SUM(cilindrata) as "Somma delle cilindrate"
from veicoli;

-- MEDIA --
select AVG(cilindrata) as "Media delle cilindrate"
from veicoli;

-- DEVIAZIONE STANDARD & VARIANZA --
select
    STDDEV(cilindrata) as "Deviazione standard cilindrate",
    VARIANCE(cilindrata) as "Varianza cilindrate"
from veicoli;

-- ARROTONDAMENTO (2 CIFRE DECIMALI DOPO LA VIRGOLA) --
select
    ROUND(stddev(cilindrata), 2) as "Deviazione standard cilindrate",
    ROUND(variance(cilindrata), 2) as "Varianza cilindrate"
from veicoli;

--  CONTA OCCORRENZE --
select COUNT(*) as "Numero veicoli" from veicoli;
select count(cavalli_fiscali) as "Numero cavalli fiscali" from veicoli;

-- ALL (ANCHE LE OCCORRENZE UGUALI) --
select count(all cavalli_fiscali) as "CF [anche uguali]" from veicoli;

-- DISTINCT --
select count(distinct cavalli_fiscali) "CF [distinti]" from veicoli;