Advanced Queries

-- Count rows
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT age) FROM users;

-- Calculate average
SELECT AVG(age) FROM users;

-- Find min/max
SELECT MIN(age), MAX(age) FROM users;

-- Sum values
SELECT SUM(age) FROM users;

-- Count users by age
SELECT age, COUNT(*) FROM users GROUP BY age;

-- Average age by name pattern
SELECT 
    CASE 
        WHEN name LIKE 'J%' THEN 'Starts with J'
        ELSE 'Other'
    END as name_group,
    AVG(age) as average_age
FROM users 
GROUP BY name_group;

-- Find ages with more than one user
SELECT age, COUNT(*) 
FROM users 
GROUP BY age 
HAVING COUNT(*) > 1;

-- Find employees earning more than average
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Find departments with above-average salaries
SELECT department_name, avg_salary
FROM (
    SELECT 
        d.name as department_name,
        AVG(e.salary) as avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
) as dept_salaries
WHERE avg_salary > 70000;

-- Page 1 (records 1-10)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;

-- Page 2 (records 11-20)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;

-- Basic text search
SELECT * FROM users 
WHERE name LIKE '%john%' OR email LIKE '%john%';

-- Recent records
SELECT * FROM users 
WHERE created_at >= DATE('now', '-7 days');

-- Age calculation
SELECT name, age, 
    DATE('now') - DATE(birthdate) as days_alive
FROM users;