public class Score {
    private int kor;
    private int eng;
    private int math;

    public Score() {}

    public Score(int kor, int eng, int math) {
        this.kor = kor;
        this.eng = eng;
        this.math = math;
    }

    public int getKor() {
        return kor;
    }

    public void setKor(int kor) {
        this.kor = kor;
    }

    public int getEng() {
        return eng;
    }

    public void setEng(int eng) {
        this.eng = eng;
    }

    public int getMath() {
        return math;
    }

    public void setMath(int math) {
        this.math = math;
    }
}
​

 

public class Student {
    private int no;
    private String name;
    private Score score;    // Student has a Score (포함 관계)

    public Student() {}

    public int getNo() {
        return no;
    }
    public void setNo(int no) {
        this.no = no;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Score getScore() {
        return score;
    }
    public void setScore(Score score) {
        this.score = score;
    }

}​

 

public class StudentApp {
    public static void main(String[] args) {
    
        // 1. 학생 정보를 저장하는 Student 객체 생성
        // 2. 그 학생의 성적 정보를 담는 Score 객체 생성
        // 3. 1번에서 생성된 Student객체에 성적 정보를 담고 있는 Score객체 전달(Setter 사용)
    
        Student s1 = new Student();
        s1.setNo(10);
        s1.setName("홍길동");

        Score s2 = new Score();
        s2.setKor(100);
        s2.setEng(70);
        s2.setMath(70);

        // Student 객체의 score필드에 Score객체 연결
        s1.setScore(s2);
    
        // 값 출력해보기

        int 번호 = s1.getNo();
        String 이름 = s1.getName();
        /*
        Score 점수 = s1.getScore();
        int 국어 = 점수.getKor();
        int 영어 = 점수.getEng();
        int 수학 = 점수.getMath();
        */

        int 국어 = s1.getScore().getKor();    // 위와 같은 코드임
        int 영어 = s1.getScore().getEng();
        int 수학 = s1.getScore().getMath();

        System.out.println(번호);
        System.out.println(이름);
        System.out.println(국어);
        System.out.println(영어);
        System.out.println(수학);

    }
}​

 

public abstract class Printer{

    public void on() {
        System.out.println("전원 켜기");
    }

    public void off() {
        System.out.println("전원 끄기");
    }

    public void feed() {
        System.out.println("용지 공급하기");
    }
    
    // 추상 메소드 정의
    // 모든 프린터들이 가지고 있는 출력기능(프린터마다 구현 내용이 다를 것으로 예상)을
    // void print()라는 추상 메소드로 추상화함으로써
    // 모든 프린터들이 출력 기능은 void print() {구체적인 구현내용}로 통일되게 정의하게 만듦
    public abstract void print();
}
​

 

public class ColorPrinter extends Printer {
    // Printer 클래스에 정의된 추상 메소드 재정의
    public void print() {
        System.out.println("컬러로 출력합니다.");
    }
    // ColorPrinter의 고유기능
    public void photo() {
        System.out.println("고품질 사진을 출력합니다.");
    
    }
}​

 

public class BlackAndWhitePrinter extends Printer {

    // Printer로부터 상속받은 추상메소드를 구체적인 구현 내용을 가진 메소드로 만든다.
    public void print() {
        System.out.println("흑백으로 출력합니다.");
    }

}
​

 

public class PrinterApp {
    public static void main(String[] args) {
        // 추상 클래스는 new 키워드를 사용해서 객체 생성 할 수 없다.
        // Printer p1 = new Printer();    <--에러

        BlackAndWhitePrinter p2 = new BlackAndWhitePrinter();
        p2.on();
        p2.feed();
        p2.print();
        p2.off();

        Printer p3 = new BlackAndWhitePrinter();
        p3.on();
        p3.feed();
        p3.print();
        p3.off();

        ColorPrinter p4 = new ColorPrinter();
        p4.on();
        p4.feed();
        p4.print();
        p4.off();

        Printer p5 = new ColorPrinter();
        p5.on();
        p5.feed();
        p5.print();
        p5.off();

    }
}​

 

public abstract class DatabaseAccess {
    public void connect() {
        System.out.println("데이터베이스와 연결");
    }
    public void send() {
        System.out.println("데이터베이스에 쿼리문 전송");
    }

    public void receive() {
        System.out.println("데이터베이스로부터 데이터 획득");
    }

    public void disconnect() {
        System.out.println("데이터베이스와 연결 해제");
    }
    
    public abstract void display();

    public void access() {
        connect();
        send();
        receive();
        disconnect();
        display();
    }
}
​

 

public class 성적조회 extends DatabaseAccess {
    public void display() {
        System.out.println("조회된 정보를 성적표에 표시합니다.");
    }
}
​

 

public class 출석부조회 extends DatabaseAccess {
    public void display() {
        System.out.println("조회된 정보를 출석부에 표시합니다.");
    }
}
​

 

public class DatabaseAccessApp {
    public static void main(String[] args) {
        출석부조회 a = new 출석부조회();
        a.access();
    }
}
​

 

public interface MemberService {

    // 추상 메소드
    public abstract void removeAllMemebers();

    //public abstract는 생략가능하다.
    public abstract void printAllMembers();

    public abstract void removeMemberByNo(int no);

    public abstract String getMemberNameByNo(int no);
}
​

 

public class MemberServiceImpl implements MemberService {

    public void removeAllMemebers() {
        System.out.println("엑셀파일에서 모든 회원 정보를 삭제한다.");
    }

    public void printAllMembers() {
        System.out.println("엑셀파일의 모든 회원정보를 출력한다.");
    }

    public void removeMemberByNo(int no) {
        System.out.println("엑셀파일에서 " + no + "번 회원을 삭제한다.");
    }

    public String getMemberNameByNo(int no) {
        String name = null;
        name = "홍길동";
        System.out.println("엑셀파일에서 " + no + "번 회원의 이름을 조회한다.");
        return name;
    }
}​

 

public class MemberApp {
    public static void main(String[] args) {
        //MemberService 인터페이스를 구현한 객체 생성
        MemberServiceImpl service1 = new MemberServiceImpl();
        service1.printAllMembers();

        // 인터페이스를 구현한 객체는 인터페이스 타입의 변수에 담을 수 있다.
        MemberService service2 = new MemberServiceImpl();
    }
}​

'자바 > oop5' 카테고리의 다른 글

Car, HRMgr, Mall.java  (0) 2019.06.07
public class Tire {
    public void go() {
        System.out.println("바퀴가 굴러갑니다.");
    }
}

 

public class SnowTire extends Tire {
    // 재정의
    public void go() {
        System.out.println("눈에서 안전하게 바퀴가 굴러갑니다.");
    }

    public void chain() {
        System.out.println("타이어에 체인을 추가로 장착할 수 있습니다.");
    }
}

 

public class KumhoSnowTire extends SnowTire {
    public void go() {
        System.out.println("금호의 신기술이 적용된 바퀴가 눈을 밀어내며 굴러갑니다.");
    }

    public void push() {
        System.out.println("눈을 바퀴 옆으로 밀어낸다.");
    }
}

 

public class InstanceOfApp {
    public static void main(String[] args) {
    /*
        객체 instanceof 클래스
        Tire t = new Tire();
        t instanceof Tire <-- t가 바라보는 객체가 Tire 종류인가요? true
        t instanceof SnowTire <--t가 바라보는 객체가 SnowTire류 인가요? true
        t instanceof KumhoSnowTire <-- t가 바라보는 객체가 KumhoSnowTire류인가요?
    */

    SnowTire t1 = new SnowTire();
    System.out.println("t1은 Tire류인가요?" + (t1 instanceof Tire));
    System.out.println("t1은 SnowTire류인가요?" + (t1 instanceof SnowTire));
    System.out.println("t1은 KumhoSnowTire류인가요?" + (t1 instanceof KumhoSnowTire));
    }
}

 

public class HankookSnowTire extends SnowTire {
    public void go() {
        System.out.println("한국의 신기술이 적용된 타이어가 눈을 녹이면서 갑니다.");
    }

    public void melt() {
        System.out.println("바퀴로 문을 녹이다.");    
    }
}​

 

public class TireTester {

    public void testTire(SnowTire tire) {
        if (tire instanceof HankookSnowTire) {
            HankookSnowTire t = (HankookSnowTire) tire;
            t.melt();
        } else if (tire instanceof KumhoSnowTire) {
            ((KumhoSnowTire) tire).push();
        }
    }
}

 

public class TireTesterApp {
    public static void main(String[] args) {
        
        TireTester tester = new TireTester();

        Tire t1 = new Tire();
        //tester.testTire(t1);    // 에러. testTire(SnowTire tire)메소드는 SnowTire류 객체만 전달받을 수 있다.

        KumhoSnowTire t2 = new KumhoSnowTire();
        tester.testTire(t2);

        HankookSnowTire t3 = new HankookSnowTire();
        tester.testTire(t3);
    }
}

 

public class TireApp {
    public static void main(String[] args) {
    
    KumhoSnowTire t1 = new KumhoSnowTire();
    SnowTire t2 = new KumhoSnowTire();
    Tire t3 = new KumhoSnowTire();

    t1.go();
    t2.go();
    t3.go();

    }
}

 

public class TireApp2 {
    public static void main(String[] args) {
        
        Tire t1 = new KumhoSnowTire();
        Tire t2 = new HankookSnowTire();
        Tire t3 = new SnowTire();
        Tire t4 = new Tire();

        t1.go();
        t2.go();
        t3.go();
        t4.go();
    }
}

 

public class TireApp3 {
    public static void main(String[] args) {
        // 에러 : 금호스노우타이어 객체를 한국스노우타이어 타입으로 형변환할 수 없다.
        // HankookSnowTire t1 = new KumhoSnowTire();

        // 에러 : 타이어 객체를 스노우타이어 타입으로 형 변환 할 수 없다.
        // 부모 타입 객체를 자식 타입의 참조 변수에 담을 수 없다.
        // SnowTire t1 = new Tire();
    }
}

 

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

    Tire t1 = new KumhoSnowTire();
    t1.go();
    t1.chain();    // 에러
    t1.push();    // 에러

    SnowTire t2 = (SnowTire) t1;
    t2.go();
    t2.chain();
    t2.push();

    }
}

 

public class Pen {
    public void draw() {
        System.out.println("그리다.");
    }
}

 

public class Redpen extends Pen {
    // 재정의
    public void draw() {
        System.out.println("빨갛게 그리다.");
    }

}

 

public class BluePen extends Pen {
    public void draw() {
        System.out.println("파랗게 그리다.");
    }
}

 

public class Painter {
    
    /* 
        매개변수의 다형성
            fillColor 메소드와 drawShape 메소드는 Pen을 상속받는 모든 Pen들을 전달받을 수 있다.
            메소드 내에서 p.draw()를 실행하면 전달받은 XXXPen 객체에 재정의된 draw()가 실행된다.
            * Pen의 종류와 상관없이 draw를 실행하면 실제로 전달받은 객체의 재정의된 draw()가 실행돼서
            펜마다의 고유 기능이 발현된다.
    */

    public void fillColor(Pen p) {
        p.draw();
    }
    
    public void drawShape(Pen p) {
        p.draw();
    }
}

 

public class PainterApp {
    public static void main(String[] args) {
        
        Painter 그림판 = new Painter();

        BluePen pen1 = new BluePen();
        Redpen pen2 = new Redpen();

        그림판.drawShape(pen1);
        그림판.drawShape(pen2);

    }
}

 

public class PhotoShop {
    private Pen pen;

    public PhotoShop() {
    
    }
    
    // Setter 메소드
    public void setPen(Pen pen) {
        this.pen = pen;
    }

    // 도형 그리기 기능
    public void drawShape() {
        pen.draw();
    }
}

 

public class PhotoShopApp {
    public static void main(String[] args) {
    
    PhotoShop ppoShop = new PhotoShop();

    BluePen pen1 = new BluePen();
    ppoShop.setPen(pen1);

    ppoShop.drawShape();

    }
}

'자바 > oop4' 카테고리의 다른 글

Camera, Phone, Car.java  (0) 2019.06.07
public class Camera {
    public void picture() {
        System.out.println("사진을 찍습니다.");
    }

    public void save() {
        System.out.println("사진을 저장합니다.");
    }

    public void delete() {
        System.out.println("사진을 삭제합니다.");
    }
}

 

public class WebCamera extends Camera {
    
    // 메소드 재정의
    public void save() {
        System.out.println("클라우드에 사진을 저장.");
    }

    // 메소드 재정의
    public void delete() {
        System.out.println("클라우드에서 사진을 삭제.");
    }
}

 

public class CameraApp {
    public static void main(String[] args) {
    
        WebCamera c = new WebCamera();

        c.picture();    // 부모로부터 상속받은 기능 사용
        c.save();        // WebCamera에 재정의된 기능 사용
        c.delete();        // WebCamera에 재정의된 기능 사용
    }
}

 

public class Phone {
    String tel;

    public void connect() {
        System.out.println(tel + "에서 전화를 겁니다.");
    }

    public void disconnect() {
        System.out.println(tel + "에서 전화를 끊습니다.");
    }
}

 

public class SmartPhone extends Phone {

    String ipAddress;

    void facetime() {
        System.out.println(tel + "로 화상통화를 시작합니다.");
    }

    void internet() {
        System.out.println(ipAddress + "로 인터넷에 접속합니다.");
    }
}

 

public class PhoneApp {
    public static void main(String[] args) {
    
        Phone p1 = new Phone();
        p1.tel = ("010-2345-5678");
        p1.connect();
        p1.disconnect();
        System.out.println();

        SmartPhone p2 = new SmartPhone();
        p2.tel = ("010-1111-2222");
        p2.connect();
        p2.disconnect();
        p2.ipAddress = ("192.168.10.254");
        p2.internet();
        p2.facetime();
    }
}

 

public class Car {
    // 자식 클래스에 상속되지 않음
    private String name;
    private int speed;
    
    public Car() {
        super();
        System.out.println("Car() 생성자가 실행됨");
    }

    public Car(String name, int speed) {
        super();
        this.name = name;
        this.speed = speed;
        System.out.println("Car(String, int) 생성자 실행됨");
    }

    // Getter/Setter 자식 클래스에 상속됨
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public int getSpeed() {
        return speed;
    }

    public void setSpeed(int speed) {
        this.speed = speed;
    }

    // 자식 클래스에 상속됨 (public 접근 제한이 적용된 필드와 메소드)
    public void Start() {
        System.out.println("출발한다.");
    }
    public void Stop() {
        System.out.println("멈춘다.");
    }

    public void carInfo() {
        System.out.println("### 차량 정보 ###");
        System.out.println("모델명 : " + name);
        System.out.println("속  도 : " + speed);
    }
}

 

public class Sonata extends Car {

    public Sonata() {
        System.out.println("Sonata() 생성자가 실행됨");
    }

    public void currentSpeed() {
        System.out.println("현재속도 : " + getSpeed());

    }

    public void speedUp() {
        int current = getSpeed();
        setSpeed(current + 20);
    }
}

 

public class CarApp {
    public static void main(String[] args) {
    
        /*
        Sonata 객체를 생성할 때 부모 객체인 Car가
        먼저 생성되고 Sonata 객체가 생성된다.
        */

        Sonata car1 = new Sonata();
        car1.speedUp();
        car1.currentSpeed();

        Sonata car2 = new Sonata();
    }
}

 

public class Genesis extends Car {
    private int price;
    
    public Genesis() {
        super();
        System.out.println("Genesis() 생성자가 실행됨");
    }

    public Genesis(String name, int speed, int price) {
        super(name, speed);
        this.price = price;
        System.out.println("Genesis(String, int, int) 생성자 실행됨");
    }

    public int getPrice() {
        return price;
    }
    public void setPrice(int price) {
        this.price = price;
    }

    public void carInfo() {
        /*
        System.out.println("### 차량 정보 ###");
        System.out.println("모델명 : " + getName());
        System.out.println("속  도 : " + getSpeed());
        */
        super.carInfo(); //    부모 클래스에 정의된 carInfo() 호출
        System.out.println("가  격 : " + price);
    }
}

 

public class CarApp2 {
    public static void main(String[] args) {
        Genesis car1 = new Genesis();
        car1.setName("G90");
        car1.setSpeed(350);
        car1.setPrice(100000000);
        car1.carInfo();

        Genesis car2 = new Genesis("G90L", 400, 150000000);
        car2.carInfo();
    }
}

'자바 > oop4' 카테고리의 다른 글

Tire, PhotoShop.java  (0) 2019.06.07
-- 전체 직원의 평균 급여보다 적은 급여를 받은 사원들의 급여 - 평균 급여를 구하기
SELECT EMPLOYEE_ID, FIRST_NAME, (SELECT avg(salary) from EMPLOYEES) - salary
from EMPLOYEES
where salary < (select avg(salary)
                   from EMPLOYEES);
 
-- with 절
-- SELECT문에서만 사용 가능하다.
-- SQL문에서 여러 번 사용되는 같은 형태의 쿼리를 with절을 사용해서 결과를 검색하고 임시로 저장한다.
-- select 문에서 with절의 결과를 이용할 수 있다.
-- 쿼리의 실행 성능이 향상된다.
-- with
-- 임시 테이블명 as (
--        select ~
--        from ~
--        where ~
-- )
-- SELECT
-- FROM 임시테이블명
-- where ~
with 
temp_avg as (
    select avg(salary) avg_salary
    from EMPLOYEES
)
select EMPLOYEE_ID, FIRST_NAME, TRUNC((SELECT avg_salary from temp_avg) - salary) salary_gap
from EMPLOYEES
WHERE SALARY < (SELECT avg_salary
                        from temp_avg);
                        
-- 부서별 총 급여를 계산했을 때, 부서별 총 급여의 평균보다 총급여를 많이 받는 부서의 이름, 총급여 조회
SELECT DEPARTMENT_NAME, TOTAL_DEPT_SALARY
FROM (SELECT B.DEPARTMENT_NAME, SUM(A.SALARY) TOTAL_DEPT_SALARY
         FROM EMPLOYEES A, DEPARTMENTS B
         WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
         GROUP BY B.DEPARTMENT_NAME) A
WHERE A.TOTAL_DEPT_SALARY > (SELECT SUM(TOTAL_DEPT_SALARY)/COUNT(*)
                                           FROM (SELECT B.DEPARTMENT_NAME, SUM(A.SALARY) TOTAL_DEPT_SALARY
                                                    FROM EMPLOYEES A, DEPARTMENTS B
                                                    WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
                                                    GROUP BY B.DEPARTMENT_NAME));
 
-- WITH절을 사용해서 부서별 총 급여의 평균보다 총급여를 많이 받는 부서의 이름, 총 급여 조회하기
WITH
    DEPT_SALARY AS (
        SELECT B.DEPARTMENT_NAME, SUM(A.SALARY) TOTAL_DEPT_SALARY
        FROM EMPLOYEES A, DEPARTMENTS B
        WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
        GROUP BY B.DEPARTMENT_NAME),
    DEPT_AVG AS (
        SELECT SUM(TOTAL_DEPT_SALARY)/COUNT(*) AVG_DEPT_SALARY
        FROM DEPT_SALARY
    )
SELECT *
FROM DEPT_SALARY
WHERE TOTAL_DEPT_SALARY > (SELECT AVG_DEPT_SALARY
                                         FROM DEPT_AVG);
                                         
-- 계층형 쿼리
-- employee에서 Neena를 시작점으로 지정하고, 상위에서 하위로 검색하기
SELECT level, employee_id, first_name, manager_id
FROM EMPLOYEES
start with first_name = 'Neena'
-- 검색방향: 상위에서 하위로
-- connect by prior 부모키 = 자식키
CONNECT BY PRIOR employee_id = manager_id;
 
-- employee에서 206번 사원을 시작점으로 지정하고, 하위에서 상위로 검색하기
SELECT EMPLOYEE_ID, FIRST_NAME, MANAGER_ID
FROM EMPLOYEES
start With  EMPLOYEE_id= 206
-- connect by prior 자식행 키 = 부모행의 키
connect by prior manager_id = EMPLOYEE_id;
 
-- employees 테이블의 계층 구조를 level과 LPAD를 사용해서 표현하기.
SELECT level, lpad(' ', (level * 5), ' '|| FIRST_NAME
FROM EMPLOYEES
START WITH EMPLOYEE_id = 100
CONNECT by PRIOR EMPLOYEE_id = MANAGER_id;
 
-- employee에서 Neena를 시작점으로 지정하고, 상위에서 하위로 검색하기
SELECT level, lpad(' ', (level * 5)-5' '|| FIRST_NAME
from EMPLOYEES
-- where FIRST_NAME != 'Nancy' -- Nancy만 안 나옴
start with first_name = 'Neena'
connect by EMPLOYEE_id = manager_id    -- 상위 -> 하위: prior 부모행의 키 = 자식행의 키
and FIRST_NAME != 'Nancy';                  -- Nanacy와 Nancy의 하위 행이 모두 나오지 않음
cs

'Oracle Database SQL' 카테고리의 다른 글

0410 필기  (0) 2019.06.17
190417  (0) 2019.06.07
190416(2)  (0) 2019.06.07
190416  (0) 2019.06.07
190415  (0) 2019.06.07
-- 모든 직원의 현재 직종 및 이전 직종을 사원 아이디, 직종 아이디로 표현하기
-- 각 사원에 대해 동일한 직종은 한 번만 조회
SELECT EMPLOYEE_ID, JOB_ID, 'C'
FROM EMPLOYEES
UNION
SELECT EMPLOYEE_ID, JOB_ID, 'P'
FROM JOB_HISTORY;
 
-- 모든 직원의 현재 직종 및 이전 직종을 사원 아이디, 직종 아이디로 표현하기
SELECT EMPLOYEE_ID, JOB_ID
FROM EMPLOYEES
UNION ALL
SELECT EMPLOYEE_ID, JOB_ID
FROM JOB_HISTORY
ORDER BY EMPLOYEE_ID;
 
-- 50번 부서에서 근무중이거나 근무했던 사원의 아이디, 직종을 조회하기
SELECT EMPLOYEE_ID, JOB_ID, HIRE_DATE AS START_DATE, NULL AS END_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
UNION ALL
SELECT EMPLOYEE_ID, JOB_ID, START_DATE, END_DATE
FROM JOB_HISTORY
WHERE DEPARTMENT_ID = 50;
 
-- 현재 50번 부서에 근무 중인 직원 중에서 이전에 50번 부서에 근무 경험이 있는 직원 조회하기
SELECT A.EMPLOYEE_ID, B.FIRST_NAME, B.SALARY
FROM (SELECT EMPLOYEE_ID
         FROM EMPLOYEES
         WHERE DEPARTMENT_ID = 50
         INTERSECT 
         SELECT EMPLOYEE_ID
         FROM JOB_HISTORY
         WHERE DEPARTMENT_ID = 50) A, EMPLOYEES B
WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID;
 
-- 90번 부서에 근무 중인 사원 중에서 업무가 변경된 적이 한 번도 없는 사원의 아이디를 조회하기
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
MINUS
SELECT EMPLOYEE_ID
FROM JOB_HISTORY;
 
-- 90번 부서에 근무 중인 사원 중에서 업무가 변경된 적이 한 번도 없는 사원의 정보를 조회하기
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID
                                FROM EMPLOYEES
                                WHERE DEPARTMENT_ID = 90
                                MINUS
                                SELECT EMPLOYEE_ID
                                FROM JOB_HISTORY);
 
-- 직원 아이디가 174 사원과 관리자 및 부서가 동일한 사원을 조회하기
SELECT *
FROM EMPLOYEES
WHERE (MANAGER_ID, DEPARTMENT_ID) IN (SELECT MANAGER_ID, DEPARTMENT_ID -- 앞부분 괄호 필수, 순서 지켜야
                                                        FROM EMPLOYEES
                                                        WHERE EMPLOYEE_ID = 174);
 
-- 부서별 최고 급여를 조회했을 때
SELECT EMPLOYEE_ID, FIRST_NAME, NVL(TO_CHAR(DEPARTMENT_ID), '없음'), SALARY
FROM EMPLOYEES
WHERE (NVL(DEPARTMENT_ID, 0), SALARY) IN (SELECT NVL(DEPARTMENT_ID, 0), MAX(SALARY)
                                                 FROM EMPLOYEES
                                                 GROUP BY DEPARTMENT_ID);
 
-- 스칼라 서브쿼리 사용하기
-- 아이디, 이름, 급여, 평균 급여와의 차
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY - (SELECT TRUNC(AVG(SALARY)) FROM EMPLOYEES) AVG_SALARY
FROM EMPLOYEES;
 
-- 전체 평균 급여보다 10000달러 이상 많이 받는 사원 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES
WHERE SALARY - (SELECT TRUNC(AVG(SALARY)) FROM EMPLOYEES) >= 10000;
 
-- 모든 사원의 급여를 인상시키기
-- 평균 급여보다 적은 급여를 받는 사원의 급여 인상시키기
-- (평균급여액 - 급여)/4
UPDATE EMPLOYEES
SET
    SALARY = SALARY + TRUNC(((SELECT AVG(SALARY) FROM EMPLOYEES) - SALARY)/4)
WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID
                                FROM EMPLOYEES
                                WHERE SALARY < (SELECT AVG(SALARY)
                                FROM EMPLOYEES));
 
SELECT * FROM EMPLOYEES;
ROLLBACK;
 
-- 급여별 급여 인상액 추정해보기
-- 15000달러 이상 10% 인상, 10000달러 이상 15% 인상, 10000달러 미만 20% 인상
-- 평균 급여 대비 인상률임
SELECT
    EMPLOYEE_ID,
    FIRST_NAME,
    SALARY,
    CASE
        WHEN SALARY >= 15000 THEN TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.1)
        WHEN SALARY >= 10000 THEN TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.15)
        ELSE TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.2)
    END INCREMENT_SALARY
FROM EMPLOYEES;
 
-- 부서 아이디, 부서 이름, 부서별 사원수 계산하기(상호연관 서브쿼리)
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME,
          (SELECT COUNT(*FROM EMPLOYEES X WHERE X.DEPARTMENT_ID = A.DEPARTMENT_ID)
FROM DEPARTMENTS A
WHERE A.MANAGER_ID IS NOT NULL;
 
-- 일반 서브쿼리와 상호 연관 서브쿼리
-- 일반 서브쿼리
-- 전체 사원의 평균급여보다 급여를 많이 받는 사원 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT TRUNC(AVG(SALARY))
                        FROM EMPLOYEES);  -- 서브쿼리는 단 한 번 실행되고, 실행 결과가 조건식에 사용된다.
 
-- 상호연관 서브쿼리
-- 소속부서의 평균급여보다 급여를 많이 받는 사원 조회하기
SELECT A.FIRST_NAME, A.DEPARTMENT_ID, A.SALARY
FROM EMPLOYEES A
WHERE A.SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES B WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID);
 
 
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'yyyy'>= '2003';
AND TO_CHAR(HIRE_DATE, 'mm') in ('01','02','03'
UNION
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
where TO_CHAR(HIRE_DATE, 'yyyymm'>= 200501
AND TO_CHAR(HIRE_DATE, 'yyyymm'<= 200506;
 
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'yyyy'= 2005;
 
SELECT a.department_id, a.department_name, (SELECT count(*FROM EMPLOYEES x where x.DEPARTMENT_ID = a.department_id) as 사원수
FROM DEPARTMENTS a;
cs

'Oracle Database SQL' 카테고리의 다른 글

0410 필기  (0) 2019.06.17
190418  (0) 2019.06.07
190416(2)  (0) 2019.06.07
190416  (0) 2019.06.07
190415  (0) 2019.06.07
-- 인덱스
-- rowid 조회하기
SELECT department_id, rowid
from EMPLOYEES;
 
-- 인덱스 생성하기
create index sample_pro_name_ix
on sample_products (product_name);
 
insert into sample_products ()
values ();
 
SELECT *
FROM SAMPLE_PRODUCTS
where PRODUCT_NAME = 'tv';
 
SELECT *
FROM SAMPLE_PRODUCTS
where PRODUCT_MAKER = '애플';
 
-- 함수 기반 인덱스 생성하기
create index emp_hire_year_ix
on employees (to_char(hire_date, 'yyyy'));
 
select *
from EMPLOYEES
where to_char(HIRE_DATE, 'yyyy'= '2008';
 
 
 
SELECT first_name
FROM EMPLOYEES
where TO_CHAR(HIRE_DATE, 'yyyy'= (SELECT TO_CHAR(HIRE_DATE, 'yyyy')
                                                 FROM EMPLOYEES
                                                 GROUP by TO_CHAR(hire_date, 'yyyy')
                                                 having count(*= (SELECT max(개수)
                                                                         from (SELECT TO_CHAR(hire_date, 'yyyy'), count(*) 개수
                                                                                 FROM EMPLOYEES
                                                                                 GROUP by TO_CHAR(hire_date, 'yyyy'))));
 
create SEQUENCE name;
cs

'Oracle Database SQL' 카테고리의 다른 글

190418  (0) 2019.06.07
190417  (0) 2019.06.07
190416  (0) 2019.06.07
190415  (0) 2019.06.07
190411  (0) 2019.06.07
CREATE TABLE TB_CATEGORIES (
    CATEGORY_NO NUMBER(30PRIMARY KEY,
    CATEGORY_NAME VARCHAR2(100NOT NULL,
    PARENT_CATEGORY_NO NUMBER(30REFERENCES TB_CATEGORIES(CATEGORY_NO)
);
 
CREATE TABLE TB_COMPANIES (
    COMPANY_NO NUMBER(3PRIMARY KEY,
    COMPANY_NAME VARCHAR2(100),
    COMPANY_TEL VARCHAR2(20),
    COMPANY_CREATE_DATE DATE DEFAULT SYSDATE
);
    
CREATE TABLE TB_GOODS (
    PRODUCT_NO NUMBER(50PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(200NOT NULL,
    COMPANY_NO NUMBER(3REFERENCES TB_COMPANIES(COMPANY_NO),
    PRODUCT_PRICE NUMBER(70),
    PRODUCT_STOCK NUMBER(40),
    PRODUCT_IMAGE_NAME VARCHAR2(100),
    PRODUCT_SELL_YN CHAR(1DEFAULT 'Y' CHECK (PRODUCT_SELL_YN IN ('Y''N')),
    PRODUCT_LIKES NUMBER(50DEFAULT 0,
    PRODUCT_CREATE_DATE DATE DEFAULT SYSDATE,
    CATEGORY_NO NUMBER(30REFERENCES TB_CATEGORIES(CATEGORY_NO)
);
    
CREATE TABLE TB_CUSTOMERS (
    CUSTOMSER_NO NUMBER(5,0NOT NULL PRIMARY KEY,
    CUSTOMSER_NAME VARCHAR2(50NOT NULL,
    CUSTOMSER_ID VARCHAR2(20UNIQUE,
    CUSTOMSER_PASSWORD VARCHAR2(20NOT NULL,
    CUSTOMSER_GENDER CHAR(1) CHECK (CUSTOMSER_GENDER IN ('F''M')),
    CUSTOMSER_EMAIL VARCHAR2(256UNIQUE,
    CUSTOMSER_PHONE VARCHAR2(20),
    CUSTOMSER_ZIPCODE CHAR(5),
    CUSTOMSER_ADDRESS VARCHAR2(200),
    CUSTOMSER_CREATE_DATE DATE
);
 
CREATE TABLE TB_ORDERS (
    ORDER_NO NUMBER(50PRIMARY KEY,
    CUSTOMER_NO NUMBER(50REFERENCES TB_CUSTOMERS(CUSTOMSER_NO),
    ORDER_STATE VARCHAR2(20DEFAULT '주문완료' CHECK(ORDER_STATE IN ('주문완료''결제완료''배송중''배송완료')),
    ORDER_CREATE_DATE DATE DEFAULT SYSDATE
);
 
CREATE TABLE TB_ORDER_ITEMS (
    ORDER_ITEM_NO NUMBER(70PRIMARY KEY,
    PRODUCT_NO NUMBER(50REFERENCES TB_GOODS(PRODUCT_NO),
    ORDER_ITEM_AMOUNT NUMBER(30),
    ORDER_NO NUMBER(50REFERENCES TB_ORDERS(ORDER_NO)
);
cs

'Oracle Database SQL' 카테고리의 다른 글

190417  (0) 2019.06.07
190416(2)  (0) 2019.06.07
190415  (0) 2019.06.07
190411  (0) 2019.06.07
190410  (0) 2019.06.07
------------------------------------------------------------ 첫 번째 트랜잭션이 시작됨
 
-- 첫 번째 트랜잭션 내의 DML 작업 1
insert into store_users
values ('kim''김유신''zxcv1234''010-2222-2222''kimg@naver.com'10, sysdate);
 
-- 첫 번째 트랜잭션 내의 DML 작업 2
update STORE_USERS
set USER_POINT = 500
where user_id = '1';
 
-- 첫 번째 트랜잭션 내의 DML 작업 3
update STORE_USERS
set USER_POINT = 0
where user_id = 'kim';
 
SELECT * FROM STORE_USERS;
 
-- 첫 번째 트랜잭션 내의 DML 작업 1, DML 작업 2, DML 작업 3을 영구적으로 DB에 반영한다.
commit;
--------------------------------------------------------- 첫 번째 트랜잭션 종료
--------------------------------------------------------- 두 번째 트랜잭션 시작
-- 두 번째 트랜잭션의 DML 작업 1
DELETE  FROM STORE_USERS WHERE USER_ID = '2';
 
UPDATE STORE_USERS SET USER_PHONE = '010-1234-5678';
 
INSERT into store_users VALUES('hong''홍길동''zxcv1234''010-3333-2222''hong@naver.com'0, SYSDATE);
 
-- 두 번째 트랜잭션 내에서 실행한 DML 작업 1, DML 작업 2의 DB 반영을 취소시킨다.
ROLLBACK;
 
--------------------------------------------------------- 두 번째 트랜잭션 종료
--------------------------------------------------------- 세 번째 트랜잭션 시작
 
-- 뷰 생성하기
-- CREATE VIEW 뷰의 이름
-- AS SELECT ~
--     FROM ~
--     WHERE ~
 
CREATE OR REPLACE VIEW emp_info_view
AS
    SELECT A.EMPLOYEE_ID AS ID, 
    A.FIRST_NAME || ', ' || A.LAST_NAME AS FULL_NAME, 
    A.SALARY, 
    A.SALARY * 12 AS ANNUAL_SALARY, 
    A.DEPARTMENT_ID AS DEPT_ID, 
    B.DEPARTMENT_NAME AS DEPT_NAME, 
    C.GRA AS SALARY_GRADE
    FROM EMPLOYEES A, DEPARTMENTS B, JOB_GRADES C -- 복합 뷰
    WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
    AND SALARY >= C.LOWEST_SALARY
    AND SALARY <= C.HIGHEST_SALARY;
    WITH READ ONLY; -- SELECT만 가능하도록
    
SELECT * FROM EMP_INFO_VIEW;
 
SELECT *
FROM EMP_INFO_VIEW
WHERE DEPT_ID = 60;
 
-- 뷰의 삭제
-- drop view 뷰 이름;
-- 뷰는 데이터를 포함하고 있찌 않기 때문에 뷰를 삭제하더라도 데이터는 지워지지 않는다.
DROP VIEW EMP_INFO_VIEW;
 
SELECT * FROM EMP_INFO_VIEW;
 
SELECT * FROM EMPLOYEES;
 
-- 인라인 뷰 작성하기
-- 부서별 사원수를 조회하기
SELECT DEPARTMENT_ID, COUNT(*) cnt
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
 
SELECT a.dept_id, b.department_name, a.cnt
from (select DEPARTMENT_id as dept_id, count(*) cnt -- dept_id // 연산식은 꼭 별칭을 주는 습관을 들이도록
       from EMPLOYEES
       GROUP BY DEPARTMENT_id) A, DEPARTMENTS B
where a.dept_id = b.department_id; -- 밖에서도 별칭으로 활용해야
 
 
--rownum
select rownum, department_id, department_name
from departments;
 
-- Top-N 분석하기
-- 급여를 가장 많이 받는 직원 3명 조회하기
SELECT ROWNUM, ID, NAME, SALARY -- 별칭 그대로 써야
FROM (SELECT employee_id as id, first_name as name, salary
         FROM EMPLOYEES
         ORDER by salary desc)
WHERE ROWNUM <= 3;
 
-- cf
 SELECT ROWNUM, employee_id, first_name, salary
 FROM EMPLOYEES
 where rownum <= 3
 ORDER BY salary DESC; -- 정렬이 마지막으로 되므로
 
 -- 가장 최근에 입사한 직원 5명 조회하기
 -- 사원 아이디, 이름, 부서명, 입사일 조회
 SELECT ROWNUM, X.EMPLOYEE_ID, X.FIRST_NAME, X.DEPARTMENT_NAME, X.HIRE_DATE
 FROM (SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME, E.HIRE_DATE
          FROM EMPLOYEES E, DEPARTMENTS D
          WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
          ORDER BY E.HIRE_DATE DESC) X
 WHERE ROWNUM <= 5;
 
 -- 80번 부서에서 급여를 가장 많이 받는 직원
 SELECT EMPLOYEE_ID
 FROM (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 80)
 WHERE ROWNUM <= 1;
 
 -- Sundita와 같은 해에 입사한 사원 중에서 급여를 가장 많이 받는 직원 3명 구하기
SELECT *
FROM (SELECT * 
         FROM EMPLOYEES
         WHERE TO_CHAR(HIRE_DATE, 'YYYY'= (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE FIRST_NAME = 'Sundita')
ORDER BY SALARY DESC)
WHERE ROWNUM <= 3;
 
-- Steven King에게 보고하는 사원 중에서 가장 최근에 입사한 사원 3명 구하기
SELECT
FROM
WHERE
 
SELECT * 
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER
WHERE WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID
AND MANAGER.EMPLOYEE_ID = 'Steven King'
 
MANAGER.FIRST_NAME || MANAGER.LAST_NAME = 
 
ORDER BY WORKER.HIRE_DATE DESC
 
WHERE ROWNUM <= 3;
 
-- 위의 쿼리는.. 조인으로 하려다 망한 코드
 
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM (SELECT * 
         FROM EMPLOYEES
         WHERE MANAGER_ID = (SELECT EMPLOYEE_ID
                                        FROM EMPLOYEES
                                        WHERE FIRST_NAME = 'Steven' AND LAST_NAME = 'King')
         ORDER BY HIRE_DATE DESC)
         WHERE ROWNUM <=3 ;
 
-- 분석함수 사용하기
-- ROW_NUMBER()는 행마다 순번을 부여한다.
-- 같은 순번을 가진 행이 존재하지 않는다. 표시되지 않는 순위가 있다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, RANK() OVER (ORDER BY SALARY DESC)
FROM EMPLOYEES; 
-- DENSE: 1, 2, 2, 3
-- DENSE 없을 때: 1, 2, 2, 4
-- DENSE_RANK()는 행마다 순위를 부여한다.
-- 같은 순위를 가진 행이 존재한다. 표시되지 않는 순위가 없다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, RANK() OVER (ORDER BY SALARY DESC)
FROM EMPLOYEES;
 
-- 급여순으로 정렬했을 때 5위~10위 사이에 속하는 직원 조회하기
SELECT RN, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT ROWNUM AS RN, EMPLOYEE_ID, FIRST_NAME, SALARY   -- 급여순으로 정렬된 결과에 순번 부여
         FROM (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY                 -- 급여순으로 내림차순 정렬
                  FROM EMPLOYEES
                  ORDER BY SALARY DESC))
WHERE RN >= 5 AND RN <= 10;
 
-- 급여순으로 정렬했을 때 11위~15위 사이에 속하는 직원 조회하기 (위의 것보다 추천함)
SELECT RN, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC) RN, EMPLOYEE_ID, FIRST_NAME, SALARY
         FROM EMPLOYEES)
         WHERE RN >= (3-1)*5+1 AND RN <= 3*5-- !!중요!!
 
 
-- 뉴스정보를 저장하는 테이블을 작성하기
-- 뉴스번호, 뉴스제목, 뉴스카테고리, 작성자, 내용, 사진파일명, 언론사이름, 작성일자, 최종수정일자
-- 뉴스번호는 행을 대표하는 컬럼이다.
-- 뉴스제목, 카테고리, 작성자, 내용, 언론사이름은 null을 허용하지 않는다.
-- 작성일자와 최종수정일자는 기본값으로 현재날짜가 저장된다.
CREATE TABLE sample_news(
    NEWS_NO             NUMBER(8,0PRIMARY KEY,
    NEWS_TITLE           VARCHAR2(100NOT NULL,
    NEWS_CATEGORY    VARCHAR2(200NOT NULL,
    NEWS_WRITER        VARCHAR2(100NOT NULL,
    NEWS_CONTENTS    VARCHAR2(1000NOT NULL,
    NEWS_PICNAME      VARCHAR2(200),
    NEWS_PRESS          VARCHAR2(200NOT NULL,
    NEWS_CREATE_DATE DATE DEFAULT SYSDATE,
    NEWS_EDIT_DATE DATE DEFAULT SYSDATE
);
 
-- 뉴스정보에 대한 댓글을 저장하는 테이블을 작성하기
-- 댓글번호, 작성자, 내용, 작성일자, 추천수, 뉴스번호
-- 댓글번호는 행을 대표하는 컬럼이다.
-- 작성자, 내용, 뉴스번호는 null을 허용하지 않는다.
-- 작성일자는 기본값으로 현재날짜가 저장된다.
-- 추천수는 기본값으로 0이 저장된다.
-- 뉴스번호는 위에서 생성한 뉴스정보 테이블의 뉴스번호를 참조한다.
CREATE TABLE sample_replies (
    replies_NO                        NUMBER(8,0PRIMARY KEY,
    replies_WRITER                   VARCHAR2(300NOT NULL,
    replies_CONTENTS               VARCHAR2(4000NOT NULL,
    replies_CREATE_DATE DATE    DEFAULT SYSDATE,
    replies_RECOMMAND           NUMBER(8,0DEFAULT 0,
    NEWS_NO                         NUMBER(8,0REFERENCES sample_news(NEWS_NO)
);
 
-- 뉴스정보테이블의 뉴스번호에 사용할 일련번호용 시퀀스를 생성하시오
-- 시작값을 100000으로 지정하고, 1씩 증가시키는 시퀀스
CREATE SEQUENCE sample_news_seq START WITH 100000 INCREMENT by 1;
 
-- 뉴스댓글테이블의 댓글번호에 사용할 일련번호용 시퀀스를 생성하시오.
CREATE SEQUENCE sample_replies_seq ;
 
-- 뉴스정보 테이블에 임의의 뉴스 정보를 데이터를 추가하는 쿼리를 작성하고, 뉴스 정보를 저장하시오.
INSERT INTO sample_news 
VALUES(0'제목''카테고리''작자''내용''사진명''언론사', SYSDATE, SYSDATE);
 
INSERT INTO sample_news (news_no, news_title, news_category, news_writer, news_contents, news_press)
VALUES(sample_news_seq.nextval, '제목제목''카테고리고리''작자작자''내용내용''언론사언론');
 
COMMIT;
 
SELECT * from sample_news;
 
-- 위에서 저장한 뉴스 정보에 대한 댓글 정보를 추가하는 쿼리를 작성하고, 댓글 정보를 저장하시오.
INSERT into SAMPLE_replies
VALUES (1'작자''내용', SYSDATE, 10);
 
INSERT into SAMPLE_replies (replies_no, replies_writer, replies_contents, news_no)
VALUES (sample_replies_seq.nextval, '작자예용''내용내용!'100000);
 
COMMIT;
 
SELECT * FROM sample_replies;
 
-- 1800번 위치에 근무하는 사원의 아이디, 이름, 직종아이디, 부서아이디, 부서이름을 조회하기
-- join
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM LOCATIONS L, EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID
AND L.LOCATION_ID = 1800;
 
-- 이름이 'A'나 'a'로  시작하는 사원의 수를 조회하기
-- like
SELECT COUNT(*)
FROM EMPLOYEES
WHERE FIRST_NAME LIKE UPPER('A%');
 
-- 모든 부서의 아이디, 부서이름, 부서별 사원수를 조회하기
-- group by, inline view
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, COUNT(*)
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME;
 
SELECT a.department_id, b.department_name, a.cnt
FROM (SELECT DEPARTMENT_id, count(*) cnt
         FROM EMPLOYEES
         group by DEPARTMENt_id) A, DEPARTMENTS B
where a.DEPartment_id=b.department_id
order by a.department_id asc;
 
-- 10번부서와 20번 부서에서 근무하는 사원들의 업무를 조회하기
-- distinct
SELECT DISTINCT(JS.JOB_TITLE)
FROM JOBS JS, EMPLOYEES E
WHERE JS.JOB_ID = E.JOB_ID
AND DEPARTMENT_ID IN (1020);
 
-- Administration  및 Executive 부서에 근무하는 직종 아이디별 사원수를 조회하기
-- subquery, group by
SELECT job_id, count(*)
FROM (SELECT js.job_id
         FROM DEPARTMENTS D, JOBS JS, EMPLOYEES E
         WHERE D.DEPARTMENT_NAME IN ('Administration''Executive')
         AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
         AND E.JOB_ID = JS.JOB_ID)
GROUP BY JOB_Id;
 
SELECT job_id, count(*)
FROM EMPLOYEES
where DEPARTMENT_ID in (SELECT DEPARTMENT_ID
                                  from DEPARTMENTS
                                  where DEPARTMENT_NAME in ('Administration''Executive'))
group by job_id;
 
-- 입사연도별 사원수를 조회했을 때 입사한 사원수가 가장 많은 년도 3개와 입사한 사원수 조회하기
-- group by, Top-N 분석
SELECT ROWNUM, hire_year, cnt
from (SELECT TO_CHAR(HIRE_DATE, 'yyyy') hire_year, count(*) cnt
FROM EMPLOYEES
group by to_char(hire_date, 'yyyy')
ORDER by cnt desc)
where rownum <= 3;
 
-- 관리자별 사원수를 조회했을 때 관리자의 아이디, 관리자의 이름, 사원수를 조회하기
-- group by, self join, inline view
SELECT worker.manager_id, manager.first_name, count(*)
FROM EMPLOYEES worker, EMPLOYEES manager
WHERE worker.MANAGER_ID = MANAGER.employee_id
GROUP BY worker.MANAGER_ID, manager.first_name
order by worker.manager_id;
 
-- 급여등급별 사원수를 조회해서 사원수가 많은 순으로 조회하기
-- group by, order by
SELECT b.gra, count(*) cnt
from EMPLOYEES a, JOB_GRADES B
where a.SALARY >= B.LOWEST_SALARY and A.SALARY <= B.HIGHEST_SALARY
group by b.gra
order by cnt desc;
 
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여를 조회하기
-- subquery
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
where department_id = (SELECT department_id
                              from EMPLOYEES
                              where FIRST_NAME = 'Allan');
                              
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여, 급여등급을 조회하기
-- subquery, 비등가 조인
 
SELECT * from JOB_GRADES;
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여, 부서 아이디, 부서이름을 조회하기
-- subquery, join
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, b.gra
FROM EMPLOYEES A, JOB_GRADES B
where DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM EMPLOYEES
                                  WHERE FIRST_NAME = 'Allan')
and A.salary >= b.lowest_salary and a.salary <= b.highest_salary;
                                  
-- 'Allan'과 같은 부서에 근무하는 사원들의 아이디, 이름, 급여를 조회하고, 급여를 가장 많이 받는 사원
-- 3명을 조회하기
-- subquery, inline view, top-n 분석
SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, SALARY
from EMPLOYEES
where DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM EMPLOYEES
                                  WHERE FIRST_NAME = 'Allan')
and ROWNUM <= 3;
 
-- 'Allan'과 같은 부서에 근무하는 사원들을 급여등급별로 사원수를 조회하기
-- subquery, 비등가조인, group by
SELECT JG.GRA, COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
where E.SALARY >= JG.LOWEST_SALARY AND E.SALARY <= JG.HIGHEST_SALARY
AND DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM EMPLOYEES
                                  WHERE FIRST_NAME = 'Allan')
GROUP BY JG.GRA;
                                  
 
-- 'Allan'과 같은 부서에 근무하는 사원들의 급여를 10%인상시키기
-- subquery, update
UPDATE (SELECT *
            FROM EMPLOYEES
            WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                               FROM EMPLOYEES
                                               WHERE FIRST_NAME = 'Allan'))
SET SALARY = SALARY * 1.1;
 
cs

'Oracle Database SQL' 카테고리의 다른 글

190416(2)  (0) 2019.06.07
190416  (0) 2019.06.07
190411  (0) 2019.06.07
190410  (0) 2019.06.07
190409  (0) 2019.06.07
-- DDL
-- 데이터베이스 정의어
-- 데이터베이스 객체의 생성, 삭제, 수정과 관련된 SQL
-- CREATE문, DROP문, ALTER문
-- COMMIT, ROLLBACK의 적용대상이 아니다.
 
-- 오라클의 데이터베이스 객체
-- 테이블: 가장 기본적인 데이터 저장소, 행과 열로 구분되어 있다.
-- 뷰: 하나 이상의 테이블에 있는 데이터의 부분 집합으로 구성된 가상의 테이블이다.
-- 시퀀스: 일련번호 생성기
-- 인덱스: 쿼리의 실행 속도를 향상시키기 위해서 테이블의 데이터에 대한 색인을 갖고 있는 객체다.
 
SELECT ROWID, EMPLOYEE_ID
FROM EMPLOYEES;
 
-- 시퀀스
-- 일련번호 생성한다.
-- 여러 사용자가 공유할 수 있다.
-- 행의 고유한 기본키 값을 생성하는데 주로 사용된다.
-- 시퀀스는 테이블과는 별도로 저장, 관리되기 때문에 여러 테이블에서 동일한 시퀀스를 사용할 수 있다.
 
-- 시퀀스 생성하기
-- CREATE SEQUENCE 시퀀스명
CREATE SEQUENCE SAMPLE_SEQ;
 
CREATE SEQUENCE MY_SEQ;
 
-- 새로운 일련 번호 발급 받기
-- NEXTVAL을 사용하면 새로운 일련 번호를 발급받을 수 있다.
 
INSERT INTO BOOKS
(BOOK_NO, BOOK_TITLE, BOOK_AUTHOR, BOOK_PUBLISHER, BOOK_PRICE, BOOK_PUBDATE, BOOK_CREATE_DATE)
VALUES
(MY_SEQ.NEXTVAL, '이것이 자바다''신용권''한빛미디어'30000'2015-01-01', SYSDATE);
 
-- CURRVAL을 사용하면 현재 사용자가 방금 생성한 일련번호를 조회할 수 있다.
SELECT MY_SEQ.NEXTVAL FROM DUAL;
SELECT MY_SEQ.CURRVAL FROM DUAL;
 
-- 시퀀스 삭제하기
-- DROP SEQUENCE 시퀀스명
DROP SEQUENCE MY_SEQ;
 
 
-- 시퀀스 생성하기
CREATE SEQUENCE 시퀀스명
    INCREMENT BY 숫자값         번호 사이의 간격을 지정(기본값은 1)
    START WITH 숫자값            첫번째 일련번호 지정(기본값은 1)
    MAXVALUE 숫자값             시퀀스의 최댓값을 지정(기본값은 10^27)
    MINVALUE 숫자값              시퀀스의 최솟값을 지정(기본값은 1)
    CYCLE | NOCYCLE               최댓값 도달 이후에도 시퀀스를 계속 생성할지 여부(기본값은
    CACHE 숫자값 | NOCACHE;   일련번호를 미리 생성해서 메모리에 저장할 개수를 지정(기본값은 CACHE 20);
    
-- 시퀀스의 NEXTVAL, CURRVAL 사용처
-- 1. 서브쿼리에 속하지 않는 SELECT문의 SELECT절에서 사용 가능
-- 2. INSERT문의 VALUES 절
-- 3. UPDATE문의 SET절
 
-- 테이블 정의하기
-- 테이블의 데이터를 저장하는 객체
-- 테이블의 이름, 컬럼의 이름은 30자까지만 가능
-- A-Z, a-z, 0-9, _, $, #를 사용할 수 있다.
 
-- 테이블 생성하기
-- CREATE TABLE 테이블명 (
--     컬럼 이름, 데이터 타입(크기)
--     컬럼 이름, 데이터 타입(크기)
--     컬럼 이름, 데이터 타입(크기), DEFAULT 값
-- );
 
-- 오라클의 데이터 타입
-- VARCHAR2(SIZE)       가변 길이 문자 데이터(최대 4000 바이트)
--                             (이름, 주소, 블로그의 내용, 책의 제목, ...)
-- CHAR(SIZE)              고정 길이 문자 데이터(최대 2000 바이트)
--                             (우편 번호, 군번, 주민등록번호, 사업자등록번호,...)
-- NUMBER(P, S)          가변 길이 숫자 데이터(P는 십진 자릿수, S는 소수점 이하 자릿수)      
-- DATE                     날짜 및 시간
-- LONG                    최대 2GB의 가변 길이 문자 데이터
--                            테이블 당 하나만 정의할 수 있다. 제약 조건을 정의할 수 없다.
--                            GROUP BY, ORDER BY 절에 포함시킬 수 없다.
-- CLOB                     최대 4GB의 가변 길이 문자 데이터 CHARACTER LARGE OBJECT
-- BLOB                     최대 4GB의 이진(바이너리) 데이터 BINARY ~
-- ROWID                  테이블에서 행의 고유 주소를 나타내는 64진수
 
CREATE TABLE SAMPLE_CATEGORY (
    CATEGORY_CODE CHAR(2PRIMARY KEY,        -- 고정 길이 문자 데이터
    CATEGORY_NAME VARCHAR2(200)                -- 가변 길이 문자 데이터
);
 
INSERT INTO SAMPLE_CATEGORY VALUES('FU''가구');
INSERT INTO SAMPLE_CATEGORY VALUES('ED''가전기기');
INSERT INTO SAMPLE_CATEGORY VALUES('KH''주방기기');
INSERT INTO SAMPLE_CATEGORY VALUES('LG''생활용품');
 
INSERT INTO SAMPLE_CATEGORY VALUES('E''기타');
COMMIT;
 
DELETE SAMPLE_CATEGORY;
 
SELECT * FROM SAMPLE_CATEGORY
WHERE CATEGORY_CODE = 'E ';
 
-- 컬럼 레벨 제약 조건 설정
CREATE TABLE SAMPLE_PRODUCTS (
    PRODUCT_NO                    NUMBER(4)                     CONSTRAINT PRODUCTS_NO_PK                 PRIMARY KEY,                                                                                       -- 숫자 데이터, 기본키 제약 조건
    PRODUCT_NAME                VARCHAR2(500)               CONSTRAINT PRODUCTS_NAME_NN             NOT NULL,                                                                                           -- 가변 길이 문자 데이터, not null 제약 조건
    PRODUCT_MAKER               VARCHAR2(200),                                                                                                                                                                                 -- 가변 길이 문자 데이터, 
    PRODUCT_PRICE                 NUMBER(80)                  CONSTRAINT PRODUCTS_PRICE_CK               CHECK (PRODUCT_PRICE > 0),                                                                    -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_STOCK                NUMBER(40)                  CONSTRAINT PRODUCTS_STOCK_CK              CHECK (PRODUCT_STOCK > 0),                                                                  -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_SELL                   CHAR(1)                         CONSTRAINT PRODUCTS_SELL_CK                CHECK(PRODUCT_SELL IN ('Y''N')),
    PRODUCT_CATEGORY           CHAR(2)                         CONSTRAINT PRODUCT_CATEGORY_FK           REFERENCES SAMPLE_CATEGORY(CATEGORY_CODE),
    PRODUCT_CREATE_DATE DATE DEFAULT SYSDATE                                                                                                                       
);
 
 
-- 테이블 레벨 제약 조건 설정
CREATE TABLE SAMPLE_PRODUCTS (
    PRODUCT_NO                    NUMBER(4),
    PRODUCT_NAME                VARCHAR2(500)               CONSTRAINT PRODUCTS_NAME_NN             NOT NULL,                                                         -- 가변 길이 문자 데이터, not null 제약 조건은 테이블 레벨로 못 내림
    PRODUCT_MAKER               VARCHAR2(200),                                                                                                                                                                                 -- 가변 길이 문자 데이터, 
    PRODUCT_PRICE                 NUMBER(7)                  CONSTRAINT PRODUCTS_PRICE_NN               NOT NULL,                                                                    -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_STOCK                NUMBER(3)                  CONSTRAINT PRODUCTS_STOCK_NN              NOT NULL,                                                                  -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_SELL                   CHAR(1)                     DEFAULT 'Y' CONSTRAINT PRODUCTS_SELL_NN NOT NULL,
    PRODUCT_CATEGORY           CHAR(2)                         CONSTRAINT PRODUCT_CATEGORY_NN       NOT NULL,
    PRODUCT_CREATE_DATE DATE DEFAULT SYSDATE,    
 
    CONSTRAINT PRODUCTS_NO_PK PRIMARY KEY (PRODUCT_NO),
    CONSTRAINT PRODUCTS_PRICE_CK CHECK (PRODUCT_PRICE > 0),
    CONSTRAINT PRODUCTS_STOCK_CK CHECK (PRODUCT_STOCK > 0),
    CONSTRAINT PRODUCTS_SELL_CK CHECK (PRODUCT_SELL IN ('Y''N')),
    CONSTRAINT PRODUCTS_CATEGORY_FK FOREIGN KEY (PRODUCT_CATEGORY) REFERENCES SAMPLE_CATEGORY (CATEGORY_CODE)
);
 
 
 
CREATE SEQUENCE PRODUCTS_SEQ START WITH 1000;
 
INSERT INTO SAMPLE_PRODUCTS
(PRODUCT_NO, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_STOCK, PRODUCT_SELL, PRODUCT_CATEGORY)
VALUES
(PRODUCTS_SEQ.NEXTVAL, '양문형 냉장고'100000010'Y''FU');
 
INSERT INTO SAMPLE_PRODUCTS(PRODUCT_NO, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_SELL)
VALUES (PRODUCTS_SEQ.NEXTVAL, '사계절 김치 냉장고'10000000);
 
INSERT INTO SAMPLE_PRODUCTS(PRODUCT_NO, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_STOCK, PRODUCT_DISCOUNT_RATE, CATEGORY_CODE)
VALUES
(PRODUCTS_SEQ.NEXTVAL, '가죽 소파'1000000100.00'FU');
 
 
SELECT * FROM SAMPLE_PRODUCTS();
DROP TABLE SAMPLE_PRODUCTS;
DROP_TABLE SAMPLE_CATEGORY;
cs

'Oracle Database SQL' 카테고리의 다른 글

190416  (0) 2019.06.07
190415  (0) 2019.06.07
190410  (0) 2019.06.07
190409  (0) 2019.06.07
190408  (0) 2019.06.07
CREATE TABLE STORE_USERS (
    USER_ID VARCHAR2(20PRIMARY KEY,
    USER_NAME VARCHAR2(100),
    USER_PWD VARCHAR2(20),
    USER_PHONE VARCHAR2(200),
    USER_EMAIL VARCHAR2(200),
    USER_POINT NUMBER(7),
    USER_CREATE_DATE DATE DEFAULT SYSDATE
);
 
SELECT * FROM store_users;
cs

'Oracle Database SQL' 카테고리의 다른 글

190415  (0) 2019.06.07
190411  (0) 2019.06.07
190409  (0) 2019.06.07
190408  (0) 2019.06.07
190405  (0) 2019.06.07
create table user_contacts(
                user_no number(3primary key,
                user_name varchar2(20),
                user_phone varchar2(20),
                user_create_date date
                );
                
drop table user_contacts;
 
SELECT * FROM USER_CONTACTS;
 
DELETE FROM USER_CONTACTS;
 
CREATE TABLE books (
    book_no            number(4PRIMARY KEY,
    book_title          VARCHAR2(500),
    book_author       VARCHAR2(200),
    book_publisher    VARCHAR2(200),
    book_price         number(7),
    book_pubdate     VARCHAR2(10),     -- '2016-12-31'
    book_create_date date DEFAULT SYSDATE
);
select * from books;
 
--    1. 사원 아이디를 입력받아서 사원 아이디, 이름, 직종 아이디, 급여, 커미션, 부서 아이디, 부서 이름을 조회하는 기능
SELECT  E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY, E.COMMISSION_PCT, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
AND E.EMPLOYEE_ID = ;
 
 
--    2. 입사년도를 입력받아서 사원 아이디, 이름, 입사일, 부서 이름을 조회하는 기능
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.HIRE_DATE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.EMPLOYEE_ID = D.DEPARTMENT_ID
AND TO_CHAR(E.HIRE_DATE, 'YYYY'= ? ;
 
--    3. 급여 등급을 입력받아서 사원 아이디, 이름, 급여, 급여 등급을 조회하는 기능
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, JG.GRA
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
AND JG.GRA = ? ;
 
--    4. 부서 아이디를 입력받아서 사원 아이디, 이름, 부서 아이디, 부서 이름을 조회하는 기능
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.EMPLOYEE_ID = D.DEPARTMENT_ID
AND E.DEPARTMENT_ID = ? ;
 
--    5. 관리자 이름을 입력받아서 해당 관리자에게 보고하는 직원의 아이디, 이름, 입사일을 조회하는 기능
SELECT WORKER.EMPLOYEE_ID, WORKER.FIRST_NAME, WORKER.HIRE_DATE
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER
WHERE WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID;
cs

'Oracle Database SQL' 카테고리의 다른 글

190411  (0) 2019.06.07
190410  (0) 2019.06.07
190408  (0) 2019.06.07
190405  (0) 2019.06.07
190404  (0) 2019.06.07
-- 단일행 서브쿼리 연산자
-- >, <, >=, <=, =, <>
 
-- 'Hermann'보다 급여를 많이 받는 사원의 이름과 급여를 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT SALARY
                        FROM EMPLOYEES
                        WHERE FIRST_NAME = 'Hermann');
 
-- 전체 사원들의 평균 급여보다 급여를 적게 받는 사원의 이름과 급여를 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
                        FROM EMPLOYEES);
                        
-- 부서별로 평균 급여를 계산했을 때 평균 급여가 가장 낮은 부서의 아이디와 평균 급여 조회하기
SELECT DEPARTMENT_ID, TRUNC(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) = (SELECT MIN(AVG(SALARY))
                                 FROM EMPLOYEES
                                 GROUP BY DEPARTMENT_ID);
                                 
-- 다중행 서브쿼리 연산자
-- IN: 서브쿼리가 반환하는 값 목록 중 어느 하나와 일치하면 TRUE가 반환된다(해당 행은 선택된다).
-- ANY: 서브쿼리가 반환하는 값 목록 중 어느 하나만 제시된 조건이 만족하면 TRUE 반환
-- ALL: 서브쿼리가 반환하는 값 조건 목록 모두 만족하면 TRUE 반환
-- SALARY > ANY (서브 쿼리)       SALARY > ALL (서브 쿼리)          SALARY = ANY (서브 쿼리)
-- SALARY > 최솟값                   SALARY > 최댓값                     SALARY IN (서브 쿼리)
 
-- 직종별 평균 급여를 계산했을 때 급여 평균이 3000미만인 직종에 근무하는 사원의 이름, 직종, 급여 조회하기
SELECT FIRST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE JOB_ID IN (SELECT JOB_ID
                       FROM EMPLOYEES
                       GROUP BY JOB_ID
                       HAVING AVG(SALARY) < 3000);
                       
                      
-- 직종이 IT_PROG인 사원들보다 급여를 많이 받는 사원들의 이름, 직종, 급여 조회하기
SELECT *
FROM EMPLOYEES
WHERE SALARY > ANY (600055008000); -- ANY는 최솟값과 같은 의미.
 
SELECT *
FROM EMPLOYEES
WHERE SALARY > ALL (600055008000); -- ALL은 최댓값과 같은 의미.
 
-- 상사가 없는 직원 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE MANAGER_ID IN (SELECT MANAGER_ID
                                FROM EMPLOYEES
                                WHERE MANAGER_ID IS NULL);
-- 널이면 같다, 작다 비교가 안 됨.
 
-- 직종이 IT_PROG인 사원들보다 급여를 많이 받는 사원들의 이름, 직종, 급여를 조회하기
-- IT_PROG 직종의 최고 급여보다 급여를 많이 받는 사원을 조회
SELECT FIRST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > ALL (SELECT SALARY
                              FROM EMPLOYEES
                              WHERE JOB_ID = 'IT_PROG')
AND JOB_ID <> 'IT_PROG';
 
-- IT_PROG 직종의 최소 급여보다 급여를 많이 받는 사원을 조회
SELECT FIRST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY > ANY (SELECT SALARY
                              FROM EMPLOYEES
                              WHERE JOB_ID = 'IT_PROG')
AND JOB_ID <> 'IT_PROG';
                              
-- 사용자 정의 데이터베이스 객체(테이블) 생성하기
CREATE TABLE COURSES (
     COURSE_NO NUMBER(3PRIMARY KEY,  -- 과정 번호
     COURSE_NAME VARCHAR2(200),          -- 과정명
     COURSE_PRICE NUMBER(7),                 -- 수강료
     COURSE_QUOTA NUMBER(3),              -- 모집 인원
     COURSE_TEACHER VARCHAR2(100),      -- 강사
     COURSE_START_DATE DATE,               -- 시작일
     COURSE_END_DATE DATE,                  -- 종료일
     COURSE_ROOM VARCHAR2(20),          -- 강의장
     COURSE_CREATE_DATE DATE              -- 데이터 생성일
);
 
-- 테이블에 새로운 행을 추가
-- INSERT INTO 구문을 사용한다.
-- 1. 컬럼명을 명시해서 INSERT 구문을 작성한다.
-- (테이블의 전체 컬럼명 혹은 값이 결정된 컬럼명만 적을 수도 있다.)
-- INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...)
-- VALUES                   (값1,       값2,      ...)
-- 2. 컬럼명을 생략하고 INSERT 구문을 작성한다.
-- (테이블의 모든 컬럼에 해당하는 값이 VALUES절에 명시되어야 한다(NULL값도 포함).)
-- INSERT INTO 테이블명
-- VALUES (값1, 값2, 값3, ...)
-- 한 번에 한 행만 추가한다.
-- 값을 표기할 때 문자 및 날짜는 작은 따옴표로 묶는다.
 
-- 새 과정 추가하기
INSERT INTO COURSES (COURSE_NO, COURSE_NAME, COURSE_PRICE, COURSE_QUOTA, COURSE_TEACHER, COURSE_START_DATE, COURSE_END_DATE, COURSE_ROOM, COURSE_CREATE_DATE)
VALUES(100'자바 개발자 과정'100000020'홍길동''2019/05/01''2019/12/31''L2', SYSDATE);
COMMIT;
 
-- 새 과정 추가하기
INSERT INTO COURSES (COURSE_NO, COURSE_NAME, COURSE_PRICE, COURSE_QUOTA, COURSE_CREATE_DATE)
VALUES(200'안드로이드 앱 개발자 과정'120000015, SYSDATE);
 
INSERT INTO COURSES (COURSE_NO, COURSE_NAME, COURSE_PRICE, COURSE_QUOTA, COURSE_TEACHER, COURSE_START_DATE, COURSE_END_DATE, COURSE_ROOM, COURSE_CREATE_DATE)
VALUES (400'데이터분석가 과정'2500000NULLNULLNULLNULL'L3');
 
 
SELECT * FROM COURSES;
 
-- 테이블의 데이터 변경하기
-- UPDATE 구문을 사용하면 하나 혹은 여러 행의 데이터를 변경할 수 있다.
-- UPDATE 테이블명
-- SET 컬럼명1 = 값1, 컬럼명2 = 값2, 컬럼명3 = 값3, ...
-- [WHERE 조건식]
-- WHERE 절은 생략할 수 있다. 생략하면 모든 행에서 지정된 컬럼의 값을 갱신한다.
 
-- 100번 과정의 수강료를 50만 원으로 변경하기
UPDATE COURSES
SET COURSE_PRICE = 500000
WHERE COURSE_NO = 100;
 
-- 200번 과정의 강사명, 시작일, 종료일, 강의실 변경하기
UPDATE COURSES
SET COURSE_TEACHER = '이순신',
     COURSE_START_DATE = '2019/05/25',
     COURSE_END_DATE = '2019/08/25',
     COURSE_ROOM = 'L6'
WHERE COURSE_NO = 200;
 
-- 모든 직원들의 급여를 1000달러 인상하기
UPDATE EMPLOYEES
SET SALARY = SALARY + 1000;
 
SELECT * FROM EMPLOYEES;
 
-- 2007년 입사자의 평균 급여
SELECT TRUNC(AVG(SALARY))
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY'= '2007';
 
-- 132번 TJ의 급여를 2007년 입사들의 평균 급여의 70% 수준으로 연장
UPDATE EMPLOYEES
SET SALARY = (SELECT TRUNC(AVG(SALARY) * 0.7)
                   FROM EMPLOYEES
                   WHERE TO_CHAR(HIRE_DATE, 'YYYY'= '2007')
WHERE EMPLOYEE_ID = 132;
 
-- 100번 직원의 부서 번호를 300번으로 변경하기
UPDATE EMPLOYEES
SET DEPARTMENT_ID = 300 -- 300번에 해당하는 부서번호가 존재하지 않으므로 부서번호를 300번으로 바꿀 수 없음
WHERE EMPLOYEE_ID = 100;
 
-- 100번 과정의 수강료를 변경하기
UPDATE COURSES
SET COURSE_PRICE = 12000000
WHERE COURSE_NO = 100;
 
UPDATE DEPARTMENTS
SET DEPARTMENT_ID = 300
WHERE DEPARTMENT_ID = 270;
 
-- 제약 조건 2가지
-- 부모 테이블에 없는 값으로 자식 레코드 임의 변경 불가
-- 자식 테이블에서 쓰고 있는 값을 임의로 변경 불가
 
-- 테이블에서 행 삭제하기
-- DELETE 구문을 사용하면 하나 혹은 여러 개의 행을 삭제할 수 있다.
-- DELETE FROM 테이블명
-- [WHERE 조건식] 조건식을 생략하면 테이블 내의 모든 행이 삭제된다.
 
-- 과정 번호가 100번인 행 삭제하기
DELETE FROM COURSES
WHERE COURSE_NO = 100;
 
-- 홍길동이 강의하는 모든 과정 삭제하기
DELETE FROM COURSES
WHERE COURSE_TEACHER = '홍길동';
 
SELECT * FROM COURSES;
 
-- 모든 과정 삭제하기
DELETE FROM COURSES; -- 데이터만 삭제(아직 메모리 용량은 그대로 차지), 롤백 가능
 
ROLLBACK;
 
-- 모든 과정 삭제하기
TRUNCATE TABLE COURSES; -- 최초의 데이터 생성 시점으로, 롤백 불가, 트랜잭션 로그 기록 안 함.
-- DROP TABLE EMPLOYEES : 롤백 불가, 테이블이 없어짐
 
 
CREATE TABLE STORES (
     STORE_NO NUMBER(3PRIMARY KEY,      -- 상점 번호
     STORE_NAME VARCHAR2(200),              -- 상점 이름
     STORE_OWNER VARCHAR2(100),            -- 점주명
     STORE_TEL VARCHAR2(20),                   -- 상점 전화 번호
     STORE_EMP_COUNT NUMBER(2),             -- 직원수
     STORE_LOCATION VARCHAR2(100),         -- 상점 소재지
     STORE_OPEN_DATE DATE,                     -- 상점 오픈 예정일
     STORE_CREATE_DATE DEFAULT SYSDATE   -- 데이터 생성일
);
 
-- 상점 3개 저장하기
INSERT INTO STORES
VALUES (001'가''가가가''111-1111'01'서울''2019/04/01''2019/04/08');
 
INSERT INTO STORES
VALUES (002'나''나나나''222-2222'02'경기''2019/04/02''2019/04/08');
 
INSERT INTO STORES
VALUES (003'다''다다다''333-3333'03'강원''2019/04/03''2019/04/08');
 
SELECT * FROM STORES;
 
-- 최초 저장된 3개의 상점 중에서 하나를 선택해서 직원 수를 변경하기
UPDATE STORES
SET STORE_EMP_COUNT = 99
WHERE STORE_NO = 1;
 
-- 최초 저장된 3개의 상점 중에서 하나를 선택해서 연락처, 직원수, 오픈 예정일을 변경하기
UPDATE STORES
SET STORE_TEL = '999-9999',
      STORE_EMP_COUNT = 99,
      STORE_OPEN_DATE = '1111-11-11'
WHERE STORE_NO = 2;
 
-- 최초 저장된 3개의 상점 중에서 하나를 선택해서 지우기
DELETE FROM STORES
WHERE STORE_NO = 3;
 
-- 모든 상점 정보를 지우기
TRUNCATE TABLE STORES;
 
SELECT A.FIRST_NAME, A.DEPARTMENT_ID || '(' || B.DEPARTMENT_NAME || ')'
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;
 
-- 부서이름별 사원수, 평균 급여를 조회하기
SELECT DEPARTMENTS.DEPARTMENT_NAME, COUNT(*), AVG(EMPLOYEES.SALARY)
FROM DEPARTMENTS, EMPLOYEES
WHERE DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME;
 
-- 과제
-- 최고 급여와 최저급여의 차액을 조회하기
SELECT MAX(SALARY) - MIN(SALARY)
FROM EMPLOYEES;
 
-- 부서 소재지(city)별 직원수를 조회하기
SELECT C.CITY, COUNT(*)
FROM EMPLOYEES A, DEPARTMENTS B, LOCATIONS C
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND B.LOCATION_ID = C.LOCATION_ID
GROUP BY C.CITY;
 
-- 'Hermann'과 같은 해에 입사한 사원들의 이름과 입사일 조회하기 --> 서브 쿼리
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY'= (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE FIRST_NAME = 'Hermann')
AND FIRST_NAME <> 'Hermann';
 
-- 50번 부서의 평균급여보다 적은 급여를 받는 다른 부서 직원의 이름과 급여를 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
                        FROM EMPLOYEES
                        WHERE DEPARTMENT_ID = 50)
AND DEPARTMENT_ID <> 50;
 
-- 1700번 지역에 소재지를 두고 있는 새로운 부서를 추가하기
-- (부서 아이디, 부서명, 관리자 아이디, 지역번호 입력)
INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (280'SI'1041700);
 
-- 위에서 생성한 부서에 새로운 사원을 추가하기
-- (사원아이디, 이름1, 이름2, 이메일, 입사일(오늘), 직종아이디, 급여, 부서아이디 )
INSERT INTO EMPLOYEES
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID)
VALUES
(207'길동''홍''hong@gmail.com', SYSDATE, 'IT_PROG'5000280);
cs

'Oracle Database SQL' 카테고리의 다른 글

190410  (0) 2019.06.07
190409  (0) 2019.06.07
190405  (0) 2019.06.07
190404  (0) 2019.06.07
190403  (0) 2019.06.07
-- 80번 부서에 근무하는 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = 80;
 
-- 커미션을 받는 사원 중에서 급여가 10000달러 이상인 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E
WHERE E.COMMISSION_PCT IS NOT NULL
AND E.SALARY >= 10000;
 
-- 사원들 중에서 급여 등급이 B등급인 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
AND JG.GRA = 'B';
 
-- 부서별 사원 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
 
-- 오류 DEPARTMENT_ID는 107개, COUNT는 1개 나오므로.
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES;
 
-- 그룹 바이 절에 등장한 컬럼은 가능
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
 
-- SELECT 5
-- FROM 1
-- WHERE 2
-- GROUP BY 3
-- HAVING 4 그룹함수가 적용된 행에 제한을 가함. 따라서 그룹 바이가 있어야 함.
-- ORDER BY 6
-- GROUP BY 절에서는 SELECT의 별칭을 사용할 수 없다.
-- GROUP 함수는 한 번만 중첩이 가능하다.
-- SUM(COUNT(*)) 가능
-- AVG(SUM(COUNT(*))) 불가능
 
-- 부서별 사원 수 조회하기
SELECT DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*>= 30;
 
-- 부서별 평균 급여를 조회하기(10000달러 미만)
SELECT E.DEPARTMENT_ID, TRUNC(AVG(E.SALARY))
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID IS NOT NULL
GROUP BY E.DEPARTMENT_ID
HAVING TRUNC(AVG(E.SALARY)) < 10000;
 
-- 급여 등급별 사원 수를 조회하기
SELECT JG.GRA, COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
GROUP BY JG.GRA
ORDER BY 1;
 
-- 급여별 사원 수를 조회하기
-- 2000    10    2000~2999
-- 3000    14    3000~3999
-- 4000     7    4000~4999
-- 5000    11    5000~5999
SELECT TRUNC(SALARY, -3), COUNT(*)
FROM EMPLOYEES
GROUP BY TRUNC(SALARY, -3)
ORDER BY 1;
 
SELECT SUBSTR(FIRST_NAME, 11), COUNT(*)
FROM EMPLOYEES
GROUP BY SUBSTR(FIRST_NAME, 11)
ORDER BY 1;
 
-- 부서별, 직종별 사원수 계산하기
SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY 12;
 
-- 부서 소재지(LOCATION의 CITY)별 사원 수 조회하기
SELECT L.CITY, COUNT(L.CITY)
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.DEPARTMENT_ID IS NOT NULL
GROUP BY L.CITY
ORDER BY L.CITY;
 
-- 각 부서별 최고/최저/평균 급여를 조회하기
SELECT D.DEPARTMENT_NAME, MAX(E.SALARY), MIN(E.SALARY), TRUNC(AVG(E.SALARY))
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.DEPARTMENT_ID IS NOT NULL -- 있어도 그만, 없어도 그만
GROUP BY D.DEPARTMENT_NAME
HAVING MAX(E.SALARY) > 10000;
 
-- 서브쿼리
-- 다른 SQL문의 WHERE절에서 사용되는 쿼리
-- SELECT
-- FROM
-- WHERE 컬럼 연산자 (SELECT 컬럼 FROM WHERE)
 
-- 전체 사원의 평균급여보다 급여를 적게 받는 사원의 아이디, 이름, 급여를 조회하기
-- 1. 평균 급여를 계산한다
 
 
-- 2. 평균 급여보다 급여를 적게 받는 사원
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
                        FROM EMPLOYEES);
                        
-- Neena와 같은 해에 입사한 사원의 아이디, 이름, 입사일 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') IN (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE FIRST_NAME = 'Steven');         -- 다중행 서브쿼리일 땐 IN으로 해야 안전, 가능하면 IN으로 할 것.
 
 
-- Sundita 직원과 같은 부서에 일하고, Sundita보다 급여를 많이 받는 사원 조회하기
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                   FROM EMPLOYEES
                                   WHERE FIRST_NAME = 'Sundita')
AND SALARY > (SELECT SALARY
                     FROM EMPLOYEES
                     WHERE FIRST_NAME = 'Sundita');
 
-- 급여를 가장 적게 받는 사원과 같은 해에 입사한 사원의 이름, 입사일, 급여를 조회하기
 
SELECT FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY'= (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                                  FROM EMPLOYEES
                                                  WHERE SALARY = (SELECT MIN(SALARY)
                                                                          FROM EMPLOYEES) );
                                                                          
-- 오라클 에러
-- 테이블 이름이 틀린 경우: TABLE OR VIEW DOES NOT EXISTS
-- SELECT *
-- FROM EMPLOYEE;
 
-- 컬럼의 이름이 틀린 경우: INVALID IDENTIFIER. 식별자가 잘못되었다네.
-- SELECT NAMED
-- FROM EMPLOYEES;
 
-- 콤마가 누락된 경우: FROM keyword not found where expected
-- SELECT FIRST_NAME LAST_NAME SALARY -- 두 개만 쓰면 AS 별칭으로 인식해서 실행은 됨
-- FROM EMPLOYEES; 
 
-- 콤마가 너무 많은 경우: missing expression 뒤에 하나 더 와야 하는데 안 왔네요?
-- SELECT FIRST_NAME, LAST_NAME, SALARY,
-- FROM EMPLOYEES;
 
-- FROM 키워드에 오타가 있는 경우: FROM keyword not found where expected
-- SELECT FIRST_NAME, LAST_NAME, SALARY
-- FORM EMPLOYEES;
 
-- SELECT 키워드에 오타가 있는 경우: invalid SQL statement
-- SELET FIRST_NAME, LAST_NAME, SALARY
-- FROM EMPLOYEES;
 
-- WHERE 키워드에 오타가 있는 경우: SQL command not properly ended
-- SELECT FIRST_NAME, LAST_NAME, SALARY
-- FROM EMPLOYEES
-- WHER DEPARTMENT_ID = 10;
 
-- 모든 사원의 급여 최고액/최저액/평균을 조회하기
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEES;
 
-- 직종별 사원수를 조회하기(직종아이디, 사원수)
SELECT E.JOB_ID, COUNT(*)
FROM EMPLOYEES E
WHERE E.JOB_ID IS NOT NULL
GROUP BY E.JOB_ID
ORDER BY E.JOB_ID;
 
-- 직종별 사원수를 조회했을 때, 사원수가 10명이 넘어가는 직종과 사원수를 조회하기
SELECT JOB_ID, COUNT(*)
FROM EMPLOYEES 
GROUP BY JOB_ID
HAVING COUNT(*> 10;
 
-- 관리자별 사원수를 조회하기(관리자 이름, 사원수)
SELECT manager.FIRST_NAME, COUNT(*)
FROM EMPLOYEES worker, EMPLOYEES manager
WHERE worker.MANAGER_ID = manager.EMPLOYEE_ID
GROUP BY MANAGER.FIRST_name;
 
-- 입사년도별 사원수를 조회하기(연도, 사원수)
SELECT TO_CHAR(HIRE_DATE, 'YYYY'), COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')
ORDER BY TO_CHAR(HIRE_DATE, 'YYYY');
 
-- 급여 등급별 사원수를 조회하기(급여등급, 사원수)
SELECT JG.GRA, COUNT(*)
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
GROUP BY JG.GRA
ORDER BY JG.GRA;
 
-- "Hermann"가 부서담당자로 지정된 부서에 소속된 사원의 아이디와 이름, 입사일을 조회하기
SELECT 직원.EMPLOYEE_ID, 직원.FIRST_NAME, 직원.HIRE_DATE
FROM EMPLOYEES 직원, EMPLOYEES 담당자
WHERE 직원.MANAGER_ID= 담당자.EMPLOYEE_ID
AND 담당자.FIRST_NAME = 'Hermann'-- 틀렸어 ㅜㅜ
 
-- 서브 쿼리로 구하기
-- 1. 사원 번호 구하기
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE FIRST_NAME = 'Hermann';
 
-- 2. 1이 관리자로 있는 부서 찾기
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE MANAGER_ID = 204;
 
-- 3. 2 부서에 근무하는 사원 구하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 70;
 
-- 종합
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                                  FROM DEPARTMENTS
                                  WHERE MANAGER_ID = (SELECT EMPLOYEE_ID
                                                                 FROM EMPLOYEES
                                                                 WHERE FIRST_NAME = 'Hermann'));
 
-- 조인으로 구하기
SELECT WORKER.EMPLOYEE_ID, WORKER.FIRST_NAME, WORKER.HIRE_DATE
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER, DEPARTMENTS DEPT
WHERE MANAGER.EMPLOYEE_ID = DEPT.MANAGER_ID
AND DEPT.DEPARTMENT_ID = WORKER.DEPARTMENT_ID
AND MANAGER.FIRST_NAME = 'Hermann';
 
-- 최고 급여가 $10000를 넘는 부서별 부서의 번호 및 평균 급여를 조회하기
SELECT DEPARTMENT_ID, TRUNC(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY) >= 10000
ORDER BY DEPARTMENT_ID;
 
-- 관리자별 번호 및 해당 관리자에 속한 사원의 최저 급여를 조회하기
SELECT MANAGER.EMPLOYEE_ID, MIN(WORKER.SALARY)
FROM EMPLOYEES WORKER, EMPLOYEES MANAGER
WHERE WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID
GROUP BY MANAGER.EMPLOYEE_ID
ORDER BY MANAGER.EMPLOYEE_ID;
 
-- 'Hermann'와 같은 급여등급에 속하는 사원들의 이름과 급여를 조회하기
 
SELECT A.FIRST_NAME, A.SALARY, B.GRA
FROM EMPLOYEES A, JOB_GRADES B
WHERE A.SALARY >= B.LOWEST_SALARY AND A.SALARY <= B.HIGHEST_SALARY
AND B.GRA = (SELECT Y.GRA
                  FROM EMPLOYEES X, JOB_GRADES Y
                  WHERE X.SALARY >= Y.LOWEST_SALARY AND X.SALARY <= Y.HIGHEST_SALARY
                  AND X.FIRST_NAME = 'Hermann');
-- 메인 쿼리와 서브 쿼리 별칭을 다르게 하라.
 
 
-- 엄청 어려운 문제. 인터넷에서 검색해보고 풀 것. 입사연도별 사원수 조회하기
-- 조회 예)
-- 2001 2002 2003 2004 2005 2006 2007 2008
-- 1     7      6      10    29    24    19   11
SELECT 
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2001'10)) "2001년"
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2002'10)) "2002년"
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2003'10)) "2003년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2004'10)) "2004년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2005'10)) "2005년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2006'10)) "2006년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2007'10)) "2007년",
         SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), '2008'10)) "2008년"
 
FROM EMPLOYEES;
cs

'Oracle Database SQL' 카테고리의 다른 글

190409  (0) 2019.06.07
190408  (0) 2019.06.07
190404  (0) 2019.06.07
190403  (0) 2019.06.07
190402  (0) 2019.06.07
-- 비등가조인 실습을 위한 테이블 생성
CREATE TABLE JOB_GRADES (
   GRA CHAR(1NOT NULL PRIMARY KEY,
   LOWEST_SALARY NUMBER(7),
   HIGHEST_SALARY NUMBER(7)
);
 
 
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('A'1000,    2999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('B'3000,    5999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('C'6000,    9999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('D'1000014999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('E'1500024999);
INSERT INTO JOB_GRADES (GRA, LOWEST_SALARY, HIGHEST_SALARY) VALUES('F'2500040000);
COMMIT;
 
-- 비등가 조인
-- = 연산자가 아닌 다른 연산자를 사용해서 조인 조건을 정의하는 조인
 
-- 사원의 이름, 사원의 급여, 급여 등급을 조회하기
-- 등호(=)를 사용하는 것이 아니라, 급여가 최소 급여와 최대 급여 사이에 해당하는 등급 관련 행이 조인되어야 함.
 
SELECT A.FIRST_NAME, A.SALARY, B.GRA
FROM EMPLOYEES A, JOB_GRADES B
WHERE A.SALARY >= B.LOWEST_SALARY
AND A.SALARY <= B.HIGHEST_SALARY
ORDER BY A.SALARY DESC;
 
-- 포괄조인(아우터조인:OUTER JOIN)
-- 포괄 조인을 사용하면 조인 조건을 만족하지 않는 행도 조회된다.
-- 포괄 조인 연산자는 +
 
-- 사원 이름, 부서 아이디, 부서 이름 조회하기
SELECT A.FIRST_NAME, A.DEPARTMENT_ID, B.DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID(+);
 
-- 부서 아이디, 부서 이름, 부서 담당자 아이디, 부서 담당자 이름, 부서 담당자 직종 아이디를 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, E.FIRST_NAME, E.JOB_ID
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.MANAGER_ID = E.EMPLOYEE_ID(+)
ORDER BY D.DEPARTMENT_ID;
 
-- 부서 아이디, 부서 이름, 부서 담당자 아이디, 부서 담당자 이름, 그 부서의 주소를 조회하기
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, A.MANAGER_ID, B.FIRST_NAME, C.CITY
FROM DEPARTMENTS A, EMPLOYEES B, LOCATIONS C
WHERE A.MANAGER_ID = B.EMPLOYEE_ID(+)
AND A.LOCATION_ID = C.LOCATION_ID
ORDER BY A.DEPARTMENT_ID;
 
-- 사원의 이름, 사원의 급여, 급여 등급, 소속 부서 이름을 조회하기(107명이 조회되어야 함)
SELECT A.FIRST_NAME, A.SALARY, B.GRA, C.DEPARTMENT_NAME
FROM EMPLOYEES A, JOB_GRADES B, DEPARTMENTS C
WHERE A.DEPARTMENT_ID = C.DEPARTMENT_ID(+)
AND A.SALARY >= B.LOWEST_SALARY
AND A.SALARY <= B.HIGHEST_SALARY
ORDER BY A.FIRST_NAME ASC;
 
-- 셀프 조인
-- 자기 자신의 테이블과 다시 조인을 해야하는 경우 사용된다.
-- 상하관계(재귀관계)가 있는 조직도, 메뉴 구성 정보, 카테고리 구성 정보 등이 셀프 조인을 통해서
-- 필요한 데이터를 조회할 수 있는 경우이다.
-- 같은 테이블을 서로 조인하기 때문에 테이블마다 적절한 역할을 부여해서 조인에 참여시켜야 한다.
 
-- 100번 부서에 근무하는 사원의 아이디, 사원의 이름, 그 사원의 상사 아이디, 그 사원의 상사 이름을 조회하기
SELECT A.EMPLOYEE_ID 직원_아이디, A.FIRST_NAME 직원_이름, B.EMPLOYEE_ID 상사_아이디, B.FIRST_NAME 상사_이름
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.MANAGER_ID = B.EMPLOYEE_ID AND A.DEPARTMENT_ID = 100;
 
-- 90번 부서에 근무하는 매니저에게 보고하는 직원들의 아이디, 이름, 급여, 부서명을 조회하기
SELECT 직원.EMPLOYEE_ID, 직원.FIRST_NAME, 직원.SALARY, 부서.DEPARTMENT_NAME
FROM EMPLOYEES 매니저, EMPLOYEES 직원, DEPARTMENTS 부서
WHERE 부서.DEPARTMENT_ID = 직원.DEPARTMENT_ID
AND 매니저.EMPLOYEE_ID = 직원.MANAGER_ID
AND 매니저.DEPARTMENT_ID = 90;
 
-- 다중행 함수(그룹함수)
-- 그룹함수는 행 집합(행 그룹)에 작용해서 그룹 당 하나의 결과를 생성한다.
-- 행 집합은 테이블 전체 행일 수도 있고,
-- 테이블에서 특정 컬럼의 값이 동일한 것끼리 그룹화된 행일수도 있다.
-- 그룹함수
-- COUNT(*), COUNT(컬럼명) 행의 개수, *일 때는 모든 행의 개수, 컬럼명을 지정하면 NULL이 아닌 행의 개수
--SUM(컬럼명) 합계를 계산, NULL은 무시한다.
--AVG(컬럼명) 평균을 계산, NULL은 무시
-- MIN(컬럼명) 최솟값을 계산, NULL 무시
-- MAX(컬럼명) 최댓값을 계산, NULL 무시
-- VARIANCE(컬럼명) 분산을 계산, NULL 무시
-- STDDEV(컬럼명) 
 
-- COUNT(*)
-- 직원의 수를 조회하기
SELECT COUNT(*)
FROM EMPLOYEES;
 
-- 커미션을 받는 직원의 수 조회하기
SELECT COUNT(*)
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
-- COUNT(컬럼명)은 해당 컬럼의 값이 NULL이 아닌 행의 개수만 센다.
SELECT COUNT(COMMISSION_PCT)
FROM EMPLOYEES;
 
-- 80번 부서에 소속된 사원들의 급여 총합을 조회하기
SELECT SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 커미션의 평균을 조회하기
SELECT AVG(COMMISSION_PCT)
FROM EMPLOYEES;
-- 또는
SELECT SUM(COMMISSION_PCT/35)
FROM EMPLOYEES;
-- NULL이 아닌 것만 갖고 계산이 된다.
 
-- 최저급여를 조회하기
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE SALARY = (SELECT MIN(SALARY)
                        FROM EMPLOYEES);
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션 및 소속부서명을 조회하기
SELECT E.FIRST_NAME, E.SALARY, E.COMMISSION_PCT, D.DEPARTMENT_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.COMMISSION_PCT IS NOT NULL
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+);
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션 및 소속부서명과 직종, 직종제목을 조회하기
SELECT E.FIRST_NAME
, E.SALARY
, E.COMMISSION_PCT
, D.DEPARTMENT_ID
, J.JOB_ID
, J.JOB_TITLE
FROM EMPLOYEES E
, DEPARTMENTS D
, JOBS J
WHERE E.COMMISSION_PCT IS NOT NULL
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
AND J.JOB_ID = E.JOB_ID;
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션 및 그 직원의 매니저의 이름을 조회하기
SELECT 직원.FIRST_NAME
, 직원.SALARY
, 직원.COMMISSION_PCT
, 매니저.FIRST_NAME
FROM EMPLOYEES 직원, EMPLOYEES 매니저
WHERE 직원.COMMISSION_PCT IS NOT NULL
AND 직원.MANAGER_ID = 매니저.EMPLOYEE_ID;
 
-- 커미션을 받는 직원들의 이름과 급여, 커미션, 소속부서명 및 그 직원의 매니저의 이름과 매니저의 
-- 소속부서명을 조회하기
SELECT 직원.FIRST_NAME
, 직원.SALARY
, 직원.SALARY
, 직원_부서.DEPARTMENT_NAME
, 매니저.FIRST_NAME
, 매니저_부서.DEPARTMENT_NAME
FROM EMPLOYEES 직원, EMPLOYEES 매니저, DEPARTMENTS 직원_부서, DEPARTMENTS 매니저_부서
WHERE 직원.MANAGER_ID = 매니저.EMPLOYEE_ID
AND 매니저_부서.DEPARTMENT_ID = 매니저.DEPARTMENT_ID
AND 직원_부서.DEPARTMENT_ID = 직원.DEPARTMENT_ID
AND 직원.COMMISSION_PCT IS NOT NULL;
 
-- 급여등급이 A등급인 직원 수를 조회하기
SELECT COUNT(*)
FROM EMPLOYEES E, JOB_GRADES J
WHERE J.LOWEST_SALARY <= E.SALARY
AND J.HIGHEST_SALARY >= E.SALARY
AND J.GRA = 'A';
 
-- 급여등급이 E등급인 상사에게 보고하는 직원의 이름과 급여를 조회하기
SELECT 직원.FIRST_NAME, 직원.SALARY
FROM EMPLOYEES 직원, EMPLOYEES 상사, JOB_GRADES 상사_등급
WHERE 상사_등급.LOWEST_SALARY <= 상사.SALARY
AND 상사_등급.HIGHEST_SALARY >= 상사.SALARY
AND 직원.MANAGER_ID = 상사.EMPLOYEE_ID
AND 상사_등급.GRA = 'E';
 
-- 급여등급이 E등급인 상사에게 보고하는 직원의 이름, 급여, 소속부서명을 조회하기
SELECT 직원.FIRST_NAME, 직원.SALARY, 직원_부서.DEPARTMENT_NAME
FROM EMPLOYEES 직원, EMPLOYEES 상사, JOB_GRADES 상사_등급, DEPARTMENTS 직원_부서
WHERE 직원.MANAGER_ID = 상사.EMPLOYEE_ID
AND 상사_등급.LOWEST_SALARY <= 상사.SALARY
AND 상사_등급.HIGHEST_SALARY >= 상사.SALARY
AND 직원.DEPARTMENT_ID = 직원_부서.DEPARTMENT_ID
AND 상사_등급.GRA = 'E';
 
-- 급여등급이 E등급인 상사에게 보고하는 직원의 이름, 급여, 그 직원의 급여등급을 조회하기
SELECT 직원.FIRST_NAME, 직원.SALARY, 직원_등급.GRA
FROM EMPLOYEES 직원, EMPLOYEES 상사, JOB_GRADES 상사_등급, JOB_GRADES 직원_등급
WHERE 직원.MANAGER_ID = 상사.EMPLOYEE_ID
AND 상사_등급.LOWEST_SALARY <= 상사.SALARY
AND 상사_등급.HIGHEST_SALARY >= 상사.SALARY
AND 직원_등급.LOWEST_SALARY <= 직원.SALARY
AND 직원_등급.HIGHEST_SALARY >= 직원.SALARY
AND 상사_등급.GRA = 'E';
 
-- 급여등급에 따라서 인상된 급여를 조회하기
-- 직원이름, 급여, 급여등급, 인상된 급여
-- 인상률: A등급 1%, B등급:3%, C등급: 5%, D등급: 7%, E등급: 10%, F등급 15% 인상시킨다.
SELECT E.FIRST_NAME, E.SALARY, JG.GRA, 
CASE WHEN JG.GRA = 'A' THEN TRUNC(1.01*E.SALARY)
        WHEN JG.GRA = 'B' THEN TRUNC(1.03*E.SALARY)
        WHEN JG.GRA = 'C' THEN TRUNC(1.05*E.SALARY)
        WHEN JG.GRA = 'D' THEN TRUNC(1.07*E.SALARY)
        WHEN JG.GRA = 'E' THEN TRUNC(1.10*E.SALARY)
        WHEN JG.GRA = 'F' THEN TRUNC(1.15*E.SALARY)
        END AS 급여인상분
FROM EMPLOYEES E, JOB_GRADES JG
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY;
 
-- DECODE(JG.GRA, 'A' TRUNC(~)도 가능
 
-- 사원의 아이디, 사원이름, 급여, 급여등급, 직종아이디, 직종제목, 
-- 소속부서 아이디, 부서명, 소재지주소, 소재지의 국가를 조회하기(6개 조인)
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, JG.GRA, E.JOB_ID, J.JOB_TITLE, E.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY, C.COUNTRY_ID
FROM EMPLOYEES E, DEPARTMENTS D, JOB_GRADES JG, JOBS J, LOCATIONS L, COUNTRIES C
WHERE E.SALARY >= JG.LOWEST_SALARY
AND E.SALARY <= JG.HIGHEST_SALARY
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
AND E.JOB_ID = J.JOB_ID
AND L.LOCATION_ID(+= D.LOCATION_ID
AND L.COUNTRY_ID = C.COUNTRY_ID(+);
cs

'Oracle Database SQL' 카테고리의 다른 글

190408  (0) 2019.06.07
190405  (0) 2019.06.07
190403  (0) 2019.06.07
190402  (0) 2019.06.07
090401  (0) 2019.06.07
-- 문자를 날짜로 변경하기
SELECT TO_DATE('2019/01/31''YYYY/MM/DD'),
          TO_DATE('2019-01-31''YYYY-MM-DD'),
          TO_DATE ('01/31/2019''MM/DD/YYYY')
FROM DUAL;
 
SELECT TRUNC(TO_DATE('2019/08/19''YYYY/MM/DD'- SYSDATE)
FROM DUAL;
 
-- 2008년 3월 1일 - 2008년 3월 15일 사이에 입사한 사원의 아이디, 이름, 입사일 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= TO_DATE('2008/03/01''YYYY/MM/DD')
AND HIRE_DATE <= TO_DATE('2008/03/15 23:59:59''YYYY/MM/DD HH24:MI:SS'); -- 23:59:59 안 쓰면 0:00:00시 까지라 3/15 아침 10시 입사자 데이터가 안 나옴
-- WHERE HIRE_DATE >= '2008/03/01'
-- AND HIRE_DATE < '2008/03/16';
 
-- 숫자 <-> 문자
-- 9: 소수점 이상 -> 숫자가 있으면 표현, 없으면 무시
--    소수점 이하 -> 숫자가 있으면 표현, 없으면 0을 표현
-- 0: 소수점 이상 -> 숫자가 있으면 표현, 없으면 0을 표현
--    소수점 이하 -> 숫자가 있으면 표현, 없으면 0을 표현
SELECT EMPLOYEE_ID, FIRST_NAME, TO_CHAR(SALARY, '99,999'-- 엑셀의 #과 같은 개념. 9 또는 0을 씀
        , TO_CHAR(COMMISSION_PCT, '9.99')
        , TO_CHAR(COMMISSION_PCT, '0.00')
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > '15000'-- 숫자로 묵시적 변환해줌 WHERE SALARY > TO_NUMBER('15,000, 99,999');
 
-- 기타 함수
-- NVL(컬럼, 값)
-- 지정된 컬럼의 값이 만약 NULL이라면 지정된 값으로 변환해서 제공
-- 지정된 컬럼의 값이 NULL이 아니면 원래 값을 제공
SELECT NVL(300), NVL(NULL,0-- 널에 대한 값 제공
FORM DUAL;
 
-- 사원의 이름, 급여, 커미션을 조회하기, 커미션이 없는 사원들은 0으로 표시하기
SELECT FIRST_NAME, SALARY, NVL(COMMISSION_PCT, 0)
FROM EMPLOYEES;
 
-- 사원의 이름, 급여, 커미션, 총급여(보너스가 포함된 급여)를 조회하기
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, SALARY * (1+NVL(COMMISSION_PCT, 0)) AS 총급여
FROM EMPLOYEES;
 
-- NVL2(컬럼, 값1, 값2) 값1, 2는 같은 타입이어야 한다.
--       지정된 컬럼의 값이 만약 NULL이라면 값2 대체해서 제공한다.
--       지정된 컬럼의 값이 NULL이 아니면 값1로 대체해서 제공한다.
 
-- 사원의 이름, 급여, 커미션을 받는지 여부를 "유", "무"로 조회하기
SELECT FIRST_NAME, SALARY, NVL2(COMMISSION_PCT, '유''무') AS 유무
FROM EMPLOYEES;
 
-- CASE ~ WHEN ~ END문
-- IF ELSE IF ELSE문의 역할을 수행한다.
-- CASE
--         WHEN 비교식 THEN 값1
--         WHEN 비교식 THEN 값2
--         WHEN 비교식 THEN 값3
--         ELSE 값4
-- END      
      
-- 부서번호가 50번인 부서는 'A팀', 60, 70번인 부서는 'B팀', 80번인 부서는 'C팀'으로 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID,
         CASE
            WHEN DEPARTMENT_ID = 50 THEN 'A팀'
            WHEN DEPARTMENT_ID IN (6070) THEN 'B팀'
            WHEN DEPARTMENT_ID = 80 THEN 'C팀'
            END AS TEAM
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50607080)
ORDER BY TEAM;
 
-- 사원들의 급여 수준을 상중하로 조회하기
-- 사원 이름, 급여, 급여 수준을 조회하고, 급여 수준 순으로 정렬하기
-- 급여 수준 상:12000달러 이상, 중: 5000달러 이상, 하:5000달러 미만 
SELECT FIRST_NAME, SALARY, 
          CASE WHEN SALARY >= 12000 THEN '상'
                 WHEN SALARY >= 5000 THEN '중'
                 ELSE '하'
          END AS 급여수준
FROM EMPLOYEES;
 
-- 사원 이름, 급여, 급여 인상분을 조회하고, 급여 인상분 순으로 정렬하기
-- 인상 비율:12000달러 이상 5%, 중:5000달러 이상 10%, 하:5000달러 미만 15%
SELECT FIRST_NAME, SALARY
        , CASE WHEN SALARY >= 12000 THEN 1.05*SALARY
                 WHEN SALARY >= 5000 THEN 1.1*SALARY
                 ELSE 1.15*SALARY
        END AS 급여인상분
FROM EMPLOYEES
ORDER BY 급여인상분 DESC;
 
-- DECODE 함수
-- IF ~ ELSE IF ~ ELSE를 간편하게 구현할 수 있는 함수
-- DECODE(컬럼 혹은 표현식, 비교값1, 결과값1
--                                  비교값2, 결과값2
--                                             결과값3) <-- ELSE와 같음
SELECT FIRST_NAME, DEPARTMENT_ID,
         DECODE(DEPARTMENT_ID, 50'A팀',
                                          60'B팀',
                                          70'B팀',
                                          80'C팀') AS TEAM
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50607080)
ORDER BY TEAM;
 
-- 테이블의 조인
-- 조회할 데이터가 2개 이상의 테이블을 사용해야지만 획득되는 경우
-- 조인 방법: FROM 절에 데이터 획득에 필요한 테이블을 나열하기만 하면 된다. 조인 조건을 제공해야 된다(N개의 테이블을 조인하면 N-1개의 조인 조건이 있어야 한다). 조인 조건은 조인된 행들 중에서 의미있게 연결된 행만 선택하게 한다.
 
-- 사원의 아이디, 사원의 이름, 부서 아이디, 부서 이름으로 조회하기
-- 단, 직종 최저 급여가 3000달러 미만인 정보만 조회하기
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.DEPARTMENT_ID, B.DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;
 
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.JOB_ID, A.SALARY, B.JOB_TITLE, B.MIN_SALARY, B.MAX_SALARY
FROM EMPLOYEES A, JOBS B  -- 조인
WHERE A.JOB_ID = B.JOB_ID AND B.MIN_SALARY < 3000;  -- 조인 조건
 
-- 사원의 아이디, 사원 이름, 부서 이름을 조회하기
-- 단, 100번 사원이 부서 담당자로 지정된 부서에 소속된 사원만 조회하기
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, B.DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID AND B.MANAGER_ID = 100;
 
-- 부서 아이디, 부서명, 그 부서가 위치한 지역의 도시명을 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D, LOCATIONS L
WHERE D.LOCATION_ID = L.LOCATION_ID;
 
-- 부서 아이디, 부서명, 부서 담당자 아이디, 부서 담당자(사원)의 이름, 부서 담당자(사원)의 연락처를 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, E.FIRST_NAME, E.PHONE_NUMBER
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.MANAGER_ID = E.EMPLOYEE_ID;
 
-- 급여를 15000달러 이상 받는 사원들의 아이디, 사원 이름, 급여, 소속 부서 아이디, 소속 부서명을 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.SALARY >= 15000;
 
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE SALARY >= 15000;
 
-- 직종 최저 급여가 10000달러 이상인 직종에 근무 중인 사원의 아이디, 사원의 이름, 직종 아이디, 직종 제목 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, J.JOB_TITLE
FROM JOBS J, EMPLOYEES E
WHERE J.JOB_ID = E.JOB_ID AND J.MIN_SALARY >= 10000;
 
-- 사원의 아이디, 사원 이름, 직종 아이디, 직종 제목, 소속 부서 아이디, 소속 부서명 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, J.JOB_TITLE, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, JOBS J, DEPARTMENTS D
WHERE E.JOB_ID=J.JOB_ID AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;
 
-- 등가, 비등가, 셀프, 아우터 조인
 
-- 모든 사원의 이름, 부서 번호, 부서 이름을 조회하기
SELECT EMPLOYEE_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME 
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
 
-- 소속 부서가 80번인 사원의 아이디, 직종 아이디, 직종 제목, 최소 급여, 최대 급여를 조회하기
SELECT E.EMPLOYEE_ID, E.JOB_ID, J.JOB_TITLE, J.MIN_SALARY, J.MAX_SALARY
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID = J.JOB_ID AND E.DEPARTMENT_ID = 80;
 
-- 커미션을 받는 사원의 이름, 부서 이름, 도시명을 조회하기
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND E.COMMISSION_PCT IS NOT NULL;
 
-- Toronto에서 근무하는 사원의 이름, 부서명을 조회하기
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND L.CITY LIKE 'Toronto';
 
-- 145번 사원이 담당자로 지정된 부서에서 근무하는 
-- 사원의 이름, 부서명, 직종 제목, 최소급여, 최대급여를 조회하기
SELECT E.FIRST_NAME, DEPARTMENT_NAME, J.JOB_TITLE, J.MIN_SALARY, J.MAX_SALARY
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND J.JOB_ID = E.JOB_ID AND D.MANAGER_ID = 145;
 
-- 101번 사원의 이름, 근무했던 직종 아이디, 근무했던 부서 아이디, 근무했던 부서명을 조회하기
SELECT E.FIRST_NAME, J.JOB_ID, J.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOB_HISTORY J
WHERE J.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.EMPLOYEE_ID = J.EMPLOYEE_ID AND E.EMPLOYEE_ID = 101;
 
-- 부서들 중에서 소재지가 Canada인 부서의 아이디, 부서명, 도시명을 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D, LOCATIONS L, COUNTRIES C
WHERE D.LOCATION_ID = L.LOCATION_ID AND L.COUNTRY_ID = C.COUNTRY_ID AND C.COUNTRY_NAME LIKE 'Canada';
 
-- 2007년에 'SA_MAN'으로 근무했던 사원의 아이디, 이름, 현재 근무 중인 직종 아이디, 급여를 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY
FROM EMPLOYEES E, JOB_HISTORY J
WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID 
AND TO_CHAR(J.START_DATE, 'YYYY'<= 2007 
AND TO_CHAR(J.END_DATE, 'YYYY'>= 2007
AND J.JOB_ID = 'SA_MAN';
 
-- 110번 부서에서 근무했던 경력이 있는 사원의 아이디, 이름, 부서명을 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, JOB_HISTORY J
WHERE J.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.EMPLOYEE_ID = J.EMPLOYEE_ID AND J.DEPARTMENT_ID = 110;
cs

'Oracle Database SQL' 카테고리의 다른 글

190408  (0) 2019.06.07
190405  (0) 2019.06.07
190404  (0) 2019.06.07
190402  (0) 2019.06.07
090401  (0) 2019.06.07
-- 오라클의 논리 연산자
-- AND
-- OR
-- NOT
 
-- 80번 부서에 소속된 사원 중에서 급여를 5000달러 이상 받는 사원의 이름과 급여, 직종, 부서 아이디를 조회하기
SELECT FIRST_NAME, SALARY, JOB_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
AND SALARY >= 5000;
 
-- 90번 부서에 소속된 사원 중에서 관리자 아이디가 100인 사원의 이름, 급여, 직종을 조회하기
SELECT FIRST_NAME, SALARY, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
AND MANAGER_ID = 100;
 
-- 커미션을 받는 사원 중에서 소속 부서가 지정되지 않는 사원의 이름, 급여, 커미션을 조회하기
SELECT FIRST_NAME, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL 
AND DEPARTMENT_ID IS NULL;
 
-- 커미션이 0.3 이상이고, 직종이 SA_MAN이며, 이름이 'A'로 시작하는 사원의 이름, 커미션, 직종을 조회
SELECT FIRST_NAME, COMMISSION_PCT, JOB_ID
FROM EMPLOYEES
WHERE COMMISSION_PCT >= 0.3
AND JOB_ID = 'SA_MAN'
AND FIRST_NAME LIKE 'A%';
 
-- 소속 부서가 80번 부서이고 급여를 5000달러 초과 10000 미만 받는 사원의 이름, 급여 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
AND SALARY BETWEEN 5000 AND 10000;
 
-- 소속 부서가 60이거나 90인 사원의 이름, 소속 부서를 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (6090);
 
-- 급여를 3000달러 이하로 받는 사원과 급여를 15000달러 이상으로 받는 사원의 이름, 급여 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
where SALARY <=3000
OR SALARY >= 15000;
 
-- 소속 부서가 50번 부서에 소속된 사원 중에서
-- 급여를 3000달러 이하로 받거나 급여를 15000달러 이상으로 받는 사원의 이름, 급여, 부서 아이디를 조회
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50 
AND (SALARY <= 3000 OR SALARY >= 15000);
 
-- 소속 부서가 50번이거나 80번인 사원의 이름, 소속 부서를 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (5080);
 
-- 소속 부서가 50번, 80번 외의 부서에서 근무하는 사원의 이름, 소속 부서를 조회하기
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID NOT IN (5080);
 
-- 조회된 데이터의 정렬
-- ORDER BY 절을 사용하면 조회된 데이터를 정렬할 수 있다.
-- ORDER BY 절은 SELECT문의 가장 끝에 위치한다.
-- SELECT
-- FROM 
-- [WHERE]
-- [ORDER BY 기준 컬럼명 [ASC <--기본값 | DESC]];
 
-- 사원 테이블에서 아이디, 이름, 급여를 조회하고, 급여를 기준으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES
ORDER BY SALARY DESC;
 
-- 80번 부서에 소속된 사원들의 이름, 입사일을 조회하고, 입사일을 기준으로 오름차순 정렬하기
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
ORDER BY HIRE_DATE ASC;
 
-- 오름차순: 날짜는 옛날 날짜부터, 문자는 알파벳, 가나다 순으로 표시, NULL값은 맨 마지막에 표시
 
-- 80번 부서에 소속된 사원 중에서 이름에 'e'를 포함하고 있고, 급여를 5000달러 미만으로 받는 사원들의
-- 아이디, 이름, 급여를 조회하고, 급여 순으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80 AND (FIRST_NAME LIKE '%e%' OR FIRST_NAME LIKE '%E%') AND SALARY < 10000
ORDER BY SALARY DESC;
 
-- 커미션을 받는 사원의 아이디, 이름, 급여, 커미션, 보너스(급여*커미션)을 조회하고
-- 보너스 순으로 오름차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*COMMISSION_PCT AS BONUS
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY 5 ASC;
 
-- 내부 연산 순서: FROM WHERE SELECT ORDER BY 순으로 하므로, WHERE은 별칭 불가, ORDER BY는 가능.
-- 두 가지 이상의 정렬 기준 적용 가능
 
-- 80번 부서 사원의 급여, 이름을 조회하기
-- 급여와 이름 순으로 오름차순 정렬하기(급여가 동일할 때는 이름 순으로 정렬하기)
SELECT SALARY, FIRST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
ORDER BY SALARY, FIRST_NAME;
 
-- SELECT
-- FROM
-- WHERE
-- GROUP BY ~
-- HAVING ~
-- ORDER BY ~ (순서 꼭 지켜야!)
 
-- 오라클의 내장함수: 값을 입력받아서 적절한 작업을 수행하고 결과값을 반환하는 것.
--                 ㄴ구분: 단일행 함수: 단일 행에 대해서만 연산을 수행하고, 행 당 하나의 결과를 반환한다. 단일행 함수는 여러 번 중첩해서 사용할 수 있다. SELECT, WHERE, ORDER BY 절에서 사용할 수 있다.
--                                      ㄴ종류: 문자/숫자/날짜/변환 함수 - 날짜 <->문자, 숫자<->문자로 변환/기타 함수.
--                           다중행 함수(그룹 함수): 여러 행(행 그룹)을 조작해서 행 그룹 당 하나의 결과를 반환한다. 한 번만 중첩 가능. 통계치(합계, 평균, 분산, 표준편차, 최댓값, 최솟값, 개수)를 계산한다.
 
-- 문자 함수
-- 대소문자 변환 함수
-- UPPER(컬럼), LOWER(컬럼)
SELECT FIRST_NAME, UPPER(FIRST_NAME), LOWER(LAST_NAME)
FROM EMPLOYEES;
 
-- 문자열 추출
-- SUBSTR(컬럼, m [, n]): m번 째부터 n번 째까지 문자를 반환한다.
SELECT FIRST_NAME, SUBSTR(FIRST_NAME, 2)
FROM EMPLOYEES;
 
-- 지정된 문자열의 등장 위치 찾기
-- INSTR(컬럼, '문자'): 지정된 문자의 위치를 반환
SELECT INSTR('801010-1121211''-')
FROM DUAL; -- 오라클의 빌트인 테이블, 1행 1열짜리 테이블. 특별한 테이블을 대상으로 수행하는 조회 작업이 아닐 때 사용된다.
 
-- 문자 채우기
-- LPAD(컬럼, 길이, '문자'), RPAD(컬럼, 길이, '문자')
-- 컬럼의 값이 지정된 길이보다 짧으면 부족한 길이만큼 문자를 채운다.
SELECT LPAD('ABC'10'*'), RPAD('ABC'10'*')
FROM DUAL;
 
-- 문자의 길이
-- LENGTH(컬럼)
SELECT FIRST_NAME, LENGTH(FIRST_NAME)
FROM EMPLOYEES;
 
-- 이름 4글자 이하인 사원의 이름과 글자수를 조회하기
SELECT FIRST_NAME, LENGTH(FIRST_NAME)
FROM EMPLOYEES
WHERE LENGTH(FIRST_NAME) <= 4;
 
-- 이름에 'C'나 'c'가 들어 있는 사원의 이름을 조회하기
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE LOWER(FIRST_NAME) LIKE '%c%';
 
-- 불필요한 공백 없애기
-- TRIM(컬럼): 불필요한 좌우 공백을 제거한다.
SELECT '            C B C              ', TRIM('             ABC        ') AS X
FROM DUAL;
 
-- 문자 바꾸기
-- REPLACE(문자, '찾을 문자', '변경할 문자')
SELECT REPLACE('자바 입문자를 위한 자바의 정석''자바''파이썬')
FROM DUAL;
 
-- 숫자 함수
-- 반올림한다
-- ROUND(숫자, 소수 자릿수) : 지정된 자리로 값을 반올림한다.
-- 버린다.
-- TRUNC(숫자, 소수 자릿수): 지정된 자리까지 남기고 값을 버린다.
-- 나머지를 반환한다.
-- MOD(숫자1, 숫자2): 숫자1을 숫자2로 나눈 나머지를 반환한다.
 
SELECT ROUND(3,2), ROUND(3,6)
FROM DUAL;
 
SELECT ROUND(3.15231), ROUND(3.15232)
FROM DUAL;
 
SELECT ROUND(10/32FROM DUAL;
 
SELECT ROUND(12340), ROUND(1234-1), ROUND(1234-2), ROUND(1234,-3FROM DUAL;
 
SELECT TRUNC(123.581), ROUND(123.581FROM DUAL;
 
SELECT FIRST_NAME, SALARY, TRUNC(SALARY, -3)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 급여 금액별 사원 수 계산하기
SELECT TRUNC(SALARY, -3), COUNT(*)
FROM EMPLOYEES
GROUP BY TRUNC(SALARY, -3)
ORDER BY 1;
 
-- 날짜 관련 함수
-- SYSDATE: 현재 날짜와 시간 정보를 반환한다. 반환값은 DATE타입의 값이다(중요!)
SELECT SYSDATE
FROM DUAL;
 
-- MONTHS_BETWEEN(날짜, 날짜): 두 날짜 사이의 개월 수 반환
-- ADD_MONTHS(날짜, 개월 수): 날짜에 개월 수를 더한 날짜를 반환
 
-- 사원 아이디, 이름, 입사일, 근무 개월 수 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS MONTHS
FROM EMPLOYEES;
 
-- 200개월 이상 근무한 사원의 사원 아이디, 이름, 입사일, 근무 개월 수 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS MONTHS
FROM EMPLOYEES
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) >= 200;
 
SELECT ADD_MONTHS(SYSDATE, 6)
FROM DUAL;
 
-- 날짜 연산
-- 날짜 + 일수: 지정된 날짜에서 더해진 숫자만큼 이후의 날짜를 반환한다.
-- 날짜 - 숫자: 지정된 날짜에서 빼진 숫자만큼 이전의 날짜를 반환한다.
-- 날짜 - 날짜: 두 날짜 사이의 일수를 반환한다.
-- 날짜 + 숫자/24: 지정된 날짜에 시간이 더해진 날짜를 반환한다.
-- !날짜 + 날짜는 없음
 
-- 최근 3일 전에 입사한 사원 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE > (SYSDATE -3);
 
-- 3일 후, 3일 전
SELECT SYSDATE + 3, SYSDATE -3
FROM DUAL;
 
-- 사원들의 이름, 입사일, 근무일수 구하기
SELECT FIRST_NAME, HIRE_DATE, TRUNC(SYSDATE - HIRE_DATE) DAYS
FROM EMPLOYEES;
 
SELECT SYSDATE, SYSDATE + 2/24
FROM DUAL;
 
-- 변환함수
SELECT TO_CHAR(SYSDATE, 'YYYY') 년, TO_CHAR(SYSDATE, 'MM') 월, TO_CHAR(SYSDATE, 'DD') 일,
          TO_CHAR(SYSDATE, 'HH24') 시, TO_CHAR(SYSDATE, 'MI') 분, TO_CHAR(SYSDATE, 'SS') 초,
          TO_CHAR(SYSDATE, 'AM') 오전오후, TO_CHAR(SYSDATE, 'DAY') 요일
FROM DUAL;
 
-- 입사연도 조회하기
SELECT FIRST_NAME, HIRE_DATE, TO_CHAR(HIRE_DATE, 'YYYY') 입사연도
FROM EMPLOYEES;
 
-- 입사일이 오늘과 동일한 날짜에 입사한 사람의 이름, 입사일 조회하기
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE (TO_CHAR(HIRE_DATE, 'MM-DD'= TO_CHAR(SYSDATE, 'MM-DD'));
 
-- 2008년에 입사한 사원의 아이디, 이름, 입사일 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY'= '2008';
 
-- 80번 부서에 소속된 사원들의 아이디, 이름, 입사일, 근무 개월 수, 근무 연수를 조회하기(소숫점 한 자리까지 표시하고 버린다).
SELECT EMPLOYEE_ID
        , FIRST_NAME
        , HIRE_DATE
        , TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS 근무개월수
        , TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 근무연수
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 급여가 12000달러를 초과하는 사원의 아이디, 이름, 급여를 조회하고, 이름순으로 오름차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 12000
ORDER BY FIRST_NAME ASC;
 
-- 100번 사원이 관리자로 지정된 사원 중에서 급여를 15000달러 이상 지급받는 사원의 아이디, 이름, 
-- 소속 부서 아이디, 급여를 조회하고, 급여순으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE MANAGER_ID = 100 AND SALARY >= 15000
ORDER BY SALARY DESC;
 
-- 20번 부서와 50번 부서에 소속된 사원 중에서 급여를 5000달러 이상 지급받는 사원의 아이디, 이름,
-- 소속 부서 아이디, 급여를 조회하고, 이름순으로 오름차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (2050) AND SALARY >= 5000
ORDER BY FIRST_NAME ASC;
 
-- 급여가 5000 ~ 12000 사이이고, 부서번호가 20번 또는 50인 사원의 이름과 급여를 조회하기
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 5000 AND SALARY < 12000 AND DEPARTMENT_ID IN (2050); -- BETWEEN은 포함됨(이상 이하 개념)
 
-- 2008년 상반기에 입사한 사원의 아이디, 이름, 입사일을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYYMM') BETWEEN 200801 AND 200806;
 
-- 커미션을 받는 사원들의 사원 아이디, 이름, 급여, 커미션, 보너스를 조회하기
-- 보너스는 소숫점 한짜리까지 반올림해서 표현하고, 보너스를 기준으로 내림차순 정렬하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT AS 커미션, ROUND(COMMISSION_PCT*SALARY, 1) AS 보너스
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY 보너스 DESC;
 
-- 80번부서에 근무하는 사원의 아이디, 이름, 급여, 15%인상된 급여를 조회하기
-- 인상된 급여는 소숫점 이하는 버린다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, TRUNC(SALARY*1.150) AS "인상 급여"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
-- 1월달, 2월달, 3월달에 입사한 사원의 아이디, 이름, 입사일을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'MM') IN (123);
 
-- 이름이 'A', 'M', 'J'로 시작하는 사원의 이름을 알파벳순으로 정렬해서 조회하기(문자함수 사용)
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE UPPER(FIRST_NAME) LIKE 'A%' 
OR UPPER(FIRST_NAME) LIKE 'M%'
OR UPPER(FIRST_NAME) LIKE 'J%'
ORDER BY FIRST_NAME ASC;
 
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE SUBSTR(FIRST_NAME, 11) IN ('A''M''J');
 
-- 사원의 이름과 급여를 '*'로 나타내는 SQL을 작성하시오, '*'하나는 1000달러를 나타낸다.
-- 결과 
-- King 24000 ********************
-- Abel 6000  ******
-- 응수 45000 **********************************************************
SELECT FIRST_NAME, SALARY, RPAD('*', SALARY/1000 ,'*') AS 별
FROM EMPLOYEES;
 
SELECT FIRST_NAME, SALARY, RPAD(SALARY/1000+LENGTH(SALARY) , LENGTH(SALARY/1000+LENGTH(SALARY)) ,'*')
FROM EMPLOYEES;
 
-- 이름에 'A'나'a'와 'C'나'c'를 모두 포함하고 있는 사원의 이름을 알파벳순으로 정렬해서 조회하기 
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE INSTR(LOWER(FIRST_NAME), 'a'> 0
AND INSTR(LOWER(FIRST_NAME), 'C'> 0;
 
--WHERE UPPER(FIRST_NAME) LIKE '%A%' 
-- OR UPPER(FIRST_NAME) LIKE '%C%'
-- ORDER BY FIRST_NAME ASC;
 
-- !문자도 크기 비교 가능
 
cs

'Oracle Database SQL' 카테고리의 다른 글

190408  (0) 2019.06.07
190405  (0) 2019.06.07
190404  (0) 2019.06.07
190403  (0) 2019.06.07
090401  (0) 2019.06.07
-- 테이블의 모든 데이터 조회
SELECT *
FROM 테이블명;
 
-- 모든 부서 정보 조회하기
SELECT *
FROM DEPARTMENTS;
 
-- 모든 국가 정보 조회하기
SELECT *
FROM COUNTRIES;
 
-- 모든 사원 정보 조회하기
-- 오라클의 키워드와 테이블 이름, 컬럼 이름은 대소문자를 구분하지 않는다.
SELECT *
FROM EMPLOYEES;
 
-- 모든 직종 정보 조회하기
SELECT *
FROM JOBS;
 
-- 모든 직종 변경 이력 정보 조회하기
SELECT *
FROM JOB_HISTORY;
 
-- 테이블의 특정 열(컬럼)의 데이터 조회하기
SELECT 컬럼명, 컬럼명, 컬럼명
FROM 테이블명;
 
-- 부서 정보 중에서 부서 번호와 부서 이름을 조회하기
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS;
 
-- 사원 정보 중에서 사원 아이디, 이름, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES;
 
-- 사원 정보 중에서 부서 아이디, 사원 아이디, 직종 아이디, 이름, 급여를 조회하기
SELECT DEPARTMENT_ID, EMPLOYEE_ID, JOB_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES;
 
-- SELECT절에서는 산술 연산자(+, -, *, /)의 사용이 가능하다.
-- 숫자 데이터 및 날짜 데이터에 대해서 사용 가능하다.
-- SELECT 컬럼명+컬럼명, 컬럼명 - 숫자, 컬럼명 * 숫자, 컬럼명/숫자, 컬럼명/컬럼명
-- from 테이블명
 
-- 사원 아이디, 이름, 급여, 연봉을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, SALARY*12
FROM EMPLOYEES;
 
-- 사원 아이디, 이름, 급여(주급), 시급(원으로 환산한 값:1달러 1130원)
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, (SALARY/40)*1130 SALARY_PER_HOUR
FROM EMPLOYEES;
 
-- 별칭 부여하기
-- SELECT 컬럼명 AS 별칭, 컬럼명 AS 별칭, 산술식 AS 별칭
-- FROM 테이블명
 
-- SELECT 컬럼명 별칭, 컬럼명 별칭, 산술식 "별칭" <-- 띄어쓰기 하고 싶을 때
-- FROM 테이블명
 
 
-- 오라클의 예약어는 컬럼명으로 쓸 수 없음.
SELECT EMPLOYEE_ID AS ID, FIRST_NAME AS NAME, HIRE_DATE AS "DATE"
FROM EMPLOYEES;
 
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY || '달러'
FROM EMPLOYEES;
 
-- 문자 이어 붙이기 연산자 ||
-- 문자(열) ''
-- 별칭 ""
 
-- 연결 연산자
-- 연결 연산자(||)는 컬럼과 컬럼, 산술식과 컬럼, 산술식과 산술식을 연결해서 문자를 생성한다.
 
 
 
-- 사원 아이디, 이름, 직종을 조회하기
-- (단, FIRST_NAME과 LAST_NAME을 연결해서 조회하고, 별칭을 FULL_NAME으로 붙이기)
SELECT EMPLOYEE_ID, FIRST_NAME || LAST_NAME AS FULL_NAME, JOB_ID
FROM EMPLOYEES;
 
SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, JOB_ID
FROM EMPLOYEES;
 
-- 사원들이 현재 근무하고 있는 직종을 조회하기(중복 제거)
SELECT DISTINCT EMPLOYEE_ID, JOB_ID -- 이 경우 둘 다 같은 게 있어야 중복 제거 됨.
FROM EMPLOYEES;
 
-- 선택되는 행을 제한하기
-- WHERE 절을 사용하면 조회되는(선택되는) 행을 제한할 수 있다(조건절).
-- WHERE 절은 FROM 절 다음에 온다.
-- (조건식 --> WHERE 컬럼 이름 비교 조건 값, WHERE 컬럼이름 비교조건 다른 컬럼)
-- 오라클의 비교 조건 
-- 같다 =
-- 같지 않다. != <>
 
 
-- 사원 정보에서 60번 부서에서 소속된 사원의 아이디, 이름, 직종을 조회하기
SELECT employee_id, first_name, job_id, department_id
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
 
-- 사원 정보에서 급여를 15000달러 이상 받는 사원의 아이디, 이름, 직종, 급여를 조회하기
 
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY >= 15000;
 
-- 사원 정보에서 사원 아이디가 200번인 사원의 아이디, 이름, 전화번호, 이메일, 소속부서 아이디 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE_NUMBER, EMAIL, DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 200;
 
-- ! 국가정보에서 아시아 지역에 위치하고 있는 나라의 국가코드, 국가명 조회하기
SELECT COUNTRY_ID, COUNTRY_NAME
FROM COUNTRIES
WHERE REGION_ID = 3;
 
-- WHERE 절에서 사용 가능한 기타 비교조건
-- WHERE 컬럼명 BETWEEN 값1 AND 값2
--      지정된 컬럼이 값1 이상 값2 이하인 행을 선택한다. 문자도?
-- WHERE 컬럼명 IN (값1, 값2, 값3)
--      지정된 컬럼의 값이 값1, 값2, 값3 중의 하나와 일치하는 행을 선택한다.
-- WHERE 컬럼명 LIKE '문자 패턴'
--      지정된 컬럼의 값이 문자 패턴과 유사한 행을 선택한다.
-- WHERE 컬럼명 IS (NOT) NULL
--      지정된 컬럼의 값이 NULL인(NULL이 아닌) 행을 선택한다.
--      컬럼의 값이 NULL인 행을 선택할 때 WHERE 컬럼명 = NULL과 같이 사용하지 않는다.
 
-- 급여가 10000 ~ 15000 달러를 받는 사원의 아이디, 이름, 직종, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY BETWEEN 10000 AND 15000;
 
-- 사원 아이디가 150 ~ 170에 속하는 사원의 아이디, 이름 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN 150 AND 170;
 
-- 소속 부서 아이디가 30, 60, 90인 사원의 아이디, 이름, 직종, 부서 아이디를 조회하시오.
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (306090);
 
-- 직종 아이디가 'ST_MAN'이거나 'ST_CLERK'인 직종의 아이디, 제목, 최소 급여, 최대 급여 조회하기
SELECT JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY
FROM JOBS
WHERE JOB_ID IN ('ST_MAN''ST_CLERK');
 
-- 오라클의 문자 패턴 형식 ( % 0 or 무한대로 이미지화 )
-- WHERE job_id LIKE 'ST%'     <-- JOB_ID가 'ST'로 시작하는 것 검색
-- WHERE job_id LIKE '%ST%'     <-- JOB_ID가 'ST'로 끝나는 것 검색
-- WHERE job_id LIKE '%ST%'     <-- JOB_ID가 'ST'를 포함하는 것 검색
 
-- WHERE NAME LIKE "이_" <-- 이름이 이*인 사람 검색(외자).
 
-- 이름에 'e'를 포함하고 있는 사원의 아이디, 이름을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '%e%';
 
-- 이름이 'A'로 시작하는 사원의 아이디, 이름을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'A%';
 
-- 이름이 'L'로 시작하고 이름이 4글자인 사원의 아이디, 이름을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'L___';
 
-- 부서 정보에서 담당자가 지정되어 있지 않은 부서의 아이디, 부서 이름 조회하기
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE MANAGER_ID IS NULL;
 
-- 사원 정보에서 커미션을 받는 사원의 아이디, 이름, 급여, 커미션을 조회하기
SELECT  EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
-- 사원 정보에서 소속 부서가 지정되어 있지 않는 사원의 아이디, 이름, 직종을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NULL;
 
-- 1. 모든 사원 정보 조회하기
SELECT *
FROM EMPLOYEES;
 
-- 2. 모든 부서 정보를 조회하기
SELECT *
FROM DEPARTMENTS;
 
-- 3. 사원 정보에서 사원 아이디가 100번인 사원의 아이디, 이름, 부서 아이디, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
 
-- 4. 사원 정보에서 소속 부서가 60번인 사원의 아이디, 이름, 부서 아이디, 직종을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
 
-- 5. 사원 정보에서 60번 부서 혹은 80번 부서에 소속된 사원의 아이디, 이름, 부서 아이디, 직종을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (6080);
 
-- 6. 직장 정보에서 최소 급여가 5000달러 이상 10000 이하인 직종의 아이디, 제목, 최소 급여, 최고 급여 조회하기
SELECT JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY
FROM JOBS
WHERE MIN_SALARY BETWEEN 5000 AND 10000;
 
-- 7. 사원 테이블에서 커미션을 받는 사원의 아이디, 이름, 급여, 연봉, 시급을 조회하기
SELECT  EMPLOYEE_ID, FIRST_NAME, SALARY AS 급여, SALARY*12 AS 연봉, SALARY/208 AS 시급
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
-- 8. 사원 테이블에서 이름이 'Kevin'인 사원의 아이디, 이름, 이메일, 전화번호를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, EMAIL, PHONE_NUMBER
FROM EMPLOYEES
WHERE FIRST_NAME = 'Kevin';
 
-- 9. 사원테이블에서 급여가 12000달러를 넘는 사원의 아이디, 이름, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 12000;
 
-- 10. 사원테이블에서 연봉이 100000달러를 초과하는 사원의 아이디, 이름, 급여, 연봉을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY*12 > 100000;
 
-- 11. 사원테이블에서 관리자가 없는 사원의 이름과 직종을 조회하기
SELECT FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE MANAGER_ID;
 
-- 12. 사원테이블에서 커미션을 받는 사원의 아이디, 이름, 급여, 커미션, 커미션 반영 급여를 조회하기 (커미션 반영 급여 = 급여 + 급여*커미션)
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT, (1+COMMISSION_PCT)*SALARY
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
 
-- 13. 이름의 3번째 문자가 'a'인 사원의 이름을 모두 조회하기
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME LIKE '__a%';
 
cs

'Oracle Database SQL' 카테고리의 다른 글

190408  (0) 2019.06.07
190405  (0) 2019.06.07
190404  (0) 2019.06.07
190403  (0) 2019.06.07
190402  (0) 2019.06.07
public class Book {
    private int no;
    private String name;
    private int price;

    public Book() {}

    public Book(int no, String name, int price) {
        this.no = no;
        this.name = name;
        this.price = price;
    }

    // Getter, Setter
    public int getNo() {return no;}
    public String getName() {return name;}
    public int getPrice() {return price;}
    public void setNo(int no) {this.no = no;}
    public void setName(String name) {this.name = name;}
    public void setPrice(int price) {this.price = price;}

}​

 

public class BookService {
    private Book[] database = new Book[10];
    private int position = 0;

    // 새로운 책 정보를 전달받아서 등록하는 기능
    public void addBook(Book book) {
        if (position < database.length) {
            database[position] = book;
            position++;
        }
    }

    // 모든 책 정보를 출력하는 기능
    public void printAllBooks() {
        for (Book book : database) {
            if (book != null) {
                System.out.println("도서번호 : " + book.getNo());
                System.out.println("도서제목 : " + book.getName());
                System.out.println("도서가격 : " + book.getPrice());
            }
        }
    }

    // 최저 가격과 최고 가격을 전달받아서 해당하는 책 정보를 출력하는 기능
    public void printBooksByPrice(int min, int max) {
        for (Book book : database) {
            if (book != null) {
                if (book.getPrice() >= min && book.getPrice() <= max) {
                    System.out.println("도서번호 : " + getNo());
                    System.out.println("도서제목 : " + getName());
                    System.out.println("도서가격 : " + getPrice());
                }
            }
        }
    }

    // 책 번호를 전달받아서 해당 책을 출력하는 기능
    public void printBookByNo(int no) {
        for (Book book : database) {
            if (book != null && book.getNo() == no) {
                System.out.println("도서번호 : " + getNo());
                System.out.println("도서제목 : " + getName());
                System.out.println("도서가격 : " + getPrice());
            }
        }
    }

    // 책 정보를 전달받아서 그 책 번호에 해당하는 책 정보를 변경하는 기능
    public void updateBook(Book book) {
        for (Book b : database) {
            if ( b != null && b.getNo() == book.getNo()) {
                b.setName(book.getName());
                b.setPrice(book.getPrice());
                // 또는
                // String name = book.getName();
                // int price = book.getPrice();
                // b.setNAme(name);
                // b.setPrice(price);
            }
        }
    }

    // 책 번호를 전달받아서 해당 책을 삭제하는 기능
    public void deleteBook(int no) {
        // 삭제할 책의 인덱스를 담을 변수
        int index = -1;        // 절대 일어나지 않을 번지

        for (int i=0; i<position; i++) {
            Book book = database[i];
            if (book.getNo() == no) {
                index = i;
                break;
            }
        }
        
        if (index != -1) {
            if (index < (position -1)) {
                database[index] = database[position - 1];
            }
            database[position-1] = null;
            position--;

        }
    }
}
​

 

import java.util.Scanner;

public class BookApp {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);

        BookService service = new BookService();

        for (;;) {
            System.out.println("1. 등록 2. 전체 출력 3. 가격별 출력 4. 번호별 출력 5. 수정 6. 삭제 0. 종료");

            /*
                1. 책 번호, 이름, 가격을 입력받아서 Book객체에 담고, Book 객체를 BookService 객체의 addBook메소드에 전달해서 저장
                2. BookService객체의 printAllBooks메소드를 실행해서 전체 책 정보 출력
                3. 최저 가격, 최고 가격을 입력받아서 BookService객체의 printBooksByPrice메소드에 전달해서 책 정보 출력하기
                4. 상품 번호를 입력받아서 BookService객체의 printBookByNo메소드에 전달해서 책 정보 출력하기
                5. 정보를 변경할 책 번호와 새 이름, 새 가격을 입력받아서 Book 객체에 담고, Book 객체를 BookService 객체의 updateBook 메소드에 전달해서 정보 변경하기
                6. 삭제할 책 번호를 입력받아서 BookService객체의 deleteBook 메소드에 전달해서 책 정보 삭제하기
            */
        }

    }
}
 

 

package kr.co.jhta.vo;

public class Professor {
    private String name;

    public Professor() {}
    public String getName() {
        return name;
    }

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

}
​

 

package kr.co.jhta.vo;

public class Student {
    private int no;
    private String name;

    public Student() {
    }
    
    public int getNo() {
        return no;
    }
    
    public String getName() {
    return name;
    }
    
    public void setNo(int no) {
        this.no = no;
    }

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

}
​

 

package kr.co.jhta.app; // 패키지 선언

//import kr.co.jhta.vo.Professor;
//import kr.co.jhta.vo.Student;  // 다른 패키지에 있는 클래스는 import 구문으로 해당 클래스의 전체 경로(패키지 경로 + 클래스)를 명시해야 한다.

import kr.co.jhta.vo.*;

public class StudentApp {
    public static void main(String[] args) {
        Student s1 = new Student();
        s1.setNo(20);
        s1.setName("홍길동");

        System.out.println("번호 : " + s1.getNo());
        System.out.println("이름 : " + s1.getName());
    
        Professor p = new Professor();
        p.setName("김교수");
        System.out.println("교수명 : " + p.getName());
    
    }
}
​
// 상품 번호, 상품 이름, 제조사, 상품 가격의 정보를 담는 객체를 위한 설계도
public class Product {

    // 비공개된 필드 정의
    private int no;
    private String name;
    private String maker;
    private int price;

    // 기본 생성자 정의
    public Product() {
    }

    // Getter/Setter 메소드 정의
    public int getNo() {
        return no;
    }

    public void setNo(int no) {
        this.no = no;
    }

    public String getName() {
        return name;
    }

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

    public String getMaker() {
        return maker;
    }

    public void setMaker(String maker) {
        this.maker = maker;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

}
​

 

public class ProductService {
    private Product[] database = new Product[10]; // 상품 객체 10개를 담는 배열
    private int position = 0; // 상품 객체를 담는 위치

    // 새로운 상품 정보를 배열에 저장하는 기능
    public void addProduct(Product product) {
        if (position < database.length) {
            database[position] = product;
            position++;
        }
    }

    // 모든 상품 정보를 화면에 출력하는 기능
    public void printAllProducts() {
        for (Product p : database) {
            printProduct(p);
        }
    }

    // 전달받은 순서(인덱스)에 해당하는 상품 정보를 제공하는 기능
    public void printProductByIndex(int index) {
    // Product product = null;
        Product p = database[index];
        printProduct(p);
    // return product;
    }

    // 전달받은 상품 번호에 해당하는 상품 정보를 출력하는 기능
    public void printProductByNo(int productNo) {
        for (Product p : database) {
            if (p != null && p.getNo() == productNo) {
                printProduct(p);
            }
        }
    }

    public void printProduct(Product p) {
        if (p != null) {
                System.out.println("상품번호 : " + p.getNo());
                System.out.println("상품이름 : " + p.getName());
                System.out.println("제 조 사 : " + p.getMaker());
                System.out.println("가    격 : " + p.getPrice());
                System.out.println();
        }
    }
}
​

 

import java.util.Scanner;

public class ProductSerivceApp {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        
        // 상품 정보 저장/출력/조회 기능을 제공하는 객체를 생성한다.
        // 아래의 각 선택메뉴에서는 ProductService 객체의 기능을 활용한다.
        ProductService service = new Scanner(System.in);

        for (;;) {
            System.out.println("1. 상품 등록 | 2. 전체 상품 출력 | 3. 순서별 상품 출력 | 4. 상품 번호별 출력 | 0. 종료");
            
            System.out.print("메뉴 선택> ");
            int menuNo = scanner.nextInt();

            if (menuNo == 1) {
                System.out.println("[새 상품 등록]");
                
                System.out.println("번호 입력> ");
                int no = scanner.nextInt();
                System.out.println("이름 입력> ");
                String name = scanner.next();
                System.out.println("제조사 입력> ");
                String maker = scanner.next();
                System.out.println("가격 입력> ");
                int price = scanner.nextInt();

                // 상품 정보를 담는 Product 객체 생성하고 setter를 활용해서 필드에 정보 저장
                Product product = new Product();
                product.setNo(no);
                product.setName(name);
                product.setMaker(maker);
                product.setPrice(price);
                // ProductService 객체가 제공하는 상품 정보를 배열에 저장하는 기능을 실행
                service.addProduct(product);
                // 1. 상품 정보를 입력 받는다.
                // 2. Product 객체를 생성한다.
                // 3. 입력받은 상품 정보를 Product에 담는다.
                // 4. 위에서 생성한 ProductService의 addProduct(Product product) 메소드를 실행해서
                //    상품 정보를 담고 있는 Product가 배열에 저장되게 한다.

            } else if (menuNo == 2) {
                System.out.println("[전체 상품 정보 출력]");
                service.printAllProducts();

            } else if (menuNo == 3) {
                System.out.println("[지정된 인덱스의 상품 정보 출력]");
                System.out.println("인덱스 입력> ");
                int index = scanner.nextInt();
                service.printProductByIndex(index);

            } else if (menuNo == 4) {
                System.out.println("[지정된 상품 번호의 상품 정보 출력]");
                System.out.println("상품 번호 입력> ");
                int no = scanner.nextInt();
                service.printProductByNo(no);

            } else if (menuNo == 0) {
                System.out.println("프로그램 종료");
                break;

            }
            System.out.println();
        }

    }

}
​

 

public class StaticService {
    int x;                // 설계도 로딩 후 -> 객체 생성 후 사용 가능한 필드
    static int y;        // 설계도 로딩 후 사용 가능한 필드
    static final int z = 10;

    public void m1() {
        System.out.println("x의 값: " + x);
        System.out.println("y의 값: " + StaticService.y);
    }

    public static void m2() {
        // static 메소드는 static 필드, static 메소드만 사용(접근) 가능하다.
        // System.out.println("x의 값: " + x);            // 컴파일 오류
        System.out.println("y의 값: " + StaticService.y);
    
    }
}
​

 

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

        // 정적 필드 정적 메소드는 객체 생성없이 즉시 사용가능하다.
        // 클래스명.필드명
        // 클래스명.메소드명(); 과 같은 형태로 사용한다.
        StaticService.y = 10;    // 정적 필드 사용하기
        StaticService.m2();        // 정적 메소드 사용하기

        StaticService.z = 20;
        System.out.println(StaticService.z);
        
    }
}
​

 

import java.util.Date;
import java.text.SimpleDateFormat;

public class StringUtils {
    
    // 홍길동 ---> 홍**
    public static final String NORMAL_DATE_FORMAT = "yyyy-MM-dd";
    public static final String DETAIL_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";

    public static String getNormalDate() {
        Date now = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat(NORMAL_DATE_FORMAT);
        return sdf.format(now);
    }

    public static String getDetailDate() {
        Date now = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat(StringUtils.DETAIL_DATE_FORMAT);
        return sdf.format(now);
    }

    public static String hideName(String username) {
        StringBuilder sb = new StringBuilder();
        sb.append(username.substring(0, 1));

        for (int i=0; i<username.length()-1; i++) {
            sb.append("*");
        }
        return sb.toString();
    }
}​

 

public class StringUtilsApp {
    public static void main(String[] args) {
        String name1 = StringUtils.hideName("홍길동");
        System.out.println(name1);

        String name2 = StringUtils.hideName("김구");
        System.out.println(name2);

        String date1 = StringUtils.getNormalDate();
        System.out.println(date1);

        String date2 = StringUtils.getDetailDate();
        System.out.println(date2);
    }
}
​

 

public class Subject {
    int no; // 과목 번호
    String name; // 과목명
    String professor; // 담당 교수

    public Subject() {

    }

    public Subject(int no, String name, String professor) {
    this.no = no;
    this.name = name;
    this.professor = professor;
    }

    // 과목번호(int타입의 값)을 제공하는 메소드
    public int getNo() {
    return no;
    }

    // 새 과목번호(int 타입의 값)을 전달받아서 그 객체의 필드값을 변경하는 메소드
    public void setNo(int no) {
    this.no = no;
    }

    // 과목명(String 타입의 값)을 제공하는 메소드
    public String getName() {
    return name;
    }

    // 새 과목명(String 타입의 값)을 전달받아서 그 객체의 필드값을 변경하는 메소드
    public void setName(String name) {
    this.name = name;
    }

    public String getProfessor() {
    return professor;
    }

    public void setProfessor(String professor) {
    this.professor = professor;
    }

    public void displayInfo() {
        System.out.println("과목번호 : "+ no);
        System.out.println("과목명 : " + name);
        System.out.println("담당교수 : " + professor);

    }
}
​

 

public class SubjectApp {
    public static void main(String[] args) {
    
    Subject s1 = new Subject(10, "전자기학", "홍길동");
    // getter 메소드 사용
    int no1 = s1.getNo();
    String name1 = s1.getName();
    String professor1 = s1.getProfessor();
    System.out.println("과목번호 -> " + no1);
    // setter 메소드 사용
    s1.setProfessor("김유신");
    // s1.displayInfo();
    
    Subject s2 = new Subject(20, "회로이론", "이순신");
    int no2 = s2.getNo();
    String name2 = s2.getName();
    String professor2 = s2.getProfessor();
    System.out.println("과목번호 -> " + no2);

    // s2.displayInfo();    

    }
}​
public class Account {
	int no;			// 계좌번호
	String owner;	// 예금주
	int balance;	// 잔액
	int pwd;		// 비밀번호


	Account(int newNo, String newOwner, int newPwd) {
		no = newNo;
		owner = newOwner;
		pwd = newPwd;
		balance = 0;
	}

	Account(int newNo, String newOwner, int newPwd, int newBalance) {
		no = newNo;
		owner = newOwner;
		pwd = newPwd;
		balance = newBalance;
	}

	// 출력기능
	void displayInfo() {
		System.out.println("계좌번호 : " + no);
		System.out.println("예 금 주 : " + owner);
		System.out.println("비밀번호 : " + pwd);
		System.out.println("잔    액 : " + balance);
		System.out.println();
	}

	// 입금 기능 : 입금액을 전달 받아서 잔액을 증가시키sms 기능
	void deposit(int amount) {
		if (amount > 0) {
			balance += amount;
			System.out.println("입금이 완료되었습니다.");
		} else {
			System.out.println("입금액이 올바르지 않습니다.");
		}
		System.out.println();
	}

	void withdraw(int amount) {
		if (amount < balance) {
			balance -= amount;
			System.out.println("출금이 완료되었습니다.");
		} else {
			System.out.println("출금액이 올바르지 않습니다.");
		}
		System.out.println();
	}

	// 비밀번호 변경 기능 : 이전 비밀번호와 새 비밀번호를 전달받아서 비밀번호를 변경하는 기능
	// 옛날 비밀번호와 새 비밀번호가 일치한다면 비밀번호 변경
	// 일치하지 않으면 화면에 에러메시지 출력

	void changePwd(int oldPwd, int newPwd) {
		if (oldPwd == pwd && newPwd != pwd) {
			pwd = newPwd;
			System.out.println("비밀번호가 정상적으로 변경되었습니다.");
		} else if (oldPwd != pwd) {
			System.out.println("비밀번호가 일치하지 않습니다.");
		} else if (newPwd == pwd) {
			System.out.println("기존의 비밀번호와 동일한 비밀번호를 입력하셨습니다.");
		}
		System.out.println();
	}
}​

 

public class AccountApp {
    public static void main(String[] args) {
    
    // 객체 생성 후 Account(int, String, int) 생성자 메소드 실행
    Account a1 = new Account(10, "홍길동", 1234);

    // 객체 생성 후 Account(int, String, int, int) 생성자 메소드 실행
    Account a2 = new Account(30, "이순신", 5678, 3000000);

        //a1.displayInfo();
        //a1.deposit(100000);
        //a1.displayInfo();
        a1.changePassword(1234, 1111);
        a1.displayInfo();
        //a2.displayInfo();
        //a2.deposit(100000);
        //a2.displayInfo();
        a2.changePassword(1212, 7777);
        a2.displayInfo();

    }
}
​

 

import java.util.Scanner;

public class AccountApp2 {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);

        Account[] accounts = new Account[2];

        for (;;) {
            System.out.println("1. 입력 | 2. 조회 | 3. 입금 | 4. 비번변경 | 0. 종료");
            System.out.println();

            System.out.println("메뉴 선택> ");
            int selectNo = scanner.nextInt();

            if (selectNo == 1) {
                for (int i=0; i<2; i++) {
                    // 번호, 소유주, 비번, 잔액을 입력받아서
                    // Account 객체를 생성하고(매개변수 4개 짜리 생성자 활용)
                    // accounts 배열에 담기

                    System.out.println("번호입력> ");
                    int no = scanner.nextInt();
                    System.out.println("소유주입력> ");
                    String owner = scanner.next();
                    System.out.println("비번입력> ");
                    int password = scanner.nextInt();
                    System.out.println("잔액입력> ");
                    int balance = scanner.nextInt();

                    Account a = new Account(no, owner, password, balance);
                    accounts[i] = a;

                }
            } else if (selectNo == 2) {
                for (Account acc : accounts) {
                    acc.displayInfo();
                }

            } else if (selectNo == 3) {
                for (Account acc : accounts) {
                    System.out.println("입금액 입력> ");
                    int a = scanner.nextInt();
                    acc.deposit(a);
                }

            } else if (selectNo == 4) {

            } else if (selectNo == 0) {
                System.out.println("프로그램 종료");
                break;
            }
            System.out.println();
        }
    }
}​

 

public class Book{
    int no;
    String title;
    String writer;
    int price;

    Book() {
    }
    
    Book(int no, String title, String writer, int price) {
        this.no = no;
        this.title = title;
        this.writer = writer;
        this.price = price;
    }

    void displayBookDetail() {
        System.out.println("번호 : " + no);
        System.out.println("제목 : " + title);
        System.out.println("저자 : " + writer);
        System.out.println("가격 : " + price);
    }
}​

 

public class BookApp {
    public static void main(String[] args) {
        Book b1 = new Book();
        b1.displayBookDetail();
        Book b2 = new Book(10, "이것이 자바다", "신용권", 30000);
        b2.displayBookDetail();
        Book b3 = new Book(30, "문제로 풀어보는 알고리즘", "황인욱", 28000);
        b3.displayBookDetail();
    }
}
​

 

public class Car {

    String color;

    // 생성자 정의
    Car() {
        System.out.println("기본 생성자가 실행됨");
        color = "녹턴그레이";
    }

    Car(String customColor) {
        System.out.println("컬러를 전달받는 생성자가 실행됨");
        color = customColor;
    }

    // 필드값을 제공하는 메소드
    String getColor(){
        return color;
    }
    
    // 전달받은 값으로 필드값을 변경하는 메소드
    void setColor(String c) {
    color = c;
    }
}
​

 

public class CarApp {
    public static void main(String[] args) {
        Car car1 = new Car();
        System.out.println(car1.color);            // 필드에 저장된 값 표현
        System.out.println(car1.getColor());    // 조회 메소드를 실행해서 획득한 값 표현
        
        Car car2 = new Car();
        System.out.println(car2.color);
        car2.color = "실버";
        car2.setColor("실버");
        System.out.println(car2.color);

        Car car3 = new Car("화이트");
        System.out.println(car3.color);

        Car car4 = new Car("미드나이트 블랙");
        System.out.println(car4.color);
    }
}
​

 

// 사원 정보를 담는 객체
public class Employee {
    // 사원 번호, 이름, 소속 부서명, 급여, 근무 연수, 전화번호
    // no      name  dept    salary workingYear tel
    
    // 비공개 필드 정의
    private int no;
    private String name;
    private String dept;
    private int salary;
    private int workingYear;
    private String tel;

    // 기본 생성자
    public Employee() {
    }
    
    public Employee(int no, String name, String dept, int salary, int workingYear, String tel) {
        this.no = no;
        this.name = name;
        this.dept = dept;
        this.salary = salary;
        this.workingYear = workingYear;
        this.tel = tel;
    }

    // getter 메소드
    public int getNo() {
        return no;
    }

    public String getName() {
        return name;
    }

    public String getDept() {
        return dept;
    }

    public int getSalary() {
        return salary;
    }

    public int getWorkingYear() {
        return workingYear;
    }

    public String getTel() {
        return tel;
    }

    // setter 메소드
    public void setNo(int no) {
        this.no = no;
    }

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

    public void setDept(String dept) {
        this.dept = dept;
    }

    public void setSalary(int salary) {
        this.salary = salary;
    }

    public void setWorkingYear(int workingYear) {
        this.workingYear = workingYear;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

}
​

 

public class EmployeeApp {
    public static void main(String[] args) {
        // 객체 생성 <-- 기본 생성자 활용
        Employee emp1 = new Employee();
        // setter를 활용해서 필드 초기화
        emp1.setNo(10);
        emp1.setName("홍길동");
        emp1.setDept("영업1팀");
        emp1.setSalary(3000000);
        emp1.setWorkingYear(4);
        emp1.setTel("010-1111-2222");

        // 매개 변수를 전달받는 생성자를 이용해서 필드 초기화
        Employee emp2 = new Employee(20, "김유신", "인사팀", 2500000, 3, "010-3333-4444");

        // Employee 객체 2개 담을 수 있는 배열 객체를 생성해서 employees에 담기
        Employee[] employees = new Employee[2];
        // employees가 참조하는 배열의 0번 째와 1번 째에 위에서 생성한 객체를 담기
        employees[0] = emp1;
        employees[1] = emp2;

        // 배열에 저장된 사원들의 사원 번호와 이름, 급여를 화면에 표시하기
        for (Employee emp : employees) {
            int 사번 = emp.getNo();
            String 이름 = emp.getName();
            int 급여 = emp.getSalary();

            System.out.println("사원 번호 : " + 사번);
            System.out.println("이     름 : " + 이름);
            System.out.println("급     여 : " + 급여);
            
        }
    }
}​

 

public class EmployeeService {
    
    // 새로운 사원 정보를 전달받아서 등록하는 기능
    Employee[] database = new Employee[3];

    public EmployeeService() {
        database[0] = new Employee(10, "김유신", "영업1팀", 250, 2, "010-1234-5678");
        database[1] = new Employee(20, "강감찬", "기술팀", 350, 2, "010-1111-1111");
        database[2] = new Employee(30, "이순신", "인사팀", 300, 2, "010-1234-2222");
    }
    
    // 삭제할 사원 번호를 전달받아서 그 번호에 해당하는 사원 정보를 삭제하는 기능
    
    // 모든 사원 정보를 출력하는 기능
    public void printAllEmployees() {
        for (Employee e : database) {
            int no = e.getNo();
            String name = e.getName();
            String dept = e.getDept();
            int salary = e.getSalary();
            int workingYear = e.getWorkingYear();
            String tel = e.getTel();
        
            System.out.println("사원번호 : " + no);
            System.out.println("사원이름 : " + name);
            System.out.println("소속부서: " + dept);
            System.out.println("실수령액 : " + salary);
            System.out.println("근무연수 : " + workingYear);
            System.out.println("전화번호 : " + tel);
         }
    }
    
    // 조회할 사원 번호를 전달받아서 그 번호에 해당하는 사원 정보를 제공하는 기능
    public Employee getEmployee(int empNo) {
        Employee emp = null;

        for (Employee e : database) {
            if (empNo == e.getNo()) {
                emp = e;
                break;
            }
        }
        
        return emp;
    }
}
​

 

public class EmployeeServiceApp {
    public static void main(String[] args) {
    
        EmployeeService service = new EmployeeService();

        // 모든 사원정보 출력해보기
        service.printAllEmployees();
    
        // 10번 사원 찾기
        Employee emp10 = service.getEmployee(10);
        Employee emp20 = service.getEmployee(20);
        System.out.println(emp10.getNo() + ", " +emp10.getName());
        System.out.println(emp20.getNo() + ", " + emp20.getName());
    }
}
​

+ Recent posts