SQL 연습문제(지속 업데이트 중)

테스트 테이블 생성

-- 1. 부서 테이블 (departments)
CREATE TABLE IF NOT EXISTS departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '부서 ID, 자동 증가 주키',
    dept_name VARCHAR(50) NOT NULL UNIQUE COMMENT '부서명, 유일성 보장',
    location VARCHAR(100) COMMENT '위치',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '등록 시간'
) COMMENT '회사 부서 정보 테이블';

-- 2. 직원 테이블 (employees)
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '직원 ID, 자동 증가 주키',
    name VARCHAR(50) NOT NULL COMMENT '이름',
    gender ENUM('남', '녀', '미상') DEFAULT '미상' COMMENT '성별',
    department VARCHAR(50) COMMENT '소속 부서 (departments.dept_name 참조)',
    hire_date DATE NOT NULL COMMENT '입사일',
    phone VARCHAR(20) UNIQUE COMMENT '휴대폰 번호, 유일성 보장',
    email VARCHAR(100) UNIQUE COMMENT '이메일, 유일성 보장',
    manager_id INT COMMENT '매니저 ID (자체참조)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '등록 시간',
    FOREIGN KEY (department) REFERENCES departments(dept_name) ON UPDATE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL
) COMMENT '회사 직원 정보 테이블';

-- 3. 기술 테이블 (skills)
CREATE TABLE IF NOT EXISTS skills (
    skill_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '기술 ID, 자동 증가 주키',
    skill_name VARCHAR(50) NOT NULL UNIQUE COMMENT '기술명, 유일성 보장',
    skill_type VARCHAR(30) COMMENT '기술 유형 (예: 프로그래밍 언어, 도구 등)',
    description VARCHAR(200) COMMENT '기술 설명'
) COMMENT '기술 정보 테이블';

-- 4. 직원-기술 중개 테이블 (employee_skills)
CREATE TABLE IF NOT EXISTS employee_skills (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '레코드 ID, 자동 증가 주키',
    employee_id INT NOT NULL COMMENT '직원 ID, employees.id 참조',
    skill_id INT NOT NULL COMMENT '기술 ID, skills.skill_id 참조',
    proficiency INT CHECK (proficiency BETWEEN 1 AND 5) COMMENT '숙련도 (1~5)',
    learned_date DATE COMMENT '기술 습득 날짜',
    UNIQUE KEY uk_employee_skill (employee_id, skill_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (skill_id) REFERENCES skills(skill_id) ON DELETE CASCADE
) COMMENT '직원과 기술의 다중 관계 테이블';

-- 5. 급여 기록 테이블 (salary_records)
CREATE TABLE IF NOT EXISTS salary_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '기록 ID, 자동 증가 주키',
    employee_id INT NOT NULL COMMENT '직원 ID, employees.id 참조',
    basic_salary DECIMAL(10, 2) NOT NULL CHECK (basic_salary >= 0) COMMENT '기본 급여',
    bonus DECIMAL(10, 2) DEFAULT 0 CHECK (bonus >= 0) COMMENT '보너스',
    subsidy DECIMAL(10, 2) DEFAULT 0 CHECK (subsidy >= 0) COMMENT '수당',
    total_salary DECIMAL(10, 2) GENERATED ALWAYS AS (basic_salary + bonus + subsidy) STORED COMMENT '총 급여 (자동 계산)',
    effective_date DATE NOT NULL COMMENT '적용일',
    expire_date DATE COMMENT '종료일 (NULL은 현재 적용)',
    reason VARCHAR(200) COMMENT '급여 조정 사유',
    created_by VARCHAR(50) COMMENT '작성자',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '등록 시간',
    CONSTRAINT uk_employee_effective UNIQUE (employee_id, effective_date),
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) COMMENT '직원 급여 변경 기록 테이블';

데이터 삽입

-- 먼저 부서 데이터를 삽입 (직원 테이블에 의존)
INSERT INTO departments (dept_name, location) VALUES
('기술부', '서울'),
('마케팅부', '부산'),
('인사부', '대전'),
('재무부', '광주');

-- 직원 데이터 삽입 (중복 방지)
INSERT IGNORE INTO departments (dept_name, location) VALUES
('기술부', '서울특별시'),
('마케팅부', '부산광역시'),
('인사부', '대전광역시'),
('재무부', '광주광역시'),
('운영부', '경기도');

-- 직원 데이터 삽입 (부서 및 매니저 연결)
INSERT IGNORE INTO employees (id, name, gender, department, hire_date, phone, email, manager_id) VALUES
(1, '김철수', '남', '기술부', '2020-01-15', '010-1234-5678', 'kimcheolsu@example.com', NULL),
(2, '박영희', '녀', '마케팅부', '2021-03-20', '010-2345-6789', 'parkyeonghee@example.com', NULL),
(3, '최민호', '남', '기술부', '2019-11-05', '010-3456-7890', 'choiminho@example.com', 1),
(4, '이수정', '녀', '인사부', '2022-05-10', '010-4567-8901', 'lee_sujung@example.com', NULL),
(5, '홍길동', '남', '기술부', '2021-09-30', '010-5678-9012', 'honggildong@example.com', 1),
(6, '김미나', '녀', '재무부', '2020-07-22', '010-6789-0123', 'kimmina@example.com', NULL),
(7, '박준호', '남', '마케팅부', '2022-01-18', '010-7890-1234', 'parkjunho@example.com', 2),
(8, '김현아', '녀', '운영부', '2021-06-05', '010-8901-2345', 'kimmhyeon@example.com', NULL),
(9, '최진호', '남', '재무부', '2023-02-10', '010-9012-3456', 'choijinho@example.com', 6),
(10, '이하나', '녀', '운영부', '2022-09-15', '010-0123-4567', 'leehana@example.com', 8);

-- 기술 데이터 삽입
INSERT IGNORE INTO skills (skill_id, skill_name, skill_type, description) VALUES
(1, 'Java', '프로그래밍 언어', '객체 지향 프로그래밍 언어'),
(2, 'Python', '프로그래밍 언어', '간결한 스크립팅 언어'),
(3, 'MySQL', '데이터베이스', '관계형 데이터베이스 관리 시스템'),
(4, 'JavaScript', '프로그래밍 언어', '프론트엔드 개발 주요 언어'),
(5, 'Excel', '사무 소프트웨어', '데이터 처리 및 분석 도구'),
(6, 'PPT', '사무 소프트웨어', '프레젠테이션 제작 도구'),
(7, 'Vue', '프론트엔드 프레임워크', '점진적 JavaScript 프레임워크'),
(8, 'Spring Boot', '백엔드 프레임워크', 'Java 개발 프레임워크'),
(9, '데이터 분석', '비즈니스 능력', '데이터 마이닝 및 분석 능력'),
(10, '프로젝트 관리', '관리 능력', '프로젝트 계획 및 실행 능력');

-- 직원-기술 중개 데이터 삽입
INSERT IGNORE INTO employee_skills (employee_id, skill_id, proficiency, learned_date) VALUES
(1, 1, 5, '2018-06-10'),  -- 김철수: Java (숙련도 5)
(1, 3, 4, '2019-01-15'),  -- 김철수: MySQL (숙련도 4)
(1, 8, 5, '2019-05-20'),  -- 김철수: Spring Boot (숙련도 5)
(3, 1, 4, '2019-03-20'),  -- 최민호: Java (숙련도 4)
(3, 2, 3, '2020-05-10'),  -- 최민호: Python (숙련도 3)
(3, 3, 3, '2019-12-05'),  -- 최민호: MySQL (숙련도 3)
(5, 1, 3, '2021-02-28'),  -- 홍길동: Java (숙련도 3)
(5, 4, 2, '2022-01-15'),  -- 홍길동: JavaScript (숙련도 2)
(5, 7, 2, '2022-03-10'),  -- 홍길동: Vue (숙련도 2)
(2, 5, 4, '2020-11-05'),  -- 박영희: Excel (숙련도 4)
(2, 6, 5, '2019-09-30'),  -- 박영희: PPT (숙련도 5)
(2, 9, 4, '2021-01-20'),  -- 박영희: 데이터 분석 (숙련도 4)
(4, 5, 5, '2021-07-20'),  -- 이수정: Excel (숙련도 5)
(4, 10, 3, '2022-08-15'), -- 이수정: 프로젝트 관리 (숙련도 3)
(6, 3, 4, '2019-05-15'),  -- 김미나: MySQL (숙련도 4)
(6, 5, 4, '2018-11-10'),  -- 김미나: Excel (숙련도 4)
(7, 6, 3, '2021-05-10'),  -- 박준호: PPT (숙련도 3)
(7, 9, 2, '2022-03-20'),  -- 박준호: 데이터 분석 (숙련도 2)
(8, 10, 4, '2020-08-05'), -- 김현아: 프로젝트 관리 (숙련도 4)
(10, 9, 3, '2022-11-10'); -- 이하나: 데이터 분석 (숙련도 3)

-- 급여 기록 데이터 삽입
INSERT IGNORE INTO salary_records (record_id, employee_id, basic_salary, bonus, subsidy, effective_date, expire_date, reason, created_by) VALUES
-- 김철수 급여 기록
(1, 1, 7000, 500, 500, '2020-01-15', '2021-12-31', '초기 급여', 'admin'),
(2, 1, 8000, 800, 500, '2022-01-01', NULL, '연차 인상', 'admin'),

-- 박영희 급여 기록
(3, 2, 6000, 300, 200, '2021-03-20', '2022-06-30', '초기 급여', 'admin'),
(4, 2, 6500, 400, 200, '2022-07-01', NULL, '6개월 인상', 'admin'),

-- 최민호 급여 기록
(5, 3, 8500, 500, 200, '2019-11-05', '2021-05-31', '초기 급여', 'admin'),
(6, 3, 9200, 600, 400, '2021-06-01', NULL, '승진 인상', 'admin'),

-- 이수정 급여 기록
(7, 4, 5500, 200, 100, '2022-05-10', NULL, '초기 급여', 'admin'),

-- 홍길동 급여 기록
(8, 5, 7000, 300, 200, '2021-09-30', '2023-02-28', '초기 급여', 'admin'),
(9, 5, 7500, 400, 200, '2023-03-01', NULL, '연차 인상', 'admin'),

-- 김미나 급여 기록
(10, 6, 7200, 500, 300, '2020-07-22', '2022-12-31', '초기 급여', 'admin'),
(11, 6, 7800, 600, 300, '2023-01-01', NULL, '연차 인상', 'admin'),

-- 박준호 급여 기록
(12, 7, 6200, 200, 100, '2022-01-18', '2023-06-30', '초기 급여', 'admin'),
(13, 7, 6800, 300, 100, '2023-07-01', NULL, '연차 인상', 'admin'),

-- 김현아 급여 기록
(14, 8, 6500, 400, 300, '2021-06-05', '2022-11-30', '초기 급여', 'admin'),
(15, 8, 7000, 500, 300, '2022-12-01', NULL, '연차 인상', 'admin'),

-- 최진호 급여 기록
(16, 9, 5800, 200, 100, '2023-02-10', NULL, '초기 급여', 'admin'),

-- 이하나 급여 기록
(17, 10, 6000, 300, 200, '2022-09-15', '2023-08-31', '초기 급여', 'admin'),
(18, 10, 6300, 300, 200, '2023-09-01', NULL, '연차 인상', 'admin');

一、기본 쿼리 및 조건 필터링 (단일 테이블 작업)

  1. 문제: departments 테이블에서 dept_name과 location을 조회하고 dept_name으로 오름차순 정렬하세요.
SELECT dept_name, location 
FROM departments 
ORDER BY dept_name ASC;
  1. 문제: employees 테이블에서 department이 '기술부'이고 hire_date가 2021년 이후인 직원들의 name, hire_date, phone을 조회하세요.
SELECT name, hire_date, phone 
FROM employees 
WHERE department = '기술부' 
  AND hire_date >= '2021-01-01';
  1. 문제: salary_records 테이블에서 total_salary가 8000~10000 사이인 기록을 조회하고 employee_id, total_salary, effective_date를 출력하며 effective_date로 내림차순 정렬하세요.
SELECT employee_id, total_salary, effective_date
FROM salary_records
WHERE total_salary BETWEEN 8000 AND 10000
ORDER BY effective_date DESC;
  1. 문제: skills 테이블에서 skill_type이 '프로그래밍 언어'인 기술을 조회하고 skill_id, skill_name, description을 출력하세요.
SELECT skill_id, skill_name, description
FROM skills
WHERE skill_type = '프로그래밍 언어';
  1. 문제: employee_skills 테이블에서 proficiency가 5이고 learned_date가 2020년인 기록을 조회하고 employee_id, skill_id, learned_date를 출력하며 learned_date로 오름차순 정렬하세요.
SELECT employee_id, skill_id, learned_date
FROM employee_skills
WHERE proficiency = 5
  AND learned_date >= '2020-01-01'
ORDER BY learned_date ASC;

二、집계 함수 및 그룹화 쿼리

  1. 문제: 각 부서의 직원 수를 계산하고 employee_count가 3 이상인 부서만 출력하세요.
SELECT department AS 부서명,
       COUNT(*) AS 직원수
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3;
  1. 문제: 각 부서의 현재 급여 평균값을 계산하고 expire_date가 NULL인 기록만 고려하여 avg_salary를 2자리까지 반올림하세요.
SELECT e.department AS 부서명,
       ROUND(AVG(sr.total_salary), 2) AS avg_salary
FROM employees e
JOIN salary_records sr ON e.id = sr.employee_id
WHERE sr.expire_date IS NULL
GROUP BY e.department;
  1. 문제: 각 기술의 학습자 수를 계산하고 학습자가 없는 기술도 포함하여 skill_name으로 내림차순 정렬하세요.
SELECT 
      s.skill_name AS 기술명,
      COUNT(es.employee_id) AS 학습자수
FROM skills s
LEFT JOIN employee_skills es ON s.skill_id = es.skill_id
GROUP BY s.skill_name
ORDER BY 학습자수 DESC;

三、JOIN 쿼리 (다중 테이블 연관)

  1. 문제: 모든 직원의 이름, 소속 부서명 및 부서 위치를 조회하고 부서가 없는 직원도 포함하세요.
SELECT 
  e.name AS 직원명,
  d.dept_name AS 부서명,
  d.location AS 위치
FROM employees e
LEFT JOIN departments d ON e.department = d.dept_name;
  1. 문제: Java 기술을 익힌 직원의 이름, 부서 및 숙련도를 조회하고 숙련도가 4 이상인 경우만 표시하세요.
SELECT 
  e.name AS 직원명,
  e.department AS 부서,
  es.proficiency AS 숙련도
FROM employees e
JOIN employee_skills es ON e.id = es.employee_id
JOIN skills s ON es.skill_id = s.skill_id
WHERE s.skill_name = 'Java' 
  AND es.proficiency >= 4;
  1. 문제: 2023년 급여 조정이 발생한 직원의 이름 및 조정 전후 급여를 조회하세요.
SELECT 
  e.name AS 직원명,
  prev.total_salary AS 조정전_급여,
  curr.total_salary AS 조정후_급여,
  curr.effective_date AS 조정일
FROM employees e
JOIN salary_records curr ON e.id = curr.employee_id
JOIN salary_records prev ON e.id = prev.employee_id 
  AND prev.expire_date = curr.effective_date - INTERVAL 1 DAY
WHERE YEAR(curr.effective_date) = 2023;

四、서브쿼리 및 중첩 쿼리

  1. 문제: 부서 평균 급여보다 높은 직원의 이름, 부서 및 현재 급여를 조회하세요.
SELECT 
  e.name AS 직원명,
  e.department AS 부서,
  sr.total_salary AS 현재_급여
FROM employees e
JOIN salary_records sr ON e.id = sr.employee_id
WHERE sr.expire_date IS NULL
  AND sr.total_salary > (
    SELECT AVG(sr2.total_salary)
    FROM employees e2
    JOIN salary_records sr2 ON e2.id = sr2.employee_id
    WHERE sr2.expire_date IS NULL
      AND e2.department = e.department
  );
  1. 문제: Java와 MySQL 두 기술을 모두 익힌 직원의 이름을 조회하세요.
SELECT e.name AS 직원명
FROM employees e
WHERE EXISTS (
  SELECT 1 
  FROM employee_skills es 
  JOIN skills s ON es.skill_id = s.skill_id
  WHERE es.employee_id = e.id AND s.skill_name = 'Java'
)
AND EXISTS (
  SELECT 1 
  FROM employee_skills es 
  JOIN skills s ON es.skill_id = s.skill_id
  WHERE es.employee_id = e.id AND s.skill_name = 'MySQL'
);
  1. 문제: 각 부서에서 가장 높은 급여를 받는 직원의 이름 및 급여를 조회하세요.
SELECT 
  dept_name AS 부서명,
  name AS 직원명,
  max_salary AS 최고_급여
FROM (
  SELECT 
    e.department AS dept_name,
    e.name,
    sr.total_salary,
    MAX(sr.total_salary) OVER (PARTITION BY e.department) AS max_salary
  FROM employees e
  JOIN salary_records sr ON e.id = sr.employee_id
  WHERE sr.expire_date IS NULL
) AS sub
WHERE total_salary = max_salary
ORDER BY dept_name;

五、윈도우 함수 및 고급 쿼리

  1. 문제: 각 부서의 직원을 현재 급여 기준으로 내림차순으로 순위를 매겨 출력하세요.
SELECT 
  e.name AS 직원명,
  e.department AS 부서,
  sr.total_salary AS 급여,
  RANK() OVER (PARTITION BY e.department ORDER BY sr.total_salary DESC) AS 순위
FROM employees e
JOIN salary_records sr ON e.id = sr.employee_id
WHERE sr.expire_date IS NULL
ORDER BY e.department, 순위;
  1. 문제: 직원의 급여 조정 비율을 계산하고 이름, 조정일, 변화율(소수점 첫째 자리)을 출력하세요.
SELECT 
  e.name AS 직원명,
  curr.effective_date AS 조정일,
  curr.total_salary AS 현재_급여,
  prev.total_salary AS 이전_급여,
  ROUND(
    (curr.total_salary - prev.total_salary) / prev.total_salary * 100, 
    1
  ) AS 변화율
FROM employees e
JOIN salary_records curr ON e.id = curr.employee_id
JOIN salary_records prev ON e.id = prev.employee_id
  AND prev.expire_date = curr.effective_date - INTERVAL 1 DAY
ORDER BY e.name, 조정일;
  1. 문제: 각 부서별 기술 유형 별 직원 수를 계산하고 결과를 부서별로 정렬하세요.
SELECT 
  e.department AS 부서,
  s.skill_type AS 기술유형,
  COUNT(DISTINCT e.id) AS 직원수
FROM employees e
LEFT JOIN employee_skills es ON e.id = es.employee_id
LEFT JOIN skills s ON es.skill_id = s.skill_id
GROUP BY e.department, s.skill_type
ORDER BY e.department, s.skill_type;

六、종합 시나리오 쿼리

  1. 문제: 김철수의 상사(직접 및 간접) 이름과 관계를 조회하세요.
WITH RECURSIVE manager_chain AS (
  SELECT 
    m.id AS manager_id,
    m.name AS manager_name,
    1 AS level,
    '직접 상사' AS relation
  FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.id
  WHERE e.name = '김철수'

  UNION ALL

  SELECT 
    m2.id AS manager_id,
    m2.name AS manager_name,
    mc.level + 1 AS level,
    CONCAT('간접 상사(', mc.level + 1, '단계)') AS relation
  FROM manager_chain mc
  JOIN employees m2 ON mc.manager_id = m2.manager_id
  WHERE m2.id IS NOT NULL
)
SELECT manager_name AS 상사명, relation AS 관계
FROM manager_chain;
  1. 문제: 기술 숙련도 3개 이상인 직원의 평균 급여와 3개 미만인 직원의 평균 급여를 비교하세요.
SELECT 
  CASE 
    WHEN skill_count >= 3 THEN '3개 이상 기술 숙련'
    ELSE '3개 미만 기술 숙련'
  END AS 기술_숙련도,
  ROUND(AVG(total_salary), 2) AS 평균_급여
FROM (
  SELECT 
    e.id,
    e.name,
    COUNT(DISTINCT es.skill_id) AS skill_count,
    sr.total_salary
  FROM employees e
  LEFT JOIN employee_skills es ON e.id = es.employee_id
  JOIN salary_records sr ON e.id = sr.employee_id
  WHERE sr.expire_date IS NULL
  GROUP BY e.id, e.name, sr.total_salary
) AS skill_stats
GROUP BY 기술_숙련도;
  1. 문제: 2022년부터 2023년까지 급여 조정 횟수가 가장 많은 부서를 조회하세요.
SELECT 
  부서,
  조정횟수
FROM (
  SELECT 
    e.department AS 부서,
    COUNT(sr.record_id) AS 조정횟수,
    RANK() OVER (ORDER BY COUNT(sr.record_id) DESC) AS rnk
  FROM employees e
  JOIN salary_records sr ON e.id = sr.employee_id
  WHERE YEAR(sr.effective_date) BETWEEN 2022 AND 2023
  GROUP BY e.department
) AS dept_adjust
WHERE rnk = 1;

태그: SQL 데이터베이스 JOIN 서브쿼리 윈도우함수

8월 9일 07:19에 게시됨

인기 태그

©2026 괴물 클럽