20250407DB_부속질의

과제(시험 공부할겸 같이하면 될 듯)

 

1. 다음과 같은 DB구축 요구사항에 대해 적절한 테이블 생성 쿼리를 작성해 제출하세요.

(각 테이블의 고유 식별자는 자동증가되는 BIGINT 타입으로 설계)

 * 병원용 DB구축 요구사항  

  - 환자 테이블
   > 이름, 주민번호, 전화번호, 주소

  - 의사 테이블
   >  이름, 직책, 담당과목

  - 환자 진료 기록 및 예약 테이블
   >  환자고유식별자, 의사고유식별자, 진료일시, 진료접수사유, 진료비, 진료진행내용

2. 다음과 같은 레코드 추가를 위한 적절한 INSERT 쿼리를 작성해 제출하세요.

  * 병원용 DB구축 완료 후 추가되어야할 레코드

  - 환자 정보
   > 김철수, 841111-1222222, 010-1234-5678, 서울 강남구
   > 손흥민, 920507-1222223, 010-4321-5678, 영국 토트넘
   > 김연아, 900905-2222222, 010-8888-7777, 경기도 부천

  - 의사 정보
   > 김영진, 과장, 이비인후과
   > 손연재, 원장, 외과

  - 진료 정보
   > 환자 김연아의식별자, 의사 김영진의식별자, 2024-04-01 11:00:00, 구강염, 5000, 알보칠 처방
   > 환자 김철수의식별자, 의사 김영진의식별자, 2024-04-03 12:00:00, 비염, 3000, 항히스타민 처방
   > 환자 손흥민의식별자, 의사 손연재의식별자, 2024-04-05 13:00:00, 다리골절, 150000, 다리 깁스 처리
   > 환자 손흥민의식별자, 의사 손연재의식별자, 2024-04-13 11:00:00, 골절, NULL, NULL

3. 다음과 같은 요구사항을 달성하기 위한 적절한 쿼리를 작성해 제출하세요.

  * 병원용 DB구축 완료 후 요구사항
  - 4월1일자 진료비 총액수를 알고 싶어요
  - 손흥민 환자의 모든 과거 진료 기록과 예약 내역을 보여주세요
  - 김영진 의사의 모든 과거 진료 기록을 보여주세요
  - 각 환자들의 상세정보와 상세 진료기록을 보여주세요

 

CREATE DATABASE task;

USE task;

-- 환자 테이블
CREATE TABLE patients (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    resident_id VARCHAR(20) NOT NULL,
    phone VARCHAR(20),
    address VARCHAR(255)
);

-- 의사 테이블
CREATE TABLE doctors (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50),
    department VARCHAR(100)
);

-- 진료 기록 및 예약 테이블
CREATE TABLE appointments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    patient_id BIGINT NOT NULL,
    doctor_id BIGINT NOT NULL,
    appointment_datetime DATETIME NOT NULL,
    reason TEXT,
    fee INT,
    content TEXT,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
);

-- 환자 정보 추가
INSERT INTO patients (name, resident_id, phone, address) VALUES
('김철수', '841111-1222222', '010-1234-5678', '서울 강남구'),
('손흥민', '920507-1222223', '010-4321-5678', '영국 토트넘'),
('김연아', '900905-2222222', '010-8888-7777', '경기도 부천');

-- 의사 정보 추가
INSERT INTO doctors (name, position, department) VALUES
('김영진', '과장', '이비인후과'),
('손연재', '원장', '외과');

-- 진료 정보 추가
-- 우선 환자, 의사의 ID를 조회해야 합니다. (아래는 서브쿼리 활용 방식)

INSERT INTO appointments (patient_id, doctor_id, appointment_datetime, reason, fee, content)
VALUES
(
    (SELECT id FROM patients WHERE name = '김연아'),
    (SELECT id FROM doctors WHERE name = '김영진'),
    '2024-04-01 11:00:00', '구강염', 5000, '알보칠 처방'
),
(
    (SELECT id FROM patients WHERE name = '김철수'),
    (SELECT id FROM doctors WHERE name = '김영진'),
    '2024-04-03 12:00:00', '비염', 3000, '항히스타민 처방'
),
(
    (SELECT id FROM patients WHERE name = '손흥민'),
    (SELECT id FROM doctors WHERE name = '손연재'),
    '2024-04-05 13:00:00', '다리골절', 150000, '다리 깁스 처리'
),
(
    (SELECT id FROM patients WHERE name = '손흥민'),
    (SELECT id FROM doctors WHERE name = '손연재'),
    '2024-04-13 11:00:00', '골절', NULL, NULL
);

-- 4월 1일자 진료비 총액
SELECT SUM(fee) AS total_fee
FROM appointments
WHERE DATE(appointment_datetime) = '2024-04-01';

-- 손흥민 환자의 모든 진료 및 예약 내역
SELECT a.*
FROM appointments a
JOIN patients p ON a.patient_id = p.id
WHERE p.name = '손흥민';

-- 김영진 의사의 모든 진료 기록
SELECT a.*
FROM appointments a
JOIN doctors d ON a.doctor_id = d.id
WHERE d.name = '김영진';

-- 모든 환자들의 상세정보 및 진료기록
SELECT patients.*, appointments.appointment_datetime, appointments.reason, doctors.name
FROM patients
LEFT JOIN appointments ON patients.id = appointments.patient_id
LEFT JOIN doctors ON appointments.doctor_id = doctors.id;

스칼라 부속 질의 - SELECT부속 질의

인라인 뷰 - FROM 부속 질의

중첩 질의 - WHERE 부분 부속 질의

*코드예시 암기 - 변수값만 바꿀 수 있게

SELECT ~

FROM ~

WHERE ~ IN (SELECT 

                        FROM 

                        WHERE )

---

※ in, not in연산자, all연산자

SELECT ~

FROM ~

WHERE ~ IN (SELECT 

                        FROM 

                        WHERE )

이 예제는 시험 아닌듯

5번 실습까지만 시험문제로 출제할 듯(5-2ppt는 중요도↓ )

---

1. 스칼라 부속 질의 (Scalar Subquery) – SELECT 부속 질의

예제 코드:

/*번쨰*/
ALTER TABLE orders
ADD product VARCHAR(100);
/*2번쨰*/
UPDATE orders
SET product = (SELECT product
FROM products
WHERE products.product_id = orders.product_id);

 

설명:

• 스칼라 부속 질의란, 서브쿼리(하위 질의)가 하나의 값(스칼라 값)을 반환하는 방식입니다. 이 예제에서는 orders 테이블의 product 컬럼을 products 테이블에서 가져오는 방식입니다.
• 첫 번째 쿼리 (ALTER TABLE orders ADD product VARCHAR(100))는 orders 테이블에 product라는 새 컬럼을 추가합니다.
• 두 번째 쿼리 (UPDATE orders SET product = (SELECT product FROM products WHERE products.product_id = orders.product_id))는 orders 테이블의 각 product_id에 해당하는 상품 이름을 products 테이블에서 찾아 orders 테이블의 product 컬럼에 업데이트합니다.
• 이때 SELECT 서브쿼리는 하나의 값(상품 이름)을 반환하므로 스칼라 부속 질의입니다.

추가 설명

 

'1. 스칼라 부속 질의 (Scalar Subquery) – SELECT 부속 질의' 에 관한 설명에서

'orders 테이블의 product 컬럼을 products 테이블에서 가져오는 방식'이 설명이 의미하는 바가

orders라는 새로운 테이블 이름을 지정하고 product라는 칼럼을 만든 다음

그 칼럼에 해당하는 값을 products테이블에서 가져와 채운다. 내가 맞게 이해한걸까?

또 두 번째 쿼리에서 UPDATE와,SET, SELECT, FROM, WHERE키워드가 무엇을 의미하는거야?

 

ChatGPT의 말:

네, 당신의 이해는 거의 맞습니다. 조금 더 정확하게 설명하자면:

• orders 테이블의 product 컬럼을 products 테이블에서 가져오는 방식은
  orders 테이블에 새로운 product라는 컬럼을 추가하고,
  그 컬럼에 products 테이블에서 해당하는 값을 찾아서 채우는 과정입니다.
  
  즉, orders 테이블에 이미 존재하는 product_id와 products 테이블의 product_id를 매칭시켜서,
  orders 테이블의 product 컬럼에 products 테이블에서 해당하는 product 값을 채워넣는 작업이죠.

이제, 두 번째 쿼리에서 각 키워드의 의미를 설명해 드릴게요.

두 번째 쿼리에서 키워드의 의미

sql

 

UPDATE orders /*UPDATE 수정할 테이블*/
SET product = (SELECT product  /*SET 수정할 칼럼 = (SELECT 값을 조회할 컬럼*/
               FROM products /*FROM 컬럼을 조회할 테이블*/
               WHERE products.product_id = orders.product_id); 
 /*WHERE = 조건설정
 products 테이블과 orders 테이블에서 product_id가 일치하는 데이터를 찾아
 그에 해당하는 product 값을 가져오겠다는 의미
 */

1. UPDATE:
   • UPDATE는 데이터를 수정하는 명령어입니다. 여기서는 orders 테이블의 데이터를 수정하려고 합니다.
   • 즉, orders 테이블에서 특정 행(row)을 수정하려는 의도를 나타냅니다.
2. SET:
   • SET은 수정할 컬럼을 지정하고, 그 컬럼에 어떤 값을 할당할 것인지 정의합니다.
   • 이 예제에서는 SET product = ...라고 쓰여 있으므로, orders 테이블의 product 컬럼의 값을 새로운 값으로 설정하겠다는 의미입니다.
3. SELECT:
   • SELECT는 데이터를 조회하는 명령어입니다. 여기서는 서브쿼리에서 products 테이블에서 product 값을 가져오기 위해 사용되고 있습니다.
   • SELECT product FROM products WHERE products.product_id = orders.product_id는 orders 테이블의 product_id 값과 일치하는 products 테이블의 product_id를 찾아 그에 해당하는 product 값을 반환합니다.
4. FROM:
   • FROM은 어디서 데이터를 가져올 것인지 지정하는 부분입니다.
   • 서브쿼리에서 FROM products는 products 테이블에서 데이터를 가져온다는 의미입니다.
5. WHERE:
   • WHERE는 조건을 설정하는 키워드입니다.
   • WHERE products.product_id = orders.product_id는 products 테이블과 orders 테이블에서 product_id가 일치하는 데이터를 찾아서 그에 해당하는 product 값을 가져오겠다는 의미입니다.

전체적인 흐름

1. UPDATE orders: orders 테이블을 수정합니다.
2. SET product = ...: orders 테이블의 product 컬럼에 값을 설정합니다.
3. SELECT product FROM products WHERE products.product_id = orders.product_id: orders 테이블의 product_id와 일치하는 product 값을 products 테이블에서 찾습니다.
4. WHERE 절은 두 테이블의 product_id가 일치하는 데이터를 찾는 조건입니다.

따라서, 이 쿼리는 orders 테이블에 있는 product_id와 일치하는 products 테이블의 product 값을 찾아서, orders 테이블의 product 컬럼에 그 값을 업데이트(수정)하는 것입니다.

요약

• UPDATE: 수정할 테이블을 지정합니다.
• SET: 수정할 컬럼과 새로운 값을 지정합니다.
• SELECT: 값을 가져오기 위한 서브쿼리입니다.
• FROM: 값을 가져올 테이블을 지정합니다.
• WHERE: 조건을 설정하여 어떤 데이터를 가져올지 결정합니다.

따라서, orders 테이블의 product 컬럼에 products 테이블에서 해당하는 product 값을 채우는 작업을 진행하는 것입니다.

---

2. 인라인 뷰 (Inline View) – FROM 부속 질의

예제 코드:

SELECT cs.name, SUM(od.sale_price) 'total'
FROM(SELECT customer_id, name
     FROM customer
     WHERE customer_id <= 2) cs, orders od
WHERE cs.customer_id = od.customer_id
GROUP BY cs.name;

설명:

• 인라인 뷰는 서브쿼리가 FROM 절에 포함되어, 마치 테이블처럼 사용되는 형태입니다. 이 예제에서는 customer_id <= 2인 고객 정보를 임시 테이블처럼 사용하여, 그 고객들의 주문 금액을 합산하는 쿼리입니다.
• FROM 절의 서브쿼리 (SELECT customer_id, name FROM customer WHERE customer_id <= 2)는 customer_id <= 2인 고객들의 customer_id와 name을 선택합니다. 이 서브쿼리는 cs라는 별칭을 가지고 마치 customer 테이블처럼 사용됩니다.
• 그 후, orders od 테이블과 결합하여, cs.customer_id = od.customer_id 조건으로 고객별 판매액을 계산합니다.
• 마지막으로, GROUP BY cs.name을 사용하여 고객 이름별로 판매액을 합산(SUM(od.sale_price))합니다.

3. 중첩 질의 (Nested Subquery) – WHERE 부속 질의

예제 코드:

SELECT order_id, customer_id, sale_price
FROM orders od
WHERE sale_price > (SELECT AVG(sale_price)
                     FROM orders so
                     WHERE od.customer_id = so.customer_id);

설명:

• 중첩 질의는 서브쿼리가 WHERE 절 안에 포함된 형태입니다. 이 예제에서는 각 고객의 평균 주문금액보다 큰 주문을 선택하는 쿼리입니다.
• 서브쿼리 (SELECT AVG(sale_price) FROM orders so WHERE od.customer_id = so.customer_id)는 각 고객별 평균 판매액을 계산합니다.
• WHERE 절에서 sale_price > (서브쿼리) 조건을 사용하여, 각 고객의 평균 주문금액보다 더 큰 주문을 찾아냅니다. 즉, 고객별로 평균 주문금액보다 더 비싼 주문 내역을 가져오는 방식입니다.

---

추가로 설명한 키워드들:

 

IN, NOT IN

• IN은 특정 값이 주어진 리스트나 서브쿼리에서 반환한 결과에 포함되는지를 확인합니다.

SELECT * FROM orders WHERE customer_id IN (1, 2, 3);

• NOT IN은 특정 값이 주어진 리스트나 서브쿼리에서 반환한 결과에 포함되지 않는지 확인합니다.

SELECT * FROM orders WHERE customer_id NOT IN (1, 2, 3);

ALL, SOME (ANY)

• ALL은 서브쿼리의 결과로 나온 모든 값을 비교할 때 사용합니다. 예를 들어, 모든 값이 특정 조건을 만족하는지 확인합니다.

SELECT * FROM orders WHERE sale_price > ALL (SELECT sale_price FROM orders WHERE customer_id = 1);

• SOME 또는 ANY는 서브쿼리의 결과 중 하나라도 조건을 만족하면 참을 반환합니다

SELECT * FROM orders WHERE sale_price > SOME (SELECT sale_price FROM orders WHERE customer_id = 1);

EXISTS, NOT EXISTS

• EXISTS는 서브쿼리에서 하나라도 행이 반환되면 참을 반환합니다. 예를 들어, 고객이 주문을 했는지를 확인할 때 사용합니다.

SELECT * FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id);

• NOT EXISTS는 서브쿼리에서 행이 반환되지 않으면 참을 반환합니다. 예를 들어, 주문이 없는 고객을 찾을 때 사용합니다.

SELECT * FROM customers WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = custome

---

사용자정의 함수

판매된 도서에 대한 이익을 계산하는 함수

가격이 30,000원 이상이면 10%, 30,000 미만이면 5%의 이익을 계산을 해라

CREATE FUNCTION fnc_interest(price INTEGER)
RETURNS INT
DETERMINISTIC /*관습적으로 붙인다, 어려워서 비중요*/

BEGIN
DECLARE my_interest INTEGER;
IF price >= 30000 THEN SET my_interest = price * 0.1;
ELSE SET my_interest = price * 0.05;
END IF;
RETURN my_interest;
END;

 

VIEW

VIEW에는 독립적인 INDEX설정이 어려움(딱히 필요가 없음)

 

VIEW지울 떄 DROP키워드

VIEW수정 REPLAC키워드