/Algorithm_JAVA

[Algorithm & SQL] Java Algorithm + MYSQL 문제 풀이

Primary LanguageJava

Algorithm 🐻

C++ -> JAVA 전향
C++ 알고리즘 저장소 : https://github.com/ggomjae/Algorithm
programmers

SQL

1 ) 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 INTAKE_CONDITION = 'Sick' ORDER BY ANIMAL_ID
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION != 'Aged' 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
  • 만약 LIMIT 4,6 라면 5부터 6개 추출

2 ) SUM MAX MIN

SELECT ANIMAL_ID, MAX(DATETIME) AS 시간 FROM ANIMAL_INS
SELECT DATETIME AS 시간 FROM ANIMAL_INS ORDER BY DATETIME limit 1
SELECT count(*) AS count FROM ANIMAL_INS
SELECT COUNT(distinct NAME) FROM ANIMAL_INS
  • 중복, NULL 제거 * -> distinct

3 ) GROUP BY

SELECT ANIMAL_TYPE , count(*) AS count FROM ANIMAL_INS GROUP BY ANIMAL_TYPE ORDER 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) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19 GROUP BY HOUR(DATETIME) ORDER BY HOUR(DATETIME)
  • COUNT(*) : 조회된 전체행 건수를 반환한다
  • COUNT(컬럼) : 컬럼의 값이 NULL인 행은 카운트 하지 않는다
  • COUNT(DISTINCT 컬럼) : 컬럼 값을 중복제거하고, 컬럼의 값 건수를 반환한다

4 ) IS NULL

SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_ID
SELECT ANIMAL_TYPE , IFNULL(NAME,'No name') AS NAME , SEX_UPON_INTAKE FROM ANIMAL_INS ORDER BY ANIMAL_ID
  • IFNULL 중요

5 ) JOIN

SELECT B.ANIMAL_ID ,B.NAME FROM ANIMAL_INS A RIGHT JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.ANIMAL_ID IS NULL ORDER BY ANIMAL_ID
SELECT A.ANIMAL_ID, A.NAME FROM ANIMAL_INS A JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.DATETIME > B.DATETIME ORDER BY A.DATETIME
SELECT A.NAME , A.DATETIME FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B  ON A.ANIMAL_ID = B.ANIMAL_ID WHERE B.DATETIME IS NULL ORDER BY A.DATETIME LIMIT 3
SELECT B.ANIMAL_ID, B.ANIMAL_TYPE , B.NAME FROM ANIMAL_INS A JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE A.SEX_UPON_INTAKE LIKE 'Intact%' AND (B.SEX_UPON_OUTCOME LIKE 'Spayed%' OR B.SEX_UPON_OUTCOME LIKE 'Neutered%') ORDER BY B.ANIMAL_ID

6 ) String DATE

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE FROM ANIMAL_INS WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty') ORDER BY ANIMAL_ID
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 ORDER BY ANIMAL_ID
SELECT * FROM (SELECT A.ANIMAL_ID, A.NAME FROM ANIMAL_INS A, ANIMAL_OUTS B WHERE A.ANIMAL_ID = B.ANIMAL_ID ORDER BY  A.DATETIME - B.DATETIME) WHERE ROWNUM <= 2
SELECT ANIMAL_ID , NAME , DATE_FORMAT(DATETIME,'%Y-%m-%d') AS 날짜 FROM ANIMAL_INS ORDER BY ANIMAL_ID
  • CASE WHEN THEN ELSE END 중요
  • A.DATETIME - B.DATETIME

REFERENCE

끝맺음


  • 알고리즘 SQL 을 위한 저장소
  • 알고리즘 : src -> main -> java
  • SQL : README.md
  • 주기적인 UPDATE