[UMC 4기] server 4주차

❗당근마켓 ERD

 

❗ERD 기반 쿼리문 만들기

create database `daangn_schema`;
 
#1. 개인 프로필 조회
select profile_image, nickname, manner_rate, repurchase_rate, response_rate from User
where User.idx = 1;
 
 
#'A:활성, R:예약, F:거래 완료, H:숨김'
#2. 판매 내역(판매중)
select U.nickname, P.title, P.price, P.place from UserProduct as UP
join User U on U.idx = UP.user_idx
join Product P on P.idx = UP.product_idx
where U.idx = 1 and UP.status = 'A'
GROUP BY P.idx;
 
 
 
#3. 판매 내역(거래 완료)
select U.nickname, P.title, P.price, P.place from UserProduct as UP
join User U on U.idx = UP.user_idx
join Product P on P.idx = UP.product_idx
where U.idx = 1 and UP.status = 'F'
GROUP BY P.idx;
 
 
#4. 판매 내역(숨김)
select U.nickname, P.title, P.price, P.place from UserProduct as UP
join User U on U.idx = UP.user_idx
join Product P on P.idx = UP.product_idx
where U.idx = 1 and UP.status = 'H'
GROUP BY P.idx;
 
 
#5. 관심 목록
select U.nickname, P.title, P.price, P.place from LikedProduct
join Product P on P.idx = LikedProduct.product_idx
join User U on U.idx = LikedProduct.user_idx
where U.idx = 1
GROUP BY P.idx;
 
 
#6. 상품 1개 조회
select U.nickname, U.manner_rate, P.title, P.price, P.content from UserProduct as UP
join User U on U.idx = UP.user_idx
join Product P on P.idx = UP.product_idx
where P.idx = 1;
 
 
#7. 상품 전체 조회(리스트)
select title, price, content, region_name, place from Product
join Region R on R.idx = Product.region_idx;