-3

What is relational algebra for this SQL for rows with values that appear more than 3 times?

select e.id, e.name, e.dno, COUNT(w.id) AS TOTAL_PROJECTS
from employee e,
     works_on w
where e.id = w.id
group by e.id
HAVING TOTAL_PROJECTS > 3;

Tables:

Employee (id, name, salary, dno)
Department (dno, dname, Mgr_id)
Project(Pno,Pname,dno)
Works_on(id, Pno, No_of_hrs)
philipxy
  • 14,867
  • 6
  • 39
  • 83
Irfan Abidi
  • 1
  • 1
  • 1
    Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed – jarlh Apr 21 '21 at 18:58
  • 3
    What is the question? Show us some sample table data, and the expected result - all as formatted text (not images.) [mcve] – jarlh Apr 21 '21 at 18:59
  • There are many RAs (relational algebras). They differ in operators & even what a relation is. Give definitions & a reference for yours. Eg textbook name, edition & page. Nested RA calls form a programming language. So give as much of a [mre] as you can, even if you are not actually running code. But--Google 'run relational algebra online'. Please show what parts you are able to do. See [ask], other [help] links, hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Apr 21 '21 at 22:20
  • [Converting aggregate operators from SQL to relational algebra](https://stackoverflow.com/q/7604969/3404097) Before considering posting please read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. PS [Re relational querying.](https://stackoverflow.com/a/24425914/3404097) – philipxy Apr 22 '21 at 22:12
  • @MujtabaKably The question was (only) in the title. I edited. But see the other comments. – philipxy Apr 01 '22 at 20:31

1 Answers1

-1

You don't have an id field in employee or work_on

SELECT e.Empid, e.name, e.dno, COUNT(*) AS TOTAL_PROJECTS
FROM employee e JOIN
     works_on w ON e.Empid = w.Empid
GROUP BY e.Empid, e.name, e.dno
HAVING TOTAL_PROJECTS > 3;
nbk
  • 45,398
  • 8
  • 30
  • 47
  • They want relational algebra not SQL. Though the column bug would be a relevant comment. Anyway you don't say what the point of this code is if this were an answer. – philipxy Apr 21 '21 at 22:37
  • i know but try http://dbis-uibk.github.io/relax/calc/local/uibk/local/3 and try it out furst and see ´how far you come – nbk Apr 22 '21 at 09:56
  • I don't understand your comment. Did you think I was the asker? I already told them to google online execution & to show what they can do. – philipxy Apr 22 '21 at 10:51
  • I might have made a mistake in the query i.e. incorrect colum name. My question was to re-write the query but using relational algebra. – Irfan Abidi Apr 22 '21 at 20:07
  • see my comment about creating your sample data and gibe it a try, but with data some one can help you – nbk Apr 22 '21 at 20:08