1
2
3
4
5
6
7
8
9
SQL> CREATE TABLE MAJOR (
  2    MAJORNO  NUMBER(3)  NOT NULL,
  3    MNAME  VARCHAR2(20) NOT NULL,
  4    BUILD VARCHAR2(20),
  5     PRIMARY KEY(MAJORNO)
  6  );
 
테이블이 생성되었습니다.
 
cs

1
2
3
4
5
6
7
8
9
10
SQL> CREATE TABLE STUDENT (
  2    STUDNO  NUMBER(4)  NOT NULL,
  3    SNAME  VARCHAR2(10) NOT NULL,
  4    GRADE  NUMBER,
  5    MAJORNO1  NUMBER(3),
  6    PRIMARY KEY(STUDNO),
  7    FOREIGN KEY(MAJORNO1) REFERENCES MAJOR(MAJORNO)
  8    );
 
테이블이 생성되었습니다.
cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> INSERT INTO MAJOR VALUES(101,'컴퓨터공학과','정보관');
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO MAJOR VALUES(102,'전자공학과','전자제어관');
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO MAJOR VALUES(103,'경영학과','인문관');
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO MAJOR VALUES(104,'경제학과',NULL);
 
1 개의 행이 만들어졌습니다.
 
SQL> SELECT * FROM MAJOR;
 
   MAJORNO MNAME                BUILD                                           
---------- -------------------- --------------------                            
       101 컴퓨터공학과         정보관                                          
       102 전자공학과           전자제어관                                      
       103 경영학과             인문관                                          
       104 경제학과                               
cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SQL> INSERT INTO STUDENT VALUES(9411,'서진수',4,101);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO STUDENT VALUES(9412,'서재수',4,102);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO STUDENT VALUES(9413,'이미경',4,103);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO STUDENT VALUES(9414,'김재수',4,102);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO STUDENT VALUES(9415,'박동호',4,102);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO STUDENT VALUES(9511,'김신영',3,101);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO STUDENT VALUES(9512,'신은경',3,102);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO STUDENT VALUES(9513,'오나라',3,103);
 
1 개의 행이 만들어졌습니다.
 
SQL> select * from student;
 
    STUDNO SNAME           GRADE   MAJORNO1                                     
---------- ---------- ---------- ----------                                     
      9411 서진수              4        101                                     
      9412 서재수              4        102                                     
      9413 이미경              4        103                                     
      9414 김재수              4        102                                     
      9415 박동호              4        102                                     
      9511 김신영              3        101                                     
      9512 신은경              3        102                                     
      9513 오나라              3        103                                     
 
cs


1
2
3
4
5
6
7
8
9
10
11
SQL> UPDATE STUDENT SET MAJORNO1=102 WHERE STUDNO=9411;
 
1 행이 갱신되었습니다.
 
SQL> SELECT * FROM STUDENT WHERE SNAME ='서진수';
 
    STUDNO SNAME           GRADE   MAJORNO1                                     
---------- ---------- ---------- ----------                                     
      9411 서진수              4        102 
 
 
cs


1
2
3
4
5
6
7
8
9
10
SQL> UPDATE STUDENT SET GRADE =4, MAJORNO1=103 WHERE SNAME='김신영';
 
1 행이 갱신되었습니다.
 
 
SQL> SELECT * FROM STUDENT WHERE SNAME='김신영';
 
    STUDNO SNAME           GRADE   MAJORNO1                                     
---------- ---------- ---------- ----------                                     
      9511 김신영              4        103     
cs

1
2
3
4
5
6
7
8
9
SQL> UPDATE MAJOR SET MNAME='통계학과' WHERE MAJORNO=104;
 
1 행이 갱신되었습니다.
 
SQL> SELECT * FROM MAJOR WHERE MNAME='통계학과';
 
   MAJORNO MNAME                BUILD                                           
---------- -------------------- --------------------                            
       104 통계학과                                    
cs


1
2
3
4
5
6
7
8
9
10
11
SQL> DELETE FROM MAJOR WHERE MNAME='통계학과';
 
1 행이 삭제되었습니다.
 
SQL> SELECT * FROM MAJOR;
 
   MAJORNO MNAME                BUILD                                           
---------- -------------------- --------------------                            
       101 컴퓨터공학과         정보관                                          
       102 전자공학과           전자제어관                                      
       103 경영학과             인문관         
cs


저작자표시

'Database > SQL' 카테고리의 다른 글

[SQL] SELECT 문 실습 - 조인 쿼리, 서브 쿼리  (0) 2018.03.05
[SQL] SELECT 문 실습 - 데이터 변경(2)  (0) 2018.03.05
[SQL] SELECT 문 실습 - 데이터 조회  (2) 2018.03.05

+ Recent posts

티스토리툴바