geeta_m9
asked on
Queries
I have the following tables:
Employee(Emp_Num, Emp_Name, Job_Classification) - PK is Emp_Num
Job_Class (Job_Class, Charge_Hour) - PK is Job_Class
Job(Emp_Num, Proj_Num, Hours_Worked) - PK is Emp_Num, Proj_Num
Project (Proj_Num, Proj_Name) - PK is Proj_Num
How can I write the SQL for the following?
1. The total charge for each project name (the project name and total charge must be listed)
2. The total charge for project name "A" if each employee working on that project has an additional 10 hours billed for the project.
Employee(Emp_Num, Emp_Name, Job_Classification) - PK is Emp_Num
Job_Class (Job_Class, Charge_Hour) - PK is Job_Class
Job(Emp_Num, Proj_Num, Hours_Worked) - PK is Emp_Num, Proj_Num
Project (Proj_Num, Proj_Name) - PK is Proj_Num
How can I write the SQL for the following?
1. The total charge for each project name (the project name and total charge must be listed)
2. The total charge for project name "A" if each employee working on that project has an additional 10 hours billed for the project.
ASKER
Actually, I made a mistake in the first table, it should be:
Employee(Emp_Num, Emp_Name, Job_Class)
This is what I have so far for part 1:
SELECT a.proj_name, b.hours_worked * c.charge_hour AS total
FROM Project AS a
JOIN Job AS b ON a.proj_num = b.proj_num
JOIN Employee as c ON c.emp_num = b.emp_num
JOIN Job_Class AS d ON c.job_class = d.job_class
GROUP BY a.proj_num
Employee(Emp_Num, Emp_Name, Job_Class)
This is what I have so far for part 1:
SELECT a.proj_name, b.hours_worked * c.charge_hour AS total
FROM Project AS a
JOIN Job AS b ON a.proj_num = b.proj_num
JOIN Employee as c ON c.emp_num = b.emp_num
JOIN Job_Class AS d ON c.job_class = d.job_class
GROUP BY a.proj_num
If you want a total or SUM across a specific field you probably want to GROUP BY that field or fields.
Look at SUM and aggregate queries.
Look at SUM and aggregate queries.
ASKER
Sorry I meant to say GROUP BY a.proj_name, not proj_num
Anyway, looks like you need a SUM in there.
ASKER
Forgot to include it:
SELECT a.proj_name, SUM(b.hours_worked * c.charge_hour) AS total
FROM Project AS a
JOIN Job AS b ON a.proj_num = b.proj_num
JOIN Employee as c ON c.emp_num = b.emp_num
JOIN Job_Class AS d ON c.job_class = d.job_class
GROUP BY a.proj_num
SELECT a.proj_name, SUM(b.hours_worked * c.charge_hour) AS total
FROM Project AS a
JOIN Job AS b ON a.proj_num = b.proj_num
JOIN Employee as c ON c.emp_num = b.emp_num
JOIN Job_Class AS d ON c.job_class = d.job_class
GROUP BY a.proj_num
Group by is still wrong.
That said:
What isn't working with that?
If you can provide some sample data for those tables and expected results, I can offer more guidance.
That said:
What isn't working with that?
If you can provide some sample data for those tables and expected results, I can offer more guidance.
Found one issue: I don't see charge_hour in the employee table.
ASKER
I copied the old value, it should be this:
SELECT a.proj_name, SUM(b.hours_worked * c.charge_hour) AS total
FROM Project AS a
JOIN Job AS b ON a.proj_num = b.proj_num
JOIN Employee as c ON c.emp_num = b.emp_num
JOIN Job_Class AS d ON c.job_class = d.job_class
GROUP BY a.proj_name;
SELECT a.proj_name, SUM(b.hours_worked * c.charge_hour) AS total
FROM Project AS a
JOIN Job AS b ON a.proj_num = b.proj_num
JOIN Employee as c ON c.emp_num = b.emp_num
JOIN Job_Class AS d ON c.job_class = d.job_class
GROUP BY a.proj_name;
Still see a missing table for the correct charge_hour column.
ASKER
SELECT a.proj_name, SUM(b.hours_worked * d.charge_hour) AS total
FROM Project AS a
JOIN Job AS b ON a.proj_num = b.proj_num
JOIN Employee as c ON c.emp_num = b.emp_num
JOIN Job_Class AS d ON c.job_class = d.job_class
GROUP BY a.proj_name;
FROM Project AS a
JOIN Job AS b ON a.proj_num = b.proj_num
JOIN Employee as c ON c.emp_num = b.emp_num
JOIN Job_Class AS d ON c.job_class = d.job_class
GROUP BY a.proj_name;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, it wasn't working earlier because of all the previous errors. I can't test it right now. I will have to do it later.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
Unfortunately we cannot provide answers to those:
http://support.experts-exchange.com/customer/portal/articles/1435136
We can offer advice and assistance.
Please provide what you have already tried.