1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 | -- 1. 연봉이 12000 이상되는 직원들의 LAST_NAME 및 연봉을 조회한다. SELECT LAST_NAME , SALARY FROM EMPLOYEES WHERE SALARY >= 12000 ; -- 2. 사원번호가 176 인 사람의 LAST_NAME 과 부서 번호를 조회한다. SELECT LAST_NAME , DEPARTMENT_ID FROM EMPLOYEES WHERE SALARY >= 12000 ; -- 3. 연봉이 5000 에서 12000의 범위 이외인 사람들의 LAST_NAME 및 연봉을 조회힌다. SELECT LAST_NAME , SALARY FROM EMPLOYEES WHERE SALARY < 5000 OR SALARY > 12000 ; -- 4. 1998/02/20 일부터 1998/05/01 사이에 고용된 사원들의 LAST_NAME 사번, 고용일자를 조회한다. -- 고용일자 순으로 정렬한다. SELECT LAST_NAME , EMPLOYEE_ID , HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE >= '1998/02/20' AND HIRE_DATE <= '1998/05/01' ; SELECT LAST_NAME , EMPLOYEE_ID , HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE BETWEEN TO_DATE('2002/02/20') AND TO_DATE('2005/05/01') ORDER BY HIRE_DATE ; -- 5. 20 번 및 50 번 부서에서 근무하는 모든 사원들의 LAST_NAME 및 부서 번호를 알파벳순으로 조회한다. SELECT LAST_NAME , DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IN ( 20, 50 ) ORDER BY LAST_NAME ASC ; -- 6. 20 번 및 50 번 부서에 근무하며, 연봉이 5000 ~ 12,000 사이인 사원들의 LAST_NAME 및 연봉을 조회한다. SELECT LAST_NAME , SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID IN ( 20, 50 ) AND SALARY >= 5000 AND SALARY <= 12000 ; -- 7. 1994년도에 고용된 모든 사람들의 LAST_NAME 및 고용일을 조회한다. SELECT LAST_NAME , HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE LIKE '1994%' ; -- 8-1. 매니저가 없는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다. SELECT LAST_NAME , JOB_ID , MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID is null OR MANAGER_ID = '' ; -- 8-2. 매니저가 있는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다. SELECT LAST_NAME , JOB_ID , MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID is not null OR MANAGER_ID != '' ; -- 9. 커미션을 버는 모든 사원들의 LAST_ANME, 연봉 및 커미션을 조회한다. -- 연봉 역순, 커미션 역순차로 정렬한다. SELECT LAST_NAME , SALARY , COMMISSION_PCT FROM EMPLOYEES WHERE NOT (COMMISSION_PCT is null) ORDER BY SALARY DESC, COMMISSION_PCT DESC ; -- 10. LAST_NAME 의 네번째 글자가 a 인 사원들의 LAST_NAME 을 조회한다. SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE '___a%' ; -- 11. LAST_NAME 에 a 및(OR) e 글자가 있는 사원들의 LAST_NAME 을 조회한다. SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE '%a%' OR LAST_NAME LIKE '%e%' ; -- 12. 연봉이 2,500, 3,500, 7000 이 아니며 직업이 SA_REP 이나 ST_CLERK 인 사람들을 조회한다. SELECT LAST_NAME , JOB_ID , SALARY FROM EMPLOYEES WHERE SALARY NOT IN ( 2500, 3500, 7000 ) AND JOB_ID IN ( 'SA_REP', 'ST_CLERK' ) ; -- 13-1. 직업이 AD_PRESS 인 사람은 A 등급을, ST_MAN 인 사람은 B 등급을, IT_PROG 인 사람은 C 등급을, -- SA_REP 인 사람은 D 등급을, ST_CLERK 인 사람은 E 등급을 기타는 0 을 부여하여 조회한다. SELECT EMPLOYEE_ID , FIRST_NAME , LAST_NAME , DECODE(JOB_ID , 'AD_PRESS', 'A' , 'ST_MAN', 'B' , 'IT_PROG', 'C' , 'SA_REP', 'D' , 'ST_CLERK', 'E' , '0') JOB_GRADE FROM EMPLOYEES ; -- 13-2 SELECT * FROM ( SELECT EMPLOYEE_ID , FIRST_NAME , LAST_NAME , DECODE(JOB_ID , 'AD_PRESS', 'A' , 'ST_MAN', 'B' , 'IT_PROG', 'C' , 'SA_REP', 'D' , 'ST_CLERK', 'E' , '0') JOB_GRADE FROM EMPLOYEES ) WHERE JOB_GRADE = 'B' ; -- 13-3 SELECT EMPLOYEE_ID , FIRST_NAME , LAST_NAME , CASE JOB_ID WHEN 'AD_PRESS' THEN 'A' WHEN 'ST_MAN' THEN 'B' WHEN 'IT_PROG' THEN 'C' WHEN 'SA_REP' THEN 'D' WHEN 'ST_CLERK' THEN 'E' ELSE '0' END GRADE FROM EMPLOYEES ; -- 13-4 SELECT EMPLOYEE_ID , FIRST_NAME , LAST_NAME , CASE WHEN JOB_ID = 'IT_PROG' THEN 'C' ELSE '0' END GRADE FROM EMPLOYEES ; -- 13-5 SELECT ROWNUM , EMPLOYEE_ID , FIRST_NAME , LAST_NAME , CASE (SELECT 98 FROM DUAL) WHEN 100 THEN (SELECT SYSDATE - 1 FROM DUAL) ELSE (SELECT SYSDATE - 1 FROM DUAL) END GRADE , CASE (SELECT 98 FROM DUAL) WHEN 100 THEN SYSDATE - 2 ELSE SYSDATE - 2 END GRADE , SYSDATE FROM EMPLOYEES ; -- 14. 모든 사원들의 LAST_NAME, 부서 이름 및 부서 번호을 조회한다. SELECT EMPLOYEE_ID , LAST_NAME , DEPARTMENT_NAME , D.DEPARTMENT_ID FROM EMPLOYEES E , DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID ; -- 15. 부서번호 30 내의 모든 직업들을 유일한 포맷으로 조회한다. 90 부서 또한 포함한다. SELECT DISTINCT JOB_ID , D.DEPARTMENT_ID FROM EMPLOYEES E , DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.DEPARTMENT_ID IN ( 30, 90 ) ORDER BY JOB_ID ; -- 16-1. 커미션을 버는 모든 사람들의 LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다. SELECT LAST_NAME , DEPARTMENT_NAME , L.LOCATION_ID , CITY FROM EMPLOYEES E , DEPARTMENTS D , LOCATIONS L WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID -- 빨간줄 AND D.LOCATION_ID = L.LOCATION_ID -- 보라줄 ; -- 16-2. 시애틀에 사는 사람 중 커미션을 버는 모든 사람들의 LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다. -- 좋지 못한 방법이다. SELECT LAST_NAME , DEPARTMENT_NAME , L.LOCATION_ID , CITY FROM EMPLOYEES E , DEPARTMENTS D , LOCATIONS L WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID -- 빨간줄 AND D.LOCATION_ID = L.LOCATION_ID -- 보라줄 AND L.CITY = "Seattle" ; -- 16-3. 시애틀에 사는 사람 중 커미션을 버는 모든 사람들의 LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다. -- 좋은 방법 - PK로 검색 SELECT LAST_NAME , DEPARTMENT_NAME , L.LOCATION_ID , CITY FROM EMPLOYEES E , DEPARTMENTS D , LOCATIONS L WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID -- 빨간줄 AND D.LOCATION_ID = L.LOCATION_ID -- 보라줄 AND L.LOCATION_ID = ( SELECT LOCATION_ID FROM LOCATIONS WHERE CITY = 'Seattle' ) ; -- 17. LAST_NAME 이 DAVIES 인 사람보다 후에 고용된 사원들의 LAST_NAME 및 HIRE_DATE 을 조회한다. SELECT LAST_NAME , HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE >= ( SELECT HIRE_DATE FROM EMPLOYEES WHERE LAST_NAME = 'Davies' ) ORDER BY HIRE_DATE ; -- 18. 자신의 매니저보다 먼저 고용된 사원들의 LAST_NAME 및 고용일을 조회한다. SELECT EMP.LAST_NAME , EMP.EMPLOYEE_ID , EMP.HIRE_DATE FROM EMPLOYEES EMP , EMPLOYEES MGR WHERE EMP.MANAGER_ID = MGR.EMPLOYEE_ID AND EMP.HIRE_DATE < MGR.HIRE_DATE ORDER BY EMP.LAST_NAME ; -- 19. 회사 전체의 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다. SELECT MAX(SALARY) , MIN(SALARY) , SUM(SALARY) , ROUND (AVG(SALARY)) FROM EMPLOYEES ; -- 20. 각 JOB_ID 별, 최대 연봉, 최소 연봉, 연봉 총 합 및 평균 연봉을 자연수로 포맷하여 조회한다. SELECT JOB_ID , MAX(SALARY) MAX , MIN(SALARY) MIN , SUM(SALARY) SUM , ROUND (AVG(SALARY)) AVG FROM EMPLOYEES GROUP BY JOB_ID ORDER BY JOB_ID ; -- 21. 동일한 직업을 가진 사원들의 총 수를 조회한다. SELECT JOB_ID , COUNT(EMPLOYEE_ID) FROM EMPLOYEES GROUP BY JOB_ID ORDER BY JOB_ID ; -- 22-1. 매니저로 근무하는 사원들의 총 수를 조회한다. SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES WHERE EMPLOYEE_ID IN ( SELECT DISTINCT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL ) ; -- 22-2. 매니저로 근무하는 사원들의 총 수를 조회한다. SELECT COUNT(DISTINCT MANAGER_ID) FROM EMPLOYEES ; -- 23. 사내의 최대 연봉 및 최소 연봉의 차이를 조회한다. SELECT MAX(SALARY) - MIN(SALARY) FROM EMPLOYEES ; -- 24. 매니저의 사번 및 그 매니저 밑 사원들 중 최소 연봉을 받는 사원의 연봉을 조회한다. -- 매니저가 없는 사람들은 제외한다. -- 최소 연봉이 6000 미만인 경우는 제외한다. -- 연봉 기준 역순으로 조회한다. SELECT MANAGER_ID , MIN(SALARY) FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL GROUP BY MANAGER_ID HAVING MIN(SALARY) >= 6000 ORDER BY MIN(SALARY) DESC ; -- 25. 부서 명, 위치 ID, 각 부서 별 사원 총 수, 각 부서 별 평균 연봉을 조회한다. -- 평균 연봉은 소수점 2 자리까지만 표현한다. SELECT DEPARTMENT_NAME , LOCATION_ID , COUNT(EMPLOYEE_ID) , ROUND(AVG(SALARY), 2) AVG_SALARY FROM EMPLOYEES E , DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID GROUP BY DEPARTMENT_NAME , LOCATION_ID ORDER BY LOCATION_ID ; -- 26-1. 총 사원 수 및 1995, 1996, 1997, 1998 년도 별 고용된 사원들의 총 수를 다음과 같이 조회한다. SELECT DISTINCT( SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES ) AS CountAll , ( SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES WHERE HIRE_DATE LIKE '2001%' ) AS Count2001 , ( SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES WHERE HIRE_DATE LIKE '2003%' ) AS Count2003 , ( SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES WHERE HIRE_DATE LIKE '2005%' ) AS Count2005 , ( SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES WHERE HIRE_DATE LIKE '2006%' ) AS Count2006 FROM EMPLOYEES ; -- 27-1. 각 부서별 각 직업 별 연봉 총 합 및 각 부서별 연봉 총 합을 조회한다. SELECT DEPARTMENT_ID , JOB_ID , SUM(SALARY) , ( SELECT SUM(SALARY) FROM EMPLOYEES E1 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID ) SumAll FROM EMPLOYEES E2 GROUP BY DEPARTMENT_ID , JOB_ID ORDER BY DEPARTMENT_ID , JOB_ID ; -- 27-2. 다음과 같은 포멧으로 각 부서별 각 직업 별 연봉 총 합 및 -- 각 부서별 연봉 총 합을 조회한다. SELECT DISTINCT JOB_ID , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 10 AND JOB_ID = E.JOB_ID ) AS "DEPT10" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 20 AND JOB_ID = E.JOB_ID ) AS "DEPT20" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 30 AND JOB_ID = E.JOB_ID ) AS "DEPT30" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 40 AND JOB_ID = E.JOB_ID ) AS "DEPT40" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 50 AND JOB_ID = E.JOB_ID ) AS "DEPT50" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 60 AND JOB_ID = E.JOB_ID ) AS "DEPT60" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 70 AND JOB_ID = E.JOB_ID ) AS "DEPT70" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 80 AND JOB_ID = E.JOB_ID ) AS "DEPT80" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 90 AND JOB_ID = E.JOB_ID ) AS "DEPT90" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 100 AND JOB_ID = E.JOB_ID ) AS "DEPT100" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE DEPARTMENT_ID = 110 AND JOB_ID = E.JOB_ID ) AS "DEPT110" , ( SELECT NVL ( SUM(SALARY), 0 ) FROM EMPLOYEES WHERE JOB_ID = E.JOB_ID ) AS "TOTAL" FROM EMPLOYEES E GROUP BY JOB_ID ; -- 27-3. 다음과 같은 포멧으로 각 부서별 각 직업 별 연봉 총 합 및 -- 각 부서별 연봉 총 합을 조회한다. SELECT JOB_ID , NVL ( SUM(DECODE (DEPARTMENT_ID, 20, SALARY)), 0 ) "DEPT 20" , NVL ( SUM(DECODE (DEPARTMENT_ID, 50, SALARY)), 0 ) "DEPT 50" , NVL ( SUM(DECODE (DEPARTMENT_ID, 80, SALARY)), 0 ) "DEPT 80" , NVL ( SUM(DECODE (DEPARTMENT_ID, 90, SALARY)), 0 ) "DEPT 90" , NVL ( SUM(SALARY), 0 ) "TOTAL" FROM EMPLOYEES GROUP BY JOB_ID; -- 28. LAST_NAME 이 Zlotkey 와 동일한 부서에 근무하는 모든 사원들의 사번 및 고용날짜를 조회한다. -- 결과값에서 Zlotkey 는 제외한다. SELECT EMPLOYEE_ID , HIRE_DATE FROM EMPLOYEES WHERE DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Zlotkey' ) AND LAST_NAME != 'Zlotkey' ; -- 29. 회사 전체 평균 연봉보다 더 받는 사원들의 사번 및 LAST_NAME 을 조회한다. SELECT EMPLOYEE_ID , LAST_NAME FROM EMPLOYEES WHERE SALARY > ( SELECT AVG(SALARY) FROM EMPLOYEES ) ; -- 30-1. LAST_NAME 에 u 가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 LAST_NAME 을 조회한다. SELECT EMPLOYEE_ID , LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME LIKE '%u%' ) ; -- 30-2. (VIEW 이용) LAST_NAME 에 u 가 포함되는 사원들과 동일 부서에 근무하는 사원들의 사번 및 LAST_NAME 을 조회한다. SELECT DISTINCT E.EMPLOYEE_ID , LAST_NAME FROM ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME LIKE '%u%' ) U_NAME , EMPLOYEES E WHERE U_NAME.DEPARTMENT_ID = E.DEPARTMENT_ID ; -- 31-1. 위치 ID 가 1700 인 부서에서 근무하는 사원들의 LAST_NAME, 부서 번호 및 JOB_ID 를 조회한다. SELECT LAST_NAME , D.DEPARTMENT_ID , JOB_ID , D.LOCATION_ID FROM EMPLOYEES E , DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = 1700 ORDER BY D.DEPARTMENT_ID , JOB_ID , LAST_NAME ; -- 31-2. (VIEW 이용)위치 ID 가 1700 인 부서에서 근무하는 사원들의 LAST_NAME, 부서 번호 및 JOB_ID 를 조회한다. SELECT LAST_NAME , DEPARTMENT_ID , JOB_ID FROM ( SELECT EMPLOYEE_ID FROM EMPLOYEES E , DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = 1700 ) EMP1700 , EMPLOYEES E WHERE EMP1700.EMPLOYEE_ID = E.EMPLOYEE_ID ; -- 32. King 을 매니저로 두고 있는 모든 사원들의 LAST_NAME 및 연봉을 조회한다. SELECT LAST_NAME , SALARY , MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IN ( SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE LAST_NAME = 'King' ) ; -- 33. EXECUTIVE 부서에 근무하는 모든 사원들의 부서 번호, LAST_NAME, JOB_ID 를 조회한다. SELECT D.DEPARTMENT_ID , DEPARTMENT_NAME , JOB_ID , LAST_NAME FROM EMPLOYEES E , DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND DEPARTMENT_NAME = 'Executive' ORDER BY JOB_ID , LAST_NAME ; -- 34-1. 회사 전체 평균 연봉 보다 더 버는 사원들 중 LAST_NAME 에 u 가 있는 사원들이 -- 근무하는 부서에서 근무하는 사원들의 사번, LAST_NAME 및 연봉을 조회한다. SELECT DISTINCT EMPLOYEE_ID , LAST_NAME , SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME LIKE '%u%' AND SALARY >= ( SELECT AVG(SALARY) FROM EMPLOYEES ) ) ; -- 34-2. (VIEW 이용)회사 전체 평균 연봉 보다 더 버는 사원들 중 LAST_NAME 에 u 가 있는 사원들이 -- 근무하는 부서에서 근무하는 사원들의 사번, LAST_NAME 및 연봉을 조회한다. SELECT DISTINCT EMPLOYEE_ID , LAST_NAME , SALARY FROM ( /* 그 사람들의 부서와 원래 EMPLOYEES 테이블과 조인 */ SELECT DEPARTMENT_ID FROM ( /* 평균 연봉보다 많이 버는 사원들 */ SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE SALARY > ( SELECT AVG(SALARY) FROM EMPLOYEES ) ) EMP2 , EMPLOYEES E WHERE EMP2.EMPLOYEE_ID = E.EMPLOYEE_ID AND E.LAST_NAME LIKE '%u%' ) DEPT , EMPLOYEES E WHERE DEPT.DEPARTMENT_ID = E.DEPARTMENT_ID ; -- 35-1. ST_CLERK 인 직업 ID 를 가진 사원이 없는 부서 ID 를 조회한다. -- NULL 값은 제외한다. (NOT EXISTS 사용) SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES E WHERE NOT EXISTS ( /* 부서 하나씩 검사하며 ST_CLERK라는 직업이 있으면 '1' 반환 없으면 NULL 반환*/ SELECT '' FROM EMPLOYEES E1 WHERE JOB_ID = 'ST_CLERK' AND E1.DEPARTMENT_ID = E.DEPARTMENT_ID ) AND DEPARTMENT_ID IS NOT NULL ORDER BY DEPARTMENT_ID ; -- 35-2. ST_CLERK 인 직업 ID 를 가진 사원이 있는 부서 ID 를 조회한다. -- NULL 값은 제외한다. (NOT EXISTS 사용) SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES E WHERE NOT EXISTS ( SELECT '1' FROM EMPLOYEES E2 WHERE E.EMPLOYEE_ID = E2.EMPLOYEE_ID AND E2.JOB_ID != 'ST_CLERK' ) ORDER BY DEPARTMENT_ID ; -- 36-1. 위치한 부서가 없는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용) SELECT COUNTRY_ID , COUNTRY_NAME FROM COUNTRIES C WHERE NOT EXISTS ( /* */ SELECT '1' FROM LOCATIONS L , DEPARTMENTS D WHERE C.COUNTRY_ID = L.COUNTRY_ID AND L.LOCATION_ID IN (D.LOCATION_ID) ) ORDER BY COUNTRY_ID ; -- 36-2. 위치한 부서가 없는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용) SELECT COUNTRY_ID , COUNTRY_NAME FROM COUNTRIES C WHERE NOT EXISTS ( SELECT '1' FROM LOCATIONS L , DEPARTMENTS D WHERE L.COUNTRY_ID = C.COUNTRY_ID AND L.LOCATION_ID NOT IN (D.LOCATION_ID) ) ORDER BY COUNTRY_ID ; -- 36-3. 위치한 부서가 있는 국가 ID 및 국가 이름을 조회힌다.(NOT EXISTS 사용) SELECT COUNTRY_ID , COUNTRY_NAME FROM COUNTRIES C WHERE EXISTS ( SELECT '1' FROM LOCATIONS L2 , DEPARTMENTS D2 WHERE C.COUNTRY_ID = L2.COUNTRY_ID AND L2.LOCATION_ID = D2.LOCATION_ID ) ; -- 37. 기존의 직업을 여전히 가지고 있는 사원들의 사번 및 JOB_ID 를 조회한다. SELECT E.EMPLOYEE_ID , E.JOB_ID FROM EMPLOYEES E WHERE E.EMPLOYEE_ID NOT IN ( SELECT EMPLOYEE_ID FROM JOB_HISTORY J WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID ) ; -- 41. 커미션을 버는 사원들의 부서와 연봉이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다. SELECT LAST_NAME , DEPARTMENT_ID , SALARY , COMMISSION_PCT FROM EMPLOYEES WHERE (DEPARTMENT_ID, SALARY) IN ( SELECT DEPARTMENT_ID , SALARY FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL ) ; -- 42. 위치 ID 가 1700 인 사원들의 연봉과 커미션이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다. SELECT LAST_NAME , DEPARTMENT_NAME , SALARY FROM EMPLOYEES E , DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (SALARY, NVL(COMMISSION_PCT,0)) IN ( SELECT SALARY , NVL(COMMISSION_PCT, 0) FROM EMPLOYEES E2 , DEPARTMENTS D2 WHERE E2.DEPARTMENT_ID = D2.DEPARTMENT_ID AND LOCATION_ID = 1700 AND E2.EMPLOYEE_ID != E.EMPLOYEE_ID ) ; -- 42-2. (VIEW)위치 ID 가 1700 인 사원들의 연봉과 커미션이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다. SELECT LAST_NAME , E2.DEPARTMENT_ID , E2.SALARY FROM ( /* 연봉, COMMISSION */ SELECT SALARY , NVL(COMMISSION_PCT,0) /* LOCATION 1700 */ FROM ( SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE LOACTION_ID = 1700 ) D3 , EMPLOYEES E2 WHERE E2.DEPARTMENT_ID = D3.DEPARTMENT_ID ) SC , EMPLOYEES E ; -- 42-2. (VIEW 강사님)위치 ID 가 1700 인 사원들의 연봉과 커미션이 동일한 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다. SELECT DISTINCT LAST_NAME , DEPARTMENT_ID , E.SALARY FROM ( /* 연봉, COMMISSION */ SELECT SALARY , NVL(COMMISSION_PCT,0) COMMISSION_PCT /* LOCATION 1700 */ FROM EMPLOYEES E , DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = 1700 ) SAL1700 , EMPLOYEES E WHERE SAL1700.SALARY = E.SALARY AND SAL1700.COMMISSION_PCT = NVL(E.COMMISSION_PCT, 0) ; -- 43. LAST_NAME 이 'Kochhar' 인 사원과 동일한 연봉 및 커미션을 버는 사원들의 LAST_NAME, 부서 번호 및 연봉을 조회한다. SELECT LAST_NAME , DEPARTMENT_ID , SALARY , COMMISSION_PCT FROM EMPLOYEES WHERE (SALARY, NVL(COMMISSION_PCT,0)) IN ( SELECT SALARY , NVL(COMMISSION_PCT, 0) FROM EMPLOYEES E2 WHERE LAST_NAME = 'Kochhar' ) AND LAST_NAME != 'Kochhar' ; -- 44-1. 직업 ID 가 SA_MAN 인 사원들이 받을 수 있는 최대 연봉 보다 높게 받는 사원들의 LAST_NAME, JOB_ID 및 연봉을 조회한다. SELECT LAST_NAME , JOB_ID , SALARY FROM EMPLOYEES WHERE SALARY > ( SELECT MAX_SALARY FROM JOBS WHERE JOB_ID = 'SA_MAN' ) ; -- 44-2. 직업 ID 가 SA_MAN 인 사원들의 최대 연봉 보다 높게 받는 사원들의 LAST_NAME, JOB_ID 및 연봉을 조회한다. SELECT LAST_NAME , JOB_ID , SALARY FROM EMPLOYEES WHERE SALARY > ( SELECT MAX(SALARY) FROM EMPLOYEES WHERE JOB_ID = 'SA_MAN' ) ; -- 45. 도시 이름이 T 로 시작하는 지역에 사는 사원들의 사번, LAST_NAME 및 부서 번호를 조회한다. SELECT EMPLOYEE_ID , LAST_NAME , D.DEPARTMENT_ID , CITY FROM EMPLOYEES E , DEPARTMENTS D , LOCATIONS L WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND CITY LIKE 'T%' ; -- 46. 각 부서별 평균 연봉보다 더 받는 동일부서 근무사원들의 -- LAST_NAME, 연봉, 부서번호 및 해당 부서의 평균 연봉을 조회한다. -- 결과는 부서별 연봉을 기준으로 정렬한다. SELECT E.LAST_NAME , E.SALARY , E.DEPARTMENT_ID , V.DEP_AVG FROM EMPLOYEES E ,( SELECT DEPARTMENT_ID , ROUND( AVG(SALARY), 0 ) DEP_AVG FROM EMPLOYEES GROUP BY DEPARTMENT_ID ) V WHERE E.DEPARTMENT_ID = V.DEPARTMENT_ID AND E.SALARY > V.DEP_AVG ORDER BY V.DEP_AVG ; SELECT LAST_NAME , SALARY , DEPARTMENT_ID -- 47. NOT EXISTS 연산자를 사용하여 매니저가 아닌 사원 이름을 조회한다. SELECT FIRST_NAME , EMPLOYEE_ID , MANAGER_ID FROM EMPLOYEES E WHERE NOT EXISTS ( SELECT '1' FROM EMPLOYEES E2 WHERE E.EMPLOYEE_ID = E2.MANAGER_ID ) ORDER BY EMPLOYEE_ID ; -- 48. 소속부서의 평균 연봉보다 적게 버는 사원들의 last_name 을 조회한다. SELECT LAST_NAME , SALARY , E.DEPARTMENT_ID , V.AVG_SALARY FROM EMPLOYEES E ,( SELECT DEPARTMENT_ID , ROUND(AVG(SALARY), 0) AVG_SALARY FROM EMPLOYEES GROUP BY DEPARTMENT_ID ) V WHERE E.DEPARTMENT_ID = V.DEPARTMENT_ID AND E.SALARY < V.AVG_SALARY ORDER BY V.AVG_SALARY , E.SALARY ; -- 49. 각 사원 별 소속부서에서 자신보다 늦게 고용되었으나 보다 많은 연봉을 받는 사원이 존재하는 모든 사원들의 last_name 을 조회한다. /* LASTNAME 출력 */ SELECT DISTINCT E.FIRST_NAME || E.LAST_NAME , E.DEPARTMENT_ID FROM EMPLOYEES E , EMPLOYEES E2 WHERE E.DEPARTMENT_ID = E2.DEPARTMENT_ID AND E.HIRE_DATE < E2.HIRE_DATE AND E.SALARY < E2.SALARY ; -- LPAD, RPAD SELECT LPAD('111', 6, '0') , LPAD('1', 6, '0') , RPAD('111', 6, '0') , RPAD('1', 6, '0') FROM DUAL ; -- || 컬럼들을 붙여서 출력할 때 SELECT LPAD('111', 6, '0') || '--++PLUS' || LPAD('1', 6, '0') || RPAD('111', 6, '0') || RPAD('1', 6, '0') CONCATENATION FROM DUAL ; | cs |
'Database > SQL' 카테고리의 다른 글
| SQL 50문제 실습하기 (0) | 2018.08.17 |
|---|---|
| 데이터베이스 설계 (0) | 2018.08.07 |
| SQL 서브쿼리 (0) | 2018.08.03 |
