oracle(DB연습문제)_2024-06-27

DB설계_학생데이터베이스설계조건.hwp

0.04MB

--drop table student;
--drop table enrollment;
--drop table course;
--drop table professor;


--1.학생 테이블 생성
create table student      
       (student_id number primary key,
        student_name varchar2(50),
        student_grade number,
        student_major varchar2(50),
        student_number varchar2(30)
        );
       
--2.수강 테이블(Enrollment)방법
create table enrollment
       (enrollment_id number primary key,
        student_id number,
        course_code varchar2(10),
        CONSTRAINT fk_enrollment_student_id FOREIGN KEY (student_id) REFERENCES student(student_id),
        CONSTRAINT FK_ENROLLMENT_COURSE_CODE FOREIGN KEY (COURSE_CODE) REFERENCES COURSE(COURSE_CODE),
        enrollment_credits number,
        enrollment_grade number
        );
       
--3.방법과목 테이블(Course)방법
create table course      
       (course_code varchar2(10) primary key,
        course_name varchar2(100) not NULL,
        student_grade varchar2(30),
        professor_id varchar2(20),
        CONSTRAINT fk_course_professor_id FOREIGN KEY (professor_id) REFERENCES professor(professor_id)
        );
       
--4.방법교수 테이블(Professor)방법
create table professor      
       (professor_id varchar2(20) primary key,
        professor_name char(30),
        professor_department char(30),
        professor_email char(100)
        );

--1.학생 테이블(Student)
insert into student values(20230001, '김민수', 2, '경영학과', '010-1234-5678');
insert into student values(20230002, '이영희', 3, '컴퓨터공학과', '010-2345-6789');
insert into student values(20230003, '박철수', 1, '경제학과', '010-3456-7890');
insert into student values(20230004, '최지영', 4, '법학과', '010-4567-8901');
insert into student values(20230005, '강민호', 2, '건축학과', '010-5678-9012');
commit;        

--2.수강 테이블(Enrollment)
insert into enrollment values(1001, 20230001, 'CS101', 3, 4.0);
insert into enrollment values(1002, 20230002, 'BUS201', 4, 3.5);
insert into enrollment values(1003, 20230003, 'ECO301', 3, 4.3);
insert into enrollment values(1004, 20230004, 'LAW401', 4, 4.0);
insert into enrollment values(1005, 20230005, 'ARC501', 3, 3.7);
commit;

--3.과목 테이블(Course)
insert into course values('CS101', '프로그래밍 기초' , 3, 'P001');
insert into course values('BUS201', '경영학 기초' , 4, 'P002');
insert into course values('ECO301', '미시경제학' , 3, 'P003');
insert into course values('LAW401', '민법 기초' , 4, 'P004');
insert into course values('ARC501', '건축 설계' , 3, 'P005');
commit;

--4.교수 테이블(Professor)
insert into professor values('P001', '김교수', '컴퓨터공학과', 'kim@university.edu');
insert into professor values('P002', '이교수', '경영학과', 'lee@university.edu');
insert into professor values('P003', '박교수', '경제학과', 'park@university.edu');
insert into professor values('P004', '강교수', '법학과', 'choi@university.edu');
insert into professor values('P005', '강교수', '건축학과', 'kang@university.edu');
commit;

 

DB설계_학생데이터베이스설계조건.hwp

0.04MB

1.
select student_name as 이름, student_grade as 학년
from student;

2.
select course_name as 과목명, professor_id as 교수ID
from course
where student_grade = 3;

3.
select *
from student
where student_major = '경영학과';

4.

select*
from enrollment
where enrollment_grade >= 4;

5.
select *
from student
order by student_grade asc;

6.
select *
from professor
order by professor_name asc;

7.
SELECT COUNT(*) AS 학생수,
       AVG(student_grade) AS 평균학년    
FROM student;

8.
select sum(student_grade) as "개설된 과목의 총 학점"
from course;

9.
SELECT A.student_name as 이름,
       A.student_major as 전공,
       B.enrollment_credits as 학점,
       B.enrollment_grade as 성적
FROM student A
INNER JOIN enrollment B ON A.student_id = B.student_id;

10.
SELECT A.course_name as 과목명,
       B.professor_name as 이름,
       B.professor_department as 학과
FROM course A
INNER JOIN professor B ON A.professor_id = B.professor_id;

11.
SELECT A.student_name as 이름,
       B.course_name as 과목명,
       C.enrollment_grade as 성적
FROM Enrollment C
JOIN Student A ON C.student_id = A.student_id
JOIN Course B ON C.course_code = B.course_code
WHERE c.enrollment_grade= 4;

12.
select avg(enrollment_grade)
from student A
join enrollment B ON A.student_id = B.student_id
join course C ON B.course_code = C.course_code
where A.student_major = '경영학과';

13.
SELECT A.student_name as 이름,
       B.enrollment_grade as 성적,
       C.course_name as 과목명,  
       D.professor_name as 교수이름
FROM student A
JOIN enrollment B ON A.student_id = B.student_id
JOIN course C ON B.course_code = C.course_code
JOIN professor D ON C.professor_id = D.professor_id
WHERE B.enrollment_grade>= 3;