How to Find Cross‑College Teaching Anomalies with SQL – An Interview Challenge

There is an interview question that looks simple but many fail to solve it correctly.

The scenario: teachers belong to a college and teach courses; each course also belongs to a college. We need to retrieve records where the college of the taught course differs from the teacher's college.

Example: Professor Zhang belongs to the Computer Science college but teaches Logic, which belongs to the Philosophy college; such records should be identified.

Table definitions

<code>-- Colleges table
CREATE TABLE colleges (
    college_id INT PRIMARY KEY,
    college_name VARCHAR(50) NOT NULL
);

-- Professors table
CREATE TABLE professors (
    professor_id INT PRIMARY KEY,
    professor_name VARCHAR(50) NOT NULL,
    college_id INT NOT NULL -- professor's college, references colleges
);

-- Courses table
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL,
    teaching_college_id INT NOT NULL  -- course's college, references colleges
);

-- Teaching relationship table (professor‑course association)
CREATE TABLE teaching (
    professor_id INT NOT NULL,  -- references professors
    course_id INT NOT NULL,     -- references courses
    PRIMARY KEY (professor_id, course_id)
);
</code>

Sample data

<code>INSERT INTO colleges (college_id, college_name) VALUES
(1, '计算机学院'),
(2, '数学学院'),
(3, '物理学院');

INSERT INTO professors (professor_id, professor_name, college_id) VALUES
(101, '张教授', 1),  -- Computer Science
(102, '李教授', 2),  -- Mathematics
(103, '王教授', 3);  -- Physics

INSERT INTO courses (course_id, course_name, teaching_college_id) VALUES
(201, '数据结构', 1),   -- Computer Science
(202, '离散数学', 2),   -- Mathematics
(203, '量子力学', 3),   -- Physics
(204, '机器学习', 1),   -- Computer Science
(205, '高等代数', 2);   -- Mathematics

INSERT INTO teaching (professor_id, course_id) VALUES
-- Normal cases (professor teaches within own college)
(101, 201),  -- Zhang teaches Data Structures (CS)
(102, 202),  -- Li teaches Discrete Math (Math)
-- Anomalous cases (cross‑college teaching)
(101, 205),  -- Zhang (CS) teaches Higher Algebra (Math) → anomaly
(103, 204);  -- Wang (Physics) teaches Machine Learning (CS) → anomaly
</code>

The query should return the rows shown in the image below, which represent the cross‑college teaching anomalies.

If you would like to see the solution, feel free to comment or send a private message with the keyword “sql”.