Q 54.
having 조건식
---------------------------------------------------------
데이터를 검색할때 2가지의 조건식이 존재
1.where 조건식 ->가장 보편적
2.having 조건식 ->그룹에 대한 조건식
------------------------select의 검색순서---------------------------------
select 필드별,,,=>별칭,가상필드
from 검색테이블,,,=>조인
where 일반검색에 대한 조건식
group by 그룹을 지어줄 필드명,
having 그룹에 대한 조건식이 필요
order by 정렬할 필드명 정렬기준
----------------------------------------------------------------------------
SQL> select deptno, sum(sal) "급여 합계" from emp group by deptno;
DEPTNO 급여합계
---------- ----------
30 9400
20 10875
10 8750
SQL> select deptno, sum(sal) "급여 합계" from emp group by deptno having count(*) >= 4;
= 사원 수 가 4명 이하인 10번 부서는 제외되서 출력
DEPTNO 급여합계
---------- ----------
30 9400
20 10875
Q 55.
그룹함수도 중첩이 가능하다. 단 2단계까지만
형식)함수명(함수명())
max(avg(sal))=>부서별로 평균값을 구한 가운데 최대 평균 급여
max(max(sal))=>부서별로 급여의 합계를 구한값중에서 가장 최대 급여값를 받는 부서
min(min(sal))=>부서별로 제일 급여합계가 적게받는 급여?
SQL> select max(avg(sal)), max(sum(sal)), min(min(sal)), max(max(sal)) from emp group by deptno;
MAX(AVG(SAL)) MAX(SUM(SAL)) MIN(MIN(SAL)) MAX(MAX(SAL))
------------- ------------- ------------- -------------
2916.66667 10875 800 5000
Q 56.
emp테이블에서 업무가 MANAGER가 아닌 사원에 대한
부서별로 급여의 합이 2500 이상인 부서의 정보를 출력하시오.
(단 부서번호,급여의 합계순으로 출력하되,급여의 합계가 높은순으로 정렬하시오.)
SQL>select deptno as "부서", sum(sal) as "급여 합계", job as "직업" from emp where job != 'MANAGER' group by deptno, job having sum(sal) >= 2500 order by sum(sal) desc;
SQL>select deptno as "부서", sum(sal) as "급여 합계", job as "직업" from emp where job <> upper('manager') group by deptno, job having sum(sal) >= 2500 order by sum(sal) desc;
부서 급여 합계 직업
---------- ---------- ---------
20 6000 ANALYST
30 5600 SALESMAN
10 5000 PRESIDENT
SQL>select deptno,sum(sal) "부서별 급여합"
from emp
where job <> upper('manager')
group by deptno
having sum(sal)>=2500
order by sum(sal) desc;
DEPTNO 부서별 급여합
---------- -------------
20 7900
30 6550
10 6300
테이블을 생성=>수정하는 방법
해당 컬럼에 자료가 없는 경우
---------------------
ID NUMBER(2)
NAME VARCHAR2(10)
---------------------------
테이블을 생성=>수정하는 방법
create table -> alter table(문제가 없다고 판단) -> insert into
1.컬럼의 데이터 타입을 변경할수 있다.(varchar2<-->number)
2.컬럼의 크기를 변경 가능하다.(확대<->축소)
해당 컬럼에 자료가 있는 경우
1.컬럼의 데이터 타입을 변경할수 없다.(varchar2<-->number(X))
2.컬럼의 크기를 늘릴 수는 있지만 현재 가지고 있는 데이터크기보다
작은크기로 변경할 수없다.(X)
varchar2(10)----->varchar2(20) (O)
varchar2(10)<-----varchar2(20) (X)
ex) 120만원----->240만원
<----(X)
----------------------------------------------------------------------------
하나이상의 테이블을 검색하는 방법=>join
Q 57.
기존의 테이블에 컬럼(=필드) 가 누락?
형식) alter table 변경시킬테이블명 add (추가시킬 필드명 자료형 제약조건);
SQL> alter table b_pretest add(tel varchar2(15) not null); // not null : 반드시 입력해야 될 값
1행에 오류:
ORA-01758: 테이블은 필수 열을 추가하기 위해 (NOT NULL) 비어 있어야 합니다.
SQL> alter table b_pretest add(tel varchar2(15));
테이블이 변경되었습니다.
***************************************************************************
not null을 반드시 설정해야 될 경우
SQL>delete from b_pretest; // 데이터 삭제 시킨 후 실행 가능
***************************************************************************
Q 58.
변경(modify)=>기존에 존재하는 필드를 변경하고자 할때 사용
데이터가 존재 O ->varchar2<-->number(X)
데이터가 존재 O ->varchar2(15)->varchar2(30) 확대만 가능
축소=>데이터 손실을 받을 수 있기때문에
형식)alter table 변경할테이블명 modify (변경할 필드명 자료형 제약조건)
SQL> alter table b_pretest modify (name varchar2(30));
Q 59.
불필요로하는 필드(=컬럼)은 삭제도 가능
형식) alter table 변경시킬테이블명 drop column 삭제시킬 필드명(=컬럼명)
SQL> alter table b_pretest drop columm tel;
Q 60.
테이블의 필드명을 변경하고 싶다.(name->irum)
형식) alter table 변경시킬테이블명 rename column 변경전 필드명 to 변경후 필드명;
SQL>alter table b_pretest rename column name to irum;
테이블이 변경되었습니다.
SQL> desc b_pretest;
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER(2)
IRUM VARCHAR2(30)
Q 61.
테이블의 필드명을 변경하고 싶다.(name->irum)
형식) alter table 변경시킬테이블명 rename column 변경전 필드명 to 변경후 필드명;
11g의 경우=>데이터가 들어있어도 변경가능
sql>alter table b_prtest rename column name to irum;
**********************************************************
동영상,이미지,음악파일 저장->실제 테이블에 저장(X)
c:\webtest\2.db\print\movie.avi =>varchar2(200)
movie.jpg
***********************************************************
varchar2(4000) 이상 저장-> Long형을 사용(2GB)
영문자 4000자 -> 한글 2000자 까지 저장된다.(clob)
==============================================
**제약조건 ->10장 ppt 정리 **
DB관리->1.어떻게 올바른 값을 저장할 것인가?(설계목적)=>제약조건이 필요한 이유
ename=>'adgqahahhh' =>활용X
2.어떻게 하면 원하는 자료를 마음대로 검색?->2,3,4일(select 명령어)=>조인
**********************************************************
** 제약조건의 종류 및 설정 **
primary key(기본키)=>1.테이블의 행을 구분하는 키워드로 사용=>유일한 값
ex) 사원번호,계좌번호,일련번호,주민등록번호
2.중복되면 안되는 필드에 부여
ex) 사원번호,계좌번호,일련번호,주민등록번호
primary key가 적용이 되는 필드 =>자동 NOT NULL(필수입력)
3.인덱스키(=책갈피(북마크))가 자동으로 생성
->내부에서 인덱스키가 부여된 필드먼저 찾아주기때문에
검색속도가 빠르다.
중복허용을 해주는 필드에 부여X ->ename,성별,직책
SQL> alter table b_pretest rename column irum to name;
Q 62.
형식)alter table 변경할테이블명 add constraint 제약조건명 제약조건종류(적용필드명)
---------------------------------------------------------------------------------
primary key(id)
**오라클의 테이블 종류**
1.사용자정의 테이블=>필요에 따라서 만드는 테이블
2.시스템 테이블=>오라클이 제공하는 테이블=>작업한 내용자동 저장=>조회
제약조건이름=>afaabad(X) =>시스템 테이블(sys~로 시작)
규칙) v_변수명, c_상수명,,,
테이블명_제약조건약어(pk)_적용필드명
b_prtest_pk_id
=>**기술면접 => 기본적인 SQL명령어(1~2일)
조인(하나 이상의 테이블) 검색
******************************
제약조건=>pk,uk,ck,not nul(nn) =>하나의 테이블에 설정이 가능
SQL> alter table b_pretest add constraint b_pretest_pk_id primary key(id);
테이블이 변경되었습니다.
SQL> insert into b_pretest values(1, '홍길동');
insert into b_pretest values(1, '홍길동')
*
1행에 오류:
ORA-00001: 무결성 제약 조건(TEST.B_PRETEST_PK_ID)에 위배됩니다
SQL> insert into b_pretest values(6, '홍길동');
1 개의 행이 만들어졌습니다.
Q 63.
unique제약조건=>중복되면 안되는 필드에 부여=>pk와 기능이 동일
형식) alter table 변경시킬테이블명 add constraint 제약조건명 제약조건종류(필드명)
uk unique(적용필드명)
*************************************************
pk=>not null(필수입력)+unique key같이 부여(중복되면 X)+자동 index key도 부여
(검색속도 향상)
unique key=>중복허용 X에 부여(not null 자동으로 부여X)
-----------------------------------------------------------------------
SQL> alter table b_pretest add constraint b_pretest_uk_is unique(id);
alter table b_pretest add constraint b_pretest_uk_is unique(id)
*
1행에 오류:
ORA-02261: 고유 키 또는 기본 키가 이미 존재하고 있습니다 (primary key)
Q 64.
제약조건을 삭제(drop constraint)
형식)alter table 변경시킬테이블명 drop constraint 삭제시킬 제약조건이름;
SQL >alter table b_prtest drop constraint b_prtest_pk_id;
Q 65.
check제약조건=>데이터를 저장시킬때 과연 올바른값이 들어왔는지를 체크
(숫자->범위지정,문자->올바른 값만 미리 등록)
형식) alter table 변경시킬테이블명 add constraint 제약조건명 제약조건
-----------------------------------------------------------------------
제약조건->check(age>=20 and age<=65)
between 20 and 65
check in ('사원','대리','계장','과장',,,)
==>입력한 문자열이 이 범위안에 있는지 없는지?
SQL>alter table b_prtest add (age number);
alter table b_prtest add (addr varchar2(80));//영문 80 ,한글 40자(최대)
SQL> desc b_prtest
이름 널? 유형
----------------------------------------- -------- ----------------------------
ID NUMBER(2)
IRUM VARCHAR2(30)
AGE NUMBER
ADDR VARCHAR2(80)
====================================
SQL>alter table b_prtest add constraint b_prtest_ck_age
check (age>=20 and age<=65);
SQL> insert into b_pretest values(7, '테스트김', 123, '서울시 강남구 세경빌딩3층');
insert into b_pretest values(7, '테스트김', 123, '서울시 강남구 세경빌딩3층')
*
1행에 오류:
ORA-02290: 체크 제약조건(TEST.B_PRETEST_CK_AGE)이 위배되었습니다
= 20살 ~ 65살 범위의 나이만 저장이 가능 (코드에선 123살)
Q 66.
제약조건->default(디폴트)->값을 저장시킬때 '값(문자)',숫자(23) 언급하지
않아도 자동으로 지정된값이 저장이 되는 역할
->회원가입 날짜 -> 필드를 작성할때 같이 부여한다.
SQL>alter table b_prtest add (hiredate date default sysdate);
자료형 default 적용시킬값(오늘날짜)
default '서울시'
default 23
Q 67.
insert into 테이블명 values(필드명,,,,);
주의 : 필드의 갯수를 맞춰야 된다(필드가 5개->필드의 순서와 갯수를 맞춰야 된다.)
SQL> insert into b_pretest values(7, '홍길동', 25, '성남시');
*
1행에 오류:
ORA-00947: 값의 수가 충분하지 않습니다 = 필수입력해야 되는데 입력을 하지 않은 필드가 존재해서 발생되는 에러메세지
insert into b_prtest values(7,'임시',34,'서울시','2021-06-11') ; ========수동으로 입력
insert into b_prtest values(8,'임시2',21,'서울시',default) ;//sysdate적용 default는 문자열이 아님
5째 필드에 default값이 적용되라는 표시
insert into b_prtest values(9,'임시3',24,'서울시',sysdate) ;//sysdate함수호출, now() = MySQL 8.0 설치시 가능
insert into b_prtest values(10,'임시4',33,'서울시',null) ;//날짜를 입력X
입력을 하는 필드에 null을 써주는경우는 값을 입력하지 않겠다는 말
** insert into 구문2 **
= input type을 이용해서 입력을 받아서 저장되는것이 아닌경우 ex)default 값이 대표적
형식) insert into 테이블명(필드명1,필드명2,,,) values(값1,값2,,,);
ex)11번 데이터를 입력하는데 addr(X) null을 허용 O ,hiredate->default 적용하고 싶다.
SQL> insert into b_pretest values(11, '홍길동', 27, null, default); = 값의 개수는 무조건 맞춤
1 개의 행이 만들어졌습니다.
SQL> insert into b_pretest(id, name, age) values(12, '임꺽정', 29); = 필드명을 지정해서 그 값만 집어넣으면 지정되지 않은 필드명은 자동으로 null값 처리됨
1 개의 행이 만들어졌습니다.
**************************************************************************************************************
만약에 not null이 적용된 필드를 생략 -> name은 null을 허용할 수 없습니다 -> 반드시 입력했는지 확인
**************************************************************************************************************
fk(foreign key) =>관계형 데이터베이스의 테이블간의 정의를 위해
한개~두개로 만들어진 관리
외래키
쇼핑몰 구축=>데이터 저장(15~20개)
하나의 테이블에 저장? 필드수가 25~5만개->저장하기도 힘들고, 검색속도가 느림.
신입=>설계X ===>조회수준
서로 다른 한개이상의 테이블사이의 관계(한개 이상의 테이블에서 설정하는 방법)
SQL>create table sawon(id number not null, name varchar2(20) not null, deptno number, primary key(id));
Q 68.
fk설정방법 sawon
alter table 변경시킬 테이블명 add constraint 관계설정할 이름 foreign key(자기 테이블의
필드명)
deptno
references 상대방테이블명(연결시킬 필드명)
pk
참조한다.
(=내용을 확인) dept(deptno)
SQL> alter table sawon add constraint sawon_fk_deptno foreign key (deptno) references dept(deptno);
*
1행에 오류:
ORA-02298: 제약 (TEST.SAWON_FK_DEPTNO)을 사용 가능하게 할 수 없음 - 부모 키가
없습니다
-> 이미 sawon에 규칙을 어기는 데이터(40)가 이미 들어가 있는 경우에는 관계설정을 할 수 없다는 에러메세지
SQL> update sawon set deptno = 20 where id = 2; // 규칙을 어기는 데이터(40)를 규칙을 지키는 데이터(20)으로 바꿈
1 행이 갱신되었습니다.
SQL> alter table sawon add constraint sawon_fk_deptno foreign key (deptno) references dept(deptno);
테이블이 변경되었습니다.
SQL> insert into sawon values(4, '테스트33', 900);
insert into sawon values(4, '테스트33', 900)
*
1행에 오류:
ORA-02291: 무결성 제약조건(TEST.SAWON_FK_DEPTNO)이 위배되었습니다- 부모 키가
없습니다
-> 엉터리 데이터는 입력이 안됨 -> fk
Q 69.
테이블의 이름도 변경이 가능
형식) rename 원본테이블명 to 변경할테이블명
무결성 제약조건=>제약조건을 추가,삭제=>기능을 잠시 중지(비활성화)
기능을 다시 사용가능(활성화)
SQL> rename b_pretest to b_prtest;
테이블 이름이 변경되었습니다.
SQL> rename b_prtest to b_pretest;
테이블 이름이 변경되었습니다.
Q 70.
데이터 딕셔너리(=데이터 사전)=>시스템 테이블(insert,update,delete)
1) user_XXX ->계정(test)들이 조회=>user_constraints테이블
2) dba_XXX =>관리자만 조회가능( 일반 계정 X)
3) all_XXX =>계정 O, 관리자 O
=>구조파악 ->desc user_constraints
SQL>desc user_constraints
Q 71.
제약조건이 활성화된 상태->임시로 중지->재 가동
add / drop
형식) alter table 변경시킬테이블명 disable constraints 비활성화시킬 제약조건이름
alter table 변경시킬테이블명 enable constraints 활성화시킬 제약조건이름
********************************************************************************************
만약 enable이 안된다면(엉터리값을 저장된 상태라면 다시) 바뀌지 않는다.
= delete from 테이블명; 조건식을 사용해서 삭제 또는 수정해야 다시 enable로 변경됨
********************************************************************************************
SQL> alter table b_pretest disable constraints b_pretest_ck_age;
테이블이 변경되었습니다.
SQL> @ c:\webtest\2.db\print\consearch.txt
table_name의 값을 입력하십시오: b_pretest
구 1: select constraint_name, constraint_type, table_name, status from user_constraints where table_name = upper('&table_name')
신 1: select constraint_name, constraint_type, table_name, status from user_constraints where table_name = upper('b_pretest')
CONSTRAINT_NAME C TABLE_NAME STATUS
------------------------------ - ------------------------------ --------
B_PRETEST_CK_AGE C B_PRETEST DISABLED
Q 72.
emp테이블 에서 ALLEN이 근무하고 있는 부서명을 알고 싶다.
부서필드(dname)=x -> dept 테이블
조인?=>한개이상의 테이블을 가지고 검색하는 방법
SQL>conn scott/tiger
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
ZIPCODE TABLE
emp(사원정보)<--->dept(부서->부서번호,부서명,부서위치)
SQL>select * from dept;
부서번호 부서명 부서위치
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select empno, ename, deptno, dname, loc from emp, dept;
select empno, ename, deptno, dname, loc from emp, dept
*
1행에 오류:
ORA-00918: 열의 정의가 애매합니다
SQL> select empno, ename, dept.deptno, dept.dname, dept.loc from emp, dept;
***************************************************************************************************************
조인->두 테이블에서 보고자하는 필드를 단순히 나열한다고 조인이
되는것은 아니다.(=cross join)=>아무 의미가 없다.
단점 1.중복된 데이터가 많아진다.=>찾고자하는 데이터를 검색하기가
쉽지않다.(섞여 있어서 문제)
2.56개의 행이 검색
emp(14명) * dept(부서 10~40) 4개=56개(중복)
20,000 * 30
=>중복된 데이터를 제거=>내가 찾고자하는 부분의 조건식을 줘야 된다.
** 조인의 개요=>한개이상의 테이블을 검색하면서 중복된 데이터가
없게 설정하는 검색방법=>원하는 데이터를 검색하는 방법 **
***************************************************************************************************************
Q 73.
조인의 종류 =>종류와 각 조인의 특징을 아는대로 기술하시오.
1.inner join(default)=>두 검색하고자하는 테이블의 양이 엇비슷할때 검색하는 방법
=>중소기업,대기업의 기준에 따라서 데이터의 양은 다를수있다
100 100 =>10,000건
형식) select 필드명1,필드명2,,, 테이블명.공통필드명,,,
from 테이블1,테이블2
where 조건식(emp.deptno=dept.deptno)
2.outer join=>한쪽의 테이블의 데이터가 많을때 사용하는 방법
많은쪽 테이블의 데이터를 무조건 다 나올수있도록 설정
(사각지대를 제거(데이터의 검색대상에 포함을 시킨다.)
**********************************************************************************************************************************
Q 74.
조인->검색->테이블명.필드명=>ex) user_constraints
=>테이블에 오타=>테이블 이 존재하지 않는다.
=>검색속도도 빠르게 오타도 줄이기 위한 방법=>테이블 이름도 별칭이 가능
=>검색속도를 향상시키는 방법=>튜닝(=업그레이드)
형식) select 필드명1,필드명2,,, 별칭1.공통필드명,,,
from 테이블1 별칭1,테이블2 별칭2,,,
where 조건식(별칭1.deptno=별칭2.deptno)
pk fk
=>fk를 설정할때=>1.필드명은 pk와 같아도되고 달라도 된다.(보통 같게 많이준다)
2.두 공통필드명의 자료형은 같아야 된다.
**********************************************************************************************************************************
Q 75.
emp테이블 에서 업무가 SALESMAN인 사원의 이름, 급여, 부서명, 근무지를 출력시켜주는 SQL 구문 작성
SQL> select e.ename, e.sal, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno and e.job='SALESMAN';
ENAME SAL DNAME LOC
---------- ---------- -------------- -------------
TURNER 1500 SALES CHICAGO
MARTIN 1250 SALES CHICAGO
WARD 1250 SALES CHICAGO
ALLEN 1600 SALES CHICAGO
Q 76.
emp테이블 에서 사원의 이름이 L자가 들어가는 사원의 정보를 출력
SQL> select e.ename, e.sal, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno and e.ename like '%L%';
ENAME SAL DNAME LOC
---------- ---------- -------------- -------------
MILLER 1300 ACCOUNTING NEW YORK
CLARK 2450 ACCOUNTING NEW YORK
BLAKE 2850 SALES CHICAGO
ALLEN 1600 SALES CHICAGO
Q 77.
emp테이블 에서 사원의 이름, 급여, 부서명 -> sal 가 1200이상인 사원만 검색
SQL> select e.ename, e.sal, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno and e.sal >= 1200;
ENAME SAL DNAME LOC
---------- ---------- -------------- -------------
ALLEN 1600 SALES CHICAGO
WARD 1250 SALES CHICAGO
JONES 2975 RESEARCH DALLAS
MARTIN 1250 SALES CHICAGO
BLAKE 2850 SALES CHICAGO
CLARK 2450 ACCOUNTING NEW YORK
SCOTT 3000 RESEARCH DALLAS
KING 5000 ACCOUNTING NEW YORK
TURNER 1500 SALES CHICAGO
FORD 3000 RESEARCH DALLAS
MILLER 1300 ACCOUNTING NEW YORK
Q 78.
inner join = 1개 이상의 테이블 -> 3개 까지
SQL>desc bonus
insert into bonus values('SMITH','CLERK',800,null);
insert into bonus values('ALLEN','SALESMAN',1600,300);
insert into bonus values('WARD','SALESMAN',1250,500);
'국비학원 > 공부' 카테고리의 다른 글
| 12일차 oracle(5) (1) | 2023.05.08 |
|---|---|
| 11일차 oracle(4) (0) | 2023.05.04 |
| 9일차 oracle(2) (0) | 2023.05.04 |
| 8일차 oracle(1) (0) | 2023.05.04 |
| 7일차 jsp ~ oracle 설치 (1) | 2023.05.04 |