CREATE TABLE employees(    id         INT AUTO_INCREMENT PRIMARY KEY,    name       VARCHAR(50) NOT NULL,    gender     VARCHAR(10) NOT NULL,    salary     INT         NOT NULL,    department VARCHAR(50) NOT NULL);
INSERT INTO employees (name, gender, salary, department)VALUES ('Ramesh Gupta', 'Male', 55000, 'Sales'),       ('Priya Sharma', 'Female', 65000, 'Marketing'),       ('Sanjay Singh', 'Male', 75000, 'Sales'),       ('Anjali Verma', 'Female', 45000, 'Finance'),       ('Rajesh Sharma', 'Male', 80000, 'Marketing'),       ('Smita Patel', 'Female', 60000, 'HR'),       ('Vikram Yadav', 'Male', 90000, 'Sales'),       ('Neha Sharma', 'Female', 55000, 'Marketing'),       ('Rahul Singh', 'Male', 70000, 'Finance'),       ('Sonali Gupta', 'Female', 50000, 'Sales');

SELECT department, gender, AVG(salary) AS avg_salaryFROM employeesGROUP BY department, gender;

SELECT name, salaryFROM employeesWHERE (department, salary) IN (SELECT department, MAX(salary)                               FROM employees                               GROUP BY department);

SELECT name, salary, departmentFROM employeesWHERE salary > (SELECT AVG(salary)                FROM employees AS e2                WHERE e2.department = employees.department);

SELECT e.department, e.name, e.salaryFROM employees eWHERE (SELECT COUNT(*)       FROM employees       WHERE department = e.department         AND salary > e.salary) < 3;

SELECT e.nameFROM employees e         JOIN (SELECT department, AVG(salary) AS avg_salary               FROM employees               GROUP BY department) AS dept_avg              ON e.department = dept_avg.departmentWHERE e.salary > dept_avg.avg_salary;

WITH max_salary AS (SELECT department, MAX(salary) AS highest_salary                    FROM employees                    GROUP BY department)SELECT m.department, e.name, e.salaryFROM employees e         JOIN max_salary m              ON e.department = m.department AND e.salary = m.highest_salary;