March 28, 2012 at 2:27 pm
Help,
i am doing a small project name 'school'..these are my tables ..after the table i have some question 1-15..i got stuck in question 3 where i have to find the number of employees in science department whose salary>7000...i went through different scenarios joining the table but for some reason it's not giving me the output...can anyone tell me the queries for the question from 3 and on..thank you
1)tEmployee
Columns: EmployeeID (PK), FirstName, LastName, DateOfJoin, Address, Email-ID, Phone#
2) **tDepartments**
Columns: DepartmentID (PK), Name
3) **tJobTitleMaster**
Columns: JobID (PK), Title
4) **tEmployeeDepartmentMap**
Columns: EmployeeID (FK), DepartmentID (FK), JobID (FK)
**5) tSalaryMaster**
Columns: SalaryID (PK), JobID (FK), YearsOfExperience, SalaryPerMonth
**6) tEmployeeSalaryMap**
Columns: EmployeeID (FK), SalaryID (FK)
Queries for below statements:
1)How many employees having salary greater than $5000 per month
2)Full name of all the Assitant professors
3)How many professors in Science Department has more than $7000 per month
4)No of Employees in each department
5)Last names of employees who belong to “Science” & “Maths” Departments
6)How many Employees have first name =”David”
7)What is the Average Salary of employees in “Arts” Department
8)Which department has highest paid employees
9)How many employees draw salary greater than $8000 per month and who have joined before 2005
10)Email Addresses of the Most and least Paid Employee
11)No of Employees Joined in each year, output of this query should be “Year”, “No Of Employees Joined”
12)Full names of all employees who belong to both “Maths” & “Science” Departments (Note that one employee can belong to more than one department)
13)Employee ID of employees who belong to either “Arts” or “Sports” Department and who have more than 5 years of experience
14)Print the Full names of all the employees in Upper Case who belong to more than one Department
15)Print the Full names of employees in Lower Case who belong to only one Department
March 28, 2012 at 2:40 pm
In my opinion you should provide your failed queries so we can guide you in the right direction.
No one is likely (hopefully) going to just answer your homework questions outright.
March 28, 2012 at 2:44 pm
JeffRush (3/28/2012)
In my opinion you should provide your failed queries so we can guide you in the right direction.No one is likely (hopefully) going to just answer your homework questions outright.
I second that.
Provide some of what you have tried, and then we can help you with that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2012 at 3:21 pm
SQLRNNR (3/28/2012)
JeffRush (3/28/2012)
In my opinion you should provide your failed queries so we can guide you in the right direction.No one is likely (hopefully) going to just answer your homework questions outright.
I second that.
Provide some of what you have tried, and then we can help you with that.
I am sure that many of us could answer thesse questions. The only problem with that is then you don't learn anything. Show us what you have done and we will tell you if you are on the right track, but we aren't going to do your work for you.
March 28, 2012 at 3:45 pm
sry to not have provided the query..i am doing something wrong coz when i joined do the second join i get blank but when i check the mapping its' correct..it's not a homework...it's practice...but good i can think it as hw..thank you for the reply
fro question 3
SELECT COUNT(*) FROM tEmployeeDepartmentMap a
INNER JOIN tDepartment b
ON a.EID = b.deptID
INNER JOIN tJobTitleMaster c
ON b.deptid = c.jobid
INNER JOIN tEmployeeSalaryMap d
ON a.eid = d.eid
INNER JOIN tSalaryMaster e
ON d.salaryid = e.salaryid
WHERE b.DepartName = 'Science'
AND c.title = 'professor'
AND e.SalaryPerMonth>7000
March 28, 2012 at 3:46 pm
sry to not have provided the query..i am doing something wrong coz when i joined do the second join i get blank but when i check the mapping its' correct..thank you for the reply
for question 3
SELECT COUNT(*) FROM tEmployeeDepartmentMap a
INNER JOIN tDepartment b
ON a.EID = b.deptID
INNER JOIN tJobTitleMaster c
ON b.deptid = c.jobid
INNER JOIN tEmployeeSalaryMap d
ON a.eid = d.eid
INNER JOIN tSalaryMaster e
ON d.salaryid = e.salaryid
WHERE b.DepartName = 'Science'
AND c.title = 'professor'
AND e.SalaryPerMonth>7000
March 28, 2012 at 3:47 pm
sry to not have provided the query..i am doing something wrong coz when i joined do the second join i get blank but when i check the mapping its' correct..it's not a homework...it's practice...but good i can think it as hw..thank you for the reply
fro question 3
SELECT COUNT(*) FROM tEmployeeDepartmentMap a
INNER JOIN tDepartment b
ON a.EID = b.deptID
INNER JOIN tJobTitleMaster c
ON b.deptid = c.jobid
INNER JOIN tEmployeeSalaryMap d
ON a.eid = d.eid
INNER JOIN tSalaryMaster e
ON d.salaryid = e.salaryid
WHERE b.DepartName = 'Science'
AND c.title = 'professor'
AND e.SalaryPerMonth>7000
March 28, 2012 at 3:50 pm
In that second join JobID is not likely to join correctly to DeptID.
I would start there.
Though you say it is not homework, I'm curious as to where you came up with the list of 15 questions to solve.
March 28, 2012 at 4:07 pm
For question 5, ......i am doing something wrong and i cant figure it out...it gives me null(blank Values)...is my mapping wrong or my queries...i am just starting with SQL and getting into 'joins'..any concept help ..thank you
select lname,departname from tEmployee a
join tEmployeeDeptMap b
on a.EID=b.DeptID
join tDepartment c
on c.DeptID=b.DeptID
where departname='science' and departname='math'
March 28, 2012 at 4:12 pm
I pointed put a potential flaw in your join logic.
You seem focused on questions.
I apologize if I am off base but I just feel as though you are just wanting answers to homework.
If I am wrong I am sure others will step forward and answer but I'm done.
March 28, 2012 at 4:22 pm
samg.j23 (3/28/2012)
For question 5, ......i am doing something wrong and i cant figure it out...it gives me null(blank Values)...is my mapping wrong or my queries...i am just starting with SQL and getting into 'joins'..any concept help ..thank youselect lname,departname from tEmployee a
join tEmployeeDeptMap b
on a.EID=b.DeptID
join tDepartment c
on c.DeptID=b.DeptID
where departname='science' and departname='math'
Couple things, none of them dealing with answering your questions for you.
First, when posting code, use the IFCode shortcuts. For sql code that would be th [ code="sql ] and [ /code ] (with NO spaces inside the the brackets.
Second, try formating your code for readability. The foloowing is how I would format your query:
select
lname,
departname
from
dbo.tEmployee a
inner join tEmployeeDeptMap b
on (a.EID = b.DeptID)
inner join tDepartment c
on (c.DeptID = b.DeptID)
where
c.departname = 'science'
and c.departname='math'
I think I see a problem in your code, specifically the WHERE clause.
March 28, 2012 at 4:27 pm
schrodinger's departname
March 28, 2012 at 5:18 pm
Just to add to what the others have said, I'm assuming that "EID" is the employer's ID and "DeptID" is the department's ID. If I'm right, this part of the query is not right:
on (a.EID = b.DeptID)
But nobody is going to help you further if you don't elaborate and post your thoughts on what should be the results of what you want to achieve. 🙂
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
March 28, 2012 at 9:03 pm
well the guy i am taking class with gave the project and the question but i am not suppose to answer and submit it as a homework..i am suppose to practice it and then we would discuss it in the next classes which are on weekends...anyway i just wanted help undertanding the concept and i didn't not want or expect anyone to answer all my questions as hw..thank you
March 28, 2012 at 9:12 pm
The person i am taking the class came up with the project and the question but again it is for practice and we would discuss it in the next class which is on the weekend...i m not expecting to get answers but i want to know my mistakes and what i am doing wrong and the concept of understanding "join" to the best...so really don't send me answer just tell me and thanx for the help i was able to figure out ques 3..thank you again
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply