Select the students that are enrolled in both the courses This is the table definition. I want to select the students that are enrolled in courses (1 and 3). 

From this dataset the expected output is 'B'.
You can approach this problem using conditional aggregation based filtering. Firstly, get all the students who are either enrolled IN(..) course no (1, 3).

Then, you can use HAVING clause to filter out the students having atleast two unique courses subscribed to (which would be 1 and 3 in this case):

SELECT student_name 
FROM your_table_name 
WHERE course_number IN (1,3) 
GROUP BY student_name 
HAVING COUNT(DISTINCT course_number) = 2


For good performance, you can define the following Covering Index: (course_number, student_name)