[SQL][TIL] ADSQL문제풀이

Q_ 고객 테이블(custs)을 이용하여 다음 조건에 맞는 고객을 검색하세요.
가) 검색 컬럼
고객번호, 이름, 전화번호, 신용한도, 성별
나) 조건
1 신용한도가 5000이상 9000 이하인 고객
2 성별 컬럼이 여성(F)이거나 NULL인 고객
3 위 두 조건을 모두 만족해야 합니다.

select cust_id, fname, phone, credit_limit, gender
from custs
where credit_limit between 5000 and 9000
and (gender = 'F' or gender is null);

 

---

Q_ 고객 테이블(custs)과 주문 테이블(orders)을 이용하여 다음 조건의 결과를 검색하세요.
가) 검색 컬럼
고객이름(lname), 주문번호, 주문일자, 전화번호, 주문 금액
나) 조건
online으로 주문한 고객

select lname, order_id, order_Date, phone, order_total
from custs c, orders o
where c.cust_id = o.cust_id
and o.order_mode = 'online';

---

Q_ 주문 테이블(orders)을 활용하여 아래의 조건에 맞는 결과를 검색하세요.
가) 컬럼 검색
판매사원번호, 고객번호, 주문 금액
나) 조건
1 direct로 판매된 제품의 주문 금액의 평균 값 보다 주문 금액이 적은 경우
2 ONLINE으로 주문된 경우는 제외
3 위 두 조건을 모두 만족하는 조건 검색
다) 정렬
주문 금액을 내림차순 정렬

select sales_rep_id, cust_id, order_total
from orders
where order_total < (select avg(order_total)
                     from orders
                     where order_mode = 'direct')
and order_mode != 'online'
order by order_total desc;

---

Q_ 상품 테이블(prods) 주문 테이블(orders) 주문 상세 테이블(order_items), 고객 테이블(custs)을 사용하여 상품을 구매했다가 취소한 고객의 번호와 이름, 상품번호, 주문일자, 주문했던 수량, 주문 단가 및 금액을 검색하세요.
가) 컬럼 검색
고객번호, 이름, 상품번호, 상품이름, 주문일자,
주문 수량, 주문 단가, 주문 금액 (quantity*unit_price)
나) 조건
주문 취소 내역이 있는 정보 검색
다) 정렬
고객번호, 주문일자, 상품번호 컬럼으로 오름차순 정렬

select c.cust_id, c.lname, p.prod_id, p.prod_name, i.quantity, i.unit_price, 
       i.quantity * i.unit_price as ant
from prods p, orders o, order_items i, custs c
where exists (select 1
              from order_cancel
              where order_id = i.order_id
              and prod_id = i.prod_id)
and c.cust_id = o.cust_id
and o.order_id = i.order_id
and i.prod_id = p.prod_id
order by c.cust_id, o.order_date, p.prod_id;

// order_cancel 테이블은 사용자에게 보여질 용도가 아니라, 필터링에만 사용되면 된다.

---

// exists 연산자는 항상 Correlated subquery를 가져야 하는건가..?  -> 아니다

① empno = 7788인 사원이 존재하므로 main query가 출력됨

select 'YES'
from dual
where exists (select 1
              from emp
              where empno = 7788);

② empno = 10000 인 사원은 존재하지 않으므로, main query가 출력되지 않음. 

select 'YES'
from dual
where exists (select 1
              from emp
              where empno = 10000);

         

③ correlated subquery가 같이 사용됨

select *
from dept d
where exists (select 1
              from emp
              where deptno = d.deptno);

---

// order_cancel 테이블과 order_items 테이블이 두개의 컬럼이 연결돼있으면, 항상 where절에 조건이 두개가 되는건가..?

---

Q_ 상품 테이블(prods)과 주문 상세 테이블(order_items), 주문 테이블(orders), 고객 테이블(custs)을 이용하여 미국에서 거주하는 고객이 주문한 상품의 번호와 이름, 주문 수량, 주문 단가, 주문 금액을 조회하세요.
가) 검색 컬럼
주문번호, 상품번호, 상품이름, 주문수량, 주문단가, 주문금액 (quantity * unit_price)
나) 조건
미국에 거주중인 고객이 주문한 정보를 검색
다) 정렬
주문금액 컬럼을 내림차순으로 정렬

select i.order_id, i.prod_id, p.prod_name, i.quantity, i.unit_price, 
       i.quantity * i.unit_price as ant
from prods p, order_items i, orders o, custs c
where c.cust_id in (select cust_id
                    from custs
                    where country = 'USA')
and c.cust_id = o.cust_id
and o.order_id = i.order_id
and i.prod_id = p.prod_id
order by order_total desc;