Skip to content

Query Examples

Jump to bottom
Kshitij Joshi edited this page May 26, 2026 · 1 revision

Query Examples 💡

Real-world examples of questions you can ask and the SQL they generate.

Basic Queries

Employee Information

Question: "Show me all employees"

Generated SQL:

SELECT * FROM employees
LIMIT 100;

Question: "Who are the highest paid employees?"

Generated SQL:

SELECT first_name, last_name, salary, job_title
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
ORDER BY salary DESC
LIMIT 10;

Question: "List employees in the Finance department"

Generated SQL:

SELECT e.first_name, e.last_name, e.email, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Finance'
ORDER BY e.last_name;

Aggregations

Salary Analysis

Question: "What's the average salary by department?"

Generated SQL:

SELECT 
    d.dept_name,
    COUNT(e.employee_id) as employee_count,
    ROUND(AVG(e.salary), 2) as avg_salary,
    MIN(e.salary) as min_salary,
    MAX(e.salary) as max_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC;

Question: "Which departments have average salaries above $75,000?"

Generated SQL:

SELECT 
    d.dept_name,
    ROUND(AVG(e.salary), 2) as avg_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING AVG(e.salary) > 75000
ORDER BY avg_salary DESC;

Budget Analysis

Question: "Which departments are over budget?"

Generated SQL:

SELECT 
    d.dept_name,
    TO_CHAR(d.budget, '$999,999') as budget,
    TO_CHAR(SUM(e.salary), '$999,999') as total_salaries,
    TO_CHAR(d.budget - SUM(e.salary), '$999,999') as difference,
    ROUND((SUM(e.salary) / d.budget * 100), 1) as budget_used_pct
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name, d.budget
HAVING SUM(e.salary) > d.budget
ORDER BY budget_used_pct DESC;

Time-Based Queries

Hiring Trends

Question: "Show me employees hired in 2023"

Generated SQL:

SELECT first_name, last_name, hire_date, job_title, dept_name
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
JOIN departments d ON e.dept_id = d.dept_id
WHERE EXTRACT(YEAR FROM hire_date) = 2023
ORDER BY hire_date DESC;

Question: "Who was hired in the last 6 months?"

Generated SQL:

SELECT first_name, last_name, hire_date, dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE hire_date >= CURRENT_DATE - INTERVAL '6 months'
ORDER BY hire_date DESC;

Question: "How many employees were hired each year?"

Generated SQL:

SELECT 
    EXTRACT(YEAR FROM hire_date) as year,
    COUNT(*) as hires
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY year DESC;

Performance Reviews

Question: "Show me employees with 5-star ratings"

Generated SQL:

SELECT DISTINCT
    e.first_name,
    e.last_name,
    pr.rating,
    pr.review_date
FROM employees e
JOIN performance_reviews pr ON e.employee_id = pr.employee_id
WHERE pr.rating = 5.0
ORDER BY pr.review_date DESC;

Question: "What's the average performance rating by department?"

Generated SQL:

SELECT 
    d.dept_name,
    ROUND(AVG(pr.rating), 2) as avg_rating,
    COUNT(pr.review_id) as review_count
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
JOIN performance_reviews pr ON e.employee_id = pr.employee_id
GROUP BY d.dept_name
ORDER BY avg_rating DESC;

Salary History

Question: "Who got raises in the last year?"

Generated SQL:

SELECT 
    e.first_name,
    e.last_name,
    TO_CHAR(sh.old_salary, '$999,999') as old_salary,
    TO_CHAR(sh.new_salary, '$999,999') as new_salary,
    TO_CHAR(sh.new_salary - sh.old_salary, '$999,999') as increase,
    sh.change_date,
    sh.change_reason
FROM salary_history sh
JOIN employees e ON sh.employee_id = e.employee_id
WHERE sh.change_date >= CURRENT_DATE - INTERVAL '1 year'
ORDER BY sh.change_date DESC;

Complex Queries

Multi-Table Analysis

Question: "Show me IT employees who earn more than the average salary and have good performance ratings"

Generated SQL:

SELECT 
    e.first_name,
    e.last_name,
    TO_CHAR(e.salary, '$999,999') as salary,
    ROUND(AVG(pr.rating), 1) as avg_rating
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN performance_reviews pr ON e.employee_id = pr.employee_id
WHERE d.dept_name = 'Information Technology'
  AND e.salary > (SELECT AVG(salary) FROM employees)
GROUP BY e.employee_id, e.first_name, e.last_name, e.salary
HAVING AVG(pr.rating) >= 4.0
ORDER BY e.salary DESC;

Question: "Compare salaries of employees in each department to their department's average"

Generated SQL:

SELECT 
    e.first_name,
    e.last_name,
    d.dept_name,
    TO_CHAR(e.salary, '$999,999') as salary,
    TO_CHAR(AVG(e2.salary) OVER (PARTITION BY d.dept_id), '$999,999') as dept_avg,
    TO_CHAR(e.salary - AVG(e2.salary) OVER (PARTITION BY d.dept_id), '$999,999') as difference
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN employees e2 ON e2.dept_id = d.dept_id
GROUP BY e.employee_id, e.first_name, e.last_name, e.salary, d.dept_id, d.dept_name
ORDER BY d.dept_name, e.salary DESC;

Project Queries

Question: "What projects are currently in progress?"

Generated SQL:

SELECT 
    p.project_name,
    p.description,
    d.dept_name,
    TO_CHAR(p.budget, '$999,999') as budget,
    p.start_date,
    p.end_date
FROM projects p
JOIN departments d ON p.dept_id = d.dept_id
WHERE p.status = 'IN_PROGRESS'
ORDER BY p.start_date DESC;

Question: "Show me total project budgets by department"

Generated SQL:

SELECT 
    d.dept_name,
    COUNT(p.project_id) as project_count,
    TO_CHAR(SUM(p.budget), '$999,999,999') as total_budget
FROM departments d
LEFT JOIN projects p ON d.dept_id = p.dept_id
GROUP BY d.dept_name
ORDER BY SUM(p.budget) DESC NULLS LAST;

Tips for Better Questions

✅ Do

  • Be specific: "Show employees in Finance" vs "Show employees"
  • Use proper names: "Information Technology" vs "IT" (if that's the actual department name)
  • Include context: "hired in last 6 months" vs "recent hires"
  • Specify order: "highest paid" includes sorting

❌ Don't

  • Use vague terms: "some employees" or "a few departments"
  • Ask multiple questions at once: "Show employees and also departments and their budgets"
  • Use uncommon abbreviations the database might not understand
  • Assume column names: check schema first with "What columns are in the employees table?"

Advanced Examples

Window Functions

Question: "Rank employees by salary within their department"

Generated SQL:

SELECT 
    first_name,
    last_name,
    dept_name,
    salary,
    RANK() OVER (PARTITION BY d.dept_id ORDER BY e.salary DESC) as dept_rank
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, dept_rank;

Common Table Expressions (CTEs)

Question: "Show departments where the highest paid employee earns more than twice the lowest paid"

Generated SQL:

WITH dept_salaries AS (
    SELECT 
        d.dept_name,
        MAX(e.salary) as max_salary,
        MIN(e.salary) as min_salary
    FROM departments d
    JOIN employees e ON d.dept_id = e.dept_id
    GROUP BY d.dept_name
)
SELECT 
    dept_name,
    TO_CHAR(max_salary, '$999,999') as highest,
    TO_CHAR(min_salary, '$999,999') as lowest,
    ROUND(max_salary / min_salary, 2) as ratio
FROM dept_salaries
WHERE max_salary > min_salary * 2
ORDER BY ratio DESC;

Need More Examples?

← Back to Home | User Guide →

Clone this wiki locally