sql 문제 모음

2021 제로베이스 SQL 온라인 완주반에서 실행했던 과제, 데이터 베이스를 접속해서 해당 스키마가 있어야 실행됨.

작년에 작성했던 거지만 파일정리하면서 있길래 한 번 블로그에올려봤다.
당시 못풀었던 문제들이 개발자가 되고난 지금 풀 능력이 생겼을지 궁굼하지만 해당 DB 접속정보가 없어서 아쉽.

---

 

 

문제) 각 제품 가격을 5 % 줄이면 어떻게 될까요?

답

 

SELECT retailprice AS ORIGINAL_PRICE, retailprice - (retailprice * 0.05) AS SALE_PRICE
FROM products p;

 

 

 

문제) orders 테이블을 활용하여, 고객번호가 1001 에 해당하는 사람이 employeeid 가 707인 직원으로부터 산 주문의 id 와 주문 날짜를 알려주세요. (주문일자가 오름차순으로 정렬하여, 보여주세요.)

답

SELECT ORDERNUMBER as ORDER_ID, ORDERDATE

FROM ORDERS

WHERE CUSTOMERID = 1001 AND EMPLOYEEID = 707

ORDER BY ORDERDATE ASC;

 

결과: ORDER_ID는 총 12개의 결과가 나옴

 

 

문제) vendors 테이블을 이용하여, 벤더가 위치한 state 주가 어떻게 되는지, 확인해보세요. 중복된 주가 있다면, 중복제거 후에 알려주세요.

답

SELECT DISTINCT VENDSTATE

FROM VENDORS;

--결과: VENDSTATE는 CA, AK, TX, MO, NY, WA가 있다.

 

 

 

문제) products 테이블을 활용하여, productdescription에 상품 상세 설명 값이 없는 상품 데이터를 모두 알려주세요.

답

SELECT *

FROM PRODUCTS

WHERE PRODUCTDESCRIPTION IS NULL;

 

--결과: 총 40개 상품 중 모든 상품의 productdescription가 없는 것으로 확인.

 

 

문제) customers 테이블을 이용하여, 고객의 id 별로, custstate 지역 중 WA 지역에 사는 사람과 WA 가 아닌 지역에 사는 사람을 구분해서 보여주세요. (customerid 와, newstate_flag 컬럼으로 구성해주세요. newstate_flag 컬럼은 WA 와 OTHERS 로 노출해주시면 됩니다.)

답 

SELECT customerid,

CASE WHEN custstate='WA' then 'WA'

ELSE 'OTHERS' END AS NEWSTATE_FLAG

FROM customers c

ORDER BY customerid;

 

 

문제) 주문일이 2017-09-02 일에 해당 하는 주문건에 대해서, 어떤 고객이, 어떠한 상품에 대해서 얼마를 지불하여 상품을 구매했는지 확인해주세요.

답 

select c.custfirstname || ' ' || c.custlastname as customer_name, 
		p.productname, 
        od.quotedprice*od.quantityordered as amount
from orders o
inner join customers c on o.customerid = c.customerid
inner join order_details od on o.ordernumber = od.ordernumber
inner join products p on od.productnumber = p.productnumber
where date(orderdate) = '2017-09-02';

 

 

 

 

문제) 헬멧을 주문한 적 없는 고객을 보여주세요. 헬맷은, Products 테이블의 productname 컬럼을 이용해서 확인해주세요.

답

select c.*
from customers c
where not exists(
    select 1 from customers c2
    inner join orders o2 on c2.customerid = o2.customerid
    inner join order_details od2 on o2.ordernumber = od2.ordernumber
    inner join products p2 on od2.productnumber = p2.productnumber
    and p2.productname like '%Helmet'
    where c.customerid = c2.customerid
);

 

 

문제) 모든 제품 과 주문 일자를 나열하세요. (주문되지 않은 제품도 포함해서 보여주세요.)

답 

 

select p.productname, o.orderdate
from products p
full outer join order_details od
on p.productnumber = od.productnumber
full outer join orders o
on o.ordernumber = od.ordernumber
order by p.productname;

 

문제) 대여점(store)별 영화 재고(inventory) 수량과 전체 영화 재고 수량은? (union all)

답

--대여점 별 영화재고 수량

select store_id, count(inventory_id) from inventory i
group by store_id
union all
select null, count(*) --전체 영화 재고수량
from inventory i ;

 

문제) 타이어과 헬멧을 모두 산적이 있는 고객의 ID 를 알려주세요. (타이어와 헬멧에 대해서는 , Products 테이블의 productname 컬럼을 이용해서 확인해주세요.)

 

답

select c.customerid
from customers c
where not exists(
    select 1 from customers c2
    inner join orders o2 on c2.customerid = o2.customerid
    inner join order_details od2 on o2.ordernumber = od2.ordernumber
    inner join products p2 on od2.productnumber = p2.productnumber
    and p2.productname like '%Helmet'
    and p2.productname like '%Tires'
    where c.customerid = c2.customerid
);

 

 

문제) 1번 주문 번호에 대해서, 상품명, 주문 금액과 1번 주문 금액에 대한 총 구매금액을 함께 보여주세요.

답

 

select * from orders o ;

select * from order_details od ;
select * from products p ;
select od.ordernumber, p.productname,od.quotedprice,od.quantityordered,od.quotedprice*od.quantityordered as order_amount
from order_details od inner join products p
on od.productnumber = p.productnumber
where ordernumber =1
union all
select null,null,null,null,sum(od.quotedprice*od.quantityordered) as total
from order_details od inner join products p
on od.productnumber = p.productnumber
where ordernumber =1
;

 

 

문제) 주문일자가 2017/09/01 ~ 2017/09/30 일에 해당하는 주문에 대해서 주문일자와 고객별로 주문 수를 확인해주세요.(또한 고객별 주문 수도 함께 알려주세요.)

답

select * from order_details od ;
select * from orders;

select orderdate,customerid, count(ordernumber) -- 주문일자와 고객별로 주문 수
from orders
where orderdate between '2017-09-01' and '2017-09-30'
group by customerid, orderdate
union
select null, customerid, count(ordernumber) -- 고객별 주문 수
from orders
where orderdate between '2017-09-01' and '2017-09-30'
group by customerid
order by customerid, orderdate
;

 

문제) 주문일자가 2017/09/01 ~ 2017/09/30일에 해당하는 주문에 대해서, 주문일자와 고객별로 주문 수를 확인해주세요.(또한 주문일자별 주문 수도 함께 알려주시고, 전체 주문 수도 함께 알려주세요.)

답

 

select orderdate,customerid, count(ordernumber) -- 주문일자와 고객별로 주문 수
from orders
where orderdate between '2017-09-01' and '2017-09-30'
group by rollup (orderdate, customerid)
order by orderdate, customerid;

 

문제) 2017년도의 주문일 별 주문 금액과, 월별 주문 총 금액을 함께 보여주세요.(동시에 일별 주문 금액이 월별 주문 금액에 해당하는 비율을 같이 보여주세요. (analytic function 활용) - ratio_to_report, perenct_rank

select * from order_details od ;

select o.orderdate, sum(od.quotedprice*od.quantityordered) as total,
from order_details od inner join orders o
on od.ordernumber = o.ordernumber
where o.orderdate between '2017-01-01' and '2017-12-31'
group by o.orderdate
order by o.orderdate
;

 

 

 

문제) 4개의 이상 업체에서 판매하는 상품번호는 무엇인가요?

select * from vendors v ;
select * from products p ;
select * from product_vendors pv ;


select productnumber, count(vendorid)
from product_vendors pv
group by productnumber
having count(vendorid)>=4;

 

 

문제) 2018년1월11일에 1024 고객아이디를 가진 사람이 652번에 지불한, 주문 금액은 얼마인가요? (소수점 금액을 제외한 값을 알려주세요.)

--hint) 주문금액은 order_details 테이블의 quotedprice (상품개당 가격) , quantityordered( 상품 주문 갯수) 를 활용하여 확인 해야합니다.

 

select *
from order_details od ;
select * from orders o ;


select od.ordernumber , sum(od.quotedprice*od.quantityordered)
from order_details od inner join orders o
on od.ordernumber = o.ordernumber
where o.customerid = 1024
and o.orderdate = '2018-01-11'
group by od.ordernumber
having od.ordernumber = 652
;

 

문제) 고객의 정보와 , 직원의 정보를 하나의 테이블로 표현하고자 합니다. 빈칸에 들어갈 값으로 알맞은 것을 모두 알려주세요.

select custfirstname || ',' || custlastname as customer, 'Customer' as type
from customers
union
select empfirstname || ',' || emplastname as satff, 'Staff' as type
from employees e


select custfirstname || ',' || custlastname as customer, 'Customer' as type
from customers
union all
select empfirstname || ',' || emplastname as satff, 'Staff' as type
from employees e

 

 

문제) 고객이 구매한 제품의 가격 ( = 상품의 개당 가격)이, "전체 제품리스트의 전체 평균 가격" 보다 높은 제품을 모두 알려주세요.

--Hint) 제품의 평균 소매가격은 - retailprice 에 대해서 평균 가격을 확인하시면 됩니다.

select * from products p ;
select avg(retailprice)
from products p;
select * from order_details od ;


select od.productnumber , od.quotedprice
from order_details od inner join products p
on od.productnumber=p.productnumber
where quotedprice > (
    select avg(p.retailprice)
    from products p
) ;

 

 

문제) 상품별 주문 수 와 전체 주문수를 함께 보여주세요.

 

select productnumber , count(distinct ordernumber) from order_details od
group by grouping sets((productnumber),())
order by productnumber ;

 

 

상품별 주문수를 구하때는 productnumber를 카운트하는게 아니라

주문번호(ordernumber)를 distinct 로 카운트해야 중복되지 않고 셀수있다.