📖 Study/🎈 ᴏʀᴀᴄʟᴇ
2023/09/11🎈 Join
유리쯔의일상
2023. 9. 14. 19:17
반응형
👇 공부한 자료들의 실제 소스파일들을 기록해논 공간입니다 👇
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;
반응형