2023/09/11🎈 Join

 

 

👇 공부한 자료들의 실제 소스파일들을 기록해논 공간입니다 👇

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

 

 

 

 

 

DML 정리 문제 

1. 성별 평균을 구하시오, 단 1학년은 제외합니다.	
	SELECT gender , avg(point)
	FROM mymember
	WHERE grade > 1
	GROUP BY gender;
	
2. 4학년만 출력하시오. 이름은 앞에 두글자만 출력하고 뒤에는 *표 4개로 마스킹하시오.	
	SELECT grade 학년 , concat(substr(name, 1, 2), '****') 이름마스킹
	FROM mymember
	WHERE grade = 4;
	
	
3. 2번 문제에서 마스킹을 *표 4개가 아닌, 남은 글자만큼 마스킹하시오.	
	SELECT name, RPAD(substr(name,1,2),length(name), '*') AS RPAD_name
	FROM mymember;
	
	
4. 모든 학생의 정보를 출력하시오, 	
w_date 출력시 년월일만 보기에 하시오.. ex 88/02/01	
	SELECT NO, name, addr, gender, GRADE, point
	, to_char(A.w_date,'yy/mm/dd') 생년월일
	FROM mymember A;
	
	
5. 나이가 가장 많은 학생 5명을 출력하시오..	
 나이가 같다면 학년이 높은 사람으로 다시 정렬합니다.	
	SELECT R,B.name 이름, B.나이,B.grade 학년
	FROM ( SELECT rownum R , A.*
	FROM ( SELECT name, to_char(w_date,'yyyy') 생년월일
	, (MONTHS_BETWEEN(sysdate, w_date)/12) 나이 , grade
	FROM mymember
	ORDER BY w_date , grade ASC) A ) B
	WHERE B.R <= 5;
	
	
6. 포인트가 5번째에서 10번째로 많은 사람의 정보를 출력하시오.	
 포인트가 같다면 나이가 적은 순서가 우선합니다.	
	SELECT n.R 순위 , n.name 이름 , n.point 포인트 , n.나이
	FROM ( SELECT rownum R, m.*
	FROM (SELECT name, point,(MONTHS_BETWEEN(sysdate, w_date)/12) 나이
	FROM MYMEMBER
	ORDER BY w_date DESC) m ) n
	WHERE R BETWEEN 5 AND 10;
	
	
7. 주소에 a라는 글자가 포함된 사람만 모든 정보를 출력하시오.	
	SELECT *
	FROM MYMEMBER
	WHERE addr LIKE '%a%';
	
	
8. 모든 사람의 포인트를 +100한 정보를 저장하시오. 실제 테이블 정보에 반영되어야 한다.	
	UPDATE mymember SET point = point + 100;
	
	
9. 2학년의 이름을 마스킹하시오…	
 마스킹 방법은 앞에 글자 3글자 뒤에 ***(3개) 입니다. 실제 테이블 정보에 반영되어야 한다.	
	UPDATE mymember
	SET name = concat(substr(name, 1, 3), '***')
	WHERE grade = 2 ;
	
	
10. 모든 학생들이 grade가 1씩 증가하도록 처리하세오 	
실제 테이블 정보에 반영되어야 한다.	
	UPDATE MYMEMBER
	SET grade = grade + 1;

 

 

 

 

 

 

JOIN

 

1. 정의 : 1개 이상의 테이블을 하나의 논리적인 테이블로 합쳐지는 것 이며 역 정규화 하는 것이다.

테이블 중복을 최소화 하기 위해 진핸ㅇ한 정규화 테이블에서 필요한 컬럼만 다시 합치는 작업

2. 종류 : INNER JOIN , LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN

 

- Inner Join : 테이블간 서로 연관된 속성으로 합쳐지는 것

- Outer Join : 테이블간 매칭되는 데이터와 매칭되지 않는 데이터를 모두 확인

- Full Join : 모든 데이터를 그냥 합친 것

 

 

 

실습

 

실습을 위한 데이터를 입력하기

↘️ 더보기 클릭 

CREATE TABLE car(
c_no varchar2(9) PRIMARY KEY,
c_type varchar2(20),
c_date timestamp DEFAULT sysdate
);

CREATE TABLE repaircar(
r_no number(3) PRIMARY KEY,
r_date timestamp DEFAULT sysdate,
r_memo varchar2(100),
r_price number(10),
r_c_no varchar2(9)
);

INSERT INTO car VALUES ('12가1234', '소나타', '23/08/12');
INSERT INTO car VALUES ('13가1234', '카니발', '23/07/12');
INSERT INTO car VALUES ('14가1234', '그랜저', '23/08/11');
INSERT INTO car VALUES ('15가1234', 'K7', '23/09/02');
INSERT INTO car VALUES ('16가1234', '포드', '23/08/20');

INSERT INTO repaircar values(1,'23/09/01','타이어교체', 150000, '12가1234');
INSERT INTO repaircar values(2,'23/09/01','엔진오일', 50000, '13가1234');
INSERT INTO repaircar values(3,'23/09/03','범퍼수리', 250000, '12가1234');
INSERT INTO repaircar values(4,'23/09/03','타이어펑크', 30000, '14가1234');
INSERT INTO repaircar values(5,'23/09/04','세차', 20000, '17가1234');
UPDATE repaircar SET r_memo = '엔진오일' WHERE R_NO = 2;

SELECT * FROM repaircar;
SELECT * FROM car;

 

 

car의 첫번째 튜플에 repaircar를 다 합쳐버린 쿼리
이론적 단어 : FULL JOIN
SELECT * FROM car,repaircar;

 

동등 inner Join 
SELECT *
FROM car CAR,
repaircar RE
WHERE CAR.c_no = RE.r_c_no;

 

자동차번호와 자동차이름과 수리내용과 수리날짜만 출력하시오
테이블명.속성명 : 유니크하면 테이블명을 적지 않아도 되지만, 중복일 경우를 대비하여 적는것이 좋다.

SELECT CAR.c_no 자동차번호 , CAR.C_TYPE 자동차이름 , RE.R_MEMO 수리내용, RE.R_DATE 수리날짜
FROM car CAR, repaircar RE
WHERE CAR.c_no = RE.r_c_no;

 

12가1234 차량의 차 타입과 수리 내역을 출력하시오, 내가 알고 있는 정보는 차량 번호 이다.
풀이 : 조인을 해야하는가? , 조인 결과에 따라 where group by, select order를 기존과 같이 고려하면 됨
문제에서 요구하는 데이터값은 테이블 두개를 합쳐야 한다 

SELECT C.C_NO 차번호 , C.C_TYPE 차타입, R.R_MEMO 수리내역
FROM car C
INNER JOIN repaircar R
ON C.c_no = R.r_c_no
WHERE C.c_no = '12가1234';

 

수리비가 10만원 이상인 자동차번호와 차타입, 수리내용, 수리가격을 출력하시오 

SELECT C.C_NO 차번호 , C.C_TYPE 차타입 , R.R_MEMO 수리내용 , R.R_PRICE 수리비
FROM car C
INNER JOIN repaircar R
ON C.c_no = R.r_c_no
WHERE R.r_price >= 100000;

 

 

자동차1대별로 수리건수를 출력하시오, 자동차 1대의 기준은 등록된 자동차 번호이다.

SELECT CAR.C_NO 차번호 , count(RE.R_NO) 수리건수
FROM car CAR
INNER JOIN repaircar RE
ON CAR.c_no = RE.r_c_no
GROUP BY CAR.c_no
ORDER BY CAR.c_no DESC;

 

 

LEFT OUTER JOIN / RIGHT OUTER JOIN 예제	
FROM 절의 테이블이 1차 = 왼쪽  테이블다. , JOIN 절에 오는 테이블이 2차 = 오른쪽 테이블이다	
	
	
LEFT OUTER JOIN 예제	
	SELECT *
	FROM car c
	LEFT OUTER JOIN repaircar r
	ON c.c_no = r.r_c_no;

1차 테이블 car , 2차 테이블 repaorcar LEFT JOIN 임으로 1차테이블 기준 데이터는 모두 출력되지만 2차 테이블 데이터는 [NULL]값 처리로 출력됨 2차 테이블엔 17가1234 세차 테이더가 있지만 1차테이블에 일치된 값이 없기 때문에 탈락됨

 

 

 

RIGHT OUTER JOIN 예제 	

	SELECT *
	FROM car c
	RIGHT OUTER JOIN repaircar r
	ON c.c_no = r.r_c_no;
    
    1차테이블 car , 2차 테이블 repaircar 
RIGHT OUTER JOIN 임으로, 2차 테이블의 데이터는 모두 출력되지만
1차 테이블에 있는 데이터와 일치 되지 않는 데이터들은 모두 탈락되고 [NULL]처리가됨

 

 

과제 준비 데이터 생성

create table member(
id int primary key,
name varchar(9),
addr varchar(13),
point int default 10);​​

설명 : id- 고객아이디

DROP TABLE MEMBER;

create table carNum(
id int,
carnum varchar(4),
infonum int
);​

설명 : id-고객아이디, infonum - 자동차관리번호

create table carInfo(
infonum int,
infoname varchar2(10)
);

설명: infonum-자동차관리번호, infoname 자동차차종정보

insert into member values (1,'홍길동','천안시',100);
insert into member values (2,'고길두','수원시',200);
insert into member values (3,'일지매','천안시',default);
insert into member values (4,'오지옹','오산시',10000);
insert into member values (7,'삼천포','청주시',30);
insert into member values (6,'우러크','천안시',6000);
insert into member values (5,'광어크','수원시',60);
insert into carNum values (1,'1111',1);
insert into carNum values (2,'1221',1);
insert into carNum values (3,'1133',2);
insert into carNum values (1,'5511',2);
insert into carNum values (1,'1441',1);
insert into carNum values (4,'8989',1);
insert into carInfo values (1,'승용');
insert into carInfo values (2,'suv');
insert into carInfo values (3,'RV');

 

 

 

 

과제

다음 문제를 해결하고 query문을 덧글로 작성하시오.​	
1. 회원이 소유한 자동차 번호를 출력하시오. (id, 이름, 주소)	
	SELECT m.id 회원아이디 , m.name 회원이름 , m.addr 회원주소 , n.carnum 자동차번호
	FROM MEMBER m
	INNER JOIN carNum n
	ON m.id = n.id );
	
2. 자동차를 소유하지 않은 회원의 아이디와 이름을 출력하시오.	
	SELECT m.id , m.name
	FROM MEMBER m
	LEFT OUTER JOIN carNum n
	ON m.id = n.id
	WHERE n.id IS NULL;
	
3. 천안 사는 사람이 소유한 자동차번호 출력하시오.	
	SELECT m.name, m.addr, n.carnum
	FROM MEMBER m
	INNER JOIN carNum n
	ON m.id = n.id
	WHERE m.addr LIKE '%천안%';
	
4. 1441 번호의 자동차의 소유자의 이름은 무엇인가?	
	SELECT m.name , n.carnum
	FROM MEMBER m
	INNER JOIN carnum n
	ON m.id = n.id
	WHERE n.carnum = 1441;
	
5. 회원의 id가 큰 것부터 회원의 모든 정보를 출력하시오.	
	SELECT J.*, i.infoname 차종정보
	FROM ( SELECT m.id 아이디, m.name 이름, m.addr 주소, m.point 포인트, c.carnum 차번호, c.infonum 관리번호
	         FROM MEMBER m
	         FULL JOIN carNum c
	         ON m.id = c.id
	         ORDER BY m.id DESC ) J
	FULL JOIN carinfo i
	ON j.관리번호 = i.infonum ;
	
6. 천안에 거주 하는 회원의 이름을 출력하시오.	
	SELECT name
	FROM MEMBER
	WHERE addr LIKE '%천안%';
	
7. 천안에 거주 하지 않는 회원의 이름과 주소를 출력하시오.	
	SELECT name , addr
	FROM MEMBER
	WHERE addr NOT LIKE '%천안%';
	
8. 천안엔 거주하는 사람들의 포인트를 모두 합하시오.	
	SELECT sum(point)
	FROM MEMBER
	WHERE addr LIKE '%천안%';
	
9. 수원에 거주하거나 천안에 거주 하는 사람들의 이름, 주소를 출력하시오.	
	SELECT name , addr
	FROM MEMBER
	WHERE REGEXP_LIKE(addr, '수원|천안');
	
10. 포인트가 높은 사람순으로 출력하되, 포인트가 동일일 경우 id가 낮은 사람부터 출력하시오.	
	SELECT point , id
	FROM MEMBER
	ORDER BY point DESC, id ASC;
	
11. 200이상 1000이하 의 포인트를 소유한 사람의 이름과 포인트를 출력하시오.	
	SELECT name, point
	FROM MEMBER
	WHERE point BETWEEN 200 AND 1000;
	
12. 도시별(천안시, 수원시 등)자동차 등록된 자동차 수를 구하시오.	
	SELECT m.addr 도시명 , count(c.id) 총자동차수
	FROM MEMBER m
	INNER JOIN carNum c
	ON m.id = c.id
	GROUP BY m.addr;
	
13. 천안 사는 사람의 정보를 출력하되 포인트가 6000 이상이면 포인트란에 vip라고 출력하시오.​	
	SELECT id 아이디, name 이름, addr 주소,
	CASE WHEN point < 6000
	THEN to_char(point)
	ELSE 'vip' END 포인트
	FROM MEMBER
	WHERE addr LIKE '%천안%';
	
14. 도시별(천안시, 수원시 등)자동차 등록된 자동차 수를 구하시오.	
 (단, 등록된 자동차의 수가 2이상인 도시와 자동차수만 출력하시오.	
	SELECT UP.addr 도시명 , UP.ccar 차랑총대수
	FROM ( SELECT m.addr , count(c.id) ccar
	FROM MEMBER m
	INNER JOIN carNum c
	ON m.id = c.id
	GROUP BY m.addr) UP
	WHERE UP.ccar >= 2;
	
15. 자동차를 소유한 회원의 정보를 출력하세요..(이름, 주소, 차번호, 차종)	
	SELECT J.*, i.infoname 차종정보
	FROM ( SELECT m.id 아이디, m.name 이름, m.addr 주소, m.point 포인트, c.carnum 차번호, c.infonum 관리번호
	FROM MEMBER m
	INNER JOIN carNum c
	ON m.id = c.id ) J
	INNER JOIN carinfo i
	ON j.관리번호 = i.infonum ;
	
16. 회원이 소유한 자동차의 종류를 차종기준으로 각각 몇 대인지 출력하시오.	
	SELECT i.infoname 차종 , count(i.infoname) 총대수
	FROM ( SELECT *
	FROM MEMBER m
	INNER JOIN carNum c
	ON m.id = c.id) S
	INNER JOIN carInfo i
	ON S.infonum = i.infonum
	GROUP BY i.infoname ;
	
17. 회원이 소유하지 않은 차종을 출력하시오.	
	SELECT i.infoname 차종
	FROM carNum n
	RIGHT OUTER JOIN carInfo i
	ON n.infonum = i.infonum
	WHERE n.id IS NULL;