0

I am unsure as to why I am receiving a syntax error on the following SQL query.

The goal of the query (and the schema/created view listed later) is to find the names and company names of the managers that earn more money than the average salary of all the managers.

Here is the query I have written and the error that came with it:

select ManagerLastname, MFirstName, MMidInitial, company_name
  from ManagerInfo, Works
    (select avg(salary) as AverageManagerSalary, Lastname
     from Works,
     group by Lastname) Work1
where ManagerInfo.ManagerLastname = Work1.Lastname and
Works.salary > Work1.AverageManagerSalary;

ORA-00933: SQL command not properly ended : select ManagerLastname, MFirstName, MMidInitial, company_name from ManagerInfo, Works (select avg(salary) as AverageManagerSalary, Lastname from Works, group by Lastname) Work1 where ManagerInfo.ManagerLastname = Work1.Lastname and Works.salary > Work1.AverageManagerSalary

I am not sure where the syntax error is in this query. I thought that all the parentheses and "punctuation" were correct so I am thinking it's an issue with the nested select statement, but am unsure as to what would need to change within that statement...

Here is the overall schema for my database. This schema along with the above view I created "compiles" correctly so I know the error has to do with the query code itself.

create table Employee(
    Lastname    varchar(10),
    FirstName   varchar(10),
    MidInitial  char(1),
    gender      char(1),
    street      varchar(10),
    city        varchar(10),
    primary key(Lastname, FirstName, MidInitial));

create table company(
    company_name    varchar(20),
    city    varchar(10),
    primary key(company_name));

create table Works(
    Lastname    varchar(10),
    FirstName   varchar(10),
    MidInitial  char(1),
    company_name    varchar(20),
    salary      numeric(8,2),
    primary key(Lastname, FirstName, MidInitial, company_name),
    foreign key(Lastname, FirstName, MidInitial) references Employee,
    foreign key(company_name) references company);

create table Manages(
    Lastname    varchar(10),
    FirstName   varchar(10),
    MidInitial  char(1),
    ManagerLastname varchar(10),
    MFirstName  varchar(10),
    MMidInitial char(1),
    start_date  date,
    primary key(Lastname, FirstName, MidInitial, ManagerLastname, MFirstName, MMidInitial),
    foreign key(Lastname, FirstName, MidInitial) references Employee);

create view ManagerInfo as
  (select distinct ManagerLastname, MFirstName, MMidInitial, street, city
   from Manages, Employee
   where Manages.ManagerLastname = Employee.Lastname and
  Manages.MFirstName = Employee.FirstName and
  Manages.MMidInitial = Employee.MidInitial);

By the way, I am using SQL for Oracle 11g R2.

Thanks for any help you can provide; much appreciated.

1
  • 2
    Comma after "Works" on line 2? I don't have oracle to check if that's the issue. Commented Sep 22, 2013 at 2:29

1 Answer 1

1

Missing a comma after Works in your from clause? (The first one) Then there appears to be an extra comma after Works in your subselect.

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for catching that! I had a feeling it would amount to something meticulous like that..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.