[SQL] 2. LOGIC

SELECT * FROM benn.college_football_players

benn.college_football_players database

1. CASE 

• SELECT 절에 사용
• CASE WHEN __[case 1]__ THEN _[case 2]__ ELSE(optional) END
• WHEN과 THEN 사이에 WHERE 같은 conditional statment 사용가능
  • multiple conditional statment 사용 시 AND , OR 사용 
• WHEN, ELSE 여러번 사용 가능 

SELECT player_name, 
       year, 
       CASE WHEN year = 'SR' THEN 'yes'
            ELSE NULL END AS is_a_senior  
  FROM benn.college_football_players

** ELSE: optional. provides a way to capture values not specified in WHEN/THEN statemennt
** CASE + WHEN ~~~ THEN is necessary

SELECT player_name, 
       year, 
       CASE WHEN year = 'SR' THEN 'yes'
            ELSE 'no' END AS is_a_senior  -- SR이 아닐경우  NULL이 아니라 'no'로 표시
  FROM benn.college_football_players

Q: Write a query that includes a column that is flagged "yes" when a player is from California, and sort the results with those players first.
A: SELECT player_name, 
       state,
       CASE WHEN state = 'CA' THEN 'yes'
       ELSE NULL END AS is_from_Cali
  FROM benn.college_football_players
  ORDER BY 3

1.2. Adding multiple conditions to CASE 
SELECT player_name, 
             weight, 
            CASE WHEN weight > 250 THEN 'over 250'
                     WHEN weight > 200 THEN '201-250'
                     WHEN weight > 175 THEN '176-200'
                     ELSE '175 or under' END AS weight_group
  FROM benn.college_football_players
-- better way to do this is the following: 
SELECT player_name,
       weight, 
       CASE WHEN weight > 250 THEN 'over 250'
            WHEN weight > 200 AND weight <=250 THEN '201-250'
            WHEN weight > 175 AND weight <= 200 THEN '176-200'
            ELSE '175 or under' END AS weight_group
  FROM benn.college_football_players

Q: Write a query that includes players' names and a column that classifies them into four categories based on height. Keep in mind that the answer we provide is only one of many possible answers, since you could divide players' heights in many ways.
A: 
SELECT player_name, 
       height,
       CASE WHEN height >80 THEN 'over 80'
            WHEN height >70 AND height <=80 THEN '71-80'
            WHEN height >60 AND height <=70 THEN '61-70'
            WHEN height >50 AND height <=60 THEN '51-60'
            ELSE 'under 50' END AS height_group
  FROM benn.college_football_players
**WHEN과 THEN 사이에 conditional operator 추가가능; AND OR 

 

SELECT player_name, 
       CASE WHEN year = 'FR' AND position = 'WR' THEN 'frosh_wr'
            ELSE NULL END AS sample_case_statement
  FROM benn.college_football_players

SELECT *,
       CASE WHEN year='JR' THEN 'junior'
            WHEN year ='SR' THEN 'senior'
            ELSE NULL END AS junior_or_senior
  FROM benn.college_football_players

 

Q: Write a query that selects all columns from benn.college_football_players and adds an additional column that displays the player's name if that player is a junior or senior.

A: 
SELECT *,
       CASE WHEN year IN ('JR', 'SR') THEN player_name 
       ELSE NULL END AS upperclass_player_name
  FROM benn.college_football_players

 

SELECT CASE WHEN year = 'FR' THEN 'FR'
            ELSE 'Not FR' END AS year_group,
            COUNT(1) AS count
  FROM benn.college_football_players
 GROUP BY CASE WHEN year = 'FR' THEN 'FR'
               ELSE 'Not FR' END
-- COUNT(1) same as COUNT(*)

            
SELECT COUNT(1) AS fr_count
  FROM benn.college_football_players
  WHERE year ='FR' -- why not use this? WHERE only allows you to count one condition

SELECT CASE WHEN year = 'FR' THEN 'FR'
            WHEN year = 'SO' THEN 'SO'
            WHEN year = 'JR' THEN 'JR'
            WHEN year = 'SR' THEN 'SR'
            ELSE 'No Year Data' END AS year_group,
            COUNT(1) AS count
  FROM benn.college_football_players
  GROUP BY 1 -- or year_group 
            -- or BY CASE WHEN year = ~

Q: Write a query that counts the number of 300lb+ players for each of the following regions: West Coast (CA, OR, WA), Texas, and Other (Everywhere else).
A: 
SELECT CASE WHEN state IN ('CA', 'OR', 'WA') THEN 'West Coast'
            WHEN state = 'TX' THEN 'Texas'
            ELSE 'Other' END AS Area_Category,
            COUNT(1) as count 
  FROM benn.college_football_players
  WHERE weight >= 300 
  GROUP BY Area_Category -- or GROUP BY 1

Q: Write a query that calculates combined weight of all underclass players(FR/SO) in California as well as the combined weight of all upperclass players (JR/SR) in California
A: 
SELECT CASE WHEN year IN ('FR', 'SO') THEN 'underclass players'
            WHEN year IN ('JR', 'SR') THEN 'uppderclass players'
            END AS class_division, 
            SUM(weight) AS sum_weight
  FROM benn.college_football_players
  WHERE state = 'CA'
  GROUP BY class_division --or 1

SELECT CASE WHEN year = 'FR' THEN 'FR'
            WHEN year = 'SO' THEN 'SO'
            WHEN year = 'JR' THEN 'JR'
            WHEN year = 'SR' THEN 'SR'
            ELSE 'No Year Data' END AS year_group,
            COUNT(1) AS count
  FROM benn.college_football_players
 GROUP BY 1

 

-- horizontal 
SELECT COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr_count,
       COUNT(CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so_count,
       COUNT(CASE WHEN year = 'JR' THEN 1 ELSE NULL END) AS jr_count,
       COUNT(CASE WHEN year = 'SR' THEN 1 ELSE NULL END) AS sr_count
  FROM benn.college_football_players

Q: Write a query that displays the number of players in each state, with FR, SO, JR, and SR players in separate columns and another column for the total number of players. Order results such that states with the most players come first.
A: 
SELECT COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr_count,
       COUNT(CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so_count,
       COUNT(CASE WHEN year = 'JR' THEN 1 ELSE NULL END) AS jr_count, 
       COUNT(CASE WHEN year ='SR' THEN 1 ELSE NULL END) AS sr_count,
       COUNT(1) AS total_players,
       state 
  FROM benn.college_football_players
  GROUP BY state
  ORDER BY total_players DESC

Q: Write a query that shows the number of players at schools with names that start with A through M, and the number at schools with names starting with N - Z.
A: 
SELECT CASE WHEN school_name < 'n' THEN 'A-M'
            WHEN school_name >= 'n' THEN 'N-Z'
            ELSE NULL END AS school_name_group,
       COUNT(1) AS players
  FROM benn.college_football_players
 GROUP BY school_name_group --or 1