Solutions – SQL Foundations Exercises

These reference solutions correspond to the practice tasks from Lesson 03 – SQL Foundations. They demonstrate typical queries against the employees table shown in the lesson.

---

Exercise 1: Get all Developers over 60,000

SELECT *
FROM employees
WHERE role = 'Developer' AND salary > 60000
ORDER BY salary DESC;

This selects only developers earning above 60k and sorts them from highest to lowest salary.

def fetch_high_salary_developers(conn) -> list[tuple]:
    """Retrieve developers with salaries above 60,000.

    Executes a SELECT statement using the provided database
    connection to fetch rows for developers earning more than 60k.
    Results are ordered from highest to lowest salary.

    Args:
        conn: A PEP 249 database connection.

    Returns:
        list[tuple]: Developer rows sorted by salary.

    Examples:
        >>> fetch_high_salary_developers(conn)[0][0]
        'Alice'
    """
    query = (
        "SELECT * FROM employees "
        "WHERE role = 'Developer' AND salary > 60000 "
        "ORDER BY salary DESC"
    )
    with conn.cursor() as cur:
        cur.execute(query)
        return cur.fetchall()

Exercise 2: Insert Grace as a Product Manager

INSERT INTO employees (name, role, salary)
VALUES ('Grace', 'Product Manager', 82000);

A standard INSERT specifying the name, role, and salary for the new employee.

Exercise 3: Update Dave's Name

UPDATE employees
SET name = 'David'
WHERE name = 'Dave';

The WHERE clause ensures only the row with the old name is changed.

Exercise 4: Delete Low‑Salary Employees

DELETE FROM employees
WHERE salary < 45000;

Any employee making less than 45k is removed from the table.

Exercise 5: Average Salary by Role

SELECT role, AVG(salary) AS average_salary
FROM employees
GROUP BY role;

GROUP BY aggregates salaries so you can see the average per job title.