WantAirpod

www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

[case문 학습]

devbox.tistory.com/entry/DBMS-CASEWHENTHEN

SUM, MAX, MIN

최대 값 구하기
SELECT MAX(DATETIME) 시간
FROM ANIMAL_INS
최소 값 구하기
SELECT MIN(DATETIME)
FROM ANIMAL_INS
동물 수 구하기
SELECT COUNT(ANIMAL_ID)
FROM ANIMAL_INS
중복 제거하기
SELECT COUNT(DISTINCT NAME) COUNT 
FROM ANIMAL_INS 
WHERE NAME IS NOT NULL

GROUP BY

SELECT ANIMAL_TYPE , COUNT(ANIMAL_TYPE) COUNT  
FROM  ANIMAL_INS  
GROUP BY ANIMAL_TYPE 
ORDER BY ANIMAL_TYPE ASC 

SELECT *
FROM (SELECT NAME,COUNT(NAME) COUNT FROM ANIMAL_INS WHERE NAME IS NOT NULL GROUP BY NAME)
WHERE COUNT >=2 
ORDER BY NAME

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

JOIN

SELECT B.animal_id, b.name
FROM ANIMAL_INS A full OUTER JOIN ANIMAL_OUTS B
on A.animal_id = b.animal_id
where A.name is null
and b.name is not null
order by B.animal_id , b.name asc

SELECT A.animal_id ,A.name
FROM ANIMAL_INS A INNER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID =B.ANIMAL_ID
WHERE A.DATETIME > B.DATETIME
ORDER BY A.DATETIME  ASC

SELECT * 
FROM(
SELECT  A.NAME, A.DATETIME
FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID 
WHERE B.ANIMAL_ID IS NULL 
ORDER BY A.DATETIME)
WHERE ROWNUM<=3

String Data

SELECT ANIMAL_ID,NAME,SEX_UPON_INTAKE
FROM ANIMAL_INS 
WHERE NAME= 'Lucy' OR NAME= 'Ella'OR NAME= 'Pickle'OR NAME= 'Sabrina'OR NAME= 'Mitty'OR NAME= 'Rogan'
ORDER BY ANIMAL_ID ASC

SELECT ANIMAL_ID ,NAME
FROM ANIMAL_INS 
WHERE  ANIMAL_TYPE ='Dog' AND (NAME LIKE  '%el%' or name like '%El%' )
ORDER BY NAME

SELECT ANIMAL_ID, NAME,  CASE WHEN SEX_UPON_INTAKE LIKE '%Neutered%' OR   SEX_UPON_INTAKE LIKE '%Spayed %' THEN 'O'
ELSE 'X' END AS 중성화
from ANIMAL_INS 
ORDER BY ANIMAL_ID ASC

SELECT *
FROM (SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A, ANIMAL_OUTS B
WHERE A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY B.DATETIME-A.DATETIME DESC)
WHERE ROWNUM <=2

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

서브쿼리

• 중첩 서브쿼리 : 선택과목 B 테이블에서 'MATH'를 선택한 학생들의 이름을 찾아, 학생 A 테이블에서 모든 정보를 조회 (where절 안에)
• 인라인 뷰 (from절) : 각각의 공급자가 공급한 물품(item_name)과 총수량(total_amt)을 알고 싶은데, 두 정보가 서로 다른 테이블에 들어있는 경우, 공급자(suppliers)와 주문(oreders) 두 테이블로부터 각각 원하는 데이터를 불러와 출력하는 쿼리
• 스칼라서브쿼리 (select절) : 테이블에는 존재하지 않는 데이터(가격 평균/합 등)를 조회하고 싶을 때 사용하는 쿼리

SELECT * 
FROM student A
WHERE A.student_name IN (SELECT B.student_name
                          FROM subject B
                          WHERE B.subject_name = 'MATH');


SELECT A.item_name, subquery1.total_amt
FROM suppliers A,
     (SELECT supplier_id, SUM(B.amount) AS total_amt
      FROM orders B
      GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = A.supplier_id;



SELECT product_name, list_price,
       ROUND(
         (SELECT  AVG( list_price )
          FROM    products p1
          WHERE   p1. category_id = p2.category_id
         ), 2 ) avg_list_price
FROM  products p2
ORDER BY  product_name; 

연습문제

두번 이상 쓰여진 이름과 그 갯수를 출력하기

인라인뷰 사용
select *
from 
 (SELECT NAME,COUNT(NAME) COUNT FROM ANIMAL_INS 
WHERE NAME IS NOT NULL GROUP BY NAME)
where count >=2
order by name asc

HAVING 사용
SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME)>1
ORDER BY NAME 

연습문제

기존의 시간

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


SELECT HOUR(DATETIME) HOUR, COUNT(DATETIME) COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
HAVING HOUR >= 9 and HOUR <= 19

연습문제

MySQL

SELECT B.ANIMAL_ID , B.NAME
FROM ANIMAL_INS AS A 
RIGHT OUTER JOIN ANIMAL_OUTS AS B
ON A.ANIMAL_ID = B.ANIMAL_ID 
WHERE A.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID ASC

Oracle

SELECT B.animal_id, b.name
FROM ANIMAL_INS A full OUTER JOIN ANIMAL_OUTS B
on A.animal_id = b.animal_id
where A.name is null
and b.name is not null
order by B.animal_id , b.name asc

MySQL

-- 코드를 입력하세요
SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A INNER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID  = B.ANIMAL_ID
WHERE A.DATETIME >= B.DATETIME
ORDER BY A.DATETIME ASC

Oracle

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A INNER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.DATETIME>B.DATETIME
ORDER BY A.DATETIME ASC

MySQL

-- 코드를 입력하세요
SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
FROM ANIMAL_INS A INNER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.ANIMAL_ID= B.ANIMAL_ID AND A.SEX_UPON_INTAKE!= B.SEX_UPON_OUTCOME
ORDER BY B.ANIMAL_ID ASC

Orcle, MySQL

select ANIMAL_ID, NAME,SEX_UPON_INTAKE
from ANIMAL_INS a
where a.name = 'Lucy' or name= 'Ella' or name= 'Pickle' or name ='Rogan'or name=  'Sabrina'or name= 'Mitty'
order by ANIMAL_ID asc

Oracle, MySQL

select ANIMAL_ID,NAME
from ANIMAL_INS 
where ANIMAL_TYPE = 'Dog'and (name like '%el%' or name like '%EL%' or name like '%eL%' or name like '%El%')
order by name asc

CASE문 연습

CASE WHEN 컬럼명 = 'aaa' THEN 'bbb' WHEN 컬럼명2 = 'aa' THEN 'bb'

Oracle, MySQL

select ANIMAL_ID,NAME,  case when SEX_UPON_INTAKE  like '%Neutered%' then 'O'
when SEX_UPON_INTAKE like '%Spayed%' then 'O' else 'X' end 중성화
from ANIMAL_INS 
order by ANIMAL_ID asc


select ANIMAL_ID,case when ANIMAL_ID = 'A355753' then '하이하이'
when ANIMAL_ID = 'A373219' then '하이' else '기타' end as 중성화
from ANIMAL_INS
 

MySQL : 상위 두개 출력

마지막 LIMIT 2 

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



//이게 정답
SELECT B.ANIMAL_ID, B.NAME
FROM ANIMAL_INS A RIGHT OUTER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID 
ORDER BY B.DATETIME - A.DATETIME DESC
LIMIT 2

Oracle : 상위 N개 출력

WHERE ROWNUM <=2

/*
select sub.ANIMAL_ID, b.NAME
from 
(
select b.DATETIME-a.DATETIME 순위 , a.ANIMAL_ID 
from ANIMAL_INS a inner join ANIMAL_OUTS b 
on a.ANIMAL_ID = b.ANIMAL_ID
order by 순위 desc 
) sub 
inner join ANIMAL_OUTS b
on sub.ANIMAL_ID = b.ANIMAL_ID
WHERE ROWNUM <= 2
order by 순위 desc 
*/

select sub.ANIMAL_ID, b.NAME
from (
select  a.ANIMAL_ID 
from ANIMAL_INS a inner join ANIMAL_OUTS b 
on a.ANIMAL_ID = b.ANIMAL_ID
order by b.DATETIME-a.DATETIME desc 
) sub 
inner join ANIMAL_OUTS b
on sub.ANIMAL_ID = b.ANIMAL_ID
WHERE ROWNUM <= 2

MySQL

시간 포맷 바꿔주는 함수 : DATE_FORMAT(칼럼,'%y')

• 종류 : %Y,%y,%M,%m,%d,%D 시간 : %h : 다다름

GROUP BY + DATE_FORMAT + HAVING

문제

쿼리

-- 코드를 입력하세요
SELECT DATE_FORMAT(DATETIME,'%H') AS HOUR, COUNT(DATE_FORMAT(DATETIME,'%H')) AS COUNT
FROM ANIMAL_OUTS A
GROUP BY DATE_FORMAT(DATETIME,'%H')
HAVING HOUR >= 9 AND HOUR<20
ORDER BY DATE_FORMAT(DATETIME,'%H') ASC

문제

쿼리

-- 코드를 입력하세요
SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS A
GROUP BY NAME
HAVING COUNT>=2 AND NAME IS NOT NULL
ORDER BY NAME ASC

2중 조인

테이블구조

SELECT * 
FROM [Customers] A INNER JOIN [Orders] B
ON A.CustomerID =B.CustomerID 
INNER JOIN [OrderDetails] C 
ON B.OrderID=C.OrderID