정처기 데베과목

3

create table patient(
	id char(5) primary key,
    name char(10),
    sex char(1),
    phone char(20),
    constraint sex_ck check(sex in('f','m')),
    constraint id_fk foreign key(id) references doctor(doc_id)
    );

 

4

create table Instructor(
	id char(5) primary key,
    name char(15) not null,
    dept char(15) references Department(dept) on delete set null on update cascade
    );

 

5 속성 추가

 

alter table patient add job char(20);

 

6 뷰만들기

 

create view cc 

as

select c.id as ccid, c.name as ccname, i.name as instname

from Course c, Instructor i

where c.id = i.id;

 

8 인덱스

 

create unique index Studex_id on Student(ssn);

 

40쪽

 

7

select 학생정보.학번, 학생정보.이름, 결제.결제여부
from 학번, 학생정보, 신청정보
where 학생정보.학변 = 신청정보.학번 
and 신청정보.신청번호 = 결제.신청번호 
and 신청정보.신청과목 = 'OpenGL';

 

51쪽

 

1

select 학과, count(*) as 학과별튜플수
from 학생
group by 학과;

 

2

select 과목이름, min(점수) as 최소점수, max(점수) as 최대점수
from 성적
group by 과목이름
having avg(점수) >= 90;

 

5

 

select sum(psale) from Sale s
where id in (select id from Product where name = 'usb');