Company: Bajaj Finserv
Difficulty: medium
Problem Description You are given a table named doctors which contains details about doctors, their medical specialties, and the hospitals where they practice. Write an SQL query to retrieve the name, specialty, and hospital of doctors who work in a hospital that offers multiple distinct specialties . In other words, include a doctor in the result if there is at least one other doctor in the same hospital who has a different specialty. Schema doctors Table Column Name Type Description id INT The unique identifier for the doctor. name VARCHAR The name of the doctor. specialty VARCHAR The medical specialty of the doctor. hospital VARCHAR The name of the hospital where the doctor works. Required Output The output should be a table containing the following columns in this exact order: name specialty hospital The results must be ordered by specialty in descending alphabetical order. If there is a tie (multiple doctors with the same specialty), order them by name in ascending alphabetical or