MySQL tutorials

programmers_tutorial.sql

# MySQL Tutorial
# https://programmers.co.kr/learn/challenges

/* ------ */
/* SELECT */
/* ------ */

SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID

SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_INS.INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_INS.INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1



/* ------------- */
/* SUM, MAX, MIN */
/* ------------- */

SELECT DATETIME as '시간'
FROM ANIMAL_INS
ORDER BY DATETIME DESC
LIMIT 1

SELECT MIN(DATETIME) AS 시간 FROM ANIMAL_INS

SELECT COUNT(*) FROM ANIMAL_INS

SELECT COUNT(DISTINCT NAME) AS 'count' FROM ANIMAL_INS;



/* -------- */
/* GROUP BY */
/* -------- */

SELECT ANIMAL_TYPE, COUNT(*) as 'count'
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE

SELECT NAME, COUNT(*) as 'COUNT'
FROM ANIMAL_INS
GROUP BY NAME HAVING COUNT(NAME) >= 2
ORDER BY NAME

SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
GROUP BY HOUR

SET @hour = -1;
SELECT
    (@hour := @hour + 1) AS 'HOUR',
    (
        SELECT COUNT(DATETIME)
        FROM ANIMAL_OUTS
        WHERE HOUR(DATETIME) = @hour
    ) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE @hour < 23



/* ------- */
/* IS NULL */
/* ------- */

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME is NULL

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID ASC

SELECT
    ANIMAL_TYPE,
    IFNULL(NAME, "No name") AS NAME,
    SEX_UPON_INTAKE
FROM ANIMAL_INS



/* ---- */
/* JOIN */
/* ---- */

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_OUTS A LEFT JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.ANIMAL_ID

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS LEFT JOIN ANIMAL_OUTS ON ANIMAL_INS.ANIMAL_ID=ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.DATETIME > ANIMAL_OUTS.DATETIME
ORDER BY ANIMAL_INS.DATETIME

SELECT ANIMAL_INS.NAME, ANIMAL_INS.DATETIME
FROM ANIMAL_INS LEFT JOIN ANIMAL_OUTS ON ANIMAL_INS.ANIMAL_ID=ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_OUTS.ANIMAL_ID IS NULL
ORDER BY DATETIME ASC LIMIT 3

SELECT OUTS.ANIMAL_ID, OUTS.ANIMAL_TYPE, OUTS.NAME
FROM ANIMAL_INS INS INNER JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID=OUTS.ANIMAL_ID
WHERE
    INS.SEX_UPON_INTAKE LIKE 'Intact %'
    and
    (OUTS.SEX_UPON_OUTCOME = 'Neutered Male' or OUTS.SEX_UPON_OUTCOME = 'Spayed Female')



/* ------------ */
/* String, Date */
/* ------------ */

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%EL%' AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

SELECT ANIMAL_ID, NAME, CASE
    WHEN SEX_UPON_INTAKE LIKE "%Neutered%" THEN "O"
    WHEN SEX_UPON_INTAKE LIKE "%Spayed%" THEN "O"
    ELSE 'X' END
    AS "중성화"
FROM ANIMAL_INS

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_INS INS INNER JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID=OUTS.ANIMAL_ID
ORDER BY (OUTS.DATETIME - INS.DATETIME) DESC
LIMIT 2

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS