플레이데이터 백엔드 개발 8기 3주차 회고

 

배운 내용 

---

1. 예외 처리

• 자바에서 예외 처리를 할 때, try 와 catch를 사용한다.
• 먼저 try 블록 안에는 예외 발생 가능성이 있는 코드를 작성한다.
• catch에는 블록안에는 예외 처리를 실행하는 코드를 작성한다.
• 여기서 finally이라는 키워드가 있는데, 이 것은 예외 발생 여부와 상관없이 실행해야 하는 코드를 작성하는 곳이다.

public class TryCatchExample {
    public static void main(String[] args) {

        try {
            // try 블록에는 예외 발생 가능성이 있는 코드를 배치합니다.
        } catch (Exception e) {
            // catch 블록에는 try에서 실제 예외가 발생했을 시
            // 실행할 내용을 작성합니다.
            e.printStackTrace(); // 예외 원인을 알고싶다고 할때 (개발자가 어디가 원인인지 앟고 싶을때)
        } finally {
        	// 예외 처리와 상관없이 출력할 코드
        }
    }
}

 

• 하나의 try 블록 안의 코드는 다양한 예외가 발생할 수 있다.
• 그렇기 때문에 예외 종류마다 다르게 처리를 하고 싶다면, 다중 catch 문을 작성하여 처리해주면 된다.

public class TryCatchExample2 {
    public static void main(String[] args) {
        int[] arr = new int[5];

        try {
            System.out.println("정수: ");
            int num = sc.nextInt();

            int result = 100 / num;
            System.out.println(arr[result]);

            String s = null;
            s.equals("메롱");


            /*
             - 다중 catch문은 실제로 에러가 발생하면
              위에서부터 순서대로 catch문을 검색하면서 내려오기 때문에
              부모 타입의 예외를 자식 타입의 예외보다 위에 작성하시면 안됩니다.

             - catch 옆에 괄호에 예외 타입을 여러 개 작성하고 싶은 경우는
              | 기호를 사용하여 타입을 나열해 주시면, 하나의 catch 블록으로
              여러 타입의 예외를 동시에 처리할 수 있습니다.
              (java 7버전부터 가능한 문법)
             */

        } catch (InputMismatchException | ArithmeticException e) {
            System.out.println("잘못된 입력값입니다.");
        } catch (ArrayIndexOutOfBoundsException e) {
            System.out.println("인덱스 범위를 벗어났어요.");
        } catch (NullPointerException e) {
            System.out.println("주소가 null 입니다!");
        } catch (Exception e) {
            System.out.println("알 수 없는 예외 발생!");
        } finally {
            System.out.println("예외 발생 여부와 상관 없이 항상 실행되는 문장!");
        }
    }
}

 

• 만약 메서드 또는 생성자를 호출한 곳으로 예외를 떠넘기고 싶으면 throws를 사용한다.
• 그렇게 되면 호출자는 꼭 try/catch를 사용하여 예외 처리를 해주어야 한다.
• throws를 사용하는 이유는 메서드를 호출하는 입장에서 매개값이 잘못될 경우, 예외를 발생시킬 수 있다는 일종의 정보를 제공할 수 있음.
• 메서드마다 일일히 예외 처리를 하는 것 보단, 호출 행위가 많은 곳에서 한번에 예외 처리를 하는 것이 코드의 가독성 및 작성하기에 좋음.

public class ThrowsExample {

    static  String[] greeting = {"안녕", "hello", "니하오"};


            /*
                # throws

                - throws는 예외의 원인이 메서드 선언부가 아닌 호출부에 있을 경우
                 예외 처리를 메서드의 호출부로 떠넘기는 방식입니다.

                - throws는 생성자에서도 선언이 가능하며, 메서드나 생성자를 호출 시
                 예외 처리를 강요하고 싶을 때 사용합니다.

                - 또한, 원하는 영역으로 예외를 모아서 한번에 처리하는 것도 가능합니다.
                */


    static void greet(int idx) throws Exception {
        System.out.println(greeting[idx]);
    }

    public static void main(String[] args) {
        try {
            greet(3);
        } catch (Exception e) {
            System.out.println("메서드의 매개값이 잘못되었어요!");
        }
    }
}

• 물론 main에도 throws를 사용할 수 있다. 하지만 이 것은 예외 처리를 안하겠다는 것과 동일하기 때문에, 예외 케이스가 아닌 이상, 안붙이는게 좋다.

 

• 예외 처리 뿐만 아니라, 예외 객체를 일부러 생성하여 예외를 발생시킬 수 있다.

public static int calcTotal(int n) {
		int sum = 0;
		for(int i=1; i<=n; i++) {
				sum += i;
		}
		return sum;
}


public static void main(String[] args) {
		int result1 = calcTotal(100);
		int result2 = calcTotal(-120);
}

• 만약 음수를 준다면, 예외가 발생하지 않고 리턴값을 받게된다. 하지만 사용자는 0 또는 0보다 작은 값을 보낸다면 강제로 종료하고 싶다. 그러나 리턴값이 int이기 때문에 return 메서드로는 강제 종료를 할 수 없다. 이 것을 해결하기 위해 예외 처리를 만들어 줘야 한다.

public static int calcTotal(int n) throws Exception {
		if(n <= 0) {
				throw new Exception(); //이 시점에서 강제로 예외를 발생시킴!
		}		

		int sum = 0;
		for(int i=1; i<=n; i++) {
				sum += i;
		}
		return sum;
}

public static void main(String[] args) {
		try {
			int result1 = calcTotal(100);
			int result2 = calcTotal(-120);
		} catch (Exception e) {
			System.out.println("매개값은 반드시 양수로 주셔야 합니다!");
		} 
}

 

• 개발자가 만든 어플리케이션에서 자체적으로 생길 수 있는 예외는 개발자가 직접 예외 클래스를 정의해서 만들 수 있다.
• 사용자 정의 예의 클래스는 Exception 클래스를 상속하여 제작.
• 이름은 Exception으로 끝나게 지어주면 됨.

public class MyException extends Exception {
		public MyException() {}

		public MyException(String message) {
				super(message);
		}
	...
}

---

2. 자바 표준 API

import java.util.Objects;

// clone 메서드를 오버라이딩 했다면 Cloneable 인터페이스를 구현해야 합니다.
public class Person implements Cloneable{
    private String name;
    private int age;

    public Person(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public String getName() {
        return name;
    }

    public int getAge() {
        return age;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setAge(int age) {
        this.age = age;
    }

    // toString(): 객체의 정보(필드)를 문자열 형태로 리턴.
    // Object가 들려주는 toString은 패키지 + 클래스 이름 + 주소값 -> 쓸 일이 잘 없다.
    // 대부분 오버라이딩 해서 사용하는 편.
    @Override
    public String toString() {
        return "Person{" +
                "name='" + name + '\'' +
                ", age=" + age +
                '}';
    }


    // 같은 객체의 속성을 비교해서 동등 객체인지를 논리값으로 리턴하는 메서드
    @Override
    public boolean equals(Object obj) {
        if (this == obj) return true; // 주소값이 같다면 같은 객체애
        if (obj instanceof Person) {
            Person p = (Person) obj;
            if (p.age == this.age && this.name.equals(p.name)); return true;
        }
        return false; // Person 타입을 가질 수 없다면 동등 비교 의미가 없음.
    }


            //equals를 오버라이딩 할 때는 hashCode도 함께 오버라이딩을 진행해 주시는 것이 좋습니다.
            //equals를 재 정의한다는 것은 내가 원하는 방식의 동등 비교를 하겠다는 것이기 때문에
            //hashcode의 값도 그에 맞게 같이 재 정의가 되어야 합니다. (hash 주소를 equals에 맞게 정의)


    @Override
    public int hashCode() {
        return Objects.hash(name, age);
    }

    @Override
    protected void finalize() throws Throwable {
        // 생성된 객체가 소명될 때 자동으로 호출되는 메서드 (gc가 실행될 때)
        // garbage collector 호출되는 순서를 보장할 수 없기 떄문에 사용을 권장하지 않음.
        super.finalize();
    }

    @Override
    protected Object clone() throws CloneNotSupportedException {
        // 객체 복사 메서드
        return super.clone();
    }
}

public class Main {
    public static void main(String[] args) {
        Person kim = new Person("김춘식", 30);
        Person hong = new Person("김춘식", 30);


        // 객체타입의 변수를 출력하거나 값을 얻을 떄는
        // 뒤에 자동으로 .toString()이 붙어서 진행됩니다. (Object 물려주는 메서드)
        System.out.println(kim);
        System.out.println(hong);

        System.out.println(kim.equals(hong));



// equals에서 동등한 객체라고 판명이 되었다면, 서로 다른 객체더라도
        // 객체의 고유한 주소를 동일하게 처리해서 서로 같은 객체임을 명확히 해야 합니다.
        // 그래야, 자바에서 기본으로 제공하는 클래스 중에 hash를 비교해서 동등 객체인지 아닌지를
        // 판별하는 자료구조(HashSet, HashMap)들이 정상적으로 동작할 수 있습니다.

        System.out.println(kim.hashCode());
        System.out.println(hong.hashCode());

        Person park = new Person("박영희", 100);
        try {
            Person clonePerson = (Person) park.clone();
            System.out.println("복사된 객체 정보: " + clonePerson);
        } catch (CloneNotSupportedException e) {
            throw new RuntimeException(e);
        }
    }
}

• Object 클래스는 자바의 최상위 클래스. 즉, 자바의 모든 클래스는 Object 메서드를 상속받고 있다는 뜻.
• equals(Object obj) : 두 객체가 같은지 비교함. 
• hashcode() : 객체의 해시 주소값을 반환. 
• toString() : 객체를 문자열로 반환하여 반환한다. 기본은 단순 패키지 경로 및 클래스 이름, 16진수의 주소값이 나타남.
• clone() : 객체를 복제하여 반환.
• getClass() : 객체의 클래스를 반환.

package etc.api.lang.system;

public class SystemTime {
    public static void main(String[] args) {

        // 1970년 1월 1일 자정을 기준으로 현재까지 소요된 시간을 밀리초로 리턴 (long)
        long start = System.currentTimeMillis();
        int total = 0;
        for (int i = 0; i < 2000000000; i++) {
            total += i;
        }
        long end = System.currentTimeMillis();

        System.out.println("실행 소요시간: " + (end - start) * 0.001 + "초");
        System.out.println(System.getProperties());



    }
}

• exit() : 현재 실행하고 있는 프로세스를 강제 종료. 정상 종료일 경우 매개값으로 0, 비정상 종료는 0외에 다른 값을 준다.
• currentTimeMillis() / nanoTime() : 1970년 1월 1일 자정을 기준으로 현재까지 흐른 유닉스 시간을 밀리세컨드 또는 나노세컨드 단위의 long값으로 리턴. 주로 프로그램의 실행 소요 시간 측정으로 사용된다.

import java.util.Arrays;

public class StringMethod {
    public static void main(String[] args) {

        String str = "Hello Java";

        // charAt(index): 문자열 인덱스에 따른 단일 문자를 반환.
        char c = str.charAt(4);
        System.out.println("4번 인덱스 = " + c);

        // subString(begin, end): 문자열을 범위를 지정해서 부분 추출.
        String ss1 = str.substring(1, 5); // 1번 이상 5번 미만.
        System.out.println("ss1 = " + ss1);

        String ss2 = str.substring(6); // 6번부터 끝까지 추출.
        System.out.println("ss2 = " + ss2);

        // length(): 문자열의 총 길이 반환.
        int len = str.length();
        System.out.println("len = " + len);

        // indexOf(str): 해당 문자가 있는 인덱스를 반환
        // 해당 문자가 존재하지 않는다면 -1을 리턴.
        int idx1 = str.indexOf("l"); // 앞에서부터 탐색
        System.out.println("idx1 = " + idx1);

        int idx2 = str.lastIndexOf("l"); // 뒤에서부터 탐색
        System.out.println("idx2 = " + idx2);

        // 여러 문자를 전달하면 시작 인덱스를 알려줍니다.
        int idx3 = str.indexOf("Java");
        System.out.println("idx3 = " + idx3);

        String str2 = "HeLLo WoRLd";
        String lower = str2.toLowerCase(); // 일괄 소문자 변경
        System.out.println("lower = " + lower);
        String upper = str2.toUpperCase(); // 일괄 대문자 변경
        System.out.println("upper = " + upper);

        // trim(): 문자열의 앞, 뒤 공백을 제거.
        String name = "                홍길동                   ";
        System.out.println(name.trim() + "님 안녕하세요~!");

        // replace(old, new): 기존 문자열 내부의 old값을 모두 찾아서 new 문자열로 일괄 변경
        String java = "자바는 재밌습니다. 자바 커피는 맛있습니다.";
        System.out.println(java.replace("자바", "java"));
        System.out.println(java.replace("습니", ""));

        // split(구분자): 문자열을 구분자로 구분하여 분할.
        // 분할된 문자들은 String 배열에 담겨서 리턴.
        String phone = "010-1234-5678";
        String[] numbers = phone.split("-");

        System.out.println(Arrays.toString(numbers));

        String pet = "멍멍이, 야옹이, 짹짹이";
        String[] pets = pet.split(", ");
        System.out.println(Arrays.toString(pets));

        System.out.println("------------------------------------------------");

        // 문자열을 정수/실수 변환
        // 순수한 정수 or 실수가 아니라면 예외가 발생합니다.
        String s1 = "100";
        int s1num = Integer.parseInt(s1);
        String s2 = "3.14";
        double s2num = Double.parseDouble(s2);
        System.out.println(s1num + s2num);

        // 기본 타입을 문자열로 변경
        String s3 = String.valueOf(s1num);
        System.out.println("s3 = " + s3);


    }
}

public class StrBuliderExample {
    public static void main(String[] args) {
        String str = "Hello";
        System.out.println(str.hashCode());

        str = "Hello world!";
        System.out.println(str.hashCode());

        str = "hello~";
        System.out.println(str.hashCode());

        System.out.println("=============================");

        StringBuilder sb = new StringBuilder("hello");
        System.out.println(sb);
        System.out.println("sb의 주소값 = " + sb.hashCode());

        // 문자얄을 맨 끝에 추가하는 메서드 : append (문자열)
        sb.append(" world");
        System.out.println(sb);
        System.out.println("sb의 주소값 = " + sb.hashCode());

        // 문자열을 특정 인덱스에 삽입하는 메서드: insert(인덱스, 문자열)
        sb.insert(6, "my");
        System.out.println(sb);
        System.out.println("sb의 주소값 = " + sb.hashCode());

        // 특정 인덱스 범위의 문자열을 교체하는 메서드: replace(begin, end, 문자열)
        sb.replace(6, 8, "your");
        System.out.println(sb);
        System.out.println("sb의 주소값 = " + sb.hashCode());

        // 문자열 내의 특정 단어를 삭제하는 메서드 delete(begin, end)
        sb.delete(6, 11);
        System.out.println(sb);
        System.out.println("sb의 주소값 = " + sb.hashCode());

        // 문자열을 역순으로 배치
        sb.reverse();
        System.out.println(sb);
    }
}

• String 클래스는 아주 많이 사용하지만, 메모리를 과소비 한다는 단점이 존재.
• 이를 보완하는 StringBuilder가 등장하게 됨. 
• StringBuilder 객체는 메서드를 통해 문자열의 변화를 얼마든지 주더라도, 객체가 다시 생성되지 않고 기존 객체가 유지되기 때문에 속도 측면에서 String보다 훨씬 빠르다.

- Math 클래스

• abs() : 절대값을 구함.
• ceil() : 올림값을 구함.
• floor() : 내림값을 구함.
• max() : 수를 비교하여 최대값을 구함.
• min() : 수를 비교하여 최소값을 구함.
• random() : 랜덤값을 구함(0.0 <= 값 < 1.0)
• round() : 반올림값을 구함.

public class WrapperExample {

    public static void main(String[] args) {
        int a = 100;
        boolean b = false;
        char c = 'A';
        double d = 3.14;

        // boxing: 기본 데이터 타입을 객체 타입으로 변환
        Integer a2 = Integer.valueOf(a);
        Boolean b2 = Boolean.valueOf(b);
        Character c2 = Character.valueOf(c);
        Double d2 = Double.valueOf(d);

        // autoboxing: 기본 타입을 자동으로 객체형으로 변환
        Integer a1 = a;
        Boolean b1 = b;
        Character c1 = c;
        Double d1 = d;

        // unboxing: 객체 타입으로 포장된 값을 기본 데이터 타입으로 풀어내는 과정.
        int i = a1;
        boolean i1 = b1;
        char i2 = c1;
        double i3 = d1;


    }
}

• 자바의 자료형은 크게 기본형 / 참조형으로 나누어진다.
• 프로그래밍을 하다 보면, 기본형을 객체로 표현해야 하는 경우가 생기는데, 이 때 래퍼 클래스를 사용하면 된다.

import java.time.*;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.Date;

public class DateExample {
    public static void main(String[] args) {
        Date date = new Date();
        System.out.println(date);

        System.out.println("=========================");

        LocalDate now = LocalDate.now();
        System.out.println(now);

        LocalTime lt = LocalTime.now();
        System.out.println(lt);

        LocalDateTime ldt = LocalDateTime.now();
        System.out.println(ldt);

        LocalDateTime localDateTime = LocalDateTime.of(2022, 7, 1, 9, 0, 0);
        System.out.println("localDateTime = " + localDateTime);

        int year = localDateTime.getYear();
        System.out.println("year = " + year);

        // 문자 형태의 월 리턴 (Month라는 열거형 타입이 리턴)
        Month month = localDateTime.getMonth();
        System.out.println("month = " + month);

        int monthValue = localDateTime.getMonthValue();
        System.out.println("monthValue = " + monthValue);

        int day = localDateTime.getDayOfMonth();
        System.out.println("day = " + day);

        DayOfWeek week = localDateTime.getDayOfWeek();
        System.out.println("week = " + week);

        int dayOfYear = localDateTime.getDayOfYear();
        System.out.println("dayOfYear = " + dayOfYear);

        LocalDateTime d3 = localDateTime.plusDays(3);
        System.out.println("d3 = " + d3);

        LocalDateTime localDateTime1 = localDateTime.plusYears(1).plusMonths(2).plusDays(7);
        System.out.println("localDateTime1 = " + localDateTime1);

        LocalDateTime localDateTime2 = localDateTime.minusMonths(9).minusDays(23);
        System.out.println("localDateTime2 = " + localDateTime2);

        LocalDate start = LocalDate.of(2020, 12, 30);
        LocalDate end = LocalDate.of(2022, 9, 14);

        long between1 = ChronoUnit.DAYS.between(start, end);
        System.out.println("between1 = " + between1);

        long between2 = ChronoUnit.WEEKS.between(start, end);
        System.out.println("between2 = " + between2);

        System.out.println("===============================================");

        DateTimeFormatter pattern = DateTimeFormatter.ofPattern("yyyy년 MM월 dd일 E요일 a hh시 mm분 ss초");

        // 준비한 DateTimeFormatter 객체를 format()의 매개값으로 전달.
        String format = localDateTime.format(pattern);
        System.out.println("format = " + format);


    }
}

---

3.  SQL - 1(코드 안에 따로 정리)

• 데이터베이스 : 관련된 데이터들을 체계적으로 저장하고 관리할 수 있도록 구성된 데이터의 집합.
• DBMS : 데이터 베이스를 관리하기 위한 소프트웨어 시스템. 이 것은 사용자와 데이터베이스 간의 인터페이스 역할을 하며, 데이터의 저장 / 검색 / 수정 / 삭제 등을 수행.
• SQL : 관계형 데이터베이스 관리 시스템에서 데이터 조작, 정의, 제어하기 위해 사용되는 표준화된 프로그래밍 언어.

1. DDL : 데이터베이스 스키마를 생성, 변경, 삭제하기 위한 명령어
2. DML : 데이터를 검색, 삽입, 수정, 삭제하기 위한 명령어
3. DCL : 데이터베이스 사용자의 권한을 관리하기 위한 명령어
4. TCL : 데이터베이스 트랜잭션을 관리하기 위한 명령어

-- 오라클의 주석입니다.

/*
    여러 줄 주석입니다.
    !!
*/

-- SELECT [컬럼명(여러 개 가능)] FROM [테이블 이름]
SELECT * FROM employees;
SELECT employee_id, first_name, last_name FROM employees;

-- 컬럼을 조회하는 위치에서 산술 연산이 가능합니다.
SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    salary + salary * 0.1 AS 성과포함급여
FROM employees;

-- alias (컬럼명, 테이블명의 이름을 변경해서 조회합니다.)
SELECT 
    first_name AS 이름,
    last_name AS 성,
    salary AS 급여
FROM employees;

-- NULL값의 확인 (숫자 0이나 공백과는 다른 개념입니다.)
SELECT
    department_id, commission_pct
FROM employees;


/*
오라클은 홑따옴표로 문자를 표현하고, 문자열 안에 홑따옴표 특수기호를
표현하고 싶다면 ''를 두 번 연속으로 쓰시면 됩니다.
문자열 연결기호는 || 입니다. 문자열의 덧셈 연산을 허용하지 않습니다.
*/


SELECT
    first_name || '' || last_name || '''s salary is $' || salary AS full_name
FROM employees;

SELECT DISTINCT -- 중복행 제거
    department_id
FROM employees;

 

-- WHERE절 비교 (데이터 값은 대/소문자를 구분합니다.)
SELECT 
    first_name, last_name, job_id
FROM employees
WHERE job_id = 'IT_PROG';

SELECT
    *
FROM employees
WHERE last_name = 'king';

SELECT
    *
FROM employees
WHERE department_id = 50;

SELECT
    *
FROM employees
WHERE salary >= 15000 
AND salary < 20000;


SELECT
    *
FROM employees
WHERE hire_date = '04/01/30';

-- 데이터의 행 제한 (BETWEEN, IN, LIKE)
SELECT
    *
FROM employees
WHERE salary BETWEEN 15000 AND 20000;

SELECT
    *
FROM employees
WHERE hire_date BETWEEN '03/01/01' AND '03/12/31';

-- IN 연산자 (특정 값들과 비교할 때 사용)
-- %는 어떠한 문자든, _는 데이터의 자리 (위치)를 표현할 떄.

SELECT
    *
FROM employees
WHERE manager_id IN (101, 102, 103);

SELECT
    *
FROM employees
WHERE job_id IN ('IT_PROG', 'AD_VP');

-- LIKE 연산자 (지정 문자열 포함 여부)
SELECT
    first_name,
    last_name,
    hire_date
FROM employees;
WHERE hire_date LIKE '03%';  -- 03으로 시작되는 것 조회 

SELECT
    first_name,
    last_name,
    hire_date
FROM employees;
WHERE hire_date LIKE '%15'; -- 15로 끝나는 것 조회 

SELECT
    first_name,
    last_name,
    hire_date
FROM employees;
WHERE hire_date LIKE '%05%'; -- 05가 존재하는 모든 값을 조회 


SELECT
    first_name,
    last_name,
    hire_date
FROM employees;
WHERE hire_date LIKE '___05%'; -- 앞에 3글자가 있으면서 05로 시작되는 것 조회


-- IS NULL : null 값을 찾음
SELECT
    *
FROM employees;
WHERE commission_pct IS NULL;

SELECT
    *
FROM employees;
WHERE commission_pct IS NOT NULL;

-- AND, OR
-- AND가 OR보다 연산 속도가 빠름.
SELECT
    *
FROM employees
WHERE job_id = 'IT_PROG'
OR job_id = 'FI_MGR'
AND salary >= 6000;

SELECT
    *
FROM employees
WHERE (job_id = 'IT_PROG'
OR job_id = 'FI_MGR')
AND salary >= 6000;

-- 데이터의 정렬 (SELECT 구문의 가장 마지막에 배치됩니다.)
-- ASC : 오름차 (ascending)
-- DESC : 내림차 (descending)
SELECT
    *
FROM employees
ORDER BY hire_date;

SELECT
    *
FROM employees
WHERE job_id = 'IT_PROG'
ORDER BY hire_date DESC;

SELECT
    first_name,
    salary * 12 AS pay
FROM employees
ORDER BY pay DESC;

-- lower(소문자), initcap(앞글자만 대문자), upper(대문자)

/*
dual이라는 테이블은 sys가 소유하는 오라클의 표준 테이블로서,
오직 한 행에 한 컬럼만 담고 있는 dummy 테이블 입니다.
일시적인 산술 연산이나 날짜 연산 등에 주로 사용합니다.
모든 사용자가 접근할 수 있습니다.
*/

SELECT
    'abcdef',
    LOWER('abcDEF'),
    INITCAP('abcDEF'),
    UPPER('abcDEF')
FROM dual;

SELECT
    last_name,
    LOWER(last_name),
    INITCAP(last_name),
    UPPER(last_name)
FROM employees;

SELECT
    salary
FROM employees
WHERE LOWER(last_name) = 'austin';

-- length(길이), instr(문자 찾기, 없으면 0을 반환, 있으면 인덱스 값) 
SELECT
    'abcdef', LENGTH('abcdef'), INSTR('abcdef', 'b')
FROM dual;

SELECT
    first_name,
    LENGTH(first_name),
    INSTR(first_name, 'a')
FROM employees;

-- SUBSTR(자를 문자열, 시작 인덱스, 길이)
-- CONCAT(문자 연결)

SELECT
    first_name,
    SUBSTR(first_name, 1, 3),
    CONCAT(first_name, last_name)
FROM employees;


-- LTRIM(), RTRIM()
-- TRIM() -> 양쪽 공백 제거
-- LTRIM(param1, param2) -> param2의 값을 param1에서 찾아서 제거 (왼쪽부터)
-- RTRIM(param1, param2) -> param2의 값을 param1에서 찾아서 제거 (오른쪽부터)
SELECT
    LTRIM('javascript_java', 'java'),
    RTRIM('javascript_java', 'java')
FROM dual;

-- LPAD(), RPAD(): 좌, 우측을 지정한 문자열로 채우기
SELECT
    LPAD('abc', 10, '*'),
    RPAD('abc', 10, '#')
FROM dual;

-- REPLACE(str, old, new) 
SELECT
    REPLACE('my dream is a president', 'president', 'programmer')
FROM dual;

SELECT
    REPLACE(REPLACE('my dream is a president', 'president', 'programmer'), ' ', '')
FROM dual;

-- 숫자함수
-- ROUND(반올림)
-- 원하는 반올림 위치를 매개값으로 지정. 음수를 주는 것도 가능.
SELECT
    ROUND(3.1415, 3), ROUND(45.923, 0), ROUND(45.923, -1)
FROM dual;

-- TRUNC(절사)
-- 정해진 소수점 자리수까지 잘라냅니다.
SELECT
    TRUNC(3.1415, 3), TRUNC(45.923, 0), TRUNC(45.923, -1)
FROM dual;

-- ABS (절대값)
SELECT ABS(-34) FROM dual;

-- CEIL(올림), FLOOR(내림)
SELECT CEIL(3.14), FLOOR(3.14)
FROM dual;

-- MOD(나머지 나눗셈 연산)
SELECT
    10 / 4, MOD(10, 4)
FROM dual;


-------------------------------------------------------------

-- 날짜 함수
-- sysdate: 컴퓨터의 현재 날짜, 시간 정보를 가져와서 제공하는 키워드 함수 -> 시간 있음!
SELECT sysdate FROM dual;
SELECT systimestamp FROM dual; -- 더 세밀한 시간과 표준시 정보까지 제공.

-- 날짜 연산
SELECT sysdate + 1 FROM dual; -- 일자 덧셈 연산이 가능.


-- 날짜 타입과 날짜 타입은 서로 뺄셈 연산이 가능합니다.
-- 덧셈은 허용하지 않습니다.
SELECT
    first_name,
    sysdate - hire_date
FROM employees; -- 일자

SELECT
    first_name,
    (sysdate - hire_date) / 365
FROM employees; -- 년수


-- 날짜 반올림, 절사
SELECT ROUND(sysdate) FROM dual;
SELECT ROUND(sysdate, 'year') FROM dual; -- 년 기준 반올림
SELECT ROUND(sysdate, 'month') FROM dual; -- 월 기준 반올림
SELECT ROUND(sysdate, 'day') FROM dual; -- 주 기준 반올림 (해당 주의 일요일 날짜)

SELECT TRUNC(sysdate) FROM dual;
SELECT TRUNC(sysdate, 'year') FROM dual; -- 년 기준 반올림
SELECT TRUNC(sysdate, 'month') FROM dual; -- 월 기준 반올림
SELECT TRUNC(sysdate, 'day') FROM dual; -- 주 기준 반올림 (해당 주의 일요일 날짜)

-- 형변환 함수 TO_CHAR, TO_NUMBER, TO_DATE

-- 날짜를 문자로 TO_CHAR(값, 형식)
-- 날짜를 문자로 형 변환할 때 원하는 형식으로 날짜를 표현할 수 있습니다.
-- 날짜 포맷형식: Y - 연도, MM - 두자리 월, D - 일수 DY - 요일
-- 시간 포맷형식: HH12 - 시(1~12), HH24 (0-23), MI - 분, SS - 초

SELECT TO_CHAR(sysdate) FROM dual;
SELECT TO_CHAR(sysdate, 'YYYY-MM-DD DY PM HH:MI:SS') FROM dual;


-- 서식 문자와 함께 사용하고 싶은 일반 문자는 ""로 감싸서 전달.
SELECT 
    TO_CHAR(sysdate, 'YY"년" MM"월" DD"일"') 
FROM dual;


-- 숫자를 문자로 TO_CHAR(값, 형식)
-- 형식에서 사용하는 '9'는 실제 숫자 9가 아니라 자리수를 표현하기 위한 기호입니다.
SELECT TO_CHAR(20000, '99,999') FROM dual;
SELECT TO_CHAR(20000, 'L99,999') FROM dual;
SELECT TO_CHAR(20000.29, '99,999.9') FROM dual; -- 반올림 지원

SELECT
    first_name,
    TO_CHAR(salary, '$99,999') AS salary
FROM employees;

-- 문자를 숫자로 변환 TO_NUMBER(값, 형식)
SELECT '2000' + 2000 FROM dual; -- 자동 변환
SELECT '$3,300' + 2000 FROM dual; -- 에러
-- 변환하려는 문자가 깔끔한 숫자 형태가 아니라면, 서식문자를 이용해서 알려주세요.
SELECT TO_NUMBER('$3,300', '$9,999') + 2000 FROM dual;


-- 문자를 날짜로 TO_DATE(값, 형식)
SELECT TO_DATE('2023-04-13') + 1 FROM dual;

-- 주어진 문자열을 모두 변환해야 합니다. 일부만 변환할 수 없습니다.
SELECT TO_DATE('2023-04-13 12:23:50', 'YY-MM-DD HH:MI:SS') FROM dual;

SELECT TO_DATE('2023년 04월 13일', 'YYYY"년" MM"월" DD"일"') FROM dual;


--------------------------------------------------------------------------------

-- NULL 형태를 변환하는 함수 NVL(컬럼 혹은 값, 변환할 타겟값)
SELECT
    first_name,
    NVL(commission_pct, 0) AS comm_pct
FROM employees;

-- NULL 형태를 변환하는 함수 NVL2(타겟, null이 아닌 경우, null 경우)
-- 연산 중간에 NULL이 있으면 전체 결과가 NULL이 됩니다!
SELECT
    first_name,
    salary,
    NVL2(
        commission_pct,
        salary + (salary * commission_pct),
        salary
    ) AS 급여
FROM employees;

-- COALESCE(expr1, ...)
-- 전달된 표현식 중 NULL이 아닌 값이 최초로 발견되면 그 값을 리턴

SELECT
    COALESCE(3000, NULL, NULL, 4000)
FROM dual;

-- comm_pct가 null인 애들은 0을 적용, null이 아닌 애들은 자신의 comm_pct를 적용
SELECT
    first_name,
    salary,
    salary + (salary * COALESCE(commission_pct, 0)) AS 급여
FROM employees;


-- DECODE(컬럼 혹은 표현식, 항목1, 결과1, 항목2, 결과2 ... default)
-- 오라클 전용 함수. 동등 비교만 가능.
SELECT
    first_name,
    job_id,
    salary,
    DECODE(
        job_id,
        'IT_PROG', salary * 1.1,
        'FI_MGR', salary * 1.2,
        'AD_VP', salary * 1.3,
        0
    ) AS result
FROM employees;

-- CASE문 WHEN THEN END
-- ANSI 표준 구문
SELECT
    first_name,
    job_id,
    salary,
    (CASE job_id
        WHEN 'IT_PROG' THEN salary*1.1
        WHEN 'FI_MGR' THEN salary*1.2
        WHEN 'AD_VP' THEN salary*1.3
        ELSE 0
    END) AS result
FROM employees;

-- 집합 연산자
-- 서로 다른 쿼리 결과의 행들을 하나로 결합, 비교, 차이를 구할 수 있게 해 주는 연산자
-- UNION(합집합 중복x), UNION ALL(합집합 중복 o), INTERSECT(교집합), MINUS(차집합)
-- 위 아래 column 개수와 데이터 타입이 정확히 일치해야 합니다.

-- UNION -> 중복 데이터를 허용하지 않음. 자동으로 정렬이 일어남 (첫 번째 컬럼 오름차가 default) 
SELECT 
    employee_id, first_name
FROM employees
WHERE hire_date LIKE '04%'
UNION
SELECT 
    employee_id, first_name
FROM employees
WHERE department_id = 20;

-- UNION ALL -> 중복을 허용 
SELECT 
    employee_id, first_name
FROM employees
WHERE hire_date LIKE '04%'
UNION ALL
SELECT 
    employee_id, first_name
FROM employees
WHERE department_id = 20;




SELECT 
    employee_id, first_name
FROM employees
WHERE hire_date LIKE '04%'
INTERSECT
SELECT 
    employee_id, first_name
FROM employees
WHERE department_id = 20;




SELECT 
    employee_id, first_name
FROM employees
WHERE hire_date LIKE '04%'
MINUS
SELECT 
    employee_id, first_name
FROM employees
WHERE department_id = 20;



SELECT 
    employee_id, first_name
FROM employees
WHERE department_id = 20
MINUS
SELECT 
    employee_id, first_name
FROM employees
WHERE hire_date LIKE '04%';

-- 집계함수 AVG, MAX, MIN, SUM, COUNT
-- 여러 행을 묶어서 한 번에 함수를 적용
-- 그룹화를 따로 하지 않으면 그룹은 테이블 전체가 됩니다.
SELECT
    AVG(salary),
    MAX(salary),
    MIN(salary),
    SUM(salary),
    COUNT(salary)
FROM employees;

SELECT COUNT(*) FROM employees; -- 총 행의 데이터 수
SELECT COUNT(first_name) FROM employees;
SELECT COUNT(commission_pct) FROM employees; -- null이 아닌 행의 수  
SELECT COUNT(manager_id) FROM employees; -- null은 카운팅되지 않습니다.


-- 부서별로 그룹화, 집계함수 사용  
SELECT
    department_id,
    AVG(salary),
    COUNT(*)
FROM employees
GROUP BY department_id;

-- 그룹 함수는 단독적으로 사용할 때는 테이블 전체가 그룹의 대상이 되지만 
-- 일반 컬럼과 동시에 출력할 수 없습니다. 일반 컬럼을 그룹화 해야 합니다. 
SELECT
    department_id,
    AVG(salary),
    COUNT(*)
FROM employees; -- 에러  

-- GROUP BY절을 사용할 때, GROUP절에 묶이지 않은 컬럼은 조회할 수 없습니다.
SELECT
    job_id,
    department_id,
    AVG(salary),
    COUNT(*)
FROM employees
GROUP BY department_id;

-- GROUP BY절 2개 이상 사용
SELECT
    job_id,
    department_id,
    AVG(salary),
    COUNT(*)
FROM employees
GROUP BY department_id, job_id;

-- GROUP BY를 통해 그룹화 할 때, 조건을 지정할 경우 HAVING을 사용.
-- WHERE은 일반 조건절. GROUP BY보다 먼저 진행됩니다.
SELECT
    department_id,
    SUM(salary),
    AVG(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;

-- 부서 아이디가 50 이상인 인원만 그룹화 시키고, 그룹 급여 평균이
-- 5000 이상만 조회 

SELECT
    department_id,
    AVG(salary) AS 평균급여
FROM employees
WHERE department_id >= 50
GROUP BY department_id
HAVING AVG(salary) >= 5000
ORDER BY 평균급여 DESC;

/*
# 조인이란?

- 서로 다른 테이블 간에 설정된 관계가 결합하여
 1개 이상의 테이블에서 데이터를 조회하기 위해서 사용합니다.
 
- SELECT 컬럼리스트 FROM 조인대상이 되는 테이블 (1개 이상)
  WHERE 조인 조건 (오라클 조인 문법)
*/



-- employees 테이블의 부서 id와 일치하는 departments 테이블의 부서 id를 찾아
-- 부서 이름을 조회하자.
SELECT
    e.first_name,
    e.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

/*
각각의 테이블에 독립적으로 존재하는 컬럼의 경우에는
테이블 이름을 생략해도 무방합니다. 그러나, 해석의 명확성을 위해
테이블 이름을 작성하셔서 소속을 표현해 주는 것이 바람직합니다.
테이블 이름이 너무 길 시에는 ALIAS를 작성하여 칭합니다.
두 테이블 모두 가지고 있는 컬럼의 경우 반드시 명시해 주셔야 합니다.
*/

-- 3개의 테이블을 이용한 내부 조인 (INNER JOIN)
-- 내부 조인: 조인 조건에 일치하는 행만 반환하는 조인
-- 조인 조건에 일치하지 않는 데이터는 조회 대상에서 제외
SELECT
    e.first_name,
    e.department_id,
    d.department_name,
    j.job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id;


-- 4개의 테이블 조인
SELECT
    e.first_name,
    e.department_id,
    d.department_name,
    j.job_title,
    loc.city
FROM employees e, departments d, jobs j, locations loc
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND d.location_id = loc.location_id
AND loc.state_province = 'California'; -- 일반 조건은 조인 조건 이후에 작성.


-- 외부 조인 (OUTER JOIN)
/*
상호 테이블간에 일치되는 값으로 연결되는 내부 조인과는 다르게
어느 한 테이블에 공통 값이 없더라도 해당 row들이 조회 결과에
모두 포함되는 조인을 말합니다.
*/
SELECT
    e.first_name,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);


-- 외부 조인 진행 시 모든 조건에 (+)를 붙여야 하며
-- 일반 조건에도 (+)를 붙이지 않으면 데이터가 누락되는 현상이 발생.
SELECT
    e.employee_id, e.first_name, e.department_id,
    jh.start_date, jh.end_date, jh.job_id
FROM employees e, job_history jh
WHERE e.employee_id = jh.employee_id(+)
AND jh.department_id(+) = 80;

-- 이너(내부) 조인
SELECT
    e.first_name,
    e.department_id,
    d.department_name
FROM employees e 
JOIN departments d -- JOIN이라고만 써도 INNER JOIN
ON e.department_id = d.department_id
WHERE e.department_id = 50;

-- 아우터(외부) 조인
SELECT
    e.first_name,
    d.department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id;

-- 풀 아우터 조인(좌측 테이블과 우측 테이블 데이터를 모두 읽어 표현하는 외부 조인) 
-- LEFT + RIGHT JOIN
SELECT
    e.first_name,
    d.department_name
FROM employees e 
FULL JOIN departments d
ON e.department_id = d.department_id;

-- CROSS JOIN은 JOIN 조건을 설정하지 않기 때문에
-- 단순히 모든 컬럼에 대한 JOIN을 수행합니다.
-- 실제로는 거의 사용하지 않습니다.
-- (실행 결과 행의 수: 각 테이블의 행을 곱해주면 됨.) 
SELECT
    *
FROM employees
CROSS JOIN departments;

SELECT * FROM employees, departments;

-- 여러 개 테이블 조인 -> 조인 조건에 사용할 키 값을 찾아
-- 연결해서 쓰면 됩니다.

SELECT
    *
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations loc
ON d.location_id = loc.location_id;

-- SELF JOIN이란 동일 테이블 사이의 조인을 말합니다.
-- 동일 테이블 컬럼을 통해 기존의 존재하는 값을 매칭시켜 가져올 때 사용합니다.

-- 각 사원별 매니저의 이름을 조회
SELECT
    e1.employee_id, e1.first_name, e1.manager_id,
    e2.first_name, e2.employee_id
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;

-- 각 매니저가 당담하는 사원의 명수
-- 매니저 아이디, 매니저 이름, 사원수
SELECT 
    m.employee_id AS manager_id,
    m.first_name AS manager_name,
    COUNT(m.employee_id) AS 사원수 
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.first_name;


-- Natural Join
-- 동일한 이름을 갖는 컬럼들에 대해 자동으로 조인조건을 생성하는 기법입니다.
-- 즉, 자동으로 2개 이상의 테이블에서 같은 이름을 가진 컬럼을 찾아 INNER JOIN을 수행합니다.
-- 조인되는 동일 이름의 컬럼은 타입이 같아야 하며,
-- ALIAS나 테이블명을 자동 조인 컬럼 앞에 표기하면 안됩니다.
-- 조회하려는 컬럼에 *을 붙이면, 공통 컬럼은 한 번만 표기됩니다.

SELECT
    e.employee_id, e.first_name,
    department_id, d.department_name    
FROM employees e
NATURAL JOIN departments d;


-- USING JOIN
-- NATURAL JOIN과는 다르게, USING을 사용하면 원하는 컬럼에 대해서만 선택적 조인조건을
-- 부여할 수 있습니다.
-- USING절에서도 조인 컬럼에 대해 ALIAS나 테이블명을 표기하시면 안됩니다.

SELECT
    e.employee_id, e.first_name,
    department_id, d.department_name    
FROM employees e
JOIN departments d USING(department_id);

/*
# 서브쿼리 
: SQL 문장 안에 또다른 SQL을 포함하는 방식.
 여러 개의 질의를 동시에 처리할 수 있습니다.
 WHERE, SELECT, FROM 절에 작성이 가능합니다.

- 서브쿼리의 사용방법은 () 안에 명시함.
 서브쿼리절의 리턴행이 1줄 이하여야 합니다.
- 서브쿼리 절에는 비교할 대상이 하나 반드시 들어가야 합니다.
- 해석할 때는 서브쿼리절 부터 먼저 해석하면 됩니다.
*/

-- 이름이 'Nancy'인 사원보다 급여가 높은 사원을 조회하기

SELECT salary FROM employees
WHERE first_name = 'Nancy';

SELECT first_name FROM employees
WHERE salary > 12008;

-- 서브쿼리를 활용한 문장   
SELECT first_name FROM employees
WHERE salary > (SELECT salary FROM employees
                WHERE first_name = 'Nancy');
                
-- employee_id가 103번인 사람의 job_id랑 동일한 job_id를 가진 사람을 조회.  
SELECT * FROM employees
WHERE job_id = (SELECT job_id FROM employees
                WHERE employee_id = 103);
                
                


-- 다음 문장은 서브쿼리가 리턴하는 행이 여러 개라서 단일 행 연산자를 사용할 수 없습니다.
-- 단일 행 연산자: 주로 비교 연산자 (=, >, <, >=, <=, <>)를 사용하는 경우 하나의 행만 반환해야 합니다.
-- 이런 경우에는 다중 행 연산자를 사용해야 합니다.
SELECT * FROM employees
WHERE job_id = (SELECT job_id FROM employees
                WHERE last_name = 'King'); -- 에러  
                
                
-- 다중 행 연산자 (IN, ANY, ALL, EXIST)

-- IN: 조회된 목록의 어떤 값과 같은지를 비교.
-- ex) salary IN (200, 300, 400)
-- 250 -> false
SELECT * FROM employees
WHERE job_id IN (SELECT job_id FROM employees
                WHERE last_name = 'King');
                
-- first_name이 David인 사람들의 급여와 같은 급여를 받는 사람들을 조회.
SELECT 
    *
FROM employees
WHERE salary IN (SELECT salary FROM employees
                WHERE first_name = 'David');
                

-- ANY, SOME : 값을 서브쿼리에 의해 리턴된 각각의 값과 비교해서
-- 하나라도 만족하면 조회 대상에 포함됩니다.
-- ex) salary > ANY (200, 300, 400)
-- 250 -> true

SELECT 
    *
FROM employees
WHERE salary > ANY (SELECT salary FROM employees
                    WHERE first_name = 'David');




-- ALL: 값을 서브쿼리에 의해 리턴된 각각의 값과 비교해서
-- 전부 다 일치해야 조회대상에 포함됩니다.
-- ex) salary > ALL (200, 300, 400)
-- 250 -> false, 200 보다는 크지만 300, 400보다는 크지 않기 때문에 
SELECT 
    *
FROM employees
WHERE salary > ALL (SELECT salary FROM employees
                    WHERE first_name = 'David');
                    

-- job_history에 존재하는 직원이 employees에도 존재한다면 조회 대상에 포함.
-- 서브쿼리 내에서 jh에 있는 id와 e에 있는 id가 일치할 때마다 1이라는 값을 조회.
-- EXISTS 연산자가 1이 조회가 될때 데이터가 존재한다는 것을 판단하여
-- employees에서 해당 사원의 모든 컬럼을 조회.
                    
-- EXISTS : 서브쿼리가 하나 이상의 행을 반환하면 참으로 간주.  
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM job_history jh
              WHERE e.employee_id = jh.employee_id);
              
              
              
-------------------------------------------------------------------------


-- SELECT 절에 서브쿼리 붙이기.
-- 스칼라 서브쿼리 라고도 칭합니다.
-- 스칼라 서브쿼리 : 실행 결과가 단일 값을 반환하는 서브쿼리.

SELECT
    e.first_name,
    d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
ORDER BY e.first_name;


SELECT
    e.first_name,
    (
        SELECT
            d.department_name
        FROM departments d
        WHERE d.department_id = e.department_id
    ) AS department_name
FROM employees e
ORDER BY e.first_name;

/*
# 스칼라 서브쿼리 vs LEFT JOIN
a. 간단한 상황에서 사용하면 쿼리가 직관적이고 간결합니다.
b. 단일 값을 반환하는 서브쿼리에서 유용.
c. 대량 데이터가 아닌 경우나, 서브쿼리의 복잡도가 낮은 경우 적합.

1. 대량 데이터를 처리하거나, 여러 컬럼을 붙여야 할 때 유리합니다.
2. 여러 테이블을 한 번에 조인해야 할 경우.
3. 다중값 처리 및 중복 데이터가 있는 경우 성능상 좀 더 유리.
*/

-- 각 부서 별 사원수 뽑기 (부서명, 사원수)

SELECT
    d.department_name,
    COUNT(e.employee_id) AS 사원
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY 사원 DESC;

SELECT
    d.department_name,
    NVL ((
        SELECT
            COUNT(*)
        FROM employees e
        WHERE e.department_id = d.department_id
        GROUP BY department_id
    ), 0) AS 사원
FROM departments d
ORDER BY 사원 DESC;

------------------------------------------------------------------------


-- FROM절 서브쿼리 (인라인 뷰)
-- 특정 테이블 전체가 아닌 SELECT를 통해 일부 데이터를 조회한 것을 가상 테이블로 사용하고 싶을 때.
-- 순번을 정해놓은 조회 자료를 범위를 지정해서 가지고 오는 경우.


/*
가장 안쪽 SELECT 절에서 필요한 테이블 형식(인라인 뷰)을 생성.
바깥쪽 SELECT 절에서 ROWNUM을 붙여서 다시 조회
가장 바깥쪽 SELECT 절에서는 이미 붙어있는 ROWNUM의 범위를 지정해서 조회.

** SQL의 실행 순서
FROM(JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
*/

SELECT
    *
FROM
    (SELECT
        ROWNUM AS rn, tbl.*
    FROM
        (SELECT 
            employee_id,first_name, salary
        FROM employees
        ORDER BY salary DESC) 
    tbl)
WHERE rn > 30 AND rn <= 40;


SELECT
    first_name, salary,
    tbl.avg_salary
FROM employees e
JOIN (SELECT
    department_id,
    TRUNC(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department_id
) tbl
ON e.department_id = tbl.department_id;

-- 부서별 최고 급여를 받는 직원을 조회해주세요.
-- 사원 이름, 급여, 부서 이름 

SELECT
    e.first_name,
    e.salary,
    d.department_name
FROM employees e
JOIN (
SELECT
    department_id,
    MAX (salary) AS max_salary
FROM employees
GROUP BY department_id
) max_sal
ON e.department_id = max_sal.department_id
AND e.salary = max_sal.max_salary
JOIN departments d
ON e.department_id = d.department_id;


SELECT
    e.first_name,
    e.salary,
    d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary = (
    SELECT
        MAX(salary)
    FROM employees
    WHERE department_id = e.department_id
)
ORDER BY e.salary;

-- INSERT
-- 테이블 구조 확인
DESC departments;

-- INSERT의 첫번째 방법 (모든 컬럼 데이터를 한 번에 지정해서 삽입)

-- 에러 -> 컬럼을 지정하지 않고 값만 주는 경우에는 모든 값을 다줘야 한다.
INSERT INTO departments
VALUES(301, '영업부');

SELECT * FROM departments;

-- INSERT의 두번째 방법 (직접 컬럼을 지정해서 저장, NOT NULL 컬럼 확인!)
INSERT INTO departments
    (department_id, manager_id)
VALUES
    (302, 101); -- 에러
    
ROLLBACK; -- 실행 시점을 다시 뒤로 되돌리는 키워드.

-- 사본 테이블 생성
-- 사본 테이블을 생성할 때 그냥 생성하면 -> 조회된 데이터까지 모두 복사
-- WHERE절에 false(1=2)를 주면 -> 테이블의 구조만 복사되고 데이터는 복사 x
-- 제약조건은 복사되지 않습니다.
CREATE TABLE emps2 AS
(SELECT employee_id, first_name, job_id, hire_date
FROM employees WHERE 1 = 2);

SELECT * FROM emps;
DROP TABLE emps2;

-- INSERT (서브쿼리)
INSERT INTO emps
(SELECT employee_id, first_name, job_id, hire_date
FROM employees WHERE department_id = 50);

-------------------------------------------------------------

-- UPDATE
-- UPDATE 테이블이름 SET 컬럼=값, 컬럼=값 ..... WHERE 누구를 수정할지(조건)
UPDATE emps
SET first_name = '춘식이'; -- 조건 지정 안하면 대상이 테이블 전체가 됨!
ROLLBACK;

UPDATE emps
SET first_name = '춘식이'
WHERE employee_id = 100;


UPDATE emps
SET 
    first_name = '야옹이', 
    job_id = '백수', 
    hire_date = sysdate
WHERE employee_id = 100;

SELECT * FROM emps;

-- UPDATE 서브쿼리
UPDATE emps
SET (job_id, hire_date) =
    (
    SELECT
        job_id, hire_date
    FROM emps
    WHERE employee_id = 100
    )
WHERE employee_id = 101;

-----------------------------------------------------------

-- DELETE
-- DELETE도 WHERE 지정하지 않으면 테이블 전체 행이 대상이 됩니다.
DELETE FROM emps
WHERE employee_id = 101;


-- DELETE (서브쿼리)
DELETE FROM emps
WHERE department_id = (SELECT department_id FROM departments
                        WHERE department_name = 'IT');

-- 오토 커밋 활성화 여부 확인
SHOW AUTOCOMMIT;
-- 오토 커밋 켜기   
SET AUTOCOMMIT ON;
-- 끄기  
SET AUTOCOMMIT OFF;

SELECT * FROM emps;

INSERT INTO emps
VALUES(300, '춘식', 'IT_PROG', sysdate);

DELETE FROM emps WHERE employee_id = 300;

INSERT INTO emps
VALUES(301, '메롱이', '영업부', sysdate);

-- 보류중인 모든 데이터 변경사항을 취소 (폐기)
-- 직전 커밋 단계로 회귀(돌아가기) 및 트랜젝션 종료.  
ROLLBACK;

-- 세이브 포인트 생성
-- 롤백할 포인트를 직접 이름을 붙여서 지정.
-- ANSI 표준 문법은 아니기 때문에 그렇게 권장하지는 않음.  
SAVEPOINT insert_choon;

ROLLBACK TO SAVEPOINT insert_choon;

-- 보류중인 모든 데이터 변경 사항을 영구적으로 적용하면서 트랜젝션 종료.
-- 커밋 후에는 어떠한 방법을 사용하더라도 되돌릴 수 없습니다.    
COMMIT;

/*
- NUMBER(2) -> 정수를 2자리까지 저장할 수 있는 숫자형 타입.
- NUMBER(5, 2) -> 정수부, 실수부를 합친 총 자리수 5자리, 소수점 2자리
- NUMBER -> 괄호를 생략할 시 (38, 0)으로 자동 지정됩니다.
- VARCHAR2(byte) -> 괄호 안에 들어올 문자열의 최대 길이를 지정. (4000byte까지)
- CLOB -> 대용량 텍스트 데이터 타입 (최대 4Gbyte)
- BLOB -> 이진형 대용량 객체 (이미지, 파일 저장 시 사용)
- DATE -> BC 4712년 1월 1일 ~ AD 9999년 12월 31일까지 지정 가능
- 시, 분, 초 지원 가능.
*/



CREATE TABLE dept (
    dept_no NUMBER(2),
    dept_name VARCHAR2(14),
    loca VARCHAR2(15),
    dept_date DATE,
    dept_bouns NUMBER(10)
);

DESC dept;
SELECT * FROM dept;

-- NUMBER와 VARCHAR2의 타입, 길이를 확인.  
INSERT INTO dept 
VALUES (20, '마케팅', '경기', sysdate, 3000000.86);

-- 컬럼 추가
ALTER TABLE dept 
ADD dept_count NUMBER(3);

-- 컬럼명 수정
ALTER TABLE dept
RENAME COLUMN dept_count TO emp_count;

-- 컬럼 속성 변경
-- 만약 변경하고자 하는 컬럼에 데이터가 이미 존재한다면, 그에 맞는 타입으로 
-- 변경해야 합니다. 맞지 않는 타입으로는 변경이 불가합니다. 
ALTER TABLE dept
MODIFY dept_no NUMBER(4);

-- DDL(CREATE, ALTER, TRUNCATE, DROP)은 트랜젝션 대상이 아닙니다.
-- DML(INSERT, UPDATE, DELETE) 이후 DDL을 실행하면 COMMIT이 자동으로 들어감.  
ROLLBACK;

-- 컬럼 삭제
ALTER TABLE dept
DROP COLUMN dept_bouns;

-- 테이블 이름 변경
ALTER TABLE dept
RENAME TO dept2;

SELECT * FROM dept2;

-- 테이블 삭제 (구조는 남겨두고 내부 테이터만 모두 삭제)
TRUNCATE TABLE dept2;

-- 테이블 삭제  
DROP TABLE dept2;

-- 테이블 생성과 제약조건
-- : 테이블에 부적절한 데이터가 입력되는 것을 방지하기 위해 규칙을 설정하는 것.

-- 테이블 열레벨 제약조건 (PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY, CHECK)
-- PRIMARY KEY: 테이블의 고유 식별 컬럼입니다. (주요 키)
-- UNIQUE: 유일한 값을 갖게 하는 컬럼 (중복값 방지)
-- NOT NULL: null을 허용하지 않음. (필수값)
-- FOREIGN KEY: 참조하는 테이블의 PRIMARY KEY를 저장하는 컬럼
-- CHECK: 정의된 형식만 저장되도록 허용.


-- 컬럼 레벨 제약 조건 (컬럼 선언마다 제약조건 지정)
-- 제약 조건 식별자는 생략이 가능합니다. (오라클에서 알아서 이름 지움)
CREATE TABLE dept (
    dept_no NUMBER(2) CONSTRAINT dept_deptno_pk PRIMARY KEY,
    dept_name VARCHAR2(14) NOT NULL CONSTRAINT dept_deptname_uk UNIQUE,
    loca NUMBER(4, 0) CONSTRAINT dept_loca_locid_fk REFERENCES locations(location_id),
    dept_bonus NUMBER(10) CONSTRAINT dept_bonus_ck CHECK(dept_bonus > 100000),
    man_gender VARCHAR2(1) CONSTRAINT dept_gneder_ck CHECK(man_gender IN ('M', 'F'))
);

DROP TABLE dept;


-- 테이블 레벨 제약조건 (모든 열 선언 후 제약조건을 한번에 취하는 방식)
CREATE TABLE dept (
    dept_no NUMBER(2),
    dept_name VARCHAR2(14) NOT NULL,
    loca NUMBER(4),
    dept_bonus NUMBER(10),
    dept_gender VARCHAR2(1),
    
    CONSTRAINT dept2_deptno_pk PRIMARY KEY(dept_no), 
    CONSTRAINT dept2_deptname_uk UNIQUE(dept_name),
    CONSTRAINT dept2_loca_locid_fk FOREIGN KEY(loca) REFERENCES locations(location_id),
    CONSTRAINT dept2_bonus_ck CHECK(dept_bonus > 100000),
    CONSTRAINT dept2_gender_ck CHECK(dept_gender IN('M', 'F'))
);



-- 외래 키 (foreign key)는 부모 테이블(참조 테이블)에 없는 값을 INSERT할 수 없음!
INSERT INTO dept
VALUES (20, 'bb', 2000, 1000000, 'F');

UPDATE dept
SET loca = 4000
WHERE dept_no = 10; -- 실패 (FK 제약조건 위반)  


UPDATE dept
SET dept_no = 20
WHERE dept_no = 10; -- 실패 (PK 위반)

UPDATE dept
SET dept_bonus = 4000
WHERE dept_no = 10; -- 실패 (CHECK 위반)

SELECT * FROM dept;

-- 타 테이블에서 나의 PK를 참조하는 경우에는 삭제가 맘대로 안됩니다.
DELETE FROM locations
WHERE location_id = 1800;


-- 테이블 생성 이후 제약조건 추가 및 변경, 삭제
-- 제약조건은 추가, 삭제만 가능합니다. 변경은 안됩니다.
-- 변경하려면 삭제하고 새로운 내용으로 추가하면 됩니다.

CREATE TABLE dept2 (
    dept_no NUMBER(2),
    dept_name VARCHAR2(14) NOT NULL,
    loca NUMBER(4),
    dept_bonus NUMBER(10),
    dept_gender VARCHAR2(1)
);

-- PK 추가
ALTER TABLE dept2
ADD CONSTRAINT dept2_deptno_pk PRIMARY KEY(dept_no);

-- UNIQUE 추가
ALTER TABLE dept2
ADD CONSTRAINT dept2_deptname_uk UNIQUE(dept_name);

-- FK 추가
ALTER TABLE dept2
ADD CONSTRAINT dept2_loca_locid_fk 
FOREIGN KEY(loca) REFERENCES locations(location_id);

-- CHECK 추가
ALTER TABLE dept2
ADD CONSTRAINT dept2_bonus_ck CHECK(dept_bonus > 10000);

ALTER TABLE dept2
ADD CONSTRAINT dept2_gender_ck CHECK(dept_gender IN('M', 'F'));

-- NOT NULL은 열 수정 형태로 변경합니다.
ALTER TABLE dept2
MODIFY dept_bonus NUMBER(10) NOT NULL;

-- 제약 조건 확인
SELECT * FROM user_constraints
WHERE TABLE_name = 'DEPT';

-- 제약 조건 삭제 (제약 조건 이름으로 -> 이름을 직접 짓지 않았다면 오라클 부여한다.   
ALTER TABLE dept DROP CONSTRAINT dept2_bonus_ck;

/*
view는 제한적인 자료만 보기 위해 사용하는 가상 테이블의 개념입니다.
뷰는 기본 테이블로 유도된 가상 테이블이기 때문에
필요한 컬럼만 저장해 두면 관리가 용이해 집니다.
뷰는 가상테이블로 실제 데이터가 물리적으로 저장된 형태는 아닙니다.
뷰를 통해서 데이터에 접근하면 원본 데이터는 안전하게 보호될 수 있습니다.
*/

SELECT * FROM user_sys_privs;

-- 단순 뷰 : 하나뷰의 테이블을 이용해서 생성한 뷰  
-- 뷰의 컬럼명은 함수 호출문, 연산식 등 가상 표현식이면 안됩니다! 
SELECT
    employee_id,
    first_name || ' ' || last_name,
    job_id,
    salary
FROM employees
WHERE department_id = 60;

CREATE VIEW view_emp AS (
    SELECT
    employee_id,
    first_name || ' ' || last_name AS full_name,
    job_id,
    salary
    FROM employees
    WHERE department_id = 60
);

SELECT * FROM view_emp;

-- 복합 뷰 : 두 개 이상의 테이블을 조인해서 필요한 데이터를 생성한 뷰
-- 복잡한 쿼리를 반복사용하지 않고 빠른 조회를 위해 사용. (생산성 증가)

CREATE VIEW view_emp_dept_job AS (
    SELECT
        e.employee_id,
        e.first_name || ' ' || last_name AS full_name,
        d.department_name,
        j.job_title
    FROM employees e
    LEFT JOIN departments d
    ON e.department_id = d.department_id
    LEFT JOIN jobs j
    ON e.job_id = j.job_id
)
ORDER BY e.employee_id;

SELECT * FROM view_emp_dept_job;

-- 뷰의 수정 (CREATE OR REPLACE)  
-- 동일 이름으로 해당 구문으로 사용하면 데이터가 변경되면서 새롭게 생성되고,
-- 기존에 존재하지 않았다면 새로 CREATE 됩니다.  
CREATE OR REPLACE VIEW view_emp_dept_job AS (
    SELECT
        e.employee_id,
        e.first_name || ' ' || last_name AS full_name,
        d.department_name,
        j.job_title,
        e.salary
    FROM employees e
    LEFT JOIN departments d
    ON e.department_id = d.department_id
    LEFT JOIN jobs j
    ON e.job_id = j.job_id
)
ORDER BY e.employee_id;

SELECT
    job_title,
    AVG(salary) AS avg
FROM view_emp_dept_job
GROUP BY job_title
ORDER BY avg DESC; -- SQL문이 간결해짐.  

-- 뷰 삭제
DROP VIEW view_emp;


/*
VIEW에 INSERT가 일어나는 경우 실제 테이블에도 반영이 됩니다.
그래서 VIEW의 INSERT, UPDATE, DELETE에는 많은 제약 사항이 따릅니다.
원본 테이블이 NOT NULL인 경우 VIEW에 INSERT가 불가능합니다.
VIEW에서 사용하는 컬럼이 가상열인 경우에도 안됩니다.
*/

SELECT * FROM view_emp_dept_job;

-- 가상열이 존재하기 때문에 INSERT 안됨!
INSERT INTO view_emp_dept_job 
VALUES(300, 'test', 'test', 'test', 10000);

-- JOIN된 뷰( 복합 뷰)의 경우에는 한번에 삽입할 수 없습니다.
INSERT INTO view_emp_dept_job 
(employee_id, department_name, job_title, salary)
VALUES(300, 'test', 'test', 'test', 10000);

-- 원본 테이블 컬럼 중 NOT NULL 컬럼이 존재하고, 그 칼럼을 
-- view에서 지목할 수 없다면 INSERT가 불가능합니다.
INSERT INTO view_emp
    (employee_id, job_id, salary)
VALUES(300, 'test', 10000);

DELETE FROM view_emp_dept_job
WHERE employee_id = 107;

ROLLBACK;

-- WITH CHECK OPTION -> 조건 제약 컬럼
-- 뷰를 생성할 때 사용한 조건 컬럼은 뷰를 통해서 변경할 수 없게 해주는 키워드  
CREATE OR REPLACE VIEW view_emp_test AS (
    SELECT
        employee_id,
        first_name,
        last_name,
        email,
        hire_date,
        job_id,
        department_id
    FROM employees
    WHERE department_id = 60
)
WITH CHECK OPTION CONSTRAINT view_emp_test_ck;

SELECT * FROM view_emp_test;

UPDATE view_emp_test
SET department_id = 100
WHERE employee_id = 107;


-- 읽기 전용 뷰 -> SELECT만 허용, DML 연산을 막음
CREATE OR REPLACE VIEW view_emp_test AS (
    SELECT
        employee_id,
        first_name,
        last_name,
        email,
        hire_date,
        job_id,
        department_id
    FROM employees
    WHERE department_id = 60
)
WITH READ ONLY;

-- 시퀀스 (순차적으로 증가, 감소하는 값을 만들어 주는 객체)

CREATE SEQUENCE test_seq
    START WITH 1 -- 시작값 (기본값 증가할 땐 최소값, 감소할 때 최대값)
    INCREMENT BY 1 -- 증감값 (기본값 1, 양수는 증가, 음수는 감소)
    MAXVALUE 10 -- 최대값 (기본값 1027, 감소일 때는 -1)
    MINVALUE 1 -- 최소값 (기본값 증가일 때 1, 감소일 때 -1028)
    NOCACHE -- 캐시 메모리 사용 여부 
    NOCYCLE; -- 순환 여부 (기본 값 NOCYCLE)
    
CREATE TABLE test_tbl (
    test_id NUMBER(2) PRIMARY KEY,
    test_msg VARCHAR2(100)
);

INSERT INTO test_tbl
VALUES(test_seq.NEXTVAL, 'test'); -- INSERT 실행 시 시퀀스의 다음 값 활용. 

SELECT * FROM test_tbl;

SELECT 
    test_seq.CURRVAL
FROM dual;

-- 시퀀스 속성 수정
-- START WITH은 수정이 불가능합니다.
ALTER SEQUENCE test_seq MAXVALUE 9999;
ALTER SEQUENCE test_seq INCREMENT BY -5;

DROP SEQUENCE test_seq;

-- 사용자 계정 확인
SELECT * FROM all_users;

-- 계정 생성 명령
CREATE USER user1 IDENTIFIED BY user1;


/*
DCL: GRANT(권한 부여), REVOKE(권한 회수)

CREATE USER -> 데이터베이스 유저 생성 권한
CREATE SESSION -> 데이터베이스 접속 권한
CREATE TABLE -> 테이블 생성 권한
CREATE VIEW -> 뷰 생성 권한
CREATE SEQUENCE -> 시퀀스 생성 권한
ALTER ANY TABLE -> 어떠한 테이블도 수정할 수 있는 권한
INSERT ANY TABLE -> 어떠한 테이블에도 데이터를 삽입하는 권한.
SELECT ANY TABLE...

SELECT ON [테이블 이름] TO [유저 이름] -> 특정 테이블만 조회할 수 있는 권한.
INSERT ON....
UPDATE ON....

- 관리자에 준하는 권한을 부여하는 구문.
RESOURCE, CONNECT, DBA TO [유저 이름]
*/

GRANT CREATE SESSION TO user1;
GRANT SELECT ON hr.departments TO user1;
GRANT RESOURCE, CONNECT, DBA TO user1;
-- REVOKE FROM -> 권한 회수  
REVOKE RESOURCE, CONNECT, DBA FROM user1;

-- ROLE: 특정 권한의 모음. 일괄적으로 권한을 부여하거나 회수하기 위해 사용.

CREATE ROLE new_emp_role;

GRANT SELECT, UPDATE ON hr.employees TO new_emp_role; 

GRANT new_emp_role TO user1;

-- 계층형 쿼리 
-- START WITH : 계층의 첫 단계를 어디서 시작할 것인지의 대한 조건
-- CONNECT BY PRIOR 자식 = 부모  -> 순방향 탐색
-- CONNECT BY 자식 = PRIOR 부모  -> 역방향 탐색
-- ORDER SIBLINGS BY : 같은 레벨끼리의 정렬을 정함.

SELECT
    employee_id,
    first_name,
    manager_id
FROM employees;

SELECT
    employee_id,
    first_name,
    manager_id,
    LEVEL, -- 계층 깊이를 나타내는 키워드 
    SYS_CONNECT_BY_PATH(first_name, '/') AS 계층구조 
FROM employees
START WITH manager_id IS NULL -- 계층 구조를 최상위 관리자부터 시작한다.
CONNECT BY PRIOR employee_id = manager_id -- 한 직원의 manager_id가 상위 직원의 employee_id와 일치하는 경우 부모 - 자식 관계를 형성하겠다.
AND LEVEL <= 2 -- 특정 깊이까지만 정보를 확인할 수 있음. 
ORDER SIBLINGS BY first_name;

SELECT
    LEVEL,
    LPAD(' ', 2*(LEVEL-1)) || first_name AS 조직인원,
    employee_id,
    manager_id
FROM employees
START WITH employee_id = 107
CONNECT BY employee_id = PRIOR manager_id;

SELECT * FROM employees;

-- 103번 직원의 모든 부하직원 찾기  
SELECT
    LEVEL,
    LPAD(' ', 2*(LEVEL-1)) || first_name AS 조직인원,
    salary
FROM employees
START WITH employee_id = 103
CONNECT BY PRIOR employee_id = manager_id;

-- ROLLUP(arg1, arg2) -> arg1과 arg2로 그룹화. -> 각 그룹별 SUM(salary)
-- -> arg1 그룹의 SUM(salary) -> 마지막에는 전체 SUM(salary)
-- 특정 컬럼들의 단계적 합계를 계산.
-- 부서별 급여 합계
-- 부서와 직무 조합별 급여 합계
-- 전체 급여 합계


SELECT
    department_id,
    job_id,
    SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP (department_id, job_id)
ORDER BY department_id;


-- CUBE(arg1, arg2): 모든 가능한 조합을 고려하여 집계.
-- 직무별 급여 합계
-- 부서별 급여 합계
-- 부서와 직무 조합별 급여 합계
-- 전체 급여 합계

SELECT
    department_id,
    job_id,
    SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE (department_id, job_id)
ORDER BY department_id;


-- GROUPING SETS(arg1, arg2): 주어진 그룹 집합에 대해서만 집계
-- 직무별 급여 합계
-- 부서별 급여 합계

SELECT
    department_id,
    job_id,
    SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (department_id, job_id)
ORDER BY department_id;

-- GROUPING(): 이 함수가 직접 그룹 집계를 하지는 않음.
-- 결과에서 특정 열이 집계가 되었는지의 여부를 확인하여 값을 임의로 집어 넣을때 사용.
-- ROLLUP, CUBE, GROUPING SETS에서 모두 사용 가능.

SELECT
    CASE GROUPING(department_id) WHEN 1 THEN 0 ELSE department_id END AS dept_id,
    CASE GROUPING(job_id) WHEN 1 THEN 'job_total' ELSE job_id END AS job_id,
    SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE (department_id, job_id);

---

회고 

 

1. 전반적인 느낀점 

• SQL은 처음 접해서 처음엔 많이 어려움이 있었지만, 강사님의 도움과 조금씩 복습 덕분에 어느정도 이해 할 수가 있었다.
• 하지만 아직 서브 쿼리 등의 이해가 가지 않는 몇몇 문법을 집중적으로 복습할 필요성을 느꼈다.

 

2. 좋았던 점

• 데이터 베이스를 조회, 삽입, 삭제, 변경들을 익히고, 직접 테이블을 만들어 커스텀을 할 수 있는 능력을 키울 수 있어서 좋았다.

 

3. 아쉬웠던 점

• 아직 몇몇 문법들을 이해하지 못했던 점, 알고리즘 문제를 풀 때 위의 자바를 이용해서 풀면 더 쉬웠을 텐데 이용하지 못했던 것이 많이 아쉬웠다.

 

4. 개선할 점

• 항상 느끼는 점이지만, 수업을 진행하고나면 부족하거나 이해가 가지 않는 것을 채워야 할 것 같다.
• 알고리즘 문제 수를 조금 더 늘려야 겠다는 생각을 하게 됨.

 

5. 다음주 계획

• 알고리즘 문제 풀기
• 이번에 배운 SQL 복습