MSSQL 기본 쿼리 연습하기

--- SELECT FROM 구성
-- SELECT ALL | DISTINCT   칼럼명 | *
-- FROM  테이블명
-- WHERE
-- GROUP BY
-- HAVING
-- ORDER BY

--- 데이터 조회---
SELECT * FROM Person;
SELECT * FROM Drug;

--- 필터링 ---
SELECT * FROM Person WHERE sex = 1; --9307
SELECT * FROM Person WHERE sex = 0; --9263

--- 카운팅 ---
SELECT COUNT(*) FROM Person; -- 18570  -- 전체 row개수만

SELECT COUNT(*) FROM Person -- 9263, 9307 -- sex칼럼별로 <sex값 없이 row개수만>***
GROUP BY Sex;

SELECT Sex, COUNT(*) FROM Person -- 9263, 9307 -- sex칼럼별로 + <SEX칼럼표시> + <row개수>***
GROUP BY Sex;

--- GROUP BY with 집계함수---
SELECT * FROM Person GROUP BY Sex; -- GROUP BY 는 항상 집계함수가 포함되어있어야 된다. 안그러면 오류

SELECT COUNT(*) FROM Person GROUP BY Sex; --칼럼자리에 GROUP BY한 칼럼명을 입력하지 않으면, 해당 칼럼의 요소가 보이질 않음

--- 칼럼별 DISTINCT(유니크한 값) 보기 --- ****
SELECT DISTINCT personid FROM Drug; --- SELECT 다음에 DISTINCT 칼럼명

--- 상위 TOP 몇개만 보기 --- ***
SELECT TOP 3 * FROM Drug;
SELECT TOP 10 * FROM Person;

--- WHERE 조건에 칼럼명 BETWEEN AND 넣어보기---***
SELECT * FROM DRUG;
SELECT * FROM Drug WHERE duration BETWEEN 3 AND 5

SELECT * FROM Electrocardiogram;
SELECT * FROM Electrocardiogram WHERE QTc BETWEEN 400 AND 500;

--- WHERE 조건에 칼럼명 = OR , 칼럼명 = OR ---
SELECT TOP 15 * FROM Drug;
SELECT * FROM Drug WHERE atccode = 'N01BB02' OR atccode = 'N07AA02'; --- ***문자열에 쌍따옴표 x 그냥 따옴표

--- WHERE 조건에 칼럼명 IN ( '', '', '') 써보기 ---***
SELECT * FROM Drug WHERE atccode IN ( 'N01BB02', 'N07AA02');

--- WHERE 조건에 칼럼명 LIKE '% , [A-Z] 정규식' 사용하기 ---***
--- %는 앞뒤로 모든 문자가 줄지어 나타남
--- [A-Z]는 한글자에 대해서 매칭
SELECT * FROM Drug WHERE atccode LIKE 'J01%' --*** J01로 시작하는 모든 것
SELECT * FROM Drug WHERE atccode LIKE 'J01[A-Z][A-Z}10' ---*** J01[?][?]10 가운데 2개 아무거나

--- FROM > WHERE GROUP BY HAVING ORDER BY에서 ---

--- ORDER BY 칼럼명 DESC/ASC ---***
SELECT TOP 15 * FROM Drug;

SELECT * FROM Drug ORDER BY personid DESC;
SELECT * FROM Drug ORDER BY personid, drugdate ASC;

SELECT * FROM Drug WHERE duration > 90 
ORDER BY personid ASC,drugdate DESC     -- WHERE (g) (h) ORDER BY 순

--- 집계함수 후 AS 새로운 칼럼명 ---***
-- cast()해주고 계산하기
-- AVG, SUM, COUNT, MAX, MIN
SELECT AVG(duration) AS avg_duration FROM Drug -- 7 : 문자열로 입력되어있을 시, cast( as float)를 안해주면 소수점 다 짤려나온다.
SELECT AVG( cast(duration AS float)) as avg_duration FROM Drug  -- 7.48

SELECT COUNT(*) AS row_cnt FROM Drug;

SELECT drugdept FROM Drug GROUP BY drugdept; -- 그룹별로, 집계없이 해당 칼럼만 보면, 그 종류만 나온다
SELECT drugdept, COUNT(*) FROM Drug GROUP BY drugdept; -- 그룹별로 종류 + cnt
SELECT drugdept, COUNT(*) AS drugdept_cnt FROM Drug GROUP BY drugdept;

-- route의 그룹별, duration의 최대값 구하기
SELECT DISTINCT route FROM Drug;

SELECT route, MAX(duration) AS max_duration FROM Drug
GROUP BY route