Edición de «Práctica de Lenguajes de Consulta (Bases de Datos)»

__NOTOC__
{{Back|Bases de Datos}}

==Ejercicio 01==
Dadas las relaciones R y S calcular:

==== Parte A ====
R U S

'''Respuesta'''

{| style="text-align: center;" border="1" cellpadding="3" cellspacing="0"
|- align="center" bgcolor="#FFFFDD"
! colspan="1"|1
! colspan="1"|2
|- align="center"
| colspan="1"|a
| colspan="1"|b
|- align="center"
| colspan="1"|b
| colspan="1"|c
|- align="center"
| colspan="1"|c
| colspan="1"|b
|- align="center"
| colspan="1"|d
| colspan="1"|e
|- align="center"
| colspan="1"|e
| colspan="1"|a
|- align="center"
| colspan="1"|b
| colspan="1"|d
|}

====Parte B====
R - S

'''Respuesta'''

{| style="text-align: center;" border="1" cellpadding="3" cellspacing="0"
|- align="center" bgcolor="#FFFFDD"
! colspan="1"|1
! colspan="1"|2
|- align="center"
| colspan="1"|a
| colspan="1"|b
|- align="center"
| colspan="1"|c
| colspan="1"|b
|- align="center"
| colspan="1"|d
| colspan="1"|e
|}

====Parte C====
<math>R \times S</math>

'''Respuesta'''

{| style="text-align: center;" border="1" cellpadding="3" cellspacing="0"
|- align="center" bgcolor="#FFFFDD"
! colspan="1"|A
! colspan="1"|B
! colspan="1"|?
! colspan="1"|C
|- align="center"
| colspan="1"|a
| colspan="1"|b
| colspan="1"|b
| colspan="1"|c
|- align="center"
| colspan="1"|a
| colspan="1"|b
| colspan="1"|e
| colspan="1"|a
|- align="center"
| colspan="1"|a
| colspan="1"|b
| colspan="1"|b
| colspan="1"|d
|- align="center"
| colspan="1"|b
| colspan="1"|c
| colspan="1"|b
| colspan="1"|c
|- align="center"
| colspan="1"|b
| colspan="1"|c
| colspan="1"|e
| colspan="1"|a
|- align="center"
| colspan="1"|b
| colspan="1"|c
| colspan="1"|b
| colspan="1"|d
|- align="center"
| colspan="1"|c
| colspan="1"|b
| colspan="1"|b
| colspan="1"|c
|- align="center"
| colspan="1"|c
| colspan="1"|b
| colspan="1"|e
| colspan="1"|a
|- align="center"
| colspan="1"|c
| colspan="1"|b
| colspan="1"|b
| colspan="1"|d
|- align="center"
| colspan="1"|d
| colspan="1"|e
| colspan="1"|b
| colspan="1"|c
|- align="center"
| colspan="1"|d
| colspan="1"|e
| colspan="1"|e
| colspan="1"|a
|- align="center"
| colspan="1"|d
| colspan="1"|e
| colspan="1"|b
| colspan="1"|d
|}

====Parte D====
<math>\rho(V(2 \rightarrow B1,3 \rightarrow B2), R \times S)</math>

'''Respuesta'''

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

====Parte E====
R |X| S

'''Respuesta'''

{| style="text-align: center;" border="1" cellpadding="3" cellspacing="0"
|- align="center" bgcolor="#FFFFDD"
! colspan="1"|A
! colspan="1"|B
! colspan="1"|C
|- align="center"
| colspan="1"|a
| colspan="1"|b
| colspan="1"|c
|- align="center"
| colspan="1"|a
| colspan="1"|b
| colspan="1"|d
|- align="center"
| colspan="1"|c
| colspan="1"|b
| colspan="1"|c
|- align="center"
| colspan="1"|c
| colspan="1"|b
| colspan="1"|d
|- align="center"
| colspan="1"|d
| colspan="1"|e
| colspan="1"|a
|}

====Parte F====
<math>\pi_B(R)</math>

'''Respuesta'''

{| style="text-align: center;" border="1" cellpadding="3" cellspacing="0"
|- align="center" bgcolor="#FFFFDD"
! colspan="1"|B
|- align="center"
| colspan="1"|b
|- align="center"
| colspan="1"|c
|- align="center"
| colspan="1"|e
|}

====Parte G====
<math>\sigma_{A=C}(R \times S)</math>

'''Respuesta'''

{| style="text-align: center;" border="1" cellpadding="3" cellspacing="0"
|- align="center" bgcolor="#FFFFDD"
! colspan="1"|A
! colspan="1"|B
! colspan="1"|?
! colspan="1"|C
|- align="center"
| colspan="1"|a
| colspan="1"|b
| colspan="1"|e
| colspan="1"|a
|- align="center"
| colspan="1"|c
| colspan="1"|b
| colspan="1"|b
| colspan="1"|c
|- align="center"
| colspan="1"|d
| colspan="1"|e
| colspan="1"|b
| colspan="1"|d
|}

====Parte H====
S / T

'''Respuesta'''

{| style="text-align: center;" border="1" cellpadding="3" cellspacing="0"
|- align="center" bgcolor="#FFFFDD"
! colspan="1"|B
|- align="center"
| colspan="1"|b
|}

====Parte I====
R |x| S (R.B<S.C)

'''Respuesta'''

{| style="text-align: center;" border="1" cellpadding="3" cellspacing="0"
|- align="center" bgcolor="#FFFFDD"
! colspan="1"|A
! colspan="1"|B
! colspan="1"|C
|- align="center"
| colspan="1"|a
| colspan="1"|b
| colspan="1"|c
|- align="center"
| colspan="1"|a
| colspan="1"|b
| colspan="1"|d
|- align="center"
| colspan="1"|c
| colspan="1"|b
| colspan="1"|c
|- align="center"
| colspan="1"|c
| colspan="1"|b
| colspan="1"|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==
<i>
Considerando el siguiente esquema de una base de datos:<br>
• FRECUENTA(Persona, Bar)<br>
• SIRVE (Bar, Cerveza)<br>
• GUSTA(Persona, Cerveza).<br>
Expresar las siguientes consultas usando álgebra relacional (AR) sin usar funciones de agregación:<br>
Expresar las mismas consultas usando cálculo relacional de tuplas (CRT) y cálculo relacional de dominios (CRD).<br>
*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).
</i>

===a.===
AR: <math>\Pi_{bar}((\sigma_{persona \ = \ Juan \ K.}(Gusta) \ join \ Sirve))</math>
<br>
CRT: <math>\{R \ | \ \exist G \ \in \ Gusta \ \exist S \ \in \ Sirve (G.persona \ = \ Juan \ K. \ \and \ G.cerveza = S.cerveza \ \and \ R.bar = S.bar)\}</math>
<br>
CRD: <math></math>
<br>

===b.===
AR: <math>\Pi_{1}(\sigma_{1 \ = \ 5 \ \and \ 2 \ = \ 3 \ \and \ 4 \ = \ 6}(Frecuenta \times Sirve \times Gusta))</math>

CRT: <math>\{R \ | \ \exist G \ \in \ Gusta \ \exist S \ \in \ Sirve \ \exist F \ \in \ Frecuenta</math>
<math>(G.persona \ = \ R.persona \ \and \ G.cerveza = S.cerveza \ \and \ F.bar = S.bar \ \and \ F.persona \ = \ G.persona)\}</math>

CRD: Completar.

===c.===
AR: <math>(\Pi_{persona}(Frecuenta) \ \bigcup \ \Pi_{persona}(Gusta)) - ejercicio\ b</math>
<br>
CRT: <math></math>
<br>
CRD: <math></math>
<br>
===d.===
AR: <math>\rho(PB\_Gustan, \Pi_{persona, bar}(join(Frecuenta \ \times \ Sirve \ \times \ Gusta)))</math>
<br>
<math>\Pi{persona}(Frecuenta) - \Pi{persona}(Frecuenta - \ PB\_Gustan)</math>
<br>
CRT: <math></math>
<br>
CRD: <math></math>
<br>
===e.===
AR: <math>\Pi_{persona}(Frecuenta \ \div \ \Pi_{bar}(Sirve))</math>
<br>
CRT: <math></math>
<br>
CRD: <math></math>
<br>

==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 04==
==Ejercicio 05==
==Ejercicio 06==
==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 FROM proveedores p
 INNER JOIN ordenes o ON o.item=p.item
 GROUP BY o.item
 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 08==
==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 10==
==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 12==
==Ejercicio 13==
==Ejercicio 14==
==Ejercicio 15==
==Ejercicio 16==
==Ejercicio 17==
==Ejercicio 18==
==Ejercicio 19==
==Ejercicio 20==
==Ejercicio 21==
==Ejercicio 22==

==Ejercicio 23==
==== a.1  ====

<math>\rho(TE, \pi_{tor, eq1}(P) \cup \pi_{tor, eq2}(P))</math>

<math>\rho(TEPar(1 \rightarrow tor1,2 \rightarrow eq1,3 \rightarrow tor2,4 \rightarrow eq2), TE \times TE)</math>

<math>\rho(J2, \pi_{eq1}(\sigma_{(eq1 = eq2) \land (tor1 \neq tor2)}(TEPar)))</math>

<math>\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)</math>

<math>\rho(J3, \pi_{eq1}(\sigma_{(eq1 = eq2) \land (eq2 = eq3) \land (tor1 \neq tor2) \land (tor3 \neq tor2) \land (tor1 \neq tor3)}(TETerna)))</math>

Respuesta: <math>J2 - J3</math>

==== a.2  ====

<math>\rho(Perdidos, \pi_{tor, eq1}(\sigma_{puntos1 = 0}(Partido)) \cup \pi_{tor, eq2}(\sigma_{puntos2 = 0}(Partido)))</math>

<math>\rho(Invictos, \pi_{tor, eq1}(Partido) \cup \pi_{tor, eq2}(Partido) - Perdidos)</math>

<math>\rho(InvictoPar(1 \rightarrow tor1,2 \rightarrow eq1,3 \rightarrow tor2,4 \rightarrow eq2), Invictos \times Invictos)</math>

<math>\rho(InvictoDoble, \pi_{eq1}(\sigma_{(eq1 = eq2) \land (tor1 \neq tor2)}(InvictoPar)))</math>

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

<math>\rho(MasChicos, \pi_{nom, eq}(Jugador) -\pi_{nom1, eq1}(\sigma_{(ed1 > ed2) \wedge (eq1 = eq2)}(JugadorPar)))</math>

<math>\rho(MasViejos, \pi_{nom, eq}(Jugador) -\pi_{nom1, eq1}(\sigma_{(ed1 < ed2) \wedge (eq1 = eq2)}(JugadorPar)))</math>

==== 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;

[[Category: Prácticas]]