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
SQL> CREATE TABLE MAJOR (
  2      MNUM    NUMBER(3) NOT NULL,
  3      MNAME VARCHAR2(20) NOT NULL,
  4      BUILD VARCHAR2(20),
  5      PRIMARY KEY(MNUM)
  6  );
 
테이블이 생성되었습니다.
 
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,'경제학과');
INSERT INTO MAJOR VALUES(104,'경제학과')
            *
1행에 오류:
ORA-00947: 값의 수가 충분하지 않습니다 
 
 
SQL> INSERT INTO MAJOR (MNUM,MNAME) VALUES(104,'경제학과');
 
1 개의 행이 만들어졌습니다.
 
SQL> select * from major;
 
      MNUM MNAME                BUILD                                           
---------- -------------------- --------------------                            
       101 컴퓨터공학과         정보관                                          
       102 전자공학과           전자제어관                                      
       103 경영학과             인문관                                          
       104 경제학과                                                             
 
SQL> spool off;
 
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
SQL> CREATE TABLE STUDENT (
  2      SNUM    NUMBER(4) NOT NULL,
  3      SNAME VARCHAR2(10) NOT NULL,
  4      GRADE NUMBER,
  5  MNUM NUMBER(3),
  6      PRIMARY KEY(SNUM),
  7        FOREIGN KEY(MNUM) REFERENCES MAJOR(MNUM)
  8  );
 
테이블이 생성되었습니다.
 
SQL> desc student;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 SNUM                                      NOT NULL NUMBER(4)
 SNAME                                     NOT NULL VARCHAR2(10)
 GRADE                                              NUMBER
 MNUM                                               NUMBER(3)
 
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;
 
      SNUM SNAME           GRADE       MNUM                                     
---------- ---------- ---------- ----------                                     
      9411 서진수              4        101                                     
      9412 서재수              4        102                                     
      9413 이미경              4        103                                     
      9414 김재수              4        102                                     
      9415 박동호              4        102                                     
      9511 김신영              3        101                                     
      9512 신은경              3        102                                     
      9513 오나라              3        103                                     
 
8 개의 행이 선택되었습니다.
 
SQL> SPOOL OFF;
 
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> SELECT SNAME,MNAME FROM STUDENT S, MAJOR M WHERE S.MNUM=E.MNUM;
SELECT SNAME,MNAME FROM STUDENT S, MAJOR M WHERE S.MNUM=E.MNUM
                                                        *
1행에 오류:
ORA-00904: "E"."MNUM": 부적합한 식별자 
 
 
SQL> SELECT SNAME,MNAME FROM STUDENT S, MAJOR M WHERE S.MNUM=M.MNUM;
 
SNAME      MNAME                                                                
---------- --------------------                                                 
서진수     컴퓨터공학과                                                         
서재수     전자공학과                                                           
이미경     경영학과                                                             
김재수     전자공학과                                                           
박동호     전자공학과                                                           
김신영     컴퓨터공학과                                                         
신은경     전자공학과                                                           
오나라     경영학과                                                             
 
8 개의 행이 선택되었습니다.
 
SQL> SPOOL OFF;
 
cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> SELECT SNAME FROM STUDENT WHERE MNUM=(SELECT MNUM FROM MAJOR WHERE MNAME='전자공학과')
  2  UNION
  3  SELECT SNAME FROM STUDENT WHERE MNUM=(SELECT MNUM FROM STUDENT WHERE SNAME='서진수');
 
SNAME                                                                           
----------                                                                      
김신영                                                                          
김재수                                                                          
박동호                                                                          
서재수                                                                          
서진수                                                                          
신은경                                                                          
 
6 개의 행이 선택되었습니다.
 
SQL> SPOOL OFF;
cs


1
2
3
4
5
6
7
8
9
SQL> SELECT SNAME, SNUM FROM MAJOR M, STUDENT S WHERE M.MNUM=S.MNUM AND (M.MNAME='경영학과' OR M.MNAME='경제학과');
 
SNAME            SNUM                                                           
---------- ----------                                                           
이미경           9413                                                           
오나라           9513                                                           
 
SQL> SPOOL OFF;
 
cs


1
2
3
4
5
6
7
8
9
10
SQL> SELECT SNAME, SNUM FROM STUDENT S WHERE EXISTS
  2  (SELECT MNUM FROM MAJOR M WHERE S.MNUM=M.MNUM AND(M.MNAME='컴퓨터공학과' OR M.MNAME='경제학과'));
 
SNAME            SNUM                                                           
---------- ----------                                                           
김신영           9511                                                           
서진수           9411                                                           
 
SQL> SPOOL OFF
 
cs


저작자표시

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

[SQL] SELECT 문 실습 - 데이터 변경(2)  (0) 2018.03.05
[SQL] SELECT 문 실습 - 데이터 변경  (0) 2018.03.05
[SQL] SELECT 문 실습 - 데이터 조회  (2) 2018.03.05

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
46
47
48
49
50
51
52
53
54
55
 
SQL> CREATE TABLE HOME (
  2      HOMENO    NUMBER,
  3      HOMENAME VARCHAR2(10),
  4      MANAGER    VARCHAR2(15),
  5      SALARY     NUMBER
  6  );
 
테이블이 생성되었습니다.
 
 
SQL> desc home;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 HOMENO                                             NUMBER
 HOMENAME                                           VARCHAR2(10)
 MANAGER                                            VARCHAR2(15)
 SALARY                                             NUMBER
 
SQL> 
SQL> INSERT INTO HOME VALUES(2106, 'Kims', '김창섭', 5000000);
 
1 개의 행이 만들어졌습니다.
 
SQL> 
SQL> INSERT INTO HOME VALUES(3423, 'Parks', '박영권', 5300000);
 
1 개의 행이 만들어졌습니다.
 
SQL> 
SQL> INSERT INTO HOME VALUES(3011, 'Lees', '이수민', 6400000);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO HOME VALUES(1003, 'Joos', '주민희', 4700000);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO HOME VALUES(3427, 'Chois', '최종철', 3500000);
 
1 개의 행이 만들어졌습니다.
 
SQL> commit;
 
커밋이 완료되었습니다.
 
SQL> select * from home;
 
    HOMENO HOMENAME   MANAGER             SALARY                                
---------- ---------- --------------- ----------                                
      2106 Kims       김창섭             5000000                                
      3423 Parks      박영권             5300000                                
      3011 Lees       이수민             6400000                                
      1003 Joos       주민희             4700000                                
      3427 Chois      최종철             3500000     
cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> ALTER TABLE HOME ADD CONSTRAINT HOME_PK PRIMARY KEY(HOMENO);
 
테이블이 변경되었습니다.
 
SQL> ALTER TABLE HOME DROP COLUMN SALARY;
 
테이블이 변경되었습니다.
 
SQL> DESC HOME;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 HOMENO                                    NOT NULL NUMBER
 HOMENAME                                           VARCHAR2(10)
 MANAGER                                            VARCHAR2(15)
 
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='HOME';
 
TABLE_NAME                     CONSTRAINT_NAME                C                 
------------------------------ ------------------------------ -                 
HOME                           HOME_PK                        P                 
 
SQL> 
SQL> spool off;  
cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
QL> CREATE TABLE MANAHOME (
  2  MANAGERNO NUMBER NOT NULL,
  3  HOMENAME VARCHAR2(10) UNIQUE,
  4  SALARY NUMBER CHECK(SALARY<=6000000),
  5  JOB VARCHAR2(15) DEFAULT '개발자',
  6  PRIMARY KEY(MANAGERNO)
  7  );
 
테이블이 생성되었습니다.
 
SQL> DESC MANAHOME;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 MANAGERNO                                 NOT NULL NUMBER
 HOMENAME                                           VARCHAR2(10)
 SALARY                                             NUMBER
 JOB                                                VARCHAR2(15)
 
SQL> SPOOL OFF;
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
SQL> ALTER TABLE MANAHOME ADD (HNO NUMBER);
 
테이블이 변경되었습니다.
 
SQL> desc manahome;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 MANAGERNO                                 NOT NULL NUMBER
 HOMENAME                                           VARCHAR2(10)
 SALARY                                             NUMBER
 JOB                                                VARCHAR2(15)
 HNO                                                NUMBER
 
 
 
SQL> INSERT INTO MANAHOME VALUES(5454, 'Kims', 5000000, '분석가', 2106);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO MANAHOME VALUES(4215, 'Lees', 4500000, '승무원', 3011);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO MANAHOME VALUES(2452, 'Joos', 3200000, '분석가', 1033);
 
1 개의 행이 만들어졌습니다.
 
SQL> INSERT INTO MANAHOME(MANAGERNO, HOMENAME, SALARY, HNO) VALUES(4852, 'Chois', 3000000, 3427);
 
1 개의 행이 만들어졌습니다.
 
SQL> select* from manahome;
 
 MANAGERNO HOMENAME       SALARY JOB                    HNO                     
---------- ---------- ---------- --------------- ----------                     
      5454 Kims          5000000 분석가                2106                     
      4215 Lees          4500000 승무원                3011                     
      2452 Joos          3200000 분석가                1033                     
      4852 Chois         3000000 개발자                3427                     
 
SQL> spool off;
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
 
SQL> SELECT * FROM HOME;
 
    HOMENO HOMENAME   MANAGER                                                   
---------- ---------- ---------------                                           
      2106 Kims       김창섭                                                    
      3423 Parks      박영권                                                    
      3011 Lees       이수민                                                    
      1003 Joos       주민희                                                    
      3427 Chois      최종철                                                    
 
##기존에 MANAHOME에 들어있던 데이터를 잘못 입력 (1033인데 1003으로 잘못)
## 하여 해당 행 수정 후 진행하였습니다
 
 
SQL> UPDATE MANAHOME SET HNO=1003 WHERE MANAGERNO=2452;
 
1 행이 갱신되었습니다.
 
SQL> SELECT * FROM MANAHOME;
 
 MANAGERNO HOMENAME       SALARY JOB                    HNO                     
---------- ---------- ---------- --------------- ----------                     
      5454 Kims          5000000 분석가                2106                     
      4215 Lees          4500000 승무원                3011                     
      2452 Joos          3200000 분석가                1003                     
      4852 Chois         3000000 개발자                3427                     
 
SQL> ALTER TABLE MANAHOME ADD CONSTRAINT MANA_FK FOREIGN KEY(HNO) REFERENCES HOME(HOMENO);
 
테이블이 변경되었습니다.
 
SQL> INSERT INTO MANAHOME VALUES(3235, 'Lees',2000000,'관리자',1111);
INSERT INTO MANAHOME VALUES(3235, 'Lees',2000000,'관리자',1111)
*
1행에 오류:
ORA-00001: 무결성 제약 조건(JS.SYS_C0011622)에 위배됩니다 
 
cs


저작자표시

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

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


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



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
SQL> SELECT ENAME AS 사원이름, EMPNO AS 사원번호, HIREDATE AS 고용일 FROM EMP ORDER BY 사원번호 DESC;
 
사원이름     사원번호 고용일                                                    
---------- ---------- --------                                                  
MILLER           7934 82/01/23                                                  
FORD             7902 81/12/03                                                  
JAMES            7900 81/12/03                                                  
ADAMS            7876 87/05/23                                                  
TURNER           7844 81/09/08                                                  
KING             7839 81/11/17                                                  
SCOTT            7788 87/04/19                                                  
CLARK            7782 81/06/09                                                  
BLAKE            7698 81/05/01                                                  
MARTIN           7654 81/09/28                                                  
JONES            7566 81/04/02                                                  
 
사원이름     사원번호 고용일                                                    
---------- ---------- --------                                                  
WARD             7521 81/02/22                                                  
ALLEN            7499 81/02/20                                                  
SMITH            7369 80/12/17                                                  
 
14 개의 행이 선택되었습니다.
 
 
 
cs


1
2
3
4
5
6
7
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL BETWEEN 3000 AND 5000;
 
     EMPNO ENAME      JOB              SAL                                      
---------- ---------- --------- ----------                                      
      7788 SCOTT      ANALYST         3000                                      
      7839 KING       PRESIDENT       5000                                      
      7902 FORD       ANALYST         3000                            
cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> SELECT DEPTNO, ENAME, JOB, SAL*3 AS SAL FROM EMP;
 
    DEPTNO ENAME      JOB              SAL                                      
---------- ---------- --------- ----------                                      
        20 SMITH      CLERK           2400                                      
        30 ALLEN      SALESMAN        4800                                      
        30 WARD       SALESMAN        3750                                      
        20 JONES      MANAGER         8925                                      
        30 MARTIN     SALESMAN        3750                                      
        30 BLAKE      MANAGER         8550                                      
        10 CLARK      MANAGER         7350                                      
        20 SCOTT      ANALYST         9000                                      
        10 KING       PRESIDENT      15000                                      
        30 TURNER     SALESMAN        4500                                      
        20 ADAMS      CLERK           3300                                      
 
    DEPTNO ENAME      JOB              SAL                                      
---------- ---------- --------- ----------                                      
        30 JAMES      CLERK           2850                                      
        20 FORD       ANALYST         9000                                      
        10 MILLER     CLERK           3900                                      
 
14 개의 행이 선택되었습니다.
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> SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP WHERE (JOB = 'CLERK' OR JOB = 'ANALYST') AND SAL NOT IN (1000,3000,5000);
 
     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM       
---------- ---------- --------- ---------- -------- ---------- ----------       
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 80/12/17        800                  
        20                                                                      
                                                                                
      7876 ADAMS      CLERK           7788 87/05/23       1100                  
        20                                                                      
                                                                                
      7900 JAMES      CLERK           7698 81/12/03        950                  
        30                                                                      
                                                                                
 
     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM       
---------- ---------- --------- ---------- -------- ---------- ----------       
    DEPTNO                                                                      
----------                                                                      
      7934 MILLER     CLERK           7782 82/01/23       1300                  
        10                                                                      
                                                                                
cs


1
2
3
4
5
6
7
8
9
10
11
12
 
SQL> SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP WHERE ENAME LIKE 'M%';
 
     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM       
---------- ---------- --------- ---------- -------- ---------- ----------       
    DEPTNO                                                                      
----------                                                                      
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400       
        30                                                                      
                                                                                
      7934 MILLER     CLERK           7782 82/01/23       1300                  
        10                                                                      
cs


1
2
3
4
5
6
7
8
 
SQL> SELECT JOB, COUNT(job) AS COUNT FROM EMP GROUP BY JOB HAVING COUNT(job) >=3;
 
JOB            COUNT                                                            
--------- ----------                                                            
CLERK              4                                                            
SALESMAN           4                                                            
MANAGER            3             
cs



저작자표시

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

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

+ Recent posts

티스토리툴바