분류 전체보기93 case when -> having select distinctcasewhen ((SKILL_CODE & (select sum(CODE) from SKILLCODES where CATEGORY = 'Front End')) and (SKILL_CODE & (select CODE from SKILLCODES where NAME = 'Python'))) then 'A'when SKILL_CODE & (select CODE from SKILLCODES where NAME = 'C#') then 'B'when SKILL_CODE & (select sum(CODE) from SKILLCODES where CATEGORY = 'Front End') then 'C'else nullend as 'grade',id,emailfrom S.. 2024. 7. 15. 중복 제거 select distinct ID,EMAIL,FIRST_NAME,LAST_NAMEfrom DEVELOPERS djoin SKILLCODES son d.SKILL_CODE & s.CODE > 0where CATEGORY='Front End'order by id 개발자가 여러 front end 기술을 가지면 여러 번 출력됨 -> 한 번만 출력해도 되므로 중복 제거 2024. 7. 12. format 소수 n번째 자리까지 select concat(format(max(length),2),'cm') 'MAX_LENGTH'from fish_info 2024. 7. 11. 데이터 조합 형식 select id, FISH_NAME, LENGTHfrom fish_info ijoin FISH_NAME_INFO non i.FISH_TYPE=n.FISH_TYPEwhere (i.fish_type, length) in (select fish_type, max(ifnull(length, 10)) from fish_info group by fish_type) 2024. 7. 10. 0708 1. 범주형 자료의 추세조나단은 무글루텐 식이요법이 긍정적인 삶의 태도를 갖는데에 도움을 준다는 뉴스를 보았습니다. 맞는 말인 것 같았지만, 조나단은 극단적으로 식이요법을 바꾸기 전에 뉴스가 맞는지 조사해야겠다고 생각했습니다. 조나단은 학교에서 100명에게 무글루텐 식이요법을 사용하는지 물어본 다음, 그 사람이 긍정적인 사람인지 부정적인 사람인지 판단했습니다.조나단은 무글루텐 식이요법과 긍정적인 삶의 태도는 아무 상관관계가 없다는 사실을 발견했습니다.이 정보를 가지고, 다음 결합도수표의 빈칸에 알맞은 값을 써넣어 보세요. 무글루텐무글루텐 아님합계긍정적임1258 60부정적임8 32 40합계 20 80 100 전체 학생 중 60%가 긍정적인 태도 -> 무글루텐 & 무글루텐 아님 모두 2. 독립사건아담의.. 2024. 7. 8. union all with recursive tree as (select id, parent_id,1 as 'level'from ecoli_datawhere parent_id is nullunion select e1.id, e1.parent_id, e2.level + 1from ecoli_data e1join tree e2on e2.id=e1.parent_id -- 크로스 조인)select idfrom treewhere level=3order by id union해줄 때 테이블 구조 일치시키기! 2024. 7. 8. percent_rank, ntile with ecoli_ds as (select *,percent_rank() over(order by size_of_colony desc) 'pr'from ecoli_data)select id,casewhen pr with ecoli_ds as (select *,percent_rank() over(order by size_of_colony desc) 'pr'from ecoli_data)select id,casewhen pr between 0 and 0.25 then 'CRITICAL'when pr between 0.25 and 0.5 then 'HIGH'when pr between 0.5 and 0.75 then 'MEDIUM'else 'LOW'end as 'COLONY_NAME'from ecoli_ds.. 2024. 7. 8. 비트연산 select id, email, first_name, last_namefrom developers as dwhere d.skill_code & (select code from skillcodes where name="Python") != 0 or d.skill_code & (select code from skillcodes where name="C#") != 0order by id/*SELECT *FROM DEVELOPERS D, SKILLCODES SWHERE 1 = 1 -- always TRUE (outer join) AND (SKILL_CODE & S.CODE) != 0 AND NAME IN ('Python', 'C#')ORDER BY ID;*/ select count(*) .. 2024. 7. 2. recursive & repeat with recursive cte as ( select 20 as NUM union all select NUM-1 from CTE where NUM > 0)select repeat('* ', NUM)from cte with recursive cte as (select 1 as NUMunion allselect NUM+1 from ctewhere NUM with recursive cte as (select 2 as NUMunion allselect NUM+1from ctewhere NUM 2024. 6. 25. interviews select c.contest_id, hacker_id, name, sum(total_submissions) 'total_submissions',sum(total_accepted_submissions) 'total_accepted_submissions',sum(total_views) 'total_views',sum(total_unique_views) 'total_unique_views'from contests cleft join colleges co on c.contest_id=co.contest_idleft join challenges ch on co.college_id=ch.college_idleft join (select challenge_id, sum(total_views) 't.. 2024. 6. 20. 이전 1 2 3 4 5 ··· 10 다음
