위 인스타그램 데이터 파일을 다운로드 받아서 실제로 쿼리 작업을 해보자...
본인은 현재 Intelij 의 datagrip 을 사용하였고 import data from file 을 이용하여 테이블에 데이터를 마이그레이션 하였다.
문제에 대해 생각한 내 쿼리문은 맨 아래에 있다.
1. 가장 옛날에 만든 유저 5명을 찾는 쿼리문을 작성해보자
아래는 유저 테이블에 대한 정보이고, 단순히 생각하면 된다.
create table users (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (username) VALUES
('anton'), ('blues'), ('capstone');
select * from users;
describe users;2. 일주일 중에 무슨 요일에 사용자들이 회원가입을 많이했는지 쿼리문을 작성해보자
create table users (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (username) VALUES
('anton'), ('blues'), ('capstone');
select * from users;
describe users;3. 한 번도 photo 를 업로드 하지 않은 유저를 찾는 쿼리문을 작성해보자
유저와 포토 테이블을 적절하게 사용하여 원하는 데이터를 뽑을 수 있다.
create table photos (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
image_url VARCHAR(255) NOT NULL,
user_id INTEGER NOT NULL ,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id)
);create table users (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (username) VALUES
('anton'), ('blues'), ('capstone');
select * from users;
describe users;4. 한 번도 댓글을 달지 않은 유저를 찾는 쿼리문을 작성해보자
create table comments(
id INTEGER AUTO_INCREMENT PRIMARY KEY,
comment_text VARCHAR(255) NOT NULL,
user_id INTEGER NOT NULL ,
photo_id INTEGER NOT NULL ,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(photo_id) REFERENCES photos(id)
);create table users (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (username) VALUES
('anton'), ('blues'), ('capstone');
select * from users;
describe users;5. 좋아요가 가장 많은 사진 찾는 쿼리문을 작성해보자
어떤 유저가 어떤 사진에 좋아요를 클릭 했는지 확인해보자...
create table likes(
user_id INTEGER NOT NULL,
photo_id INTEGER NOT NULL,
created_at timestamp default now(),
foreign key (user_id) references users(id),
foreign key (photo_id) references photos(id),
primary key(user_id, photo_id)
);create table photos (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
image_url VARCHAR(255) NOT NULL,
user_id INTEGER NOT NULL ,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id)
);create table users (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (username) VALUES
('anton'), ('blues'), ('capstone');
select * from users;
describe users;6. 사용자 한명이 등록한 평균 사진의 수를 계산하는 쿼리문을 작성해보자
총 사용자 / 총 사진의 갯수 == 한명의 사용자가 등록한 평균 사진의 갯수
create table photos (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
image_url VARCHAR(255) NOT NULL,
user_id INTEGER NOT NULL ,
created_at TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id)
);create table users (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (username) VALUES
('anton'), ('blues'), ('capstone');
select * from users;
describe users;7. 모든 사진에 좋아요를 누른 사용자를 찾는 쿼리문을 작성해보자
create table likes(
user_id INTEGER NOT NULL,
photo_id INTEGER NOT NULL,
created_at timestamp default now(),
foreign key (user_id) references users(id),
foreign key (photo_id) references photos(id),
primary key(user_id, photo_id)
);create table users (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (username) VALUES
('anton'), ('blues'), ('capstone');
select * from users;
describe users;아래는 해당하는 문항에 대한 쿼리문들이다.
1. 가장 옛날에 만든 유저 5명을 찾는 쿼리문을 작성해보자
limit 을 이용하면 출력되는 데이터의 갯수를 제한할 수 있다.
select * from users order by created_at limit 5;
2. 일주일 중에 무슨 요일에 사용자들이 회원가입을 많이했는지 쿼리문을 작성해보자
dayname 함수를 이용하면 argument 에 해당하는 요일을 알 수 있다.
select count(username) as totalUser, dayname(created_at) as dayname
from users group by dayname
order by totalUser desc;
3. 한 번도 photo 를 업로드 하지 않은 유저를 찾는 쿼리문을 작성해보자
left outer join 을 이용해서 users 테이블과 photos 테이블을 join 한다.
left outer join 이기 때문에 일반적인 inner join 과 다르게 주체가 users 이기 때문에 photos 테이블에서 값이 null 인 녀석들도 불러들인다.
where ___ is null 을 사용하여 image_url 이 null 인 유저를 찾는다.
select username, image_url
from users left outer join photos on users.id = photos.user_id
where image_url is null;
4. 한 번도 댓글을 달지 않은 유저를 찾는 쿼리문을 작성해보자
위와 동일하게 left outer join 을 사용하여 users 테이블과 comments 테이블을 조인하는데 users 테이블이 주체이기 때문에 comments 테이블에서 값이 null 인 녀석들도 불러들인다.
where ___ is null 을 사용하여 comment_text 가 null 인 유저를 찾는다.
select username, comment_text
from users left outer join comments c on users.id = c.user_id
where comment_text is null;
5. 좋아요가 가장 많은 사진 찾는 쿼리문을 작성해보자
어떤 유저가 어떤 사진에 좋아요를 클릭 했는지 확인, 똑같은 photo_id 에 다른 user_id 를 가지는 데이터들이 저장될 것이고 count 와 group by 를 이용해서 찾으면 된다.
첫번째 방법
select p.user_id, likes.photo_id, image_url, count(p.user_id) as totalNum
from likes
inner join photos p on likes.photo_id = p.id
inner join users u on likes.user_id = u.id
group by likes.photo_id order by totalNum desc limit 1;
두번째 방법
select username, photo_id, image_url, count(l.user_id) as totalNum
from photos
inner join likes l on photos.id = l.photo_id
inner join users u on photos.user_id = u.id
group by photo_id
order by totalNum desc limit 1;
세번째 방법
select photo_id, image_url, p.user_id, count(likes.user_id) as num
from likes
inner join users u on likes.user_id = u.id
inner join photos p on likes.photo_id = p.id
group by photo_id
order by num desc limit 1;
6. 사용자 한명이 등록한 평균 사진의 수를 계산하는 쿼리문을 작성해보자
일반적으로 '/' 를 사용한 나누기는 불가능하고 select 를 붙여줘야 계산이 가능하다...
7. 모든 사진에 좋아요를 누른 사용자를 찾는 쿼리문을 작성해보자
users 와 likes 테이블을 inner join
user_id 별 좋아요를 클릭한 photo_id 갯수 조회
select username, count(photo_id) as num_liks
from users
inner join likes on users.id = likes.user_id
group by likes.user_id
order by num_liks desc;