DB 03/07
2) SELECT : 데이터 조회하기
--사원 테이블에서 이름의 세번째 글자가 a인 사원의 사번 이름 출력
SELECT employee_id, first_name
FROM employees
WHERE first_name LIKE '__a%';--사원테이블에서 입사년도가 2006년인 사원들의 사번,이름,입사일을 출력
SELECT employee_id, first_name, hire_date
FROM employees
WHERE hire_date LIKE '%2006';--이름이 H로 시작하면 서 6글자 이상인 사원의 사번,이름을 출력
SELECT employee_id, first_name
FROM employees
WHERE first_name LIKE 'H_____%';
--이름에 소문자 o가 포함되어 있으며 a로 끝나는 사원들의 사번,이름을 출력
SELECT employee_id, first_name
FROM employees
WHERE first_name LIKE '%o%a';--전화번호가 6으로시작해서 중간에 2가 포함되어 있고 9로 끝나는 사원들의 사번,이름,전화번호를 출력
SELECT employee_id, first_name,phone_number
FROM employees
WHERE phone_number LIKE '6%2%9';NOT연산자 : 해당 조건을 제외한 자료를 조회
1) not을 사용하지 않은 쿼리문
SELECT employee_id, first_name, job_id
FROM employees
WHERE job_id != 'SA_MAN' and job_id != 'IT_PROG' and job_id != 'HR_REP';
2)not을 사용하여 위의 쿼리문을 수정
SELECT employee_id, first_name, job_id
FROM employees
WHERE job_id NOT IN('SA_MAN', 'IT_PROG', 'HR_REP'); -- NOT과 IN을 활용-- 부서번호가 80이 아닌 부서의 사원들의 정보를 이름, 부서번호 순으로 출력
SELECT first_name, department_id
FROM employees
WHERE department_id != 80;
SELECT first_name, department_id
FROM employees
WHERE department_id NOT IN(80);
SELECT first_name, department_id
FROM employees
WHERE NOT department_id =80;ORDER BY : 검색 결과에 반환되는 행(row)을 정렬할때 사용
언제나 SELECT절의 마지막에 기술 되어야 한다.
--오름차순 : ASC( 생략 가능 )
--내림차순 : DESC( 생략 불가 )
--사원테이블에서 급여를 많이 받는 순으로 사번 , 이름 ,급여, 입사일을 출력 하시오
단, 급여가 같을 경우 먼저 입사한 사람이 위에 나오도록 출력
SELECT employee_id, first_name, salary, hire_date
FROM employees
ORDER BY salary DESC, hire_date ASC;--급여가 15000이상인 사원들의 이름,급여,입사일을 출력하되 입사일이 빠른 순으로 정렬하여 조회
SELECT first_name, salary, hire_date
FROM employees
WHERE salary >= 15000
ORDER BY hire_date ASC;-- 입사일이 빠른순으로 이름, 입사일을 출력하되, 입사일이 같다면 이름내림차순으로 조회
SELECT first_name, hire_date
FROM employees
ORDER BY hire_date, first_name DESC; --ASC 생략-- 부서번호가 빠른순, 같으면 직종이 빠른순 , 직종까지 같다면 , 급여가 높은순 으로
사번, 이름, 부서번호, 직종 , 급여를 조회
SELECT employee_id, first_name, department_id, job_id, salary
FROM employees
ORDER BY department_id, job_id, salary DESC; --ASC 생략SET : 중복제거와 관련된 sql 키워드들
--distinct : 중복제거
--union : 중복제거 ,정렬
--부서번호가 90이거나 80인 사람들의 이름, 부서번호 오름차순, 급여 오름차순으로 정렬하고 중복을 제거
SELECT distinct department_id, salary -- distinct 이용
FROM employees
WHERE department_id IN(80,90)
ORDER BY department_id, salary;--union을 통해 위의 코드 수정하기
SELECT department_id, salary
FROM employees
WHERE department_id = 80
union -- 자동 오름차순 및 중복 제거
SELECT department_id, salary
FROM employees
WHERE department_id = 90--union은 서로 다른 테이블의 자원을 동시에 조회하는 것이 가능
SELECT department_id
FROM departments
union -- 자동 오름차순 및 중복 제거
SELECT salary
FROM employees;--intersect : 두 결과의 교집합된 행을 반환
SELECT salary
FROM employees
WHERE department_id >90
intersect -- 위 아래의 sql문의 공통된 결과만 출력하게 해주는 기능, 모든 열의 결과가 같아야만 교집합 인정
SELECT salary
FROM employees
WHERE department_id <= 80;
--minus : 첫 번째 select문에 의해 반환되는 행 중에서 두번째 select문이 검색한 내용을 제외하고 출력
--급여오름차순, 부서번호를 출력하되, 부서번호가 100번인 사원은 제외하고 중복을 제거하시오.
SELECT distinct salary, department_id
FROM employees
WHERE department_id != 100
ORDER BY salary;-- 위의 코드를 minus를 이용하여 코드 수정
SELECT salary, department_id FROM employees
minus
SELECT salary, department_id FROM employees
WHERE department_id = 100;<SQL 함수의 종류>
1. 그룹 함수 : 여러개의 행 또는 테이블 전체에 대하여 하나의 결과만을 가져오는 함수
그룹함수는 null값은 무시하며 NVL함수를 통해 null 값을 변경해주어야 무시를 하지 않는다.
(NVL 함수는 뒤에 기타함수에서 다룸)
--AVG() : 평균을 구하는 함수
--COUNT() : 행의 갯수를 반환하는 함수 ( null값은 무시함 )
--MAX() : 최대값을 구하는 함수
--MIN() : 최소값을 구하는 함수
--SUM() : 총 합을 구하는 함수
--규칙 : 그룹함수와 일반 컬럼은 함께 사용 될 수 없다.
SELECT COUNT(*)
FROM employees;
SELECT COUNT(commission_pct), salary --사용불가, 오류
FROM employees;
SELECT COUNT(commission_pct) --사용가능
FROM employees;
SELECT COUNT(commission_pct) person -- AS를 생략하고 별칭도 사용 가능
FROM employees; -- 컬럼 이름이 COUNT(commission_pct) -> person별칭으로 변경--사원 테이블에서 사번을 가지고 있는 모든 사원의 수를 출력
SELECT COUNT(employee_id) -- 모든 사원 전체 사번을 가지고있으므로 * 과 같은 의미
FROM employees; -- 모든 사원수를 출력하고 싶다면 * 을 사용하는 것이 훨씬 효율적--사원테이블에서 상사가 있는 사원의 수를 출력
SELECT COUNT(manager_id)
FROM employees;--전체 사원수와 보너스를 받는 사원의 수를 각각 출력
SELECT COUNT(*) allCnt, COUNT( commission_pct) comm
FROM employees;--직종이 'SA_REP'인 사원들의 수와 평균 급여와 최고급여, 최저급여, 급여의 총 합계를 화면에 출력
SELECT COUNT(job_id) count, AVG(salary) avg, MAX(salary) max, MIN(salary) min, SUM(salary) sum
FROM employees
WHERE job_id = 'SA_REP';2. 숫자함수
--round() : 반올림
--trunc() : 버림
--power() : 제곱
--mod() : 나머지 값
--규칙 : 숫자함수와 일반 컬럼은 함께 사용 할 수 있다.
--round를 이용한 소숫점 반올림 후 화면에 출력
SELECT ROUND(0.12345678, 2), ROUND(3.141592,3) -- 0.12 와 3.142 표시
FROM dual;--round()가 avg()를 포함해서 화면에 출력
SELECT ROUND(AVG(salary), 2)
FROM employees;--trunc()를 이용한 소숫점을 버린 후 화면에 출력
SELECT TRUNC(3.555, 1) -- 3.5 표시
FROM dual;--trunc()가 avg()를 포함해서 화면에 출력
SELECT TRUNC(AVG(salary), 2)
FROM employees;
--power()를 이용한 4^2를 화면에 출력
SELECT POWER(4,2) -- 4^2 = 16
FROM dual;--mod()를 이용한 10/3의 나머지를 화면에 출력
SELECT MOD(10,3) -- 10/3 -> 나머지 1 출력
FROM dual;--짝수 사번을 가진 사원들의 사번,이름을 출력
SELECT employee_id, first_name
FROM employees
WHERE MOD(employee_id,2) = 0;
ORDER BY employee_id ASC; -- 오름차순 정리--10/3의 몫을 구하고 싶다
SELECT TRUNC((10/3))
FROM dual;3. 문자함수
-- initcap() : 첫 문자를 대문자로 변환
-- lower() : 대문자 -> 소문자
-- upper() : 소문자 -> 대문자
-- substr() : 특정 문장을 잘라낸다
-- replace() : 필요한 문장을 교체
-- lenth() : 문장의 길이
--규칙 : 문자함수와 일반 컬럼은 함께 사용 할 수 있다.
--initcap()
initcap()은 공백이나 /를 구분자로 인식한다.
SELECT initcap('good morning') -- Good Morning 띄어쓰기를 기준으로 첫문자를 대문자로 변환
FROM dual;
SELECT initcap('good/morning') -- Good Morning / 를 기준으로 첫문자를 대문자로 변환 (/ 구분자)
FROM dual;
--lower()
SELECT lower('GOOD MORNING') -- good morning 출력
FROM dual;
SELECT first_name, lower( first_name )
FROM employees;
SELECT first_name
FROM employees
WHERE lower( first_name ) = 'michael' --모두 소문자로 만든 후 검색--upper()
SELECT UPPER('hi hello') -- HI HELLO 출력
FROM dual;--substr()
SELECT substr('good morning',1,4) -- good 출력 --첫번째 자리부터 4글자 자르기
FROM dual;
-- 문제 모든사원들에 대해 입사일, 입사연도만 출력
SELECT substr(hire_date,1,5), substr(hire_date,7,4) -- 5글자 자름, 4글자 자름 -- 01/02 2001 형태로 출력
FROM employees
ORDER BY substr(hire_date,7,10);--replace()
SELECT replace('good morning tom', 'o', 'i') -- 'o'를 'i'로 변경
FROM dual;
--length()
SELECT LENGTH('hi')
FROM dual;
-- 사원 테이블에서 이름이 6글자 초과인 사원들의 이름만 출력
SELECT first_name
FROM employees
WHERE LENGTH(first_name) > 6;