sql 문제풀이 3

1. 대장균의 크기에 따른 분류 1

 

답

SELECT
    ID,
    CASE
        WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
        WHEN SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
        ELSE 'HIGH'
    END AS SIZE
FROM
    ECOLI_DATA
ORDER BY
    ID ASC;

 

2. 

 

SELECT -- 풀이1
    SUM(CASE WHEN (GENOTYPE & 2) = 0 AND ((GENOTYPE & 1) > 0 
    OR (GENOTYPE & 4) > 0) THEN 1 ELSE 0 END) AS COUNT
FROM
    ECOLI_DATA;
    
   SELECT COUNT(*) AS COUNT -- 풀이2
FROM ECOLI_DATA
WHERE (GENOTYPE & 2) = 0  
    AND ((GENOTYPE & 1) > 0 
    OR (GENOTYPE & 4) > 0);

 

3. 

 

 

select id,
    case
        when a.quartile = 1 then 'critical'
        when a.quartile = 2 then 'high'
        when a.quartile = 3 then 'medium'
        when a.quartile = 4 then 'low'
    end as colony_name
from
    (select id, size_of_colony,
            ntile(4) over (order by size_of_colony desc) as quartile -- ()등분 
        from ecoli_data
    ) as a
order by
    id asc;