기본 셀렉트문 연습문제 난이도 하급!

--14쪽 연습문제--

=====================================================

1. EMPLOYEE 테이블에서 모든 컬럼의 데이터를 검색하면?

=====================================================

SELECT * FROM EMPLOYEE;

=========================================================================

2. EMPLOYEE 테이블에서 사원번호, 사원이름, 직급, 연봉, 입사일을 검색하면?

=========================================================================

SELECT EMP_NO, EMP_NAME, JIKUP, SALARY, HIRE_DATE FROM EMPLOYEE;

===================================================================================================

3. EMPLOYEE 테이블에서 사원번호, 사원이름, 직급, 연봉, 입사일을 별칭하고 연봉에는 만원을 넣으시오.

===================================================================================================

SELECT EMP_NO AS "사원명", EMP_NAME AS "사원명", JIKUP AS "직급", SALARY||'만원' AS "연봉", HIRE_DATE AS "입사일" FROM EMPLOYEE;

================================================================================

4. EMPLOYEE 테이블에서 사원이름, 직급, 연봉, 세금을 검색하면?(세금은 연봉의 12%)

================================================================================

SELECT EMP_NO AS "사원명", JIKUP AS "직금", SALARY ||'만원' AS "연봉", SALARY*0.12 ||'만원' AS "세금" FROM EMPLOYEE;

================================================

5. EMPLOYEE 테이블에서 직급을 중복없이 검색하면?

================================================

SELECT DISTINCT JIKUP AS "직급" FROM EMPLOYEE;

============================================================

6. EMPLOYEE 테이블에서 부서번호와 직급을 중복 없이 검색하면?

============================================================

SELECT DISTINCT DEP_NO AS "부서번호",JIKUP AS "직급" FROM EMPLOYEE;

==============================================================

7. EMPLOYEE 테이블에서 연봉이 3000만원 이상인 직원을 검색하면?

==============================================================

SELECT * FROM EMPLOYEE WHERE SALARY>=3000;

====================================================

8. EMPLOYEE 테이블에서 연봉을 오름차순으로 검색하면?

====================================================

SELECT * FROM EMPLOYEE ORDER BY SALARY ASC;

=================================================================================

9. EMPLOYEE 테이블에서 부서번호 오름차순을 유지하면서 연봉 내림차수능로 검색하면?

=================================================================================

SELECT * FROM EMPLOYEE ORDER BY DEP_NO ASC, SALARY DESC;

================================================

10. EMPLOYEE 테이블에서 상위연봉 5명을 검색하면?

================================================

SELECT * FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM<=5;

--22쪽 연습문제--

===================================================================================

1. EMPLOYEE 테이블에서 최소연봉, 최대연봉, 평균연봉, 연봉총합, 총인원수를 검색하면?

===================================================================================

SELECT

MIN(SALARY)      AS "최소연봉"

,MAX(SALARY)     AS "최대연봉"

,AVG(SALARY)     AS "평균연봉"

,SUM(SALARY)     AS "연봉총합"

,COUNT(EMP_NAME) AS "총인원수"

FROM EMPLOYEE;

=========================================================================

2. EMPLOYEE 테이블에서 소속부서 총개수를 검색하면? 회사에 있는 총부서는 X

=========================================================================

SELECT COUNT(DISTINCT(DEP_NO)) "총개수"FROM EMPLOYEE;

====================================================================

3. CUSTOMER 테이블에서 담당직원이 있는 고객수를 검색해서 출력하려면?

====================================================================

SELECT COUNT(*)"고객수" FROM CUSTOMER WHERE EMP_NO IS NOT NULL;

======================================================================

4. EMPLOYEE 테이블에서 직원번호 직원명 입사분기를 검색해서 출력하려면?

======================================================================

SELECT EMP_NO, EMP_NAME,TO_CHAR(HIRE_DATE,'Q')||'분기' AS "입사분기" FROM EMPLOYEE;

====================================================================

5. EMPLOYEE 테이블에서 직원번호 직원명 근무년차를 검색해서 출력하면?

====================================================================

SELECT EMP_NO "직원번호", EMP_NAME "직원 이름", CEIL((SYSDATE-HIRE_DATE)/365)-1||'년차' AS "근무년차" FROM EMPLOYEE;

===============================================================

6. EMPLOYEE 테이블에서 직원번호 직원명 성별을 검색해서 출력하면?

===============================================================

SELECT EMP_NO "직원번호", EMP_NAME "직원 이름", CASE SUBSTR(JUMIN_NUM,7,1) WHEN '1' THEN '남' WHEN '2' THEN '여' ELSE '맙소사' END "성별" FROM EMPLOYEE;

===================================================================================================================

7. CUSTOMER 테이블에서 고객번호 고객명 담당직원번호를 검색해서 출력하면 ? 단 직원번호가 없으면 없음으로 표시하시오.

===================================================================================================================

SELECT CUS_NO,CUS_NAME,NVL(TO_CHAR(EMP_NO),'없음') AS "담당직원번호" FROM CUSTOMER;

=================================================================================================================================

8. CUSTOMER 테이블에서 고객번호 고객명 담당직원존여부를 검색해서 출력하면 ? 단 직원번호가 있으면 있음 없으면 없음으로 표시하시오.

=================================================================================================================================

SELECT CUS_NO,CUS_NAME,NVL2(TO_CHAR(EMP_NO),'있음','없음') AS "담당직원존재여부" FROM CUSTOMER;

=======================================================================

9. EMPLOYEE 테이블에서 직원번호 직원명 나이 연령대를 검색해서 출력하면?

=======================================================================

SELECT

EMP_NO

,EMP_NAME

,(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-TO_NUMBER(CONCAT('19',SUBSTR(JUMIN_NUM,1,2))))-1 || '세' AS "연령"

,CASE SUBSTR(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-TO_NUMBER(CONCAT('19',SUBSTR(JUMIN_NUM,1,2))),1,1) WHEN '1' THEN '10대' WHEN '2' THEN '20대' WHEN '3' THEN '30대' WHEN '4' THEN '40대' WHEN '5' THEN '50대'

WHEN '6' THEN '60대' ELSE '로진' END AS "연령대"

FROM EMPLOYEE;

==============================================

10. EMPLOYEE 테이블에서 직급순서대로 출력하면?

==============================================

SELECT

*

FROM EMPLOYEE

ORDER BY

CASE JIKUP

WHEN '사장' THEN 1

WHEN '부장' THEN 2

WHEN '과장' THEN 3

WHEN '대리' THEN 4

WHEN '사원' THEN 5 END ASC;

===============================================================================

11. 다음 테이블에서 제품총개수를 내림차순으로 제일 많은 것을 정렬하여 보이려면?

===============================================================================

CREATE TABLE PRODUCT(

  P_NO NUMBER(3)

  ,P_NAME VARCHAR2(20) NOT NULL UNIQUE

  ,TOT_CNT VARCHAR2(20) NOT NULL

);

INSERT INTO PRODUCT VALUES(1, '컴퓨터1', '20');

INSERT INTO PRODUCT VALUES(2, '컴퓨터2', '2');

INSERT INTO PRODUCT VALUES(3, '컴퓨터3', '4');

INSERT INTO PRODUCT VALUES(4, '컴퓨터4', '2');

INSERT INTO PRODUCT VALUES(5, '컴퓨터5', '16');

INSERT INTO PRODUCT VALUES(6, '컴퓨터6', '60');

INSERT INTO PRODUCT VALUES(7, '컴퓨터7', '30');

INSERT INTO PRODUCT VALUES(8, '컴퓨터8', '27');

INSERT INTO PRODUCT VALUES(9, '컴퓨터9', '25');

INSERT INTO PRODUCT VALUES(10, '컴퓨터10', '22');

INSERT INTO PRODUCT VALUES(11, '컴퓨터11', '34');

INSERT INTO PRODUCT VALUES(12, '컴퓨터12', '50');

INSERT INTO PRODUCT VALUES(13, '컴퓨터13', '8');

INSERT INTO PRODUCT VALUES(14, '컴퓨터14', '9');

INSERT INTO PRODUCT VALUES(15, '컴퓨터15', '10');

INSERT INTO PRODUCT VALUES(16, '컴퓨터16', '15');

INSERT INTO PRODUCT VALUES(17, '컴퓨터17', '20');

INSERT INTO PRODUCT VALUES(18, '컴퓨터18', '30');

INSERT INTO PRODUCT VALUES(19, '컴퓨터19', '40');

INSERT INTO PRODUCT VALUES(20, '컴퓨터20', '50');

SELECT * FROM PRODUCT ORDER BY TO_NUMBER(TOT_CNT)DESC;

--27쪽 연습문제--

=====================================================

1. EMPLOYEE 테이블에서 직급이 과징인 직원을 검색하면?

=====================================================

SELECT * FROM EMPLOYEE WHERE JIKUP='과장';

==========================================================

2. EMPLOYEE 테이블에서 직급이 과장이 아닌 직원을 검색하면?

==========================================================

SELECT * FROM EMPLOYEE WHERE JIKUP != '과장';

SELECT * FROM EMPLOYEE WHERE JIKUP <> '과장';

==========================================================================

3. EMPLOYEE 테이블에서 부서번호가 10번 이고 직급이 과장인 직원을 검색하면?

==========================================================================

SELECT * FROM EMPLOYEE WHERE DEP_NO = 10 AND JIKUP='과장';

===============================================================

4. EMPLOYEE 테이블에서 직급이 과장 또는 부장인 직원을 검색하면?

===============================================================

SELECT * FROM EMPLOYEE WHERE JIKUP='과장' OR JIKUP='부장';

SELECT * FROM EMPLOYEE WHERE JIKUP IN ('부장','과장');

SELECT * FROM EMPLOYEE WHERE JIKUP=ANY('부장','과장');

=========================================================================

5. EMPLOYEE 테이블에서 10번,20번 부서 중에 직급이 과장인 직원을 검색하면?

=========================================================================

SELECT * FROM EMPLOYEE WHERE DEP_NO IN (10, 20) AND JIKUP = '과장';

SELECT * FROM EMPLOYEE WHERE (DEP_NO=ANY(10,20)) AND JIKUP = '과장';

=======================================================

6. CUSTOMER 테이블에서 담당직원이 없는 고객을 검색하면?

=======================================================

SELECT * FROM CUSTOMER WHERE EMP_NO IS NULL;

=======================================================

7. CUSTOMER 테이블에서 담당직원이 있는 고객을 검색하면?

=======================================================

SELECT * FROM CUSTOMER WHERE EMP_NO IS NOT NULL;

==================================================================

8. CUSTOMER 테이블에서 담당직원 번호가 9번이 아닌 고객을 검색하면?

==================================================================

SELECT * FROM CUSTOMER WHERE EMP_NO != 9 AND EMP_NO IS NOT NULL;

=====================================================

9. EMPLOYEE 테이블에서 연봉이 4000만원 이상인 직원을 검색하면?

=====================================================

SELECT * FROM EMPLOYEE WHERE SALARY>=4000;

==========================================================================

10. EMPLOYEE 테이블에서 연봉이 3000만원 ~ 4000만원 사이인 직원을 검색하면?

==========================================================================

SELECT * FROM EMPLOYEE WHERE SALARY>=3000 AND SALARY<=4000 ;

SELECT * FROM EMPLOYEE WHERE SALARY<=4000 AND SALARY>=3000;

SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 3000 AND 4000;

==============================================================================

11. EMPLOYEE 테이블에서 연봉이 5%로 인상할 경우에 3000 이상인 직원을 검색하면?

==============================================================================

SELECT * FROM EMPLOYEE WHERE (SALARY+SALARY*0.05)>=3000;

=================================================================

12. EMPLOYEE 테이블에서 입사일이 1995년도 이상인 직원을 검색하면?

=================================================================

SELECT * FROM EMPLOYEE WHERE TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))>=1995;

SELECT * FROM EMPLOYEE WHERE HIRE_DATE>=TO_DATE('1995-01-01','YYYY,MM,DD');

======================================================================

13. EMPLOYEE 테이블에서 입사일이 1990년~1999년 사이인 직원을 검색하면?

======================================================================

SELECT * FROM EMPLOYEE WHERE TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))>=1990 AND TO_NUMBER(TO_CHAR(HIRE_DATE,'YYYY'))<=1999;

SELECT * FROM EMPLOYEE WHERE HIRE_DATE>=TO_DATE('1990-01-01','YYYY,MM,DD') AND HIRE_DATE<=TO_DATE('1999-12-31','YYYY,MM,DD');

==============================================================================================================================

14. EMPLOYEE 테이블에서 부서번호가 10번 또는 30번인 직원 중에 연봉이 3000 미만이고 입사일이 '1996-01-01' 미만 직원을 검색하면?

==============================================================================================================================

SELECT * FROM EMPLOYEE WHERE DEP_NO IN (10, 30) AND SALARY<3000 AND HIRE_DATE<TO_DATE('1996-01-01','YYYY,MM,DD');

SELECT * FROM EMPLOYEE WHERE (DEP_NO=10 OR DEP_NO=30) AND SALARY<3000 AND HIRE_DATE<TO_DATE('1996-01-01','YYYY,MM,DD');

=====================================================================================================================

15. EMPLOYEE 테이블에서 부서번호가 10번 중에 연봉 1000만 미만 또는 20번인 직원 중에 연봉이 5000 이상 직원을 검색하면?

=====================================================================================================================

SELECT * FROM EMPLOYEE WHERE (DEP_NO = 10 AND SALARY < 1000) OR (DEP_NO = 20 AND SALARY >= 5000);

SELECT * FROM EMPLOYEE WHERE (DEP_NO=10 AND SALARY<1000) OR (DEP_NO=20 AND SALARY>=5000);

=====================================================

16. EMPLOYEE 테이블에서 성이 김씨인 직원을 검색하면?

=====================================================

SELECT * FROM EMPLOYEE WHERE EMP_NAME LIKE '김%';

=============================================================

17. EMPLOYEE 테이블에서 성이 김씨이고 3글자인 직원을 검색하면?

==============================================================

SELECT * FROM EMPLOYEE WHERE EMP_NAME LIKE '김__';

===================================================================

18. EMPLOYEE 테이블에서 이름 중간에만 김이 들어간 직원을 검색하면?

===================================================================

SELECT * FROM EMPLOYEE WHERE EMP_NAME LIKE '_%김%_';

SELECT * FROM EMPLOYEE WHERE EMP_NAME LIKE '_%궁%_';

SELECT * FROM EMPLOYEE WHERE EMP_NAME LIKE '_%미%_';

=============================================

19. EMPLOYEE 테이블에서 여자 직원을 검색하면?

=============================================

SELECT * FROM EMPLOYEE WHERE TO_NUMBER(SUBSTR(JUMIN_NUM,7,1)) = 2;

SELECT * FROM EMPLOYEE WHERE SUBSTR(JUMIN_NUM,7,1)=2;

SELECT * FROM EMPLOYEE WHERE JUMIN_NUM LIKE '______2%';

===============================================================

20. EMPLOYEE 테이블에서 60년대,70년대 출생자중 남자만 검색하면?

===============================================================

SELECT * FROM EMPLOYEE WHERE TO_NUMBER(SUBSTR(JUMIN_NUM,1,2))>=60 AND TO_NUMBER(SUBSTR(JUMIN_NUM,1,2))<=79 AND TO_NUMBER(SUBSTR(JUMIN_NUM,7,1)) = 1;

SELECT * FROM EMPLOYEE WHERE (TO_NUMBER(SUBSTR(JUMIN_NUM,1,2))>=60 AND TO_NUMBER(SUBSTR(JUMIN_NUM,1,2))<=79) AND TO_NUMBER(SUBSTR(JUMIN_NUM,7,1)) = 1;