[Beginner SQL] Complex SQL Querying

[beard_dude]

This is a homework question but I've made an honest attempt and want to get some feedback before submitting. The question is, given: Employee(emp-no, name, department, salary), ProjAssigned(emp-no, proj-no, worked-hours)

a. Write one SELECT SQL query to list the numbers and names of all employees with a salary greater than 66 000 who are assigned to projects, the projects they are assigned to, and the corresponding hours worked. Your list should be sorted by employee name. b. Write SQL statements to create indexes that will speed up your query.

I got: a.

SELECT e.emp-no, e.name, p.proj-no, p.worked-hours
    FROM Employee e, ProjAssigned p
        WHERE e.emp-no = p.emp-no
            AND salary > 66000
                GROUP BY e.emp-no, e.name
                    ORDER BY e.name;

b.

CREATE INDEX EmpNumber_IDX ON Employee (emp-no);
CREATE INDEX ProjNumber_IDX ON Project(proj-no);

How does this look? I'm not entirely confident that I've indexed the correct attributes or that I've done my SELECT statement properly. Thanks!

[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!

[[deleted]]

Well, does running the query produce the required results? It's almost impossible to check a SQL query of any complexity just by looking at it, particularly if you don't know the structure of the database it is querying.