Writing query in SQL in a different way

Question

I am trying to write a query for this question.

Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result.

One suitable answer to this would be

select distinct student.ID
from (student join takes using(ID))
join (instructor join teaches using(ID))
using (course_id, sec_id, semester, year)
where instructor.name = 'Einstein'

but I don't want to use the join.....using Syntax. I want to write the same query without using join....using. I was able to write some parts of the query, but don't understand how to write the whole query without it returning any errors. Below is what I am trying to do, As an alternative to the join....using syntax the query I am trying to write is by enumerating relations in the from clause, and adding the corresponding join predicates on ID, course id, section id, semester, and year to the where clause. But when I do that, its giving me back errors, saying that "no such column exists"

select distinct student.id
from student, takes
where student.id = takes.id 
and student.sec_id = takes.sec_id 
and student.semester = takes.semester
and student.year = takes.year

How can I fix the code?

Table setup:

Student

ID      NAME     DEPT_NAME   TOT_CRED
-------------------------------------
00128   Zhang    Comp. Sci.  102
12345   Shankar  Comp. Sci.  32
19991   Brandt   History     80
44553   Peltier  Physics     56

Instructor

ID      NAME        DEPT_NAME   SALARY
---------------------------------------
10101   Srinivasan  Comp. Sci.  65000
12121   Wu          Finance     90000
15151   Mozart      Music       40000
22222   Einstein    Physics     95000

Teaches

ID      COURSE_ID  SEC_ID  SEMESTER  YEAR 
------------------------------------------
10101   CS-101     1       Fall      2009
10101   CS-315     1       Spring    2010
10101   CS-347     1       Fall      2009 
22222   PHY-101    1       Fall      2017

Takes

ID      COURSE_ID  SEC_ID  SEMESTER YEAR  GRADE
------------------------------------------------
00128   CS-101     1       Fall     2009    A
00128   CS-347     1       Fall     2009    A-
12345   CS-101     1       Fall     2009    C 
44553   PHY-101    1       Fall     2017    B-

Why do you want to rewrite your query in a syntax that is considered obsolete and harder to read? — Nick Bailey, Apr 17 '22 at 23:08
[`USING`](https://www.sqlite.org/lang_select.html#determination_of_input_data_from_clause_processing_) is just a shorthand of calling `ON` to match to same column name. (i.e., `student join takes ON student.ID = takes.ID`) which is the preferred explicit join as opposed to your proposed, [implicit join](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins). See also [INNER JOIN ON vs WHERE clause](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause). — Parfait, Apr 17 '22 at 23:50
this was a challenge question given to us students in our class. that's why! I just couldn't figure out how to write it in the format asked for. — Help_needed, Apr 18 '22 at 13:50

Writing query in SQL in a different way

0 Answers0