오라클 중심의 SQL 배움터 10장 연습문제 풀이

(1)

-- 묵시적
INSERT INTO professor(profno, name, deptno)
VALUES (9910, '이순신', 201);

-- 명시적
INSERT INTO professor
VALUES (9910, '이순신', NULL, NULL, NULL, NULL, NULL, 201);

 

(2)

UPDATE professor
SET userid='sunshinlee', hiredate='05/01/01'
WHERE name='이순신';

 

(3)

UPDATE professor
SET position = (SELECT position FROM professor WHERE userid='Pascal'),
    sal = (SELECT sal FROM professor WHERE profno=9908)
WHERE name='이순신';

 

(6)

DELETE professor
WHERE profno=9910;

 

(7)

UPDATE professor
SET comm = (SELECT MIN(comm)
            FROM professor)
WHERE comm IS NULL;

 

(8)

CREATE TABLE height_info(
studno NUMBER(5),
name VARCHAR2(30),
height NUMBER(5,2)
);
CREATE TABLE weight_info(
studno NUMBER(5),
name VARCHAR2(30),
height NUMBER(5,2)
);

INSERT ALL
INTO height_info VALUES(studno, name, height)
INTO weight_info VALUES(studno, name, weight)
SELECT studno, name, height, weight FROM student WHERE grade='1';

 

(10)

CREATE SEQUENCE s_board
INCREMENT BY 2
START WITH 2
MAXVALUE 100;