728x90
반응형
-- 1. 학과별로 평균 몸무게와 학생 수를 출력하되 평균 몸무게의 내림차순으로 정렬하여라.
SELECT DEPTNO1, AVG(WEIGHT), COUNT(*)
FROM STUDENT
GROUP BY DEPTNO1
ORDER BY AVG(WEIGHT) DESC
;
SELECT DEPTNO1, AVG(WEIGHT), COUNT(*)
FROM STUDENT
GROUP BY DEPTNO1
ORDER BY 2 DESC
;
-- 2. 학과별 교수 수가 2명 이하인 학과 번호, 교수 수를 출력하여라
SELECT DEPTNO, COUNT(*)
FROM PROFESSOR
HAVING COUNT(*) <= 2
;
/* 그룹의 조건은 반드시 HAVING 구문 사용하기
SELECT DEPTNO, COUNT(*)
FROM PROFESSOR
WHERE COUNT(*) <= 2
;
*/
-- 3. 교수테이블의 부서중 최대인원을 가진 부서의 인원수와 최소인원을 가진 부서의 인원수 출력하기
SELECT MAX(COUNT(*)), MIN(COUNT(*))
FROM PROFESSOR
GROUP BY DEPTNO
;
-- 4. 교수테이블에서 평균 급여가 350이상인 부서의 부서코드, 평균급여, 급여합계를 출력하기
SELECT DEPTNO, AVG(PAY), SUM(PAY)
FROM PROFESSOR
GROUP BY DEPTNO
HAVING AVG(PAY) >= 350
;
-- 5.학년별, 지역(전화번호의 지역번호)별 학생의 키평균과 몸무게 평균을 출력하시오.
SELECT GRADE, SUBSTR(TEL,1,INSTR(TEL,')')-1), AVG(HEIGHT), AVG(WEIGHT)
FROM STUDENT
GROUP BY GRADE, SUBSTR(TEL,1,INSTR(TEL,')')-1)
ORDER BY GRADE, 2
;
-- 6. 4학년 학생의 이름 학과번호, 학과이름 출력하기
SELECT S.NAME, S.DEPTNO1, D.DNAME
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = D.DEPTNO
AND S.GRADE = 4
;
SELECT S.NAME, S.DEPTNO1, D.DNAME
FROM STUDENT S JOIN DEPARTMENT D
ON S.DEPTNO1 = D.DEPTNO
AND S.GRADE = 4
;
-- 7. 오나라 학생의 이름, 학과코드1,학과이름,학과위치 출력하기
SELECT S.NAME, S.DEPTNO1, D.DNAME, D.BUILD
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = D.DEPTNO
AND S.NAME = '오나라'
;
SELECT S.NAME, S.DEPTNO1, D.DNAME, D.BUILD
FROM STUDENT S JOIN DEPARTMENT D
ON S.DEPTNO1 = D.DEPTNO
AND S.NAME = '오나라'
;
-- 8. 학생테이블,점수테이블(exam_01),학점테이블(hakjum) 테이블에서
-- 학생의 이름, 점수, 학점조회하기
SELECT S.NAME, E.TOTAL, H.GRADE
FROM STUDENT S, EXAM_01 E, HAKJUM H
WHERE S.STUDNO = E.STUDNO
AND E.TOTAL BETWEEN H.MIN_POINT AND H.MAX_POINT
;
SELECT S.NAME, E.TOTAL, H.GRADE
FROM STUDENT S JOIN EXAM_01 E
ON S.STUDNO = E.STUDNO JOIN HAKJUM H
ON E.TOTAL BETWEEN H.MIN_POINT AND H.MAX_POINT
;
-- 9. 학번과 학생 이름과 소속학과이름을 학생 이름순으로 정렬하여 출력
SELECT S.STUDNO, S.NAME, D.DNAME
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = D.DEPTNO
ORDER BY S.NAME
;
-- 10. 교수별로 교수 이름과 지도 학생 수를 출력하기.
SELECT P.NAME, COUNT(*)
FROM PROFESSOR P, STUDENT S
WHERE P.PROFNO = S.PROFNO
GROUP BY P.NAME
ORDER BY P.NAME
;
-- 11. 교수별로 교수 이름과 지도 학생의 이름을 출력하기
SELECT P.NAME, S.NAME
FROM PROFESSOR P, STUDENT S
WHERE P.PROFNO = S.PROFNO
ORDER BY P.NAME
;
-- 12.emp2, p_grade 테이블을 조회하여 나이를 기준으로 예상직급을 조회하기
-- 사원들의 이름,나이,현재 직급, 예상직급을 출력하기
-- 나이는 생일과 오늘을 기준으로하고, 개월수 /12로 한다. 소숫점 이하는 버림
SELECT *
FROM EMP2
;
SELECT E.NAME, TRUNC(MONTHS_BETWEEN(SYSDATE, E.BIRTHDAY) / 12) 나이, E.POSITION, P.POSITION
FROM EMP2 E, P_GRADE P
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,E.BIRTHDAY) / 12) BETWEEN P.S_AGE AND P.E_AGE
;
-- 13.emp2, p_grade 테이블을 조회하여 입사년(empno 앞4자리)을 기준으로 예상 직급을 조회하기
-- 사원들의 이름,나이,근속년도,현재 직급, 예상직급을 출력하기
-- 입사년도는 오늘을 기준으로하고, trunc함수를 사용
SELECT E.NAME, TRUNC(MONTHS_BETWEEN(SYSDATE, E.BIRTHDAY) / 12) 나이,
TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - TO_NUMBER(SUBSTR(E.EMPNO,1,4)) 근속년도,
E.POSITION, P.POSITION
FROM EMP2 E, P_GRADE P
WHERE TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - TO_NUMBER(SUBSTR(E.EMPNO,1,4)) BETWEEN P.S_YEAR AND P.E_YEAR
;
-- 14. 성이 김씨인 학생들의 이름, 학과이름 학과위치 출력하기
SELECT S.NAME, D.DNAME, D.BUILD
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = D.DEPTNO
AND S.NAME LIKE '김%'
;
SELECT S.NAME, D.DNAME, D.BUILD
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = D.DEPTNO
AND SUBSTR(S.NAME,1,1) = '김'
;
-- 15. 고객테이블과 gift 테이블을 조인하여 고객이 자기포인트보다 낮은 포인트 상품 중
-- 한가지를 선택할 수 있다고 할때, 고객이 선택할 수 있는 상품의갯수 출력하기
-- 고객명과 선택가능한 상품의 갯수 출력하기
SELECT G1.GNAME, COUNT(G2.GNAME)
FROM GOGAK G1, GIFT G2
WHERE G1.POINT >= G2.G_START
GROUP BY G1.GNAME
ORDER BY G1.GNAME
;
-- 16. dept2테이블에서 부서코드와, 부서명, 상위부서명 출력하기. outer join 사용
-- 단 모든 부서가 조회되도록 한다.
SELECT *
FROM DEPT2
;
SELECT D1.DCODE, D1.DNAME, D2.DNAME
FROM DEPT2 D1, DEPT2 D2
WHERE D1.PDEPT = D2.DCODE(+)
ORDER BY D1.DCODE
;
SELECT D1.DCODE, D1.DNAME, D2.DNAME
FROM DEPT2 D1 LEFT OUTER JOIN DEPT2 D2
ON D1.PDEPT = D2.DCODE(+)
ORDER BY D1.DCODE
;
-- 17. 교수테이블에서 교수번호, 교수이름,입사일 자신보다 입사일이 빠른사람의 인원수를 출력하기
-- 모든 교수정보가 조회되어야 한다. 입사일이 빠른순으로 정렬하기
SELECT P1.PROFNO, P1.NAME, COUNT(P2.HIREDATE)
FROM PROFESSOR P1, PROFESSOR P2
WHERE P1.HIREDATE > P2.HIREDATE(+)
GROUP BY P1.PROFNO, P1.NAME
ORDER BY P1.PROFNO
;
-- 18. emp 테이블에서 사원번호,사원명,직업,상사번호,상사이름,상사의직업을 출력하기.
-- 모든 사원이 조회되도록 한다.
SELECT E1.EMPNO, E1.ENAME, E1.JOB, E2.EMPNO, E2.ENAME, E2.JOB
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO(+)
;
-- 19. 부서테이블(department)에서 각 부서의 상위 부서 이름을 출력 단 상위부서가 없는 부서도 출력
SELECT D1.DNAME, D2.DNAME
FROM DEPARTMENT D1, DEPARTMENT D2
WHERE D1.PART = D2.DEPTNO(+)
;
-- 20. 부서테이블(department)에서 공과대학(DEPTNO=10)에 소속된 학과 이름을 출력
SELECT D1.DNAME
FROM DEPARTMENT D1, DEPARTMENT D2, DEPARTMENT D3
WHERE D1.PART = D2.DEPTNO
AND D2.PART = D3.DEPTNO
AND D3.DEPTNO = 10
;728x90
반응형