I need Perform select Query following this - Bandi

Posted by Jayakumars under Sql Server on 7/31/2015 | Points: 10 | Views : 1062 | Status : [Member] [MVP] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi
bandi

Create table Employee2
(
Eno int,
Name varchar(30),
Sex varchar(10),
Dob date,
Doj date,
Designation varchar(20),
Basic_Pay decimal(18,2),
Dept_No int
)

Create table Department2
(
DeptNo int,
DeptName varchar(30)
)

Create table Project
(
ProNo int,
proName varchar(20),
Dept_No int
)

Create table Worksfor
(
Eno int,
ProNi int,
Date date,
Hours int
)

My Questions

1.who have worked in more than three projects on a day.

2.View need track of the department number, the number of employees in the department, and the total basic pay expenditure for
each department.

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 8/3/2015 [Member] [MVP] Platinum | Points: 25

0	1.who have worked in more than three projects on a day. SELECT e.Eno, w.Date, COUNT( w.ProNi) FROM Worksfor w JOIN Employee2 E ON w.Eno = E.Eno GROUP BY e.Eno, w.Date HAVING COUNT( w.ProNi)>3 2.View need track of the department number, the number of employees in the department, and the total basic pay expenditure for each department. SELECT e.Dept_No, COUNT(e.Eno) NumberOfEmps, SUM(Basic_Pay) TotalExpenditure FROM Employee2 E JOIN Department2 d ON E.Dept_No= d.DeptNo GROUP BY e.Dept_No Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

1.who have worked in more than three projects on a day.

SELECT e.Eno, w.Date, COUNT( w.ProNi)
FROM Worksfor w
JOIN Employee2 E ON w.Eno = E.Eno
GROUP BY e.Eno, w.Date
HAVING COUNT( w.ProNi)>3

2.View need track of the department number, the number of employees in the department, and the total basic pay expenditure for
each department.

SELECT e.Dept_No, COUNT(e.Eno) NumberOfEmps, SUM(Basic_Pay) TotalExpenditure
FROM Employee2 E
JOIN Department2 d ON E.Dept_No= d.DeptNo
GROUP BY e.Dept_No

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 8/3/2015 [Member] [MVP] Bronze | Points: 25

0	Hi bandi Not working My test data this --Insert into Employee2 values(1,'AAA1','Male','02/02/1981','05/05/2002','Software Programmer1',12850.25,101) --Insert into Employee2 values(2,'AAA2','FeMale','07/02/1981','06/05/2002','Software Programmer2',12850.25,102) --Insert into Employee2 values(3,'AAA3','Male','09/02/1981','07/05/2002','Software Programmer3',12850.25,103) --Insert into Employee2 values(4,'AAA4','FeMale','12/02/1981','08/05/2002','Software Programmer4',12850.25,101) --Insert into Department2 values(101,'Software'),(102,'Hardware'),(104,'HR'),(103,'Production') --Insert into Project values(1,'Sales Management system',101),(2,'School Management system',102),(3,'Accounts Management system',103) --Insert into Worksfor VALUES(1,2,'02/03/1981',60),(2,3,'04/02/2013',40),(3,4,'07/02/2013',20) --Insert into Worksfor VALUES(4,2,'08/03/1981',20),(5,3,'04/02/2013',60),(6,4,'07/02/2013',35) My Question this 1.who have worked in more than three projects on a day. based calculation hours column ProNi column just enter number value thats it Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Hi
bandi

Not working

My test data this

--Insert into Employee2 values(1,'AAA1','Male','02/02/1981','05/05/2002','Software Programmer1',12850.25,101)
--Insert into Employee2 values(2,'AAA2','FeMale','07/02/1981','06/05/2002','Software Programmer2',12850.25,102)
--Insert into Employee2 values(3,'AAA3','Male','09/02/1981','07/05/2002','Software Programmer3',12850.25,103)
--Insert into Employee2 values(4,'AAA4','FeMale','12/02/1981','08/05/2002','Software Programmer4',12850.25,101)
--Insert into Department2 values(101,'Software'),(102,'Hardware'),(104,'HR'),(103,'Production')
--Insert into Project values(1,'Sales Management system',101),(2,'School Management system',102),(3,'Accounts Management system',103)
--Insert into Worksfor VALUES(1,2,'02/03/1981',60),(2,3,'04/02/2013',40),(3,4,'07/02/2013',20)
--Insert into Worksfor VALUES(4,2,'08/03/1981',20),(5,3,'04/02/2013',60),(6,4,'07/02/2013',35)

My Question this

1.who have worked in more than three projects on a day.
based calculation hours column
ProNi column just enter number value thats it

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/3/2015 [Member] [MVP] Platinum | Points: 25

0	I didn't get your point.... can you explain how u want the output for above sample data? I thought, Worksfor table will be having EmployeeNum, ProjectNum, Date on which she/he has worked on task, Hours ( how many hours she/he spent ) Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

I didn't get your point....

can you explain how u want the output for above sample data?

I thought, Worksfor table will be having EmployeeNum, ProjectNum, Date on which she/he has worked on task, Hours ( how many hours she/he spent )

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts