March 28, 2012 at 9:20 pm
We have identified an issue with your initial join(s).
Same with question 5.
If you have specific questions I would attempt an answer.
March 29, 2012 at 2:39 pm
Thank You everyone (JEFF,Lynn,Andre and all),
i just started the Advanced select topic and "join" function and it's just getting harder...i was able to fix my mistake for 3 and 5 where i had to Join the deptid with deptid...i am still trying to grab the concept and get better, if any advice,suggestion or way to understand the concept better please let me know...also i have some more errors ..i'll keep wring queries and trying and if i can't i ll keep posting...thank you all again.
Question 7. What is the Average Salary of employees in “Arts” Department?
select AVG(salarypermonth) from
(select * from tEmployeeDeptMap a
inner join tDepartment b
on a.deptid=b.DeptID
inner join tSalaryMaster c
on a.JobID=c.JobId
where DepartName='arts') e
the subquery is fine...am i missing something or the select avg part is wrong?
Question 13.Employee ID of employees who belong to either “Arts” or “Sports” Department and who have more than 5 years of experience
--although i get the number something is wrong here..do i need to map the tEmplooyeeSalaryMap too?
select a.eid from tEmployee a
join tEmployeeDeptMap b
on a.EID=b.DeptID
join tDepartment c
on c.DeptID=b.DeptID
Join tSalaryMaster d
on d.jobid=c.DeptID
where departname='arts' or departname='sports'
and yearsofexperience >5
Question 11 .No of Employees Joined in each year, output of this query should be “Year”, “No Of Employees Joined”
select year(dateadd(M,-3,JoinDate)),COUNT(*) as [No of Employees Joined],eid
from tEmployee
where JoinDate is not null
group by JoinDate,EID
i get which eid joined at which year but how do i group it . do i use sum
March 29, 2012 at 11:19 pm
Question 7. What is the Average Salary of employees in “Arts” Department?
select AVG(salarypermonth) from
(select * from tEmployeeDeptMap a
inner join tDepartment b
on a.deptid=b.DeptID
inner join tSalaryMaster c
on a.JobID=c.JobId
where DepartName='arts') e
the subquery is fine...am i missing something or the select avg part is wrong?
The AVG is on what and what are your SELECT? :hehe:
March 29, 2012 at 11:24 pm
Question 11 .No of Employees Joined in each year, output of this query should be “Year”, “No Of Employees Joined”
select year(dateadd(M,-3,JoinDate)),COUNT(*) as [No of Employees Joined],eid
from tEmployee
where JoinDate is not null
group by JoinDate,EID
i get which eid joined at which year but how do i group it . do i use sum
Please read the book and check your syntax. 😉
March 30, 2012 at 5:28 am
--Question 11 .No of Employees Joined in each year, output of this query should be “Year”, “No Of Employees Joined”
select
distinct DateOfJoin,
COUNT(EmployeeID) No_Of_EmployeesJoined
from tEmployee
group by
DateOfJoin,
EmployeeID
Need help? Help us help you.
March 31, 2012 at 11:19 am
tommey152 (3/30/2012)
--Question 11 .No of Employees Joined in each year, output of this query should be “Year”, “No Of Employees Joined”
select
distinct DateOfJoin,
COUNT(EmployeeID) No_Of_EmployeesJoined
from tEmployee
group by
DateOfJoin,
EmployeeID
The above code would return a single row per DateOfJoin -and- Employee.
You should look into only grouping by the date and keep in mind the DATEPART you are wanting to group by to satisfy the question.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply