1교시 : 깃헙에서 공공데이터? 받아옴
ZIP파일 다운
여기서 깔린 파일들 확인가능
명령프롬프트에서 하는건지
psql shell에서 하는건지 확인!
근데 둘 다 되는디
오타 때문에 frunction_example 됨..
집에서는 잘하자..
2교시
논리 비교연산_ postgres 에서 실습
DROP TABLE IF EXISTS example_table;
CREATE TABLE example_table (
bool_col boolean,
num_col integer
);
INSERT INTO example_table
VALUES
(true, 0),
(false, 5),
(NULL, 9);
SELECT*FROM example_table;
SELECTFROM example_table WHERE bool_col = 'false';
SELECTFROM example_table WHERE bool_col IS FALSE;
NULL 과 FALSE는 비교못함 그래서 FALSE의 역은 TRUE 만 나옴
SELECTFROM example_table WHERE bool_col != 'false';
그러나 IS NOT이면 NULL 도 나옴
SELECTFROM example_table WHERE bool_col IS NOT FALSE;
SELECT NULL = 'false' AS equal_false, NULL IS FALSE AS is_false;
null = false인 결과를 equal_false라는 칼럼에 넣어서 출력해라는 의미
널과 펄스가 같냐 > 모른다(null 출력), 널이 펄스냐 > 거짓(false) 출력
수학으로 하면 모른다고(null) 안나오고, 말로하면 거짓이라고(false)나오네
DROP TABLE IF EXISTS example_table;
CREATE TABLE example_table (
num_col INTEGER
);
INSERT INTO example_table
VALUES
(1),
(0),
(5),
(9);
SELECTFROM example_table WHERE 1<= num_col AND num_col <=9;
SELECTFROM example_table WHERE num_col BETWEEN 1 AND 9;
SELECT*FROM example_table WHERE num_col NOT BETWEEN 1 AND 9;
SELECT date '2020-07-15' + integer '7' AS result;
'2020-07-22' 로 나옴 (칼럼자료형은 date)
SELECT date '2020-07-15' +time '13:00' AS result;
date와 time이 합쳐지면 TIMESTAMP라는 자료형으로 변환되서 나옴
SELECT date '2020-07-15' +time '24:00' AS result;
2020-07-16 00:00:00 timestamp without time zone으로 나옴
SELECT date '2020-07-15' - date '2020-07-01' AS result;
14 (integer)로 나옴
select CURRENT_TIME;
현재시간 나옴 (소문자도 동작함)
SELECT LOCALTIME;
3교시
frunction_example에서 실습
SELECTFROM real_amount;
SELECTFROM assumption_amount;
SELECT*FROM exception;
SELECT FROM real_amount
WHERE EXISTS (
SELECTFROM assumption_amount
);
--row가 있으니까 where절이 참 >> 전부 조회됨
SELECT FROM real_amount
WHERE EXISTS (
SELECT FROM exception
);
--row가 없으니까 where절이 거짓 >> 조회되는거 없음
=========
SELECT *FROM real_amount
WHERE amount IN (
10,20,30
);
-- amount가 10,20,30이 있는것들은 참이라서 출력됨
SELECT *FROM real_amount
WHERE amount NOT IN (
10,20,30
);
-- amount가 10,20,30이 없는것들은 참이라서 출력됨
========
SELECT *FROM real_amount
WHERE 10 = ANY (
SELECT amount FROM assumption_amount
);
-- 하나라도 10이있으면 참이라서 결과가 나옴
SELECT *FROM real_amountWHERE 11 = ANY (
SELECT amount FROM assumption_amount
);
-- 하나라도 10이있으면 참이라서 결과가 나옴
==========
SELECT *FROM real_amount
WHERE 10 <= ALL (
SELECT amount FROM assumption_amount
);
-- 모두 10보다 크거나 같으므로 출력됨
-- 20<= 하면 모두 그렇지는 않으므로 출력 안됨
==========
패턴매칭 연산 P.202
%가 들어가면 모든 문자열을 나타냄
SELECT *FROM real_amount
WHERE name LIKE 'banana';
-- 바나나 나옴
SELECT *FROM realamount
WHERE name LIKE 'bana__';
-- 은 문자열 하나를 나타내므로 이것도 바나나 나옴
SELECT *FROM real_amount
WHERE name LIKE '%';
-- %는 모든 문자열을 나타내므로 row 다 나옴
SELECT
'pink' LIKE 'in' AS pattern1,
'pink' LIKE 'p%k'AS pattern2,
'pink' LIKE 'p',
'pink' LIKE '_IN',
'pink' LIKE 'p';
-- as 붙이면 칼럼명 써져서 나오고 안붙이면 ?칼럼으로 나옴
-- 언더바는 문자 갯수만틈 맞춰야 하고 대소문자 틀리면 안됨
-- T/T/T/F/F나옴
==========
POSIX식 정규식
[ _ = . ]으로 쓰고 [ % = * ] 로 씀
p%k = p*k
p__k = p..k
예시 ) 자주쓰이는 정규식 으로 검색하면
https://hee-kkk.tistory.com/22
이런식으로 나옴
==========
THEN 에 들어간 값들의 컬럼명은
END 옆에 쓴 grade 가 됨
SELECT id,
name,
score,
CASE
WHEN score <= 100 AND score >= 90 THEN 'A'
WHEN score <= 89 AND score >= 80 THEN 'B'
WHEN score <= 79 AND score >= 70 THEN 'C'
WHEN score < 70 THEN 'F'
END grade
FROM student_score
ORDER BY grade ASC; > 이거 추가하면 성적순으로 뽑아줌
============
4교시
실습 준비
(1) 
(2) 
(3) 
실습 P. 209 문제
- 한해동안 특수학교에서 졸업한 학생의 수가 25명 이상이었던 학교 이름과 남, 여 졸업생 수를 출력하라
SELECT 학교명, 졸업남자수, 졸업여자수 FROM graduates
WHERE 학교급명 = '특수학교' AND (졸업남자수 + 졸업여자수) >= 25 ;
-- 2. 2015년에 남, 여 통학 취업률이 50%가 넘은 학교의 지역명 이름과 취업률 (%)을 출력하라
SELECT EXTRACT(YEAR FROM 기준년도),
지역명,
학교명,
100(취업남자수 + 취업여자수)/(졸업남자수+졸업여자수) AS 취업률
FROM graduates
WHERE EXTRACT (YEAR FROM 기준년도) = 2015
AND (졸업남자수 + 졸업여자수 ) > 0
AND 100(취업남자수 + 취업여자수)/(졸업남자수+졸업여자수) >= 50;
-- where 이하를 이렇게 해도 됨
-- 기준년도 >= '2015-01-01' and 기분년도 < '2016-01-01' and (졸업남자수 + 졸업여자수) ! =0
-- and 100*(취업남자수 + 취업여자수)/(졸업남자수+졸업여자수) >= 50;
-- 3. 경기도 고양시 일산 지역에 잇는 고등학교의 각 연도별 졸업생 정보를 다음의 조건을 만족하도록 출력하라
-- 진학률 기준 내림차순으로 출력한다
-- 졸업생이 없으면 진학률은 0%로 표시한다.
-- 지역명에 "고양시 일산"이 포함되어있는 로우를 검색해야 한다.
SELECT EXTRACT( YEAR FROM 기준년도),
학교명,
CASE
WHEN (졸업남자수 + 졸업여자수) =0 THEN 0
WHEN (졸업남자수 + 졸업여자수) > 0 THEN 100*(진학남자수 + 진학여자수)/(졸업남자수 + 졸업여자수)
END 진학률
FROM graduates
WHERE 지역명 LIKE '%고양시 일산%'
ORDER BY 진학률 DESC;
※참고※
100(진학남자수 + 진학여자수)/(졸업남자수 + 졸업여자수) 는 되는데
(진학남자수 + 진학여자수)/(졸업남자수 + 졸업여자수)100은 안되는 이유
후자는
앞에 먼저 계산해서 소숫점 버리고 정수로 변환후 100을 곱함
그럼 0이 나옴
전자는 100배를 하고 나누기 때문에 값이 나옴.
- 티쳐의 답
SELECT EXTRACT( YEAR FROM 기준년도),
학교명,
CASE
WHEN (진학남자수 + 진학여자수) = 0 OR (졸업남자수 + 졸업여자수) =0 THEN 0
WHEN (진학남자수 + 진학여자수) != 0 THEN 100*(진학남자수 + 진학여자수)/ (졸업남자수 + 졸업여자수)
END AS 진학률 --END 진학률도 됨.
from graduates
WHERE 지역명 LIKE '경기도 고양시 일산%'
ORDER BY 진학률 DESC;
-- from 과 WHERE의 결과가 셀렉트로 올라가고
-- 셀렉트의 결과가 오더바이로 간다고..??
===============
6교시
SELECT
item_type
FROM rating;
-- 중복된거 많음
SELECT
DISTINCT item_type
FROM rating;
-- 중복된거 없어짐
-- DISTICT는 여러컬럼에 중복 적용 가능
SELECT
item_type
FROM rating
ORDER BY item_type;
SELECT
DISTINCT item_type, item_id
FROM rating
ORDER BY item_type;
-- DISTICT는 여러컬럼에 중복 적용 가능
SELECT
item_type
FROM rating
GROUP BY item_type
SELECT
item_type, item_id
FROM rating
GROUP BY item_type, item_id;
--셀렉트 절과 그룹 바이 절에는 항상같은 칼럼을 적어야 한다
-- SELECT에 GROUP BY 관련 함수를 추가할 수 있다.
-- 그룹바이는 집계함수 사용 가능, 집계 연산 후 HAVING 사용가능
SELECT
item_type, count(*)
FROM rating
GROUP BY item_type;
;
;
;
;
;
;
;
;
;
;
;
=============
7교시
()UNION()과 ()UNION ALL() ; 합집합
두개의 쿼리문으로 하나의 테이블을 만드는 명령어
- 두SQL은 컬럼의 수가 동일해야함
- 두SQL은 컬럼의 자료형이 동일해야함
UNION 과 UNION ALL 의 차이
1+1=1 과 1+1=2 같은것
123+12=123과 123+12=12312
중복을 제거하고 붙이는것과 중복상관없이 걍 다 합친것
()INTERSECT(), ()INTERSECT ALL() ; 교집합
135 INTERSECT 124 = 1
()EXCEPT(), ()EXCEPT ALL() ; 차집합
135 EXCEPT 124 = 35 > 잘안쓴데-!!
SELECT FROM rating, ramen;
-- SELECT FROM rating, ramen;
(가로로 묶어버림)
. 접근 제어자 (access modifier)
SELECT rating.user_id, rating.rating,
ramen.name, ramen.quantity, ramen.is_spicy
FROM rating, ramen
WHERE ramen.id = rating.item_id AND rating.item_type = 'ramen';
SELECT rating.user_id, rating.rating,
ramen.name, ramen.quantity, ramen.is_spicy
FROM (rating JOIN ramen
ON ramen.id = rating.item_id AND rating.item_type = 'ramen';
-- 두방식 모두 같은 내부 동작을 수행
-- 가독성이 좋기 때문에 JOIN 선호
DROP TABLE IF EXISTS left_table;
DROP TABLE IF EXISTS right_table;
CREATE TABLE left_table (
id INTEGER,
left_contents VARCHAR
);
INSERT INTO left_table VALUES
(1,'가'),
(2,'나'),
(3,'다'),
(4,'라');
CREATE TABLE right_table (
id INTEGER,
left_contents VARCHAR
);
INSERT INTO right_table VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d');
SELECT FROM left_table INNER JOIN right_table ON left_table.id = right_table.id;
-- SELECT FROM left_table INNER JOIN right_table USING(id);
USING은 칼럼명이 같을 때 쓸 수있음.
=======
DROP TABLE IF EXISTS left_table;
DROP TABLE IF EXISTS right_table;
CREATE TABLE left_table (
id INTEGER,
left_contents VARCHAR
);
INSERT INTO left_table VALUES
(1,'가'),
(2,'나'),
(3,'다'),
(5,'라');
CREATE TABLE right_table (
id INTEGER,
left_contents VARCHAR
);
INSERT INTO right_table VALUES
(1,'a'),
(2,'b'),
(3,'E'),
(4,'d');
(1) SELECT * FROM left_table INNER JOIN right_table USING(id);
INNER JOIN 겹치는 것만 (3줄) 교집합
(2) SELECT * FROM left_table LEFT JOIN right_table USING(id);
LEFT JOIN 왼쪽기준 (4줄) 좌집합 ㅋㅋㅋ
(3) SELECT * FROM left_table RIGHT JOIN right_table USING(id);
RIGHT JOIN 오른쪽기준 (4줄) 우집합 ㅋㅋㅋ
(4) SELECT * FROM left_table FULL JOIN right_table USING(id);
FULL JOIN 전부 다 (5줄) 합집합
8교시
p. 344 데이터모델링
필기 퍼가요 ~ ♡