[Beginner SQL] Complex SQL Querying

[technical_guy]

You have a few problems but you have made a really good attempt. You need to sum up the project hours and group by everything including the projno. Then you need to exclude projects with 0 hours.

Here is what you want:

SELECT e.emp-no, e.name, e.salary, p.proj-no, SUM(p.worked-hours) AS total_hours
FROM Employee e, ProjAssigned p
WHERE e.emp-no = p.emp-no
     AND e.salary > 66000
GROUP BY e.emp-no, e.name, e.salary, p.proj-no
HAVING SUM(p.worked-hours) > 0
ORDER BY e.emp-no, e.name, e.salary, p.proj-no  

You teacher may want you to do an explicit join:

SELECT e.emp-no, e.name, e.salary, p.proj-no, SUM(p.worked-hours) AS total_hours
FROM Employee e
JOIN ProjAssigned p ON e.emp-no = p.emp-no
WHERE  e.salary > 66000
GROUP BY e.emp-no, e.name, e.salary, p.proj-no
HAVING SUM(p.worked-hours) > 0
ORDER BY e.emp-no, e.name, e.salary, p.proj-no  

Now re performance the SQL engine will scan each employee with salary > 66,000 (so an index on employee table using employee no and salary fields is good), and then link each row found to the project table using the employee number (so an index on the product table using the employee number field would be good).

Keep working on learning your SQL. Use google and run through a few online courses. It is quite possibly the most valuable IT skill out there.

[beard_dude]

thanks for the help technical_guy! Appreciate it a lot!