UptoSkills Logo
Login
Find Interns with Above Average SalaryMedium

Find Interns with Above Average Salary

MediumSQLPractice library · +20 XPsubqueriessqlaggregation

UptoSkills Arena needs to identify high-potential interns for potential full-time offers. From the internship records, find all interns whose monthly salary is greater than the average monthly salary of all interns.

  • interns (intern_id integer PRIMARY KEY, name varchar(100) NOT NULL, department_id integer, start_date date, end_date date, monthly_salary numeric(10,2))
  • departments (department_id integer PRIMARY KEY, dept_name varchar(100) NOT NULL)

Return the intern_id, name, and monthly_salary of these interns, ordered by intern_id. Handle ties by ordering by intern_id. NULL salaries should be ignored in average calculation and should not appear in the output.

Tables

CREATE TABLE interns (
    intern_id integer PRIMARY KEY,
    name varchar(100) NOT NULL,
    department_id integer,
    start_date date,
    end_date date,
    monthly_salary numeric(10,2)
);

CREATE TABLE departments (
    department_id integer PRIMARY KEY,
    dept_name varchar(100) NOT NULL
);

Example data

INSERT INTO departments (department_id, dept_name) VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Sales');

INSERT INTO interns (intern_id, name, department_id, start_date, end_date, monthly_salary)
VALUES
(101, 'Alice', 1, '2023-06-01', '2023-08-31', 3000.00),
(102, 'Bob', 1, '2023-06-01', '2023-08-31', 3500.00),
(103, 'Charlie', 2, '2023-07-01', '2023-09-30', 2800.00),
(104, 'David', 3, '2023-07-01', '2023-09-30', 4000.00),
(105, 'Eve', 2, '2023-06-15', '2023-08-15', 3200.00),
(106, 'Frank', 1, '2023-06-01', '2023-08-31', 3500.00); -- Tie with Bob

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems