|
View previous topic :: View next topic
|
| Author |
Message |
murali_andaluri
Currently Banned
New User
Joined: 07 May 2005
Posts: 22
|
|
|
|
Hi Friends,
An EMP_PROJ table has following fields:
EMP_ID, EMP_NAME, PROJ_ID, PROJ_NM, EFF_DT, EXP_DT
Key of the table is EMP_ID + PROJ_ID + EFF_DT.
a. Write a query to list those employees who have served in at least 3 projects, and they are not on bench currently. Bench PROJ_ID = 99999.
b. Write a query to list those employees who have served in at least 3 projects, and they are on bench currently. Bench PROJ_ID = 99999.
c. Write a query to list those employees who were on bench before they left the company.
d. Write a query to list those employees who have served at least 1 month in their last project.
e. Write a query to list employees and their project experience till date. |
|
| Back to top |
|
 |
Help-Me-Out
New User
.jpg)
Joined: 09 Dec 2006
Posts: 56
Location: Pune
|
|
|
|
Hi,
I thing only one query will do for your answer, only thing is you need to replace the condition accordingly.
Here is your answer:
a.
SELECT COUNT(PROJ_ID) AS A , EMP_ID, EMP_NAME
FROM EMP_PROJ
GROUP BY PROJ_ID
HAVING COUNT(PROJ_ID) > 2 AND PROJ_ID != '99999' |
|
| Back to top |
|
|
nuthan
Active User
Joined: 26 Sep 2005
Posts: 146
Location: Bangalore
|
|
|
|
1)
SELECT EMP_ID, EMP_NAME FROM EMP_PROJ WHERE PROJ_ID <> 99999 AND EMP_ID IN (SELECT EMP_ID FROM EMP_PROJ GROUP BY (EMP_ID) HAVING COUNT(*) > 3 );
2)
SELECT EMP_ID, EMP_NAME FROM EMP_PROJ WHERE PROJ_ID = 99999 AND EMP_ID IN (SELECT EMP_ID FROM EMP_PROJ GROUP BY (EMP_ID) HAVING COUNT(*) > 3 ); |
|
| Back to top |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
Hi All,
Nuthun answered excellently, keep a top Nuthan.
| Quote: |
| c. Write a query to list those employees who were on bench before they left the company. |
Elaborate your question I mean for status of employee (in which table status column is available and status column)
But u can use below query if know status and status table.
SELECT EMP_ID, EMP_NAME FROM EMP_PROJ WHERE PROJ_ID = 99999 AND STATUS_EMP = 'NOT ACTIVE' (GIVE STATUS CONDITION HERE);
| Quote: |
| d. Write a query to list those employees who have served at least 1 month in their last project. |
SELECT EMP_ID, EMP_NAME FROM EMP_PROJ
WHERE EFF_DT(MONTH(EXP_DT - EFF_DT ) >= 1 AND
EFF_DT IN (SELECT MAX(EFF_DT) FROM EMP_PROJ);
Use above query, If error found let us know, So that again we can look into it..
| Quote: |
| e. Write a query to list employees and their project experience till date. |
SELECT EMP_ID, EMP_NAME, (MONTH(EXP_DT - EFF_DT ) AS EXPERIENCE
FROM EMP_PROJ
GROUP BY EMP_ID, EMP_NAME
HAVING COUNT(EMP_ID) >= 1;
Use above query, If error found let us know, So that again we can look into it..
Regards |
|
| Back to top |
|
|
murali_andaluri
Currently Banned
New User
Joined: 07 May 2005
Posts: 22
|
|
|
|
Hi Friends,
Thank you very much for your quick answers. |
|
| Back to top |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
Hi Murali,
| Quote: |
Hi Friends,
Thank you very much for your quick answers. |
Let us know whether you had got answer or not?
Thanks N Regards |
|
| Back to top |
|
|
|
|
