예상 출제 문제

SQL 문제 예제

1. Join 활용 문제
1. 환자 테이블(Patients)과 진료 기록 테이블(Visits)이 있다고 가정, 특정 환자의 모든 진료 기록 조회하기

SELECT Patients.patient_id, Patients.name, Visits.visit_date, Visits.doctor_id
FROM Patients
JOIN Visits ON Patients.patient_id = Visits.patient_id
WHERE Patients.name = '김환자';

1. 집계 함수 활용 문제
💡
• 특정 그룹(환자) 내에서 특정 데이터(진료횟수)를 계산해야 할 때, 집계 함수(COUNT, SUM, AVG 등)를 사용
• 각 환자가 병원을 방문한 총 횟수 계산, 가장 많이 진료받은 환자 상위 5명을 찾아내는 것

SELECT patient_id, COUNT(visit_id) AS visit_count
FROM Visits
GROUP BY patient_id
ORDER BY visit_count DESC
LIMIT 5;

• COUNT(visit_id): 각 환자의 진료 횟수를 세는 집계 함수
• GROUP BY patient_id: 환자별로 데이터를 그룹화하여 각각의 진료 횟수를 계산
• ORDER BY visit_count DESC: 진료 횟수가 많은 순서대로 정렬
• LIMIT 5: 상위 5명의 환자만 선택
 

SELECT AVG(visit_count) AS avg_visits
FROM (
		SELECT patient_id, COUNT(visit_id) AS visit_count
		FROM Visits
		GROUP BY patient_id
) AS visit_counts
JOIN Patients ON visit_counts.patient_id = Patients.patient_id
WHERE Patients.age >= 60;

SELECT *
FROM Visits
ORDER BY visit_date DESC
LIMIT 20;

1. 서브쿼리 문제
💡
• 서브쿼리는 ‘쿼리 안의 쿼리’로, 하나의 쿼리 결과를 사용하여 다른 쿼리를 실행
• 각 환자의 가장 최근 진료 날짜 조회

SELECT patient_id, MAX(visit_date) AS last_visit
FROM Visits
GROUP BY patient_id;

• MAX(visit_date): 각 환자가 병원을 방문한 날짜 중 가장 최신 날짜를 가져옴
• GROUP BY patient_id: 환자별로 데이터를 그룹화하여 각각의 가장 최근 날짜를 구함
 

2. 인덱스 및 성능 최적화 문제
1. 진료 기록 데이터가 증가하면서 조회 성능이 저하됐다. 진료 기록 테이블(Visits)에 어떤 인덱스를 추가하면 성능이 향상 될까? → 자주 사용하는 검색 조건으로 걸리는 열(Ex. patient_id, visit_date)에 인덱스를 추가하여 성능을 최적화

1. NULL 처리 문제
1. 환자 테이블에서 주소 정보(address)가 없는 환자만 조회

SELECT patient_id, name
FROM Patients
WHERE address IS NULL;

1. UPDATE 및 DELETE 문제
1. 특정 환자 ID를 가진 환자의 모든 진료 기록을 삭제

DELETE FROM Visits
WHERE patient_id = '특정환자ID';

1. 복잡한 조건 쿼리 문제
💡
• 특정 조건에 맞는 데이터를 복합적으로 검색
• 지난 1개월 동안 진료를 받은 환자들의 정보를 찾는 문제

SELECT DISTINCT Patients.patient_id, Patients.name
FROM Patients
JOIN Visits ON Patients.patient_id = Visits.patient_id
WHERE Visits.visit_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

• JOIN: Patients와 Visits 테이블을 결합하여 각 환자의 진료 내역을 가져옴
• DATE_SUB(NOW(), INTERVAL 1 MONTH): 현재 날짜 기준으로 한 달 전의 날짜를 계산
• DISTINCT: 중복된 환자 ID가 나오지 않도록 한 번씩만 표시

1. TRANSACTION 문제
💡
• 트랜잭션은 여러 작업을 묶어 한 번에 처리, 모든 작업 성공 시 커밋, 하나라도 실패 시 롤백하여 이전 상태로 복구
• 환자 등록과 예약을 동시에 처리하고, 중간에 문제가 발생하면 모든 작업을 취소

BEGIN;
INSERT INTO Patients (patient_id, name) VALUES ('새환자ID', '홍길동');
INSERT INTO Appointments (appointment_id, patient_id, appointment_date) VALUES ('새예약ID', '새환자ID', '2024-10-30');'

• BEGIN: 트랜잭션 시작을 알림
• INSERT INTO: 환자와 예약 정보를 각각 삽입하는 명령어
• COMMIT: 모든 작업이 성공적으로 완료되면 데이터베이스에 영구적으로 반영
• 만약 에러가 발생한다면 ROLLBACK 명령어를 사용하여 이전 상태로 되돌릴 수 있음

1. CASE문 활용 문제
1. 진료 기록(Visits)에서 의사별로 진료 횟수를 세고, 진료 횟수가 10회 이상인 경우 “많음”, 그렇지 않으면 “적음”으로 분류하여 출력

SELECT doctor_id,
			 COUNT(visit_id) AS total_visits,
			 CASE WHEN COUNT(visit_id) >= 10 THEN '많음' ELSE '적음' END AS visit_category
FROM Visits
GROUP BY doctor_id;

1. 중복 데이터 처리 문제
1. 중복된 진료 기록이 존재할 때, 가장 오래된 진료 기록을 제외하고 삭제

DELECT FROM Visits
WHERE visit_id NOT IN (
		SELECT MIN(visit_id)
		FROM Visits
		GROUP BY patient_id, visit_date
);

1. 데이터 무결성 제약 조건 문제
1. 환자 테이블(Patients)에서 중복된 주민등록번호(resident_number)를 허용하지 않도록 설정

ALTER TABLE Patients
ADD CONSTRAINT unique_resident_number UNIQUE (resident_number);

Linux 문제 예제

1. 디렉토리 및 파일 관리
1. 특정 디렉토리 내에서 .log 파일을 모두 찾아서 backup_logs라는 디렉토리로 이동

# backup_logs라는 디렉토리 생성 (-p 옵션으로 디렉토리가 이미 존재해도 오류 없음)
mkdir -p backup_logs

# 현재 디렉토리의 모든 .log 파일을 backup_logs 디렉토리로 이동
mv *.log backup_logs/

1. 파일 검색 및 내용 확인
1. /var/log 디렉토리에서 특정 에러 메시지(”error”)를 포함한 로그 파일을 찾아내 그 내용을 확인

# /var/log 디렉토리 안에서 "error"가 포함된 파일을 재귀적으로 검색
grep -r "error" /var/log

 

2. 사용자 및 권한 관리
1. 새로운 사용자 meduser를 생성, read-only 권한으로 /data/medical 디렉토리에 접근할 수 있도록 설정

# 새로운 사용자 meduser를 추가
useradd meduser

# /data/medical 디렉토리의 파일을 모든 사용자에게 읽기 전용으로 설정
chmod 444 /data/medical

 
2. /medical_data 디렉토리와 하위 파일을 모든 사용자가 읽기 전용으로 접근할 수 있도록 설정

chmod -R 444 /medical_data

 
 

3. 네트워크 확인 및 설정
1. 현재 시스템의 네트워크 설정 및 IP 주소 확인

ifconfig

1. 프로세스 관리
1. 특정 프로세스(MySQL)의 PID를 찾고 종료

# "mysql"이름이 포함된 프로세스의 PID 조회
pgrep mysql

# 특정 PID에 해당하는 프로세스를 강제 종료
kill -9 [PID]


# 8080 포트가 열려있는지 확인
netstat -tuln | grep 8080

# 8080 포트를 사용 중인 프로세스 ID 조회
lsof -i :8080

 
2. 프로세스 우선순위 조정

# MySQL 프로세스의 우선순위를 낮춤 (PID는 MySQL의 실제 프로세스 ID)
renice +10 [PID]

1. 시스템 모니터링
1. 시스템의 현재 CPU 및 메로리 사용 상태 모니터링

# 시스템의 CPU, 메모리 사용 상태를 실시간으로 모니터링
top

 

2. Crontab 설정
1. 매일 자정에 /backup 디렉토리로 데이터를 백업하는 스크립트를 설정

# crontab 편집 모드로 진입
crontab -e

• Crontab 설정

# 매일 0시(자정)에 /data 디렉토리의 내용을 /backup 디렉토리로 복사
0 0 * * * cp -r /data /backup

→ 자동화 스케줄리 능력 확인
 

3. 시스템 백업 스크립트 작성
1. 매주 일요일 자정에 /data 디렉토리의 백업을 /backup/weekly_backup으로 자동 저장하는 스크립트를 작성하고 crontab에 등록

# backup.sh
#!/bin/bash
cp -r /data /backup/weekly_backup

• Crontab 설정

0 0 * * 0 /path/to/backup.sh

 

4. 패키지 설치 및 관리
1. MySQL 패키지 설치

# sudo를 통해 관리 권한으로 명령 실행
sudo apt-get install mysql-server

1. 파일 압축 및 해제
1. /data/medical 디렉토리를 backup_medical.tar.gz로 압축하고, /backup 디렉토리에 압축을 해제

# .tar.gz 파일로 압축 생성
tar -czvf backup_medical.tar.gz /data/medical

# 압축 해제 후 /backup으로 이동
tar -xzvf backup_medical.tar.gz -C /backup

 

2. 디스크 사용량 확인
1. 시스템의 각 파일 시스템의 사용량을 확인하고, 가장 많이 사용되는 디렉토리 상위 5개를 /data에서 검색

# 디스크 사용량 확인
df -h

# /data 내 가장 큰 파일 및 디렉토리 상위 5개 조회
du -ah /data | sort -rh | head -n 5

1. 로그 파일 크기 관리
1. 시스템의 로그 파일이 너무 커지는 것을 방지하기 위해, 특정 로그 파일의 크기를 10MB로 제한하고 초과 시 압축하도록 스크립트를 작성

#!/bin/bash
LOG_FILE="/var/log/syslog"
MAX_SIZE=10485760  # 10MB

if [ $(stat -c%s "$LOG_FILE") -gt $MAX_SIZE ]; then
    mv $LOG_FILE "$LOG_FILE.$(date +%Y%m%d%H%M%S).gz"  # 압축
    gzip "$LOG_FILE"
    touch $LOG_FILE  # 새로운 로그 파일 생성
fi

1. 서비스 관리
1. Apache 웹 서버가 현재 실행 중인지 확인, 실행 중이지 않다면 시작

systemctl status apache2  # Apache 서버 상태 확인
systemctl start apache2   # Apache 서버 시작