10/11 수업

create table ENROL (
  sno number(3) references STUDENT(sno),  
  cno char(4) not null,
  grade char,
  midterm integer,
  finterm integer,
  primary key (sno, cno),
  foreign key (sno) references STUDENT(sno)   둘다 같다.
 on delete cascade,
  foreign key (cno) references COURSE
 on delete cascade,
  check (grade in ('A', 'B', 'C', 'D', 'F'))
  );

 

 

 DML 데이터 추가 삭제 갱신

 

 

프린트 페이지 5 문제 답

select sname from student where year = 4 and dept = '산공';
select sno from enrol where midterm >=90 or finterm>=90;
select sno from enrol where midterm < finterm;

 

select sname,sno,year+1 as 내년학년 from student where dept like '컴퓨터%';
=> 사용 기법 : 엘리어스 와 like 연산자.

 

select * from student order by YEAR desc;  내림 차순
select * from student order by YEAR asc;    오름 차순

=> 사용 기법 : order by 구문( 오름차순 및 내림 차순 구분 기능)

 

select * from student order by YEAR desc, sno asc; 
같은 학년을 만나면 학번이 낮은 것이 위로 올라간다

 

select * from student order by YEAR desc, sno desc;
반대의 경우

---

 

관계형 DB는 모든 값이 릴레이션이다. 즉, 집합 개념

 

but, SQL 은 table 이다.

 

이 문제를 해결하는 데에는 "distinct" 함수를 사용하면 중복을 해결할 수 있다.

---

 

select sname from student where year = 2 and dept like '산_';

like 연산자에서

 

1) '산_' 이렇게 되있는거는 단일의 임의의 문자 (여기서는 2글자)

2) '산%' 이런게 되어있으면 '산'을 포함한 뒤에 오는 아무 문자나 와도 조건에 충족된다.

 

C & JAVA VS SQL

 

=> c나 java는 많은 정보를 출력하기 위해서는 roof를 사용한다. (low level 하다.)

   하지만 SQL은 답을 찾아가는 상세한 과정을 기술하지 않고 바로 답을 찾아간다.

(선언적이다.)

 

특정 시점에서 하나의 record만 가지고 일한다. (C나 JAVA)

입력이 몽땅 집합 출력도 집합 collection을 한꺼번에 다룬다. (SQL)

 

 

select max(finterm) as 기말고사최대점 from enrol;   기말고사의 최고점을 알수 있다.
select min(finterm) as 기말고사최대점 from enrol;   기말고사의 최저점을 알수 있다.

select avg(finterm) as 기말고사평균 from enrol;
select count(finterm) as 기말고사친총원 from enrol;
select sum(finterm) as 기말고사합 from enrol;

 

but first() 와 last()는 오라클에서 지원을 하지않는다.

 

 

※group by

select cno as 과목번호,count(*) as 수강인원 from enrol group by cno;
-- 과목별로 수강인원
select cno as 과목번호,max(finterm) as 기말고사최고점 from enrol group by cno;
-- 과목별로 기말고사 최고점
select cno as 과목번호,avg(finterm) as 기말평균,avg(midterm) as 중간평균 from enrol group by cno;
-- 과목별로 기말고사,중간고사 평균

 

 

 

 

-- commit

drop table ENROL;
drop table STUDENT;
drop table COURSE;

create table STUDENT (
  sno number(3) primary key,
  sname nvarchar2(4) constraint sname_always_exists not null,
  year number(1) default 1,
  dept nvarchar2(5),
  unique (dept, sname),
  constraint year_check check (year >=1 and year <=4 )
);

 insert into STUDENT values (100, '나수영', 4, '컴퓨터');
 insert into STUDENT values (200, '이찬수', 3, '전기');
 insert into STUDENT values (300, '정기태', 1, '컴퓨터');
 insert into STUDENT values (400, '송병길', 4, '컴퓨터');
 insert into STUDENT values (500, '박종화', 2, '산공');
 
create table COURSE (
  cno char(4),
  cname nvarchar2(10) not null,
  credit number(1) not null,
  dept nvarchar2(4) not null,
  professor nvarchar2(4),
  primary key(cno),
  unique (cno,professor)
);

insert into COURSE values ('C123', 'C프로그래밍',  3, '컴퓨터', '김성국');
insert into COURSE values ('C312', '자료구조',    3, '컴퓨터', '황수관');
insert into COURSE values ('C324', '화일구조',    3, '컴퓨터', '이규찬');
insert into COURSE values ('C413', '데이터베이스', 3, '컴퓨터', '이일로');
insert into COURSE values ('E412', '반도체',    3, '전자',   '홍봉진');
 
 
  create table ENROL (
  sno number(3) references STUDENT(sno),
  cno char(4) not null,
  grade char,
  midterm integer,
  finterm integer,
  primary key (sno, cno),
  foreign key (sno) references STUDENT(sno)
 on delete cascade,
  foreign key (cno) references COURSE
 on delete cascade,
  check (grade in ('A', 'B', 'C', 'D', 'F'))
  );
 
insert into ENROL values (100, 'C413', 'A', 90, 95);
insert into ENROL values (100, 'E412', 'A', 95, 95);
insert into ENROL values (200, 'C123', 'B', 85, 80);
insert into ENROL values (300, 'C312', 'A', 90, 95);
insert into ENROL values (300, 'C324', 'C', 75, 75);
insert into ENROL values (300, 'C413', 'A', 95, 90);
insert into ENROL values (400, 'C312', 'A', 90, 95);
insert into ENROL values (400, 'C324', 'A', 95, 90);
insert into ENROL values (400, 'C413', 'B', 80, 85);
insert into ENROL values (400, 'E412', 'C', 65, 75);
insert into ENROL values (500, 'C312', 'B', 85, 80);

commit;

select * from student order by YEAR desc;
select * from student order by YEAR desc, sno asc;
select * from student order by YEAR desc, sno desc;

select * from enrol;

select sname,sno,year+1 from student;
select sname,sno,year from student;
select sname,sno,year+1 as 내년학년 from student where dept like '컴퓨터%';

select sname from student where year = 2 and dept like '산_';
select distinct sno from enrol where midterm >=90 or finterm>=90;
select distinct sno from enrol where midterm < finterm;

select * from student where dept like '%컴%' order by year asc;

select * from student where year in (1,2) order by year desc;
select sno as 학번 from enrol where  midterm between 80 and 90;

select * from dual;

select max(finterm) as 기말고사최대점 from enrol;
select min(finterm) as 기말고사최저점 from enrol;

select min(finterm) as 기말고사최저점 from enrol where cno= 'C413';

select avg(finterm) as 기말고사평균 from enrol;
select count(finterm) as 기말고사친총원 from enrol;
select sum(finterm) as 기말고사합 from enrol;

--'C413' 과목을 수강한 학생는 몇 명인가?
select *from enrol;
select count(cno) as 학생수 from enrol where cno ='C413';

select cno as 과목번호,count(*) as 수강인원 from enrol group by cno;
-- 과목별로 수강인원
select cno as 과목번호,max(finterm) as 기말고사최고점 from enrol group by cno;
-- 과목별로 기말고사 최고점
select cno as 과목번호,avg(finterm) as 기말평균,avg(midterm) as 중간평균 from enrol group by cno;
-- 과목별로 기말고사,중간고사 평균

select *from all_catalog;
select *from ALL_TABLES where owner = ' ';