Oracle SQL Developer Create View Assignment -

i've got assignment following instructions:

create view named a11t1 (that's a-one-one-t-one, not a-l-l-t-l) display concatenated name, jobtitle , salary of people have cat value of n , salary @ least 30 percent higher average salary of people have cat value of n. 3 column headings should name, jobtitle , salary. rows should sorted in traditional phonebook order.

note 1: always, concatenated names must appear 1 space between first , last names.

note 2: concatenated names , job titles must displayed in proper case (e.g., mary ellen smith, assistant manager) task.

note 3: remember, person11 data messy. sure n , n when identifying people cat value of n.

what have far is:

create view a11t1  select initcap(fname||' '||lname) "name", initcap(jobtitle), salary person11 upper(cat) = 'n' group initcap(fname||' '||lname), initcap(jobtitle), salary having salary >= 1.3 * round(avg(salary),0) order lname, fname  

error @ command line:7 column:10 error report: sql error: ora-00979: not group expression 00979. 00000 - "not group expression"

is current error i'm getting

no matter how edit code won't create view , i've been stuck on hours! appreciate responses, point in right direction.

why need "group by" concatenated name, job title , salary? have more 1 row per name?

perhaps it's because need compute average salary , requires aggregation? can't in single select statement in sql (at least not simple tools - seem in stages of learning , not looking use window functions).

the "avg salary" needs come subquery. have >= 1.3 * round(...) should have instead:

... >= 1.3 * (select avg(salary) person11 cat = 'n') 

note subquery must enclosed in parentheses. in code see use upper(cat) - there concern cat may upper or lower case? in case may better write

cat in ('n', 'n') 

avoid wrapping column values inside functions whenever possible (that leads worse performance). also, see no need round average salary in requirements - , in case, what's point rounding 0 decimal places if multiply 1.3? rounding may lead incorrect output.

edit: sorry, clarify: think on way already. use subquery average salary, remove group (which doesn't hurt unneeded), , if care to, change upper(cat) suggested; think query work these changes.

good luck!