TP SQL - Revisión

[Lautaro Ramos]

Gurises,

            Les paso los grupos que tienen que mandar los ejercicios del TP 6 para que los revisemos:

• Grupo 7 (Echeverria, Parlato, Rebord): Ejercicio 1.1
• Grupo 1 (Bruni, Duarte, Elisalde, Obelar, Rosano): Ejercicio 1.2
• Grupo 4 (Balsanyaque, Caire, Chichi, Venditti, Zapata): Ejercicio 1.3
• Grupo 3 (Aguire, Ballesteros, Castell, Richard, Rodriguez): Ejercicio 1.4

Traten de mandarlos antes del miércoles, así los revisamos durante ese día y se los paso.

Saludos,

 Lautaro Ramos

Ing. en Sist. de Información

Tel.: +5493442562020

[Paula Zabalegui]

Vamos chicos, no se duerman ;)

Es el último tramo, sólo necesitan un poco más de dedicación.

Saludos!!!

--
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.

[Alcides Rosano]

Grupo 1 ejercicio 1.2

-- A -- Los nombres y los países de las clases que llevaban cañones de al menos 16 pulgadas de calibre.

select clase, pais

from clase 

where calibre>=16

-- B -- Hallar los barcos botados antes de 1921

select nombre

from  barco 

where  botado<1921

-- C -- Hallar los barcos hundidos en la batalla del Atlántico Norte

select barco

from participa 

where resultado='Hundido' and batalla='Atlantico Norte';

-- D -- El  tratado  de  Washington  de  1921  prohibió  los  barcos  de  más  de  35000  toneladas.  Listar  los barcos que violaron el tratado de Washington

select bar.nombre as barco,bat.nombre as batalla

from barco bar, clase c, participa p, batalla bat

where (bar.clase=c.clase and bar.nombre=p.barco and p.batalla=bat.nombre) and c.desplazamiento>35000 and bat.fecha>'1921-01-01';

-- E -- Listar el nombre, el desplazamiento y el número de cañones de los barcos que participaron de la batalla de Guadalcanal

select b.nombre,c.desplazamiento,c.caniones

from clase c, barco b, participa p

where (c.clase=b.clase and b.nombre=p.barco) and p.batalla='Guadalcanal'

-- F -- Hallar los países que tuvieron tanto cruceros como acorazados 

select distinct c1.pais

from clase c1, clase c2

where c1.pais=c2.pais and c1.tipo<>c2.tipo

-- G -- Hallar  los  barcos  que,  siendo  dañados  en  alguna  batalla,  participaron  posteriormente  de  alguna otra.

-- Primero creé la VISTA: participabatalla

select *

from participabatalla p1, participaBatalla p2

where p1.barco=p2.barco and p1.resultado='Dañado' and p2.fecha>p1.fecha

-- H -- ¿La base de datos presentada como ejemplo se encuentra en  estado consistente? De no ser así,indique  alguna  inconsistencia  que  haya  encontrado,

-- y  la  expresión  del  álgebra  relacionar  que  le permitió hallarla.

[Lautaro Zapata]

Grupo 4 ejercicio 1.3

/*

a) Por cada localidad donde hayan ocurrido infracciones entre los años 1988 y 2000 indicar el código

postal de la localidad, el nombre de la localidad, el nombre de la provincia.

*/

SELECT DISTINCT I.codPostal, L.localidad, P.provincia FROM infraccion I, localidad L, provincia P WHERE

I.fecha BETWEEN '1987-12-31' AND '2001-01-01' AND

I.codPostal = L.codPostal AND

L.idProvincia = P.idProvincia;

/*

b) La lista de todas las infracciones cometidas en el año 1992, incluyendo el numero de infracción, la

descripción del tipo de infracción, el importe que corresponde pagar, la patente del vehículo

infractor y el nombre del chofer.

*/

SELECT I.idInfraccion, TI.descripcion, TI.importe, I.patente,P.nombre FROM infraccion I, tipoInfraccion TI, persona P WHERE

I.fecha BETWEEN '1991-12-31' AND '1993-01-01' AND

I.tipoInfraccion = TI.tipoInfraccion AND

I.dni = P.dni;

/*

c) El nombre de las personas que adeuden infracciones anteriores al ano 1992 y que después de esa

fecha, bien no hayan cometido otras infracciones, o si las cometieron, las han pagado a todas.

*/

SELECT P.nombre FROM persona P WHERE

P.dni in (SELECT dni FROM infraccion WHERE

fecha < '1992-01-01' AND

NOT pagada) AND

P.dni not in (SELECT dni FROM infraccion WHERE

fecha > '1991-12-31' AND

NOT pagada);

/*

d) Los vehículos con los que se hayan cometido infracciones que se adeudan y que no cuenten con

la valuación correspondiente

*/

SELECT I.patente FROM infraccion I, vehiculo V WHERE

NOT I.pagada AND

I.patente = V.patente AND

(V.idModelo,V.anio) NOT IN(select idModelo,anio FROM valuacion);

/*

e) Para aquellas infracciones en las que el dueño del vehículo involucrado en la misma difiera del

chofer, listar el número de infracción, la patente del vehículo infractor y el nombre del propietario

del vehículo.

*/

SELECT I.idInfraccion, I.patente, P.nombre FROM infraccion I, vehiculo V, persona P WHERE

I.patente = V.patente AND

I.dni != V.dni AND

V.dni = P.dni;

/*

f) Para aquellas personas que han cometido dos infracciones, el mismo día en diferentes localidades

listar su nombre y los números de las infracciones.

*/

SELECT I1.idInfraccion, I2.idInfraccion, P.nombre FROM infraccion I1,infraccion I2, persona P WHERE

I1.dni = I2.dni AND

I1.fecha = I2.fecha AND

I1.codPostal != I2.codPostal AND

I1.idInfraccion < I2.idInfraccion AND

I1.dni=P.dni;

[Lautaro Ramos]

Gurises,

             Les paso la revisión del ejercicio 1.2.

Saludos!

-- A -- Los nombres y los países de las clases que llevaban cañones de al menos 16 pulgadas de calibre.

select clase, pais

from clase 

where calibre>=16

-- B -- Hallar los barcos botados antes de 1921

select nombre

from  barco 

where  botado<1921

-- C -- Hallar los barcos hundidos en la batalla del Atlántico Norte

select barco

from participa 

where resultado='Hundido' and batalla='Atlantico Norte';

-- D -- El  tratado  de  Washington  de  1921  prohibió  los  barcos  de  más  de  35000  toneladas.  Listar  los barcos que violaron el tratado de Washington

select bar.nombre as barco,bat.nombre as batalla

from barco bar, clase c, participa p, batalla bat

where (bar.clase=c.clase and bar.nombre=p.barco and p.batalla=bat.nombre) and c.desplazamiento>35000 and bat.fecha>'1921-01-01';

Que un barco haya violado o no el tratado no tiene que ver con las batallas en las que participó, sino con el año en que fue botado, porque el barco podría seguir en funcionamiento pese a no participar en ninguna batalla

SELECT b.nombre

FROM barco b, clase c

WHERE b.clase = c.clase

AND c.desplazamiento > 35000

AND b.botado >= 1921

-- E -- Listar el nombre, el desplazamiento y el número de cañones de los barcos que participaron de la batalla de Guadalcanal

select b.nombre,c.desplazamiento,c.caniones

from clase c, barco b, participa p

where (c.clase=b.clase and b.nombre=p.barco) and p.batalla='Guadalcanal'

-- F -- Hallar los países que tuvieron tanto cruceros como acorazados 

select c1.pais

from clase c1, clase c2

where c1.pais=c2.pais and c1.tipo<>c2.tipo

Esta bien. También podría resolverse con una intersección. Otra opción, para ser mas exactos, se podría cambiar la condición por c1.tipo='Bc' and c2.tipo='Bb', por si es que puede existir algún otro tipo. Con ese cambio también se ahorrarían el DISTINCT.

-- G -- Hallar  los  barcos  que,  siendo  dañados  en  alguna  batalla,  participaron  posteriormente  de  alguna otra.

-- Primero creé la VISTA: participabatalla

select *

from participabatalla p1, participaBatalla p2

where p1.barco=p2.barco and p1.resultado='Dañado' and p2.fecha>p1.fecha

Se puede resolver sin la necesidad de una vista. Dos soluciones posibles

SELECT p.barco

FROM participa p, batalla b

WHERE b.nombre = p.batalla

AND p.resultado = 'Dañado'

AND EXISTS(SELECT * 

FROM participa, batalla

WHERE batalla.nombre = participa.batalla

AND participa.barco = p.barco

AND batalla.fecha > b.fecha);

SELECT p.barco

FROM participa p, batalla b

WHERE b.nombre = p.batalla

AND p.resultado = 'Dañado'

AND p.barco IN (SELECT participa.barco

FROM participa, batalla

WHERE batalla.nombre = participa.batalla

AND participa.barco = p.barco

AND batalla.fecha > b.fecha);

 Lautaro Ramos

Ing. en Sist. de Información

Tel.: +5493442562020

[Lautaro Ramos]

Gurises,

              Les paso la revisión del ejercicio 1.3.

Saludos!

/*

a) Por cada localidad donde hayan ocurrido infracciones entre los años 1988 y 2000 indicar el código

postal de la localidad, el nombre de la localidad, el nombre de la provincia.

*/

SELECT DISTINCT I.codPostal, L.localidad, P.provincia

FROM infraccion I, localidad L, provincia P WHERE

I.fecha BETWEEN '1987-12-31' AND '2001-01-01' AND

I.codPostal = L.codPostal AND

L.idProvincia = P.idProvincia;

La condición de fechas debería ser I.fecha BETWEEN '1988-01-01' AND '2000-12-31' porque el between incluye los valores de los extremos

/*

b) La lista de todas las infracciones cometidas en el año 1992, incluyendo el numero de infracción, la

descripción del tipo de infracción, el importe que corresponde pagar, la patente del vehículo

infractor y el nombre del chofer.

*/

SELECT I.idInfraccion, TI.descripcion, TI.importe, I.patente,P.nombre

FROM infraccion I, tipoInfraccion TI, persona P WHERE

I.fecha BETWEEN '1991-12-31' AND '1993-01-01' AND

I.tipoInfraccion = TI.tipoInfraccion AND

I.dni = P.dni;

La condición de fechas debería ser I.fecha BETWEEN '1992-01-01' AND '1992-12-31'

/*

c) El nombre de las personas que adeuden infracciones anteriores al ano 1992 y que después de esa

fecha, bien no hayan cometido otras infracciones, o si las cometieron, las han pagado a todas.

*/

SELECT P.nombre FROM persona P WHERE

P.dni in (SELECT dni FROM infraccion WHERE

fecha < '1992-01-01' AND

NOT pagada) AND

P.dni not in (SELECT dni FROM infraccion WHERE

fecha > '1991-12-31' AND

NOT pagada);

No es necesario hacer 2 subconsultas

SELECT DISTINCT P.nombre FROM persona P, infraccion I

WHERE I.dni = P.dni

AND fecha < '1992-01-01'

AND NOT pagada

AND P.dni not in (SELECT dni FROM infraccion WHERE

fecha >= '1992-01-01' AND

NOT pagada);

/*

d) Los vehículos con los que se hayan cometido infracciones que se adeudan y que no cuenten con

la valuación correspondiente

*/

SELECT I.patente FROM infraccion I, vehiculo V WHERE

NOT I.pagada AND

I.patente = V.patente AND

(V.idModelo,V.anio) NOT IN(select idModelo,anio FROM valuacion);

 Los operadores como IN y NOT IN permiten comparar por mas de un atributo usando lo que se denomina Row Cosntructors, que, como el nombre lo dice, lo que hacen es definir una tupla de valores. La sintaxis de este tipo de expresiones es ROW(val1, val2,..., valN) o simplemente con () si se utiliza más de un valor, que es lo que usaron ustedes.

Tengan en cuenta que las patentes pueden repetirse porque estan haciendo un join con infracción y un vehículo pudo haber cometido mas de una infracción, así que es necesario agregar el DISTINCT.

Otra forna de resolverlo es usando subconsultas correlacionadas:

SELECT DISTINCT

i.patente

FROM infraccion i, vehiculo v

WHERE i.pagada = false

and v.patente = i.patente

and not exists (SELECT *

FROM valuacion

WHERE anio = v.anio

and idmodelo = v.idmodelo);

/*

e) Para aquellas infracciones en las que el dueño del vehículo involucrado en la misma difiera del

chofer, listar el número de infracción, la patente del vehículo infractor y el nombre del propietario

del vehículo.

*/

SELECT I.idInfraccion, I.patente, P.nombre

FROM infraccion I, vehiculo V, persona P WHERE

I.patente = V.patente AND

I.dni != V.dni AND

V.dni = P.dni;

/*

f) Para aquellas personas que han cometido dos infracciones, el mismo día en diferentes localidades

listar su nombre y los números de las infracciones.

*/

SELECT I1.idInfraccion, I2.idInfraccion, P.nombre

FROM infraccion I1,infraccion I2, persona P WHERE

I1.dni = I2.dni AND

I1.fecha = I2.fecha AND

I1.codPostal != I2.codPostal AND

I1.idInfraccion < I2.idInfraccion AND

I1.dni=P.dni;

Está bien. Se podrian ahorrar la condición sobre los idInfraccion colocando < o > para comparar los codPostal en lugar de usar !=.

 Lautaro Ramos

Ing. en Sist. de Información

Tel.: +5493442562020

[farebord .]

Te mando lo debido por el Grupo 7. 

Ejercicio 1.1

a)

select cod

from pc

where veloc >= 150;

b)

select distinct p.fabricante

from laptop as l, producto as p

where l.hd >= 1 and l.cod = p.cod;

c)

(select laptop.cod, precio

from laptop, producto as p

where p.fabricante = 'B' and p.cod = laptop.cod)

  union

(select pc.cod,precio

from pc, producto as p

where p.fabricante = 'B' and p.cod = pc.cod)

  union

(select impresora.cod, precio

from impresora producto as p

where p.fabricante = 'B' and p.cod = impresora.cod);

d)

select cod

from impresora

where color = TRUE

e)

select distinct producto.fabricante

from producto, laptop

where producto.cod = laptop.cod and producto.fabricante not in

  (select producto.fabricante

  from producto, pc

  where producto.cod = pc.cod)

f)

select pc1.cod, pc2.cod

from pc pc1, pc pc2

where pc1.cod <> pc2.cod and pc1.ram = pc2.ram;

g)

select pc1.cod, pc2.cod

from pc pc1, pc pc2

where pc1.cod < pc2.cod and pc1.ram = pc2.ram and pc1.veloc = pc2.veloc;

h)

create view pcylaptop as

select cod, veloc

from pc

union

select cod, veloc

from laptop;

select distinct producto.fabricante

from pcylaptop, producto

where pcylaptop.cod = producto.cod and pcylaptop.veloc >= 133;

i)

select distinct producto.fabricantea)

select cod

from pc

where veloc >= 150;

b)

select distinct p.fabricante

from laptop as l, producto as p

where l.hd >= 1 and l.cod = p.cod;

c)

(select laptop.cod, precio

from laptop, producto as p

where p.fabricante = 'B' and p.cod = laptop.cod)

  union

(select pc.cod,precio

from pc, producto as p

where p.fabricante = 'B' and p.cod = pc.cod)

  union

(select impresora.cod, precio

from impresora producto as p

where p.fabricante = 'B' and p.cod = impresora.cod);

d)

select cod

from impresora

where color = TRUE

e)

select distinct producto.fabricante

from producto, laptop

where producto.cod = laptop.cod and producto.fabricante not in

  (select producto.fabricante

  from producto, pc

  where producto.cod = pc.cod)

f)

select pc1.cod, pc2.cod

from pc pc1, pc pc2

where pc1.cod <> pc2.cod and pc1.ram = pc2.ram;

g)

select pc1.cod, pc2.cod

from pc pc1, pc pc2

where pc1.cod < pc2.cod and pc1.ram = pc2.ram and pc1.veloc = pc2.veloc;

h)

create view pcylaptop as

select cod, veloc

from pc

union

select cod, veloc

from laptop;

select distinct producto.fabricante

from pcylaptop, producto

where pcylaptop.cod = producto.cod and pcylaptop.veloc >= 133;

i)

select distinct producto.fabricante

from pcylaptop, producto

where pcylaptop.cod = producto.cod and pcylaptop.veloc = (select max (veloc) from pcylaptop);

from pcylaptop, producto

where pcylaptop.cod = producto.cod and pcylaptop.veloc = (select max (veloc) from pcylaptop);

Saludos.

[Lautaro Ramos]

Gurises,

            Les paso la revisión del ejercicio 1.1.

Saludos!

Deberían devolver el tamaño de disco, no los códigos de las PCs.

La comparación debería ser con el atributo hd, no ram.

select pc1.hd

from pc pc1, pc pc2

where pc1.cod <> pc2.cod and pc1.hd = pc2.hd;

g)

select pc1.cod, pc2.cod

from pc pc1, pc pc2

where pc1.cod < pc2.cod and pc1.ram = pc2.ram and pc1.veloc = pc2.veloc;

h)

create view pcylaptop as

select cod, veloc

from pc

union

select cod, veloc

from laptop;

select distinct producto.fabricante

from pcylaptop, producto

where pcylaptop.cod = producto.cod and pcylaptop.veloc >= 133;

Está bien que reutilicen la vista, pero también se puede hacer sin la vista

(select distinct producto.fabricante

from laptop, producto

where laptop.cod = producto.cod and laptop.veloc >= 133)

union

(select distinct producto.fabricante

from pc, producto

where pc.cod = producto.cod and pc.veloc >= 133);

i)

select distinct producto.fabricante

from pcylaptop, producto

where pcylaptop.cod = producto.cod and pcylaptop.veloc = (select max (veloc) from pcylaptop);

 Lautaro Ramos

Ing. en Sist. de Información

Tel.: +5493442562020