Missing right parenthesis- ORA-00907

Question

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'

As rightly pointed out Bob, there was a '_' in that. But now it says USING cannot have qualifier.

Student :

create table student
(ID         varchar(5), 
 name           varchar(20) not null, 
 dept_name      varchar(20), 
 tot_cred       numeric(3,0) check (tot_cred >= 0),
 primary key (ID),
 foreign key (dept_name) references department
 on delete set null
 )

Takes:

create table takes
(ID         varchar(5), 
 course_id      varchar(8),
 sec_id         varchar(8), 
 semester       varchar(6),
 year           numeric(4,0),
 grade              varchar(2),
 primary key (ID, course_id, sec_id, semester, year),
 foreign key (course_id,sec_id, semester, year) references section
    on delete cascade,
 foreign key (ID) references student
    on delete cascade
)

Instructor:

create table instructor
(ID         varchar(5), 
 name           varchar(20) not null, 
 dept_name      varchar(20), 
 salary         numeric(8,2) check (salary > 29000),
 primary key (ID),
 foreign key (dept_name) references department
    on delete set null
)

Teaches :

create table teaches
(ID         varchar(5), 
 course_id      varchar(8),
 sec_id         varchar(8), 
 semester       varchar(6),
 year           numeric(4,0),
 primary key (ID, course_id, sec_id, semester, year),
 foreign key (course_id,sec_id, semester, year) references section
    on delete cascade,
 foreign key (ID) references instructor
    on delete cascade
);

I have pasted the structure as requested. I hope that helps! I have also tried editing the quotes but still no use.

Answer 1

The problem is not that there are too few parentheses but that there are too many.

I'm guessing a bit here because I don't know exactly what your tables look like, but it appears that your SELECT statement should be:

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

In addition, you need to be careful about the editor you're using. In your question the single quotes surrounding 'Einstein' are not real single quotes - they appear to be Unicode apostrophes as would be added by a word processor such as Microsoft Word. String constants need to be surrounded by single quote characters (') - using anything else will cause errors.

Best of luck.

Answer 2

As Bob Jarvis pointed out, the third using clause has spaces where there should be underscores; changing those then gets (as you edited the question to say):

ORA-25154: column part of USING clause cannot have qualifier

because the select list has student.ID. When you use the using clause the column(s) in that can't be referenced directly in the joined tables, so you need to do:

select distinct 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 that will now get

ORA-00918: column ambiguously defined

... because both student and instructor have ID columns, which is presumably why the select list was prefixed in the first place. You could use an inline view to lose the instructor ID from the select list scope:

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

But really you'd be better off avoiding using here and instead use the on syntax and simplified joins, e.g.:

select distinct stu.id
from instructor ins
join teaches tea on tea.id = ins.id
join takes tak on tak.course_id = tea.course_id
and tak.sec_id = tea.sec_id
and tak.semester = tea.semester
and tak.year = tea.year
join student stu on stu.id = tak.id
where ins.name = 'Einstein';

Also see an earlier related answer for more.