SQL Tutorial: vistes

Introducció

• https://www.sqltutorial.org/sql-views

Les vistes serveixen per donar un nom a una consulta sql que pot ser més o menys complexa.

Tutorial

Per crear una vista:

CREATE VIEW view_name 
AS
SELECT-statement

Creem una vista a partir d'una consulta que és un INNER JOIN sobre employees i departments:

CREATE VIEW employee_contacts AS
    SELECT 
        first_name, last_name, email, phone_number, department_name
    FROM
        employees e
            INNER JOIN
        departments d ON d.department_id = e.department_id
    ORDER BY first_name;

I un cop tenim la vista, la podem utilitzar com si fos una consulta normal i corrent:

SELECT * FROM employee_contacts;

i sobre aquesta consulta podem aplicar filtres com fem amb una consulta:

SELECT job, MIN(compensation), MAX(compensation), AVG(compensation)
FROM payroll
WHERE job LIKE 'A%'
GROUP BY job;

NOTA: el GROUP BY encara no l'hem vist. El veurem la setmana vinent.

Podem redefinir la vista d'una manera molt senzilla:

CREATE OR REPLACE view_name AS
SELECT-statement;

Per exemple:

CREATE OR REPLACE VIEW payroll (first_name , last_name , job , department , salary) AS
SELECT first_name, last_name, job_title, department_name, salary FROM
employees e INNER JOIN jobs j ON j.job_id = e.job_id
INNER JOIN departments d ON d.department_id = e.department_id
ORDER BY first_name;

SELECT * FROM payroll;

Ara tenim 5 columnes en comptes de 4.

Per eliminar les vistes és molt fàcil:

DROP VIEW payroll;

Vistes sobre la base de dades de municipis

vista 1: municipis_zaragoza

Té l'estructura de id_mun, municipis, habitants, i només de Saragossa

create view municipis_zaragoza as
select  id_mun, municipi, habitants from municipis m inner join provincies p
on m.id_prov=p.id_prov
where provincia='Zaragoza';

select * from municipis_zaragoza;

select * from municipis_zaragoza where habitants>=20000;

select * from municipis_zaragoza where superficie>=20000;

'7903', 'Calatayud', '20092'
'8128', 'Zaragoza', '681877'

Dóna error perquè en la vista municipis_superficie no està definit el camp superficie.

vista 2: municipis_catalunya

Llistat dels municipis de Catalunya, ordenats per número d'habitants (id_mun, cod_ine, municipi, habitants)

create view municipis_catalunya as
select  id_mun, cod_ine, municipi, habitants from ((municipis m inner join provincies p
on m.id_prov=p.id_prov)
inner join comunitats c
on p.id_com=c.id_com)
where comunitat='Catalunya';

select * from municipis_catalunya;

select * from municipis_catalunya where habitants>=20000;

vista 3: comunitats_provincies

Llistat de comunitats i provincies, amb la següent informació: id_com, comunitat, id_prov, provincia

create view comunitats_provincies as
select  c.id_com, comunitat, id_prov, provincia from 
((provincies p inner join comunitats c 
on p.id_com=c.id_com)) order by c.comunitat;

select * from comunitats_provincies where comunitat LIKE 'castilla%';

vista 4: municipis_a

Municipis que comencin per A i que la seva província també comenci per A

create view municipis_a as
select  id_mun, municipi, provincia from municipis m inner join provincies p
on m.id_prov=p.id_prov
where provincia like='A%' AND municipi like='A%';

vista 5: municipis_grans

Municipis que la seva superfície sigui com a mínim el doble que la mitjana de la superfície dels municipis.

select AVG(superficie) from municipis where superficie is not null;
62,9724 Km^2

create view municipis_grans as
select municipi, superficie from municipis where superficie >= (select 2*AVG(superficie) from municipis);

select * from municipis_grans order by superficie desc;

Per veure la superfície dels municipis tens aquest mapa coroplètic:

• https://opendata.esri.es/datasets/53229f5912e04f1ba6dddb70a5abeb72_0/explore?location=38.306693%2C-4.748559%2C6.95

vista 6: num_municipis_per_prov

Creem la vista num_municipis_per_prov amb quatre columnes: id_com, id_prov, província i número de municipis (necessitem fer GROUP BY)

create view num_municipis_per_prov as
select id_com, p.id_prov, provincia, count(municipi) num from municipis m inner join provincies p
on m.id_prov=p.id_prov
group by p.id_prov, provincia
order by num desc;

select * from num_municipis_per_prov;

Ara podem combinar la vista que hem acabat de crear amb la taula comunitats per saber el número de municipis que tenen les províncies de Catalunya:

select provincia,num,comunitat from
num_municipis_per_prov num inner join comunitats c
on num.id_com=c.id_com
where comunitat='Catalunya';