4-예제2(join)

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_sno_fk foreign key(sno) references student(sno),
 constraint enrol_cno_fk foreign key(cno) references course(cno),
 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 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 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);


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



9.코드번호가 E로 시작하는 과목을 수강한 학생의
 학번과 과목이름, 과목코드, 성적을 나타내시오.

 select sno, cname, e.cno, grade
 from enrol e
 join course c
 on e.cno=c.cno
 where c.cno like'E%';

10. 이규철 교수의 과목을 수강한 학생은 몇명인가요

select count(*)"수강학생 수"
from course c
join enrol e
on c.cno=e.cno
where prname='이규철';

select *
from course c, enrol e, student s
where c.cno=e.cno and e.sno=s.sno and prname='이규철';


1. 학생 이름과 학년을 출력하시오.
select sname, year from student;

2. 3학년 학생을 출력하시오.
select * from student
where year='3';

3. 3학년과 4학년 학생을 출력하시오.
select * from student
where year='3' or year='4';

4. 이름이 '나연묵' 이고 4학년인 학생을 출력하시오.
select * from student
where sname='나연묵' and year='4';

5. 컴퓨터과 학생의 학년과 이름을 출력하시오.
select year, sname from student
where dept='컴퓨터';

6. 정기태 학생의 학년과 학과를 출력하시오. 
select year, dept from student
where sname='정기태';

7. 컴퓨터과가 아닌 학생의 이름과 학과를 출력하시오.
select sname, dept from student
where dept != '컴퓨터';

8. 학번이 100번, 300번, 500번인 학생의 학번과 이름 학과를 출력하시오.
select sno, sname, dept from student
where sno in(100,300,500);

9. 이름의 첫글자가 "정" 과 "박" 이름 시작하는 사람의 학번과 이름을 출력하시오.
select sno, sname from student
where sname like'정%' or sname like'박%';

10. 3학년 또는 4학년 학생이 아닌 학생의 학번과 이름 , 학년을 출력하시오. 
select sno, sname, year from student
where year!='3' and year!='4';

1. 등록테이블에서 100 번 학생의 레코드 개수를 출력하시오.
select count(*) from enrol
where sno ='100';

2. 성적이 A 인 레코드 개수를 출력하시오.
select count(*) from enrol
where grade='A';

3. 300번 학생의 중간성적, 기말성적의 합과 평균을 나타내시오.

-- 300번 학생이라는 학번도 보여주고싶음 

select sno, midterm, final, (midterm+final) 합계, (midterm+final)/2 평균
from enrol
where sno='300';
-- 한명의 중간과 기말을 합쳐서 낸 일년의 합과 평균

select sno, sum(midterm), sum(final), round(avg(midterm),1),round(avg(final),1)
from enrol
group by sno
having sno='300';

-- 한명의 모든 중간 합과 평균 모든 기말 합과 평균

-- 300번이라는 sno를 나타내고 싶은데 sum, avg를 썼으니 그룹화 해줘야함

 

select sno, sum(midterm), sum(final), round(avg(midterm),1),round(avg(final),1)
from enrol
where sno='300'
group by sno;

4. c413 과목의 중간고사 최고점, 최저점을 출력하시오.
select max(midterm), min(midterm) from enrol
where  cno='C413';

 

select cno, max(midterm), min(midterm) 
from enrol
group by cno
having cno='C413';

5. 200번 학생의 학번, 이름, 학년, 중간성적, 기말성적, 평점, 합계, 평균을 나타내시오.
select s.sno, sname, year, midterm, final, grade, (midterm+final) 합계, (midterm+final)/2 평균
from student s
join enrol e
on s.sno=e.sno
where s.sno='200';

6. 학번별 수강과목의 갯수를 출력하시오.
select sno, count(*) 
from enrol
group by sno;

select sno, count(*) 
from enrol
group by sno;
having sno='100';

7. 성적이 A인 레코드 개수를 출력하시오
select grade, count(*)
from enrol
group by grade
having grade='A';

 

8. 전기과,산공과,컴퓨터과 학생이 수강한 과목의 개수를 출력하시오 
select dept, count(dept)
from student s
join enrol e
on s.sno=e.sno
where dept in('전기','산공','컴퓨터')
group by dept;

select dept, count(dept)
from student s, enrol e
where s.sno=e.sno and dept in('전기','산공','컴퓨터')
group by dept;

 

select dept, count(dept)
from student s, enrol e
where s.sno=e.sno  
group by dept
having dept in('전기','산공','컴퓨터');

 

9.과목이름이 화일처리인 과목의 중간평균과 기말평균을 나타내시오

--시험에는 출력값이 무엇인지 확실하게 나와서 과목이름 안나와도 된대용

 

select cname 과목이름, avg(midterm) 중간평균, avg(final) 기말평균
from course c, enrol e
where c.cno=e.cno 
group by cname
having cname='화일처리';