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"|b |
| |- align="center" | | |- align="center" |
| | colspan="1"|e | | | colspan="1"|e |
Línea 250: |
Línea 252: |
| |} | | |} |
| no falta una columna? | | 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== |