본문 바로가기
Coding/데이터베이스

레코드와 컬렉션 & JOIN 연습문제

by 찡콩찡 2022. 9. 19.
레코드란  자료형이 다른 데이터들을 하나의 변수에 저장
TYPE ① 레코드이름 IS RECORD (
   ② 변수이름 ③ 자료형 ④NOT NULL ⑤ :=(또는 DEFAULT) 값 또는 값이 도출되는 여러 표현식
)

 

 

 

컬렉션이란 특정 자료형의 데이터를 여러 개 저장
 * 연관배열(associtative array (or index by table))
 * 중첩 테이블(nestied table)
 * VARRAY(variable-size array)
TYPE
 ① 연관 배열 이름
IS TABLE OF  ② 자료형 [NOT NULL] INDEX BY ③ 인덱스형

MADANG

--판매된 도서번호, 도서명, 출판사명, 판매일자, 판매금액,고객번호, 고객명
SELECT BOOKID,BOOKNAME,PUBLISHER,ORDERDATE,SALEPRICE,CUSTID, NAME
FROM BOOK NATURAL JOIN ORDERS NATURAL JOIN CUSTOMER;


SELECT BOOKID, BOOKNAME,PUBLISHER, ORDERDATE, SALEPRICE,CUSTID, NAME
FROM BOOK JOIN ORDERS USING(BOOKID) JOIN CUSTOMER USING(CUSTID);

SELECT B.BOOKID, BOOKNAME,PUBLISHER,ORDERDATE, SALEPIRCE, C.CUSTID, NAME
FROM BOOK B JOIN ORDERS O ON(B.BOOKID =O.BOOKID) JOIN CUSTOMER C ON(O.CUSTID=C.CUSTID);

SELECT B.BOOKID,BOOKNAME,PUBLISHER,ORDERDATE,SALEPRICE,C.CUSTID,NAME
FROM BOOK B,ORDERS O,CUSTOMER C
WHERE O.CUSTID = C.CUSTID;
SCOTT

--1. EMP와 DEPT TABLE을 JOIN 하여 부서번호, 부서명, 이름, 급여를 출력하라
SELECT DEPTNO,DNAME,DNAME,SAL
FROM EMP NATURAL JOIN DEPT; --공통속성 자연스럽게 조인,소속X

SELECT DEPTNO, DNAME,ENAME, SAL
FROM EMP JOIN DEPT USING(DEPTNO); --공통속성 자연스럽게 조인, 소속 X

SELECT DEPTNO, DNAME,ENAME, SAL
FROM EMP E INNER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO); --소속 밝혀라

SELECT E.DEPTNO,DNAME,ENAME, SAL
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;  --소속 밝혀라


--2.이름이 'ALLEN'인 사원의 부서명을 출력하라
SELECT DNAME
FROM EMP NATURAL JOIN DEPT
WHERE ENAME ='ALLEN';

SELECT DNAME
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE ENAME ='ALLEN';

SELECT DNAME
FROM EMP E JOIN DEPT D ON(D.DEPTNO =E.DEPTNO)
WHERE ENAME ='ALLEN';

SELECT DNAME
FROM EMP E, DEPT D
WHERE ENAME ='ALLEN' AND E.DEPTNO = D.DEPTNO;

--3. DEPT TABLE에 있는 모든 부서를 출력하고, 
--EMP TABLE에 있는 DATA와 JOIN 하여 모든 사원의 이름, 부서번호, 부서명,급여를 출력하라
SELECT ENAME,DEPTNO,DNAME,SAL
FROM EMP E FULL OUTER JOIN DEPT E ON(D.DEPTNO=E.DEPTNO);

--4. EMP TABLE에 있는 EMPNO와 MGR을 이용하여 서로의 관계를 다음과 같이 출력하라 SMTH의 매니저는 FORD이다
SELECT E.ENAME ||'의 관리자는'|| M.ENAME 
FROM EMP E, EMP M
WHERE E.MGR =M.EMPNO;

--5.'ALLEN'의 직업과 같은 사람의 이름, 부서명, 급여, 근무지,직업을 출력하라
SELECT DNAME,E.DEPTNO,SAL,LOC,JOB
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO AND JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN');

--6.'JONES'가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사일,급여를 출력하라
SELECT DEPTNO,ENAME,HIREDATE,SAL
FROM EMP
WHERE DEPTNO =(SELECT DEPTNO FROM EMP WHERE ENAME='JONES');


--7.전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일, 근무지, 급여를 출력하라
SELECT EMPNO,ENAME,DNAME,HIREDATE,LOC,SAL
FROM EMP E, DEPT D
WHERE SAL> (SELECT AVG(SAL) FROM EMP); 

--7.1 각 부서별 최고 임금 출력
SELECT DEPTNO,MAX(SAL)
FROM EMP
GROUP BY DEPTNO;

--7.2 각 부서별 최고 임금을 받는 사원들의 부서번호와 임금,사원명 출력
SELECT DEPTNO,SAL,ENAME
FROM EMP
WHERE (DEPTNO, SAL) IN(SELECT DEPTNO,MAX(SAL)
                       FROM EMP
                       GROUP BY DEPTNO);

--8. 10번 부서 사람들 중에서 20번 부서의 사원과 같은 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일,근무지를 출력하라
SELECT E.DEPTNO,ENAME,DNAME,HIREDATE,LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = 10 AND E.DEPTNO=D.DEPTNO AND JOB IN(SELECT JOB FROM EMP WHERE DEPTNO=20);

--9. 10번 부서 중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일,근무지를 출력하라
SELECT EMPNO,ENAME,DNAME,HIREDATE, LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO =10 AND JOB NOT IN (SELECT JOB FROM EMP WHERE DEPTNO=30);

--10.10번 부서와 같은 일을 하는 사원의 사원번호, 이름 ,부서명,근무지,급여를 급여가 많은 순으로 출력하라
SELECT EMPNO, ENAME,DNAME,LOC, SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND JOB IN (SELECT JOB FROM EMP WHERE DEPTNO=10)
ORDER BY SAL DESC;

--11.'MARTIN'이나 SCOTT의 급여와 같은 사원의 사원번호, 이름, 급여를 출력하라
SELECT DEPTNO,ENAME,SAL 
FROM EMP
WHERE SAL IN (SELECT SAL FROM EMP WHERE ENAME IN('MARTIN','SCOTT'));

--12.급여가 30번 부서의 최고 급여보다 높은 사원의 사원번호,이름,급여를 출력하라
SELECT DEPTNO,ENAME,SAL
FROM EMP
WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);

--13.급여가 30번 부서의 최저 급여보다 높은 사원의 사원번호,이름,급여를 출력하라
SELECT DEPTNO,ENAME,SAL
FROM EMP
WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);

번외 문제! SCOTT에 테이블을 생성

-- v테이블 생성
create table v
(vname varchar2(10),
vid number primary key);

-- s테이블 생성
create table s
(sid number primary key);

-- vp 테이블 생성
create table vp
(vpid number primary key,
vid number,
sid number,
foreign key (vid) references v(vid),
foreign key (sid) references s(sid)
 );

-- v 테이블에 값 삽입
insert into v values('v1', 1);
insert into v values('v2', 2);
insert into v values('v3', 3);
insert into v values('v4', 4);
insert into v values('v5', 5); --실제 부품이 없음

-- s 테이블에 값 삽입
insert into s values(1);
insert into s values(2);
insert into s values(3);
insert into s values(4);
insert into s values(5);

-- vp 테이블에 값 삽입 (1번 차의 부품들, 부품번호: 1,2=>2개, 제공사:1번회사, 2번회사=>2개)
insert into vp values(1,1,1);
insert into vp values(2,1,2);

-- vp 테이블에 값 삽입 (2번 차의 부품들, 부품번호: 3,4,5 =>3개, 제공사:2번회사,3번회사=>2개)
insert into vp values(3,2,3);
insert into vp values(4,2,2);
insert into vp values(5,2,3);

-- vp 테이블에 값 삽입 (3번 차의 부품들, 부품번호: 6,7=>2개, 제공사:4번회사=>1개)
insert into vp values(6,3,4);
insert into vp values(7,3,4);

-- vp 테이블에 값 삽입 (4번 차의 부품들, 부품번호: 3,4,5, 제공사:5번회사=>1개)
insert into vp values(8,4,5);
자동차별 부품의 갯수, 회사의 갯수(중복 제거)
단, 부품회사가 두군데 이상인 자동차에 대한 정보만 출력

VID :자동차번호
VPID:자동차 부품 번호
SID:공급업체 번호

SELECT VID, COUNT(DISTINCT VPID),COUNT(DISTINCT SID)
FROM VP
GROUP BY VID
HAVING COUNT(DISTINCT SID) >=2;

'Coding > 데이터베이스' 카테고리의 다른 글

[코딩테스트 연습] JOIN문  (0) 2022.09.27
QUERY 예제 총 복습 _1  (0) 2022.09.26
PL/ SQL 구조  (1) 2022.09.19
[오라클] 마당서점 복습하기2 : 조인함수,서브쿼리사용  (0) 2022.09.05
[오라클] 마당서점  (0) 2022.08.29