학습내용
- 과제 확인
- sql keyword
- function
- GROUP BY
- HAVING
- JOIN
과제 확인
- private static
- 포함된 class 안에서만 사용가능하다, main() 해당 class 밖에 있으면 접근 할 수 없다.
- 포함된 class 안에서만 사용가능하여 호출시 class 이름을 생략하기도 한다.
- Null String
String s = new String();
- String을 NULL로 초기화 시키고 .으로 접근하면 NullPointException 발생 가능하기에 위 방법처럼 초기화 한다.
- jvm
- Java Platform의 제일 하단에 위치하여 .class 파일을 os가 읽을수 있도록 기계어로 컴파일해주는 virtual machine이다.
- 사용자가 .java 파일 작성 ==> jdk의 javac를 통해 .class 파일로 변환 ==> jvm이 .class 파일을 기계어로 컴파일 및 os에 전달 ==> os에서 실행
수업 사용 테이블
SQL> select * from emp_test;
ID NAME SALARY TITLE IN_DATE DEPT_NAME
---------- ------------------------------ ---------- ------------------------------ -------- ------------------------------
1 김사장 600 사장 01/07/19 개발부
2 홍길동 1500 부사장 04/07/19 관리부
3 홍길순 2500 사장 23/01/16 대기발령
111 김유신111 1111 대리11 01/01/01 영업부
7 SCOTT 1500 부사장 23/01/16 관리부
222 김유신222 2222 대리22 02/02/02 영업부
333 333김유신 3333 33대리 03/03/03 영업부
444 444김유신 4444 44대리 04/04/04 영업부
555 이순신555 5555 부장 05/05/05 관리부
666 이순신666 6666 06/06/06 관리부
777 주몽777 7777 07/07/07 기획부
888 평민888 대리 08/08/08 영업부
990 한국990 9990 09/09/09 기획부
991 꺽다리991 23/01/16 영업부
992 짠돌이992 9992 과장 09/09/09 관리부
박_% 특수% 23/01/17
김_% 특수% 23/01/17
SQL> select * from c_emp;
ID NAME SALARY PHONE DEPT_ID
---------- ------------------------------ ---------- ------------------------------ ----------
101 김사장 1000 3429-0001 1
102 홍길동 2000 3429-0002 1
103 홍길순 3000 3429-0003 1
104 이순신 4000 3429-0004 2
105 강감찬 5000 3429-0005 2
106 류시원 1100 3429-0006 2
107 이문세 1200 3429-0007 3
108 강호동 1300 3429-0008 3
109 강문영 1400 3429-0009 3
110 추현재 1500 3429-0010 4
111 박상민 2500 3429-0011 4
112 박상면 3500 3429-0012 4
113 유재석 4500 3429-0013 5
114 노무현 5500 3429-0014 5
115 전두환 1000 3429-0015 5
116 김일성 2000 3429-0016 6
117 김정일 3000 3429-0017 6
SQL> select * from sal_grade; SQL> select * from s_dept;
GR LOSAL HISAL ID DEPT_NAME
-- ---------- ---------- ---------- ------------------------
A 4000 5999 1 총무부
B 3000 3999 2 개발부
C 2000 2999 3 영업부
D 1000 1999 4 인사부
E 500 999 5 자재부
6 회계부
sql keyword
- ||
SELECT name |'님의 직급은 '| title |'입니다.'|FROM emp_test;
- Data와 문자를 같이 출력하고싶을때 사용
- DISTINCT
SELECT distinct dept_name, name FROM emp_test;
- 중복을 제거하고 보여줄 때 사용
- one to many의 관계에서 distinct를 사용하면 many 부분만 중복을 제거하고 보여준다.
- ORDER BY
SELECT name 이름, salary * 12 연봉 , dept_name 부서 FROM emp_test ORDER BY dept_name, salary NULLS FIRST; //dept_name을 오름차순 정렬 한후 해당 dept_name의 salary를 다시 오름차순 정렬하고 NULL은 처음에 표시- 데이터를 정렬하여 보여줄 때 사용
- ASC(deafult)는 오름차순 DESC는 내림차순
- ASC일때 NULL은 최하위, DESC일때 NULL은 최상위에 나타남
- NULLS FIRST, NULLS LAST를 통해 NULL의 위치를 조정 가능
- BETWEEN
SELECT name 이름, salary 월급, in_date 입사일 FROM emp_test WHERE salary BETWEEN 1000 AND 2500 ORDER BY salary;
- 데이터의 범위를 지정하여 보여줄 때 사용
- NOT BETWEEN은 해당 범위를 제외하고 찾음
- IN
SELECT name 이름, dept_name 부서 FROM emp_test WHERE dept_name IN('관리부','영업부') ORDER BY dept_name;
- IN안의 데이터만을 보여주고 싶을 때 사용
- NOT IN()은 해당 데이터를 제외한 모든 데이터를 찾음
- LIKE
SELECT * FROM emp_test WHERE name LIKE '김%';
SELECT * FROM emp_test WHERE name LIKE '_\_\%' ESCAPE '\';
SELECT * FROM emp_test WHERE name LIKE '_?_?%' ESCAPE '?';- 특정 데이터가 포함된 데이터를 찾을 때 사용
- '김%', '김'으로 시작하는 모든 데이터 찾음
- '김_' '김'으로 시작하고 길이가 2인 데이터 찾음
- '김/%' , '김?%' '김_'으로 시작하는 모든 데이터를 찾음
function
- Single Row Function
- single row 하나의 row에서만 사용가능(문자형, 숫자형, 날짜형 , 변환형)
- Multi Row Function(사용하기 위해 GROUP BY가 필요)
- 여러개의 row를 통해 사용 가능한 함수(NULL은 제외하고 동작)
GROUP BY
SELECT name, MAX(salary) FROM emp_test WHERE dept_name = '관리부';
- 위 쿼리는 "단일 그룹의 그룹 함수가 아닙니다" 에러가 발생
사진 넣기 - 위와 같이 여러개의 name이 하나의 MAX(salary)에 접근하여 발생하는 에러이며 이를 해결하기위해 GROUP BY를 사용한다
- GROUP BY를 통해 name을 하나의 그룹으로 묶어주어 many to one의 관계가 아닌 one to one의 관계로 변경하여 단일그룹 함수로 변경 해준다.
having
- GROUP BY는 WHERE 뒤에 수행된다. 따라서 GROUP을 지정하고 해당 GROUP에 조건을 부여하기 위해서는 HAVING이 필요하다
카티션 곱
- 둘 이상의 테이블을 FROM에 적어주고 join 옵션을 설정하지 않았을 때 발생하며 각 테이블의 데이터들을 전부 결합하여 각 테이블의 레코드 * 레코드 수만큼 레코드가 생성된다.
Equi Join
SELECT * FROM c_emp, s_dept WHERE c_emp.dept_id = s_dept.id;
- 두 테이블에 공통된 레코드가 존재 할 때 사용하는 JOIN방법이다 where에 공통된 레코드의 관계를 적어준다
Non Equi Join
SELECT * FROM c_emp, sal_grade WHERE c_emp.salary BETWEEN sal_grade.losal AND sal_grade.hisal;
- 두 테이블에 공통된 레코드가 존재하지 않더라도 테이블의 레코드가 다른 테이블의 범위에 속한다면 사용 할 수 있다.
SQL실습
- || 사용하여 "홍길동 님은 관리부에서 근무함" 형식으로 출력
SELECT name || '님은 ' || dept_name || '에서 근무함' FROM emp_test;
- || 사용 "김사장 님 사장이며 연봉은 7200 원" 형식으로 출력
SELECT name || ' 님은 ' || dept_name || '이며 연봉은' || NVL(salary ,0) * 12 || '원' FROM emp_test;
- "개발부서의 김사장입니다" 형식으로 출력 (부서 오름차순으로 정렬)
SELECT dept_name || '의 ' || name || ' 입니다' FROM emp_test ORDER BY dept_name;
- 부서별/연봉이 높은 순서 이름/부서/연봉 조회
SELECT name 이름, dept_name 부서, salary * 12 연봉 FROM emp_test ORDER BY dept_name, salary desc;
- 연봉이 높은순서, 입사연도가 빠른순서 연봉/입사년도/이름 조회
SELECT salary * 12 연봉, in_date 입사년도, name 이름 FROM emp_test ORDER BY salary desc, in_date;
- 직책 종류 조회
SELECT DISTINCT title "직책 종류" FROM emp_test WHERE title IS NOT NULL;
- 2001년 입사자 이름, 직책, 입사일 조회 (1번째 QUERY는 LIKE사용, 2번째 QUERY는 BETWEEN사용)
SELECT name 이름, title 직책, in_date 입사일 FROM emp_test WHERE in_date LIKE '01%';
SELECT name 이름, title 직책, in_date 입사일 FROM emp_test WHERE in_date BETWEEN '01-01-01' AND '01-12-31';- 임금이 NULL인 사원의 모든 정보 입사일 오름차순 조회
SELECT name 이름, in_date 입사일 FROM emp_test WHERE salary IS NULL ORDER BY in_date;
- 관리부에서 급여가 6000이상 인 사람 이름/급여/부서 조회
SELECT name 이름, salary 급여, dept_name 부서 FROM emp_test WHERE dept_name IN('관리부') AND salary >= 6000;
- 부서명, 부서salary 평균, 합, 인원수 조회(부서명 null, 대기발령 제외)
SELECT dept_name 부서명, AVG(salary) "부서 월급 평균", SUM(salary) "월급 합", COUNT(dept_name) "인원수 조회 " FROM emp_test WHERE dept_name IS NOT NULL AND dept_name Not IN('대기발령') GROUP BY dept_name;
- 부서명, 부서 salary 평균, 합, 인원수 조회(부서명 null, 대기발령 제외, 인원수가 5이상만 출력)
- where이 group by에 선행한다 따라서 group에 조건을 부여하기 위해서 HAVING이라는 키워드가 필요하다
``` SELECT dept_name 부서명, AVG(salary) "부서 월급 평균", SUM(salary) "월급 합", COUNT(dept_name) "인원수 조회 " FROM emp_test WHERE dept_name IS NOT NULL AND dept_name Not IN('대기발령') GROUP BY dept_name having count() >= 5;```
- 부서명, 부서salary 평균, 합, 인원수 조회 (부서명 null, 대기발령 제외, 인원수가 5이상만 출력, 평균급여가 5000이상)
SELECT dept_name 부서명, AVG(salary) "부서 월급 평균", SUM(salary) "월급 합", COUNT(dept_name) "인원수 조회 " FROM emp_test WHERE dept_name IS NOT NULL AND dept_name Not IN('대기발령') GROUP BY dept_name having count(*) >= 5 AND avg(salary) >= 5000;
- 김씨성 직원의 이름, 부서명, 연봉 조회(연봉은 \250,000 형식 , 연봉높은순)
SELECT c.name 이름, s.dept_name 부서명, TO_CHAR(c.salary * 12 ,'L999,999.99') 연봉 FROM c_emp c, s_dept s WHERE c.dept_id = s.id AND c.name LIKE('김%') ORDER BY c.salary DESC;
- 월급이 2000이상 직원 부서명, 이름, 일당 조회 (부서별 :: 오름차순, 이 름 :: 오름차순, 일 당 :: 30일기준, 소수점 0자리 버림, \250,000형식, 높은순)
SELECT s.dept_name 부서명, c.name 이름, TO_CHAR(TRUNC(c.salary / 30, 0), 'L999,999.99') 일당 FROM c_emp c, s_dept s WHERE c.dept_id = s.id AND c.salary >= 2000 ORDER BY s.dept_name, c.name, c.salary desc;
- 부서별 평균 월급 부서번호, 부서명, 평균월급 조회(부서번호 :: 오름차순)
select c.dept_id 부서번호, s.dept_name 부서명, AVG(c.salary) 평균월급 FROM c_emp c, s_dept s WHERE c.dept_id = s.id GROUP BY c.dept_id, s.dept_name;
- 총무부 평균 월급 부서번호, 부서명, 평균월급 조회
SELECT c.dept_id 부서번호, s.dept_name 부서명, AVG(c.salary) FROM c_emp c, s_dept s where c.dept_id = s.id AND s.dept_name In('총무부') GROUP BY c.dept_id, s.dept_name;
- 개발부 직원과 영업부직원 이름 , 월급 , 월급등급 , 부서명 조회 (등급 높은순, NON-EQUI JOIN /EQUI JOIN 같이 사용)
select c.name 이름, c.salary 월급, sg.grade 월급등급 , s.dept_name 부서명 from c_emp c, s_dept s, sal_grade sg where c.dept_id = s.id AND s.dept_name IN ('개발부', '영업부') AND c.salary BETWEEN sg.losal AND sg.hisal;
- sal_grade가 A 인원이 2명이상인 부서명 , 인원수 , 등급 ( NON-EQUI JOIN / EQUI JOIN 같이 사용)
GROUP BY s.dept_name, sg.grade HAVING COUNT(s.dept_name) >= 2; ```
select
c/n [, c/n]
from t/n [, t/n]
where "조건"
group by c/n [, c/n]
having "조건"
order by
