3-2) 글로 적는 웹개발 공부 - 오라클 디비(Oracle DB)

---

■ 다음과 같이 출력해보세요.

 

지역번호	우편번호	주소	도시	국가코드
1200	1689	2017 Shinjuku-ju	Tokyo	JP
1300	6823	9450 Kamiya-cho	Hiroshima	JP
1400	26192	2014 Jabberwocky Rd	Shothlake	US
1500	99236	2011 Interiors Blvd	South San Francisco	US
1600	50090	2007 Zagora St	South Brunswick	US
1700	98199	2004 Charade Rd	Seattle	US

SELECT  location_id 지역번호, postal_code 우편번호, street_address 주소,
        city 도시, country_id 국가코드
FROM locations
WHERE location_id BETWEEN 1200 AND 1700

 

>> 문제를 착각해 substr() 함수를 이용해 문자열 중 일부분을 잘라 출력하는 줄 알았다.
검색을 하다가 오라클(Orcale) 정규식(REGEXP) 이라는걸 처음 알게됐다.
나중에 오라클 정규식에 대해서 정리해보자.

 

■ employees 테이블에서 50번 부서에 근무하는 사람들 중 이름이 'Matthew'라는 매니져를 상사로 두고 있고, 월급이 3000달러 이상 되는 사람들을 출력해보세요. 단 Matthuw는 제외(출력필드 : 사번, 이름, 성, 월급, 부서번호, 직업 아이디)

 

SELECT employee_id, first_name, last_name, salary, department_id, job_id
FROM employees
WHERE department_id = 50
AND salary >= 3000
AND employee_id NOT IN(120)

>> 특정 컬럼을 제외하고 출력하고 싶을 때 쓰는 NOT IN 함수를 쓰면 된다. 

비슷한 함수로  EXISTS 함수가 있는데 나중에 참고해서 공부해보자.

 

■ 다음과 같이 출력하세요.

 

국가아이디	국가명	지역아이디
AR	Argentina	2
AU	Australia	3
CA	Canada	2

SELECT country_id 국가아이디, country_name 국가명, region_id 지역아이디
FROM countries
WHERE country_id IN('AR', 'AU', 'CA')

 

■ 지점번호가 1000~1500까지 지점의 도시와 지역명을 출력하세요. (locations, countries, regions 테이블 이용)

 

지점번호	도시명	지역명
1000	Roma	Europe
1100	Venice	Europe
1200	Tokyo	Asia
1300	Hiroshima	Asia
1400	Southlake	Americas
1500	South San Fancisco	Americas

 

SELECT l.location_id 지점번호, l.city 도시명, r.region_id 지역명
FROM locations l, contries c, regions r
WHERE l.location_id = c.location_id
AND c.region_id = r.region_id
AND location_id BETWEEN 1000 AND 1500
ORDER BY location_id asc

 

■ employees , departments 테이블을 이용해 2005년 이전 고용된 사람들 중에서 평균연봉 이하를 받는 직원들의 사번, 이름, 성, 고용일, 부서번호, 부서명을 출력하세요.

 

사번	이름	성	고용일	부서번호	부서명
200	Jennifer	Whalen	09/17/2003	10	Administration
115	Alexander	Khoo	05/18/2003	30	Purchasing
184	Nandita	Sarchand	01/27/2004	50	Shipping
192	Sarah	Bell	02/04/2004	50	Shipping
137	Renske	Ladwig	07/14/2003	50	Shipping
133	Jason	Mallin	06/14/2004	50	Shipping
141	Trenna	Rajs	10/17/2003	50	Shipping

SELECT employee_id 사번, first_name 이름, last_name 성, TO_CHAR(hire_date,'YYYY/MM/DD') as 고용일, e.department 부서번호, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND hire_date < '2005/01/01'
AND (SELECT avg(salary*12) 
     FROM employees) >= salary * 12

서브쿼리(SUBQUERY)

- 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 의미한다. - 서브쿼리는 메인 쿼리가 실행되기 전에 먼저 실행되며 서브쿼리에서는 메인 쿼리의 모든 컬럼을 참조할 수 있지만 메인  쿼리에서는 서브쿼리의 컬럼을 참조할 수 없다.

■ 각 도시별 인원수와 평균급여를 구하세요.

 

도시명	평균급여	사람수
South San Francisco	$3,476	45
Southlake	$5,760	5
London	$6,500	1
Seattle	$8,845	18
Oxford	$8,956	34
Toronto	$9,500	2
Munich	$10,000	1

SELECT city 도시명, TO_CHAR(ROUND(AVG(salary),1),'$999,999,999') 평균급여, COUNT(employee_id) 사람수
FROM employees e, departments d, locations s
WHERE e.department = d.department
AND d.location_id = s.location_id
GROUP BY city
ORDER BY 2

ROUNDE 함수

- ROUND 함수는 지정한 자리 수 이하에서 반올림한 결과를 구해주는 함수이다. - ROUND 인자값은 숫자, 반올림자리수 2개로 되어 있다.

TO_CHAR 함수

- DATE 형, NUMBER 형을 문자 타입으로 변환하는 함수이다. - 여기서 Format 을 $ 로 사용, $ 통화로 표시한다는 뜻이다.

 

■ 다음과 같이 고용년도에 따른 인력현황을 출력하세요.

2001년	2002년	2003년	2004년	2005년	2006년	2007년	2008년
1	7	6	10	29	24	19	11

SELECT SUM(DECODE(TO_CHAR(hire_date,'YYYY'),'2001',1,0)) "2001년",
       SUM(DECODE(TO_CHAR(hire_date,'YYYY'),'2002',1,0)) "2002년",
       SUM(DECODE(TO_CHAR(hire_date,'YYYY'),'2003',1,0)) "2003년",
       SUM(DECODE(TO_CHAR(hire_date,'YYYY'),'2004',1,0)) "2004년",
       SUM(DECODE(TO_CHAR(hire_date,'YYYY'),'2005',1,0)) "2005년",
       SUM(DECODE(TO_CHAR(hire_date,'YYYY'),'2006',1,0)) "2006년",
       SUM(DECODE(TO_CHAR(hire_date,'YYYY'),'2007',1,0)) "2007년",
       SUM(DECODE(TO_CHAR(hire_date,'YYYY'),'2008',1,0)) "2008년"
FROM employees

DECODE 함수

- 오라클 내장함수로 자바에서 if 문과 같은 기능을 한다. - DECODE(기준값, 조건1, 결과1, 결과2) - 결과2를 생략하면 자동으로 NULL 값으로 변환된다.

---