Oracle 계정접속(SYS 외)

Microsoft Windows [Version 6.1.7601]

Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Jacob>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on 수 1월 13 21:28:18 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

다음에 접속됨:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user sys identified by m********;

사용자가 변경되었습니다.

SQL> select * from user_tables where rownum <= 1;

SQL> select * from all_tables where rownum <= 1;

select * from user_all_tables;

REGIONS

COUNTRIES

LOCATIONS

DEPARTMENTS

JOBS

EMPLOYEES

JOB_HISTORY

desc REGIONS

desc COUNTRIES

desc LOCATIONS

desc DEPARTMENTS

desc JOBS

desc EMPLOYEES

desc JOB_HISTORY

1.REGION 별 SALARY 의 합을 구하시오 (지역명 명시할 것)

2.매년 가장 높은 평균연봉을 받는 DEPARTMENTS 를 구하시오

select * from job_history;

select count(*) from employees;

select * from employees where department_id is null;

select a.*, b.*

from (

        select b.location_id ,b.department_id, a.salary

          from employees a,

               departments b

         where a.department_id=b.department_id

         ) a,

     locations b

where a.location_id=b.location_id

select b.department_id, count(a.employee_id)

  from employees a,

       departments b

 where a.department_id=b.department_id

 group by b.department_id;

select r.region_id,                            /* 지역 */

       sum(e.salary) as region_sum_salary      /* 지역별 급여합*/

  from employees e

      ,departments d

      ,locations l

      ,countries c

      ,regions r

 where e.department_id = d.department_id

   and d.location_id   = l.location_id

   and l.country_id    = c.country_id

   and c.region_id     = r.region_id

 group by r.region_id;

-- ANSI SQL --

INNER JOIN

LEFT OUTER JOIN   /* A LEFT OUTER JOIN B */   A.XX = B.XX(+)

FULL OUTER JOIN   /* A FULL OUTER JOIN B */

sys 관련 View 공부할 것 - sql join 

관련 테이블 ERD 를 그려봐라

select * from all_tables;

select * from all_tab_cols;

select * from all_tab_cols;

OWNER, TABLESPACE, TABLE, COLUMN, COMMENT, INDEX, SEGMENT, BLOCK, SUBPARTITION

select * from v$sql

select * from v$sga

select * from v$latch

select * from v$datafile

select * from v$active_session_history  /* sql_id */

엮어보세요

해당 View 열거

SQL TEXT 문이