UptoSkills Logo
Login
Internship Offers by Department and SalaryMedium

Internship Offers by Department and Salary

MediumSQLPractice library · +20 XPmulti-table joinssqlaggregation

UptoSkills Arena is tracking internship offers from various companies to students across different departments. The HR department needs a report summarizing the total number of offers and the average salary offered by each department.

Tables:

  • Departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(50))
  • Companies (comp_id INT PRIMARY KEY, comp_name VARCHAR(100))
  • Internships (intern_id INT PRIMARY KEY, comp_id INT, dept_id INT, offered_salary NUMERIC(10, 2), offer_date DATE)

Return the department name, the total count of internship offers, and the average offered salary for each department. The average salary should be rounded to two decimal places.

Handle ties by including all tied departments. NULL salaries should be ignored in the average calculation. If a department has no offers, it should not appear in the result.

Tables

CREATE TABLE Departments (
    dept_id INTEGER PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE Companies (
    comp_id INTEGER PRIMARY KEY,
    comp_name VARCHAR(100) NOT NULL
);

CREATE TABLE Internships (
    intern_id INTEGER PRIMARY KEY,
    comp_id INTEGER,
    dept_id INTEGER,
    offered_salary NUMERIC(10, 2),
    offer_date DATE,
    FOREIGN KEY (comp_id) REFERENCES Companies(comp_id),
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

Example data

INSERT INTO Departments (dept_id, dept_name) VALUES
(1, 'Computer Science'),
(2, 'Electrical Engineering'),
(3, 'Mechanical Engineering'),
(4, 'Civil Engineering');

INSERT INTO Companies (comp_id, comp_name) VALUES
(101, 'Tech Innovations Inc.'),
(102, 'Power Solutions Ltd.'),
(103, 'AutoMakers Corp.');

INSERT INTO Internships (intern_id, comp_id, dept_id, offered_salary, offer_date)
VALUES
(1, 101, 1, 50000.00, '2024-05-15'),
(2, 101, 1, 55000.00, '2024-05-20'),
(3, 102, 2, 60000.00, '2024-06-01'),
(4, 103, 3, 48000.00, '2024-06-10'),
(5, 101, 1, NULL, '2024-05-25'),
(6, 102, 2, 62000.00, '2024-06-05'),
(7, 103, 3, 52000.00, '2024-06-15'),
(8, 101, 4, 45000.00, '2024-07-01');

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

Hints

Similar problems

More in: SQL and Databases problems