Day4

Day4 ADSQL.Quiz

Q1.다음 조건에 맞는 고객을 검색하시오.

검색컬럼 )-고객번호, 이름, 전화번호, 신용한도, 성별

조건)1.신용한도 5000이산 9000이하

2.성별 컬럼이 여성이거나 null인 경우

---

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

---

Q2.고객 테이블과 주문테이블을 이용하여 다음 조건의 결과를 검색하시오.

조건)1.고객이름, 주문번호, 주문일자, 전화번호, 주문금액

2.online으로 주문한 고객

---

select lname, order_id, order_date, phone, order_total
from custs c join orders o 
on c.cust_id = o.cust_id
and order_mode = 'online';

---

Q3.주문 테이브릉 활용하여 아래의 조건에 맞는 결과를 검색하시오.

조건)1.판매사원번호, 고객번호, 주문금액 검색

2.direct로 판매된 제품의 주문금액의 평균 값보다 주문 금액이 적은 경우

3.online으로 주문된 경우 제외

4. 주문 금액을 내림차순 정렬

---

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

---

Q4.상품테이블, 주문테이블, 주문상세 테이블, 고객테이블을 이용하여 상품을 구매했다가 취소한 고객을 검색하시오

조건) 1.고객번호, 이름, 상품번호, 상품이름, 주문일자, 주문수량, 주문 단가, 주문금액(quantity*unit_price)

조건2. 주문 취소 내역이 있는 정보 검색

조건3. 고객번호, 주문일자, 상품번호 컬럼으로 오름차순 정렬

---

SELECT
    c.cust_id, c.lname,
    p.prod_id, o.order_date,
    oi.quantity, oi.unit_price,
    (quantity*unit_price) AS "AMT"
FROM
    custs  c
    , orders o
    , order_items oi
    , prods p
WHERE
    c.cust_id = o.cust_id
    AND o.order_id = oi.order_id
    AND oi.prod_id = p.prod_id
    #// order_cancel 필터링
    AND (o.order_id,oi.prod_id) IN (SELECT order_id, prod_id
                                      FROM order_cancel
                                WHERE order_id = o.order_id
                                       AND prod_id = p.prod_id

---

Q5.상품 테이블과 주문 상세 테이블, 주문테이블, 고객테이블을 이요하여 미국에서 거주하는 고객이 주문한 상품의 번호와, 이름 주문수량, 주문 단가, 주문 금액을 조회하세요.

조건)1.주문번호 상품번호, 상품이름, 주문수량, 주문단가, 주문금액(quantity*unit_price)

2. 미국에 거주중인 고객이 주문한 정보를 검색

3. 주문금액 컬럼을 내림차순으로 정렬

---

//1번
select order_id, prod_id, prod_name, quantity, unit_price, quantity*unit_price as AMT

from prods
join order_items using(prod_id)
join orders using(order_id)
join custs using(cust_id)
where country = 'USA'
order by AMT desc;

select *
from orders;
----------------------------------------------
//2번
select oi.order_id, oi.prod_id, p.prod_name, oi.quantity, oi.unit_price, (quantity*unit_price) as "AMT"

from prods p, order_items oi, orders o, custs c
where p.prod_id = oi.prod_id and
      oi.order_id = o.order_id and
      o.cust_id = c.cust_id and
      country = 'USA'
order by AMT desc;