스키마 설계 - 인스타

인스타_클론.sql

0.42MB

-- 가장 오래된 회원 5명을 찾으세요.

SELECT *
FROM users
order by created_at
limit 5;

 

 

-- 회원가입을 가장 많이 하는 요일은?

SELECT DAYNAME(created_at ) day, count(*) cnt
FROM users u 
group by day
order by cnt desc;

 

 

-- 회원가입은 했지만, 사진은 올린적 없는 유령회원의 데이터를 가져오세요.

SELECT *
FROM users u 
left join photos p 
on p.user_id =u.id 
where p.id is null;

 

-- 유령 회원의 수는?

SELECT count(*)
FROM users u 
left join photos p 
on p.user_id = u.id 
where p.id is null;

 

-- 가장 좋아요가 많은 사진을 찾아서, 그 사진을 올린 유저 이름과 사진 url, 좋아요 수를 가져오세요.

SELECT u.username ,p.image_url ,count(*) likes
FROM likes l 
join photos p 
on l.photo_id =p.id
join users u 
on u.id =l.user_id 
group by p.id
order by likes desc;

select count(*) likes
		FROM likes l 
		join photos p 
		on l.photo_id =p.id
		join users u 
		on u.id =l.user_id 
		group by p.id
		order by likes desc;
-- 서브쿼리
SELECT max(likes) max_likes
FROM (select count(*) likes
		FROM likes l 
		join photos p 
		on l.photo_id =p.id
		join users u 
		on u.id =l.user_id 
		group by p.id) sub_table;


-- 서브쿼리
SELECT *
FROM (select u.username ,p.image_url, count(*) like_count
		FROM likes l 
		join photos p 
		on l.photo_id =p.id
		join users u 
		on u.id =l.user_id 
		group by p.id) sub_table
where like_count = (select max(like_count)
					from (select count(*) like_count
					FROM likes l 
					join photos p 
					on l.photo_id =p.id
					join users u 
					on u.id =l.user_id 
					group by p.id) sub_table);

 

 

-- 가장 많이 사용된 해시태그의 이름은 무엇일까요

-- 해시태그의 이름과 사용 횟수를 나타내세요.

SELECT t.tag_name ,count(*) cnt
FROM photo_tags pt 
join tags t 
on pt.tag_id =t.id 
group by t.tag_name 
order by cnt desc;
-- 서브쿼리
SELECT *
FROM (SELECT t.tag_name ,count(*) cnt
	FROM photo_tags pt 
	join tags t 
	on pt.tag_id =t.id 
	group by t.tag_name ) sub_table
where cnt=(SELECT max(cnt)
		from (SELECT t.tag_name ,count(*) cnt
			FROM photo_tags pt 
			join tags t 
			on pt.tag_id =t.id 
			group by t.tag_name ) sub_table);

 

 

-- 포토아이디 72번인 사진의 사진 url 주소, 작성자 이름, 좋아요 수를 가져오세요.

SELECT p.id, p.image_url ,u.username, count(*) likecnt 
FROM photos p 
join users u 
on u.id =p.user_id 
join likes l 
on l.photo_id =p.id 
WHERE p.id =72
group by l.photo_id ;

 

 

 

-- 포토 아이디 72번 사진의 해시태그를 가져오세요.

SELECT p.id ,t.tag_name 
FROM photos p
join photo_tags pt 
on p.id =pt.photo_id
join tags t ON pt.tag_id =t.id 
where p.id=72;

 

 

-- 유저아이디 1이 포토아이디 72번에 댓글을 다는 SQL작성해보세요.

INSERT INTO comments (comment_text,photo_id,user_id)
values ('사진 멋져요',72,1);

 

 

-- 포토아이디 72번의 댓글 리스트를 가져오는 SQL을 만드세요.

-- 댓글내용, 댓글 작성자 이름, 댓글 작성 날짜가 나오도록 개발

-- 댓글 리스트는 최신순으로 나오도록 개발

SELECT c.id,c.comment_text ,u.username ,c.created_at 
FROM comments c 
join photos p ON c.photo_id =p.id 
JOIN users u on u.id =c.user_id 
where p.id=72
order by c.created_at desc;

 

 

-- 유저 아이디 2인 사람의 팔로워 수와 팔로잉 수를 가져오세요.

SELECT count(*)
FROM follows f 
WHERE follower_id =2;

SELECT count(*)
FROM follows f 
WHERE f.followee_id =2;