Act2.3_Labo3

--1 La cantidad de archivos con extensión zip.
SELECT COUNT(*) AS cantArchZip FROM Archivos WHERE extension='zip'
--2 La cantidad de archivos que fueron modificados y, por lo tanto, su fecha de
--última modificación no es la misma que la fecha de creación.
SELECT COUNT(*) AS archModificados FROM Archivos WHERE fechaCreacion<>FechaUltimaModificacion
--3 La fecha de creación más antigua de los archivos con extensión pdf.
SELECT MIN(FechaCreacion) AS pdfAntiguo FROM Archivos WHERE Extension='pdf'
--4 La cantidad de extensiones distintas cuyos archivos tienen en su nombre o en
--su descripción la palabra 'Informe' o 'Documento'.
SELECT COUNT(DISTINCT Extension) AS extencionesIoD FROM Archivos WHERE (nombre LIKE'%Informe%' OR nombre LIKE'%Documento%') OR (descripción LIKE'%Informe%' OR descripción LIKE'%Documento%')
--5 El promedio de tamaño (expresado en Megabytes) de los archivos con
--extensión 'doc', 'docx', 'xls', 'xlsx
SELECT avg(Tamaño)/1048576 AS promTamaño FROM Archivos WHERE extensión IN('doc','docx','xls','xlsx')
--6 La cantidad de archivos que le fueron compartidos al usuario con apellido 'Clarck'
SELECT COUNT(*) AS compartidosClarck
FROM ArchivosCompartidos AC INNER JOIN Usuarios U ON AC.IDUsuario=U.IDUsuario WHERE U.Apellido ='Clarck'
--7 La cantidad de tipos de usuario que tienen asociado usuarios que registren,
--como dueños, archivos con extensión pdf.
SELECT COUNT(DISTINCT U.IDTipoUsuario) AS asociadoYdueño FROM Usuarios U
INNER JOIN Archivos A ON U.IDUsuario=A.IDUsuarioDueño
WHERE A.Extension='pdf'
--8 El tamaño máximo expresado en Megabytes de los archivos que hayan sido creados en el año 2024.
SELECT MAX(Tamaño/1048576) FROM Archivos WHERE YEAR(fechaCreacion)=2024
--9  El nombre del tipo de usuario y la cantidad de usuarios distintos de dicho tipo
--que registran, como dueños, archivos con extensión pdf.
SELECT TU.TipoUsuario AS tipoDeUsuario, COUNT(DISTINCT U.IDUsuario) AS cantUsuarios
FROM TiposUsuario TU
INNER JOIN Usuarios U ON TU.IDTipoUsuario=U.IDTipoUsuario
INNER JOIN Archivos A ON U.IDUsuario=A.IDUsuarioDueño
WHERE A.Extension='pdf'
GROUP BY TU.TipoUsuario
--10 El nombre y apellido de los usuarios dueños y la suma total del tamaño de los archivos que tengan
--compartidos con otros usuarios. Mostrar ordenado de mayor sumatoria de tamaño a menor.
SELECT U.Nombre, U.Apellido, SUM(A.Tamaño) AS TamañoArcComp FROM Usuarios U
INNER JOIN Archivos A ON U.IDUsuario=A.IDUsuarioDueño
INNER JOIN ArchivosCompartidos AC ON A.IDArchivo=AC.IDArchivo
GROUP BY U.Nombre, U.Apellido ORDER BY TamañoArcComp DESC
--11 El nombre del tipo de archivo y el promedio de tamaño de los archivos que corresponden a dicho tipo de archivo
SELECT TA.TipoArchivo, avg(A.Tamaño) AS promedioTamaño FROM Archivos A INNER JOIN TiposArchivos TA ON A.IDTipoArchivo=TA.IDTipoArchivo GROUP BY TA.TipoArchivo
--12 Por cada extensión, indicar la extensión, la cantidad de archivos con esa extensión y
--el total acumulado en bytes. Ordenado por cantidad de archivos de forma ascendente.
SELECT A.Extension, COUNT(*) AS totalArchivos, SUM(A.Tamaño) AS totalBytes
FROM Archivos A GROUP BY A.Extension ORDER BY totalArchivos
--13 Por cada usuario, indicar IDUSuario, Apellido, Nombre y la sumatoria total en
--bytes de los archivos que es dueño. Si algún usuario no registra archivos
--indicar 0 en la sumatoria total.
SELECT U.IDUsuario, U.Apellido,U.Nombre,
ISNULL(SUM(A.Tamaño), 0) AS sumaArchivos
FROM Usuarios U LEFT JOIN Archivos A ON U.IDUsuario=A.IDUsuarioDueño
GROUP BY U.IDUsuario, U.Apellido,U.Nombre
--14 Los tipos de archivos que fueron compartidos más de una vez con el permiso
--con nombre 'Lectura'
SELECT TA.TipoArchivo
FROM Archivos A INNER JOIN TiposArchivos TA ON A.IDTipoArchivo=TA.IDTipoArchivo
INNER JOIN ArchivosCompartidos AC ON A.IDArchivo=AC.IDArchivo
INNER JOIN Permisos P ON Ac.IDPermiso=P.IDPermiso
WHERE P.Nombre='Lectura'
GROUP BY TA.TipoArchivo HAVING COUNT(*)>1
--15 Escribí una consulta que requiera una función de resumen, el uso de joins y de
--having. Pega en el Foro de Actividad 2.3 en el hilo "Queries del Ejercicio 15" el
--enunciado de la consulta y la tabla en formato texto plano de lo que daría
--como resultado con los datos que trabajamos en conjunto.
--Selecionar Extension de Archivo junto con Apellido y Nombre de usuarios dueños que son dueños de más
--de un archivo con la misma extensión y además son usuarios Plus o Premium
SELECT U.Apellido, U.Nombre, A.Extension FROM Archivos A
INNER JOIN Usuarios U ON A.IDUsuarioDueño=U.IDUsuario
INNER JOIN TiposUsuario TU ON U.IDTipoUsuario=TU.IDTipoUsuario
WHERE TU.TipoUsuario LIKE '%Plus' OR TU.TipoUsuario LIKE '%Premium'
GROUP BY U.Apellido, U.Nombre, A.Extension HAVING COUNT(*)>1
--16 Por cada tipo de archivo indicar el tipo de archivo y el tamaño del archivo de
--dicho tipo que sea más pesado.
SELECT TA.TipoArchivo, MAX(A.Tamaño) AS MaxTamaño
FROM Archivos A INNER JOIN TiposArchivos TA ON A.IDTipoArchivo=TA.IDTipoArchivo
GROUP BY TA.TipoArchivo
--17 El nombre del tipo de archivo y el promedio de tamaño de los archivos que
--corresponden a dicho tipo de archivo. Solamente listar aquellos registros que
--superen los 50 Megabytes de promedio.
SELECT TA.TipoArchivo, avg(A.Tamaño) AS promedioTamaño FROM Archivos A
INNER JOIN TiposArchivos TA ON A.IDTipoArchivo=TA.IDTipoArchivo
GROUP BY(TA.TipoArchivo)
HAVING avg(A.Tamaño/1048576)>50
--18 Listar las extensiones que registren más de 2 archivos que no hayan sido compartidos.
SELECT A.Extension FROM Archivos A
LEFT JOIN ArchivosCompartidos AC ON A.IDArchivo=AC.IDArchivo
WHERE AC.IDArchivo IS NULL
GROUP BY A.Extension
HAVING COUNT(A.IDArchivo)>2