Sub queries - I do not get them

Question

While I understand the basis definition of a sub query, I am having a hard time applying it. Below are listed a few of my attempts of getting A but no luck. What am I doing wrong?

A. Find the ID and name of each student who has taken at least one Comp. Sci. course; make sure there are no duplicate names in the result

SELECT DISTINCT ID,name FROM student WHERE dept_name >= 'comp.sci.'

wrong

SELECT DISTINCT ID, name, dept_name FROM student WHERE dept_name > SOME IN (SELECT ID, name, dept_name FROM student WHERE dept_name = 'comp.sci.')

wrong

SELECT DISTINCT S.ID S.name FROM student JOIN takes T on T.ID= S.ID WHERE dept_name IN (SELECT ID, name FROM takes WHERE dept_name = 'comp.sci.')

wrong

Answer 1

Build it up piece by piece.

create table students (id numeric, name text, dept_name text);
 insert into students values 
  (1, 'alice', 'comp.sci.java'), 
  (2, 'bob', 'comp.sci.php'), 
  (3, 'eve', 'metaphysics');

You can run the subquery on its own:

SELECT dept_name FROM student WHERE dept_name like 'comp.sci.%';

Then try use this in a trivial subselect:

SELECT dept_name FROM 
(SELECT dept_name FROM student WHERE dept_name like 'comp.sci.%');

And extend to your target:

SELECT DISTINCT ID, name, dept_name 
FROM student 
WHERE dept_name IN 
  (SELECT dept_name FROM student WHERE dept_name like 'comp.sci.%');