-- Fill in the TODOs, then run: npm run db:practice

-- Q1: List every application with company name (INNER JOIN)
-- Expected columns: company, role, status
SELECT
  c.name AS company,
  a.role,
  a.status
FROM applications a
INNER JOIN companies c ON c.id = a.company_id
ORDER BY c.name, a.role;

-- Q2: Count applications by status (GROUP BY)
SELECT
  status,
  COUNT(*) AS total
FROM applications
GROUP BY status
ORDER BY total DESC;

-- Q3: Applications for one company (replace name as needed)
-- Student TODO: change 'Tencent' and understand why we prefer bound params in app code.
SELECT
  a.role,
  a.status,
  a.notes
FROM applications a
INNER JOIN companies c ON c.id = a.company_id
WHERE c.name = 'Tencent';

-- Q4 (stretch): companies with 2+ applications
SELECT
  c.name AS company,
  COUNT(a.id) AS apps
FROM companies c
LEFT JOIN applications a ON a.company_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(a.id) >= 2;
