Help me understand the logic of complex SQL queries in MySQL and relational algebra...

Question

Hey,

I'm working on a simple project to create tables and insert data into them, and finally execute some queries. Please note: I'm using MySQL command client.

My schema is easy to implement; it's no problem.

   Emp(eid: integer,  ename: string,  age: integer,  salary: real)
   Works(eid: integer,  did: integer,  pct_time: integer)
   Dept(did: integer,  budget: real,  managerid: integer)

My first question:

1. Find the department with the highest average age of employees.

When I first read the question, it seems easy. However while implementing I can't get the results. I have many queries like this, can someone explain the steps to make these queries easier?

I use the following query to get the avg ages of the all employees. The next step is to get the MAX age, but how?

> SELECT AVG(E.age) FROM DEPT D,EMP E,WORKS W  
> WHERE D.did=W.did and
> W.eid=E.eid  GROUP BY D.did

If you know any good books for just understanding these types of queries, that will be fine for me.

Second question:

Sometimes some instructors or database managers want the relational algebra of the queries?

Knowing the relational algebra of the queries, why important?

And can we all implement all these complex queries by using relational algebra?

Answer 1

Do yourself a favour and get a MySQL client with a GUI. It'll save you tons of time, especially when you're editing more complex queries.

I can recommend HeidiSQL, but there are plenty of other ones available for free.

Your first question:

select
  dept.did as `department`,
  avg(age) as `avgAge`
from
  dept,works,emp
where
  emp.eid=works.eid and 
  dept.did=works.did
group by
  dept.did
order by
  2 desc
limit 1

Maybe somebody else wants to do the rest of your homework for you.

Answer 2

You were close. Try this:

SELECT
    MAX(AVG(age)) AS Department_id_with_the_highest_average_age
FROM
    dept, emp, works
WHERE
    dept.did=works.did
    AND
    works.eid=emp.eid
GROUP BY dept.did

About Relational Algebra, read this

Enjoy.

Answer 3

Answering to your three distinct "second questions" :

"Sometimes some instructors or database managers want the relational algebra of the queries?"

I wouldn't know about instructors, but I'm fairly certain database managers never will (alas). They just want "the job done". Oh wait, and they usually don't want to see any "exotic" queries emerge on their precious databases either. If they do, they'll come and tell you something that basically means that you should NOT have used the full expressive power of the algebra in that query of yours they find so suspicious. Sometimes with good reason, sometimes not.

Knowing the relational algebra of the queries, why important?

Because if you don't understand the algebra, you basically just don't have a clue about what your SQLs mean/do. But that needn't worry you - the vast majority of so-called and more often than not self-proclaimed "database professionals" don't have a clue either and they can still make a living out of their ignorance. Ask a dozen people in your environment whether anyone can tell you something sensible about generalized transitive closure and you'll see what I mean.

And can we all implement all these complex queries by using relational algebra?

Yes. Relational algebra/Relational calculus (distinct but equivalent) are the foundation. And they are commonly regarded as "expressively complete" (meaning : they are sufficient to express just any query one could possibly imagine).