26. (데이터베이스) 스칼라 서브쿼리 연습 문제

1. 문제

1) image 테이블

2) favorite 테이블

3) member 테이블

4) tag 테이블

5) 문제

select id, img_url, content, 3 as '좋아요개수', 'ssar' as '유저이름'
from image;

2. 풀이

1) 좋아요 개수

select count(image_id)
from favorite
where image_id = 1;

select id, img_url, content, (select count(image_id)
from favorite
where image_id = i.id) as '좋아요개수', 'ssar' as '유저이름'
from image i;

2) 유저 이름

select username
from member
where id = 1;

select id, img_url, content, (select count(image_id)
from favorite
where image_id = i.id) as '좋아요개수', (select username
from member
where id = i.user_id) as '유저이름'
from image i;

3) 태그

select content
from tag
where image_id = 1;

select id, img_url, content, (select count(image_id)
from favorite
where image_id = i.id) as '좋아요개수', (select username
from member
where id = i.user_id) as '유저이름',
(select content
from tag
where image_id = i.id) as '태그'
from image i;