오라클로 배우는 데이터베이스 개론과 실습 2판 - 연습문제 5장

본 게시글은 오라클로 배우는 데이터베이스 개론과 실습 2판 - 연습문제 5장 풀이입니다. 제가 이 책을 공부하면서 풀었던 풀이의 흔적이므로 정답이 아닐 수 있습니다. 혹여나 틀린 부분이 있어 오류를 댓글로 지적해주신다면 감사하겠습니다.

1. PL/SQL에 대한 설명 중 가장 거리가 먼 것은?

• 기존 SQL문과는 다른 것이다. → 표준 SQL을 기반으로 하며, 프로시저, 함수, 트리거 등을 포함한 확장된 기능을 제공함
• 기존 SQL 이상의 기능을 제공한다.
• 많은 시스템 함수를 제공한다.
• 기타 특별한 연산자들을 제공한다.

 

---

 

2. 저장 프로시저의 장점이 아닌 것은?

• QL 질의 전체를 전송하는 대신 매개변수만 전달하여 네트워크 트래픽을 증가시킨다. → 저장 프로시저는 네트워크 트래픽을 감소시킴. 프로시저는 데이터베이스 서버에 미리 컴파일되어 저장되므로, 클라이언트는 매개변수만 전송하고 프로시저를 실행하면 됨. 이는 반복되는 SQL 문을 전송하는 것보다 네트워크 부하를 줄임
• 프로시저 질의의 실행 계획을 미리 작성하고 있어서 빠른 처리가 가능하다.
• 프로시저에 대한 접근 권한을 설정함으로써 보안 관리가 유연하다.
• 자주 반복되는 SQL 문을 미리 작성하여 프로그래밍이 용이하다.

 

---

 

3. 다음 중 프로시저를 실행시키는 명령어는?

• EXEC → 저장된 프로시저를 실행시키는 데 사용됨
• RUN
• CREATE
• DROP

 

---

 

4. SQL에서 데이터베이스가 미리 정해놓은 조건을 만족하거나 어떤 동작이 수행되면 자동으로 동작하는 객체를 무엇이라 하는가?

• 트리거 → 데이터베이스 테이블에 대한 특정 작업(INSERT, UPDATE, DELETE 등)이 발생할 때 자동으로 실행되는 데이터베이스 객체
• 저장 프로시저
• 함수
• 뷰

 

---

 

5. 다음 중 트리거를 만들기 위한 명령어는?

• ALTER TRIGGER
• DROP TRIGGER
• CREATE TRIGGER
• INSERT TRIGGER

 

---

 

6. 다음 관계형 데이터베이스의 세 가지 기능적 요소에 대한 설명에서 ㄱ~ㄷ에 들어갈 용어를 순서대로 쓴 것 중 맞는 것은?

(ㄱ)은 SQL에서 삽입, 삭제, 갱신과 같은 데이터 변경문을 실행할 때 미리 명시된 조건을 만족하는 경우 특정한 동작을 자동으로 수행할 수 있도록 한다.
(ㄴ)은 데이터베이스 내에 존재하는 작업 순서가 정해진 수행 단위로서 DBMS에서 컴파일된 후 실행된다.
(ㄷ)은 데이터베이스에서 데이터를 신속하게 탐색할 수 있도록 만든 데이터 구조이다.

• 인덱스(index) / 트리거(trigger) / 주장(assertion)
• 주장 / 인덱스 / 저장 프로시저 (stored procedure)
• 주장 / 인덱스 / 트리거
• 트리거 / 저장 프로시저 / 인덱스

 

---

 

7. 다음과 같이 사원과 부서 테이블을 이용하는 회사가 직원의 급여를 인상할 때 오류를 방지하기 위해 <설명>과 같은 SQL99 표준에 따른 트리거를 정의하여 사용 중이다. 이와 같은 조건에서 직원들의 급여 인상을 위한 <보기>의 질의를 수행하였다. 급여 인상의 결과를 보기 위해 질의 “SELECT 사번, 이름, 급여 FROM 사원”을 수행한 결과는?

 

트리거 프로그램

CREATE TRIGGER Employment
AFTER UPDATE OF 급여 ON 사원
REFERENCING OLD ROW AS O, NEW ROW AS N
FOR EACH ROW
WHEN (N.급여 > O.급여 * 1.1)
UPDATE 사원
SET 급여 = O.급여 * 1.1
WHERE 사원.사번 = O.사번;

사원 테이블에 대해 UPDATE 연산이 수행된 후 활성화되며, 새로운 급여(N.급여)가 기존 급여(O.급여)의 110%를 초과하는 경우, 급여를 기존 급여의 110%로 조정함

 

데이터 조작문

UPDATE 사원 SET 급여 = 급여 * 1.2
WHERE 사번 IN (SELECT 부서장 FROM 부서);

UPDATE 사원 SET 급여 = 급여 * 1.05
WHERE 사번 != ALL (SELECT 부서장 FROM 부서);

UPDATE 사원 SET 급여 = 급여 * 1.1 WHERE 사번 = 100;

1. 부서장의 급여를 120% 인상
2. 부서장이 아닌 직원의 급여를 105% 인상
3. 사번이 100인 직원의 급여를 110% 인상

 

---

 

8. 다음 프로그램을 프로시저로 작성하고 실행하시오.

(1) InsertBook() 프로시저를 수정하여 고객을 새로 등록하는 InsertCustomer() 프로시저를 작성하시오.

CREATE OR REPLACE PROCEDURE InsertCustomer(custid IN NUMBER, name IN VARCHAR2, address IN VARCHAR2, phone IN VARCHAR2) AS
BEGIN
    INSERT INTO Customer (custid, name, address, phone)
    VALUES (custid, name, address, phone);
END;
/

 

(2) BookInsertOrUpdate() 프로시저를 수정하여 삽입 작업을 수행하는 프로시저를 작성하시오. 삽입하려는 도서와 동일한 도서가 있으면 삽입하려는 도서의 가격이 높을 때만 새로운 값으로 변경한다.

CREATE OR REPLACE PROCEDURE BookInsertOrUpdate(bookid IN NUMBER, bookname IN VARCHAR2, publisher IN VARCHAR2, price IN NUMBER) AS
    v_price NUMBER;
BEGIN
    SELECT price INTO v_price FROM Book WHERE bookid = bookid;

    IF SQL%NOTFOUND THEN
        INSERT INTO Book (bookid, bookname, publisher, price)
        VALUES (bookid, bookname, publisher, price);
    ELSIF v_price < price THEN
        UPDATE Book
        SET bookname = bookname, publisher = publisher, price = price
        WHERE bookid = bookid;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL;
END;
/

 

---

 

9. 다음 프로그램을 프로시저로 작성하고 실행하시오. 데이터베이스는 마당서점을 이용한다.

(1) 출판사가 ‘이상미디어’인 도서의 이름과 가격을 보여주는 프로시저를 작성하시오.

CREATE OR REPLACE PROCEDURE ShowIsangMediaBooks AS
BEGIN
    FOR rec IN (SELECT bookname, price FROM Book WHERE publisher = '이상미디어') LOOP
        DBMS_OUTPUT.PUT_LINE('도서 이름: ' || rec.bookname || ', 가격: ' || rec.price);
    END LOOP;
END;
/

 

(2) 출판사별로 출판사 이름과 도서의 판매 총액을 보이시오(판매 총액은 Orders 테이블에 있다).

CREATE OR REPLACE PROCEDURE ShowPublisherSales AS
BEGIN
    FOR rec IN (SELECT publisher, SUM(saleprice) AS total_sales
                FROM Book B JOIN Orders O ON B.bookid = O.bookid
                GROUP BY publisher) LOOP
        DBMS_OUTPUT.PUT_LINE('출판사: ' || rec.publisher || ', 판매 총액: ' || rec.total_sales);
    END LOOP;
END;
/

 

(3) 출판사별로 도서의 평균가보다 비싼 도서의 이름을 보이시오(예를 들어 A출판사 도서의 평균가가 20,000이라면 A 출판사 도서 중 20,000원 이상인 도서를 보이면 된다).

CREATE OR REPLACE PROCEDURE ShowExpensiveBooks AS
BEGIN
    FOR rec IN (SELECT B.bookname, B.publisher
                FROM Book B
                WHERE B.price > (SELECT AVG(price) FROM Book WHERE publisher = B.publisher)
                GROUP BY B.bookname, B.publisher) LOOP
        DBMS_OUTPUT.PUT_LINE('출판사: ' || rec.publisher || ', 도서 이름: ' || rec.bookname);
    END LOOP;
END;
/

 

(4) 고객별로 도서를 몇 권 구입했는지와 총 구매액을 보이시오.

CREATE OR REPLACE PROCEDURE ShowCustomerPurchases AS
BEGIN
    FOR rec IN (SELECT C.custid, C.name, COUNT(O.bookid) AS books_purchased, SUM(O.saleprice) AS total_spent
                FROM Customer C JOIN Orders O ON C.custid = O.custid
                GROUP BY C.custid, C.name) LOOP
        DBMS_OUTPUT.PUT_LINE('고객 번호: ' || rec.custid || ', 이름: ' || rec.name || ', 구매 권수: ' || rec.books_purchased || ', 총 구매액: ' || rec.total_spent);
    END LOOP;
END;
/

 

(5) 주문이 있는 고객의 이름과 주문 총액을 출력하고, 주문이 없는 고객은 이름만 출력하는 프로시저를 작성하시오.

CREATE OR REPLACE PROCEDURE ShowCustomersWithAndWithoutOrders AS
BEGIN
    FOR rec IN (SELECT C.name, NVL(SUM(O.saleprice), 0) AS total_orders
                FROM Customer C LEFT JOIN Orders O ON C.custid = O.custid
                GROUP BY C.name) LOOP
        DBMS_OUTPUT.PUT_LINE('고객 이름: ' || rec.name || ', 주문 총액: ' || rec.total_orders);
    END LOOP;
END;
/

 

---

 

10. 다음 PL/SQL 함수를 작성하시오. 데이터베이스는 마당서점을 이용한다.

(1) 고객의 주문 총액을 계산하여 20,000원 이상이면 ‘우수’, 20,000원 미만이면 ‘보통’을 반환하는 함수 Grade()를 작성하시오. Grade()를 호출하여 고객의 이름과 등급을 보이는 SQL 문도 작성하시오.

CREATE OR REPLACE FUNCTION Grade(custid IN NUMBER) RETURN VARCHAR2 IS
    total_orders NUMBER;
BEGIN
    SELECT SUM(saleprice) INTO total_orders FROM Orders WHERE custid = custid;

    IF total_orders >= 20000 THEN
        RETURN '우수';
    ELSE
        RETURN '보통';
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN '보통';
END;
/

SELECT C.name, Grade(C.custid) AS 등급
FROM Customer C;

 

(2) 고객의 주소를 이용하여 국내에 거주하면 ‘국내거주’, 해외에 거주하면 ‘국외거주’를 반환하는 함수 Domestic()을 작성하시오. Domestic()을 호출하여 고객의 이름과 국내/국외 거주 여부를 출력하는 SQL 문도 작성하시오.

CREATE OR REPLACE FUNCTION Domestic(address IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
    IF address LIKE '%한국%' OR address LIKE '%Korea%' THEN
        RETURN '국내거주';
    ELSE
        RETURN '국외거주';
    END IF;
END;
/

SELECT C.name, Grade(C.custid) AS 등급
FROM Customer C;

 

(3) (2)에서 작성한 Domestic()을 호출하여 국내거주 고객의 판매 총액과 국외거주 고객의 판매 총액을 출력하는 SQL 문을 작성하시오.

SELECT Domestic(C.address) AS 거주여부, SUM(O.saleprice) AS 판매총액
FROM Orders O JOIN Customer C ON O.custid = C.custid
GROUP BY Domestic(C.address);

 

---

 

11. [데이터 조회 프로그램] 데이터베이스 프로그래밍 방법 중 한 가지를 선택하여 다음 기능을 구현하는 프로그램을 작성하시오. 데이터베이스는 마당서점을 이용한다.

(tip) 자바 응용 혹은 웹 응용을 이용하여 작성할 수 있다.

(1) 출판사가 ‘이상미디어’인 도서이름과 가격을 보이는 프로그램을 작성하시오.

(2) 출판사별로 출판사 이름과 도서의 판매 총액을 보이는 프로그램을 작성하시오.

(3) 출판사별로 도서의 평균가보다 비싼 도서의 이름을 보이는 프로그램을 작성하시오(예를 들어 A 출판사 도서의 평균가가 20,000원이라면 A 출판사 도서 중 20,000원 이상인 도서를 보이면 된다).

import java.sql.*;

public class Main {
    // 데이터베이스 연결 정보
    static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe"
    static final String USER = "madang";
    static final String PASS = "madang";

    public static void main(String[] args) {
        try {
            Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

            showBooksByIsangMedia(conn);
            showPublisherSales(conn);
            showExpensiveBooksByPublisher(conn);

            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 이상미디어 출판사의 도서 이름과 가격을 보여주는 메소드
    private static void showBooksByIsangMedia(Connection conn) throws SQLException {
        String sql = "SELECT bookname, price FROM Book WHERE publisher = '이상미디어'";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                System.out.println("도서 이름: " + rs.getString("bookname") + ", 가격: " + rs.getInt("price"));
            }
        }
    }

    // 출판사별 판매 총액을 보여주는 메소드
    private static void showPublisherSales(Connection conn) throws SQLException {
        String sql = "SELECT B.publisher, SUM(O.saleprice) AS total_sales FROM Book B JOIN Orders O ON B.bookid = O.bookid GROUP BY B.publisher";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                System.out.println("출판사: " + rs.getString("publisher") + ", 판매 총액: " + rs.getInt("total_sales"));
            }
        }
    }

    // 출판사별 평균가보다 비싼 도서의 이름을 보여주는 메소드
    private static void showExpensiveBooksByPublisher(Connection conn) throws SQLException {
        String sql = "SELECT B.bookname FROM Book B WHERE B.price > (SELECT AVG(price) FROM Book WHERE publisher = B.publisher) GROUP BY B.bookname";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                System.out.println("도서 이름: " + rs.getString("bookname"));
            }
        }
    }
}

 

---

 

12. [데이터 변경 프로그램] 데이터베이스 프로그래밍 방법 중 한 가지를 선택하여 다음 기능을 구현하는 프로그램을 작성하시오. 데이터베이스는 마당서점을 이용한다.

(tip) 자바 응용 혹은 웹 응용을 이용하여 작성할 수 있다.

(1) 고객의 이름을 조회하고 주소를 변경하는 프로그램을 작성하시오.

(2) 도서 목록을 출력하고 새로운 도서 한 권을 도서이름, 출판사, 가격 등과 함께 입력하는 프로그램을 작성하시오.

import java.sql.*;
import java.util.Scanner;

public class Main {
    static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe"
    static final String USER = "madang";
    static final String PASS = "madang";

    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");

            Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

            updateCustomerAddress(conn);
            addNewBook(conn);

            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC Driver를 찾을 수 없습니다.");
            e.printStackTrace();
        }
    }

    // 고객의 이름을 조회하고 주소를 변경하는 메소드
    private static void updateCustomerAddress(Connection conn) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("고객의 이름을 입력하세요:");
        String name = scanner.nextLine();

        System.out.println("새 주소를 입력하세요:");
        String newAddress = scanner.nextLine();

        String sql = "UPDATE Customer SET address = ? WHERE name = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, newAddress);
            pstmt.setString(2, name);
            int rowsAffected = pstmt.executeUpdate();
            System.out.println(rowsAffected + "행이 업데이트되었습니다.");
        }
    }

    // 새로운 도서를 추가하는 메소드
    private static void addNewBook(Connection conn) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("도서 이름을 입력하세요:");
        String bookname = scanner.nextLine();

        System.out.println("출판사를 입력하세요:");
        String publisher = scanner.nextLine();

        System.out.println("가격을 입력하세요:");
        int price = Integer.parseInt(scanner.nextLine());

        String sql = "INSERT INTO Book (bookname, publisher, price) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, bookname);
            pstmt.setString(2, publisher);
            pstmt.setInt(3, price);
            int rowsAffected = pstmt.executeUpdate();
            System.out.println(rowsAffected + "행이 추가되었습니다.");
        }
    }
}

 

---

 

13. [프로시저 호출을 통한 데이터 변경 프로그램] 데이터베이스 프로그래밍 방법 중 한 가지를 선택하여 다음 기능을 구현하는 프로그램을 작성하시오. 데이터베이스는 마당서점을 이용한다.

(1) 6번의 (1)에서 작성한 InsertCustomert() 프로시저로 고객을 새로 등록하는 프로그램을 작성하시오.

(2) 6번의 (2)에서 작성한 수정된 BookinsertOrUpdate() 프로시저로 도서를 새로 등록하는 프로그램을 작성하시오.

import java.sql.*;
import java.util.Scanner;

public class Main {
    static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe"
    static final String USER = "madang";
    static final String PASS = "madang";

    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");

            Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

            insertNewCustomer(conn);
            insertOrUpdateBook(conn);

            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC Driver를 찾을 수 없습니다.");
            e.printStackTrace();
        }
    }

    // 고객을 새로 등록하는 프로시저 호출 메소드
    private static void insertNewCustomer(Connection conn) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("고객 번호를 입력하세요:");
        int custid = Integer.parseInt(scanner.nextLine());
        System.out.println("고객 이름을 입력하세요:");
        String name = scanner.nextLine();
        System.out.println("주소를 입력하세요:");
        String address = scanner.nextLine();
        System.out.println("전화번호를 입력하세요:");
        String phone = scanner.nextLine();

        CallableStatement stmt = conn.prepareCall("{call InsertCustomer(?, ?, ?, ?)}");
        stmt.setInt(1, custid);
        stmt.setString(2, name);
        stmt.setString(3, address);
        stmt.setString(4, phone);
        stmt.execute();
        stmt.close();
        System.out.println("새로운 고객이 등록되었습니다.");
    }

    // 도서를 새로 등록 또는 업데이트하는 프로시저 호출 메소드
    private static void insertOrUpdateBook(Connection conn) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("도서 번호를 입력하세요:");
        int bookid = Integer.parseInt(scanner.nextLine());
        System.out.println("도서 이름을 입력하세요:");
        String bookname = scanner.nextLine();
        System.out.println("출판사를 입력하세요:");
        String publisher = scanner.nextLine();
        System.out.println("가격을 입력하세요:");
        int price = Integer.parseInt(scanner.nextLine());

        CallableStatement stmt = conn.prepareCall("{call BookInsertOrUpdate(?, ?, ?, ?)}");
        stmt.setInt(1, bookid);
        stmt.setString(2, bookname);
        stmt.setString(3, publisher);
        stmt.setInt(4, price);
        stmt.execute();
        stmt.close();
        System.out.println("도서가 등록되었거나 업데이트되었습니다.");
    }
}