CODEWITHSUNDEEP
sqloracle-databaseselect
user1462294
user1462294

Reputation: 167

Error on SQL query (nested select statement)

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.

Upvotes: 0

Views: 274

Answers (1)

NYCdotNet
NYCdotNet

Reputation: 4647

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.

Upvotes: 1

Related Questions