[2월 4주차-2/25(2)]🎓학사 관리 테이블 실습 - SQL 문제 풀이

1. 학사 관리 테이블 소개

이번 실습에서는 학사 관리 테이블을 설계하고, 다양한 SQL 쿼리를 사용하여 데이터를 조회하고 분석하는 연습을 해보겠습니다.

2. 테이블 생성 및 데이터 입력

-- create db university
create database university;
use university;

-- create table
CREATE TABLE department (
    dept_id VARCHAR(10) PRIMARY KEY,  -- 학과 코드
    dept_name VARCHAR(100) NOT NULL,    -- 학과명
    office VARCHAR(50)             -- 학과실
);

CREATE TABLE student (
    stu_id VARCHAR(10) PRIMARY KEY,  -- 학번
    resident_id VARCHAR(14) NOT NULL,    -- 주민등록번호
    name VARCHAR(50) NOT NULL,           -- 학생 이름
    year INT, 								-- 학년
    address VARCHAR(100),                -- 주소
    dept_id VARCHAR(10),               -- 학과 코드 (외래키)
    FOREIGN KEY (dept_id) REFERENCES department(dept_id) -- 학과와 연관
);

CREATE TABLE professor (
    prof_id VARCHAR(10) PRIMARY KEY,  -- 교수 ID
    resident_id VARCHAR(14) NOT NULL,      -- 주민등록번호
    name VARCHAR(50) NOT NULL,             -- 교수 이름
    dept_id VARCHAR(10),                 -- 학과 코드 (외래키)
    position VARCHAR(20) NOT NULL,         -- 직급 (교수, 부교수 등)
    year_emp INT,						   -- 임용연도
    FOREIGN KEY (dept_id) REFERENCES department(dept_id) -- 학과 테이블 연결
);

CREATE TABLE course (
    course_id VARCHAR(10) PRIMARY KEY,  -- 과목 코드
    title VARCHAR(100) NOT NULL,  -- 과목명
    credit INT     					-- 학점
);

CREATE TABLE class (
    class_id VARCHAR(10) PRIMARY KEY,      -- 강의 ID
    course_id VARCHAR(10) NOT NULL,        -- 과목 코드 (외래키)
    year INT,         						-- 년도
    semester INT,							-- 학기 (1학기 또는 2학기)
    division VARCHAR(5),                    -- 
    prof_id VARCHAR(10),              -- 담당 교수 ID (외래키)
    classroom VARCHAR(10),                 -- 강의실
    enroll INT, 						-- 최대 수강 인원
    FOREIGN KEY (course_id) REFERENCES course(course_id), -- 과목 연결
    FOREIGN KEY (prof_id) REFERENCES professor(prof_id) -- 교수 연결
);

CREATE TABLE takes (
    stu_id VARCHAR(10),   -- 학생 ID (외래키)
    class_id VARCHAR(10),     -- 강의 ID (외래키)
    grade VARCHAR(5),         -- 성적 (예: A+, A, B+ 등)
    CONSTRAINT pk_takes PRIMARY KEY (stu_id, class_id), -- 학생과 강의의 조합이 고유해야 함
    CONSTRAINT fk_takes1 FOREIGN KEY (stu_id) REFERENCES student(stu_id), -- 학생 연결
    CONSTRAINT fk_takes2 FOREIGN KEY (class_id) REFERENCES class(class_id) -- 강의 연결
);

-- insert values
insert into department values('920', '컴퓨터공학과', '201호'); 
insert into department values('923', '산업공학과', '207호'); 
insert into department values('925', '전자공학과', '308호');

insert into student values('1292001', '900424-1825409', '김광식', 3, '서울', '920'); 
insert into student values('1292002', '900305-1730021', '김정현', 3, '서울', '920'); 
insert into student values('1292003', '891021-2308302', '김현정', 4, '대전', '920'); 
insert into student values('1292301', '890902-2704012', '김현정', 2, '대구', '923'); 
insert into student values('1292303', '910715-1524390', '박광수', 3, '광주', '923'); 
insert into student values('1292305', '921011-1809003', '김우주', 4, '부산', '923'); 
insert into student values('1292501', '900825-1506390', '박철수', 3, '대전', '925'); 
insert into student values('1292502', '911011-1809003', '백태성', 3, '서울', '925');


insert into professor values('92001', '590327-1839240', '이태규', '920', '교수', 1997); 
insert into professor values('92002', '690702-1350026', '고희석', '920', '부교수', 2003); 
insert into professor values('92301', '741011-2765501', '최성희', '923', '부교수', 2005); 
insert into professor values('92302', '750728-1102458', '김태석', '923', '교수', 1999); 
insert into professor values('92501', '620505-1200546', '박철재', '925', '조교수', 2007); 
insert into professor values('92502', '740101-1830264', '장민석', '925', '부교수', 2005);


insert into course values('C101', '전산개론', 3); 
insert into course values('C102', '자료구조', 3); 
insert into course values('C103', '데이터베이스', 4); 
insert into course values('C301', '운영체제', 3); 
insert into course values('C302', '컴퓨터구조', 3); 
insert into course values('C303', '이산수학', 4); 
insert into course values('C304', '객체지향언어', 4); 
insert into course values('C501', '인공지능', 3); 
insert into course values('C502', '알고리즘', 2);


insert into class values('C101-01', 'C101', 2012, 1, 'A', '92301', '301호', 40); 
insert into class values('C102-01', 'C102', 2012, 1, 'A', '92001', '209호', 30); 
insert into class values('C103-01', 'C103', 2012, 1, 'A', '92501', '208호', 30); 
insert into class values('C103-02', 'C103', 2012, 1, 'B', '92301', '301호', 30); 
insert into class values('C501-01', 'C501', 2012, 1, 'A', '92501', '103호', 45); 
insert into class values('C501-02', 'C501', 2012, 1, 'B', '92502', '204호', 25); 
insert into class values('C301-01', 'C301', 2012, 2, 'A', '92502', '301호', 30); 
insert into class values('C302-01', 'C302', 2012, 2, 'A', '92501', '209호', 45); 
insert into class values('C502-01', 'C502', 2012, 2, 'A', '92001', '209호', 30); 
insert into class values('C502-02', 'C502', 2012, 2, 'B', '92301', '103호', 26);


insert into takes values('1292001', 'C101-01', 'B+'); 
insert into takes values('1292001', 'C103-01', 'A+'); 
insert into takes values('1292001', 'C301-01', 'A'); 
insert into takes values('1292002', 'C102-01', 'A'); 
insert into takes values('1292002', 'C103-01', 'B+'); 
insert into takes values('1292002', 'C502-01', 'C+'); 
insert into takes values('1292003', 'C103-02', 'B'); 
insert into takes values('1292003', 'C501-02', 'A+'); 
insert into takes values('1292301', 'C102-01', 'C+'); 
insert into takes values('1292303', 'C102-01', 'C'); 
insert into takes values('1292303', 'C103-02', 'B+'); 
insert into takes values('1292303', 'C501-01', 'A+');

3. SQL 문제 풀이

-- 1) student 테이블에서 모든 학생들의 주소를 추출
select address from student;

 -- 2) student 테이블에서 모든 학생들의 주소를 추출(중복제거)
 select distinct address from student;
 
 -- 3) professor 테이블에서 교수의 이름과 현재까지의 재직연수를 검색
select name, 2025-year_emp
FROM professor b;
    
-- 4) select문은 학생들의 이름, 학번, 그리고 소속 학과의 이름을 검색
SELECT s.name, s.stu_id, d.dept_name
FROM student AS s JOIN department AS d ON s.dept_id = d.dept_id;
 
 -- 5) 컴퓨터공학과 3학년 학생들의 학번을 검색
 select stu_id 
 from student AS s JOIN department AS d ON s.dept_id = d.dept_id 
 where d.dept_name = '컴퓨터공학과' and s.year = 3;
 
 -- 6) student 테이블에서 3, 4학년 학생들의 이름과 학번 오름차순으로 검색
SELECT name, stu_id
FROM student
WHERE year > 2
ORDER BY name , stu_id;

-- 7) student 테이블에서 3, 4학년 학생들의 이름(내림차순)과 학번으로 검색
SELECT name, stu_id
FROM student
WHERE year > 2
ORDER BY name desc, stu_id;
 
 -- 8) student 테이블과 department 테이블을 이용하여 학생들의 이름과 소속 학과 이름을 검색
 select s.name, d.dept_name
 from student s join department d on s.dept_id = d.dept_id;
 
 -- 9) student 테이블에서 ‘김광식’ 학생과 주소가 같은 학생들의 이름과 주소를 검색
 select name, address
 from student
 where address = (select address from student where name = '김광식');
 
 -- 10) 교수들의 이름과 직위, 재직연수를 출력
 select name as 이름, position as 직위, 2025-year_emp as 재직연수
 from professor;
 
 -- 11) student 테이블에서 김씨 성을 가진 학생들을 출력
 select * from student
 where name like '김%';
 
 -- 11) student 테이블에서 김씨 성을 가진 학생들을 출력
 select * from student
 where resident_id like '%-%2';
 
 -- 13) 컴퓨터공학과 학생들 중에서 교과목에 상관없이 학점을 'A+' 받은 학생들의 학번을 검색
 select s.stu_id
 from student s join takes t on s.stu_id = t.stu_id join department d on s.dept_id = d.dept_id
 where d.dept_name = '컴퓨터공학과' and t.grade = 'A+';
 
 -- 14) 모든 교과목들에 대해 교과목명, 학점수, 개설 년도, 개설 학기를 검색
 select co.title, co.credit, cl.year, cl.semester
 from course co join class cl on co.course_id = cl.course_id;
 
 -- 15) student 테이블에서 3학년 학생이 몇 명인지 출력
 select count(year) from student where year =3;
 
 -- 16) student 테이블에서 dept_id 필드에 값이 몇 개인지를 출력
  select count(dept_id) from student;
  
-- 17) student 테이블에서 dept_id 필드에 값이 몇 개인지를 출력(중복재거)
  select count(distinct dept_id) from student;

-- 18) 컴퓨터공학과의 학생 수를 출력
  select count(*) 
 from student AS s JOIN department AS d ON s.dept_id = d.dept_id 
 where d.dept_name = '컴퓨터공학과';
 
 -- 19) 전체 교수들의 재직연수 합
 SELECT  sum(2025 - year_emp)
FROM professor b;
 
 -- 20) 전체 교수의 평균 재직연수를 출력
  SELECT  avg(2025 - year_emp)
FROM professor b;

-- 21) student 테이블에서 학과번호(dept_id 필드)별로 레코드의 개수를 출력
select dept_id, count(*)
from student
group by dept_id;
 
 -- 22) 학과번호 대신 department 테이블과 연결하여 학과 이름이 출력되도록 위의 문제를 수정
select d.dept_name, count(*)
from student s join department d on s.dept_id = d.dept_id
group by d.dept_name;
 
 -- 23) 학사 데이터베이스에서 학과별 교수 숫자와 평균 재직연수, 최대 재직연수를 출력
select d.dept_name, 
		count(*), 
        avg(2025 - year_emp),
        max(2025 - year_emp)
from professor p join department d on p.dept_id = d.dept_id
group by d.dept_name;
 
 -- 24) 평균 재직연수가 20년 이상인 학과에 대해서만 교수 숫자와 평균 재직연수, 최대 재직연수를 출력
 select d.dept_name, 
		count(*), 
        avg(2025 - year_emp),
        max(2025 - year_emp)
from professor p join department d on p.dept_id = d.dept_id
group by d.dept_name
having avg(2025-year_emp)>=20;
 
 -- 25) '301호' 강의실에서 개설된 강좌의 과목명을 출력
 select title 
 from course co join class cl on co.course_id = cl.course_id
 where classroom = '301호';

 

4. 정리

이번 실습을 통해 학사 관리 데이터베이스에서 SQL을 활용하여 데이터를 조회, 분석하는 방법을 익혔습니다. 다양한 조건과 집계 함수를 사용하여 데이터를 효과적으로 다룰 수 있으며, 실전에서 필요한 SQL 실력을 향상할 수 있습니다.🚀