2023/09/07⭐️SQL(Structured Query Language)의 종류와 구문법 그리고 과제

개쪼렙 개바린이예여 개바린..✨

👇 정리한 SQL파일 및 예제/과제 데이터들은 하단 git 링크에 오시면 있어용 👇

GitHub - gayulz/StudyHistory: 나의 공부 기록 commit

 

 

 

구문법의 정의

 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을 써야된다