2023/09/07⭐️SQL(Structured Query Language)의 종류와 구문법 그리고 과제
👇 정리한 SQL파일 및 예제/과제 데이터들은 하단 git 링크에 오시면 있어용 👇
구문법의 정의
ORACLE 문법 중 가장 중요한 기본은 기본 쿼리 구문법의 정의다.
기본 쿼리 구문법의 기준이 절 대 흔들려선 안된다.
SELECT : 검색 하고자 하는 컬럼
FROM : 대상 데이터
WHERE : 튜플을 선정하기 위한 조건
GROUP BY : 그룹을 짓는 컬럼기준 선택
HAVING : 그룹을 짓기 위한 그룹의 조건
ORDER BY : 출력되는 데이터의 정렬 방법 선택
실행 순서는 FROM ➡️ WHERE ➡️ GROUP BY ➡️ HAVING ➡️ SELECT ➡️ ORDER BY 이다.
이 실행 순서와 해당 절에 대한 기준은 절 대 흔들려선 안된다
SQL(Structured Query Language)의 종류
DDL ( 데이터 정의어 )
DML ( 데이터 조작어 )
DCL ( 데이터 제어어 )
TCL ( 트랜잭션 언어 )
크게 이렇게 4가지가 있다.
사실 'SELECT'의 정의는 어떤 책애선 DQL 로 정의 하기도 하고, 어떤 책에선 DML로 포함시키기도 한다.
대부분 DML에 포함시키는 것 같다.
데이터 정의어 DDL
Data Definition Language 의 약자로
단위는 컬럼단위(Field, Attribute) 단위이다
주의 : 직접 DB TABLE에 영향을 줌, DCL 명령 어를 입력하는 순간 즉시 AUTO COMMIT-완료 된다는 것을 꼭 기억해야함
CREATE , ALTER , DROP , TRUNCATE , RENAME 이 포함 되어 있다
CREATE : 테이블이나 뷰 생성 , 데이터 베이스 구조 또는 스키마 정의
ALTER : 수정, 삭제, 추가
DROP : 데이터베이스나 테이블 구조 자체 삭제
TRUNCATE : 테이블 구조는 남겨두고 데이터만 전부 삭제 / 테이블 재사용 가능
RENAME : 테이블 명이나 컬럼명 변경
데이터 조작어 DML
Data Query Language 의 약자로
단위는 튜플 단위 (Row, Recode) 단위 이다.
또 다른 이름은 CRUD 라고 한다.
대표적으로 SELECT , INSERT , UPDATE , DELETE 가 포함되어 있다.
특이 한건 DML만 COMMIT / ROLLBACK 가능 하고 나머지는 불가 하다
( 조작하려는 테이블을 메모리 버퍼에 올려놓고 작업을 하기 때문에 실시간으로 COMMIT 되진 않는다 )
SELECT : 데이터 검색
INSERT : 데이터 추가
UPDATE : 데이터 수정
DELETE : 데이터 삭제
MERGE : UPSERT 작업 ( INSERT or UPDATE ) 데이터가 테이블에 존제하지 않으면 INSERT, 존재하면 UPDATE 수행
CALL : PL/SQL 또는 JAVA 서브 프로그램 호출
EXPLAIN PLAN : 데이터 접근 경로 해석 ( SQL문의 실행,작동하는 부분에 점검/분석 하도록 도와줌)
LOCK TABLE : 동시성 제어
데이터 제어어 DCL
Data Control Language 의 약자로
데이터베이스에 접근 하거나 객체에 권한을 주는 등 역할을 하는 언어다.
말 그대로 데이터를 제어하는 언어이며
GRANT / REVOKE 가 포함 되어 있다.
주의 : 직접 DB TABLE에 영향을 줌, DCL 명령 어를 입력하는 순간 즉시 AUTO COMMIT-완료 된다는 것을 꼭 기억해야함
GRANT : 데이터베이스 사용자에게 특정 작업에 대한 수행 권한부여
REVOKE : 데이터베이스 사용자에게 특정 작업에 대한 권한을 회수, 박탈 , 해제
오라클 계정 권한 설명
system , sysdba : 권한 부여/해제 권한 있음
user (hr 계정 scott 계정 포함 ) : 권한 부여/해제 권한 없음
SYS : Oracle DB 관리자, Super 사용자 계정, 모든 관리 기능을 수행
SYSTEM : 백업 및 복구, 데이터 베이스 업그레이드를 제외한 모든 관리 및 기능 수행 가능
HR, SCOTT : 오라클을 잘 다루지 못하는 교육 계정
트랜잭션 언어 TCL
Transaction Control Language
데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는데 사용한다
주의 : 직접 DB TABLE에 영향을 줌, DCL 명령 어를 입력하는 순간 즉시 AUTO COMMIT-완료 된다는 것을 꼭 기억해야함
COMMIT : 트랜잭션 작업이 정상적으로 완료(저장) 됨을 관리자에게 알려줌
ROLLBACK : 데이터베이스를 마지막으로 COMMIT된 지점의 상태로 복원,
데이터에 대한 변경 내용은 논리적인 트랜잭션으로 그룹화 될 수 있다
SAVEPOINT : SAVEPOINT(저장점)을 정의시 롤백 할 때 트랜잭션에 포함된 전체 작업을 롤백 하는 것이 아니라
현 시점에서 저장점까지 트랜잭션의 일부만 롤백 할 수 있다. 보통 대규모 트랜잭션에서 에러 발생 시
SAVEPOIN까지 트랜잭션만 롤백 하고 오류에 대해서만 다시 실행해 볼 수 있으나 일부 툴에선 안될 수 있다
복수의 SAVEPOINT 를 정의 가능, 동일 이름으로 정의 시 나중에 정의한 SAVEPOINT로 재정의 됨
SET TRANSACTION : TRANSACTION 지정
주의점 : 한번 커밋하면 롤백 불가하다
비절차적 언어이다 보니 위에서 오류가 나도 오류 난 것 아래 쿼리는 실행 된다.
오늘도 과제를 해봅시다
# 백데이터 생성
create table mymember(
no int primary key,
name varchar(10),
addr varchar(10),
gender varchar(1) default 'w',
grade int default 1,
point int default 100,
w_date timestamp);
create sequence member_no
increment by 1
start with 10;
DROP TABLE mymember;
DROP SEQUENCE member_no;
insert into mymember values(member_no.nextval,'kim min','suwon city','m',2,300,'19780201');
insert into mymember values(member_no.nextval,'Lee soo','seoul','m',1,200,'19810201');
insert into mymember values(member_no.nextval,'kim jin','cheonan','w',1,150,'19880401');
insert into mymember values(member_no.nextval,'Park su','daejeon','w',2,100,'19780601');
insert into mymember values(member_no.nextval,'kim hyun','busan','m',2,300,'19780201');
insert into mymember values(member_no.nextval,'Ryu min','cheonan','w',3,400,'19780201');
insert into mymember values(member_no.nextval,'kim mee','suwon','m',4,300,'19880201');
insert into mymember values(member_no.nextval,'kim sun','seoul','w',4,290,'19720201');
insert into mymember values(member_no.nextval,'choi min','suwon','w',2,220,'19850701');
insert into mymember values(member_no.nextval,'choi sun','cheonan','m',2,200,'19810801');
insert into mymember values(member_no.nextval,'park min','seoul','w',3,340,'19800501');
insert into mymember values(member_no.nextval,'kim min','suwon','m',3,370,'19841101');
insert into mymember values(member_no.nextval,'choi min','cheonan','w',2,300,'19901101');
insert into mymember values(member_no.nextval,'Song ban','busan','w',4,390,'19921201');
insert into mymember values(member_no.nextval,'Lee kyu','suwon','w',2,500,'19920901');
insert into mymember values(member_no.nextval,'kim suk','suwon','m',1,400,'19800801');
insert into mymember values(member_no.nextval,'park suk','daejeon','w',1,250,'19780501');
insert into mymember values(member_no.nextval,'kim wan','cheonan','m',2,210,'19790201');
insert into mymember values(member_no.nextval,'James','suwon','w',1,240,'19790301');
insert into mymember values(member_no.nextval,'Han me','seoul','m',4,210,'19731201');
insert into mymember values(member_no.nextval,'Jeon su','suwon','w',3,320,'19820401');
insert into mymember values(member_no.nextval,'Gong dul','daejeon','w',3,410,'19770411');
insert into mymember (no,name,gender,grade,point,w_date) values(member_no.nextval,'Ha ha','m',2,250,'19820201');
insert into mymember (no,name,gender,grade,point,w_date) values(member_no.nextval,'lee ha','w',3,350,'19870601');
insert into mymember (no,name,gender,grade,point,w_date) values(member_no.nextval,'choi ha','m',1,450,'19900201');
# 글쓴이가 직접 풀면서 하는 부분이라 군데 군데 이상한 부분이 있거나
# 무언가 부분만 돌아가는 경우가 있으니 참고해주세요
1. 여자와 남자의 평균포인트를 각각 출력하시오
성별 평균
SELECT gender , avg(point)
FROM mymember
GROUP BY gender;
## GROUP BY 로 남자와 여자 성별을 그룹화 한 다음
AVG() 함수를 통해 성별 그룹 별 평균 포인트를 출력하였습니다
2. 그룹별(grade) 최고점을 출력하되
그룹의 인원이 3이상인 그룹만 출력하시오.
SELECT max(point) , grade
FROM MYMEMBER
GROUP BY grade HAVING count(*) >= 3;
## 그룹의 인원이 3명이상인 그룹만 이라는 부분을 먼저 처리하기위해
GROUP BY 를 이용하여 grade 컬럼을 그룹화 하고
HAVING 절에서 각 그룹화된 것 중 누적인원이
3명이상인 그룹만 조건이 검색되게끔 하였습니다
## 그룹별 최고점을 출력하라
SELECT 절에서 조건에 부합된 그룹에 최고값을 출력될 수 있도록 하였고
, grade를 추가로 출력하여 어떤학년인지 표현하였습니다
3. 포인트가 200이상 400미만인 사람들의 이름, 주소, 성별, 포인트를 출력하시오.
SELECT name , addr , gender , point
FROM mymember
WHERE point >= 200 AND point <= 400 ;
SELECT name , addr , gender , point
FROM mymember
WHERE point BETWEEN 200 AND 400;
## 조건절에서 BETWEEN ~ AND 를 사용하여
point점수가 200점 이상 400점 미만에 해당하는
튜플들만 조회될 수 있도록 하였습니다
4. 주소 중 suwon글자 뒤에는 *를 추가하시오 예) suwon city suwon* city
SELECT name
, CASE addr WHEN 'suwon city' THEN 'suwon* city'
WHEN 'suwon' THEN concat(addr,'*')
ELSE addr END AS 주소
FROM mymember ;
## CASE ~ WHEN ~ THEN 을 이용하여 addr에 등록된
suwon DATA 와 suwon city 데이터를 모두다
문제에 맞게 표현 될 수 있도록 하였습니다.
suwon city 경우 공백에 '*'를 넣는건 아직어려워서 저렇게 치환 되게끔 하였고
suwon 만 있는 데이터는 concat() 함수를 이용해
글자 뒤에 '*' 가 붙어서 출력 되게끔 하였습니다
5. 서울에 거주하는 사람들의 포인트를 현재 포인트에서 10을 더해서 저장하시오.
SELECT name , point
FROM MYMEMBER
WHERE addr = 'seoul';
UPDATE MYMEMBER
SET point = point+10
WHERE addr = 'seoul';
SELECT NO, name ,addr ,point
FROM MYMEMBER;
## UPDATE ~ SET 구문으로 포인트를 10 더하게끔 설정하였고 ,
조건에 'seoul'에 부합한 사람들만 더할 수 있도록 하였습니다
6 모든 사람의 나이를 구하시오. 출력은 이름, 생년월일, 현재나이
SELECT name
, w_date 생년월일
, TRUNC(MONTHS_BETWEEN(sysdate, w_date)/12+1,0) 현재나이
FROM MYMEMBER;
## (MONTHS_BETWEEN(sysdate, w_date)/12+1) 날짜 함수중
월 기준으로 계산해주는걸 이용하여 (sysdate(기준데이터), w_date(출생년도)) 를
하여 계산된 개월수를 / 12 를 통해 년단위로 바꾸었습니다
만 나이가 아닌 한국 나이로 하기 위해 +1 를 하였습니다
## Trunc를 하지 않는 다면현재 나이가 소수점으로 많이 표기되기 때문에
,0 을 통해 소수점절삭을 진행하였습니다
7. 봄에 태어난 사람의 이름과 생년월일, 거주지, 성별을 출력하시오.
봄은 3월4월5월이다. 성별은 남또는여로 출력한다.
SELECT name 이름, to_char(w_date, 'yyyy/mm/dd') 생년월일
, addr 거주지
, decode (gender, 'm', '남', 'w', '여') AS 성별
FROM MYMEMBER
WHERE to_char(w_date, 'mm') IN ( 03, 04, 05);
## w_date 데이터에 TO_char 함수를 사용하여
월만 추출하게 하였고 IN함수를 써서 03, 04, 05 만 조건에 부합하도록 함
## 생년월일 포멧을 TO_char 를 통해 보기 쉽게 하였으며
## decode 함수를 통해 m일땐 남 , w 일땐 여로 출력되게끔함
8. 그룹의 인원수가 6미만인 그룹의 그룹번호를 출력하시오
SELECT count(*) , grade
FROM mymember
GROUP by grade;
## 해당 그룹별 인원수 조사시 count 함수를 통해서
각 그룹별의 인원수를 체크함, 4학년 grade에 4명으로 확인됨
SELECT grade , count(grade)
FROM mymember
GROUP BY grade
HAVING count(*) < 6;
## GROUP BY 함수로 grade(학년) 별로 그룹화 시킨 다음
HAVING 절에서 그룹별 count 명수가 6미만 경우만
## 조건에 부합하게끔 하여 처리함
9. 포인트가 300미만인 사람이
그룹별로 몇명인지 출력하시오.
단 그룹원수가 1명이하는 출력하지 마시오.
SELECT concat(count(*), '명') 인원 , grade
FROM MYMEMBER
WHERE point < 300
GROUP BY grade
HAVING count(*) > 1;
## 먼저 처리할 조건이 포인트가 300미만 사람을 추출 해야 함으로
WHERE 절을 통해 300미만 인 사람으로 범위 설정을 하였으며
GROUP BY 절을 통해 300미만 사람들 기준으로 그룹화를 시킴
HAVING 절을 통해 인원이 1명 이하는 포함되지 않도록 함
## concat 처리로 count 된 사람의 숫자와 +보기 편하게 '명'이라고
추가로 처리했으며 해당 grade 학년 번호까지 출력하였음
10. 그룹별 평균포인트를 출력하시오.
SELECT grade, avg(point) 평균
FROM mymember
GROUP BY grade
ORDER BY grade ;
## GROUP BY 를 통해 grade 그룹화 한 뒤
그룹당 avg(POINT)를 통하여 grade 별 평균값 추출
11. 그룹별 평균포인트를 출력하되 평균포인트가
300이상인 그룹의 평균포인트와 그룹명을 출력하시오.
SELECT grade 그룹, avg(point) 그룹별평균
FROM MYMEMBER
GROUP BY grade
HAVING avg(point) > 300 ;
## GROUP BY 를 통해 grade 그룹화 한 뒤 그룹당
avg(POINT)를 통하여 300 이상인 그룹만 출력되도록 조건설정
12. 그룹별 평균 포인트를 구하시오. 단 포인트가 200미만인 사람은 그룹에서 제외시킨다.
SELECT grade, avg(point) 평균포인트
FROM mymember
WHERE point > 200
GROUP BY grade;
## 포인트가 200 미만인 사람은 제외시키기 위해
WHERE 절에서 먼저 조건을 설정 후
## 제외된 사람들만 기준으로 GROUP BY 를 통해 grade 그룹화
13. 그룹별 최고점의 포인트 그룹명을 출력하시오. 포인트가 높은 그룹부터 출력하시오.
SELECT max(point) , grade
FROM MYMEMBER
GROUP BY grade
ORDER BY max(point) DESC;
## GROUP BY 를 통해 grade 그룹화 한 다음
, max(POINT)를 통해그룹별 최고값만 출력되게끔 함
## 마지막 정렬때엔 내림차순으로 갈 수 있도록 ORDER BY ~ DESC 처리
14. 모든 사람의 정보를 출력하시오. 단 포인트가 500이상이면 이름 옆에 별표를 하시오.
SELECT NO
, CASE
WHEN point >= 500 THEN concat(name,'*') ELSE name END AS 이름
, ADDR, gender , grade, point, w_date
FROM MYMEMBER
ORDER BY point DESC;
## CASE~WHEN~THEN 을 사용하여 point 가 500 이상인 사람에게만
concat() 함수를 이용 '*' 을 붙히고 아닐경우 일반 name 이 나올 수 있도록 설정
데이터 값이 많이 나오기 때문에 해당 '*' 된값을
빨리 확인 할 수 있도록 ORDER BY 추가설정
15. suwon에 거주 하는 사람의 포인트를 출력하시오.
출력시 전체 포인트 평균도 같이 출력하시오.. 이름 주소 포인트 전체포인트
SELECT name ,addr ,point
FROM MYMEMBER
WHERE addr LIKE '%suwon%';
## suwon 거주하는 사람들의 이름과 주소 포인트만 먼저 확인
SELECT avg(point) FROM MYMEMBER;
## 전체 평균 포인트
SELECT A.name 이름, A.addr 주소, A.point 포인트
, (SELECT avg(point) FROM MYMEMBER) 전체평균포인트
FROM MYMEMBER A
WHERE addr LIKE '%suwon%';
## 전체적으로 suwon 거주하는 사람들의 이름과 주소 포인트를 출력
## 서브쿼리를 이용하여 avg(point)를 추가 출력하도록 진행
16. 나이가 적은 5명의 이름과 생년월일 포인트를 출력하시오.
SELECT name 이름 , w_date 생년월일 , point
FROM (SELECT * FROM mymember ORDER BY w_date DESC)
WHERE rownum <= 5;
## 쿼리 실행 시 1번째 절인 FROM에 서브쿼리를 사용하여
출생년도가 나중인 사람들만 검색이 되게끔 조건을생성 한 뒤
WHERE 절에서 rownum 문법을 통해5번째 순서까지만 출력 되도록 조건추가
17. 평균포인트보다 낮은 포인트를 소유한 사람들의 이름과 포인트를 출력하시오.
SELECT name, point
FROM mymember
WHERE point < (SELECT avg(point) FROM mymember);
## 서브쿼리로 평균 포인트를 먼저 조건을 발생 시키고
WHERE 절에서 비교하여 추가조건을 인식할 수 있도록 함
18. 학년 별 평균을 출력하시오.
단 학년의 평균이 전체 평균 보다 높은 학년만 출력하시오.
SELECT avg(point) FROM mymember;
SELECT grade 학년 , avg(point) 평균
FROM mymember
GROUP BY grade ;
SELECT GRADE, avg(point)
FROM MYMEMBER
GROUP BY GRADE
HAVING avg(point) > (SELECT avg(point) FROM mymember);
## GROUP BY 로 학년을그룹화 시킨 다음 ,
HAVING 절에서 조건을 추가한다, 단 서브 쿼리를 이용하여
전체 평균 포인트와 그룹별 평균 포인트를 비교하도록 하여 출력하게한다
19. 그룹별 인원수를 출력하시오.
단 그룹의 인원수가 전체 인원수/그룹수 평균 높은 그룹만 출력하시오.
SELECT grade 그룹 , avg(point) 그룹별평균, count(point) 그룹별인원
FROM mymember
GROUP BY grade
ORDER BY count(*) desc;
20. 가장 높은 점수를 획득한 사람의 이름과, 점수, 그룹번호를 출력하시오.
SELECT name, point, grade
FROM mymember
WHERE point IN ( SELECT max(point) FROM mymember) ;
## 먼저 가장 높은 사람의 정보를 찾을 수 있게
SELECT max(point) FROM mymember 를 작성 후 서브 쿼리로 적용 하여
본 쿼리에서 point IN (서브쿼리) 로 point 값이 같은 사람을 추출 할 수 있게 한다.
21. 각 그룹별 가장 높은 점수를 획득한 사람의 이름과, 거주지, 포인트를 출력하시오.
SELECT point 포인트 , grade 그룹번호, name 이름 , addr 주소
FROM mymember
WHERE point IN (SELECT max(point)
FROM mymember
GROUP BY grade )
ORDER BY point DESC;
## 그룹별 최대값을추출 , WHERE 절에 중첩 서브쿼리로 적용
## 비교대상은 본쿼리에 point 에 조건비교를 하여 서브쿼리에
각 최대점수의 그룹번호와 이름 주소 포인트를 출력한다
## 출력값이 보기 편하게 ORDER BY point 로 가장 높은 포인트의 순서대로
출력하며 요구했던 이름,거주지를 출력하는걸 추가한다
where 절에서 특수한 함수(in) 없이 사용 시 단일행 서브쿼리로 조회된 행은
반드시 1개가 와야된다, 다중 행의 리턴값을 받으려면in을 써야된다