[TIL] 240219

Today

• SQL 프로그래밍

-- 2024-02-19
-- COUNT(열) : 선택한 열의 데이터(행) 개수를 반환합니다. 
USE employees;
SHOW DATABASES;

# 총 데이터 수 확인하기
SELECT COUNT(*) FROM salaries;
 
# 총 데이터 수 (중복제거) 확인하기
SELECT COUNT(DISTINCT emp_no) FROM salaries;

# GROUP BY와 함께 응용하기
SELECT from_date, COUNT(*)
FROM salaries
GROUP BY from_date
ORDER BY from_date
LIMIT 10;

# TIP : 위와 똑같지만 CONVERT() 등 행 조작을 하는 연산을 사용하면 INDEX를 안타서 속도가 왕 느려짐
SELECT from_date, COUNT(*)
FROM salaries
GROUP BY CONVERT(from_date, CHAR(10))
ORDER BY CONVERT(from_date, CHAR(10))
LIMIT 10;

-- 실습1
/*
1. from_date가 1986-06-26 ~ 1986-06-27 사이의 salary를 from_date로 그룹핑하고, 오름차순 정렬하여 출력하는 쿼리를 작성
*/

# 지현작성 : 조건절을 HAVING에 넣어 줌
SELECT from_date, SUM(salary) AS 'total'
FROM salaries
GROUP BY from_date
HAVING from_Date BETWEEN '1986-06-26' AND '1986-06-27'
ORDER BY from_date ASC;

# WHERE 조건절 사용할 경우 : (이걸로 사용해야함.)
SELECT from_date, SUM(salary) AS 'total'
FROM salaries
WHERE from_Date BETWEEN '1986-06-26' AND '1986-06-27'
GROUP BY from_date
ORDER BY from_date ASC;

/*
[HAVING으로 사용했을 때와 WHERE로 사용했을 때의 차이점]

WHERE일 경우 그룹핑 하기 전 조건체크해서 데이터 뽑고 그룹핑 하며 INDEX를 탑니다.
HAVING으로 할 경우 그룹핑 한 다음에 그룹핑한 것 기준으로 조건을 체크하며 INDEX를 안 탑니다.

가능하면, WHERE 조건절로 데이터를 줄이고 그룹핑 하는 방식으로 사용하는게 INDEX를 타는게 좋습니다.
GROUP BY에는 그룹핑한 데이터로 집계함수를 이용할 때, 즉 그룹핑하고 조건절 체크가 꼭 필요할 때만 사용하는게 좋습니다.
*/

-- 실습2
/*
1. 2002년의 월별 판매 총액을 계산하여 출력
*/

# 선생님 예시
SELECT CONVERT(from_date, CHAR(4)) AS from_year, SUM(salary) AS sum_sal
FROM salaries
GROUP BY CONVERT(from_date, CHAR(4))
ORDER BY CONVERT(from_date, CHAR(4));


# 지현
SELECT * from salaries WHERE from_date BETWEEN '2002-08-01' AND '2002-08-31';

SELECT CONVERT(year(from_date),CHAR(4)) AS 'YEAR', CONVERT(MONTH(from_date),CHAR(2)) AS 'MONTH', SUM(salary)
FROM salaries
WHERE YEAR(from_date) = 2002
GROUP BY CONVERT(from_date, CHAR(7));


-- 실습3
/*
1. 2002년도 8월 직원별 매출 합계액을 매출액 오름차순 출력하는 쿼리를 작성하시오
*/

# 지현
SELECT emp_no, SUM(salary) AS total
FROM salaries
WHERE from_date BETWEEN '2002-08-01' AND '2002-08-31'
GROUP BY emp_no
ORDER BY total ASC
;

-- 실습4
/*
YYYY-MM-DD 별, 상품코드별 주문수량 합계를 계산하여,
YYYY-MM-DD(regDate) 별, 상품코드(goodsCode)별 주문 수량을 출력하는 쿼리를 작성하시오.

선생님 TIP : ~~별, ~~묶어서, ~~그룹화해서 하는 말이 있으면 GROUP BY로 작성하라는 뜻임
*/

SELECT * FROM tbl_orderinfo;
SELECT * FROM tbl_orderdetail;

# 지현
SELECT 
	to_char(OI.regDate, 'YYYY-MM') AS order_date, OD.goodsCode, SUM(OI.orderAmount) AS SUM_OrderAmount, SUM(OD.amount) AS SUM_amount
FROM tbl_orderinfo AS OI
INNER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
GROUP BY order_date, OD.goodsCode
;

# GROUP BY 컬럼 순서 바꿔보기 : 기준 순위가 달라져서 결과데이터 순서가 좀 바뀜
SELECT 
	to_char(OI.regDate, 'YYYY-MM') AS order_date, OD.goodsCode, SUM(OI.orderAmount) AS SUM_OrderAmount, SUM(OD.amount) AS SUM_amount
FROM tbl_orderinfo AS OI
INNER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
GROUP BY OD.goodsCode, order_date
;

-- 실습5
/*
tbl_orderinfo
tbl_orderdetail

orderDate : YYYY-MM월 별
goodsCode : 상품코드 별

주문 년월(YYYY-MM)
주문 건수 : orderCnt
주문 상세의 주문 최대 금액
주문 상세의 주문 최소 금액
최대 주문 건수
최소 주문 건수
*/

SELECT * FROM tbl_orderinfo;
SELECT * FROM tbl_orderdetail;

SELECT
	to_char(OI.orderDate, 'YYYY-MM') AS order_date
	, OD.goodsCode
	, MAX(OD.amount) AS MAX_amount
	, MIN(OD.amount) AS MIN_amount
	, AVG(OD.orderedCnt) AS AVG_cnt
	, SUM(OD.orderedCnt) AS SUM_cnt
	, MAX(OD.orderedCnt) AS MAX_cnt
	, MIN(OD.orderedCnt) AS MIN_cnt
FROM tbl_orderdetail AS OD
INNER JOIN tbl_orderinfo AS OI ON OI.orderNo = OD.orderNo
GROUP BY order_date, OD.goodsCode;

# 1. orderDate 별
SELECT
	to_char(OI.orderDate, 'YYYY-MM') AS order_date
	, MAX(OD.amount) AS MAX_amount
	, MIN(OD.amount) AS MIN_amount
	, AVG(OD.orderedCnt) AS AVG_cnt
	, SUM(OD.orderedCnt) AS SUM_cnt
	, MAX(OD.orderedCnt) AS MAX_cnt
	, MIN(OD.orderedCnt) AS MIN_cnt
FROM tbl_orderdetail AS OD
INNER JOIN tbl_orderinfo AS OI ON OI.orderNo = OD.orderNo
GROUP BY order_date;

# 위 데이터 두개로 쪼개기!
# 2. goodsCode 별
SELECT
	OD.goodsCode
	, MAX(OD.amount) AS MAX_amount
	, MIN(OD.amount) AS MIN_amount
	, AVG(OD.orderedCnt) AS AVG_cnt
	, SUM(OD.orderedCnt) AS SUM_cnt
	, MAX(OD.orderedCnt) AS MAX_cnt
	, MIN(OD.orderedCnt) AS MIN_cnt
FROM tbl_orderdetail AS OD
INNER JOIN tbl_orderinfo AS OI ON OI.orderNo = OD.orderNo
GROUP BY OD.goodsCode;

-- 실습6
/*
tbl_orderinfo
tbl_orderdetail

tbl_orderdetail 기준, 2023년도 주문 총액이 연평균 총액보다 큰 주문의 
주문번호(orderNo), 주문일자(tbl_orderdetail -> regDate)를 출력하는 쿼리를 작성하세요
*/

SELECT * FROM tbl_orderinfo;
SELECT * FROM tbl_orderdetail;

SELECT 	OI.orderNo, OD.regDate, OD.amount
FROM tbl_orderdetail AS OD
INNER JOIN tbl_orderinfo AS OI ON OI.orderNo = OD.orderNo
WHERE OD.amount > (
	SELECT AVG(amount) FROM tbl_orderdetail WHERE to_char(regDate, 'YYYY') = '2023'
);

/*
[지현 실패 답안]

SELECT 	OI.orderNo, OD.regDate, OD.amount
FROM tbl_orderdetail AS OD
INNER JOIN tbl_orderinfo AS OI ON OI.orderNo = OD.orderNo
WHERE OD.amount > AVG(OD.amount)    # 서브쿼리 안쓰고 냅다 넣어버려서 오류남...
;
*/

-- orderinfo와 orderdetail 값 맞추기
SELECT * FROM tbl_orderinfo;
SELECT * FROM tbl_orderdetail;

SET autocommit=0;

START TRANSACTION;

# 실패한 UPDATE 쿼리!
UPDATE tbl_orderinfo AS OI
INNER JOIN tbl_orderdetail AS OD
SET OI.orderAmount = (
	SELECT SUM(OD.amount) FROM tbl_orderdetail as OD2 WHERE OI.orderNo = OD2.orderNo
) 
WHERE OI.orderNo = OD.orderNo
;

# UPDATE 쿼리 다시 짜기
UPDATE tbl_orderinfo AS OI INNER JOIN tbl_orderdetail AS OD          # JOIN 안써도 됨 이런식으로 쓸 수도 있다고 예시로 작성...!
SET OI.orderAmount = (
	SELECT SUM(OD2.amount) FROM tbl_orderdetail AS OD2 WHERE OD2.orderNo = OI.orderNo
)
WHERE OI.orderNo = OD.orderNo;
# 한번 더 깔끔하게 가공하기 (최종본!!!!)
UPDATE tbl_orderinfo AS OI
SET OI.orderAmount = (
	SELECT SUM(OD.amount) FROM tbl_orderdetail AS OD WHERE OD.orderNo = OI.orderNo
);

# 주문번호 별 orderdetail 총액 조회하는 쿼리
SELECT OD.orderNo,
SUM(OD.amount) AS sum_amount
FROM tbl_orderinfo AS OI
INNER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
WHERE OI.orderNo = OD.orderNo
GROUP BY OD.orderNo;

ROLLBACK;

COMMIT;

# 확인해보기
SELECT 
	OI.orderNo, OD.orderNo, OI.orderAmount, SUM(OD.amount)
FROM tbl_orderinfo AS OI
INNER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
GROUP BY OI.orderNo
;


-- 다차원 피벗 : 특정 항목에 대한 기간별 합계 등의 행/열 데이터 모두 활용하여 통계 데이터를 출력하는 것을 다차원 피벗이라고 합니다.
USE employees;
DESC salaries;
SELECT *, COUNT(emp_no) FROM salaries 
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31' 
GROUP BY emp_no
ORDER BY emp_no 
LIMIT 500;

SELECT *
FROM salaries
WHERE emp_no = 10009;

-- 실습
/* salary 에서 직원번호에 따른 2002년 1월~8월까지의 매출 평균*/
# 지현
SELECT 
	emp_no
	, AVG(case MONTH(from_date) when 1 then salary ELSE 0 END) AS '1월'
	, AVG(case MONTH(from_date) when 2 then salary ELSE 0 END) AS '2월'
	, AVG(case MONTH(from_date) when 3 then salary ELSE 0 END) AS '3월'
	, AVG(case MONTH(from_date) when 4 then salary ELSE 0 END) AS '4월'
	, AVG(case MONTH(from_date) when 5 then salary ELSE 0 END) AS '5월'
	, AVG(case MONTH(from_date) when 6 then salary ELSE 0 END) AS '6월'
	, AVG(case MONTH(from_date) when 7 then salary ELSE 0 END) AS '7월'
	, AVG(case MONTH(from_date) when 8 then salary ELSE 0 END) AS '8월'
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31'           # 최대한 포맷팅 연산 없는게 속도에 유리함.
GROUP BY emp_no
;

# 검증
SELECT emp_no, salary, to_char(from_date, 'YYYY-MM'), COUNT(from_date)
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31' 
GROUP BY emp_no, to_char(from_date, 'YYYY-MM')
LIMIT 100;


-- WITH ROLLUP : 그룹 단위로 그룹합계, 총합계를 만들어주는 키워드
/**/

-- 사용해보기
SELECT to_char(from_date, 'YYYY-MM'), SUM(salary) AS sum_salary
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31' 
GROUP BY to_char(from_date, 'YYYY-MM') WITH ROLLUP;

-- 실습1 : 위 데이터에서 YYYY-MM 별로 emp_no 별로 테이터 가져오기 
SELECT emp_no, to_char(from_date, 'YYYY-MM'), SUM(salary) AS sum_salary
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31' 
GROUP BY to_char(from_date, 'YYYY-MM'), emp_no WITH ROLLUP;

# 지현 : 월별 말고 년도별로 보는게 더 나을꺼같아서 바꿔봄
SELECT emp_no, to_char(from_date, 'YYYY'), SUM(salary) AS sum_salary
FROM salaries
WHERE (from_date BETWEEN '1995-01-01' AND '2002-12-31') && (emp_no BETWEEN 10001 AND 10010)
GROUP BY to_char(from_date, 'YYYY'), emp_no WITH ROLLUP;

-- 실습2 : 위와 유사한 결과가 나오게 서브쿼리를 이용하여 쿼리를 작성하시오! (with rollup 없이)
# 지현답안ㅎㅎ(1차)
SELECT 
	A.emp_no, A.from_date, A.sum_salary, B.total
FROM (
	SELECT 
		emp_no
		, to_char(from_date, 'YYYY-MM') AS from_date
		, SUM(salary) AS sum_salary
	FROM salaries
	WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31' 
	GROUP BY to_char(from_date, 'YYYY-MM'), emp_no
) AS A
INNER JOIN (
	SELECT
		to_char(from_date, 'YYYY-MM') AS from_date
		, SUM(salary) AS total
	FROM salaries
	WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31' 
	GROUP BY to_char(from_date, 'YYYY-MM')
) AS B ON B.from_date = A.from_date
;

# 2차 도전
USE employees;
SELECT
	emp_no, from_date, sum_salary
FROM(
	SELECT 
		emp_no
		, to_char(from_date, 'YYYY-MM') AS from_date
		, SUM(salary) AS sum_salary
	FROM salaries
	WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31' 
	GROUP BY to_char(from_date, 'YYYY-MM'), emp_no
	UNION
	SELECT
		''
		,concat(to_char(from_date, 'YYYY-MM'), '월 총합') AS from_date
		, SUM(salary) AS sum_salary
	FROM salaries
	WHERE to_char(from_date, 'YYYY-MM') BETWEEN '2002-01-01' AND '2002-12-31' 
	GROUP BY to_char(from_date, 'YYYY-MM')
	UNION 
	SELECT
		''
		, concat('총합계') AS from_date
		, SUM(salary) AS sum_salary
	FROM salaries
	WHERE to_char(from_date, 'YYYY-MM') BETWEEN '2002-01-01' AND '2002-12-31' 
) AS A
;
	

# 직원번호별 월별 합계
SELECT 
	emp_no
	, to_char(from_date, 'YYYY-MM') AS from_date
	, SUM(salary) AS sum_salary
FROM salaries
WHERE from_date BETWEEN '2002-01-01' AND '2002-12-31' 
GROUP BY to_char(from_date, 'YYYY-MM'), emp_no;

# 월별 총합
SELECT
	'' AS emp_no
	, concat(to_char(from_date, 'YYYY-MM'), '월 총합') AS from_date
	, SUM(salary) AS sum_salary
FROM salaries
WHERE to_char(from_date, 'YYYY-MM') BETWEEN '2002-01-01' AND '2002-12-31' 
GROUP BY to_char(from_date, 'YYYY-MM')
;

# 총합
SELECT
	'' AS emp_no
	, concat('총합계') AS from_date
	, SUM(salary) AS sum_salary
FROM salaries
WHERE to_char(from_date, 'YYYY-MM') BETWEEN '2002-01-01' AND '2002-12-31' 
;

-- Common Table : 임시테이블로 쿼리문을 실행하는 동안에만 사용가능한 테이블 입니다.
USE maria;

WITH temp_orderDetail(goodsCode, total) AS (
	SELECT goodsCode, SUM(amount)
	FROM tbl_orderDetail
	GROUP BY goodsCode
)
SELECT * FROM temp_orderDetail;        # 이렇게 만든 임시테이블은 해당 쿼리를 실행할 동안만 조회 가능

SELECT * FROM temp_orderDetail;        # 이것만 실행하면 이런 테이블 없다고 에러남!

• Java 기초 문법1 시험 오답노트

1. 다음 중 변수나 상수 선언 명령으로 적절한 것은 O, 그렇지 않은 것은 X를 표시하고, 이유를 적으시오.
1) boolean flag;              답 : o 
2) byte public;               답 : x / 예약어 사용 불가
3) short data$;               답 : o
4) char 이름;                 답 : o / 가능은 하나 한글 사용 지양함이 좋음
5) int name3;                답 : o
6) long 3name;               답 : X / 첫글자 숫자 안됨 
7) final double USER_DATE;   답 : o
8) final int &MY_DATA;       답 : o
9) final float HELLO^^;      답 : x / 특수문자 $, _ 제외 불가

4. 다음 산술 연산의 결과를 적으시오.
1) System.out.println(2 + 3 / 2);        답 : 3
2) System.out.println(12 % 3 + 5 / 2);   답 : 2
3) System.out.println((3 + 7 / 2) % 4);  답 : 2

5. 다음 연산의 결과를 적으시오.
int a=4, b=5, c=6;
1) System.out.println(false && a-- > 6);     답 : FALSE
2) System.out.println(true || b++ > 6);      답 : TRUE
3) System.out.println(true ^ c++ > 6);       답 : TRUE
4) a:                                        답 : 3
5) b:                                        답 : 6
6) c:                                        답 : 7

6. OOP(Object Oriented Programming)의 4가지 특징에 대하여 설명하시오.
답 : 
- 상속 
: 기존의 클래스를 재활용하여 새로운 클래스를 작성하는 자바의 문법 요소로 상위 클래스로부터 확장된 여러 개의 하위 클래스들이 모두 상위 클래스의 속성과 기능들을 간편하게 사용할 수 있으며 오버라이딩을 통해 상속받은 메서드를 재정의해서 사용할 수도 있음.
- 추상성 
: 중요한 부분을 강조하기 위해 불필요한 세부 사항들은 제거하고 가장 본질적이고 공통적인 부분만을 추출하여 표현하는 것, 즉 객체의 공통적인 속성과 기능을 추출하여 정의하는것을 의미. 인터페이스로 객체의 기능과 역할을 추상적으로 정의 후 해당 인터페이스를 상속받아 구현하는 것
- 캡슐화 
: 캡슐화란 클래스 안에 서로 연관있는 속성과 기능들을 하나의 캡슐(capsule)로 만들어 데이터를 외부로부터 보호하는 것으로 데이터 보호(data protection)와 데이터 은닉(data hiding)을 목적으로 함.
- 다형성 
: 어떤 객체의 속성이나 기능이 상황에 따라 여러 가지 형태를 가질 수 있는 성질을 의미, 부모 클래스를 상속받은 클래스의 경우 상황에 따라 자기 자신으로 정의되기도 부모클래스로도 정의할 수 있음.

• Java 기초 문법2 시험 오답노트

14. StudentTest의 출력 결과가 다음처럼 나오도록 Student 클래스를 구현하시오.
public class StudentTest {
	public static void main(String[] args) {
    	HashSet<Student> set = new HashSet<Student>();
    	set.add(new Student("100", "홍길동"));
        set.add(new Student("200", "이순신"));
        set.add(new Student("300", "정약용"));
        set.add(new Student("400", "강감찬"));
        set.add(new Student("100", "송중기"));
        
        System.out.println(set);
    }
}

<출력결과>
100 : 홍길동, 200 : 이순신, 300 : 정약용, 400 : 강감찬

답:

class Student {
	String k;
    String v;
    Student() {}
    Student(String k, String v) {
    	this.k = k;
        this.v = v;
    }
    @Override
    public String toString() {
        return k + " : " + v;
    }
}

+ 240221 bbbooo님 의견 추가!! : toString() 오버라이딩 추가

19. try{} 블록이 수행되면 블록으로서 주로 열린 파일이나 네트워크 리소스의 해제를 수행하는 블록을 구현하는 예약어는 _____ 입니다.
답 : finally

20. 바이트로 읽어 들인 자료를 문자로 변환해주는 스트림은 _____ 입니다.
답 : InputSreamReader

Review

• Java 재시험을 볼 예정일 듯함 => 이번주 내로 자바책 정리 완료 필요
  컬렉션(정리중)/람다식/자바IO
• Java 1차, 2차 기초 시험 오답노트 진행 => 틀렸던거 또 틀리지 않도록!!!

TO DO

• Java 최종 정리(~2/25)
• Maria DB 정리 수업진도 따라잡기