2020-09-14, SQL_5일차_연습문제

---

----1
update tb_department
set capacity = round(capacity * 1.1);

---

 

---

---2
update tb_student
set student_address = '서울시 종로구 숭인동 181-21'
where student_no = 'A413042';

---

 

---

---3
update tb_student
set student_ssn = substr(student_ssn, 1, 6);

---

 

---

---4
update tb_grade
set point = 3.5
where term_no = '200501' 
    and student_no = (select student_no
                        from tb_student join tb_department using (department_no)
                        where student_name = '김명훈'
                        and department_name = '의학과')
    and class_no = (select class_no
                    from tb_class
                    where class_name = '피부생리학');

---

 

---

---5
DELETE
FROM tb_grade
WHERE student_no in (SELECT student_no
                    FROM tb_student
                    WHERE absence_yn = 'Y');

---

 

---

CREATE TABLE subject
( subno NUMBER(5) CONSTRAINT subject_no_pk PRIMARY KEY,
    subname VARCHAR2(20) CONSTRAINT subject_name_nn NOT NULL,
    term VARCHAR2(1) CONSTRAINT subject_term_ck check (term in ('1', '2') ),
    type VARCHAR2(4) CONSTRAINT subject_type_ck check ( type in ('필수', '선택'))
);

---

 

---

 

CREATE TABLE sugang
( studno NUMBER(5) CONSTRAINT student_no_pk_fk REFERENCES student(studno),
    subno number(5) CONSTRAINT subject_no_pk_kf REFERENCES subject(subno),
    regdate date,
    resut number(3),
        constraint sugang_resut_pk primary key (studno, subno) );

---