모델링 & 쿼리 실습
예제 1: 대학교 관리 시스템
-- Departments 테이블 생성
-- 대학의 각 학과 정보를 저장
-- Departments 테이블은 대학 내의 각 학과 또는 부서를 나타냅니다.
-- 이 테이블에는 학과의 고유 식별자, 학과 이름, 학과가 위치한 건물,
-- 그리고 학과의 예산과 같은 정보가 포함됩니다.
-- 학과는 대학의 기본적인 구성 단위로, 다양한 전공 과정과 연구 활동을 관리합니다.
-- 예를 들어, "컴퓨터 과학과"나 "생물학과" 등이 이에 해당됩니다.
CREATE TABLE Departments (
-- 학과의 고유 ID, 자동 증가
-- 학과 이름, 필수 입력
-- 학과 건물 위치, 선택적 입력
-- 학과 예산, 필수 입력, 소수 두 자리까지
);
-- Professors 테이블 생성
-- 대학의 교수 정보를 저장
-- Professors 테이블은 대학의 교수님들에 대한 정보를 저장합니다.
-- 각 교수는 고유 ID, 이름, 직급(예: 조교수, 부교수, 정교수), 그리고 사무실 위치 정보를 가지고 있습니다.
-- 또한, 교수가 소속된 학과 정보도 저장되어야 합니다.
CREATE TABLE Professors (
-- 교수의 고유 ID, 자동 증가
-- 교수 이름, 필수 입력
-- 교수 직급, 선택적 입력
-- 교수 사무실 번호, 선택적 입력
-- 소속 학과 ID
);
-- Courses 테이블 생성
-- Courses 테이블은 대학에서 제공하는 각각의 강의 과목을 나타냅니다.
-- 이 테이블은 강의의 고유한 특성을 저장하는 데 사용됩니다.
-- 예를 들어, 강의의 제목, 강의가 부여하는 학점 수, 그리고 이 강의가 어떤 학과에 속해 있는지 등의
-- 정보가 포함됩니다. 한 강의가 여러 학기에 걸쳐 반복적으로 개설될 수 있지만,
-- 강의의 기본적인 내용과 구조는 일반적으로 같습니다. 예를 들면:
-- ex) "데이터베이스 시스템"
-- ex) "일반화학"
-- ex) "마이크로경제학"
-- 이 테이블은 강의의 정의와 관련된 기본적인 정보를 담고 있으며,
-- 실제 강의가 언제 어디서 이루어지는지의 세부 사항은 포함하지 않습니다.
CREATE TABLE Courses (
-- 강의 고유 ID, 자동 증가
-- 강의 제목, 필수 입력
-- 강의 학점, 필수 입력
-- 개설 학과 ID
);
-- Sections 테이블 생성
-- 강의 섹션(분반) 정보를 저장
-- Sections 테이블은 특정 강의(Courses 테이블에 정의된)가 구체적으로 언제, 어느 장소에서,
-- 누가 가르치는지에 대한 정보를 관리합니다. 이는 같은 강의도 다른 시간, 다른 교수,
-- 다른 장소에서 여러 번 제공될 수 있다는 사실을 반영합니다.
-- 각 '섹션'은 동일 강의의 구체적인 인스턴스를 의미하며, 학기별로 각기 다른 교수가 담당할 수 있고,
-- 강의 시간이나 장소도 달라질 수 있습니다.
-- 예를 들어, "데이터베이스 시스템" 강의는 봄 학기에는 A 교수가 월요일 오전에 가르치고,
-- 가을 학기에는 B 교수가 금요일 오후에 가르칠 수 있습니다.
CREATE TABLE Sections (
-- 섹션 고유 ID, 자동 증가
-- 강의 ID
-- 개설 학기, 필수 입력
-- 개설 연도, 필수 입력
-- 담당 교수 ID
);
-- Students 테이블 생성
-- 대학의 학생 정보를 저장
-- Students 테이블은 대학에 재학 중인 학생들의 정보를 관리합니다.
-- 이 테이블은 학생의 고유 ID, 이름, 생년월일, 그리고 성별을 포함합니다.
-- 학생 데이터는 학생 관리, 학적 관리, 강의 등록 및 성적 처리 등의 용도로 사용됩니다.
-- 학생 정보는 대학 운영의 가장 기본이 되는 데이터 중 하나입니다.
CREATE TABLE Students (
-- 학생 고유 ID, 자동 증가
-- 학생 이름, 필수 입력
-- 생년월일, 선택적 입력
-- 성별, 선택적 입력
);
-- Enrollments 테이블 생성
-- 학생의 강의 등록 정보를 저장
-- Enrollments 테이블은 학생들이 각 학기에 등록하는 강의 섹션의 정보를 저장합니다.
-- 이 테이블은 학생 ID와 섹션 ID를 외래 키로 사용하여,
-- 어떤 학생이 어떤 강의에 등록했는지를 나타냅니다.
-- 또한, 해당 강의에서 학생이 받은 성적도 저장할 수 있습니다.
-- 이 테이블을 통해 학생들의 수강 신청 및 성적 관리가 이루어집니다.
CREATE TABLE Enrollments (
-- 등록 고유 ID, 자동 증가
-- 학생 ID
-- 등록 섹션 ID
-- 성적, 선택적 입력
);
-- Departments 테이블 생성
-- 대학의 각 학과 정보를 저장
-- Departments 테이블은 대학 내의 각 학과 또는 부서를 나타냅니다.
-- 이 테이블에는 학과의 고유 식별자, 학과 이름, 학과가 위치한 건물,
-- 그리고 학과의 예산과 같은 정보가 포함됩니다.
-- 학과는 대학의 기본적인 구성 단위로, 다양한 전공 과정과 연구 활동을 관리합니다.
-- 예를 들어, "컴퓨터 과학과"나 "생물학과" 등이 이에 해당됩니다.
CREATE TABLE Departments (
-- 학과의 고유 ID, 자동 증가
department_id INT AUTO_INCREMENT PRIMARY KEY,
-- 학과 이름, 필수 입력
name VARCHAR(100) NOT NULL,
-- 학과 건물 위치, 선택적 입력
building VARCHAR(100),
-- 학과 예산, 필수 입력, 소수 두 자리까지
budget DECIMAL(10, 2) NOT NULL
);
-- Professors 테이블 생성
-- 대학의 교수 정보를 저장
-- Professors 테이블은 대학의 교수님들에 대한 정보를 저장합니다.
-- 각 교수는 고유 ID, 이름, 직급(예: 조교수, 부교수, 정교수), 그리고 사무실 위치 정보를 가지고 있습니다.
-- 또한, 교수가 소속된 학과 정보도 저장되어야 합니다.
CREATE TABLE Professors (
-- 교수의 고유 ID, 자동 증가
professor_id INT AUTO_INCREMENT PRIMARY KEY,
-- 교수 이름, 필수 입력
name VARCHAR(100) NOT NULL,
-- 교수 직급, 선택적 입력
title VARCHAR(100),
-- 교수 사무실 번호, 선택적 입력
office_number VARCHAR(50),
-- 소속 학과 ID
department_id INT,
FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);
-- Courses 테이블 생성
-- Courses 테이블은 대학에서 제공하는 각각의 강의 과목을 나타냅니다.
-- 이 테이블은 강의의 고유한 특성을 저장하는 데 사용됩니다.
-- 예를 들어, 강의의 제목, 강의가 부여하는 학점 수, 그리고 이 강의가 어떤 학과에 속해 있는지 등의
-- 정보가 포함됩니다. 한 강의가 여러 학기에 걸쳐 반복적으로 개설될 수 있지만,
-- 강의의 기본적인 내용과 구조는 일반적으로 같습니다. 예를 들면:
-- ex) "데이터베이스 시스템"
-- ex) "일반화학"
-- ex) "마이크로경제학"
-- 이 테이블은 강의의 정의와 관련된 기본적인 정보를 담고 있으며,
-- 실제 강의가 언제 어디서 이루어지는지의 세부 사항은 포함하지 않습니다.
CREATE TABLE Courses (
-- 강의 고유 ID, 자동 증가
course_id INT AUTO_INCREMENT PRIMARY KEY,
-- 강의 제목, 필수 입력
title VARCHAR(100) NOT NULL,
-- 강의 학점, 필수 입력
credits INT NOT NULL,
-- 개설 학과 ID
department_id INT,
FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);
-- Sections 테이블 생성
-- 강의 섹션(분반) 정보를 저장
-- Sections 테이블은 특정 강의(Courses 테이블에 정의된)가 구체적으로 언제, 어느 장소에서,
-- 누가 가르치는지에 대한 정보를 관리합니다. 이는 같은 강의도 다른 시간, 다른 교수,
-- 다른 장소에서 여러 번 제공될 수 있다는 사실을 반영합니다.
-- 각 '섹션'은 동일 강의의 구체적인 인스턴스를 의미하며, 학기별로 각기 다른 교수가 담당할 수 있고,
-- 강의 시간이나 장소도 달라질 수 있습니다.
-- 예를 들어, "데이터베이스 시스템" 강의는 봄 학기에는 A 교수가 월요일 오전에 가르치고,
-- 가을 학기에는 B 교수가 금요일 오후에 가르칠 수 있습니다.
CREATE TABLE Sections (
-- 섹션 고유 ID, 자동 증가
section_id INT AUTO_INCREMENT PRIMARY KEY,
-- 강의 ID
course_id INT,
FOREIGN KEY (course_id) REFERENCES Courses(course_id),
-- 개설 학기, 필수 입력
semester VARCHAR(50) NOT NULL,
-- 개설 연도, 필수 입력
year INT NOT NULL,
-- 담당 교수 ID
professor_id INT,
FOREIGN KEY (professor_id) REFERENCES Professors(professor_id)
);
-- Students 테이블 생성
-- 대학의 학생 정보를 저장
-- Students 테이블은 대학에 재학 중인 학생들의 정보를 관리합니다.
-- 이 테이블은 학생의 고유 ID, 이름, 생년월일, 그리고 성별을 포함합니다.
-- 학생 데이터는 학생 관리, 학적 관리, 강의 등록 및 성적 처리 등의 용도로 사용됩니다.
-- 학생 정보는 대학 운영의 가장 기본이 되는 데이터 중 하나입니다.
CREATE TABLE Students (
-- 학생 고유 ID, 자동 증가
student_id INT AUTO_INCREMENT PRIMARY KEY,
-- 학생 이름, 필수 입력
name VARCHAR(100) NOT NULL,
-- 생년월일, 선택적 입력
date_of_birth DATE,
-- 성별, 선택적 입력
gender ENUM('M', 'F', 'Other')
);
-- Enrollments 테이블 생성
-- 학생의 강의 등록 정보를 저장
-- Enrollments 테이블은 학생들이 각 학기에 등록하는 강의 섹션의 정보를 저장합니다.
-- 이 테이블은 학생 ID와 섹션 ID를 외래 키로 사용하여,
-- 어떤 학생이 어떤 강의에 등록했는지를 나타냅니다.
-- 또한, 해당 강의에서 학생이 받은 성적도 저장할 수 있습니다.
-- 이 테이블을 통해 학생들의 수강 신청 및 성적 관리가 이루어집니다.
CREATE TABLE Enrollments (
-- 등록 고유 ID, 자동 증가
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
-- 학생 ID
student_id INT,
FOREIGN KEY (student_id) REFERENCES Students(student_id),
-- 등록 섹션 ID
section_id INT,
FOREIGN KEY (section_id) REFERENCES Sections(section_id),
-- 성적, 선택적 입력
grade CHAR(2)
);
더미 데이터 추가
-- Departments 데이터 수정
DELETE FROM Departments;
INSERT INTO Departments (name, building, budget) VALUES
('Computer Science', 'Engineering Building', 150000.00),
('Biology', 'Science Center', 120000.00),
('Economics', 'Business Hall', 130000.00),
('Psychology', 'Humanities Building', 110000.00),
('Mathematics', 'Science Center', 140000.00);
-- Professors 데이터 수정
DELETE FROM Professors;
INSERT INTO Professors (name, title, office_number, department_id) VALUES
('John Smith', 'Professor', 'E201', 1),
('Lisa Ray', 'Assistant Professor', 'S301', 2),
('James Lee', 'Associate Professor', 'B401', 3),
('Susan Choi', 'Professor', 'H501', 4),
('Michael Brown', 'Associate Professor', 'S302', 5),
('Anna White', 'Professor', 'S310', 1),
('Tom Green', 'Assistant Professor', 'E210', 1),
('Linda Gold', 'Associate Professor', 'B420', 3),
('Robert Black', 'Professor', 'H520', 4),
('Charles Grey', 'Assistant Professor', 'S330', 2);
-- Courses 데이터 수정
DELETE FROM Courses;
INSERT INTO Courses (title, credits, department_id) VALUES
('Introduction to Programming', 3, 1),
('Data Structures', 4, 1),
('Machine Learning', 3, 1),
('Genetics', 4, 2),
('Microbiology', 3, 2),
('Macroeconomics', 3, 3),
('Behavioral Psychology', 4, 4),
('Developmental Psychology', 3, 4),
('Linear Algebra', 4, 5),
('Statistics', 3, 5);
-- Sections 데이터 수정
DELETE FROM Sections;
INSERT INTO Sections (course_id, semester, year, professor_id) VALUES
(1, 'Fall', 2024, 1),
(2, 'Spring', 2024, 6),
(3, 'Fall', 2024, 7),
(4, 'Spring', 2024, 2),
(5, 'Fall', 2024, 10),
(6, 'Spring', 2024, 3),
(7, 'Fall', 2024, 4),
(8, 'Spring', 2024, 9),
(9, 'Fall', 2024, 5),
(10, 'Spring', 2024, 8);
-- Students 데이터 수정
DELETE FROM Students;
INSERT INTO Students (name, date_of_birth, gender) VALUES
('Alice Johnson', '1999-04-23', 'F'),
('Bob Lee', '2000-11-12', 'M'),
('Charlie Kim', '1998-07-19', 'Other'),
('Diane Park', '1997-05-25', 'F'),
('Ethan Cho', '2001-08-30', 'M'),
('Fiona Yoon', '1999-12-14', 'F'),
('George Han', '1998-03-11', 'M'),
('Hannah Shin', '2000-07-22', 'F'),
('Ian Jeong', '2001-02-02', 'M'),
('Julia Kang', '1997-09-18', 'F');
-- Enrollments 데이터 수정
DELETE FROM Enrollments;
INSERT INTO Enrollments (student_id, section_id, grade) VALUES
(1, 1, 'A'),
(2, 2, 'B'),
(3, 3, 'A'),
(4, 4, 'B'),
(5, 5, 'A'),
(6, 6, 'B'),
(7, 7, 'A'),
(8, 8, 'B'),
(9, 9, 'A'),
(10, 10, 'B');
초급
1. 문제: 모든 학과의 이름을 조회하세요.
SELECT d.name
FROM Departments d;
2. 문제: 모든 교수의 이름과 직급을 조회하세요.
SELECT p.name, p.title
FROM Professors p;
3. 문제: 'Science Center' 건물에 소속된 학과의 이름을 조회하세요.
SELECT d.name
FROM Departments d
WHERE d.building = 'Science Center';
4. 문제: 모든 학과의 이름을 조회하세요.
SELECT s.name, s.date_of_birth
FROM Students s;
중급 (조인 포함)
1. 문제: 각 학과에 소속된 교수의 이름을 조회하세요.
SELECT d.name ,p.name
FROM Professors p
JOIN Departments d
ON p.department_id = d.department_id;
2. 문제: 'Computer Science' 학과에서 개설된 강의의 제목을 조회하세요.
SELECT c.title
FROM Courses c
JOIN Departments d
ON c.department_id = d.department_id
WHERE d.name = 'Computer Science';
3. 문제: 모든 강의의 제목과 해당 강의를 개설한 학과의 이름을 조회하세요.
SELECT d.name, c.title
FROM Courses c
JOIN Departments d
ON c.department_id = d.department_id;
4. 문제: 2024년 봄학기에 개설된 모든 강의의 섹션 정보(강의 제목, 학기, 연도)를 조회하세요.
SELECT c.title , s.semester, s.year
FROM Sections s
JOIN Courses c
ON c.course_id = s.section_id
WHERE s.semester = 'Spring' AND s.year = 2024;
5. 문제: 각 학과의 평균 예산을 조회하세요.
SELECT d.name, AVG(d.budget)
FROM Departments d
GROUP BY d.name;
6. 문제: 각 학생이 등록한 강의의 수를 조회하세요.
SELECT s.name, COUNT(e.enrollment_id)
FROM Enrollments e
LEFT JOIN Students s
ON s.student_id = e.enrollment_id
GROUP BY s.name;
고급 (복잡한 조인 및 서브쿼리)
1. 문제: 'John Smith' 교수가 가르치는 강의의 제목을 조회하세요.
SELECT c.title
FROM Courses c
JOIN Sections s
ON s.course_id = c.course_id
JOIN Professors p
ON s.professor_id = p.department_id
WHERE p.name = 'John Smith';
2. 문제: 성적이 'A'인 학생의 이름과 강의 제목을 조회하세요.
SELECT s.name, c.title
FROM Students s
JOIN Enrollments e
ON s.student_id = e.enrollment_id
JOIN Sections se
ON e.section_id = se.section_id
JOIN Courses c
ON se.course_id = c.course_id
WHERE e.grade = 'A';
3. 문제: 각 교수가 담당하는 강의의 수를 조회하세요.
SELECT p.name, count(s.section_id)
FROM Professors p
LEFT JOIN Sections s
ON s.professor_id = p.professor_id
GROUP BY p.name;
4. 문제: 각 학과별로 평균 학점이 가장 높은 강의를 조회하세요.
SELECT d.name, c.title, c.credits
FROM Departments d
JOIN Courses c
ON d.department_id = c.department_id
WHERE c.credits IN (
SELECT MAX(c2.credits)
FROM Courses c2
WHERE c2.department_id = c.department_id
GROUP BY c2.department_id
);
예제 2: 병원 관리 시스템
-- Departments 테이블 생성
-- 병원의 각 부서 정보를 저장합니다.
-- 각 부서는 고유 식별자와 부서 이름을 가지고 있습니다.
CREATE TABLE Departments (
);
-- Doctors 테이블 생성
-- 병원의 의사 정보를 저장합니다.
-- 각 의사는 고유 식별자, 이름, 전문 분야, 그리고 소속 부서 ID를 가지고 있습니다.
CREATE TABLE Doctors (
);
-- Patients 테이블 생성
-- 병원의 환자 정보를 저장합니다.
-- 각 환자는 고유 식별자, 이름, 생년월일, 성별을 가지고 있습니다.
CREATE TABLE Patients (
);
-- Appointments 테이블 생성
-- 환자와 의사 사이의 진료 예약 정보를 저장합니다.
-- 각 예약은 고유 식별자, 환자 ID, 의사 ID, 예약 날짜, 예약 상태를 가지고 있습니다.
CREATE TABLE Appointments (
);
-- Prescriptions 테이블 생성
-- 환자의 진료 시 의사에 의해 처방된 약물 정보를 저장합니다.
-- 각 처방은 고유 식별자, 예약 ID, 약물 이름, 용량을 가지고 있습니다.
CREATE TABLE Prescriptions (
);
-- Departments 테이블 생성
-- 병원의 각 부서 정보를 저장합니다.
-- 각 부서는 고유 식별자와 부서 이름을 가지고 있습니다.
CREATE TABLE Departments (
department_id INT AUTO_INCREMENT PRIMARY KEY, -- 부서의 고유 ID, 자동 증가
name VARCHAR(100) NOT NULL -- 부서 이름, 필수 입력
);
-- Doctors 테이블 생성
-- 병원의 의사 정보를 저장합니다.
-- 각 의사는 고유 식별자, 이름, 전문 분야, 그리고 소속 부서 ID를 가지고 있습니다.
CREATE TABLE Doctors (
doctor_id INT AUTO_INCREMENT PRIMARY KEY, -- 의사의 고유 ID, 자동 증가
name VARCHAR(100) NOT NULL, -- 의사 이름, 필수 입력
specialty VARCHAR(100), -- 의사의 전문 분야, 선택적 입력
department_id INT, -- 소속 부서 ID, 외래 키
FOREIGN KEY (department_id) REFERENCES Departments(department_id) -- Departments 테이블의 department_id 참조
);
-- Patients 테이블 생성
-- 병원의 환자 정보를 저장합니다.
-- 각 환자는 고유 식별자, 이름, 생년월일, 성별을 가지고 있습니다.
CREATE TABLE Patients (
patient_id INT AUTO_INCREMENT PRIMARY KEY, -- 환자의 고유 ID, 자동 증가
name VARCHAR(100) NOT NULL, -- 환자 이름, 필수 입력
date_of_birth DATE, -- 환자의 생년월일, 선택적 입력
gender ENUM('M', 'F', 'Other') -- 환자 성별, 선택적 입력
);
-- Appointments 테이블 생성
-- 환자와 의사 사이의 진료 예약 정보를 저장합니다.
-- 각 예약은 고유 식별자, 환자 ID, 의사 ID, 예약 날짜, 예약 상태를 가지고 있습니다.
CREATE TABLE Appointments (
appointment_id INT AUTO_INCREMENT PRIMARY KEY, -- 예약의 고유 ID, 자동 증가
patient_id INT NOT NULL, -- 환자 ID, 외래 키
doctor_id INT NOT NULL, -- 의사 ID, 외래 키
appointment_date DATE NOT NULL, -- 예약 날짜, 필수 입력
status ENUM('Scheduled', 'Completed', 'Cancelled') DEFAULT 'Scheduled', -- 예약 상태, 기본값 'Scheduled'
FOREIGN KEY (patient_id) REFERENCES Patients(patient_id), -- Patients 테이블의 patient_id 참조
FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id) -- Doctors 테이블의 doctor_id 참조
);
-- Prescriptions 테이블 생성
-- 환자의 진료 시 의사에 의해 처방된 약물 정보를 저장합니다.
-- 각 처방은 고유 식별자, 예약 ID, 약물 이름, 용량을 가지고 있습니다.
CREATE TABLE Prescriptions (
prescription_id INT AUTO_INCREMENT PRIMARY KEY, -- 처방의 고유 ID, 자동 증가
appointment_id INT NOT NULL, -- 예약 ID, 외래 키
medication VARCHAR(255) NOT NULL, -- 처방된 약물 이름, 필수 입력
dosage VARCHAR(255) NOT NULL, -- 약물의 용량, 필수 입력
FOREIGN KEY (appointment_id) REFERENCES Appointments(appointment_id) -- Appointments 테이블의 appointment_id 참조
);
초급
1. 문제: 모든 부서의 이름을 조회하세요.
SELECT dp.name
FROM Departments dp;
2. 문제: 모든 의사의 이름과 전문 분야를 조회하세요.
SELECT dc.name, dc.specialty
FROM Doctors dc;
3. 문제: 'Pediatrics' 부서에 속한 의사들의 이름을 조회하세요.
SELECT dc.name
FROM Doctors dc
JOIN Departments dp
ON dp.department_id = dc.department_id
WHERE dp.name = 'Pediatrics';
4. 문제: 모든 환자의 이름과 생년월일을 조회하세요.
SELECT p.name, p.date_of_birth
FROM Patients p;
중급 (조인 포함)
1. 문제: 각 부서에 소속된 의사의 수를 조회하세요.
SELECT dp.name,COUNT(dc.name)
FROM Departments dp
LEFT JOIN Doctors dc
ON dp.department_id = dc.department_id
GROUP BY dp.name;
2. 문제: 'Surgery' 부서에서 근무하는 의사들이 진료 예정인 모든 환자의 이름을 조회하세요.
SELECT p.name
FROM Patients p
JOIN Appointments a
ON p.patient_id = a.appointment_id
JOIN Doctors dc
ON dc.doctor_id = a.doctor_id
JOIN Departments dp
ON dp.department_id = dc.department_id
WHERE dp.name = 'Surgery' AND a.status = 'Scheduled';
3. 문제: 모든 예약의 상태와 해당 예약을 진행하는 의사의 이름을 조회하세요.
SELECT a.status, dc.name
FROM Appointments a
JOIN Doctors dc
ON a.doctor_id = dc.doctor_id
ORDER BY a.status ;
4. 문제: 2024년에 예정된 모든 진료의 세부 정보(환자 이름, 의사 이름, 예약 날짜)를 조회하세요.
SELECT p.name, dc.name, a.appointment_date
FROM Patients p
JOIN Appointments a
ON p.patient_id = a.patient_id
JOIN Doctors dc
ON a.doctor_id = dc.doctor_id
WHERE YEAR (a.appointment_date) = 2024;
5. 문제: 각 의사가 진료한 환자 수를 조회하세요. 최소 2명 이상의 환자를 진료한 의사만 출력해야 합니다.
SELECT dc.name, COUNT(p.patient_id)
FROM Doctors dc
JOIN Appointments a
ON dc.doctor_id = a.doctor_id
JOIN Patients p
ON p.patient_id = a.patient_id
WHERE a.status <> 'Cancelled'
GROUP BY dc.name
HAVING count(p.patient_id) > 1;
고급 (복잡한 조인 및 서브쿼리)
1. 문제: 가장 많은 환자를 진료한 의사의 이름을 조회하세요.
SELECT dc.name, COUNT(a.appointment_id) as cnt
FROM Doctors dc
LEFT JOIN Appointments a
ON dc.doctor_id = a.doctor_id
GROUP BY dc.name
ORDER BY cnt
limit 1;
2. 문제: 진료가 완료된 모든 예약에서 처방된 약물의 종류를 조회하세요.
SELECT DISTINCT p.medication
FROM Prescriptions p
JOIN Appointments a
ON p.appointment_id = a.appointment_id
WHERE status = 'Completed';
3. 문제: 가장 많이 처방된 약물을 조회하세요.
SELECT p.medication, COUNT(p.prescription_id) as cnt
FROM Prescriptions p
GROUP BY p.medication
ORDER BY cnt DESC
LIMIT 1;
4. 문제: 각 환자가 받은 처방의 총 수를 조회하세요.
SELECT pt.name, COUNT(ps.prescription_id)
FROM Patients pt
LEFT JOIN Appointments a
ON a.patient_id = pt.patient_id
LEFT JOIN Prescriptions ps
ON a.appointment_id = ps.appointment_id
GROUP BY pt.name;