Act2.2_Labo3

--1 Por cada usuario listar el nombre, apellido y el nombre del tipo de usuario.
SELECT U.Nombre, U.Apellido, T.TipoUsuario FROM Usuarios U, TiposUsuario T WHERE U.IDTipoUsuario=T.IDTipoUsuario
--usando JOIN:
SELECT U.Nombre, U.Apellido, T.TipoUsuario FROM Usuarios U INNER JOIN TiposUsuario T ON U.IDTipoUsuario=T.IDTipoUsuario

--2 Listar el ID, nombre, apellido y tipo de usuario de aquellos usuario que sean
--del tipo 'Suscripción Free' o 'Suscripción Básica'
--opc A
SELECT U.IDUsuario, U.Nombre, U.Apellido, T.TipoUsuario FROM Usuarios U INNER JOIN TiposUsuario T ON U.IDTipoUsuario=T.IDTipoUsuario WHERE U.IDTipoUsuario<=2
--opc B
SELECT U.IDUsuario, U.Nombre, U.Apellido, T.TipoUsuario FROM Usuarios U INNER JOIN TiposUsuario T ON U.IDTipoUsuario=T.IDTipoUsuario WHERE T.TipoUsuario='Suscripción Free' OR T.TipoUsuario='Suscripción Básica'

--3 Listar los nombres de archivos, extensión, tamaño expresado en Megabytes y el nombre del tipo de archivo.
--NOTA: En la tabla Archivos el tamaño está expresado en Bytes.
--opc A
SELECT A.Nombre, A.Extension, A.Tamaño/1048576 AS TamañoMegabytes, T.TipoArchivo FROM Archivos A INNER JOIN TiposArchivos T ON A.IDTipoArchivo=T.IDTipoArchivo
--opc B
SELECT A.Nombre, A.Extension, CAST(A.Tamaño/1048576.0 AS DECIMAL(10, 2)) AS TamañoMegabytes, T.TipoArchivo FROM Archivos A INNER JOIN TiposArchivos T ON A.IDTipoArchivo=T.IDTipoArchivo

--4 Listar los nombres de archivos junto a la extensión con el siguiente formato
--'NombreArchivo.extension'. Por ejemplo, 'Actividad.pdf'.
--Sólo listar aquellos cuyo tipo de archivo contenga los términos 'ZIP', 'Word', 'Excel', 'Javascript' o 'GIF'
SELECT Nombre+'.'+Extension FROM Archivos
WHERE Extension LIKE'%ZIP%' OR Extension LIKE'%Word%' OR Extension LIKE'%Excel%'
OR Extension LIKE'%Javascript%' OR Extension LIKE'%GIF%'

--5 Listar los nombres de archivos, su extensión, el tamaño en megabytes y la
--fecha de creación de aquellos archivos que le pertenezca al usuario dueño
--con nombre 'Michael' y apellido 'Williams'.
SELECT A.Nombre, A.Extension, CAST(A.Tamaño/1048576.0 AS DECIMAL(10, 2)) AS TamañoMegabytes, A.FechaCreacion
FROM Archivos A INNER JOIN Usuarios U ON A.IDUsuarioDueño=U.IDUsuario WHERE U.Nombre='Michael' AND U.Apellido='Williams'

--6 Listar los datos completos del archivo más pesado del usuario dueño con nombre 'Michael' y apellido 'Williams'.
--Si hay varios archivos que cumplen esa condición, listarlos a todos.
SELECT top 1 WITH ties A.*
FROM Archivos A INNER JOIN Usuarios U ON A.IDUsuarioDueño=U.IDUsuario
WHERE U.Nombre='Michael' AND U.Apellido='Williams'
ORDER BY Tamaño DESC

--7 Listar nombres de archivos, extensión, tamaño en bytes, fecha de creación y
--de modificación, apellido y nombre del usuario dueño de aquellos archivos
--cuya descripción contengan el término 'empresa' o 'presupuesto'
SELECT A.Nombre, A.Extension, A.Tamaño,
CONVERT(VARCHAR(20), A.FechaCreacion, 103) AS FechaCreacion,
CONVERT(VARCHAR(20), A.FechaUltimaModificacion, 103) AS FechaUltimaModificacion,
U.Apellido, U.Nombre
FROM Archivos A INNER JOIN Usuarios U ON A.IDUsuarioDueño=U.IDUsuario
WHERE A.Descripcion LIKE'%empresa%' OR A.Descripcion LIKE'%presupuesto%'

--8 Listar las extensiones sin repetir de aquellos usuarios dueños que tengan tipo
--de usuario 'Suscripción Plus', 'Suscripción Premium' o 'Suscripción Empresarial'
SELECT DISTINCT A.Extension
FROM Archivos A INNER JOIN TiposUsuario T ON a.IDUsuarioDueño=T.IDTipoUsuario
WHERE T.IDTipoUsuario>=3

--9 Listar los apellidos y nombres de los usuarios dueños y el tamaño del archivo
--de los tres archivos con extensión 'zip' más pesados. Puede ocurrir que el
--mismo usuario aparezca varias veces en el listado.
SELECT top 3 A.Tamaño, U.Apellido, U.Nombre
FROM Archivos A INNER JOIN Usuarios U ON A.IDUsuarioDueño=U.IDUsuario
WHERE A.Extension='zip' ORDER BY A.Tamaño DESC

--10 Por cada archivo listar el nombre del archivo, la extensión, el tamaño en bytes,
--el nombre del tipo de archivo y el tamaño calculado en su mayor expresión y la
--unidad calculada. Siendo Gigabytes si al menos pesa un gigabyte, Megabytes
--si al menos pesa un megabyte, Kilobyte si al menos pesa un kilobyte o en su
--defecto expresado en bytes.
--Por ejemplo, si el archivo imagen.jpg pesa 40960 bytes entonces debe figurar
--40 en la columna Tamaño Calculado y 'Kilobytes' en la columna unidad.
SELECT A.Nombre, A.Extension, A.Tamaño, T.TipoArchivo,
CASE
WHEN A.Tamaño < 1024 THEN A.Tamaño
WHEN A.Tamaño BETWEEN 1024 AND 1048575 THEN A.Tamaño/1024
WHEN A.Tamaño BETWEEN 1048576 AND (1E+09-1) THEN A.Tamaño/1048576
WHEN A.Tamaño>= 1E+09 THEN A.Tamaño/1E+09
END AS TamañoCalculado,
CASE
WHEN A.Tamaño < 1024 THEN 'Byte'
WHEN A.Tamaño BETWEEN 1024 AND 1048575 THEN 'Kilobyte'
WHEN A.Tamaño BETWEEN 1048576 AND (1E+09 -1) THEN 'Megabyte'
WHEN A.Tamaño>= 1E+09 THEN 'Gigabyte'
END AS Unidad
FROM Archivos A INNER JOIN TiposArchivos T ON A.IDTipoArchivo=T.IDTipoArchivo

--11 Listar los nombres de archivo y extensión de los archivos que han sido compartidos.
SELECT A.Nombre, A.Extension FROM Archivos A INNER JOIN ArchivosCompartidos C ON A.IDArchivo=C.IDArchivo

--12 Listar los nombres de archivo y extensión de los archivos que han sido
--compartidos a usuarios con apellido 'Clarck' o 'Jones'
SELECT A.Nombre, A.Extension FROM Archivos A
INNER JOIN ArchivosCompartidos C ON A.IDArchivo=C.IDArchivo
INNER JOIN Usuarios U ON A.IDUsuarioDueño=U.IDUsuario
WHERE U.Apellido='Clarck' OR U.Apellido='Jones'

--13 Listar los nombres de archivo, extensión, apellidos y nombres de los usuarios
--a quienes se le hayan compartido archivos con permiso de 'Escritura'
SELECT A.Nombre, A.Extension, U.Apellido, U.Nombre FROM Archivos A
INNER JOIN ArchivosCompartidos C ON A.IDArchivo=C.IDArchivo
INNER JOIN Usuarios U ON A.IDUsuarioDueño=U.IDUsuario
INNER JOIN Permisos P ON C.IDPermiso=P.IDPermiso
WHERE P.Nombre='Escritura'

--14 Listar los nombres de archivos y extensión de los archivos que no han sido compartidos.
SELECT A.Nombre, A.Extension FROM Archivos A
LEFT JOIN ArchivosCompartidos C ON A.IDArchivo=C.IDArchivo
WHERE C.IDArchivo IS NULL

--15 Listar los apellidos y nombres de los usuarios dueños que tienen archivos eliminados.
SELECT DISTINCT U.Apellido, U.Nombre
FROM Archivos A INNER JOIN Usuarios U ON A.IDUsuarioDueño=U.IDUsuario
WHERE A.Eliminado=1

--16 Listar los nombres de los tipos de suscripciones, sin repetir, que tienen
--archivos que pesan al menos 120 Megabytes.
SELECT DISTINCT TU.TipoUsuario AS tipoSuscripcion
FROM TiposUsuario TU INNER JOIN Archivos A ON TU.IDTipoUsuario=A.IDUsuarioDueño
WHERE A.Tamaño/1048576>=120

--17 Listar los apellidos y nombres de los usuarios dueños, nombre del archivo,
--extensión, fecha de creación, fecha de modificación y la cantidad de días
--transcurridos desde la última modificación. Sólo incluir a los archivos que se
--hayan modificado (fecha de modificación distinta a la fecha de creación).
SELECT U.Apellido, U.Nombre, A.Nombre, A.Extension, A.FechaCreacion, A.FechaUltimaModificacion
FROM Archivos A INNER JOIN Usuarios U ON A.IDUsuarioDueño=U.IDUsuario
WHERE A.FechaCreacion<>A.FechaUltimaModificacion

--18 Listar nombres de archivos, extensión, tamaño, apellidos y nombres del
--usuario dueño del archivo, apellido y nombre del usuario que tiene el archivo
--compartido y el nombre de permiso otorgado.
SELECT  A.Nombre, A.Extension, A.Tamaño, UD.Apellido+', '+UD.Nombre AS Dueño,
UC.Nombre+', '+UC.Apellido AS UserCompartido,
P.Nombre AS TipoPermiso
FROM Archivos A
INNER JOIN Usuarios UD ON A.IDUsuarioDueño=UD.IDUsuario
INNER JOIN ArchivosCompartidos C ON A.IDArchivo=C.IDArchivo
INNER JOIN Usuarios UC ON C.IDUsuario=UC.IDUsuario
INNER JOIN Permisos P ON C.IDPermiso=P.IDPermiso

--19 Listar nombres de archivos, extensión, tamaño, apellidos y nombres del
--usuario dueño del archivo, apellido y nombre del usuario que tiene el archivo
--compartido y el nombre de permiso otorgado. Sólo listar aquellos registros
--cuyos tipos de usuarios coincidan tanto para el dueño como para el usuario al
--que le comparten el archivo.
SELECT  A.Nombre, A.Extension, A.Tamaño, UD.Apellido+', '+UD.Nombre AS Dueño,
UC.Nombre+', '+UC.Apellido AS UserCompartido,
P.Nombre AS TipoPermiso
FROM Archivos A
INNER JOIN Usuarios UD ON A.IDUsuarioDueño=UD.IDUsuario
INNER JOIN ArchivosCompartidos C ON A.IDArchivo=C.IDArchivo
INNER JOIN Usuarios UC ON C.IDUsuario=UC.IDUsuario
INNER JOIN Permisos P ON C.IDPermiso=P.IDPermiso
WHERE UD.IDTipoUsuario=UC.IDTipoUsuario

--20 Apellidos y nombres de los usuarios que tengan compartido o sean dueños del
--archivo con nombre 'Documento Legal'.
SELECT U.Apellido, U.Nombre
FROM Archivos A
INNER JOIN Usuarios U ON A.IDUsuarioDueño = U.IDUsuario
WHERE A.Nombre = 'Documento Legal'
UNION
SELECT UC.Apellido, UC.Nombre
FROM Archivos A
INNER JOIN ArchivosCompartidos AC ON A.IDArchivo = AC.IDArchivo
INNER JOIN Usuarios UC ON AC.IDUsuario = UC.IDUsuario
WHERE A.Nombre = 'Documento Legal';