write SQL for a given scenario

Consider these RDBMS Tables:

Table: Employee
    EmpID: INTEGER NOT NULL
    DeptID: INTEGER NOT NULL
    Name: Char(40) NOT NULL
    Age: INTEGER NOT NULL
    Salary: INTEGER NOT NULL

Table: Department
    DeptID: INTEGER NOT NULL
    Name: CHAR(20) NOT NULL
    Rank: INTEGER
Here, Rank is currently NULL for all Departments.

Write an SQL update statement which will rank all the departments by decreasing order of average age of all employees in that department, and then update the Rank column accordingly. Thus, the department with the highest average employee age should be ranked 1, the department with the second highest average employee age should be ranked 2, and so on. In case more than one departments have the same average employee average age, then they should be ranked by DeptID which is unique. The Rank of departments without any employees should not be updated.
itaakashcomAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
Looks like homework.

What have you tried?
itaakashcomAuthor Commented:
I am not able to get where to start. Though I can make out it's nth highest value type but don't know how to use that concept here.
PortletPaulEE Topic AdvisorCommented:
You will need tables with data in them.
Did the instructor give you data?

btw:
If you are looking for a place to try some SQL use http://sqlfiddle.com

& there are several rdbms types to choose from including:
   Oracle Database
   MySQL Server
   MS SQL Server

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

Geert GOracle dbaCommented:
if you are looking to rank something ...
then why not have a very close look at the word "RANK"
and look up this function in the oracle docs

start with the language reference
http://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm
then functions
then analytic
then rank function
Scott PletcherSenior DBACommented:
Has you covered the ROW_NUMBER() function in your class?  It will do exactly what you need to do for either SQL Server or Oracle (I can't say for the other DBMS's).

Since you want to rank only those Departments which appear in the Employee table, you will need to use  either a DISTINCT or an EXISTS() against that table in your query.
awking00Information Technology SpecialistCommented:
Have you covered the analytic functions (ones with the keyword OVER)?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.