Select 연습(4)-서브쿼리

 

drop  table enrol;
drop  table student;
drop  table course;


create table student(
 sno varchar2(3) ,
 sname nvarchar2(10),
 year varchar2(10),
 dept nvarchar2(10) ,
 constraint   student_sno_pk   primary  key(sno));


create table course(
 cno varchar2(5) primary key,
 cname nvarchar2(10),
 credit varchar2(3),
 dept nvarchar2(10),
 prname nvarchar2(10));


create table enrol(
 sno varchar2(3) ,
 cno varchar2(5) ,
 grade varchar2(3),
 midterm int,
 final int ,
 constraint enrol_combo_pk primary key(sno, cno));


insert into student(sno,sname,year,dept)
values('100','나연묵','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('200','이찬영','3','전기');
insert into student(sno,sname,year,dept)
values('300','정기태','1','컴퓨터');
insert into student(sno,sname,year,dept)
values('400','송병호','4','컴퓨터');
insert into student(sno,sname,year,dept)
values('500','박종화','2','산공');

insert into student(sno,sname,year,dept)
values('600','하늘이','4','만화과');
insert into student(sno,sname,year,dept)
values('700','영심이','4','만화과');

insert into course(cno,cname,credit,dept,prname)
values('C123','프로그래밍','3','컴퓨터','김성기');
insert into course(cno,cname,credit,dept,prname)
values('C312','자료구조','3','컴퓨터','황수찬');
insert into course(cno,cname,credit,dept,prname)
values('C324','화일처리','3','컴퓨터','이규철');
insert into course(cno,cname,credit,dept,prname)
values('C413','데이터베이스','3','컴퓨터','이성호');
insert into course(cno,cname,credit,dept,prname)
values('E412','반도체','3','전자','홍봉희');

insert into course(cno,cname,credit,dept,prname)
values('F111','자동차정비','2','자동차','최동운');
insert into course(cno,cname,credit,dept,prname)
values('F222','드론프로그램','2','드론','이광재');
insert into course(cno,cname,credit,dept,prname)
values('F333','AI','2','인공지능','김박사');

insert into enrol(sno,cno,grade,midterm,final)
values('100','C413','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('100','E412','A',95,95);
insert into enrol(sno,cno,grade,midterm,final)
values('200','C123','B',85,80);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C312','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C324','C',75,75);
insert into enrol(sno,cno,grade,midterm,final)
values('300','C413','A',95,90);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C312','A',90,95);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C324','A',95,90);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C413','B',80,85);
insert into enrol(sno,cno,grade,midterm,final)
values('400','E412','C',65,75);
insert into enrol(sno,cno,grade,midterm,final)
values('500','C312','B',85,80);
insert into enrol(sno,cno,grade,midterm,final)
values('400','C123','A',90,90);

insert into enrol(sno,cno,grade,midterm,final)
values('901','F111','A',90,90);
insert into enrol(sno,cno,grade,midterm,final)
values('902','F111','A',90,90);
insert into enrol(sno,cno,grade,midterm,final)
values('903','F222','A',90,90);


select * from student;
select * from course;
select * from enrol;
​

 

 

1. 학번이 100번이고  과목번호가 C413 인 학생에 대해서 
중간고사 성적인 5점 업데이트( +5) 해 주세요.!!  

 

2.중간고사 성적이 80점 이하인 학생에 대해서 5점을 더해 주세요!!

 

3.전체 학생에 대해서 중간고사 성적을 1점 더해주세요.

 

1. 컴퓨터과 학생들의 점수를 중간, 기말 점수에서 -1점씩 진행하시오

 

2. 서브쿼리를 이용하여 화일처리 과목의 중간고사 평균과 기말고사 평균을 나타내시오

 

3. 서브쿼리를 이용하여 기말고사의 평균 성적이 85점 이상인 학생의 이름과 학과를 나타내시오

 

4. 서브쿼리를 이용하여 등록 테이블에서 기말고사 평균을 구하여 평균보다 성적이 좋은 학생의 이름을 구하시오.

 

 

2022.05.19 - [Oracle] - 4-3

4-3

2022.05.19 - [Oracle] - 4-예제3(서브쿼리)

4-예제3(서브쿼리)