4. DML - SELECT - 1
•
SELECT 절
◦
PREDICATE : 검색할 튜플 수를 제한하는 명령어를 기술한다.
▪
DISTINCT : 중복된 튜플이 있으면, 그 중 첫 번째 한 개만 표시한다.
◦
속성명 : 검색하여 불러올 속성(열) 또는 속성을 이용한 수식을 지정한다.
◦
AS : 속성이나 연산의 이름을 다른 이름으로 표시하기 위해 사용한다.
•
FROM 절 : 검색할 데이터가 들어있는 테이블 이름을 기술
•
WHERE
•
ORDER BY
◦
ASC
◦
DESC
◦
생략시 ASC
조건 연산자
비교 연산자
논리 연산자
•
NOT, AND, OR
LIKE 연산자
•
대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용된다.
•
예제 : <사원> 테이블의 모든 튜플을 검색하시오.
select * from 사원;
select 사원.* from 사원;
select 이름, 부서, 생일, 주소, 기본급 from 사원;
select 사원.이름, 사원.부서, 사원.생일, 사원.주소, 사원.기본급 from 사원;
SQL
복사
•
예제 : <사원> 테이블에서 ‘주소’만 검색하되, 같은 ‘주소’는 한 번만 출력하시오.
select distinct 주소
from 사원;
SQL
복사
•
예제 : <사원> 테이블에서 ‘기본급’에 특별수당 10을 더한 월급을 “XX부서의 XXX의 월급XXX” 형태로 출력하시오.
select 부서 + '부서의' as 부서2, 이름 + '의 월급' as 이름2, 기본급 + 10 as 기본급2
from 사원;
SQL
복사
조건 지정 검색
•
예제 : <사원> 테이블에서 ‘기획’ 부의 모든 튜플을 검색하시오.
select *
from 사원
where 부서 = '기획';
SQL
복사
•
예제 : <사원> 테이블에서 ‘기획’ 부서에 근무함녀서 ‘대흥동’에 사는 사람의 튜플을 검색하시오.
select *
from 사원
where 부서 = '기획' and 주소 = '대흥동';
SQL
복사
•
예제 : <사원> 테이블에서 성이 ‘김’인 사람의 튜플을 검색하시오.
select *
from 사원
where 이름 like "김%";
SQL
복사
•
예제 : <사원> 테이블에서 ‘생일’이 ‘01/06/69’ 에서 ‘12/31/73’ 사이인 튜플을 검색하시오.
select *
from 사원
where 생일 between #01/06/69# and #12/31/73#;
SQL
복사
•
예제 : <사원> 테이블에서 ‘주소’가 NULL인 튜플을 검색하시오.
select *
from 사원
where 주소 is null;
SQL
복사
•
NULL이 아닌 값을 검색할 때는 NOT NULL을 사용하면 된다.
정렬 검색
•
ORDER BY 절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색한다.
•
예제 : <사원> 테이블에서 ‘주소’를 기준으로 내림차순 정렬시켜 상위 2개 튜플만 검색하시오.
select top 2 *
from 사원
order by 주소 desc;
SQL
복사
하위 질의
•
조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용한다.
•
예제 : ‘취미’가 ‘나이트댄스’인 사원의 ‘이름’과 ‘주소’를 검색하시오.
select 이름, 주소
from 사원
where 이름 = (select 이름 from 여가활동 where 취미 = '나이트댄스');
SQL
복사
•
예제 : 취미 활동을 하지 않는 사원들을 검색하시오.
select *
from 사원
where 이름 not in (select 이름 from 여가활동);
SQL
복사
•
예제 : 취미 활동을 하는 사원들의 부서를 검색하시오.
select 부서
from 사원
where exists (select 이름 from 여가활동 where 여가활동.이름 = 사원.이름);
SQL
복사
•
exists () 는 하위 질의로 검색된 결과가 존재하는지 확인할 때 사용한다.
복수 테이블 검색
•
여러 테이블을 대상으로 검색을 수행한다.
•
예제 : ‘경력’이 10년 이상인 사원의 ‘이름’, ‘부서’, ‘취미’, ‘경력’을 검색하시오.
select 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
from 사원, 여가활동
where 여가활동.경력 >= 10 and 사원.이름 = 여가활동.이름;
SQL
복사
5. DML - SELECT - 2
•
그룹함수 : GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술함
•
WINDOW 함수 : GROUP BY 절을 이용하지 않고, 속성의 값을 집계할 함수를 기술함
◦
PARTITION BY
▪
WINDOW 함수의 적용 범위가 될 속성을 지정한다.
◦
ORDER BY
▪
PARTITION 안에서 정렬 기준으로 사용할 속성을 지정하다.
•
GROUP BY 절
◦
특정 속성을 기준으로 그룹화하여 검색할 때 사용한다.
◦
일반적으로 GROUP BY절은 그룹 함수와 함께 사용된다.
•
HAVING 절
◦
GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정한다.
그룹 함수
•
GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 때 사용된다.
WINDOW 함수
•
GROUP BY 절을 이용하지 않고, 함수의 인수로 지정한 속성의 값을 집계한다.
•
함수의 인수로 지정한 속성이 집계할 범위가 되는데, 이를 윈도우(WINDOW)라고 부른다.
•
WINDOW 함수
◦
ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련번호를 반환한다.
◦
RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 반영한다.
◦
DENSE_RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여한다.
WINDOW 함수 이용 검색
•
GROUP BY 절을 이용하지 않고, 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계한다.
•
예제 : <상여금> 테이블에서 ‘상여내역’ 별로 ‘상여금’에 대한 일련번호를 구하시오 (단 순서는 내림차순이며 속성명은 ‘NO’로 할 것)
select 상여내역, 상여금, row_number() over (partition by 상여내역 order by desc) as NO
from 상여금;
SQL
복사
•
예제 : <상여금> 테이블에서 ‘상여내역’ 별로 ‘상여금’에 대한 순위를 구하시오. (단, 순서는 내림차순이며, 속성명은 ‘상여금순위’로 하고, RANK() 함수를 이용할 것)
select 상여내역, 상여금, RANK() over (partition by 상여내역 order by desc) as 상여금순위
from 상여금;
SQL
복사
그룹 지정 검색
•
GROUP BY 절에 지정한 속성을 기준으로 자료를 그룹화하여 검색한다.
•
예제 : <상여금> 테이블에서 ‘부서’별 ‘상여금’의 평균을 구하시오.
select 부서, avg(상여금) as 평균
from 상여금
group by 부서;
SQL
복사
•
예제 : <상여금> 테이블에서 ‘부서’별 튜플 수를 검색하시오.
select 부서, count(*) as 사원수
from 상여금
group by 부서;
SQL
복사
•
예제 : <상여금> 테이블에서 ‘상여금’이 100 이상인 사원이 2명 이상인 ‘부서’의 튜플 수를 구하시오.
select 부서, count(*) as 사원수
from 상여금
where 상여금 >= 100
group by 부서
having count(*) >= 2;
SQL
복사
•
예제 : <상여금> 테이블의 ‘부서’, ‘상여내역’ 그리고 ‘상여금’에 대해 부서별 상여내역별 소개와 전체 합계를 검색하시오 (단, 속성명은 ‘상여금합계’로 하고, ROLLUP 함수를 사용할 것)
select 부서, 상여내역 sum(상여금) as 상여급합계
from 상여금
group by rollup(부서, 상여내역);
SQL
복사
•
예제 : <상여금> 테이블의 ‘부서’, ‘상여내역’ 그리고 ‘상여금’에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오.(단, 속성명은 ‘상여금합계’로 하고, CUBE 함수를 사용할 것)
select 부서, 상여내역, sum(상여금) as 상여금합계
from 상여금
group by cube(부서, 상여내역);
SQL
복사
집합 연산자를 사용하여 2개 이상의 테이블의 데이터를 하나로 통합한다.
•
2개의 SELECT 문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야 한다.
•
집합 연산자의 종류(통합 질의의 종류)
•
예제 : <사원> 테이블과 <직원> 테이블을 통합하는 질의문을 작성하시오. (단, 같은 레코드가 중복되어 나오지 않게 하시오)
select *
from 사원
union
select *
from 직원;
SQL
복사
•
예제 : <사원> 테이블과 <직원> 테이블에 공통으로 존재하는 레코드만 통합하는 질의문을 작성하시오.
select *
from 사원
intersect
select *
from 직원;
SQL
복사
6. DML - JOIN
JOIN
•
2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환한다.
•
일반적으로 FROM 절에 기술하지만, 릴레이션이 사용되는 곳 어디에나 사용할 수 있다.
•
크게 INNER JOIN과 OUTER JOIN으로 구분된다.
INNER JOIN
•
일반적으로 EQUI JOIN 과 NON-EQUI JOIN으로 구분된다.
◦
조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻을 수 있다.
▪
CROSS JOIN (교차 조인)
•
조인하는 두 테이블에 있는 튜플들의 순서쌍을 결과로 반환한다.
•
교차 조인의 결과로 반환되는 테이블의 행의 수는 두 테이블의 행 수를 곱한 것과 같다.
EQUI JOIN
•
JOIN 대상 테이블에서 공통 속성을 기준으로 ‘=’(equal) 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN 방법
•
EQUI JOIN에서 JOIN 조건이 ‘=’일 때, 동일한 속성이 2번 나타나게 되는데, 이 중 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL JOIN 이라고 한다.
•
EQUI JOIN에서 연결 고리가 되는 속성을 JOIN 속성이라고 한다.
•
WHERE절을 이용한 EQUI JOIN의 표기형식
•
예제 : <학생> 테이블과 <학과> 테이블에서 ‘학과코드’ 값이 같은 튜플을 JOIN 하여 ‘학번’, ‘이름’, ‘학과코드’, ‘학과명’을 출력하는 SQL문을 작성하시오.
// where - equi join
select 학번, 이름, 학생.학과코드, 학과명
from 학생, 학과
where 학생.학과코드 = 학과.학과코드;
// natural join
select 학번, 이름, 학생.학과코드, 학과명
from 학생 natural join 학과;
// join ~ using
select 학번, 이름, 학생.학과코드, 학과명
from 학생 join 학과 using(학과코드);
SQL
복사
NON-EQUI JOIN
•
JOIN 조건에 ‘=’ 조건이 아닌 나머지 비교 연산자를 사용하는 JOIN 방법
•
예제 : <학생> 테이블과 <성적등급> 테이블을 JOIN 하여 각 학생의 ‘학번’, ‘이름’, ‘성적’, ‘등급’을 출력하는 SQL문을 작성하시오.
select 학번, 이름, 성적, 등급
from 학생, 성적등급
where 학생.성적 between 성적등급.최저 and 성적등급.최고;
SQL
복사
OUTER JOIN
•
릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법
•
LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
LEFT OUTER JOIN
•
INNER JOIN의 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULl 값을 붙여서 INNER JOIN의 결과에 추가한다.
RIGHT OUTER JOIN
•
INNER JOIN의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULl 값을 붙여서 INNER JOIN의 결과에 추가한다.
FULL UTER JOIN
•
예제 : <학생> 테이블과 <학과> 테이블에서 ‘학과코드’ 값이 같은 튜플을 JOIN하여 ‘학번’, ‘이름’, ‘학과코드’, ‘학과명’을 출력하는 SQL문을 작성하시오. 이때, ‘학과코드’가 입력되지 않은 학생도 출력하시오.
select 학번, 이름, 학생.학과코드, 학과명
from 학생 left outer join 학과
on 학생.학과코드 = 학과.학과코드;
select 학번, 이름, 학생.학과코드, 학과명
from 학생, 학과
on 학생.학과코드 = 학과.학과코드(+);
SQL
복사
select 학번, 이름, 학생.학과코드, 학과명
from 학과 right outer join 학생
on 학생.학과코드 = 학과.학과코드;
select 학번, 이름, 학생.학과코드, 학과명
from 학과, 학생
on 학과,학과코드(+) = 학생.학과코드;
SQL
복사
•
예제 : <학생> 테이블과 <학과> 테이블에서 ‘학과코드’ 값이 같은 튜플을 JOIN하여 ‘학번’, ‘이름’, ‘학과코드’, ‘학과명’을 출력하는 SQL 문을 작성하시오. 이때 ‘학과코드’가 입력 안 된 학생이나 학생이 없는 ‘학과코드’도 모두 출력하시오.
select 학번, 이름, 학생.학과코드, 학과명
from 학생 full outer join 학과
on 학생.학과코드 = 학과.학과코드;
SQL
복사
7. 프로시저 (Procedure)
•
SQL을 사용하여 작성한 일련의 작업을 저장해두고 호출을 통해 원할 때마다 저장한 작업을 수행하도록 하는 절차형 SQL
•
데이터베이스에 저장되어 수행되기 때문에 스토어드(Stored) 프로시저라고도 불린다.
•
시스템의 일일 마감 작업, 일괄(Batch) 작업 등에 주로 사용된다.
프로시저의 구성도
프로시저 생성
•
예제 : ‘사원번호’를 입력받아 해당 사원의 ‘지급방식’을 “S”로 변경하는 프로시저를 생성하시오.
create or replace procedure emp_change_s(i_사원번호 in INT)
is
begin
update 급여 set 지급방식 = 'S' where 사원번호 = i_사원번호;
exception
when program_error then
rollback;
commit;
end;
SQL
복사
프로시저 실행
•
프로시저를 실행하기 위해서는 EXECUTE 명령어 또는 CALL 명령어를 사용하며, EXECUTE 명령어를 줄여서 EXEC로 사용하기도 한다.
EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;
SQL
복사
•
예제 : ‘사원번호’ 32를 인수로 하여 위에서 생성된 emp_change_s 프로시저를 실행하시오.
EXEC emp_change_s(32);
SQL
복사
프로시저 제거
•
예제 : 위에서 생성된 프로시저 emp_change_s를 제거하시오.
drop procedure emp_change_s;
SQL
복사
8. 트리거 (Trigger)
트리거 (Trigger)
•
데이터베이스 시스템에서 데이터의 삽입(Insert), 갱신(Update), 삭제(Delete) 등의 이벤트(Event)가 발생할 때, 관련 작업이 자동으로 수행되게 하는 절차형 SQL
•
트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용된다.
•
트리거의 구문에는 DCL(데이터 제어어)을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에 오류가 발생한다.
트리거의 구성도
트리거의 생성
•
트리거를 생성하기 위해서는 CREATE TRIGGER 명령어를 사용한다.
•
예제 : <학생> 테이블에 새로운 튜플이 삽입될 때, 삽입되는 튜플에 학년 정보가 누락 됐으면 ‘학년’ 속성에 “신입생”을 저장하는 트리거를 ‘학년정보_tri’라는 이름으로 정의하시오.
create trigger 학년정보_tri before insert on 학생
referencing new as new_table
for each ROW
when (new_table.학년 is null)
begin
:new_table.학년 := '신입생';
end
SQL
복사
트리거의 제거
•
트리거를 제거하기 위해 DROP TRIGGER 명령어를 사용한다.
•
예제 : ‘학년정보_tr’ 라는 트리거를 제거하는 SQL문을 작성하시오.
drop trigger 학년정보_tri
SQL
복사