[Oracle] SQL 문제 풀이 (문제집 07)

🚩서로 짝이 있는 남자와 여자

-- tblMan. tblWoman. 서로 짝이 있는 사람 중 남자와 여자의 정보를 모두 가져오시오.
--    [남자]        [남자키]   [남자몸무게]     [여자]    [여자키]   [여자몸무게]
--    홍길동         180       70              장도연     177        65
--    아무개         175       null            이세영     163        null
--    ..

나의 풀이

SELECT
    m.name AS 남자,
    m.height AS 남자키,
    m.weight AS 남자몸무게,
    w.name AS 여자,
    w.height AS 여자키,
    w.weight AS 여자몸무게
FROM tblMen m
	INNER JOIN tblWomen w
		ON m.name = w.couple
			ORDER BY 남자 DESC, 여자 DESC;

 

정답

SELECT name as "[남자]", height as "[남자키]", weight as "[남자몸무게]", couple as "[여자]", (SELECT height FROM tblwomen where tblmen.couple = name) as "[여자키]", (SELECT weight FROM tblwomen where tblmen.couple = name) as "[여자몸무게]" FROM tblmen 
    where couple is not null;

 

🚩빈도수가 높은 직업을 가진 사람들의 출신

-- tblAddressBook. 가장 많은 사람들이 가지고 있는 직업은 주로 어느 지역 태생(hometown)인가?

나의 풀이

SELECT
	job AS 직업,
	hometown AS 출신,
	count(*) AS 빈도수
FROM tblAddressBook
	GROUP BY job, hometown
		ORDER BY count(*) DESC;

 

정답

select 
    distinct hometown
from tblAddressBook
    where job = (select job from tblAddressBook group by job having count(*) = (select max(count(*)) from tblAddressBook group by job))
        order by hometown asc;

🚩아이디 길이가 가장 긴 이메일의 도메인

-- tblAddressBook. 이메일 도메인들 중 평균 아이디 길이가 가장 긴 이메일 사이트의 도메인은 무엇인가?

나의 풀이

SELECT
	domain,
	avg(length(id)) AS avg_length
FROM (
    SELECT
        substr(email, 1, instr(email, '@') - 1) AS id,
        substr(substr(email, instr(email, '@') + 1), 1, instr(substr(email, instr(email, '@') + 1), '.') - 1) AS domain
    FROM tblAddressBook
)
GROUP BY DOMAIN
	ORDER BY avg_length DESC;

 

정답

select 
    substr(email,instr(email,'@')+1), 
    avg(length(substr(email,1,instr(email,'@')-1)))
from tbladdressbook
    group by substr(email,instr(email,'@')+1)
        having avg(length(substr(email,1,instr(email,'@')-1))) 
            = (select max(avg(length(substr(email,1,instr(email,'@')-1)))) from tbladdressbook group by substr(email,instr(email,'@')+1));

 

🚩평균 나이가 가장 높은 출신의 직업 빈도

-- tblAddressBook. 평균 나이가 가장 많은 출신(hometown)들이 가지고 있는 직업 중 가장 많은 직업은?

나의 풀이

SELECT
    hometown AS 출신,
    job AS 직업,
    AVG(age) AS 평균나이,
    COUNT(*) AS 직업빈도
FROM tblAddressBook
	WHERE (hometown, age) IN (
	    SELECT
	    	hometown,
	    	MAX(age) AS max_age
		    	FROM tblAddressBook
		    		GROUP BY hometown
	)
	GROUP BY hometown, job
		ORDER BY 평균나이 desc, 직업빈도 DESC;

 

정답

select 
    job
from tblAddressBook
    where hometown = (select hometown from tblAddressBook 
                        group by hometown having avg(age) = (select max(avg(age)) from tblAddressBook group by hometown))
        group by job
            having count(*) = (select max(count(*)) from tblAddressBook where hometown = (select hometown from tblAddressBook group by hometown having avg(age) = (select max(avg(age)) from tblAddressBook group by hometown)) group by job); 

select * from tblAddressBook;

select
    max(avg(age))
from tblAddressBook
    group by hometown;

 

🚩나이가 많은 서울 태생인 직업이 있는 남자

나의 풀이

SELECT
	*
FROM tblAddressBook
	WHERE
	    address LIKE '%서울%'
	    AND job IS NOT NULL
	    AND age > (SELECT AVG(age) FROM tblAddressBook);

 

정답

select 
    *
from tblAddressBook
    where age > (select avg(age) from tblAddressBook where gender='m')
            and hometown='서울' and job not in ('취업준비생','백수');

 

🚩나이가 많고 몸무게가 높고 직업이 있는 사람

-- tblAddressBook. 가장 나이가 많으면서 가장 몸무게가 많이 나가는 사람과 같은 직업을 가지는 사람들을 가져오시오.

정답

SELECT * FROM tbladdressbook where job = (SELECT job FROM tbladdressbook
where weight = (SELECT max(weight) FROM tbladdressbook where age = (SELECT max(age) FROM tbladdressbook)) and age = (SELECT max(age) FROM tbladdressbook));

 

🚩동명이인의 명단

-- tblAddressBook.  동명이인이 여러명 있습니다. 이 중 가장 인원수가 많은 동명이인(모든 이도윤)의 명단을 가져오시오.

정답

select 
    *
from tblAddressBook
    where name = (select name from tblAddressBook group by name having count(*) = (select max(count(*)) from tblAddressBook group by name));

 

🚩사람이 가장 많은 직업의 세대별 비율

-- tblAddressBook. 가장 사람이 많은 직업의(332명) 세대별 비율을 구하시오.
--    [10대]       [20대]       [30대]       [40대]
--    8.7%        30.7%        28.3%        32.2%

정답

SELECT 
    job,
    round(count(CASE
        when age between 10 and 19 then 1
    end)/count(*)*100,2)||'%' as "[10대]",
    round(count(CASE
        when age between 20 and 29 then 1
    end)/count(*)*100,2)||'%' as "[20대]",
    round(count(CASE
        when age between 30 and 39 then 1
    end)/count(*)*100,2)||'%' as "[30대]",
    round(count(CASE
        when age between 40 and 49 then 1
    end)/count(*)*100,2)||'%' as "[40대]"
FROM tbladdressbook group by job having count(job) = (SELECT max(count(job)) FROM tbladdressbook group by job);