Oracle SQL - Relations

Hey, I'm making some progress with this language but I've just a brick wall.

Data base that I work with:
It's 3 tables, one with workers (bosses included), the second one with ranks (which determine payment, max and minimum) and the third one with specific departments in this company.

STILL, since I only have problems with specific things, we'll only focus on these 2:

Departments:
Dep_name
Id_dep

Workers:
Name
Id_dep
Salary
Id_wor
Id_bos

So this is it. The Department name can be applied in the Workers table by using JOIN etc etc... but here's the tasks that got me confused:
1) I'm supposed to list all departments, the number of workers in it and the average salary that the people in it get. Now this sounds simple, but one of the departments has no people employed.
When I type:
select Dep_name from Departments;

I see all 5 of them.
However as we were told, to get their numbers, it's a bit more complicated:
select Dep_name, count(*) from Workers natural join Departments group by Dep_name;
And there, I see only 4. As I've researched, it has to be because one of them has null as a value. STILL, I tried using NVL as a function, but it didn't make one bit of a difference. So now I'm clueless how to include that 1 department in it.

2) Now this is even stranger. We had an exercise before where we found the boss for each person:
select p.id_wor, p.name, s.id_wor, s.name as Boss from (Workers p) left join (Workers s) on p.id_bos=s.id_wor
Now however we have to find all workers who are bosses of some people, and name a number of people to which that person is the boss. So I guess it's taking the id_wor of a person and counting for how many people it's id_bos. I have no clue how to get to that.

Please help and thanks a lot!

Comments

  • Try this -- no guarantee it will work. I worked with Sybase SQL, which may or may not be the same on your database server.

    select Department.Dep_name, Workers.count(*) as NumWorkers
    from Workers, Department
    where workers.dep_id *= Departments.dep_id
    group by dep_name
  • Doesn't quite work. The first error comes from count(*).

    This says that there's an expression missing. For testing sake I've replaced * with 1.

    The next error then came with the "*=", again "missing expression". I don't know what "*=" does, if it's unequal then it should be "!=" I think... BUT just to test, I changed it to =

    And with that, the error pops out much earlier, at workers.count(1). The error is called "%s: invalid identifier"... and so I get clueless what to do now...
  • > I don't know what "*=" does

    It means to return a row even if there are no rows in one of the tables. So it should return a row from Department even if there are no rows in workers that have the dep_id (count would return 0)

    As I said, that may be Sybase specific, I don't know. It's been a few years since I did any SQL.
  • outer join in Oracle is a construct like this:
    table1.id(+) = table2.id
    
Sign In or Register to comment.