[실습문제] 2023. 11. 2.(목) 문제 풀이(5문제)

실습문제 1. movie_table_t1 테이블을 사용하여 시도별 영화 이용비율이 가장 높은 연령대를 확인하여라(시도, 연령대, 이용비율 출력).

나의 경우에는 이 문제를 다중컬럼 서브쿼리로 풀었다. 아직 이름이 익숙치 않고 그냥 감으로만 풀었는데 앞으로는 내가 어떤 서브쿼리로 풀어가는지도 인지해야겠다.

 

먼저 다중컬럼 서브쿼리를 사용한 쿼리이다.

사전정보( group by 정보)를 where절 상수로 사용한다.

메인쿼리절의 컬럼만 출력되므로 메인쿼리에 없는 데이터는 select 처리할 수 없다.

max(이용비율)이 위와 같은 경우는 서브쿼리에 있으므로 얘는 메인쿼리 select에 올 수 없다는 의미이다.

성능은 둘 째 치고 서브쿼리에 미리 만들어둔 사전정보 일부를 메인 select에 올리고 싶으면 인라인뷰를 사용하여야 한다.

 

 

다음은 인라인뷰를 사용한 쿼리이다.

여기서 중요한 것은 where절에 지역일치조건(m.시도 = i.시도)이 있어야 한다는 것이다.

이 문제는 지역일치조건 없이 뽑게 되면, 같은 지역이 두 번 나올 수 있다.

 

위 문제에 사용한 movie_table_t1은 movie_table 자료를 ctas를 활용하여 가공한 것이다.

movie_table 본 자료는 다음과 같다.

 

그런데 ctas로 데이터를 가공하여 중간데이터를 만들어서 문제를 푼 것이다.

 

그런데 SQL 코테에서는 절대로 중간자료를 테이블로 만들어서 사용할 수 없다.

그러면 movie_table 그대로 위 문제를 해결해보자.

 

 

 

실습문제 2. movie_table을 이용하여 성별, 요일별 이용비율의 총 합을 출력하여라.

 

이 문제는 간단한 group by 문제였다.

여기서 새로운 날짜출력방식을 배웠다. step 1의 첫 번째 방법인데 저 방법도 기억해둬야겠다.★

 

 

실습문제 3. 실습문제 2의 결과를 사용하여 movie_table_t2 테이블 생성 후, 성별로 어느 요일에 영화를 가장 많이 이용하는지 확인하여라(성별, 요일, 이용비율 출력)

먼저 ctas 생성은 아래와 같이 하였다.

 

동등비교이기 때문에 다중컬럼 서브쿼리와 인라인뷰로 풀어보았다.

 

먼저 다중컬럼 서브쿼리이다.

 

 

아래는 인라인뷰 서브쿼리이다.

 

728x90

 

실습문제 4. cctv_table을 사용하여 연도별로 범죄 발생이 가장 많았던 구를 확인하여라(년도, 구, 발생 출력)

 

먼저 다중컬럼 서브쿼리 방법으로 풀었다.

 

다음은 인라인뷰 서브쿼리 방법으로 풀었다.

 

 

실습문제 5. cctv_table 테이블에서 각 연도별로 평균 검거율보다 낮은 검거율을 기록한 구를 확인하여라(년도, 구, 검거 출력)

 

이 문제는 다중컬럼 서브쿼리는 불가하므로 인라인뷰로 풀었다.

검거율은 100이 넘기도 하는데 과거에 발생한 범죄를 다음해에 검거 할 수도 있기 때문이다.

데이터를 보다보니 100이 넘는 것이 있어서 잘못된 출력인 줄 알았다.

 

 

실습문제 6. emp2, dept2를 사용하여 각 직원과 같은 취미를 가진 동료직원(본인 제외)의 수를 각 직원의 사번, 이름, 취미, 부서명과 함께 출력하여라. 단, 취미가 같은 동료 직원이 없는 경우에도 함께 출력하여라.

 

일단 나의 틀린 코드 박제

 

왜 틀렸냐면, 20명 사원 중 19명만 나왔다. 취미가 없는 사람도 출력해야 하는데 취미가 없는 사원을 이해하지 못하고 같은 취미가 없는 사람으로 착각해서 같은 취미 수가 0이 나왔기에 잘 출력한 결과물인줄 알았다.

그렇다면 취미가 없는 사람도 출력하기 위해서는 outer join이 필요한데, 내 코드에 outer join을 심으면

이윤나 라는 직원은 취미가 없는데, 내가 코드 안에서 같은 취미를 구하는 과정에 내 자신과의 취미 숫자가 겹치는 것을 막기 위해 -1 을 한게, 이런 결과를 초래한 것이다.

 

꼼수로 -1을 할 게 아니라, 코드 안에서 내 자신을 출력하지 못하도록 unique value를 잘 설정하는 것을 배워야겠다.

일단 이 문제는 self join이 필요하다는 것을 인지하여야 하는 것도 중요한 포인트이다.

선생님 해설이다.

먼저 where절에서 e1.hobby=e2.hobby하면 자기자신도 출력이 된다.

그래서 그 다음줄에 e1.empno != e2.empno 를 걸어서  내 자신은 출력되지 않도록 하는 것이다.

name 컬럼을 사용할 수도 있지만 동명이인이 나올 수 있기 때문에 unique value인 사원번호로 사용하였다.

 

위 코드는 oracle 표준이며, ansi 표준으로 고치면 다음과 같다.