7. 세 명 이상의 사원이 참여한 프로젝트의 번호, 이름, 사원의 수를 보이시오.(검색 결과에 ‘사원수’ 열 이름 사용)
8. 세 명 이상의 사원이 있는 부서의 사원 이름, 전화번호, 성별, 부서명을 보이시오.
숙제인데..
1~6,9번은 해결을했는데 7번과 8번의 3명이상이라는게 어떤방향인지 감도 안잡히네요..
그룹바이 해빙절 ㎢쨉잘 안되는거 같네요.. 힌트점 부탁드립니다..
밑줄은 기본키입니다.. 쌍 밑줄은 복합키입니다.
Employee Department
열 이름 |
데이터타입 |
널 허용여부 |
empno |
int |
허용 안 함 |
name |
varchar(20) |
|
phoneno |
int |
|
address |
varchar(20) |
|
sex |
varchar(20) |
|
position |
varchar(20) |
|
deptno |
int |
|
열 이름 |
데이터타입 |
널 허용여부 |
deptno |
int |
허용 안 함 |
deptname |
varchar(20) |
|
manager |
varchar(20) |
|
Project Works
열 이름 |
데이터타입 |
널 허용여부 |
projno |
int |
허용 안 함 |
projname |
varchar(20) |
|
deptno |
int |
|
열 이름 |
데이터타입 |
널 허용여부 |
empno |
int |
허용 안 함 |
projno |
int |
허용 안 함 |
hoursworked |
int |
|
!...
From work inner join Project
on work.projno = Project.projno
group by work.projno,Project.projename
having COUNT(*) >=3
¶Ç´Â
select z.projno, Project.projename,z.»ç¿ø¼ö
from ( Select work.projno, COUNT(*) as »ç¿ø¼ö
From work
group by work.projno
having COUNT(*) >=3 ) as z
inner join Project on z.projno = Project.projno
8¹øÀº ¼÷Á¦ÀÌ´Ï Âü°íÇؼ Á÷Á¢ Çغ¸½Ã±â ¹Ù¶ø´Ï´Ù.
ÇÏÇÏ..
°øºÎ°¡ ´õ ÇÊ¿äÇÏ´Ù´Â ½ÅÈ£ÀΰŠ°°½À´Ï;´Ù..
(
Select '1' As empno, 'p1' as projno, 10 as hoursworked Union All
Select '2' As empno, 'p1' as projno, 10 as hoursworked Union All
Select '3' As empno, 'p1' as projno, 10 as hoursworked Union All
Select '1' As empno, 'p2' as projno, 10 as hoursworked Union All
Select '1' As empno, 'p3' as projno, 10 as hoursworked Union All
Select '3' As empno, 'p3' as projno, 10 as hoursworked Union All
Select '4' As empno, 'p3' as projno, 10 as hoursworked Union All
Select '5' As empno, 'p3' as projno, 10 as hoursworked Union All
Select '6' As empno, 'p3' as projno, 10 as hoursworked Union All
Select '1' As empno, 'p4' as projno, 10 as hoursworked Union All
Select '5' As empno, 'p4' as projno, 10 as hoursworked Union All
Select '7' As empno, 'p4' as projno, 10 as hoursworked
)
,Project As
(
Select 'p1' as projno, 'Project 1' as projename, 'D1' as deptno Union All
Select 'p2' as projno, 'Project 2' as projename, 'D1' as deptno Union All
Select 'p3' as projno, 'Project 3' as projename, 'D1' as deptno Union All
Select 'p4' as projno, 'Project 4' as projename, 'D1' as deptno Union All
Select 'p5' as projno, 'Project 5' as projename, 'D1' as deptno Union All
Select 'p6' as projno, 'Project 6' as projename, 'D1' as deptno
)
Select work.projno, Project.projename,COUNT(*) as »ç¿ø¼ö
From work inner join Project
on work.projno = Project.projno
group by work.projno,Project.projename
having COUNT(*) >=3