[sakila] join & subquery 연습

 

#1. 영화점 직원의 연락처 조회

당신은 DVD 대여점 체인의 데이터베이스 관리자입니다. 본사에서는 첫 번째 영화점(store_id=1)에서 근무하는 모든 직원의 이름(이름과 성을 공백으로 구분하여)과 연락처를 요청하였습니다. 이 정보는 향후 직원들과의 긴급 연락을 위해 필요합니다. 데이터베이스 조회를 통해 이 정보를 제공해야 합니다.

• 테이블명 staff: first_name, last_name, address_id, store_id
• 테이블명 address: address_id, phone

결과 예시:

 

#2. 특정 국가의 고객 연락처 및 위치 정보 조회

당신은 국제 영화 대여 회사의 데이터베이스 관리자입니다. 회사는 마케팅 캠페인을 위해 'South Korea'에 거주하는 모든 고객의 이름(이름과 성을 공백으로 구분하여), 이메일 주소, 전화번호, 도시 이름, 그리고 국가명을 조회하려고 합니다. 이 데이터는 특정 지역에 맞춤형 마케팅 전략을 수립하는 데 도움이 될 것입니다. 데이터베이스에서 이러한 정보를 조회하는 SQL 쿼리를 작성해야 합니다.

• 테이블명 customer: first_name, last_name, email, address_id
• 테이블명 address: address_id, phone, city_id
• 테이블명 city: city_id, country_id
• 테이블명 country: country_id, country

결과 예시:

 

#3 .각 언어별 영화 제작 편수 조회

당신은 글로벌 영화 대여 회사의 데이터베이스 관리자입니다. 회사는 다양한 언어로 제작된 영화들을 보유하고 있으며, 각 언어별로 제작된 영화의 편수를 파악하고자 합니다. 이 정보는 회사가 어떤 언어의 영화에 더 많은 투자를 해야 할지 결정하는 데 중요한 자료가 될 것입니다. 당신의 임무는 데이터베이스에서 각 언어별로 제작된 영화의 편수를 조회하는 것입니다.

• 테이블명 language: language_id, name
• 테이블명 film: film_id, language_id

결과 예시: 

 

#4. 첫 번째 대여된 영화의 정보 조회

당신은 영화 대여 서비스 회사의 데이터베이스 관리자입니다. 회사 경영진은 서비스 시작 이후 첫 번째로 대여된 영화에 대한 정보를 알고 싶어 합니다. 이는 회사의 역사적인 순간을 기록하기 위한 것이며, 해당 영화의 제목, 대여된 영화의 ID, 해당 영화를 대여한 고객의 ID 및 이메일 주소를 포함해야 합니다. 당신의 임무는 이 정보를 데이터베이스에서 조회하는 것입니다.

• 테이블명 rental: rental_id, customer_id, inventory_id
• 테이블명 customer: customer_id, email
• 테이블명 inventory: inventory_id, film_id
• 테이블명 film: film_id, title

결과 예시:

 

#5. 비 인기 영화 상영회 준비

당신은 지역 커뮤니티 센터에서 영화 상영회를 준비하고 있습니다. 상영회에는 아직 대여된 적이 없는, 즉 관객들에게 새로운 경험을 줄 수 있는 영화를 선정하고자 합니다. 이를 위해, 아직 한 번도 대여되지 않은 영화들의 제목, 설명, 출시 연도, 대여 기간, 대여 요금, 영화 길이, 등급 정보를 조회해야 합니다. 이 정보를 바탕으로 관객들에게 새롭고 흥미로운 영화를 소개할 계획입니다. 데이터는 film_id로 정렬한다.

• 테이블명 film: film_id, title, description, release_year, rental_duration, rental_rate, length, rating
• 테이블명 inventory: film_id
• 테이블명 rental: inventory_id

결과 예시:

총 43건

 

#6. 특정 고객의 특정 기간동안 결제 내역 조회

당신은 소매점의 데이터베이스 관리자입니다. 고객 서비스 팀은 이메일이 EMILY.DIAZ@sakilacustomer.org 고객이 2005년 6월과 7월 동안 진행한 결제 내역에 대한 정보를 요청했습니다. 이 정보는 고객의 구매 패턴을 분석하고, 고객 서비스 개선에 활용될 예정입니다. 요청된 정보에는 결제 ID, 담당자 ID, 담당자 이메일, 결제일이 포함되어야 하며, 결제 ID를 기준으로 오름차순으로 정렬된 결과가 필요합니다. 이를 통해 최초의 결제부터 최근의 결제까지 시간 순으로 정보를 확인할 수 있게 됩니다. 당신의 임무는 이 정보를 데이터베이스에서 조회하고 정렬하는 것입니다.

• 테이블명 customer: customer_id
• 테이블명 payment: payment_id, staff_id, payment_date, customer_id
• 테이블명 staff: staff_id, email

결과 예시:

총 17건

 

#7. 고객의 특정 기간 결제 처리 건수에 따른 담당자별 성과 분석

당신은 소매점의 데이터 분석가입니다. 고객 서비스 팀의 리더는 이메일이 EMILY.DIAZ@sakilacustomer.org 고객이 2005년 6월과 7월 동안 진행한 결제 처리 건수를 기반으로 담당자별 성과를 분석하고자 합니다. 이 데이터 분석은 팀원들의 성과를 평가하고, 더 나은 고객 서비스를 제공하기 위한 목적으로 사용될 예정입니다. 분석에는 각 담당자의 이메일과 해당 기간 동안 처리한 결제 건수가 포함되어야 하며, 처리 건수를 기준으로 내림차순으로 정렬된 결과가 필요합니다. 이를 통해 가장 많은 결제를 처리한 담당자부터 확인할 수 있게 됩니다. 당신의 임무는 이 정보를 데이터베이스에서 조회하고 정렬하는 것입니다.

• 테이블명 customer: customer_id
• 테이블명 payment: payment_id, staff_id, payment_date, customer_id
• 테이블명 staff: staff_id, email

결과 예시:

 

#8. 특정 영화에 출연한 배우 목록 조회

당신은 영화 제작 팀의 일원이며, 현재 제작 중인 영화의 캐스팅 과정을 돕고 있습니다. 팀은 'Academy Dinosaur'라는 고전 영화의 리메이크를 계획 중이며, 원작 영화에 출연했던 배우들에 대한 정보를 찾고자 합니다. 특히, 각 배우의 식별 번호(ID)와 전체 이름(성과 이름)을 알아보고자 하는데, 이 정보는 추후 캐스팅 제안을 위해 필요합니다. 정보 요청에 따라, 결과는 배우의 ID 순으로 오름차순으로 정렬되어야 합니다.

• 테이블명 film: film_id, title
• 테이블명 film_actor: film_id, actor_id
• 테이블명 actor: actor_id, first_name, last_name

결과 예시:

총 10건

 

#9. 영화 리메이크 프로젝트: Academy Dinosaur 주연 배우 선정

당신은 영화 제작사에서 일하며, 'Academy Dinosaur'라는 고전적인 영화의 리메이크 버전을 제작하는 프로젝트의 일부입니다. 제작 팀은 이 영화의 주연 배우를 선정하는 중요한 과정에 있으며, 원작 영화에 출연했던 배우 중에서 경험이 풍부하고, 30편 이상의 다른 작품에 출연한 배우들을 대상으로 선택하고자 합니다. 이러한 배우들의 목록을 작성하기 위해, 당신은 배우의 ID, 이름, 그리고 그들이 출연한 작품의 수를 포함한 정보를 조회해야 합니다. 정보는 출연 작품 수에 따라 내림차순으로 정렬되어야 합니다.

• 테이블명 film: film_id
• 테이블명 film_actor: film_id, actor_id
• 테이블명 actor: actor_id

결과 예시:

 

#10. 도시 이름이 가장 긴 도시와 그 도시가 속한 국가의 이름

당신은 지리 정보 시스템(GIS) 관련 프로젝트에 참여하고 있으며, 데이터 분석의 일환으로 세계에서 가장 긴 이름을 가진 도시와 그 도시가 위치한 국가의 정보를 찾고자 합니다. 이 정보는 향후 흥미로운 지리적 사실을 소개하는 데 사용될 예정입니다. 따라서, 당신은 도시의 이름 길이가 가장 긴 도시와 해당 도시가 속한 국가의 이름을 조회해야 합니다.

• 테이블명 city: city, country_id
• 테이블명 country: country_id, country

결과 예시:

 

#11, 영화 마라톤 대비: 가장 짧은 영화와 가장 긴 영화 찾기

당신은 영화 마라톤 이벤트를 기획하고 있으며, 이벤트의 시작과 끝을 장식할 영화를 선택하고자 합니다. 시작을 위해 가장 짧은 영화를, 마라톤의 마무리를 위해 가장 긴 영화를 선정하는 것이 목표입니다. 참가자들에게 다양한 장르의 매력을 보여주기 위해, 영화의 제목, 길이, 그리고 해당 영화의 장르를 함께 조회해야 합니다. 결과는 영화 제목에 따라 알파벳 순으로 정렬되어야 합니다.

• 테이블명 film: film_id, title, length
• 테이블명 film_category: film_id, category_id
• 테이블명 category: category_id, name

결과 예시: 

총 15건

 

#12. 가족 단위 관람객을 위한 장편 애니메이션 영화 추천

당신은 영화관에서 가족 단위 관람객을 위한 특별 상영회를 기획하고 있습니다. 이번 상영회에서는 특별히 어린이 관람가(G 등급)에 해당하며, 시간이 180분을 넘는 장편 애니메이션 영화를 선보이고자 합니다. 관람객들에게 영화의 선택을 돕기 위해, 영화의 제목, 길이, 장르, 그리고 간단한 설명(30글자만)을 제공할 예정입니다. 결과는 영화의 제목에 따라 알파벳 순으로 정렬되어야 합니다.

• 테이블명 film: film_id, title, length, rating
• 테이블명 film_category: film_id, category_id
• 테이블명 category: category_id, name
• 테이블명 film_text: film_id, description

결과 예시: 

 

#13. 영화 대여 기간 분석: 1번 영화의 대여 기간과 어떻게 다른가요?

당신은 영화 대여점의 데이터 분석가로서, 대여점의 영화 대여 기간에 대한 분석을 진행하고 있습니다. 분석의 일환으로, 1번 영화의 대여 기간이 전체 영화들의 대여 기간 평균에 비해 어떻게 위치하는지를 파악하고자 합니다. 이를 위해, 1번 영화의 제목과 대여 기간을 조회하고, 그 대여 기간이 전체 영화들의 대여 기간 평균보다 긴지, 짧은지, 아니면 평균과 같은지를 "상대 길이"로 표시합니다. "상대 길이"는 "긴편", "평균", "짧은편"의 세 가지 값 중 하나로 표시됩니다.

• 테이블명 film: film_id, title, rental_duration

결과 예시:

 

#14. 가장 바쁜 배우 찾기: 영화 출연 회수 기준

당신은 영화 제작사의 데이터 분석팀에서 일하고 있으며, 최근 프로젝트로서 영화 출연 횟수를 기준으로 가장 바쁜 배우를 찾고자 합니다. 이를 위해, 출연한 영화의 수가 가장 많은 배우의 ID, 이름, 성, 그리고 출연한 영화의 총 수를 조회해야 합니다. 이 정보는 추후 캐스팅 결정 과정에서 중요한 참고 자료로 활용될 예정입니다.

• 테이블명 film_actor: actor_id, film_id
• 테이블명 actor: actor_id, first_name, last_name

결과 예시:

 

#15. 영화 Academy Dinosaur의 대여 가능한 복사본 수 찾기

당신은 영화 대여점의 직원으로서, 고객들이 자주 찾는 인기 영화 'Academy Dinosaur'의 현재 대여 가능한 복사본 수를 확인해야 합니다. 특히, 이 정보는 점포 2에서의 복사본 수에 대한 것이어야 합니다. 대여 중인 영화는 대여 가능한 복사본 수에 포함되지 않으며, 따라서 현재 대여 중이지 않은(즉, 반환된) 'Academy Dinosaur' 영화의 복사본 수를 정확히 알아내야 합니다.

• 테이블명 film: film_id, title
• 테이블명 inventory: inventory_id, film_id, store_id
• 테이블명 

결과 예시: 

 

#16. 영화 Academy Dinosaur의 미반환 복사본에 대한 연체료 계산

당신은 영화 대여점의 관리자이며, 인기 영화 'Academy Dinosaur'의 복사본 중 아직 반환이 되지 않은 것들에 대해 연체료를 계산해야 합니다. 연체료 계산은 2022년 12월 31일을 기준으로 하며, 각 영화 복사본의 대여 날짜, 마감일, 그리고 연체료를 포함하는 리스트를 작성해야 합니다. 연체료는 복사본이 마감일 이후에 얼마나 많은 날짜를 초과했는지에 대한 일수와 해당 영화의 대여율을 기반으로 계산됩니다.

• 테이블명 rental: rental_date, return_date, inventory_id
• 테이블명 inventory: inventory_id, film_id
• 테이블명 film: film_id, title, rental_duration, rental_rate

결과 예시:

 

#17. 영화 장르별 평균 상영 시간 조사

당신은 영화 데이터 분석가로서, 다양한 영화 장르들이 평균적으로 얼마나 긴 상영 시간을 가지고 있는지 조사해야 합니다. 이 정보는 특정 장르의 영화가 일반적으로 얼마나 길게 제작되는지 이해하는 데 도움이 될 것입니다. 조사 대상은 MySql의 sakila 데이터베이스에 등록된 모든 영화 장르이며, 각 장르별로 평균 상영 시간을 계산한 후, 그 결과를 평균 상영 시간이 긴 장르부터 내림차순으로 정렬해야 합니다.

• 테이블명 film: film_id, length
• 테이블명 film_category: film_id, category_id
• 테이블명 category: category_id, name

결과 예시:

 

#18. 영화 대여 시 연체된 경우 분석

당신은 영화 대여점의 데이터 분석가로서, 대여된 영화들 중 연체가 발생한 경우에 대해 분석하고자 합니다. 특히, 각 영화별로 연체된 건수와 그 영화의 총 보유 수량을 파악하여, 연체율이 높은 영화들을 확인하고자 합니다. 이 정보는 향후 대여 정책 조정 및 연체 방지 대책을 마련하는 데 도움이 될 것입니다. 결과는 '연체 건수'에 대한 내림차순으로 정렬합니다.

• 테이블명 rental: rental_date, return_date, inventory_id
• 테이블명 inventory: inventory_id, film_id
• 테이블명 film: film_id, title, rental_duration

결과 예시:

총 947건

 

#19. 현재 연체 중인 영화 대여 현황 분석

당신은 영화 대여점의 관리자로서, 현재 연체 중인 영화 대여 현황을 파악하고자 합니다. 특히, 연체 고객의 정보, 연체 영화 제목, 대여일, 예정된 대출 마감일, 그리고 현재까지 발생한 연체료를 확인하고자 합니다. 이 정보를 통해 연체 고객에게 연락을 취하고, 연체료를 안내함으로써 연체 상황을 해결하려고 합니다. 결과는 1차로 연체료를 기준으로 내림차순 정렬하고 2차로 이름 기준으로 오름차순 정렬합니다.

• 테이블명 customer: last_name, first_name, customer_id
• 테이블명 address: address_id, phone
• 테이블명 rental: rental_date, inventory_id, customer_id, return_date
• 테이블명 inventory: inventory_id, film_id
• 테이블명 film: title, rental_duration, rental_rate, film_id

결과 예시:

총 183건

 

#20. 지점별 총 매출액 분석

당신은 대형 비디오 대여 체인의 데이터 분석가로서, 최근 비즈니스의 성과를 평가하기 위해 각 지점별 총 매출액을 분석하고자 합니다. 이를 통해 어떤 지점이 가장 많은 매출을 올렸는지, 그리고 각 지점의 성과를 비교하고자 합니다.

• 테이블명 store: store_id
• 테이블명 staff: store_id, staff_id
• 테이블명 payment: staff_id, amount

결과 예시: