| Revisión actual |
Tu texto |
| Línea 178: |
Línea 178: |
| |- align="center" | | |- align="center" |
| | colspan="1"|c | | | colspan="1"|c |
| | |- align="center" |
| | | colspan="1"|d |
| |- align="center" | | |- align="center" |
| | colspan="1"|e | | | colspan="1"|e |
| |} | | |} |
| | |
| | ¿Puede ser que la 'd' no vaya? |
|
| |
|
| ====Parte G==== | | ====Parte G==== |
| Línea 249: |
Línea 253: |
| | colspan="1"|d | | | 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== | | ==Ejercicio 02== |
| Línea 380: |
Línea 310: |
|
| |
|
| ==Ejercicio 03== | | ==Ejercicio 03== |
| (Revisar : le restaria tambien la alumnas que cursan materias de Jeff) | | (Revisar) |
| * REN (Alumnas, SEL{a.sexo='F' && a.edad>21}(Alumno a)) | | * 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) ) | | * 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) ) |
| Línea 447: |
Línea 377: |
| *a.iii | | *a.iii |
| { n:persona | #{a:persona | es_amigo(n,a)} > 8 } | | { 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 | | SELECT p.nombre from persona p |
| Línea 454: |
Línea 382: |
| GROUP BY p.nombre | | GROUP BY p.nombre |
| HAVING 8 < COUNT(*) | | 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 10== |
| Línea 468: |
Línea 391: |
| INNER JOIN evento e ON e.codigo_e = p.codigo_e | | INNER JOIN evento e ON e.codigo_e = p.codigo_e |
| WHERE p.codigo_m = m.codigo_m ) | | 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 | | *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 | | *c |
| SELECT e.* FROM evento e | | SELECT e.* FROM evento e |
