[Oracle] 6. 서브쿼리(SUBQURY), DML(INSERT, UPDATE, DELETE), DDL(CREATE, ALTER, DROP), TCL(COMMIT, ROLLBACK) TEST 풀이 [Feat. 제약조건]

-- 1. 학생 테이블에서 지도교수가 없는 학생의 지도교수를 이서진학생의 지도교수로 변경하기
-- 이서진 학생의 지도교수
SELECT PROFNO
  FROM STUDENT
  WHERE NAME = '이서진'
  ;
-- 지도교수가 없는 학생 
SELECT NAME
  FROM STUDENT
  WHERE PROFNO IS NULL
  ;  
-- 학생 테이블에서 지도교수가 없는 학생의 지도교수를 이서진학생의 지도교수로 변경
UPDATE STUDENT SET PROFNO = 
 (SELECT PROFNO
  FROM STUDENT
  WHERE NAME = '이서진')
  WHERE PROFNO IS NULL
  ;
-- 결과
SELECT * 
  FROM STUDENT
  WHERE GRADE = 1
  ;

-- 2,3번 관련  문장을 실행 하세요.
drop table member2 -- MEMEBER2 테이블 제거.
;
create table member2(
USERID varchar2(10) PRIMARY KEY, -- 기본키 설정 : USERID 컬럼의 값은 중복안됨. NULL값 안됨.
USERNAME varchar2(10),
PASSWD varchar2(10),
PHONE varchar2(13),
ADDRESS varchar2(20),
REGDATE date
);
/*
  create table student 
  ( studno number(4) primary key,              --학번   : 제약조건. 기본키설정. NOT NULL, 중복불가
  name   varchar2(10) not null,                --이름   : 제약조건. NOT NULL. 중복가능
  id varchar2(20) not null unique,             --아이디 : 제약조건. NOT NULL. 중복불가
  grade number check(grade between 1 and 6),   --학년   : 제약조건. 숫자로만 입력. 1 ~ 6 사이의 값만 허용
  jumin char(13) not null,
  birthday  date,
  tel varchar2(15),
  height  number(4),
  weight  number(3),
  deptno1 number(3),
  deptno2 number(3),
  profno  number(4)) tablespace users;
*/
-- 2. 회원테이블에 다음 회원정보를 저장하라
--이름 : 이순신
--아이디: sunshinLee
--비밀번호 : ssl000
--전화번호 : 02)333-2123
--주소 : 서울
--등록일자 : 오늘
SELECT *
  FROM MEMBER2
  ;

INSERT INTO MEMBER2 (USERNAME, USERID, PASSWD, PHONE, ADDRESS, REGDATE)
  VALUES ('이순신', 'sunshinLee', 'ssl000', '02)333-2123', '서울', SYSDATE)
  ;

-- 3. 학생테이블로부터 컴퓨터 공학과 학생들을 회원테이블에 추가하기
-- userid,는 학생테이블의 id로 이름, 전화번호, 등록일자는 오늘로 하고, 
-- 비밀번호는 주민번호 앞 6자리로한다.
/*
insert into 테이블명 (컬럼명1,컬럼명2,.....)
select 구문           => 기존테이블의 내용

예)
insert into dept3 (dcode,dname)
select dcode, dname from dept2
where dcode = 0001
*/
-- 학생테이블로부터 컴퓨터 공학과 학생들
SELECT *
  FROM STUDENT
  WHERE DEPTNO1 = 
  (SELECT DEPTNO 
  FROM DEPARTMENT 
  WHERE DNAME = '컴퓨터공학과')
  ;
-- 학생테이블로부터 컴퓨터 공학과 학생들을 MEMBER2 테이블에 추가
INSERT INTO MEMBER2 (USERID, USERNAME, PHONE, PASSWD, REGDATE)
  SELECT ID, NAME, TEL, SUBSTR(JUMIN,1,6), SYSDATE
  FROM STUDENT
  WHERE DEPTNO1 = 
  (SELECT DEPTNO 
  FROM DEPARTMENT 
  WHERE DNAME = '컴퓨터공학과')
  ;
-- 결과  
SELECT *
  FROM MEMBER2
  ;
-- 4. 학생테이블 중 1학년 학생의 키정보를 저장하는 height_info1 테이블을 작성하라. 
-- 단, 컬럼은 학번, 학년, 키 로 구성된다.
CREATE TABLE HEIGHT_INFO1
  AS SELECT STUDNO, GRADE, HEIGHT
  FROM STUDENT
  WHERE GRADE = 1
  ;
-- 결과  
SELECT * 
  FROM HEIGHT_INFO1
  ;
-- 5. 학생테이블 중 1학년 학생의 몸무게정보를 저장하는 weight_info1 테이블을 작성하라. 
-- 단, 컬럼은 학번, 학년, 몸무게 으로 구성된다.

CREATE TABLE WEIGHT_INFO1
  AS SELECT STUDNO, GRADE, WEIGHT
  FROM STUDENT
  WHERE GRADE = 1
  ;
-- 결과  
SELECT * 
  FROM WEIGHT_INFO1
  ;