SQL Query From two Table satisfying a condition Table 1 name: Student
Name      Department
Alex         CSE
Bob          EEE
Briyan       ME
John         CSE
James        ETE
Mike         CE

Table 2 name: Program
Department    Semester
   CSE         Summer
   CSE         Winter
   EEE         Summer
   ETE         Summer
   ME          Winter
   CE          Summer
   CE          Winter


What is the SQL query to print the names and the semester where each student's department have only 1 type of semester? 

SQL Query Result should be like this:
Name     Semester
Bob       Summer
Briyan    Winter
James     Summer


Here, CSE and CE have both summer and winter semester. So Alex, John and Mike are not included.

I have tried this far:

SELECT Student.Name,
     Program.Semester
FROM Student
JOIN Program
    ON Program.Department = Student.Department
WHERE ....
You want to run a query like this (see db-fiddle here):

SELECT 
  st.student_name, 
  -- st.student_dept,  -- &lt;&lt;= uncomment for an insight into
  -- sm.semester_dept, -- &lt;&lt;= how the query works!
  sm.semester_name
FROM student st
JOIN semester sm
ON
  st.student_dept = sm.semester_dept
WHERE st.student_dept IN
(
  SELECT
    sm.semester_dept AS sem_dept
  FROM semester sm
  GROUP BY sm.semester_dept
  HAVING COUNT(sm.semester_dept) = 1 
)


Result:

Student name  Semester name
___________________________
         Bob         Summer
      Briyan         Winter
       James         Summer


This query will work (I believe) for any/all of the major RDBMS servers - works on MySQL and (my reference implementation, PostgreSQL here). Oh, yes, just a point to note - the student_name field should be a PRIMARY KEY (or at least UNIQUE with NOT NULL) in order for this to be guaranteed to work properly!

This solution is much more elegant than my original query solution (here) - thanks to @ypercube for the hint!

============== DDL and DML ==================

CREATE TABLE student
(
  student_name VARCHAR (20) NOT NULL,
  student_dept VARCHAR (4) NOT NULL
);

INSERT INTO student VALUES
('Alex',   'CSE'),
('Bob' ,   'EEE'),
('Briyan', 'ME'),
('John',   'CSE'),
('James',  'ETE'),
('Mike',   'CE');

CREATE TABLE semester
(
  department VARCHAR (4) NOT NULL,
  semester   VARCHAR (6) NOT NULL
);

INSERT INTO semester VALUES
('CSE', 'Summer'),
('CSE', 'Winter'),
('EEE', 'Summer'),
('ETE', 'Summer'),
('ME',  'Winter'),
('CE',  'Summer'),
('CE',  'Winter');