Useful Functions in MySQL

https://www.w3schools.com/sql/sql_ref_mysql.asp

Programmers Problems

• ORDER BY with two keys

    select animal_id, name, datetime from animal_ins
    order by name (asc), datetime desc
  

• Q. Animal name with the earliest joined date

    select name from animal_ins
    where datetime = (
        select min(datetime) from animal_ins
    )
  

    -- Different way
    SELECT name 
    from animal_ins
    order by datetime asc
    limit 1
  

• CASE END statement

  : Q. print 'N' if freezer_yn is NULL

    select warehouse_id, warehouse_name, address, 
    (case when freezer_yn is null then 'N'  
          else freezer_yn end) as freezer_yn
    from food_warehouse
    where address like '경기도%'
    order by warehouse_id
  

• OR, AND in WHERE

    select dr_name, dr_id, mcdp_cd, substring_index(hire_ymd, ' ', 1) from doctor
    where mcdp_cd = 'CS' or mcdp_cd = 'GS' --- mcdp_cd = 'CS' or 'GS' not acceptable
    order by hire_ymd desc, dr_name asc
  

• SUBSTRING_INDEX by whitespace as a key

    select book_id, substring_index(published_date, ' ', 1) as published_date from book
    where published_date between '2021-01-01' and '2021-12-31' and
          category = '인문'
    order by published_date
  

• LIKE operator

  

• DATEDIFF

    select 
        history_id, 
        car_id, 
        substring_index(start_date, ' ', 1) as start_date,        
        substring_index(end_date, ' ', 1) as end_date, 
        (case 
            when datediff(end_date, start_date) + 1 >= 30 then '장기 대여' 
            else '단기 대여' 
         end) as rent_type
    from CAR_RENTAL_COMPANY_RENTAL_HISTORY
    where start_date between '2022-09-01' and '2022-09-30'
    order by history_id desc