Find all professors who teach courses in the Science department, along with the names of those courses.

-- Find all professors who teach courses in the Science department, along with the names of those courses.

  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);


CREATE TABLE Professors (
  professor_id INT PRIMARY KEY,
  professor_name VARCHAR(50)
);


CREATE TABLE Courses (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(50),
  department_id INT,
  professor_id INT
);


INSERT INTO Departments (department_id, department_name) VALUES
(10, 'Science'),
(20, 'Arts');


INSERT INTO Professors (professor_id, professor_name) VALUES
(1, 'Dr. Smith'),
(2, 'Dr. Johnson'),
(3, 'Dr. Lee');


INSERT INTO Courses (course_id, course_name, department_id, professor_id) VALUES
(101, 'Biology 101', 10, 1),
(102, 'Chemistry Basics', 10, 1),
(103, 'Literature Intro', 20, 2),
(104, 'Physics Principles', 10, 3);



-- Check Departments
SELECT * FROM Departments;

-- Check Professors
SELECT * FROM Professors;

-- Check Courses
SELECT * FROM Courses;


SELECT 
  p.professor_name,
  c.course_name
FROM Professors p
JOIN Courses c 
  ON p.professor_id = c.professor_id
JOIN Departments d 
  ON c.department_id = d.department_id
WHERE d.department_name = 'Science'
ORDER BY p.professor_name, c.course_name;