문방구앞오락기

Decode 는 if else 와 비슷함 switch 와 더 유사 

문법자체는 이렇다 

여기서 

select ename 

  , deptno 

  , DECODE(deptno, 10, '테스트1' 

                  , 20, '테스트2' 

                  , 30, '테스트3' 

                  , 40, '테스트4' 

                  )dname 

  from emp; 

NVL 함수는 NULL 값을 다른 값으로 바꿀 때 사용하며, 모든 데이터 타입에 적용이 가능하다. 

--NVL 

select empno, deptno, sal, NVL(Comm , 999) from emp 

order by deptno; 

이렇게 하면 null이 다 999로 바뀜 

--NVL2 

select empno, deptno, sal, NVL2(Comm , 111 , 999 ) from emp 

order by deptno; 

이건 안비어있으면 111 널이면 999로 바꾸는 함수 

--COALESCE 이건 (조건1, 조건2, 조건3 ..) 계속 늘어나는형태 

select stdu.s_id, stdu.s_nm, cou.c_id,cou.c_nm 

from STUDENT stdu cross join course cou; 

두개의 조건이 없어도 조인을 할수있다. 

select stdu.s_id, stdu.s_nm, cou.c_id,cou.c_nm 

from STUDENT stdu ,course cou; 

사실 이거랑 결과 같다. 

제약조건 확인 

SELECT *  

FROM         ALL_CONSTRAINTS 

WHERE TABLE_NAME = '테이블명'; 

SELECT A.UNIQUENESS, 

B.* 

FROM         ALL_INDEXES A, 

ALL_IND_COLUMNS B 

WHERE A.INDEX_NAME = B.INDEX_NAME 

AND A.TABLE_NAME='테이블명'; 

제약조건 삭제 

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명 CASCADE; 

인덱스 생성 

CREATE UNIQUE INDEX PK명 ON 테이블명 (컬럼명1, 컬럼명2....); 

PK 생성 

ALTER TABLE 테이블명 ADD (CONSTRAINT PK명 PRIMARY KEY (컬럼명1, 컬럼명2....)); 

Create view sampleView as select city.countryCOde As abbr, country.Name As fullName from city; 

Select * from sampleView; 

Create view sampleView as [select city.countryCOde As abbr, country.Name As fullName from city;] 

괄호로 만든다 테이블을 만들어서 뷰를 만든다. 

select emp.employee_id, emp.first_name, emp.DEPARTMENT_ID 

   from EMPLOYEES emp; 

select * from DEPARTMENTS; 

select emp.employee_id 

  , emp.first_name 

  , emp.DEPARTMENT_ID 

  ,( 

  select dep.department_name  

    from departments dep 

    where dep.department_id = emp.department_id 

  ) 

    from EMPLOYEES emp; 

select emp.employee_id 

  , emp.first_name 

  , emp.DEPARTMENT_ID 

  ,( 

  select dep.department_name  

    from departments dep 

    where dep.department_id = emp.department_id 

  ) 

    from EMPLOYEES emp 

    order by emp.department_id; 

    select  

    count(emp.employee_id) as cot 

    ,avg(emp.salary) 

    ,emp.DEPARTMENT_ID 

  ,( 

  select dep.department_name  

    from departments dep 

    where dep.department_id = emp.department_id 

  ) 

    from EMPLOYEES emp 

    group by emp.department_id; 

    --join 으로 표현하면 

    select count(emp.employee_id) as cot 

    ,avg(emp.salary) 

    ,emp.department_id 

    ,dep.DEPARTMENT_NAME 

    from EMPLOYEES emp left outer join DEPARTMENTS dep 

    on emp.DEPARTMENT_ID = dep.DEPARTMENT_ID 

    group by emp.DEPARTMENT_ID, dep.DEPARTMENT_NAME; 

오늘도  

Employees 테이블이용 

Department를 묶어서 보고싶었다  

그럼 다음과같이 

Group by를 쓰면된다. 

select department_id  

  from EMPLOYEES 

  group by DEPARTMENT_ID; 

이거에 관련되어 무언가 통계를 가미해본다. 

select DEPARTMENT_ID, count(DEPARTMENT_ID),avg(SALARY) 

  from EMPLOYEES 

    group by DEPARTMENT_ID; 

Having이것에 대한 통계값의 조건비교가 가능하고 무조건 그룹바이랑 같이쓴다. 

    select DEPARTMENT_ID, count(DEPARTMENT_ID),avg(SALARY) 

  from EMPLOYEES 

    group by DEPARTMENT_ID 

      HAVING avg(salary) > 10000; 

ORDER BY 절은 SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용한다. 

ORDER BY 절에 칼럼(Column)명 대신에 SELECT 절에서 사용한 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능하다. 

그리고 별도로 정렬 방식을 지정하지 않으면 기본적으로 오름차순이 적용 

select * from EMPLOYEES; 

Employees 테이블 조회값 

Order by 로 조건을 하나 걸게되면 

부서로 걸었따 10부터 오름차순으로 쭉 나온다. 

select * from EMPLOYEES 

  order by DEPARTMENT_ID; 

두가지 이상의 조건을 걸때는 앞에 나온게 1순위 그후로 2순위 이런식으로 쭉 나온다. 

이런테이블에 급여 평균을 구하여 

select avg(SALARY) from EMPLOYEES; 

근로자들의 평균임금을 구하는 식이다. 

이것을 서브쿼리를 이용하여 평균보다 높은사람 적은사람을 출력해보자 

select  first_name, salary 

  from EMPLOYEES 

  where SALARY >= (select avg(SALARY)  

                  from EMPLOYEES); 

이렇게하면된다 

SELECT e.Department_Id , d.Department_Name 

  FROM departments d inner join employees e 

  ON e.department_id = d.department_id; 

Inner join으로 가져왔떤거다 이걸 27개의 로우로 만들게 중복을 제거하여보자 

SELECT distinct e.Department_Id , d.Department_Name 

  FROM departments d inner join employees e 

  ON e.department_id = d.department_id; 

이런 실제로 활용된파트는 10번부터 100번까지 밖에 안써서 11개의 칼럼만 나왔다 

나머지들은 안나왔다 허엉