-- ACTIVIDAD 1

-- 1
SELECT * FROM Genre ORDER BY Name ASC;

-- 2
SELECT * FROM Genre ORDER BY Name ASC LIMIT 6;

-- Pro
SELECT * FROM Genre ORDER BY GenreId DESC LIMIT 2;

-- ACTIVIDAD 2

-- 1
---- Academicamente aceptable
SELECT CustomerId, FirstName, LastName
FROM Customer
WHERE SupportRepId > 3;

---- Perfecta
SELECT SupportRepId, COUNT(*) AS TotalClientes
FROM Customer
GROUP BY SupportRepId
HAVING COUNT(*) > 3;

-- 2
SELECT * FROM Customer
WHERE Country = 'Brazil'

-- 3
SELECT * FROM Customer
WHERE PostalCode LIKE '7%';

-- 4
SELECT * FROM Customer
WHERE Email LIKE '%hotmail%';

-- 5
SELECT * FROM Customer
WHERE Country = 'USA' OR Country = 'Canada';

-- 6
SELECT * FROM Customer
WHERE (Country = 'USA' OR Country = 'Canada')
  AND Email LIKE '%gmail%';

-- 7
SELECT * FROM Customer
WHERE Company LIKE '%Apple%' COLLATE NOCASE;

-- 8
---- Academicamente aceptable
SELECT CustomerId, FirstName, LastName
FROM Customer
WHERE SupportRepId BETWEEN 3 AND 4;

---- Perfecta
SELECT SupportRepId, COUNT(*) AS TotalClientes
FROM Customer
GROUP BY SupportRepId
HAVING TotalClientes BETWEEN 3 AND 4;


-- ACTIVIDAD 3

-- 1
SELECT City, COUNT(*) AS TotalEmpleados
FROM Employee
GROUP BY City;

-- 2
SELECT Title, COUNT(*) AS TotalEmpleados
FROM Employee
GROUP BY Title;

-- Pro 1
SELECT
    FirstName,
    LastName,
    (strftime('%Y', 'now') - strftime('%Y', BirthDate)) AS Edad
FROM Employee;

-- Pro 2
SELECT
    Title,
    AVG(strftime('%Y', 'now') - strftime('%Y', BirthDate)) AS EdadMedia
FROM Employee
GROUP BY Title;

-- Pro 3
SELECT
    strftime('%Y', HireDate) AS AnioContratacion,
    COUNT(*) AS EmpleadosContratados
FROM Employee
GROUP BY AnioContratacion;