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

De Cuba-Wiki

Plantilla:Back

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

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

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

Respuesta

B
b
c
e

Parte G

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:
CRT:
CRD:

b.

AR:

CRT:

CRD: Completar.

c.

AR:
CRT:
CRD:

d.

AR:

CRT:
CRD:

e.

AR:
CRT:
CRD:

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

Respuesta:

a.2

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;