[1일차 과제]select절과 쿼리 실행 순서

SQL 1일차 실습 문제 (SELECT)
                                      
이름 : 조은수


EX1. 급여가 $10000를 넘는 사원의 이름 및 급여를 표시하는 질의를 작성하시오.
SELECT LAST_NAME, SALARY
FROM EMP
WHERE SALARY>10000;

King 24000
Kochhar 17000
De Haan 17000
Greenberg 12008
Raphaely 11000
Russell 14000
Partners 13500
Errazuriz 12000
Cambrault 11000
Zlotkey 10500
Vishney 10500
Ozer 11500
Abel 11000
Hartstein 13000
Higgins 12008

EX2. 사원의 번호가 100인 사원의 이름 및 부서 번호를 표시하는 질의를 작성하시오.
SELECT LAST_NAME, DEPARTMENT_ID
FROM EMP
WHERE EMPLOYEE_ID=100;

King 90

EX3. 급여가 $15000 이상 ~ $22000 이하의 범위에 속하지 않는 
모든 사원의 이름 및 급여를 표시하는 질의를 작성하시오.
SELECT LAST_NAME, SALARY
FROM EMP
WHERE SALARY BETWEEN 15000 AND 22000;

Kochhar 17000
De Haan 17000


EX4. 2004년 2월 2일 ~ 2004년 5월 1일에 입사한 
사원 이름, 직위 및 시작일을 표시한다. 
시작일을 기준으로해서 오름차순 정렬한다. 
SELECT LAST_NAME, JOB_ID, HIRE_DATE
FROM EMP
WHERE HIRE_DATE BETWEEN '2004-02-02' AND '2004-05-01'
ORDER BY HIRE_DATE;

Bell SH_CLERK 04/02/04
Hartstein MK_MAN 04/02/17
Sully SA_REP 04/03/04                       


EX5. 부서 10및 30에 속하는 모든 사원의 이름 및 부서 번호를 
이름을 기준으로 해서 영문자순으로 표시한다.
SELECT LAST_NAME, DEPARTMENT_ID
FROM EMP
WHERE DEPARTMENT_ID = 10 OR DEPARTMENT_ID = 30
ORDER BY LAST_NAME;

Baida 30
Colmenares 30
Himuro 30
Khoo 30
Raphaely 30
Tobias 30
Whalen 10

  

EX6. 부서 10 또는 30에 속하는 사원 중 급여가 $5000를 넘는 
      사원의 이름 및 급여를 표시하도록 하며, 
      EMPLOYEE 및 MONTHLY SALARY 으로 이름을 바꾸시오.

SELECT  LAST_NAME AS EMPLOYEE, SALARY AS 'MONTHLY SALARY'
FROM EMP
WHERE DEPARTMENT_ID IN(10, 30) AND SALARY>5000;

Raphaely 11000

EX7. 2004년에 입사한 모든 사원의 이름 및 입사일을 표;시한다.
SELECT LAST_NAME, HIRE_DATE
FROM EMP
WHERE HIRE_DATE BETWEEN '2004-01-01' AND '2004-12-31';

=============================
SELECT LAST_NAME, HIRE_DATE
FROM EMP
WHERER EXTRACT (YEAR FROM HIRE_DATE)='2004';

-------------------------------------
SELECT LAST_NAME, HIRE_DATE
FROM EMP
WHERE TO-CHAR(HIRE_DATE,'YYYY')='2004';
=============================

Weiss 04/07/18
Mallin 04/06/14
Russell 04/10/01
King 04/01/30
Sully 04/03/04
McEwen 04/08/01
Abel 04/05/11
Sarchand 04/01/27
Bell 04/02/04
Hartstein 04/02/17

EX8. 관리자가 없는 모든 사원의 이름 및 직위를 표시한다.
SELECT LAST_NAME, JOB_ID
FROM EMP
WHERE MANAGER_ID IS NULL;

King AD_PRES

EX9. 커미션을 받는 모든 사원의 이름, 급여 및 커미션을 
급여 및 커미션을 기준으로 해서 내림차순 정렬하시오.
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY SALARY DESC, COMMISSION_PCT DESC;

Russell 14000 0.4
Partners 13500 0.3
Errazuriz 12000 0.3
Ozer 11500 0.25
Cambrault 11000 0.3
Abel 11000 0.3
Vishney 10500 0.25
Zlotkey 10500 0.2
King 10000 0.35
Tucker 10000 0.3
Bloom 10000 0.2
Fox 9600 0.2
Sully 9500 0.35
Bernstein 9500 0.25
Greene 9500 0.15
McEwen 9000 0.35
Hall 9000 0.25
Hutton 8800 0.25
Taylor 8600 0.2
Livingston 8400 0.2
Smith 8000 0.3
Olsen 8000 0.2
Doran 7500 0.3
Cambrault 7500 0.2
Smith 7400 0.15
Bates 7300 0.15
Marvins 7200 0.1
Sewall 7000 0.25
Tuvault 7000 0.15
Grant 7000 0.15
Lee 6800 0.1
Ande 6400 0.1
Banda 6200 0.1
Johnson 6200 0.1
Kumar 6100 0.1

EX10. 이름의 세 번째 문자가 A인 모든 사원의 이름을 표시한다.
SELECT LAST_NAME
FROM EMP
WHERE LAST_NAME LIKE '__a%';

Grant
Grant
Whalen

EX11. 이름에 s가 두 번 들어가며 부서 50 또는 80에 속한 모든 사원의 이름을 표시한다.
SELECT LAST_NAME
FROM EMP
WHERE LAST_NAME LIKE '%ss%' AND DEPARTMENT_ID IN (50,80);

Weiss
Bissot
Russell

===========================================
SELECT LAST_NAME
FROM EMP
WHERE LAST_NAME LIKE '%s%s%' AND DEPARTMENT_ID IN (50,80);

Weiss
Bissot
Russell


EX12. 업무가 CLERK 면서 급여가 $1000, 3000, $5000가 아닌 
모든 사원의 이름, 직무 및 급여를 표시한다.
SELECT LAST_NAME, JOB_ID, SALARY
FROM EMP
WHERE JOB_ID LIKE '%CLERK' AND SALARY NOT IN (1000, 3000, 5000);

Khoo PU_CLERK 3100
Baida PU_CLERK 2900
Tobias PU_CLERK 2800
Himuro PU_CLERK 2600
Colmenares PU_CLERK 2500
Nayer ST_CLERK 3200
Mikkilineni ST_CLERK 2700
Landry ST_CLERK 2400
Markle ST_CLERK 2200
Bissot ST_CLERK 3300
Atkinson ST_CLERK 2800
Marlow ST_CLERK 2500
Olson ST_CLERK 2100
Mallin ST_CLERK 3300
Rogers ST_CLERK 2900
Gee ST_CLERK 2400
Philtanker ST_CLERK 2200
Ladwig ST_CLERK 3600
Stiles ST_CLERK 3200
Seo ST_CLERK 2700
Patel ST_CLERK 2500
Rajs ST_CLERK 3500
Davies ST_CLERK 3100
Matos ST_CLERK 2600
Vargas ST_CLERK 2500
Taylor SH_CLERK 3200
Fleaur SH_CLERK 3100
Sullivan SH_CLERK 2500
Geoni SH_CLERK 2800
Sarchand SH_CLERK 4200
Bull SH_CLERK 4100
Dellinger SH_CLERK 3400
Chung SH_CLERK 3800
Dilly SH_CLERK 3600
Gates SH_CLERK 2900
Perkins SH_CLERK 2500
Bell SH_CLERK 4000
Everett SH_CLERK 3900
McCain SH_CLERK 3200
Jones SH_CLERK 2800
Walsh SH_CLERK 3100
OConnell SH_CLERK 2600
Grant SH_CLERK 2600
                               
EX13. 커미션과 급여의 합계가 10% 인상된 급여보다 많은 
모든 사원의 이름, 급여 및 커미션을 표시하도록 한다.
SELECT LAST_NAME, SALARY, COMMISION_PCT
FROM EMP
WHERE (COMMISSION_PCT*SALARY)+SALARY > SALARY+(SALARY*0.1);

Russell 14000 0.4
Partners 13500 0.3
Errazuriz 12000 0.3
Cambrault 11000 0.3
Zlotkey 10500 0.2
Tucker 10000 0.3
Bernstein 9500 0.25
Hall 9000 0.25
Olsen 8000 0.2
Cambrault 7500 0.2
Tuvault 7000 0.15
King 10000 0.35
Sully 9500 0.35
McEwen 9000 0.35
Smith 8000 0.3
Doran 7500 0.3
Sewall 7000 0.25
Vishney 10500 0.25
Greene 9500 0.15
Ozer 11500 0.25
Bloom 10000 0.2
Fox 9600 0.2
Smith 7400 0.15
Bates 7300 0.15
Abel 11000 0.3
Hutton 8800 0.25
Taylor 8600 0.2
Livingston 8400 0.2
Grant 7000 0.15

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

쿼리 실행 순서

1 - SELECT ...
2 - FROM ...
3 - WHERE ...
4 - GROUP BY ...
5 - HAVING ...
6 - ORDER BY ...;

2 -> 3 -> 4 -> 5 -> 1 -> 6