Revisión TP 6 SQL - Ejercicio 2

[Lautaro Ramos]

Gurises, cómo están?

             Los integrantes del grupo 2 (Albornoz, Chamorro, Comparin, Fiorotto y Mariani) deberán enviar la resolución del ejercicio 2 del TP de SQL para que lo revisemos entre todos.

             Aprovecho y les recuerdo que fijamos la fecha de entrega del TP4 para el próximo lunes 13/06.

Saludos!

 Lautaro Ramos

Ing. en Sist. de Información

Tel.: +5493442562020

[Julieta Comparin]

TRABAJO PRACTICO 6_EJERCICIO 2_GRUPO 2

------------------------------------------------------------------- 1 --------------------------------------------------------------

select l.codpostal,l.localidad,p.provincia,count(*) as cant

from localidad l, provincia p, infraccion i

where i.fecha between '1988-01-01' and '2000-12-31' and

i.codpostal=l.codpostal and

l.idprovincia=p.idprovincia

group by l.codpostal,p.provincia;

------------------------------------------------------------------- 2 --------------------------------------------------------------

select sum(t.importe) as total

from infraccion i, tipoinfraccion t,localidad l,provincia p

where i.codpostal=l.codpostal and

l.idprovincia=p.idprovincia and

p.provincia='Entre Ríos' and

i.tipoinfraccion=t.tipoinfraccion and

i.pagada='FALSE';

------------------------------------------------------------------- 3 --------------------------------------------------------------

select t.tipoinfraccion,t.descripcion, p.sexo, count(*) as Total

from infraccion i, tipoinfraccion t, persona p

where i.fecha between '1990-01-01' and '1990-12-31' and

t.tipoinfraccion=i.tipoinfraccion and i.dni=p.dni

group by t.tipoinfraccion, p.sexo

------------------------------------------------------------------- 4 --------------------------------------------------------------

select v.patente,v.anio,mo.modelo,m.marca,count(*) as Cant 

from infraccion i, vehiculo v, modelo mo, marca m

where i.patente=v.patente and

v.idmodelo=mo.idmodelo and

mo.idmarca=m.idmarca

group by v.patente,mo.modelo,m.marca

having count(*)>3

order by Cant desc;

------------------------------------------------------------------- 5 --------------------------------------------------------------

create view localidades as

select l.localidad,count(*) as Cant

from localidad l, infraccion i, tipoinfraccion t 

where t.descripcion='Estacionamiento indebido' and

t.tipoinfraccion=i.tipoinfraccion and

i.codpostal=l.codpostal

group by l.localidad

select l.localidad

from localidades l

where l.Cant=(select max (localidades.Cant) from localidades)

------------------------------------------------------------------- 6 --------------------------------------------------------------

select COALESCE(i80.codpostal, i90.codpostal) AS codpostal, COALSCE (i80.localidad, i90.localidad) AS localidad, 

       COALESCE (i80.tipoinfraccion, i90.tipoinfraccion) AS tipoinfraccion, COALESCE (CANT80,0) AS cant0, 

       COALESCE (cant90,0) AS cant90

from (select l.codpostal, l.localidad, tipoinfraccion, COUNT (*) AS cant80

      from infraccion I, localidad l

      where i.codpostal=l.codpostal AND date_part(‘year’,fecha) BETWEEN 1980 AND 1989

      group by  l.codpostal, tipoinfraccion

      order by l.codpostal) 

      i80 FULL OUTER JOIN

      (select l.codpostal, l.localidad, tipoinfraccion, COUNT(*) AS cant90

       from infraccion I, localidad l

       where i.codpostal=l.codpostal AND date_part(‘year’,fecha) BETWEEN 1990 AND 1999 

       group by l.codpostal,tipoinfraccion

       order by l.codpostal) i90 ON i80.codpostal=i90.codpostal AND i80.tipoinfraccion=i90.tipoinfraccion

order by codpostal

------------------------------------------------------------------ 7 --------------------------------------------------------------

create view vh as

select i.patente,sum(t.importe) as Total

from infraccion i, tipoinfraccion t, vehiculo v

where i.patente=v.patente and i.pagada=false and i.tipoinfraccion=t.tipoinfraccion

group by i.patente

select v.patente,v2.dni,v.Total

from vh v, valuacion val, vehiculo v2

where v.patente=v2.patente and v2.idmodelo=val.idmodelo and v2.anio=val.anio and v.Total<val.valor

Libre de virus. www.avast.com

--
Has recibido este mensaje porque estás suscrito al grupo "gdatos" de Grupos de Google.
Para anular la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a gdatos+un...@googlegroups.com.
Para acceder a más opciones, visita https://groups.google.com/d/optout.

[Lautaro Ramos]

Gurises,

             Les paso la revisión del ejercicio 2 del TP6. 

             Aprovecho para recordarles que el miércoles es 22 es la fecha de entrega de este TP, y que deben resubir el resto de los trabajos, ya que se perdieron todos durante la migración de moodle.

Saludos!

La vista debería ser más general, y no solo incluir datos para el tipo Estacionamiento Indebido. Además debería incluir el año en el que se produjo la infracción para poder filtrar las infracciónes de 1999 como pide el enunciado

create view vw_Infracciones_x_Localidad AS

SELECT l.codpostal,

l.localidad,

t.tipoinfraccion as idtipoinfraccion,

t.descripcion as tipoinfraccion,

extract(year from i.fecha) as anio,

count(*) as cantidad

FROM infraccion i, localidad l, tipoinfraccion t

WHERE i.codpostal = l.codpostal

AND t.tipoinfraccion = i.tipoinfraccion

GROUP BY

l.codpostal,

l.localidad,

t.tipoinfraccion,

t.descripcion,

extract(year from i.fecha);

SELECT l.localidad,

l.cantidad

FROM vw_Infracciones_x_Localidad l

WHERE l.anio = 1999

and l.idtipoinfraccion = 1

and l.cantidad = (SELECT max(cantidad) FROM vw_Infracciones_x_Localidad WHERE anio=1999 and idtipoinfraccion = 1);

------------------------------------------------------------------- 6 --------------------------------------------------------------

select COALESCE(i80.codpostal, i90.codpostal) AS codpostal, COALESCE (i80.localidad, i90.localidad) AS localidad, 

       COALESCE (i80.tipoinfraccion, i90.tipoinfraccion) AS tipoinfraccion, COALESCE (CANT80,0) AS cant0, 

       COALESCE (cant90,0) AS cant90

from (select l.codpostal, l.localidad, tipoinfraccion, COUNT (*) AS cant80

      from infraccion I, localidad l

      where i.codpostal=l.codpostal AND date_part(‘year’,fecha) BETWEEN 1980 AND 1989

      group by  l.codpostal, tipoinfraccion

      order by l.codpostal) 

      i80 FULL OUTER JOIN

      (select l.codpostal, l.localidad, tipoinfraccion, COUNT(*) AS cant90

       from infraccion I, localidad l

       where i.codpostal=l.codpostal AND date_part(‘year’,fecha) BETWEEN 1990 AND 1999 

       group by l.codpostal,tipoinfraccion

       order by l.codpostal) i90 ON i80.codpostal=i90.codpostal AND i80.tipoinfraccion=i90.tipoinfraccion

order by codpostal

------------------------------------------------------------------ 7 --------------------------------------------------------------

create view vh as

select i.patente,sum(t.importe) as Total

from infraccion i, tipoinfraccion t, vehiculo v

where i.patente=v.patente and i.pagada=false and i.tipoinfraccion=t.tipoinfraccion

group by i.patente

select v.patente,v2.dni,v.Total

from vh v, valuacion val, vehiculo v2

where v.patente=v2.patente and v2.idmodelo=val.idmodelo and v2.anio=val.anio and v.Total<val.valor

Se puede resolver sin la necesidad de una vista colocando una restricción en el HAVING.

SELECT v.patente,

v.dni,

va.valor,

sum(t.importe) as deuda

FROM infraccion i, vehiculo v, tipoinfraccion t, valuacion va

WHERE v.patente = i.patente

AND i.tipoinfraccion = t.tipoinfraccion

AND v.idmodelo = va.idmodelo

AND and v.anio = va.anio

AND i.pagada = false

GROUP BY

v.patente,

v.dni,

va.valor

HAVING sum(t.importe) < va.valor

ORDER BY

v.patente;

 Lautaro Ramos

Ing. en Sist. de Información

Tel.: +5493442562020