UptoSkills Logo
Login
Internship Offers by Company and DepartmentMedium

Internship Offers by Company and Department

MediumSQLPractice library · +20 XPmulti-table joinssqlaggregation

UptoSkills Arena is analyzing internship offers from various companies. We want to identify companies that are actively recruiting across different departments, indicating a broad reach and diverse hiring needs.

We have two tables:

  • companies: company_id (integer, PK), company_name (varchar(100))
  • internships: internship_id (integer, PK), company_id (integer, FK to companies), department (varchar(50))

Return the company_name for all companies that have offered internships in at least two distinct departments. The results should be ordered alphabetically by company_name.

Handle NULLs: If a company has no internship offers, it should not appear in the result. If a department is NULL, it should be treated as a distinct department.

Tables

CREATE TABLE companies (
    company_id integer PRIMARY KEY,
    company_name varchar(100) NOT NULL
);

CREATE TABLE internships (
    internship_id integer PRIMARY KEY,
    company_id integer NOT NULL,
    department varchar(50),
    FOREIGN KEY (company_id) REFERENCES companies(company_id)
);

Example data

INSERT INTO companies (company_id, company_name) VALUES
(1, 'TechNova'),
(2, 'Innovate Solutions'),
(3, 'Global Enterprises'),
(4, 'FutureTech'),
(5, 'Data Dynamics');

INSERT INTO internships (internship_id, company_id, department) VALUES
(101, 1, 'Engineering'),
(102, 1, 'Marketing'),
(103, 2, 'Engineering'),
(104, 2, 'Data Science'),
(105, 2, 'HR'),
(106, 3, 'Sales'),
(107, 4, 'Engineering'),
(108, 4, 'Engineering'), -- Duplicate department for the same company
(109, 5, 'Marketing'); -- Company with only one department

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

Hints

Similar problems

More in: SQL and Databases problems