스키마설계 실습 movie_2

2024.12.05 - [데이터베이스/MySQL 실습문제] - 스키마설계 실습 movie_1

스키마설계 실습 movie_1

 

 

Foreign Keys 설정하기

  review

     - movieId 

     - userId

 

Foreign Keys 설정 ERD 확인 

 

 

 

 

문제 1번 

  아래 그림에 들어가야 할 SQL 문을 작성하세요.

insert into user (email, password, nickname, gender) 
values ('exma@yahoo.com', 'JrCLlDuTwHkp', '홍길동', 0);

문제 2번 

  아래 그림에 들어가야 할 SQL 문을 작성하세요.

 

 

스키마 실습 1번에서 제공되었던 db 가 없는 경우 생성하여 진행 하세요

-- 1. 영화명, 리뷰갯수, 별점평균 정보를 보여준다.
-- 2. 한번 요청시마다 25개씩 불러오도록 만든다.
-- 3. 정렬조건이 있다( 리뷰갯수 내림차순정렬 / 별점평균 내림차순 정렬

-- 리뷰갯수정렬
SELECT m.id as movie_id, m.title, 
	count(r.id) as cnt_review,
	IFNULL(avg(r.rating),0)as avg_rating, 
	if(f.id is null , 0,1) as is_favorite
FROM movie m
left join review r 
on r.movieid = m.id
left join favorite f 
on f.movieid= m.id and f.userid=301
group by m.id
order by cnt_review desc;

-- 평점 정렬
SELECT m.id as movie_id, m.title, 
	count(r.id) as cnt_review,
	IFNULL(avg(r.rating),0)as avg_rating, 
	if(f.id is null , 0,1) as is_favorite
FROM movie m
left join review r 
on r.movieid = m.id
left join favorite f 
on f.movieid= m.id and f.userid=301
group by m.id
order by avg_rating desc;

-- 4.하트는 즐겨찾기다. (a. 하트를 누르면 내 즐겨찾기에 저장된다)
-- 즐겨찾기는 1개만 저장되고 추가적으로 저장되지 않도록 해야 한다
-- ( 이러한 문제를 해결하기 위해 유니크키 설정 적용을 해야 한다)
-- 1회 터치는 즐겨찾기, 다시한번 터치 할 경우 즐겨찾기 해제

SELECT *
FROM favorite;

insert into favorite (userid,movieid) 
values (1,1);

-- 5 즐겨찾기 해제 301이 영화 아이디 1번을 즐겨 찾기 해제 하는 경우


DELETE FROM favorite
WHERE userid =301 and movieid= 1;

 

 

문제 3번

  아래 그림에 들어가야 할 SQL 문을 작성하세요.

 

-- 영화제목 사진 줄거리내용 장르 개봉일 관객수 별점평균                     
SELECT m.id, m.title, p.image_url,m.storyline, m.year, m.attendance, avg(r.rating)
FROM movie m 
left join photos p on p.movieid = m.id
left join review r on r. movieid = m.id
group by m.id;

 

문제 4번

  아래 그림에 들어가야 할 SQL 문을 작성하세요.

SELECT m.id, m.title, u.nickname, r.content, avg(r.rating)as rating_avg 
FROM movie m 
join review r on r.movieid = m.id
join user u on u.id = r.userid
where m.id = 1
order by r.created_at desc
limit 0,20;

문제 5번

  아래 그림에 들어가야 할 SQL 문을 작성하세요.

-- 2. 검색어 입력시 상세 내용 보여주기(** 단어 포함검색어 예 타이)
-- 영화명 리뷰개수 별점평균
SELECT m.id,m.title,count(m.id), avg(r.rating)
from movie m
left join review r on r.movieid = m.id
WHERE title like '%b%'
group by m.id;

문제 6번

  아래 그림에 들어가야 할 SQL 문을 작성하세요.

 

-- 즐겨찾기는 1개만 저장되고 추가적으로 저장되지 않도록 해야 한다
-- ( 이러한 문제를 해결하기 위해 유니크키 설정 적용을 해야 한다)
-- 1회 터치는 즐겨찾기, 다시한번 터치 할 경우 즐겨찾기 해제

SELECT *
FROM favorite;

insert into favorite (userid,movieid) 
values (1,1);

-- 즐겨찾기 해제 301이 영화 아이디 1번을 즐겨 찾기 해제 하는 경우


DELETE FROM favorite
WHERE userid =301 and movieid= 1;

-- 내 즐겨찾기
-- 영화id 유져id 영화명 리뷰수 별점평균
SELECT m.id, u.id, m.title, r.content as review, avg(r.rating) as rating_avg
FROM user u
left join favorite f on f.userid = u.id
left join movie m on f.movieid = m.id 
left join review r on u.id = r.userid
where u.id = 1
order by r.created_at;


-- 2. 검색어 입력시 상세 내용 보여주기(** 단어 포함검색어 예 타이)
-- 영화명 리뷰개수 별점평균 최근 

SELECT m.id,m.title,
	count(m.id)as cnt, 
	IFNULL(avg(r.rating),0) as rating_avg
from movie m
left join review r 
		on r.movieid = m.id
WHERE title like '%b%'
group by m.id;

-- 검색기록을 보여주기 위해서
SELECT *
FROM search_history s
where s.userid=301
order by s.created_at desc;

-- 검색기록 삭제 
DELETE FROM search_history
where id = 2 and user_id=301;

 

문제 7번

  아래 그림에 들어가야 할 SQL 문을 작성하세요.

-- 내 정보화면 
-- 오른쪽 화면처럼 정보를 가져오는 API 개발 
-- 내 리뷰 리스트는 전부 다 가져오도록 한다.
-- ID 301 이라고 가정하고 해보자
-- 이메일 닉네임 성별 
SELECT id, email, nickname, gender
FROM user
WHERE id = 301;


-- ID 1 이라고 가정하고 리뷰리스트 보여주기 : 영화이름 별점
SELECT *
FROM review r
join movie m 
	on m.id = r.movieid
where r.USERid = 1;

로그아웃 : exit