MySQL를 설치하고, SQL 쿼리 문제 풀기를 시작했다.
쿼리는 직접 날려봐야 한다고 하니 차근차근 풀고 기록할 예정이다 :)
데이터는 '공판장품목별입출고현황', '공판장품목별재고현황' 두가지를 주셨고, 데이터 정보는 아래와 같다.
#데이터 베이스 지정해주기
use seongdong데이터베이스를 상단에 지정해주면 이후로는 따로 입력해주지 않아도 된다.
#1.공판장품목별입출고현황 데이터를 전체 조회하세요.
use seongdong;
select *
from 공판장품목별입출고현황;select : 불러올 컬럼명
from : 컬럼을 불러올 데이터 베이스
" * " 는 전체 column을 볼 때 사용한다.
특정 열을 볼 때는 컬럼명을 입력한다.
#2.공판장품목별재고현황 데이터 중 기준일자가 "2023.9.12"인 데이터를 조회하세요.(재고량이 높은 순서대로)
select *
from 공판장품목별재고현황
where stock_date='2023.9.12';where : 특정 조건
#3.공판장품목별입출고현황 데이터 중 상태가공분류가 냉동이고 기준일자가 “2023.9.1”이며 입,출고량이 0인 데이터는 제외하고 조회하세요.
select *
from seongdong.공판장품목별입출고현황
where 공판장품목별입출고현황.product_category_name ="(냉동)"
and stock_date ='2023.9.1'
and stock_quantity != 0
and shipment_quantity !=0;
#4.공판장품목별재고현황 테이블에서 냉동이거나 냉장인 수산물품목의 전체 재고량 무게가 2번째로 높은 물류센터는 어디인지 확인하세요.
select warehouse_name, warehouse_code, sum(inventory_quantity_kg)
from seongdong.공판장품목별재고현황
where product_category_name in ('(냉동)','(냉장)')
group by warehouse_name, warehouse_code
order by sum(inventory_quantity_kg) desc
limit 1
offset 1;where 절에서 사용한 in( , ) 은 or를 다르게 표현한 것이다.
의미는 product_category_name의 값이 (냉동)이거나 (냉장)인 값이다.
group by로 그룹핑을 하고, order by로 정렬할 조건을 입력한다. asc는 오름차순, desc는 내림차순이다.
limit 는 행을 몇개 볼 것이지를 보여준다. limit 1은 첫번째 행만 보여준다.
offset은 위에서부터 특정 갯수의 행을 제외하고 보여준다. offset 1은 첫번째 행을 제외하고 값을 표시한다.
#5.공판장품목별재고현황에서 공판장 코드가 “108100”인 수산물 품목 중에서 누적 재고량이 가장 많은 수산물 품목과 날짜를 확인하세요.
select warehouse_code, warehouse_name, stock_date, product_code, product_name, sum(inventory_quantity) as 누적재고량
from seongdong.공판장품목별재고현황
where warehouse_code="108100"
group by warehouse_code, warehouse_name, stock_date, product_code, product_name
order by sum(inventory_quantity) desc
limit 1;
#6.공판장품목별입출고현황 테이블과 공판장품목별재고현황 테이블을 이용해 기준 일자가 2023.9.2. 인 공판장품목별 수산물품목에 대한 입,출고량과 재고량을 모두 구하세요.
SELECT
t.warehouse_code,
t.warehouse_name,
t.product_code,
t.product_name ,
i.inventory_quantity,
t.stock_quantity,
t.shipment_quantity
FROM
seongdong.공판장품목별입출고현황 t
LEFT JOIN
seongdong.공판장품목별재고현황 i
ON
t.warehouse_code = i.warehouse_code
AND t.product_code = i.product_code
WHERE t.stock_date = "2023.9.2"
AND i.stock_date = "2023.9.2";left join은 왼쪽에 있는 테이블을 메인을 하고, 그 옆에 테이블을 붙이는 것이다.
공판장품목별입출고 현황 테이블을 메인으로 하고, 해당 테이블에 없는 재고량 컬럼을 공판장품목별재고현황 테이블에서 가져와 붙였다.
두 테이블에 동일하게 있는 컬럼을 구분하기 위해 a,b 등 문자를 "."과 함께 붙여준다.
on은 key값을 지정하는 것과 같다. 어떤 컬럼을 기준으로 붙일지 정의해준다. "어떤 기준으로 붙일까?"에 대한 것.
on 까지가 데이터를 조인해서 새로운 테이블을 만든 것!
이후에 where에는 그 데이터에서 보고 싶은 데이터의 조건을 입력해준다.
#7.공판장품목별입출고현황 테이블에서 공판장 별 상태가공분류코드가 몇 개 존재하는지 확인하세요.
#column : 공판장코드, 공판장명, 상태가공분류 개수
select warehouse_code, warehouse_name, count(distinct product_category)
from 공판장품목별입출고현황
group by warehouse_code, warehouse_name;distinct 는 중복값을 제거하는 것으로 상태가공분류코드 종류를 중복되지 않고 카운팅하도록 한다.
#8.모든 물류센터에 대해서 보관 방법이 냉동이고 누적 입고량이 1000 이상인 상품에 대한 정보를 나타내는 테이블을 생성하세요
#column : 공판장코드, 공판장명, 품목코드, 품목명, 상태가공분류명, 누적 입고량
select warehouse_code, warehouse_name, product_code, product_name, product_category_name, sum(stock_quantity) as 누적입고량
from 공판장품목별입출고현황
where product_category_name='(냉동)'
group by warehouse_code, warehouse_name, product_code, product_name, product_category_name
having sum(stock_quantity)>='1000'where와 having은 둘 다 조건을 지정하는 것이지만 차이가 있다.
1) where는 그룹핑 하기 전에 조건을 지정하는 것으로 from 바로 뒤에 오고,
having은 그룹핑 후에 조건에 맞는 데이터를 뽑는다.
2) where는 컬럼명을 바로 사용할 때 사용하며, sum, count 등의 연산이 되 컬럼은 사용하지 못한다.
그럴 때는 having을 사용한다.
앞으로 수업시간에 강사님께서 주신 문제들을 복습하면서 누군가에게 설명해 줄 수 있도록 공부해야겠다!