Práctica de Lenguajes de Consulta (Bases de Datos)

Ejercicio 01

Dadas las relaciones R y S calcular:

Parte A

R U S

Respuesta

1	2
a	b
b	c
c	b
d	e
e	a
b	d

Parte B

R - S

Respuesta

1	2
a	b
c	b
d	e

Parte C

$R\times S$

Respuesta

A	B	?	C
a	b	b	c
a	b	e	a
a	b	b	d
b	c	b	c
b	c	e	a
b	c	b	d
c	b	b	c
c	b	e	a
c	b	b	d
d	e	b	c
d	e	e	a
d	e	b	d

Parte D

$\rho (V(2\rightarrow B1,3\rightarrow B2),R\times S)$

Respuesta

Idem c, pero (A B ? C) pasa a ser: (A B1 B2 C)

Parte E

R |X| S

Respuesta

A	B	C
a	b	c
a	b	d
c	b	c
c	b	d
d	e	a

Parte F

$\pi _{B}(R)$

Respuesta

B
b
c
e

Parte G

$\sigma _{A=C}(R\times S)$

Respuesta

A	B	?	C
a	b	e	a
c	b	b	c
d	e	b	d

Parte H

S / T

Respuesta

B
b

Parte I

R |x| S (R.B<S.C)

Respuesta

A	B	C
a	b	c
a	b	d
c	b	c
c	b	d

no falta una columna?

Ejercicio 02.01 parte de sql

-- (f) Mediante SQL indicar cuantos albumes tiene cada PlayList. Debe devolver nombre -- de la PlayList y cantidad de albumes.

SELECT p.playlist_id, p.name, COUNT(DISTINCT a.album_id) FROM playlist as p LEFT OUTER JOIN playlist_track as pt ON pt.playlist_id = p.playlist_id LEFT OUTER JOIN track as t ON t.track_id = pt.track_id LEFT OUTER JOIN album as a ON a.album_id = t.album_id GROUP BY p.playlist_id, p.name;

-- (g) Mediante SQL listar los nombres de los empleados (Employee) mayores de 25 a~nos -- que tienen al menos una factura (Invoice) con mas de 10 tems.

SELECT DISTINCT e.employee_id, e.first_name FROM employee as e INNER JOIN customer as c ON c.support_rep_id = e.employee_id INNER JOIN invoice as i ON i.customer_id = c.customer_id INNER JOIN invoice_line as il ON i.invoice_id = il.invoice_id WHERE date_part('year', e.birth_date) < 1995 GROUP BY e.employee_id, e.first_name, i.invoice_id HAVING SUM(il.quantity) > 10;

-- (i) Mediante SQL listar los nombres de los empleados que soportan clientes con m�as de -- 10 facturas.

SELECT DISTINCT e.employee_id, e.first_name FROM employee as e INNER JOIN customer as c ON c.support_rep_id = e.employee_id INNER JOIN invoice as i ON i.customer_id = c.customer_id GROUP BY c.customer_id, e.employee_id, e.first_name HAVING COUNT( i.invoice_id)>10;

-- (j) Mediante SQL listar los empleados junto a su jefe. Las tuplas resultantes tendr�an la -- siguiente forma: (nombre empleado (FirstName), apellido de empleado (LastName), -- nombre jefe, apellido de jefe)

SELECT e.employee_id, e.first_name , e.last_name, j.first_name as Jefecito , j.last_name FROM employee as e INNER JOIN employee as j ON j.employee_id = e.reports_to;

-- (k) Resolver el tem anterior pero que no falte ningun empleado en el listado

SELECT e.employee_id, e.first_name , e.last_name, j.first_name as Jefecito , j.last_name FROM employee as e LEFT OUTER JOIN employee as j ON j.employee_id = e.reports_to;

-- (l) Obtener mediante SQL el promedio de tracks comprados en las facturas de cada -- clientes. Es decir si en una factura compro 8 tracks y en otra 4 el promedio es 6. SELECT r.customer_id, avg(r.count) FROM ( SELECT c.customer_id, count(il.quantity) FROM customer as c INNER JOIN invoice as i ON i.customer_id = c.customer_id INNER JOIN invoice_line as il ON il.invoice_id = i.invoice_id GROUP BY c.customer_id, i.invoice_id ORDER BY c.customer_id ) as r GROUP BY r.customer_id;

-- (m) Obtener para cada empleado el total de tracks del genero "Rock" comprados por los -- clientes que soporta.

select beta.EmployeeId, count(beta.EmployeeId) as track_count from( select distinct employeeid, alpha.trackid from (select trackid FROM track join genre on (track.genreid = genre.genreid and genre.name = "Rock")) alpha join InvoiceLine il on alpha.TrackId = il.TrackId join Invoice i on il.InvoiceId = i.InvoiceId join customer c on i.CustomerId = c.CustomerId join Employee e on e.EmployeeId = c.SupportRepId) beta group by beta.EmployeeId;

Ejercicio 02

Considerando el siguiente esquema de una base de datos:
• FRECUENTA(Persona, Bar)
• SIRVE (Bar, Cerveza)
• GUSTA(Persona, Cerveza).
Expresar las siguientes consultas usando álgebra relacional (AR) sin usar funciones de agregación:
Expresar las mismas consultas usando cálculo relacional de tuplas (CRT) y cálculo relacional de dominios (CRD).

• a. Bares que sirven alguna cerveza que le guste a “Juan K.”
• b. Personas que frecuentan al menos un bar que sirve alguna cerveza que les guste.
• c. Personas que no frecuenten ningún bar que sirva una cerveza que les guste.
• d. Personas que frecuentan sólo bares que sirven alguna cerveza que les guste. (Asumir que cada persona gusta al menos de una cerveza y frecuenta al menos un bar).
• e. Personas que frecuentan todos los bares. (Asumir que todos los bares sirven al menos una cerveza).

a.

AR: $\Pi _{bar}((\sigma _{persona\ =\ Juan\ K.}(Gusta)\ join\ Sirve))$
CRT: $\{R\ |\ \exists G\ \in \ Gusta\ \exists S\ \in \ Sirve(G.persona\ =\ Juan\ K.\ \land \ G.cerveza=S.cerveza\ \land \ R.bar=S.bar)\}$
CRD: ${\displaystyle }$

b.

AR: $\Pi _{1}(\sigma _{1\ =\ 5\ \land \ 2\ =\ 3\ \land \ 4\ =\ 6}(Frecuenta\times Sirve\times Gusta))$

CRT: $\{R\ |\ \exists G\ \in \ Gusta\ \exists S\ \in \ Sirve\ \exists F\ \in \ Frecuenta$ $(G.persona\ =\ R.persona\ \land \ G.cerveza=S.cerveza\ \land \ F.bar=S.bar\ \land \ F.persona\ =\ G.persona)\}$

CRD: Completar.

c.

AR: $(\Pi _{persona}(Frecuenta)\ \bigcup \ \Pi _{persona}(Gusta))-ejercicio\ b$
CRT: ${\displaystyle }$
CRD: ${\displaystyle }$

d.

AR: $\rho (PB\_Gustan,\Pi _{persona,bar}(join(Frecuenta\ \times \ Sirve\ \times \ Gusta)))$
$\Pi {persona}(Frecuenta)-\Pi {persona}(Frecuenta-\ PB\_Gustan)$
CRT: ${\displaystyle }$
CRD: ${\displaystyle }$

e.

AR: $\Pi _{persona}(Frecuenta\ \div \ \Pi _{bar}(Sirve))$
CRT: ${\displaystyle }$
CRD: ${\displaystyle }$

Ejercicio 03

(Revisar : le restaria tambien la alumnas que cursan materias de Jeff)

• REN (Alumnas, SEL{a.sexo='F' && a.edad>21}(Alumno a))
• REN (Materias, SEL{c.desc='cs. comp' && p.nombre<>'Jeff Ullman'}(Materia m |x| Plan pl |x| Carrera c |x| Dicta d |x| Profesor p) )
• PRY{nombre}( (Alumnas |x| Cursa) / Materias )

Ejercicio 07

• a

SELECT c.*,SUM(o.cantidad) FROM clientes c
INNER JOIN ordenes o ON o.nombc=c.nombc
GROUP BY c.nombc

• b

SELECT p.nombre_proveedor FROM proveedores p
INNER JOIN ordenes o ON o.item=p.item
GROUP BY p.nombre_proveedor
HAVING 2000 < SUM(o.cantidad)

• c

SELECT c.nombc FROM clientes
INNER JOIN ordenes o ON o.nombc=c.nombc
WHERE 15 < ( 	SELECT SUM(o.cantidad) FROM orders
		INNER JOIN ordenes o ON o.nombc=c.nombc
		WHERE item='lampara' )

Sin usar anidadas podría ser:

SELECT o.nombre_cliente FROM Clientes c 
INNER JOIN ordenes o ON c.nombre_cliente = o.nombre_cliente
WHERE o.item ='lampara' GROUP BY o.nombre_cliente 
HAVING SUM(o.cantidad)>15

• d

SELECT c.nombc FROM clientes
INNER JOIN ordenes o ON o.nombc=c.nombc
GROUP BY o.item
HAVING SUM(o.cantidad) > 
	(SELECT AVG(cant) FROM
	( 	
	SELECT c.nombc, SUM(o.cantidad) as cant FROM orders
	INNER JOIN ordenes o ON o.nombc=c.nombc
	GROUP BY o.item
	)
	)

• e

UPDATE ordenes o SET o.cantidad = o.cantidad * 1.10
WHERE
	10000 < ( SELECT c.saldo FROM clientes
	  	  WHERE o.nombc=c.nombc
		  )

Yo propongo esta otra manera:

UPDATE Ordenes o 
SET o.Cantidad = o.Cantidad*1.1 
WHERE o.nombre_cliente IN
(SELECT nombre_cliente FROM Clientes c WHERE c.saldo > 10000)

Ejercicio 09

es_amigo(a,b) = (a,b) in amigo || (b,a) in amigo

• a.i

{ n:persona | ~EX a:persona . es_amigo(n.progenitor,a) }

• a.ii

{ n:persona | FA a:persona . es_amigo(n,a) -> es_amigo(n.progenitor,n) }

• a.iii

{ n:persona | #{a:persona | es_amigo(n,a)} > 8 }

Este es válido si valiera la simetría en la relación amigo:

SELECT p.nombre from persona p
INNER JOIN amigo a on a.nombre1=p.nombre
GROUP BY p.nombre
HAVING 8 < COUNT(*)

Este contemple la asimetría de la misma:

SELECT p.nombre FROM Persona p WHERE
8 < (SELECT count(*) FROM amigo WHERE nombre1 = p.nombre OR nombre2 = p.nombre)

Ejercicio 11

• a

SELECT m.* FROM miembro m
INNER JOIN organiza o ON o.codigo_m = c.codigo_m
WHERE NOT EXISTS ( SELECT p.* FROM participa p
                   INNER JOIN evento e ON e.codigo_e = p.codigo_e
                   WHERE p.codigo_m = m.codigo_m )

O bien:

SELECT m.* FROM Organiza o 
INNER JOIN Miembro m ON m.codigo_m = o.codigo_m   
WHERE m.codigo_m NOT IN (SELECT codigo_m FROM participa)

• b

SELECT COUNT(e.codigo_E), Localidad FROM (EVENTO e
INNER JOIN Auditorio a ON e.codigo_A = a.codigo_A)
INNER JOIN 
      (SELECT codigo_e, count(codigo_m) as inscriptos FROM Participa GROUP BY codigo_E) ev 
      ON  ev.codigo_E = e.Codigo_E
WHERE e.cupo_Minimo <= inscriptos AND Especialidad = "Medica"
GROUP BY Localidad

• c

SELECT e.* FROM evento e
WHERE e.fecha_limite_inscripcion < TODAY()
AND e.cupo_minimo < ( SELECT COUNT(*) FROM participa p
                      INNER JOIN evento e ON e.codigo_e = p.codigo_e
                      WHERE p.codigo_m = m.codigo_m )

• d

SELECT m.* FROM miembro m
WHERE NOT EXISTS ( SELECT e.* FROM evento e
                   WHERE NOT EXISTS ( SELECT p.* FROM participa p
                                      WHERE p.codigo_m = m.codigo_m
                                      AND p.codigo_e = e.codigo_e ) )

• e

DELETE FROM auditorio a
WHERE NOT EXISTS ( SELECT e.* FROM evento e
                   WHERE e.codigo_a = a.codigo_a )

• f
• g

Ejercicio 23

a.1

$\rho (TE,\pi _{tor,eq1}(P)\cup \pi _{tor,eq2}(P))$

$\rho (TEPar(1\rightarrow tor1,2\rightarrow eq1,3\rightarrow tor2,4\rightarrow eq2),TE\times TE)$

$\rho (J2,\pi _{eq1}(\sigma _{(eq1=eq2)\land (tor1\neq tor2)}(TEPar)))$

$\rho (TETerna(1\rightarrow tor1,2\rightarrow eq1,3\rightarrow tor2,4\rightarrow eq2,5\rightarrow tor3,6\rightarrow eq3),TE\times TE\times TE)$

$\rho (J3,\pi _{eq1}(\sigma _{(eq1=eq2)\land (eq2=eq3)\land (tor1\neq tor2)\land (tor3\neq tor2)\land (tor1\neq tor3)}(TETerna)))$

Respuesta: $J2-J3$

a.2

$\rho (Perdidos,\pi _{tor,eq1}(\sigma _{puntos1=0}(Partido))\cup \pi _{tor,eq2}(\sigma _{puntos2=0}(Partido)))$

$\rho (Invictos,\pi _{tor,eq1}(Partido)\cup \pi _{tor,eq2}(Partido)-Perdidos)$

$\rho (InvictoPar(1\rightarrow tor1,2\rightarrow eq1,3\rightarrow tor2,4\rightarrow eq2),Invictos\times Invictos)$

$\rho (InvictoDoble,\pi _{eq1}(\sigma _{(eq1=eq2)\land (tor1\neq tor2)}(InvictoPar)))$

$\rho (JugadorPar(1\rightarrow nom1,2\rightarrow ed1,3\rightarrow eq1,4\rightarrow nom2,5\rightarrow ed2,6\rightarrow eq2),Jugador\times Jugador)$

$\rho (MasChicos,\pi _{nom,eq}(Jugador)-\pi _{nom1,eq1}(\sigma _{(ed1>ed2)\wedge (eq1=eq2)}(JugadorPar)))$

$\rho (MasViejos,\pi _{nom,eq}(Jugador)-\pi _{nom1,eq1}(\sigma _{(ed1<ed2)\wedge (eq1=eq2)}(JugadorPar)))$

b.1

SELECT DISTINCT P2.equipo1
    FROM Partidos as P2
    WHERE NOT EXISTS (
        SELECT * FROM Partidos as P3
        WHERE P2.equipo1 = P3.equipo1 AND P2.torneo = P3.torneo AND P3.goles1 < P3.goles2
    )
    GROUP BY P2.equipo1
    HAVING COUNT(DISTINCT P2.torneo) > 1

b.2

SELECT P.torneo FROM partidos P
WHERE NOT EXISTS
(
	SELECT P2.equipo1
	FROM partidos P2
	WHERE P2.torneo = P.torneo
	GROUP BY P2.equipo1
	HAVING SUM(P2.puntos1) >
	(
	   SELECT SUM(P3.puntos1)
	   FROM partidos as P3
	   WHERE P3.torneo = P.torneo
	   AND P3.equipo1 = P.equipo1
	)
)
GROUP BY P.torneo
HAVING COUNT(DISTINCT P.equipo1) > 1;