Recuperatorio SQL 1er Cuat 2022

Enunciado

El examen deberá ser entregado en un archivo de texto (un .sql o notebook) de tal forma que las consultas implementadas puedan ser ejecutadas directamente. La base de datos a usar es la chinook del laboratorio (puede usarse la notebook privada).
La entrega se debe realizar sin excepción en el campus y en la tarea correspondiente antes de la hora de finalización.
La aprobación requiere de tener al menos 4 (cuatro) consultas correctamente realizadas.

Cada consulta deberá resolverse en una única consulta. No usar select en el from salvo que no haya otra manera de resolver la consulta y en ese caso podría usar CTE.

1. Obtener la cantidad de Invoices por track, deberá responder el identificador de track, el nombre y la cantidad de Invoices donde participa.
2. Listar todos los datos de los tracks cuya duración sea mayor al promedio de la duración de los tracks de Rock.
3. Obtener el nombre y apellido de los clientes que son atendidos por los empleados que atienden a la mayor cantidad de clientes. La relación entre empleado y cliente se da por la clave foránea en cliente SupportRepId.
4. Listar los tracks más vendidos. Es decir aquellos tales que no hay un track que tenga más ventas.
5. Realizar una consulta corrrelacionada que me devuelva las invoices (ID, Fecha y BillingAddress) tales que en sus ítems (InvoiceLine) no haya ningún precio unitario de 0.99
6. Realizar una consulta que devuelva todos los empleados contratados después que Park Margaret. La fecha de contratación es HireDate.

Solución

Ejercicio 1

SELECT tr.track_id, tr.name, COUNT(il.invoice_id) FROM
track tr INNER JOIN invoice_line il ON tr.track_id = il.track_id
GROUP BY tr.track_id, tr.name

Ejercicio 2

SELECT tr.* FROM
track tr
WHERE tr.milliseconds > (

   SELECT AVG(tr1.milliseconds) FROM track tr1 

   INNER JOIN genre g ON tr1.genre_id = g.genre_id 

   WHERE g.name = 'Rock' 

 );

Ejericicio 3

WITH employee_customer_count AS (

   SELECT e.employee_id, COUNT(c.customer_id) as customer_count FROM 
   employee e inner join customer c on e.employee_id = c.support_rep_id 
   group by employee_id

)

SELECT c.first_name, c.last_name FROM
customer c INNER JOIN employee e ON c.support_rep_id = e.employee_id
WHERE e.employee_id IN (

   SELECT e1.employee_id FROM 

       employee e1 INNER JOIN customer c1 ON e1.employee_id = c1.support_rep_id 

       GROUP BY e1.employee_id 

       HAVING COUNT(customer_id) = (SELECT MAX(ecc.customer_count) FROM employee_customer_count ecc) 
   )

Ejercicio 4

WITH track_total_sold AS (

   SELECT tr.track_id, tr.name, SUM(il.quantity) AS total_sold FROM 

   track tr INNER JOIN invoice_line il ON tr.track_id = il.track_id 

   GROUP BY tr.track_id, tr.name 

)

SELECT tr.track_id, tr.name FROM
track tr INNER JOIN invoice_line il ON tr.track_id = il.track_id
GROUP BY tr.track_id, tr.name
HAVING SUM(il.quantity) = (SELECT MAX(track_total_sold.total_sold) FROM track_total_sold);

Ejercicio 5

SELECT i.invoice_id, i.invoice_date, i.billing_address FROM
invoice i
WHERE NOT EXISTS (

   SELECT il.invoice_id FROM 

   invoice_line il  

   WHERE il.invoice_id = i.invoice_id AND il.unit_price = 0.99 

)

Ejercicio 6

SELECT e.employee_id, e.last_name, e.first_name FROM
employee e
WHERE e.hire_date > (SELECT e1.hire_date FROM employee e1

       WHERE e1.last_name = 'Park' AND e1.first_name = 'Margaret');