UptoSkills Logo
Login
Internship Offers by Status and DepartmentMedium

Internship Offers by Status and Department

MediumSQLPractice library · +20 XPconditional aggregationsqlinternships

The UptoSkills Arena helps students find internships. Internships can have different statuses like 'Accepted', 'Rejected', or 'Pending'.

Tables:

  • Internships: internship_id (PK), student_id, department_id, offer_status (varchar(20))
  • Departments: department_id (PK), department_name (varchar(50))

Return the department_name and the counts of internships for each offer_status. The columns should be in the order: department_name, accepted_count, rejected_count, pending_count.

Handle NULL offer_status values by treating them as 'Pending'. If a department has no internships, it should not appear in the results. Ties in department names are not possible as department_name is unique.

Tables

CREATE TABLE Departments (
  department_id integer PRIMARY KEY,
  department_name varchar(50) NOT NULL
);
CREATE TABLE Internships (
  internship_id integer PRIMARY KEY,
  student_id integer NOT NULL,
  department_id integer NOT NULL REFERENCES Departments(department_id),
  offer_status varchar(20)
);

Example data

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

INSERT INTO Internships (internship_id, student_id, department_id, offer_status)
VALUES
(101, 1, 1, 'Accepted'),
(102, 2, 1, 'Pending'),
(103, 3, 1, 'Rejected'),
(104, 4, 2, 'Accepted'),
(105, 5, 2, 'Accepted'),
(106, 6, 3, 'Pending'),
(107, 7, 3, 'Pending'),
(108, 8, 3, 'Pending'),
(109, 9, 4, NULL); -- NULL status

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

Hints

Similar problems

More in: SQL and Databases problems