Coding/데이터베이스
[오라클] SQL기초 복습예제
찡콩찡
2022. 8. 22. 17:11
madang접속
--1. book테이블에서 축구가 들어가들어가 있는 책을 찾아보기
select *
from book
where bookname like '%축구%';
--2.고객의 정보
SELECT * FROM CUSTOMER;
--3. 도서의 수
SELECT COUNT (*) FROM BOOK;
--4. 주문테이블 확인
SELECT * FROM ORDERS;
--5.2014.7월 5일 이후에 판매된 건
SELECT *
FROM ORDERS
WHERE ORDERDATE >= '14/07/05';
--6.BOOK테이블에서 출판사 굿스포츠에서 출판한 책들
SELECT *
FROM BOOK
WHERE PUBLISHER = '굿스포츠';
--7.영국에 살고 있는 고객들의 정보
SELECT *
FROM CUSTOMER
WHERE ADDRESS LIKE '%영국%';
--8.영국에서 살고 있는 사람들이 주문한 책이름 (테이블 3개 필요)
SELECT BOOKNAME
FROM ORDERS O, CUSTOMER C, BOOK B
WHERE O.CUSTID = C.CUSTID
AND ADDRESS LIKE '%영국%' AND B.BOOKID=O.BOOKID;
--9.도서 정가가 만원이하인 도서구매자 명
SELECT NAME
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE C.CUSTID =O.CUSTID AND B.BOOKID =O.BOOKID AND PRICE <=10000;
--10.축구라는 이름이 들어간 도서를 구매한 고객들의 이름(WHERE절 사용)
SELECT DISTINCT NAME
FROM BOOK B, CUSTOMER C,ORDERS O
WHERE BOOKNAME LIKE '%축구%' AND B.BOOKID=O.BOOKID AND C.CUSTID=O.CUSTID;
--NATURAL JOIN
SELECT DISTINCT NAME
FROM BOOK B NATURAL JOIN ORDERS O NATURAL JOIN CUSTOMER C
WHERE BOOKNAME LIKE '%축구%';
--JOIN USING 니꺼내꺼 가리는게 필요가 없어 (공통으로 들어가는거 자동으로 써주기)
SELECT DISTINCT NAME
FROM BOOK B JOIN ORDERS O USING(BOOKID)
JOIN CUSTOMER C USING(CUSTID)
WHERE BOOKNAME LIKE '%축구%';
--JOIN ON 누구걸 쓰는지 콕 집어서 얘기해줘야 함
SELECT DISTINCT NAME
FROM BOOK B JOIN ORDERS O ON(B.BOOKID=O.BOOKID)
JOIN CUSTOMER C ON(C.CUSTID=O.CUSTID)
WHERE BOOKNAME LIKE '%축구%';
--11.판매가 15000이상인 도서를 구매한 구매자명 (WHERE)
SELECT NAME
FROM CUSTOMER C, ORDERS O
WHERE C.CUSTID =O.CUSTID AND SALEPRICE >= 15000;
--11.1 NATURAL JOIN
SELECT NAME
FROM CUSTOMER C NATURAL JOIN ORDERS O
WHERE SALEPRICE >= 15000;
--11.2 JOIN USING(소속 안밝힘, 따지지 말고 쓰자)
SELECT NAME
FROM CUSTOMER C JOIN ORDERS O USING(CUSTID)
WHERE SALEPRICE >= 15000;
--11.3 JOIN ON(소속을 밝혀라)
SELECT NAME
FROM CUSTOMER C JOIN ORDERS O ON(C.CUSTID=O.CUSTID)
WHERE SALEPRICE >= 15000;
--12. 도서번호 1번을 구매한 고객명, 고객주소
SELECT NAME, ADDRESS
FROM CUSTOMER C, ORDERS O
WHERE C.CUSTID=O.CUSTID AND BOOKID=1;
--12.1 NATURAL JOIN
SELECT NAME, ADDRESS
FROM CUSTOMER C NATURAL JOIN ORDERS O
WHERE BOOKID=1;
--12.2 JOIN USING
SELECT NAME, ADDRESS
FROM CUSTOMER C JOIN ORDERS O USING(CUSTID)
WHERE BOOKID=1;
--12.3 JOIN ON
SELECT NAME, ADDRESS
FROM CUSTOMER C JOIN ORDERS O ON(C.CUSTID=O.CUSTID)
WHERE BOOKID=1;
--13. 굿스포츠 출판사에서 출판한 판매가 10000원 이하인 도서명, 정가, 구매자명
SELECT DISTINCT BOOKNAME,PRICE,NAME
FROM CUSTOMER C,BOOK B, ORDERS O
WHERE PUBLISHER='굿스포츠' AND PRICE<=10000
AND O.CUSTID =C.CUSTID AND B.BOOKID = O.BOOKID;
--13.1 NAUTRAL JOIN
SELECT BOOKNAME,PRICE, NAME
FROM BOOK B NATURAL JOIN CUSTOMER C NATURAL JOIN ORDERS O
WHERE PUBLISHER='굿스포츠' AND PRICE<=10000;
--13.2 JOIN USING
SELECT BOOKNAME,PRICE, NAME
FROM BOOK B JOIN ORDERS O USING(BOOKID)
JOIN CUSTOMER C USING(CUSTID)
WHERE PUBLISHER='굿스포츠' AND PRICE<=10000;
--13.3 USING ON
SELECT BOOKNAME,PRICE, NAME
FROM BOOK B JOIN ORDERS O ON(B.BOOKID=O.BOOKID)
JOIN CUSTOMER C ON(C.CUSTID=O.CUSTID)
WHERE PUBLISHER='굿스포츠' AND PRICE<=10000;
--14. 제일 많이 팔린 도서명
SELECT BOOKID FROM ORDERS GROUP BY BOOKID
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM ORDERS GROUP BY BOOKID);
=================================================
SELECT BOOKID, C
FROM ( SELECT BOOKID, COUNT(*) C
FROM ORDERS
GROUP BY BOOKID
ORDER BY COUNT(*) DESC)BB
WHERE ROWNUM <=5;
=================================================
--14.1 전체도서번호
SELECT BOOKID
FROM BOOK;
--15.안팔린 도서번호 (전체도서번호 빼기 팔린 도서번호)
SELECT BOOKID
FROM BOOK
MINUS
SELECT BOOKID
FROM ORDERS;
--15.1전체도서번호 팔린도서번호가 아닌 것만 출력 ( not in 사용하기 )
SELECT BOOKID
FROM BOOK
WHERE BOOKID NOT IN (SELECT BOOKID FROM ORDERS);
--16.제일 많이 팔린 책번호와 이름을 출력하라
SELECT BB.BOOKID,RANK, BOOKNAME
FROM (SELECT BOOKID, RANK() OVER (ORDER BY COUNT(*) DESC) RANK
FROM ORDERS
GROUP BY BOOKID
ORDER BY COUNT(*) DESC) BB, BOOK
WHERE RANK = 1 AND BB.BOOKID=BOOK.BOOKID;
--17.책을 제일 많이 구매한 고객명
SELECT NAME
FROM CUSTOMER
WHERE CUSTID IN (SELECT CUSTID
FROM ORDERS
GROUP BY CUSTID
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM ORDERS
GROUP BY CUSTID);
--18.책 구매한 권수로 등수출력(RANK사용)
SELECT RANK() OVER (ORDER BY COUNT(O.CUSTID) DESC ) AS RANK, C.NAME, COUNT(*) || '권' AS CNT
FROM ORDERS O JOIN CUSTOMER C ON O.CUSTID = C.CUSTID
GROUP BY O.CUSTID, C.NAME
ORDER BY COUNT(*) DESC;
--19부서별 급여역순, 보너스 역순, 등수
SELECT DEPTNO,
DNAME,
ENAME,
SAL,
COMM,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, COMM DESC) RANK,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, COMM DESC) RANK
FROM EMP NATURAL JOIN DEPT
ORDER BY DEPTNO, SAL DESC, COMM DESC;
--20도서 판매 합계
SELECT SUM(SALEPRICE)
FROM ORDERS;
--21.어느 고객이 얼마나 주문했는지
SELECT CUSTID, SUM(SALEPRICE)
FROM ORDERS
GROUP BY CUSTID;
--21.2 어느 고객(고객번호, 고객명)이 얼마나 주문했나?
SELECT CUSTID, SUM(SALEPRICE)
FROM ORDERS O NATURAL JOIN CUSTOMER C
GROUP BY CUSTID, NAME;
--22.도서를 주문하지 않은 고객명, 전화번호
--1차: 주문한 고객번호 2차:전체 고객에서 주문한 고객 제외
SELECT CUSTID,NAME,PHONE
FROM CUSTOMER
WHERE CUSTID NOT IN (SELECT CUSTID FROM ORDERS);
--23.날짜별로 판매건수와 판매금액 합계
SELECT ORDERDATE, COUNT(*)|| '권' AS 판매권수, SUM(SALEPRICE) || '원' AS 판매금액
FROM ORDERS
GROUP BY ORDERDATE;
--24.도서 정가가(price)가 10000원이 아닌 것 정보 출력
select *
from book
where price <>10000;
--25. 도서 정가가 10000원~20000원 사이 도서 정보 출력
SELECT *
FROM BOOK
WHERE PRICE BETWEEN 10000 AND 20000;
--26. 판매 가격이 20000원 이상인 도서들의 번호와 이름
SELECT BOOKID, BOOKNAME
FROM BOOK
WHERE PRICE >=20000;
--27. 판매가격이 10000원 이상인 도서를 구매한 고객의 번호, 고객명
SELECT CUSTID, COUNT(*), NAME
FROM ORDERS NATURAL JOIN CUSTOMER
WHERE SALEPRICE>=10000
GROUP BY CUSTID, NAME;
SCOTT으로 접속
--1. MP 테이블에서 급여가 가장 많은 사람 3명
--정렬을 세워놓고 급여가 높은 3명을 뽑게끔해야해
--ROWNUM쓸때는 SELECT절에 괄호를 쓰고 시작하자
SELECT ENAME,SAL
FROM (SELECT *
FROM EMP
ORDER BY SAL DESC)
WHERE ROWNUM <=3;
--2.급여 등수대로 사원명, 사원번호, 급여, 등수 출력하기
SELECT ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) AS RANK,
DENSE_RANK() OVER (ORDER BY SAL DESC) AS DENSE_RANK
FROM EMP
ORDER BY SAL DESC;
--3.보너스 받는 사람들의 이름과 급여, 보너스 출력
SELECT ENAME, SAL,COMM
FROM EMP
WHERE COMM>0;
--4.보너스가 정해진 사람들의 이름과 급여, 보너스 출력
SELECT ENAME,SAL,COMM
FROM EMP
WHERE COMM IS NOT NULL;
도구 > 환경설정 : 날짜 RR로 변경하면 예: 14/07/05로 찾아봐도 검색결과가 잘 나온다.
