I have a few things that I would like somebody's insights on about SQL queries.

1)  If I understand it correctly, there are some queries that can be done either by 'distinct' or 'group by'. For instance,

select distinct department_id from courses;

would return same result as

select department_id from courses group by department_id;

The above use of 'group by' probably does not fully utilize the power of 'group by' (i.e. use of it with aggregates) or simply it is a wrong use, but the results the database returns seem to be the same.

My question is, is 'distinct' generally less expensive compared to 'group by', or is it totally case dependent and these two are just not comparable because of their functional differences?

2)  which one of the three shown below is the least expensive? My guess would be 'intersect' or 'in()'. Cross join is most likely the most expensive since it looks at all of the Cartesian cross products, 'intersect' and 'in()' might be a tie because they both only search through a specific group/list of records for matches.

    -- 23. List the names that appear in both the students table and the faculty
    --     table.

            select name from students
            intersect
            select name from faculty;

            select name from students where name in (select name from faculty);

            select s.name from students s, faculty f where s.name = f.name;

        
3)  Could I have done an inner join as shown immediately below on the queries like the one at the bottom(#26), or would I have gotten an undesired result with different sets of entries? I assume inner join would have been fine because inner joins (my understanding is that inner joins are the same thing as equi-joins, and the way it works can be visualized as the intersection representation of the Venn Diagram...) only returns complete match(s) unlike full-outer joins, left joins, or right joins. We are only concerned about complete match cases for this case. Did I miss some important factors to take into consideration?

select count(*) from departments d inner join courses c    on d.id = c.department_id where d.name = 'Computer Science';
 

        -- 26. Find the number of courses offered by the Computer Science Department.

        select count(*) from departments d, courses c
            where d.name = 'Computer Science' and d.id = c.department_id;

Any insights will be greatly appreciated. Thank you,
 

I see! Thank you for such a thorough reply. It seems much more clearer now. Also, thank you for pointing out my mistake on identifying the equi-join!   Sincerely,