[WIL]SQLD: SQL 기본 문법 1(WHERE, GROUP BY, HAVING, ORDER BY)+ Speak + 매카시 논문

8. SQL 기본 문법 1

-- 8-1 WHERE
--  1) WHERE절 개요: 모든 데이터가 아닌 원하는 데이터만 조회, 수정, 삭제하는 조건문
--  2) 연산자 종류
--   ①비교 연산자 !참고 - 문자 유형 간 비교 가능

--   ②SQL 연산자
--    •BETWEEN 값1 AND 값2 = '값1 ≤ 비교값 ≤ 값2'
--     ex)키가 180 ~ 190 사이인 사원을 조회 
SELECT * FROM EMP 
WHERE HEIGHT BETWEEN 180 AND 190;   
--    •IN (값1, 값2, ..., 값_리스트): 리스트에 있는 값 중에 어느 하나라도 일치하면 된다.
--     ex) 직책이 매니저 혹은 점원이고 부서번호가 20 혹은 30 인 사원을 조회 
SELECT * FROM EMP 
WHERE JOB IN ('MANAGER', 'CLERK')  
    AND DEPTNO IN (20, 30);
-- !참고 - AND는 둘 다 만족해야, OR은 둘 중 하나 만족
--    •LIKE '비교 문자열': 비교 문자열과 형태 일치한 것 조회 → 대소문자 구분X, WildCard( % , _ ) 문자 사용 비교
--     ◦% : 어떤 문자 포함한 모든 것 조회 ex) '나%', '%도', '%감자%': 나(비), 검도, 군감자튀김
--     ◦_ : 어떤 문자 포함한 두 글자 조회 ex) '커_', '_도': 커피, 수도
--      ex) '_2%': 그2형제자매 / '5 _ _ _': 5형제들
--    •IS NULL / IS NOT NULL: 비교 대상이 NULL(이 아닐 때) TRUE ex) 데이터에서 '포지션'이 빈 사원 삭제

--   ③논리 연산자: AND/OR → 앞뒤 조건 둘 다/중 하나 만족, NOT → 만족 시 결과 거짓으로 !논리 연산자 사용시 우선순위 주의(가로사용 권장)
--    ex) -- 수비수 아닌 선수 조회 
SELECT * FROM PLAYER 
WHERE NOT POSITION = '수비';

--연습
CREATE TABLE OLYMPIC (
	olympic_id  NUMBER    NOT NULL,
	host_city  CHAR(15) NOT NULL,
	host_year  NUMBER  NOT NULL,
    nation CHAR(10) NOT NULL,
    season CHAR(6) NOT NULL,
	history CHAR(10),
	CONSTRAINT OLYMPIC_PK PRIMARY KEY (olympic_id)
);
-- 2001년 이후에 열린 season이 'winter'인 olympic
SELECT * FROM OLYMPIC
WHERE host_year > 2001 AND season = 'winter';
-- nation 이 '국'으로 끝나거나 1980~2000에 개최한 올림픽의 정보(이하 SELECT * FROM 생략) 
WHERE nation LIKE '%국' OR host_year BETWEEN 1980 AND 2000;
-- nation 에 '아' 가 들어가고 1990 년 이후에 개최된 올림픽의 정보 
WHERE nation LIKE '%아%' AND host_year > 1990;


--   ④부정 연산자: 부정 논리 연산자(!= , ^= , <>) / 부정 SQL 연산자 (NOT ..)

--   ⑤연산자의 우선순위: () → NOT연산자 → 비교 연산자, SQL비교 연산자 → AND → OR(!그래도 가로 치자!)
-- !연습문제에서 주의사항 → FROM문의 데이터 형태 확인하고 답 추출(CHAR 데이터 타입에서 1001과 비교X)


-- 8-2. GROUP BY와 HAVING
--  1)집계 함수(Aggregate Function): 여러 데이터들의 정보를 집계하여 연산을 해주는 함수
--   ①집계 함수의 종류:COUNT(*/표현식), SUM/AVG/MAX/MIN/STDDEV/VARIAN([DISTINCT | ALL] 표현식)  
SELECT COUNT(*/Name) FROM OLYMPIC;
SELECT SUM(history) FROM OLYMPIC;

--   ②집계 함수의 특징: 집계 함수는 COUNT(*)를 제외하고는 NULL 값은 제외, (우선순위 앞서는)WHERE 절에 올 수 없다.

--  2)GROUP BY절 → 이전에 WHERE(+조건식)절로 묶음 'GB'(그룹 컬럼) 이후 HAVING절(그룹 조건)로 조건 부여
--    ⇒ FROM(테이블 '에서') → WHERE('조건식'을 걸어 데이터를 가져와) → GROUP BY('컬럼 그룹'을 짓고) → HAVING(필요하면 '그룹 조건'을 걸고) → SELECT(컬럼 데이터를 가져오는데) → ORDER BY(정해진 대로 '정렬') 순으로 실행
SELECT Country, SUM(history) FROM OLYMPIC GROUP BY Country;
SELECT Country, AVG(history) FROM OLYMPIC GROUP BY Country HAVING AVG(history) >= 1990;
-- !SELECT position '포지션', AVG(height) '평균 키' FROM player; → 애러"단일 그룹의 집계 함수가 아님"(연습문제 체크) / GROUP BY에서 Alias 사용 불가
SELECT first_name, AVG(salary) FROM EMPLOYEES;

-- 연습문제)
CREATE TABLE pokemon (
  pm_id NUMBER PRIMARY KEY NOT NULL,
  name VARCHAR2(20) NOT NULL,
  attr VARCHAR2(20),
	weight VARCHAR2(20)
);
CREATE TABLE pokemon (
  pm_id NUMBER PRIMARY KEY NOT NULL,
  name VARCHAR2(20) NOT NULL,
  attr VARCHAR2(20),
	weight VARCHAR2(20)
);

INSERT INTO pokemon VALUES (1, 'Bulbasaur', 'Grass', '30');
INSERT INTO pokemon VALUES (4, 'Charmander', 'Fire', '80');
INSERT INTO pokemon VALUES (25, 'Pikachu', 'Electric', '15');
INSERT INTO pokemon (pm_id, name) VALUES (54, 'Psyduck');
INSERT INTO pokemon (pm_id, name, attr) VALUES (76, 'Golem', 'Rock');
INSERT INTO pokemon (pm_id, name, weight) VALUES (86, 'Seel', '85');

SELECT attr, AVG(weight) FROM pokemon GROUP BY attr;
--> 'GB' 안 하면 "단일 그룹의 집계 함수가 아님" / SELECT 'attr' 안 하면 컬럼 없이 출력

-- 3)HAVING절: '집계된 결과'를 기준으로 조건 부여 → GROUP BY 이후 사용(단독 사용x) // WHERE절은 집계함수 사용 불가, (GB 이전)단독 사용 가능
SELECT attr, AVG(weight) "AVG_WEIGHT" FROM pokemon GROUP BY attr HAVING AVG(weight) > 50;

-- 연습문제)
CREATE TABLE student (
  id NUMBER,
  class NUMBER,
  score NUMBER
);

INSERT INTO student VALUES (1, 1, 5);
INSERT INTO student VALUES (2, 1, 5);
INSERT INTO student VALUES (3, 1, 5);
INSERT INTO student VALUES (9, 1, null);
INSERT INTO student VALUES (10, 1, null);

SELECT 
	class, 
	AVG(score), 
	COUNT(score), 
	COUNT(*)
FROM student
GROUP BY class;


-- 8-3. ORDER BY
--  1)기본 개념: 기본적으로 오름차순(1,2,3,4,5), 내림차순은 'DESC' 명시해야, Oracle/SQL Server에서는 NULL을 가장 큰/작은 값으로 간주
--   ex) 
SELECT dname, loc, deptno FROM dept ORDER BY dname, loc, deptno DESC;
--> dname 먼저 정렬하고 / 같은 dname인 경우 loc 기준으로 정렬 / 같은 dname과 loc인 경우에는 deptno를 이용해 내림차순 정렬 (+ORDER BY 여러 가지 기입 가능!)
--   연습문제) name과 height 를 조회, height를 내림차순 정렬해서 출력. 만약 키가 같다면 name 기준 알파벳 순서로 정렬
CREATE TABLE pokemon2 (
  pm_id NUMBER PRIMARY KEY NOT NULL,
  name VARCHAR2(20) NOT NULL,
  attr VARCHAR2(20) DEFAULT 'normal',
	height NUMBER
);

INSERT INTO pokemon2 VALUES (1, 'Bulbasaur', 'grass', 50);
INSERT INTO pokemon2 VALUES (2, 'Ivysaur', 'grass', 90);
INSERT INTO pokemon2 VALUES (3, 'Venusaur', 'grass', 250);

SELECT 
	name, 
	height 
FROM pokemon2
ORDER BY height DESC, name ASC;


-- 2)SELECT 문장 실행 순서
--5. SELECT 칼럼명 [ALIAS명]         -- 5. 데이터의 값을 출력/계산
--1. FROM 테이블명                   -- 1. 발췌대상 테이블 참조
--2. WHERE 조건식                    -- 2. 발췌 대상 데이터가 아닌 것은 제거
--3. GROUP BY 칼럼(Column)이나 표현식  -- 3. 행동들을 소그룹화
--4. HAVING 그룹조건식                -- 4. 그룹핑된 값의 조건에 맞는 것만을 출력
--6. ORDER BY 칼럼(Column)이나 표현식; -- 6. 데이터를 정렬

SELECT job, sal FROM emp GROUP BY job HAVING COUNT(*) > 0 ORDER BY sal;
--> Error 발생, sal 은 GROUP BY로 job 이 그룹화 될 것이기 때문에 
-- 일반 칼럼 sal 사용으로 에러가 발생

SELECT job FROM emp GROUP BY job HAVING COUNT(*) > 0 
ORDER BY MAX(empno), MAX(mgr), SUM(sal), COUNT(deptno), MAX(hiredate);
--> 정상 조회 가능, ORDER BY 에서 집계 함수를 이용한 정렬은 가능함