-- view
create or replace view shoppings
as
select * from "Customers" c inner join "Orders" o
using (userid) inner join "Products" p
using (prodid);
-- 달콤비스킷을 제조한 업체가 만든 제품들의 이름과 단가 조회 -join으로 풀기 불가능
-- 하위 질의문으로 풀기
select maker from "Products"
where PRODNAME='달콤비스킷'; -- 한빛제과
select PRODNAME, PRICE from "Products"
where MAKER = '한빛제과'
and PRODNAME <> '달콤비스킷';
-- sql 문은 일반적으로 앞에서부터 실행하지만 괄호를 이용해 하위질의문 먼저 실행시킬 수 있음
select PRODNAME, PRICE from "Products"
where MAKER = (select maker from "Products"
where PRODNAME = '달콤비스킷');
-- 적립금이 가장 많은 고객의 이름과 적립금 조회
-- select name, milege from "Customers"
-- where name = (select NAME from "Customers"
-- where milege = 5000 ) ; -- 번거로움
select max(MILEGE) from "Customers";
select name, MILEGE from "Customers"
where MILEGE = ( select max(MILEGE) from "Customers");
-- 하위질의문 여러개의 값이라면 = 으로 불가능
-- banana 고객이 주문한 제품의 제품명과 업체 조회 -- banana고객이 누구인지 먼저 찾아야함.
select PRODNAME, MAKER from "Products"
where PRODID in (select PRODID from "Orders"
where USERID = 'banana');
-- banana 고객이 주문하지 않은 제품의 제품명과 업체 조회
select PRODNAME, MAKER from "Products"
where PRODID not in (select PRODID from "Orders"
where USERID = 'banana');
-- 판매데이터베이스에서 '대한식품'이 제조한 모든 제품의 단가보다 비싼제품의 제품명과 업체 조회
select PRODNAME, MAKER, price from "Products"
where PRICE > all (select PRICE from "Products"
where MAKER = '대한식품');
-- 가장 비싼제품보다 비싼 제품
-- select PRODNAME, MAKER, price from "Products"
-- where PRICE > (select max(PRICE) from "Products"
-- where MAKER = '대한식품');
-- 판매 데이터베이스에서 2022년 3월 15일에 제품을 주문한 고객의 이름을 검색
select name from "Customers"
where USERID in (select userid from "Orders"
where orderdate = '2013-03-15') ; -- 데이터양이 많을때는 힘듬, userid와 비교
(select userid from "Orders"
where orderdate = '2013-03-15');
-- exist로 작성 이거는 앞에서부터 실행됨. userid존재 여부만 비교
select name from "Customers" c
where exists (select * from "Orders" o
where ORDERDATE = '2013-03-15'
and c.USERID = o.USERID);
-- silver 등금인 회원의 마일리지보다 많은 회원의 이름조회
select name, MILEGE from "Customers"
where MILEGE > all (select MILEGE from "Customers" where GRADE = 'silver');
-- 2022년 3월 15일에 제품을 주문하지 않은 고객의 이름을 검색
select NAME
from "Customers" where not exists (select * from "Orders"
where ORDERDATE = '2013-03-15'
and "Customers".USERID="Orders".USERID) ;
-- 비상관/상관 서브질의문
-- 비상관 쿼리 : 서브쿼리는 메인쿼리에 값을 제공하는 역할담당
-- 상관 쿼리 : 서브쿼리는 메인쿼리의 값을 확인하는 역할담당 (exists) 성능이 더 좋음
-- 페이징 기법
-- select 컬럼들 from 대상
-- order by 컴럼 [ offset n rows, fetch first/last m rows only ]
select *
from EMPLOYEES;
select * from EMPLOYEES fetch first 10 rows only;
select * from EMPLOYEES fetch next 10 rows only;
-- 데이터 전체 행수 107
-- 페이지 당 출력 행수 10개
-- 총 페이지 수 : 11개
-- 현재 페이지 : 1
-- fetch 시작 행번호 : 1, 11, 21, .... , 101
--1페이지
select * from EMPLOYEES offset 0 rows fetch next 10 rows only ;
--2페이지
select * from EMPLOYEES offset 10 rows fetch next 10 rows only ;
--3페이지
select * from EMPLOYEES offset 20 rows fetch next 10 rows only ;
-- n 페이지
-- select * from EMPLOYEES offset a*(n-1) rows fetch first a rows only ;
-- EMPLOYEES 테이블에서 Kochhar의 급여보다 많은 사원의 정보를 사원번호,이름,담당업무,급여를 출력헤세요
select EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY from EMPLOYEES
where SALARY > (select SALARY from EMPLOYEES where LAST_NAME = 'Kochhar') ;
-- EMPLOYEES 테이블에서 급여의 평균보다 적은 사원의 사원번호,이름,담당업무,급여,부서번호를 출력하여라.
select EMPLOYEE_ID, LAST_NAME, SALARY, DEPARTMENT_ID from EMPLOYEES
where SALARY < (select round(avg(SALARY)) from EMPLOYEES);
select round(avg(SALARY)) from EMPLOYEES;
-- EMPLOYEES 테이블에서 100번 부서의 최소 급여보다 최소 급여가 많은 다른 모든 부서를 출력하라
select DEPARTMENT_ID from EMPLOYEES
where SALARY > (select round(min(SALARY)) from EMPLOYEES where DEPARTMENT_ID=100) and DEPARTMENT_ID is not null
group by DEPARTMENT_ID;
select round(avg(SALARY)) from EMPLOYEES where DEPARTMENT_ID=100;
-- 업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호를 출력하여라.
-- 단 업무별로 정렬하여라.
select EMPLOYEE_ID, LAST_NAME, JOB_ID, DEPARTMENT_ID from EMPLOYEES
where (JOB_ID , SALARY) in (select JOB_ID, min(SALARY) from EMPLOYEES
group by JOB_ID);
-- EMPLOYEES 과 DEPARTMENTS 테이블에서 업무가 SA_MAN 사원의 정보를 이름,업무,부서명,근무지를 출력헤세요
SELECT LAST_NAME, JOB_ID, DEPARTMENT_NAME, LOCATION_ID from EMPLOYEES
inner join DEPARTMENTS using (department_id)
where EMPLOYEES.JOB_ID in ( select JOB_ID from EMPLOYEES where JOB_ID='SA_MAN');
-- EMPLOYEES 테이블에서 (가장 많은 사원) 을 갖는 MANAGER의 사원번호를 출력헤세요
select EMPLOYEES.MANAGER_ID from EMPLOYEES inner join DEPARTMENTS using (department_id)
where DEPARTMENT_ID in
(select DEPARTMENT_ID from dc)
FETCH first 1 rows only;
create or replace view dc as select DEPARTMENT_ID, count(EMPLOYEE_ID) 사원수 from EMPLOYEES
group by DEPARTMENT_ID order by 사원수 desc;
-- 46. 사원수가 가장 많은 도시에서 근무하는 모든 사원들의 부서별 및 직무별 평균 연봉을 조회하라.
select DEPARTMENT_ID, JOB_ID, avg(SALARY) 평균연봉 from EMPDEPTLOC
where city = (select city from maxcityemp fetch first 1 rows only)
group by DEPARTMENT_ID, JOB_ID ;
select MANAGER_ID from EMPLOYEES where DEPARTMENT_ID = (
select DEPARTMENT_ID, count(EMPLOYEE_ID) 사원수 from empdeptloc group by DEPARTMENT_ID) ;
select MANAGER_ID, count(EMPLOYEE_ID) from EMPLOYEES
group by MANAGER_ID;
-- 사원번호가 123인 사원의 직업과 같고 사원번호가 192인 사원의 급여보다 많은 사원의
-- 사원번호,이름,직업,급여를 출력헤세요
-- 직업(JOB)별로 최소급여를 받는 사원의 정보를 사원번호,이름,업무,부서명을 출력헤세요
-- 조건1 :직업별로 내림차순정렬
-- EMPLOYEES 테이블에서 (50번 부서의 최소 급여)를 받는 사원보다 많은 급여를 받는
-- 사원의 사원번호,이름,업무,입사일자,급여,부서번호를 출력하세요
-- 단 50번은 제외
-- EMPLOYEES 테이블에서 (50번 부서의 최고 급여)를 받는 사원 보다 많은 급여를 받는 사원의
-- 사원번호,이름,업무,입사일자,급여,부서번호를 출력하세요
-- 단50번은 제외
-- 가장 비싼 도서의 이름을 조회
select max(price) from BOOKS;
select BOOKNAME from BOOKS where PRICE = 35000; -- X
select BOOKNAME from BOOKS where PRICE = (select max(price) from BOOKS); -- O
-- 도서를 구매한 적이 있는 고객의 이름을 조회
select distinct CUSTID from ORDERBOOKS;
select name from CUSTOMER
where CUSTID in ( select distinct CUSTID from ORDERBOOKS);
-- 대한미디어에서 출판한 도서를 구매한 사람의 이름을 검색
select BOOKID from BOOKS
where PUBLISHER = '대한미디어';
select CUSTID from ORDERBOOKS
where BOOKID in (3,4);
select name from CUSTOMER
where CUSTID in (select CUSTID from ORDERBOOKS
where BOOKID in ( select BOOKID from BOOKS
where PUBLISHER = '대한미디어'));
-- join문으로 풀기
select * from CUSTOMER c inner join ORDERBOOKS o
on c.CUSTID = o.CUSTID inner join BOOKS b
on o.BOOKID = b.BOOKID where PUBLISHER = '대한미디어';
-- 출판사별로 출판사의 평균도서가격보다 비싼 도서를 조회
select * from BOOKS;
select PUBLISHER, round(avg(PRICE)) from BOOKS group by PUBLISHER;
-- 출판사와는 상관없이 모든 도서를 기준으로 평균가격들보다 높은 도서조회
select BOOKNAME, PRICE from BOOKS where price > all (select round(avg(PRICE)) from BOOKS group by PUBLISHER);
-- 출판사에서 출간한 책들의 평균가격을 조사하고
-- 출판사에서 출간한 책들이 이것보다 비싼 도서 조회
select PUBLISHER, PRICE from BOOKS b1
where b1.PRICE > (select avg(b2.PRICE) from BOOKS b2 where b1.PUBLISHER=b2.PUBLISHER);
create or replace view bkabprice
as
select PUBLISHER, avg(PRICE) avgprice from BOOKS
group by PUBLISHER;
select b1.PUBLISHER, PRICE, avgprice from bkabprice b1 inner join BOOKS b2 on b1.PUBLISHER = b2.PUBLISHER
where PRICE > avgprice;
select bookname, PUBLISHER, price from BOOKS b1 where price > (select avg(price) from books b2 where b1.PUBLISHER = b2.PUBLISHER);
-- from절부터 시작, where조건을 거쳐 select문으로 검색
-- 도서를 주문하지 않은 고객의 이름 조회
select * from CUSTOMER c full outer join ORDERBOOKS o
using (custid) where orderid is null;
select * from CUSTOMER c inner join ORDERBOOKS o
using (custid);
-- (모든 고객 정보 - 도서를 주문한 고객) 차집합 연산
(select * from CUSTOMER) minus
(select * from CUSTOMER where CUSTID in (select CUSTID from ORDERBOOKS));
select * from CUSTOMER where CUSTID not in (select CUSTID from ORDERBOOKS);
-- 주문이 있는 고객의 이름, 주소 조회
-- 단, 상관질의문 (exist) 이용
select name, ADDRESS from CUSTOMER c where exists (select custid from orderbooks o where c.CUSTID=o.CUSTID);
-- 이결과가 있는지 없는지
select name, ADDRESS from CUSTOMER c where not exists (select custid from orderbooks o where c.CUSTID=o.CUSTID);