728x90
반응형

 

* GitHub 설치방법 –필요없음 그냥 setup파일 next하면됨

1) https://gitforwindows.org/ 접속 후 Download

 

2) 다운받은 파일(Git 2.19.1 Setup) 실행

Next 클릭

 

3) 기본 경로를 사용. Next 클릭

 

4) 구성 요소 선택

Addtional icons

- On the Desktop : 바탕화면에 아이콘 추가

Windows Explorer integration

- Git Bash Here, Git GUI Here : 폴더에서 Git에 바로 연결할 수 있는 기능 추가

Git LFS(Large File Support) : 용량이 큰 파일 지원

Associate .git* configuration files with the default text editor : .git 형식의 파일을 기본 텍스트 편집기와 연결

Associate .sh files to be run with Bash : Bash와 함께 실행될 sh 파일 연결

Use a TrueType font in all console windows : 윈도우 콘솔에서 올바른 글꼴 사용여부

Check daily for Git for Windows updates : 매일 업데이트 확인 여부

기본 옵션으로 Next 클릭

 

5) 시작 메뉴에 추가할 폴더를 선택(만들어진 폴더가 없을 경우 자동 생성)

Don't create a Start Menu folder를 체크하면 시작 메뉴에 추가하지 않는다.

기본 옵션으로 Next 클릭

6) Git 편집기를 선택

VimWindowsGit의 기본 편집기로 Next 클릭

 

7) PATH 환경 설정

Use Git from Git Bash only : Git Bash에서 Git command line tools만 사용 가능

Use Git from the Windows Command Prompt : Git BashWindows 명령 프롬프트(cmd)에서 Git 사용 가능

Use Git and optional Unix tools from the Windows Command Prompt : Git과 유닉스 툴을 사용자 PC 경로에 추가해서 Git을 사용

기본 옵션으로 Next 클릭

 

8)HTTPS 선택

Use the OpenSSL library : OpenSSL 라이브러리 사용

Use the native Windows Secure Channel library : Windows 인증서 저장소를 사용하여 유효성 검사

기본 옵션으로 Next 클릭

 

9) 텍스트 파일의 line ending 스타일 선택

Checkout Windows-style, commit Unix-style line endings : 체크아웃은 윈도우, 커밋은 유닉스 스타일 적용

Checkout as-is, commit Unix-style line endings : 체크아웃은 스타일 변환없음, 커밋은 유닉스 스타일 적용

Checkout as-is, commit as-is : 체크아웃, 커밋 스타일 변환 없음

이번 설치는 윈도우 환경이기 때문에 기본 옵션으로 Next 클릭

 

10) Git Bash 터미널 설정

Use MinTTY(the default terminal of MSY52) : MinTTY terminal emulator 사용

Use Windows' default console window : Windows 기본 콘솔 사용

기본 옵션으로 Next 클릭

 

11) 기타 옵션

Enable file system caching : 성능 향상을 위해 파일 시스템 데이터를 메모리에 캐시

Enable Git Credential Manager : WindowsGit 보안 자격증명 저장소를 사용하기 위해 Git Credential Manager 활성화

Enable symbolic links : symbolic links 사용

기본 옵션으로 Next 클릭

 

12) 실험 옵션(?)

Enable experimental, built-in rebase : 기본 제공되는 rebase 사용

Enable experimental, built-in stash : 실험적으로 내장 된 숨김 기능 사용

이 부분은 따로 체크를 하지 않아도 되는 것 같다. Install 클릭

13) 설치 실행

14) 설치 완료

15) 윈도우 키를 눌러서 설치된 폴더 확인

16) cmd 창을 열고(윈도우 키-실행에서 cmd 입력) 아래 명령어 입력

cd/

git version

아래와 같이 뜬다면 설치 성공!

 

 

728x90
반응형

'IT_Web > Project_Setting' 카테고리의 다른 글

SVN 설치 및 사용방법  (0) 2022.09.06
GitHub 사용법  (0) 2020.03.09
자바 설치 및 환경 설정 스키마 계정생성  (0) 2020.03.09
728x90
반응형

파일 설치시 파일구조

dev > db > app > oracle

> eclipse, tomcat, workspace, java

 

 

* 자바설치 (dev 파일안 설치)

1. 자바환경변수 설정 >내컴퓨터 >우클릭 >속성> 고급 시스템 설정 >환경변수>시스템변수 창에 >새로만들기

> 경로는 자바설치된 경로로 설정하기 및 환경변수 이름설정 > path를 편집> 새로만들기

>%환경변수이름%\bin

2. 클래스패스 설정 > 시스템변수창에 > 새로 만들기 > %환경변수이름%\bin

3. 자바확인 cmd창에서 java –version - 끝 -

 

* db 오라클설치 알아서 하기 * 설치시 아뒤 및 비번설정 칸이 나온다

 

 

* 스카마 계정 생성 및 테이블 생성하기

1. sqlplus /nolog -- cmd에서 sqlplus 접속하겠다

2. conn / as sysdba -- 시스템관리자로 접속하겠다

3. create user scott identified by tiger default tablespace system; -- 스키마계정 만들기

   *scott - 아이디 tiger – 비밀번호

4 .grant connect, resource, dba to scott; -- 권한설정

5. conn scott/tiger -- 연결방법

6. alter session set nls_territory='AMERICA'; -- 글자깨짐 방지

7. alter session set nls_language='AMERICAN';

8. @C:\dev\db\app\oracle\scott.sql; -- 스키마 생성방법

9. @C:\dev\db\app\oracle\summit2.sql; -- 기존 sql파일이 있을때만 가능한 방법

- .spl 파일은 스키마계정과 sqlplus 접속이 필요하며, 테이블을 먼저 생성하고 나머지를 insert를 해야한다

- select * from 으로 테이블이 생성되었는지 최종적으로 하자

 

 

* 덤프 파일 테이블 생성하기

- 덤프 파일 익스포트 (현재 어떠한 스키마 계정의 userid정보에 대한 spl정보를 익스포트한다)

C:\) exp userid=아이디/비밀번호 file='C:\파일명.dmp' full=y

 

- 덤프 파일 임포트 (현재 덤프파일의 sql정보를 어떤 스키마 계정의 userid정보로 spl정보를 임포트한다)

1. cmd 에서 sqlplus에서 나온 후, C: 폴더로 이동한 상태에서,

2. imp userid=아이디/비밀번호 file='C:\파일명.dmp' full=y

 

 

* sql developer 설치 및 계정추가하기

계정추가 시 c: > windows > system32 > drivers > etc > hosts

hosts파일을 확인하여 로컬의 ip주소 서버의 ip주소를 확인할 수 있다

 

1. local 경우 - 플러스 버튼 클릭 > 접속이름, 사용자이름 스키마계정 & 비밀번호 설정

> 테스트버튼 클릭하여 상태확인

2. server 경우 – 호스트 이름을 서버 주소 이름으로 변경한다 예:] aws.seoulit.co.kr

 

 

* 이클립스 설치

1. 다운로드 받은 이클립스 파일을 dev 파일에 압출을 풀게되면 설치는 끝

2. workspace 또한 dev에 폴더를 생성하고 거기에 만든다

 

 

* 톰캣 설치 및 설정

1. 코어를 다운받고 톰캣을 실행시켜 설치한다 https://tomcat.apache.org/download-70.cgi

    사용자 비밀번호를 입력하고 java경로는 자동인식함으로 건딜지말고 설치경로만 dev > tomcat 에 설치한다

2. Servers 란에 create a new server를 클릭하고 설정을 진행한다

3. Apache를 선택하고 tomcat을 설치한 버전으로 선택한다

4. dev > tomcat 경로를 지정하여 tomcat을 설정한다 (경로지정 주의)

 

 

* STS(Spring Tool Suit) 플러그인 설치 (스프링 기반의 웹 프로젝트를 생성한다)

Help > Eclipse Marketplace에서 Spring Tool Suite 검색

spring 프로젝트시 sts3 standard

 

 

* 실습 프로젝트 생성 및 설정변경

Spring Legacy Project > Spring MVC Project 로 생성

위의 방법으로 플러그인 STS로 프로젝트를 생성하면

JRE 버전도 맞지 않고 서버 라이브러리도 등록되지 않아 변경해야한다

프로젝트 마우스 우클릭 > Project Facets > java 1.8버전 변경 > 우측창 Runtimes > Apache 체크하여 Apply

pom.xml에서 스프링버전을 최신으로 바꿔준다

 

728x90
반응형

'IT_Web > Project_Setting' 카테고리의 다른 글

SVN 설치 및 사용방법  (0) 2022.09.06
GitHub 사용법  (0) 2020.03.09
GitHub 설치방법  (0) 2020.03.09
728x90
반응형

modules / counter.js

import { createAction, handleActions } from 'redux-actions';

const INCREASE = 'counter/INCREASE'; // action type를 변수에 저장하여 그 변수로 handleActions해준다!

const DECREASE = 'counter/DECREASE';

 

export const increase = createAction(INCREASE); // type이 지정된 변수를 careateAction()메소드를 활요하여 액션함수를 생성한다

export const decrease = createAction(DECREASE); // 이 액션함수는 사용할 클래스 내부에서 dispatch로 사용하게된다

 

const initialState = { // 리듀서

number: 0,

};

const counter = handleActions(

// 첫 번째 파라미터로는 액션에 따라 실행할 함수들을 가진 객체를 넣어주고,

// 두 번째 파라미터로는 상태의 기본 값( initialState )을 넣어준다.

{

[INCREASE]: (state, action) => ({ number: state.number + 1 }),

[DECREASE]: (state, action) => ({ number: state.number - 1 }),

},

initialState,

);

export default counter;

 

CounterContainer / contaiers.js

import React, { useCallback } from 'react';

import { useCallback, useDispatch } from 'react-redux';

import Counter from '../components/Counter';

import { increase, decrease } from '../modules/counter';

 

const CounterContainer = () => {

const number = useSelector(state => state.counter.number);  // mapStateToProps와 같은 기능이다

const dispatch = useDispatch();

const onIncrease = useCallback(() => dispatch(increase()), [dispatch]);

const onDecrease = useCallback(() => dispatch(decrease()), [dispatch]);

// useCallback()으로 최적화를 하여 dispatch되는 리듀스와 액션에 대한 state 객체가 변경이 있을 때만 함수가 재사용된다

// 위의 방법은 react-redux의 함수를 사용한 connect방법이다

 

return (

<Counter number={number} onIncrease={onIncrease} onDecrease={onDecrease} />

);

};

 

export default CounterContainer;

 

728x90
반응형
728x90
반응형

reducers / PostsReducers.js

export default (state = [], action) => {

  switch (action.type) {

    case 'FETCH_POSTS' :

      console.log(action.payload)

      return action.payload;

    default:

      return state;

    }

};

 

reducers / usersReducers.js

export default (state = [], action) => {

switch (action.type) {

case 'FETCH_USERS' :

return [ ...state, action.payload] -- es6문법으로 해체

default: 할당이다

return state;

             //state[]에 action.payload를 추가하고 배열을 만든다

}

};

 

reducers / index.js

import { combineReducers } from 'redux';

import PostsReducers from './PostsReducers';

import usersReducers from './usersReducers';

 

export default combineReducers({

posts: PostsReducers,     // 두개의함수를 가지고와 리덕스를 활용하여 state로 저장한다

users: usersReducers      // 그렇게 combine된 함수들은 react-redux에서 connect해서 활용한다

});

 

 

index.js

import React from 'react';

import ReactDOM from 'react-dom';

import { Provider } from 'react-redux';

import { createStore, applyMiddleware } from 'redux';

import thunk from 'redux-thunk';

 

import App from './components/App';

import reducers from './reducers';

 

const store = createStore(reducers, applyMiddleware(thunk));

// createStore을 하면 store를 생성할 수 있으며 미들웨어인 thunk를 같이 인자값으로 생성하면

// 액션함수를 선언할 때 dispatch가 가능하여 자유롭게 리듀서의 정보를 액션에 맞게 변경할 수 있다

 

ReactDOM.render(           // Privider 컴포넌트에 store을 주면, 그 자식컴포넌트에서 store의 정보들을 편하게 사용이 가능하다

                                                // 이때 중요한 점은 applyMiddleware(thunk)도 같이 넣어주면서 store에 저장하게된다

<Provider store={store}>

<App />

</Provider>,

document.querySelector('#root')

);

// 이번 프로젝트는 redux-thunk를 활용하여 비동기식 처리 방법이다

// Thunk는 특정작업을 나중에 할 수 있도록 미루기 위해 함수 형태로 감싼 것을 의미합니다.

// 그리고 보통의 액션생성자는 그냥 하나의 액션객체를 생성할 뿐이지만 redux-thunk를 통해 만들게 되면 그 내부에서 여러가지 작업을 할 수도 있습니다

 

// 총정리

// react-redux의 Provider로 App를 묶어주면 App에 적용될 태그들은 store의 reducers 정보를 적용할 수 있다

// 적용하려고 하는 클래스를 connect함수로 연결하면 사용할 수 있다

 

// redux는 액션 함수들을 한 번에 묶어주어 리듀서를 만들어주어 관리해준다. 이때 redux의 combineReducers 함수를 사용하여야만 한다

 

Thunk와 Saga의 차이점

두 방식의 가장 큰 차이점을 문장 구조라고 생각을 할 것입니다. 그것이 가장 큰 사실이지만 더 큰 차이점이 존재하는데,

Thunks는 절대로 action에 응답을 줄 수 없습니다.

반면 Redux-Saga는 store를 구독하고 특정 작업이 디스패치 될 때 saga가 실행되도록 유발할 수 있습니다.

 

 

actions / index.js

import _ from 'lodash';

import jsonPlaceholder from '../apis/jsonPlaceholder'

 

export const fetchPostsAndUsers = () => async (dispatch, getState) => {

await dispatch(fetchPosts());

 

// thunk의 장점중 하나는 dispatch를 함수로 할수 있다는 것이다 그렇게 전체적인 fetchPostsAndUsers 를 만들어서  호출하여 fetchPosts(), fetchUser(id) 두개를 dispatch하는 과정에서 데이터를 조작하여 id를 주입하게된다

 

// const userIds = _.uniq(_.map(getState().posts, 'userId'));

// userIds.forEach(id => dispatch(fetchUser(id)));

 

_.chain(getState().posts) // lodash의 chain()를 사용하면 가독성에도 좋다 한번 참조하자

.map('userId')

.uniq()

.forEach(id => dispatch(fetchUser(id)))

.value()

};

 

지금의 문제점은 userid의 중복된 호출이다

이것을 보다 성능을 향상시키기 위해 _.memozie()를 활용하는 것과 각종 중복배열을 제거하고

새로운 배열을 만들어 dispatch한다 위의 코드분석은 아래의 블로그를 참조하자

http://kbs0327.github.io/blog/technology/lodash/ , https://gracefullight.dev/2016/12/25/Lodash-활용법/

_.uniq() 배열의 중복제거,

_.map() 특정 값들의 제 배치,

forEach() 배열 나열

 

// export const fetchUser = id => dispatch => _fetchUser(id, dispatch);

// const _fetchUser = _.memoize(async (id, dispatch) => {

// const response = await jsonPlaceholder.get(`/users/${id}`);

 

// dispatch({ type: 'FETCH_USERS', payload: response.data });

// });

 

그 변수는 매개변수로 지정된 함수와 같은 기능을 하는 동시에 이 함수는 메모리에 저장되어 동일한 인자값을 받을 때에는

네트워크에 거치지않고 자체적으로 메모리에 저장하여 request를 하지 않는다는 것이 장점이다

const getUser(int v){

return " hello world";

}

//const memoizedGetUser = _.memoize(getUser);

//memoizedGetUser(3)

//-> " hello world"

위의 예문처럼 최초 한번 네트워크에 요청하면 로그가 남는다. 하지만 그 다음에는 로그가 남지 않음을 알 수 있다

지금처럼 유저 정보가 반복될 때 네트워크에 요청하지 않고 재사용할 수 있다

 

export const fetchPosts = () => async dispatch => {

console.log(dispatch) // api는 개별적으로 관리를 하고 그것을 접근하는 것은 비동기로 처리하는 것이 중요하다

const response= await jsonPlaceholder.get('/posts');

// 그 이유는 JSX문법으로 태그를 넘겨받고 움직이는 컴퓨터의 실행력과 서버에서

// 데이터를 받아오는 시간차가 있기때문에 동기식으로는 절대 받아올 수 없다

 

dispatch({ type: 'FETCH_POSTS', payload: response.data });

// 그래서 비동기식인 async 와 await을 사용하여야 한다

// dispatch를 하려는 액션을 수동으로 불러낸다.

}; 즉, 액션을 직접적으로 돌려주는 것이 아니라 dispatch를 호출하여 액션 오브젝트를 건네어 주게 된다

 

export const fetchUser = id => async dispatch => {

// redux-thunk의 장점은 액션함수에서도 dispatch를 함수를 사용할 수 있다는 점이다

const response = await jsonPlaceholder.get(`/users/${id}`);

// 그렇게 axios로 비동기화 시켜 받아온 정보를 dispatch시켜 액션과 데이터를 건네줄수 있다

dispatch({ type: 'FETCH_USERS', payload: response.data });

};

// 이 예제를 잘 이용하면 어떠한 정보를 CURD하여 그 정보를 액션 타입에 맞게 행동하는 코드를 작성하면 괜찮을 듯 하다

// 그리고 dispatc메서드에 매개변수를 지정하는데 이때 함수를 지정하여 다른 action 파일에 있는 함수도 호출을 할 수 있게 된다

// https://velog.io/@dongwon2/Redux-Thunk-vs-Redux-Saga를-비교해-봅시다- 여기에 들어가면 상세정보를 얻을 수 있다

 

당연히 밑의 예제처럼 일반적인 action 함수도 사용이가능하다

export const selectPost =()=> {

return {

type: 'SELECT_POST' // 하지만 서버에서 DATA를 가져올때에는 비동기식으로 가져올 것을 생각해야만한다

}

})

 

apis / jsonPlaceholder.js

import axios from 'axios';

 

export default axios.create({

baseURL: 'https://jsonplaceholder.typicode.com'

});              // 단지 api를 보관하기위한 파일여러개의 export해서 사용이가능할 것이다

 

 

 

 

 

components / PostList.js

import React from 'react';

import { connect } from 'react-redux'; // react에서 리덕스로 combined된 state를 사용하기 위해서는 react-redux가 필요하고

// connect 메소드라는 함수로 연결을 해주어야한다

import { fetchPostsAndUsers } from '../actions';

import UserHeader from './UserHeader';

 

 

class PostList extends React.Component {

componentDidMount() {

this.props.fetchPostsAndUsers();

}

renderList(){

return this.props.posts.map(post => {

return (

<div className="item" key={post.id}>

<i className="large middle aligned icon user"></i>

<div className="content">

<div className="description">

<h2>{post.title}</h2>

<p>{post.body}</p>

</div>

<UserHeader userId={post.userId}/>

 

</div>

</div>

);

});

}

 

render() {

return <div className="ui relaxed divided list">{this.renderList()}</div>;

}

}

 

const mapStateToProps = (state) => {

return { posts : state.posts}

}

 

export default connect       // 일반적으로 첫 번째 인자값에는 store의 state를 인수로 받습니다.

mapStateToProps                 // store의 state는 props와 맵핑되는 방식을 만들 객체를 반환하는

                                                             mapStateToProps메서드를 사용한다

{ fetchPostsAndUsers } )(PostList); 

// 두 번째 인자값은 mapDispatchToProps는 dispatch action이 props와 맵핑되는 방식을 만들 유사한 객체를 반환한다

// fetchPostsAndUsers 로 정보를 받아와 componentDidMount에서 fetchPostsAndUsers ()를 실행시켜 액션을 취한다

//두 번째 괄호는 이 정보들을 사용할 클래스명을 쓴다

 

 

 

components / UserHeader.js

import React from 'react';

import { connect } from 'react-redux';

 

class UserHeader extends React.Component {

// componentDidMount() {                             // componentDidMount는 부모태그에서 넘겨주는 state만을 받아서 사용가능하다

// this.props.fetchUser(this.props.userId); // 그 이유는 componentDidMount는 랜더링이 끝나고 나서 처음 한번만 실행되기때문이다

// lodash추가로 DidMount 삭제 // 그래서 지금 한 번 실행될 때 fecthuser을 실행함으로써 액션을 실행하게 되고

그렇게 그 후 데이터를 불러온다

 

render() {

const { user } = this.props;

// const user = this.props.users.find(user => user.id ===this.props.userId);

     이렇게 장문으로 해서 비교해서 id를 찾는 방법이있지만

// 부모에서 받아온 props와 dispatch된 props가 혼동될수 있으니 확인하여야한다

if (!user) { // 그래서 밑에 처럼 connect후 mapstateToprops를 통해 기존 ownProps와 state를 구분해서 작업이 가능하다

return null;

}

 

return <div className="header">{user.name}</div>

}

}

 

const mapStateToProps = (state, ownProps) => {

return { user: state.users.find(user => user.id === ownProps.userId) }; // 참고합시다

}

 

export default connect(mapStateToProps)(UserHeader);

// mapstateProps와 그 fetchuser 함수를 연결함으로써 정보를 userHeader에서 사용이가능

 

 

 

components / App.js

import React from 'react';

import PostList from './PostList';

 

const App = () => { // App은 모든 컴포넌트를 받아 전달하는 의미가 있다

return (

<div className="ui container">

<PostList />

</div>

);

}

 

export default App;

 

 

 

 

 

 

728x90
반응형
728x90
반응형

Temp를 이용한 문제를 풀어보자

Temp에 있는 직원들을 부서별 직급별로 SALARY 을 보려한다 이때 부서별로 직급들이 동일행에 나오도록

보여주고 부서별 전체급여 그리고 PER컬럼의 전체 부서 급여합계 비율을 구하여라

 

SELECT CASE WHEN DEPTNO IS NOT NULL THEN DEPTNO ELSE '합계' END DEPTNO

, SUM(A.부장) 부장, SUM(A.차장) 차장, SUM(A.과장) 과장

, SUM(A.대리) 대리, SUM(A.사원) 사원, SUM(A.수습) 수습, SUM(A.PER) 전체급여비율

, SUM(A.SAL) 부서별급여합계

FROM (SELECT DEPT_CODE DEPTNO, SALARY SAL

                               , SUM(CASE WHEN LEV = '부장' THEN SALARY ELSE 0 END) 부장

                               , SUM(CASE WHEN LEV = '차장' THEN SALARY ELSE 0 END) 차장

                               , SUM(CASE WHEN LEV = '과장' THEN SALARY ELSE 0 END) 과장

                               , SUM(CASE WHEN LEV = '대리' THEN SALARY ELSE 0 END) 대리

                               , SUM(CASE WHEN LEV = '사원' THEN SALARY ELSE 0 END) 사원

                               , SUM(CASE WHEN LEV = '수습' THEN SALARY ELSE 0 END) 수습

                               , ROUND(RATIO_TO_REPORT(SALARY) OVER (),2) * 100 AS PER

                               , SUM(SALARY)

             FROM TEMP

             GROUP BY DEPT_CODE, SALARY, LEV

              ) A

GROUP BY ROLLUP(DEPTNO)

ORDER BY DEPTNO

 

-->  컬럼 합계를 하기위해서는 ROLLUP()필요하다

그리고 select 문에 DEPTNO별로 소계를 한다면 DEPTNO가 NULL이 되어서 CASE문에 합계라는 문자를 줄 수 있다  

밑의 2개의 구문으로 컬럼의 합계를 표현할 수 있다

 

SELECT CASE WHEN DEPTNO IS NOT NULL THEN DEPTNO ELSE '합계' END DEPTNO

GROUP BY ROLLUP(DEPTNO)

 

 

TEST02 테이블의 자료를 이용해 최대 CRATE를 가지는 일자의 AMT와 최소 CREATE를 가지는 일자의 AMT를 읽어오는 문장을 출력하시오

 

SELECT distinct LAST_VALUE(AMT) OVER ( ORDER BY CRATE

                                                                                        ROWS BETWEEN UNBOUNDED PRECEDING

                                                                                        AND UNBOUNDED FOLLOWING) MAX_VAL,

FIRST_VALUE(AMT) OVER( ORDER BY CRATE

                                                      ROWS BETWEEN UNBOUNDED PRECEDING

                                                      AND UNBOUNDED FOLLOWING) MIN_VAL

FROM TEST02

728x90
반응형
728x90
반응형

CASE, GROUP BY 활용

 

'HP'라는 제품은 너무 많은 등급을 가지고 있어 제품별로 분석하기를 원하고,

'LD'라는 제품은 등급의 수가 적고 중요하므로 등급별로 분석하기를 원하며,

'PP'라는 제품은 다양한 등급을 가지고 있으나 'P530C'라는 등급은 전략적으로 관리하고자 하여 등급별로 분석하고,

나머지는 기타로 모아주기를 원한다고 생각하자

 

이런 경우에는 CASE와 GROUP BY를 활용하여 풀어보자

 

SELECT

   CASE WHEN 제품 = ‘HP’ THEN 제품

   WHEN 제품 = ‘LD’ THEN 등급 ,

   WHEN 등급 = ‘P530C’ THEN 등급

   ELSE ’기타‘

   END

FROM 매출테이블

WHERE 매출일자 LIKE ’9808%’

GROUP BY CASE WHEN 제품 = ‘HP’ THEN 제품

                                  WHEN 제품 = ‘LD’ THEN 등급 ,

                                  WHEN 등급 = ‘P530C’ THEN 등급

ELSE '기타'

END

 

 

SALE_HIST 테이블을 참조하여 ‘PENCIL’ 총 판매금액과 01번 사업장의 총판매금액과

ERASER를 판매하고 있는 사업장의 총판매 금액을 출력하고 그 나머지를 기타로 묶어 총 판매금액을 출력하시오

SELECT

   CASE WHEN SALE_ITEM = 'PENCIL' THEN SALE_ITEM

   WHEN SALE_ITEM = 'ERASER' THEN SALE_SITE

   WHEN SALE_SITE = 01 THEN SALE_SITE

   ELSE '기타'

   END AS 특별관리항목,

   SUM(SALE_AMT) AS 총판매금액

FROM SALE_HIST

GROUP BY CASE WHEN SALE_ITEM = 'PENCIL' THEN SALE_ITEM

                                  WHEN SALE_ITEM = 'ERASER' THEN SALE_SITE

                                  WHEN SALE_SITE = 01 THEN SALE_SITE

ELSE '기타'

END

 

 

TEST100 테이블을 참조하여 C2에 해당하는 값의 비율을 구하여라

SELECT C1, C2

,ROUND(RATIO_TO_REPORT(C2) OVER(),2) * 100 AS PER

FROM TEST100

 

 

TEST35 테이블을 참조하여 KEY1은 부서라고 생각하고 KEY2의 A는 판매액 B는 매입액이라고 할 때

부서별 판매액과 매입액의 차에대한 값(수익)을 구하고 차액의 비율을 구하시오

SELECT

CASE WHEN KEY1 IS NOT NULL THEN KEY1 ELSE '합계' END AS KEY1

   , SUM(CASE WHEN KEY2 = 'A' THEN AMT ELSE 0 END) AS 판매액

   , SUM(CASE WHEN KEY2 = 'B' THEN AMT ELSE 0 END) AS 매입액

   , SUM(CASE WHEN KEY2 = 'A' THEN AMT ELSE - AMT END) AS 수익

   , ROUND(SUM(CASE WHEN KEY2='A' THEN AMT ELSE -AMT END)/ SUM(CASE WHEN KEY2='A' THEN AMT END)*100 )AS PER

FROM (SELECT KEY1, KEY2, SUM(AMT) AMT

               FROM TEST35

               GROUP BY ROLLUP(KEY1), KEY2

)

GROUP BY CASE WHEN KEY1 IS NOT NULL THEN KEY1 ELSE '합계' END

ORDER BY KEY1

728x90
반응형
728x90
반응형

8. 부서번호가 50인 사원들 중 이 부서의 평균 급여액보다 낮은 월급을 받는 사원 명단을 추출하시오

SELECT a.employee_id, a.last_name, a.salary

FROM ( SELECT employee_id, manager_id, salary, last_name

FROM employees WHERE department_id = 50 ) a,

 

(SELECT AVG (salary) avg_salary

FROM employees WHERE department_id = 50 ) b

WHERE a.salary < b.avg_salary

 

 

10. 9번의 출력문을 다시 with을 사용하여 다시 작성하시오

WITH a AS  (SELECT employee_id, manager_id, salary, last_name

                         FROM employees

                         WHERE department id = 50 ),

 

             b AS (SELECT AVG(salary) avg_salary

                         FROM employees

                         WHERE department id = 50)

SELECT a.employee id, a.last_name, a.salary

FROM a, b

WHERE a. salary < b.avg_salary

 

 

11. 전체 부서별 평균 급여액보다 부서별 급여 합계액이 큰 부서의 명단을 추출해 보자 단 서브쿼리

SELECT d.d_dep, d.SUM_sal

FROM

(SELECT a.department_name d_dep, ROUND(SUM(b.salary)) SUM_sal

FROM departments a

, employees b

WHERE a.department_id = b.department_id

GROUP BY a.department_name

) d,

(SELECT ROUND(SUM(sum(b.salary)) / COUNT(*)) AVG_sal

FROM departments a

, employees b

WHERE a.department_id = b.department_id

GROUP BY a.department_name

) c

WHERE d.SUM_sal > c.AVG_sal;

 

 

12 위의 방법을 다시 WITH를 이용하여 다시 작성하시오

WITH dept_costs AS (SELECT department_name, SUM(salary) dept_total

                                            FROM employees e, departments d

                                            WHERE e.department_id = d.department_id

                                            GROUP BY department_name ),

            avg_cost AS (SELECT SUM(dept_total) /COUNT (*) avg

                                       FROM dept_costs)

 

SELECT dept_costs.*

FROM dept_costs, avg_cost

WHERE dept_costs.dept_total > avg_cost.avg

 

 

13. WITH은 SELECT 절과 같이 쓰여 아주 유용한 기능을 제공한다.

10장에서 계층형 쿼리에 대해 학습했는데, 계층형 쿼리를 이용하여 EMPLOYEES 테이블에 있는 사원들을 계층적으로 조회하였다

 

SELECT LEVEL, LPAD(' ', 4* (LEVEL -1)) || first_name || ' ' || last_name "성명"

FROM employees

START WITH MANAGER_ID IS NULL

CONNECT BY manager_id = PRIOR employee_id;

 

계층형 정보를 꼭 CONNECT BY 절을 사용해서만 구현할 수 있는 것은 아니다. WITH 구문을 사용하여 위의 결과와 동일한 데이터를 추출하는 쿼리를 작성해 보자.

 

(힌트: 실제 EMPLOYEES 테이블의 사원 계층은 총 4레벨까지 존재하므로, 레벨 별로 인라인 뷰를 4개 생성해서 WITH을 사용해 생성한 인라인 뷰에서 다른 인라인 뷰를 참조하는 형태로 작성하자. 문제는 난 이도가 꽤 높다. 따라서 이 문제를 풀 정도먼 WITH 구문을 완전히 이해했다고 볼 수 있다 )

 

 

15. 0RDERS 테이블을 참조하여 연도별로 주문건수와 총 주문금액을 구하는 쿼리를 작성해 보자.

 

 

 

16. 1번 쿼리를 기준으로 해서 다음과 같은 형태의 정보를 조회하는 쿼리를 작성해 보자.

기준연도 주문건수 주문총액 이전연도_주문총액 이후연도_주문총액

xxxx 00 0000 0000000 000000

SELECT order_date, order_status, order_total,

SUM(order_total) OVER ( ORDER BY order_date

                                                    RANGE interval '1' year preceding ) AS 이전연도_총합계,

SUM(order_total) OVER ( ORDER BY order_date

                                                    RANGE BETWEEN CURRENT row AND interval '1' year following) AS 이후년도_총합계

FROM ORDERS

 

단 이전연도와 이후연도는 기준연도에서 1년을 더하거나 뺀 연도가 아니라

실제 집계된 데이터가 존재하는 연도를 말한다.

 

 

17. 1999년도 전체 영업실적 중 각 개인별 실적 비율을 구해보자

전체 실적금액으로 각 개인별 실적금액을 니누면 된다 윈도우 함수를 중 분석함수를 사용해서 풀어보자

SELECT emp.last_name 이름, SUM (ord.ORDER_total) 개인별실적,

ROUND(SUM(ord.order_total) / SUM(SUM(ord.order_total)) OVER( PARTITION BY TO_CHAR(ord.order_date, 'YYYY' ) ) ,2) 개인별비율

FROM orders ord, employees emp

WHERE TO_CHAR(ord.ORDER_date, 'YYYY') = '1999'

AND ord. sales_rep_id = emp. employee_id

GROUP BY emp.last_name, TO_CHAR(ord.order_date, 'YYYY')

ORDER BY emp.last_name;

 

 

18. 위의 결과물과 같은 출력값을 가질 수 있게 RATIO_TO_REPORT 함수를 써서 출력하시오

SELECT emp.last_name 이름, SUM(ord.order_total) 개인별실적,

ROUND (RATIO_TO_REPORT(SUM(ord.order_total)) OVER (PARTITION BY TO_chAR(ord.order_date, 'YYYY' )),2) RATIO

FROM orders ord, employees emp

WHERE TO_CHAR(ORD.ORDER_DATE, 'YYYY') = '1999'

AND ord.sales_rep_id = emp.employee_id

GROUP BY emp.last_NAME, TO_CHAR(ord.ORDER_DATE, 'YYYY')

ORDER BY emp.last_name;

728x90
반응형
728x90
반응형

1. TEST12 테이블을 참고하여 BOOK_TYPE별 가격 합계액을 구하시오 (BOOK_TYPE, PRICE만 출력)

SELECT BOOK_TYPE, SUM(PRICE)

FROM TEST12

GROUP BY BOOK_TYPE

 

 

2. 1번의 답을 참고하며, 이번에는 BOOK_TYPE의 소계를 구하시오 (BOOK_TYPE, PRICE만 출력)

SELECT BOOK_TYPE, SUM(PRICE)

FROM TEST12

GROUP BY BOOK_TYPE, ROLLUP(BOOK_NAME)

 

 

3. 1번의 답을 찹고하며, 이번에는 BOOK_TYPE별 가격 합계액을 구하시오

(BOOK_TYPE, PRESS, BOOK_NAME의 3개 컬럼의 정보를 출력하시오)

SELECT PRESS, BOOK_TYPE, SUM(PRICE)

FROM TEST12

GROUP BY PRESS, ROLLUP(BOOK_TYPE)

 

 

4. EMP, DEPT 테이블을 참고하여 부서별로 총 인원수와 급여 합계를 구하시오 (DNAME 출력하시오)

SELECT b.dname, SUM(a.sal) sal, COUNT(a.empno) emp_count

FROM emp a, dept b

WHERE a.deptno = b.deptno

GROUP BY b.dname

 

 

5. EMP, DEPT 테이블과 4번을 참고하여 부서이름과 직업별로 급여소계와 인원소계를 구하고

총인원과 총 급여를 구하여라 단, 부서번호, 부서이름, 직업, 급여, 인원수를 출력하시오

SELECT B.DEPTNO,b.dname,a.job

, SUM(a.sal) sal

, COUNT(a.empno) emp_count

FROM emp a, dept b

WHERE a.deptno = b.deptno

GROUP BY ROLLUP(B.DEPTNO,(b.dname,a.job))

 

 

6. 5번의 출력값에 GROUPING 함수를 사용해서 NULL값을 컬럼의 정보를 표시할 수 있는 문자열을 추가하시오

SELECT DECODE(B.DEPTNO, NULL, '전체합계', B.DEPTNO) AS 부서번호

                , DECODE(b.dname, NULL, '부서소계', b.dname) AS 부서이름

                , DECODE(a.job, NULL, '직업소계', a.job) AS 직업명

                , SUM(a.sal) sal

                , COUNT(a.empno) emp_count

FROM emp a, dept b

WHERE a.deptno = b.deptno

GROUP BY ROLLUP(B.DEPTNO,(b.dname,a.job))

 

 

7. 주문방법, 결혼여부, 성별,주문금액정보와 주문방법별, 결혼여부별, 성별별 주문합계금액을 출력하시오(group sets)

SELECT ORD.ORDER_mode 주문방법,

    DECODE (cus.marital_status, 'single', '미혼', 'married', '기혼') 결혼여부,

    DECODE (cus.gender, 'F', '여성', 'M', '남성') 성별,

    SUM (ord.order_total) 주문금액

FROM orders ord, customers cus

WHERE ord.customer_id = cus.customer_id

GROUP BY GROUPING SETS (ord.order_mode, cus.marital_status, cus.gender);

 

 

 

728x90
반응형
728x90
반응형

WITH

WITH 구문의 사용 (ORACLE9i R2이후 사용 가능)

WITH 문장은 인라인 뷰에 별칭(alias)을 부여하는데, 별칭을 부여히는 것으로 끝나지 않고

SELECT 문장에서 별칭이 부여된 인라인 뷰를 시용 기능하게 한다

 

특징

자주사용되는 쿼리를 사용하기전에 WITH절로 미리 쿼리 블록으로 정의한 후 사용한다

서브쿼리문에서 서브쿼리에 의해 메인 쿼리가 실행되기 때문에 서브쿼리문은 성능이 저하된다.

with절은 여러 개의 서브쿼리가 하나의 메인 쿼리에서 사용될 때 생기는 복잡성을 보다 간결하게 정의하여 사용함으로써 서브쿼리에서 발생할 수 있는 성능저하 현상을 방지할 수 있다

 

 

WITH <별칭1> AS ( SELECT 문장1 ),

             <별칭2> AS ( SELECT 문장2 FROM 별칭1 ),

 

SELECT 컬럼1, 걸림2,

FROM 별칭1, 별칭2

 

 

WITH 구문은 별칭2의 인라인 뷰를 FROM 절에서 바로 이전에 시용한 별칭1을 직접 사용할 수 있는 점이

WITH 구문만이 가진 고유한 특징이다.

 

 

 

728x90
반응형
728x90
반응형

RATIO_TO_REPORT

 

RATIO_TO_REPORT는 계산 대상 값 전체에 대한 현재 로우의 상대적인 비율 값을 반환하는 함수이다.

 

SELECT department_id, First_name, hire_date, salary,

                 ROUND(RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id),2) * 100 AS salary_percent

FROM employees

WHERE department_id IN (30, 90);

 

RATIO_TO_REPORT 함수를 사용하면 굳이 나눗셈을 수행하지 않아도 된다

RATIO_TO_REPORT 함수는 각 로우별로 PARTITION BY 절에 명시된 그룹의 총합에 대한 비율을 반환하는 함수이다.

즉 위 쿼리에서 수행했던 나눗셈을 이 함수 하나가 담당하게 된다

 

 

 

728x90
반응형
728x90
반응형

ROLLUP

ROLLUPSELECT한 컬럼들을 GROUP BY 절과 함께 사용하게 되면 소계를 구할 수 있다

그 과정에는 CARTESIAN PRODUCT를 이용한 결과물들이란 것을 알아두자

또 한 ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과바뀌게되므로 인수의 순서에도 주의하자

 

SELECT PRESS, BOOK_TYPE, BOOK_NAME, SUM(PRICE)

FROM TEST12

GROUP BY ROLLUP(PRESS, ROLLUP(BOOK_TYPE, BOOK_NAME)

 

위의 예제를 보면 알 수 있듯이 ROLLUP은 GROUP BY절에 사용하며 그룹별로 묶어주는 개념비슷하다

그리고 이 때 소계를 출력하고 싶다면 컬럼들을 ROLLUP으로 묶어주면 간단히 출력이 가능하다

 

CUBE

CUBE는 결합이 가능한 모든 값에 대하여 다차원 집계를 생성한다

GROUPING COLUMNS이 가질 수 있는 모든 경우에 대하여 SUBTOTAL을 생성해야 하는 경우에는 CUBE를 사용

ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다

 

SELECT PRESS, BOOK_TYPE, BOOK_NAME, SUM(PRICE)

FROM TEST12

GROUP BY CUBE(PRESS, BOOK_TYPE, BOOK_NAME)

 

위의 구문을 실행해보고 ROLLUP과 차이점을 생각해보자

 

 

GROUPING 함수

ROLLUP, CUBE, GROUPING SET등 새로운 그룹 함수를 지원하기위해 GROUPING 함수가 추가되었다

 

-ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1이 표시되고

-그 외의 결과는 GROUPING(EXPR) = 0이 표시된다

 

SELECT PRESS, GROUPING(PRESS), BOOK_TYPE, GROUPING(BOOK_TYPE),

BOOK_NAME, GROUPING(BOOK_NAME),SUM(PRICE)

FROM TEST12

GROUP BY ROLLUP(PRESS, BOOK_TYPE, BOOK_NAME)

 

출력값을 확인하자

 

 

GROUPING_ID(expr)와 GROUP ID() 함수

루트 노드의 경우는 1그 디음 계층은 2 3을 순차적으로 반환하였다. 그룹핑 쿼리에서도 그룹핑되어 추출된 각각의 로우들에 대해 그룹핑 레벨을 알 수 있는데 바로 GROUPING_IDO 함수를 시용하면 된다

 

SELECT

      DECODE (GROUPING (department_id) , 1, ' 전체부서', department_id) DEP,

      DECODE (GROUPING (job_id), 1, '합계', job_id) JOB,

      SUM (salary),

      GROUPING_ID (department_id) g_dep,

      GROUPING_ID (job_id) g_job,

      GROUPING_ID (department_id, job_id) g_total

FROM employees

WHERE department_id <= 40

GROUP BY CUBE(department_id, job_id)

ORDER BY 1,2;

 

결국 G_TOTAL 컬럼값은 0에서 3까지의 수를 반환하게 되는 것이다.

GROUPING_ID 함수의 표현식으로 부서번호와 직급 순서를 변경하면 G_TOTAL 컬럼이 반환하는 값도 달라지게 된다.

만약 위 쿼리에서 세부적인 정보는 제거하고 부서별 합계액직급별 합계액만 조회하고자 한다면 어떻게 해야 할까?

다음과 같이 HAVING 절에 GROUPING_ID() 함수사용하여 조건을 주면 된다

 

SELECT

   DECODE(GROUPING(department_id), 1, '전체부서 ', department_id) DEP,

   DECODE (GROUPING(job_id), 1, '합계', job_id) JOBS,

   SUM (salary)

FROM employees

WHERE department_id <= 40

GROUP BY CUBE (department_id, job_id)

HAVING GROUPING_ID(department_id, job_id) > 0 ORDER BY 1, 2;

 

 

GROUPING SETS(expr) 표현식

쿼리는 단순히 주문방법, 결혼여부, 성별 별로 주문금액을 집계한 것이다.

SELECT ORD.ORDER_mode 주문방법,

   DECODE (cus.marital_status, 'single', '미혼', 'married', '기혼') 결혼여부,

   DECODE (cus.gender, 'F', '여성', 'M', '남성') 성별,

   SUM (ord.order_total) 주문금액

FROM orders ord, customers cus

WHERE ord.customer_id = cus.customer_id

GROUP BY GROUPING SETS (ord.order_mode, cus.marital_status, cus.gender);

 

위의 GROUP함수의 결과값을 비교해보자

GROUPING SETS은 CUBE를 사용한 쿼리에서 개별표현식에 대한 전체합계만을 따로 추출한 것과 같다고 볼 수 있다.

 

 

 

 

 

728x90
반응형
728x90
반응형

1. SALE_HIST의 자료로 일자별 품목별로 ‘01’, ‘02’ 사업장 판매 금액합계, ‘03’, ‘04’ 사업장 판매 금액 합을 구하시오

SELECT SAEL_DATE,

                 SUM(CASE WHEN SALE_SITE BETWEEN ‘01’ AND ‘02’ THEN SALE_AMT END) AS S01

               , SUM(CASE WHEN SALE_SITE BETWEEN ‘03’ AND ‘04’ THEN SALE_AMT END) AS S02

FROM SALE_HIST

GROUP BY SALE_DATE

 

 

2. TEMP의 자료를 이용해 한 행에 5명의 사번과 성명을 보여주는 QUERY를 작성해 보라.

SELECT CEIL(ROWNUM/5) C0

,MAX(DECODE(MOD(ROWNUM, 5), 1, EMP_ID, NULL)) C1

,MAX(DECODE(MOD(ROWNUM, 5), 1, EMP_NAME, NULL)) C2

,MAX(DECODE(MOD(ROWNUM, 5), 2, EMP_ID, NULL)) C3

,MAX(DECODE(MOD(ROWNUM, 5), 2, EMP_NAME, NULL)) C4

,MAX(DECODE(MOD(ROWNUM, 5), 3, EMP_ID, NULL)) C5

,MAX(DECODE(MOD(ROWNUM, 5), 3, EMP_NAME, NULL)) C6

,MAX(DECODE(MOD(ROWNUM, 5), 4, EMP_ID, NULL)) C7

,MAX(DECODE(MOD(ROWNUM, 5), 4, EMP_NAME, NULL)) C8

,MAX(DECODE(MOD(ROWNUM, 5), 0, EMP_ID, NULL)) C9

,MAX(DECODE(MOD(ROWNUM, 5), 0, EMP_NAME, NULL)) C10

FROM TEMP

GROUP BY CEIL(ROWNUM/5)

 

 

3. TEST14와 TEST13을 이용하여 Cartesian Product을 이용하여 테이블을 조인하시오

SELECT *

FROM TEST14 , TEST13

 

 

4. 고객이 어떤 해당 상품을 받아야 하는지 TEST14와 TEST13을 이용하여 고객과 상품LIST를 출력하시오

두 테이블은 POINT에 관련이 있다

SELECT A.CUST, B.GIFT

FROM TEST14 A, TEST13 B

WHERE A.POINT > 0

AND A.POINT BETWEEN B.FPOINT AND B.TPOINT

 

 

5. TEST14와 TEST13을 이용하여 상품별로 준비되어야 할 개수를 알아보는 문장을 작성 하시오

SELECT B.GIFT, COUNT(A.CUST)

FROM TEST14 A, TEST13 B

WHERE A.POINT > 0

AND A.POINT BETWEEN B.FPOINT AND B.TPOINT

GROUP BY B.GIFT

 

 

6.고객이 가진 포인트보다 하한금액이 낮은 모든 등급의 상품을 선택할 수 있다고 할 때 갈비세트를 상품으로 받을 수 있는 고객의 고객번호, 포인트, 선물을 보여주는 QUERY를 만들어보라

SELECT *

FROM TEST14 A, TEST13 B

WHERE B.FPOINT <=  A.POINT

AND B.GIFT ='갈비세트’

 

CREATE TABLE LOGTABLE(USERID VARCHAR(10), STIME VARCHAR(5), ETIME VARCHAR(5));

 

INSERT INTO LOGTABLE

SELECT '사용자1', '07:00', '11:00'

SELECT '사용자2', '10:00', '15:00'

SELECT '사용자3', '11:00', '12:00';

 

CREATE TABLE SETIME(STIME VARCHAR(5), ETIME VARCHAR(5));

 

INSERT INTO SETIME VALUES('00:00', '01:00');

INSERT INTO SETIME VALUES('01:00', '02:00');

INSERT INTO SETIME VALUES('02:00', '03:00');

INSERT INTO SETIME VALUES('03:00', '04:00');

INSERT INTO SETIME VALUES('04:00', '05:00');

INSERT INTO SETIME VALUES('05:00', '06:00');

INSERT INTO SETIME VALUES('06:00', '07:00');

INSERT INTO SETIME VALUES('07:00', '08:00');

INSERT INTO SETIME VALUES('08:00', '09:00');

INSERT INTO SETIME VALUES('09:00', '10:00');

INSERT INTO SETIME VALUES('10:00', '11:00');

INSERT INTO SETIME VALUES('11:00', '12:00');

INSERT INTO SETIME VALUES('12:00', '13:00');

INSERT INTO SETIME VALUES('13:00', '14:00');

INSERT INTO SETIME VALUES('14:00', '15:00');

INSERT INTO SETIME VALUES('15:00', '16:00');

INSERT INTO SETIME VALUES('16:00', '17:00');

INSERT INTO SETIME VALUES('17:00', '18:00');

INSERT INTO SETIME VALUES('18:00', '19:00');

INSERT INTO SETIME VALUES('19:00', '20:00');

INSERT INTO SETIME VALUES('20:00', '21:00');

INSERT INTO SETIME VALUES('21:00', '22:00');

INSERT INTO SETIME VALUES('22:00', '23:00');

INSERT INTO SETIME VALUES('23:00', '24:00');

 

 

7. 위의 구문을 INSERT하고 출퇴근 시간대별로 몇 사람이 출퇴근했는지 사람 수와 출퇴근 시간을 출력하여라

SELECT S.STIME || '-' || S.ETIME, COUNT(*)

FROM LOGTABLE L, SETIME S

WHERE L.STIME < S.ETIME AND L.ETIME >= S.STIME

GROUP BY S.STIME || '-' || S.ETIME

ORDER BY S.STIME || '-' || S.ETIME

 

 

TEST34 활용 문제 8, 9

INSERT INTO TEST34 VALUE ('A10',4,100);

INSERT INTO TEST34 VALUE ('A11',4,110);

COMMIT;

8. 위 구문을 인설트하고 KEY_TYPE이 ‘4’인 경우에 ‘1’, ‘2’, ‘3’ 에 모두 더하고 ‘1’의 합과 ‘2’, ‘3’의 합을 구해라

선택적 조인문과 DECODE를 활용하시오

SELECT T2.R_CNT, SUM(AMT) AMT

FROM TEST34 T1, (SELECT ROWNUM R_CNT

                                       FROM TEST34

                                       WHERE ROWNUM<4) T2

WHERE T1.KEY_TYPE = DECODE(T1.KEY_TYPE, 4, T1.KEY_TYPE, T2.R_CNT)

GROUP BY T2.R_CNT;

 

 

key type amt rcnt

A01 1 10 1 true

A03 1 30 1 tur

A07 1 70 1

A09 1 90 1

A10 4 100 1 true

A11 4 110 1

410

 

A02 2 20 2

A04 2 40 2

A08 2 80 2

A10 4 100 2

A11 4 110 2

350

 

A05 3 50 3

A06 3 60 3

A10 4 100 3

A11 4 110 3

320

 

 

9. 위 구문을 인설트하고 KEY_TYPE이 ‘4’인 경우에 ‘1’, ‘2’, ‘3’ 에 모두 더하고 ‘1’, ‘2’, ‘3’의 합을 구해라

선택적 조인문과 윈도우 함수를 활용하시오 LAG()

SELECT *

FROM (SELECT DECODE(KEY_TYPE,1,'A',2,'B',3,'C') AS KEY

               , SUM(AMT) 

                 + LAG(SUM(AMT), DECODE(KEY_TYPE,1,1,2,2,3,3)) OVER (ORDER BY DECODE(KEY_TYPE,1,'A',2,'B',3,'C')

                                                                                                                                      NULLS FIRST) AS AMT

               FROM TEST34

              GROUP BY KEY_TYPE

              )

WHERE KEY IS NOT NULL

 

 

 

728x90
반응형
728x90
반응형

선택적 조인

Lateral View를 활용한 튜닝

Lateral View와 아우터조인의 개념에 대해서는 이미 언급이 되었고

이번에는Lateral View 를 이용한 튜닝에 대하여 알아보기로 한다.

 

먼저 개발자들에게 받는 질문 상위 10개 중에 항상 들어있는 질문이 있다.

"선택적으로 조인하는 기능이 오라클에 있습니까?"

필자는 항상 다음과 같이 답변한다.

"있습니다."

아래 모델을 보자.

 

이모델을 보면 고객유형(subtype) 에 따라서 개인기본으로 조인할지 사업자기본으로 조인할지 결정이 되는것이다.

물론 연락처기본은 고객기본과 항상 1:1 이다.

이런 모델에서 대부분의 개발자는 아래와 같은 SQL 을 작성한다.

 

SELECT

A.고객번호, A.고객유형,

B.취미코드, B.종교코드,

C.사업규모코드, C.종업원수,

D.대표핸드폰번호

FROM 고객기본 A, 개인기본 B, 사업자기본 C, 연락처기본 D

WHERE A.고객번호 = B.고객번호 (+)

AND A.고객번호 = C.고객번호(+)

AND A.고객번호 = D.고객번호

AND A.고객번호 = :V_고객번호; --> 고객번호에 고객유형이 개인인 고객번호 대입함.

언뜻 보기에 위의 SQL 은 아무 문제가 없어보인다.

하지만 과연 그런가?

아래 Trace 결과를 보자

 

Rows Row Source Operation

------- ---------------------------------------------------

0 STATEMENT 1 NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=225 us)

1 NESTED LOOPS OUTER (cr=11 pr=0 pw=0 time=186 us)

1 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=145 us)

1 TABLE ACCESS BY INDEX ROWID 고객기본 (cr=4 pr=0 pw=0 time=81 us)

1 INDEX UNIQUE SCAN PK_고객기본 (cr=3 pr=0 pw=0 time=38 us)

1 TABLE ACCESS BY INDEX ROWID 연락처기본 (cr=4 pr=0 pw=0 time=47 us)

1 INDEX UNIQUE SCAN PK_연락처기본 (cr=3 pr=0 pw=0 time=26 us)

0 TABLE ACCESS BY INDEX ROWID 사업자기본 (cr=3 pr=0 pw=0 time=33 us)

0 INDEX UNIQUE SCAN PK_사업자기본 (cr=3 pr=0 pw=0 time=29 us)

1 TABLE ACCESS BY INDEX ROWID 개인기본 (cr=4 pr=0 pw=0 time=37 us)

1 INDEX UNIQUE SCAN PK_개인기본 (cr=3 pr=0 pw=0 time=25 us)

 

개인고객임에도 불구하고 사업자기본 테이블 및 인덱스에 3블럭(cr =3)씩 read 한것을 볼수 있다.

위의 SQL 은 항상 고객번호 인덱스로 개인기본과 사업자 기본을 뒤진후에 연락처기본과 조인하는 구조이다.

다시말하면 개인고객인경우도 사업자기본 테이블을 access 하고 사업자고객인 경우도 개인기본 테이블을 access 한다는 뜻이다.

 

아래처럼 ANSI SQL 을 사용하여 SQL 을 수정하면 오라클은 Lateral View 로 변환하여 비효율적인 access 를 방지한다.

고객유형에 따라서 개인일 경우 개인 기본 테이블만 access하고 고객유형이 사업자일 경우는 사업자기본 테이블만 access 한다.

 

SELECT

A.고객번호, A.고객유형,

B.취미코드, B.종교코드,

C.사업규모코드, C.종업원수,

D.대표핸드폰번호

FROM 고객기본 A

left outer join 개인기본 B

on (A.고객번호 = B.고객번호 and A.고객유형 = '1') --> 고객유형이 개인 일경우만 조인됨

 

left outer join 사업자기본 C

on (A.고객번호 = C.고객번호 and A.고객유형 = '2') --> 고객유형이 사업자 일경우만 조인됨

 

join 연락처기본 D

on (A.고객번호 = D.고객번호) --> 무조건 조인한다.

 

WHERE A.고객번호 = :V_고객번호; --> 고객번호에 고객유형이 개인인 고객번호 대입함

 

이것을 Trace 에서 차이를 비교해보면 read 한 블럭수가 차이난다.

즉 개인고객이면 사업자기본 테이블을 읽은 블럭수가 0 이고 사업자고객이면 개인기본 테이블을 읽은 블럭수가 0 이라는 뜻이다.

아래의 Trace 결과를 보자.

 

Rows Row Source Operation

------- ---------------------------------------------------

0 STATEMENT 1 NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=225 us)

1 NESTED LOOPS OUTER (cr=11 pr=0 pw=0 time=186 us)

1 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=145 us)

1 TABLE ACCESS BY INDEX ROWID 고객기본 (cr=4 pr=0 pw=0 time=81 us)

1 INDEX UNIQUE SCAN PK_고객기본 (cr=3 pr=0 pw=0 time=38 us)

1 TABLE ACCESS BY INDEX ROWID 연락처기본 (cr=4 pr=0 pw=0 time=47 us)

1 INDEX UNIQUE SCAN PK_연락처기본 (cr=3 pr=0 pw=0 time=26 us)

0 TABLE ACCESS BY INDEX ROWID 사업자기본 (cr=0 pr=0 pw=0 time=33 us)

0 INDEX UNIQUE SCAN PK_사업자기본 (cr=0 pr=0 pw=0 time=29 us)

1 TABLE ACCESS BY INDEX ROWID 개인기본 (cr=4 pr=0 pw=0 time=37 us)

1 INDEX UNIQUE SCAN PK_개인기본 (cr=3 pr=0 pw=0 time=25 us)

 

자주 엑세스 하는 뷰를 만들때도 위와 같은 SQL 로 만들어야 할것이다.

ANSI SQL 을 사용할수 없는 구조라면 아래처럼 DECODE 함수를 활용하면 위와 같은 효과를 얻을수 있다.

 

SELECT

A.고객번호, A.고객유형,

B.취미코드, B.종교코드,

C.사업규모코드, C.종업원수,

D.대표핸드폰번호

FROM 고객기본 A, 개인기본 B, 사업자기본 C, 연락처기본 D

WHERE DECODE(A.고객유형, '1',A.고객번호) = B.고객번호(+) --> 고객유형이 개인일경우만 조인됨

AND DECODE(A.고객유형, '2',A.고객번호) = C.고객번호(+) --> 고객유형이 사업자 일경우만 조인됨

AND A.고객번호 = D.고객번호

AND A.고객번호 = :V_고객번호;

 

 

 

 

 

 

 

728x90
반응형
728x90
반응형

부등호 조인

이번 장은 기본 개념 부분에서 다룬 조인에 대한 심화학습이다

 

1. 부등호 조인에 대한 복습

2. 조인을 이용해 해결하는 방법과 서브쿼리로 해결하는 방법을 제시한다

   두가지 해법의 비교를 통해 언제 조인과 서브쿼리가 같은 결과를 낼 수 있는지를 알아보기 바란다

3. 경우에 따라 선택적으로 조인을 하는 문제를 다뤄본다

 

참고로 부등호 조인은 Cartesian Product와 많은 관련이 있으므로 기본적인 의미를 알아야만 이해하기가 쉽다

Cartesian Product WHERE절에 조인조건 없이 조인하는 경우 테이블 ROW 개수만큼 데이터가 복제되는 현상이다.

 

부등호 조인은 조인을 할 경우 BETWEEN, LIKE, 부등호 같은 연산자 조인이 필요할 때 사용한다

부등호 조인이 일어나기전 Cartesian Product로 조인이 일어난다 그리고 where절의 조건문에 의해 행마다 조건에 부합하는 행들이 출력되는 것이다

(이것은 부등호조인만이 아니라 테이블을 조인할 때에는

select문 순서에 의해 모든 테이블이 Cartesian Product이 일어나는 것을 알수 있다)

이 때 부등호 조인은 한 개의 테이블의 컬럼 값이 다른 테이블 두 개의 컬럼에 수치상으로 포함될 때 두 테이블은 하나의 테이블로 합쳐지고 그 합쳐질 때

두 개를 부등호 조인으로 행마다 조건절을 주어 거기에 해당하는 행만 출력하는 조건이다

 

- 부등호조인 단점

cartesian productjoin을 하면서 부등호 조건을 동시에 비교하기 때문에

칼럼이 많을 수록 연산속도가 저하

100행 연산 0.38 / 1000행 연산 16.80

 

728x90
반응형
728x90
반응형

27 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼에 해당 사원과 이전 사원 급여액 합계를 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (ORDER BY SALARY

                                       ROWS 1 PRECEDING ) AS 이전사원급여합계

FROM TEMP

 

 

28, 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼에 해당 부서별 사원과 이전 사원 급여액 합계를 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (PARTITION BY DEPT_CODE

                                       ORDER BY SALARY

                                       ROWS 1 PRECEDING ) AS 부서별이전사원급여합계

FROM TEMP

 

 

29 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼 3개를 생성하여 첫번째는 사원들의 급여의 총액

두 번째는 사원들의 누적급여총액 그리고 또 다른 하나의 세 번째는 두 번째를 역순으로 표시하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (ORDER BY SALARY

                                       ROWS BETWEEN UNBOUNDED PRECEDING

                                       AND UNBOUNDED FOLLOWING ) AS 부서사원급여합계

 

         ,SUM(SALARY) OVER (ORDER BY SALARY

                                      ROWS UNBOUNDED PRECEDING ) AS 부서사원누적급여합계

 

         ,SUM(SALARY) OVER (ORDER BY SALARY DESC

                                      ROWS BETWEEN CURRENT ROW

                                      AND UNBOUNDED FOLLOWING ) AS 부서사원누적역순급여합계

FROM TEMP

 

 

30 29번 결과 값을 참고하여 사원이 아닌 부서별로 사원들의 총액, 부서별 사원들의 누적급여액 등을 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

                ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY

                                                            ROWS BETWEEN UNBOUNDED PRECEDING

                                                            AND UNBOUNDED FOLLOWING ) AS 부서사원급여합계

 

               ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY

                                                            ROWS UNBOUNDED PRECEDING ) AS 부서사원누적급여합계

 

                ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY DESC

                                                            ROWS BETWEEN CURRENT ROW

                                                            AND UNBOUNDED FOLLOWING ) AS 부서사원누적역순급여합계

FROM TEMP

 

 

31 일자별 사업장별 매출액과 사업장별 매출액의 3일 이동평균 금액을 구하라 (SALE_HIST)

31번 문제는 PARTITION, GROUP, RANGE 활용 문제이다

SELECT SALE_DATE, SALE_SITE, SUM(SALE_AMT),

                 ROUND(AVG(SUM(SALE_AMT)) OVER( PARTITION BY SALE_SITE

                                                                                              ORDER BY SALE_DATE

                                                                                              RANGE INTERVAL '2' DAY PRECEDING)) AS 이동평균

FROM SALE_HIST

GROUP BY SALE_DATE, SALE_SITE

 

 

32 각 ROW의 판매액, 동일일자/ 동일품목의 최대판매액, 최대판매액사업장, 해당 사업장 최소판매액, 최소판매액 사업장을 구하여라

SELECT SALE_DATE, SALE_ITEM, SALE_AMT, SALE_SITE

          ,FIRST_VALUE(SALE_AMT) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                    ORDER BY SALE_AMT DESC

                                                    ROWS BETWEEN UNBOUNDED PRECEDING

                                                    AND UNBOUNDED FOLLOWING ) AS 최고판매액

 

         ,FIRST_VALUE(SALE_SITE) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                   ORDER BY SALE_AMT DESC

                                                   ROWS BETWEEN UNBOUNDED PRECEDING

                                                   AND UNBOUNDED FOLLOWING ) AS 최고판매사업장

 

        ,LAST_VALUE(SALE_AMT) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                 ORDER BY SALE_AMT DESC

                                                 ROWS BETWEEN UNBOUNDED PRECEDING

                                                 AND UNBOUNDED FOLLOWING ) AS 최저판매액

 

        ,LAST_VALUE(SALE_SITE) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                 ORDER BY SALE_AMT DESC

                                                 ROWS BETWEEN UNBOUNDED PRECEDING

                                                 AND UNBOUNDED FOLLOWING ) AS 최저판매사업장

FROM SALE_HIST;

 

 

33 SALE_HIST의 자료를 이용하여‘01’ 사업장‘ PENCIL’ 품목의 일자별 누적 판매금액을 구하여라

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER ( ORDER BY SALE_DATE

                                          RANGE INTERVAL '5' DAY PRECEDING) AS 누적합계

FROM SALE_HIST

WHERE SALE_ITEM = 'PENCIL'

AND SALE_SITE = '01'

 

 

34 품목별/일자별 과거 판매매액을 모두 이용하는 이동평균값을 구하라

SELECT SALE_ITEM , SALE_DATE, SUM(SALE_AMT)

          ,AVG(SUM(SALE_AMT)) OVER( PARTITION BY SALE_ITEM

                                                 ORDER BY SALE_DATE

                                                 ROWS UNBOUNDED PRECEDING ) AS 품목일자별이동평균

FROM SALE_HIST

GROUP BY SALE_ITEM , SALE_DATE

 

 

35 SALE_HIST의 자료를 이용하여 “01”사업장 PENCIL 품목에 대해 일자별 매출액과 전일 매출, 당일과 전일의 매출액 차이를 구하시오

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT

          , LAG(SALE_AMT,1) OVER ( ORDER BY SALE_DATE) AS 전일매출

          , SALE_AMT - LAG(SALE_AMT,1) OVER ( ORDER BY SALE_DATE) AS 전일매출차액

FROM SALE_HIST

WHERE SALE_ITEM = 'PENCIL'

AND SALE_SITE = '01’

 

 

 

 

 

 

 

728x90
반응형
728x90
반응형

 

21 TEMP의 자료에서 ROWNUM을 채취해 값이 5와 10 사이에 있는 행의 ROWNUM, 사번, 이름을 조회하시오

SELECT NUM, EMP_ID, EMP_NAME

FROM ( SELECT ROWNUM NUM, EMP_ID, EMP_NAME FROM TEMP)

WHERE NUM > 4 AND NUM <11

 

 

22

INSERT INTO TDEPT ( DEPT_CODE, DEPT_NAME, PARENT_DEPT, USE_YN, AREA, BOSS_ID ) VALUE

(‘000000’, ‘사장실’, ‘ ’, ‘Y’, ‘서울’, ‘ ’)

 

위의 구문을 COMMIT 하고

기존 테이블에 최상위 부서로 지정되어있는 경영지원 부서를 사장실로 변경한다

UPDATE TDEPT

SET PARENT_DEPT = '000000'

WHERE DEPT_CODE=PARENT_DEPT;

 

 

23 사장실을 기준으로 계층구조 전개를 하여 출력하시오 (LPAD를 활용)

SELECT LPAD(DEPT_NAME, LEVEL*12,' ')AS 부서이름

,DEPT_CODE

,PARENT_DEPT

FROM TDEPT

START WITH DEPT_CODE='000000'

CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT

 

 

24 CA0001의 하위 부서를 모두 제거 하고 계층구조 전개를 하여 출력하시오 (LPAD를 활용)

SELECT LPAD(DEPT_NAME, LEVEL*12,' ')AS 부서이름, DEPT_CODE, PARENT_DEPT

FROM TDEPT

CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT

AND DEPT_CODE <> ‘CA0001’

START WITH DEPT_CODE='000000'

 

CONNECT 와 조건절의 순서를 어디에 배치하는가에 따라 그 느낌도 구조도 달라진다

"CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT" 

->  왼쪽의 구문은 CONNECT가 일어나고 그다음

 

"AND DEPT_CODE <> ‘CA0001’" 

->  CONNECT에 대한 조건절을 하나 더 추가하는 느낌으로 CONNECT가 생성이되고 나서 조건절에 대한 계층에 의미를 부여한다 그래서 CA0001에 해당하는 하위부서 까지 모두 제거된다

 

============================================================================

 

SELECT LPAD(DEPT_NAME, LEVEL*12,' ')AS 부서이름, DEPT_CODE, PARENT_DEPT

FROM TDEPT

WHERE DEPT_CODE <> ‘CA0001’

CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT

START WITH DEPT_CODE='000000'

 

 

테이블의 조건절로 사용되며 테이블에서 먼저 CA0001을 제거한 후

WHERE DEPT_CODE <> ‘CA0001’ 

->  CONNECT를 실시하게 된다 그래서 CA0001만 없어지고

 

CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT 

->  CA0001의 하위부서는 그대로 남겨지게된다

 

 

25 어느 한 사원이 영업2(CD0001)부서에 근무하고 있다 자기의 상위 부서를 출력하시오

(계층구조 전개로출력)

SELECT LEVEL, LPAD(DEPT_NAME, LEVEL*12,' ') AS 부서이름 ,DEPT_CODE ,PARENT_DEPT

FROM TDEPT

START WITH DEPT_CODE = 'CD0001'

CONNECT BY PRIOR PARENT_DEPT=DEPT_CODE

 

 

26 부서별로 부서장(BOSS_ID)과 성명(EMP_ID)를 부서의 전개에 의해 도출된 결과와 같은 형식으로 출력하시오

SELECT A.LEV, A.EMP_NAME, B.BOSS, B.DEPT_CODE, B.DEPT_NAME

FROM TEMP A,

         (SELECT LEVEL LEV, LPAD(BOSS_ID, LEVEL*12,' ') BOSS

                    ,DEPT_CODE, DEPT_NAME

          FROM TDEPT

          START WITH DEPT_CODE = '000000'

          CONNECT BY PRIOR DEPT_CODE= PARENT_DEPT

          ) B

WHERE A.EMP_ID(+)=B.BOSS

 

 

728x90
반응형
728x90
반응형

SALE_HIST의 자료를 이용하여 사업장의 상품 판매금액과 아이템순으로 일자별 판매누적금액을 출력하시오

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER (PARTITION BY SALE_DATE ORDER BY SALE_ITEM

                                          ROWS UNBOUNDED PRECEDING) AS 누적합계

FROM SALE_HIST

 

 

SALE_HIST의 자료를 이용하여 사업장의 상품 판매금액과 일자순으로 아이템별 판매누적금액을 출력하시오

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER (PARTITION BY SALE_ITEM ORDER BY SALE_DATE

                                           ROWS UNBOUNDED PRECEDING) AS 누적합계

FROM SALE_HIST

 

 

SALE_HIST의 자료를 이용하여 사업장의 상품 판매금액과 일자와 아이템별 판매누적금액을 출력하시오

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER (PARTITION BY SALE_ITEM, SALE_DATE ORDER BY SALE_AMT

                                           ROWS UNBOUNDED PRECEDING) AS 누적합계

FROM SALE_HIST

 

 

SALE_HIST의 자료를 이용하여 ‘01 사업장‘ ’PENCIL’ 품목의 아이템별 판매금액과 누적 판매금액을 구하여라

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER (PARTITION BY SALE_ITEM ORDER BY SALE_DATE

                                           RANGE INTERVAL '5' DAY PRECEDING) AS 누적합계

FROM SALE_HIST

WHERE SALE_ITEM = 'PENCIL'

AND SALE_SITE = '01’

 

 

일자별 사업장별 매출액과 사업장별 매출액의 3일 이동평균 금액을 구하라 (SALE_HIST)

SELECT SALE_DATE, SALE_SITE, SUM(SALE_AMT),

          ROUND(AVG(SUM(SALE_AMT)) OVER (PARTITION BY SALE_SITE ORDER BY SALE_DATE

                                                            RANGE INTERVAL '2' DAY PRECEDING) AS 이동평균

FROM SALE_HIST

GROUP BY SALE_DATE, SALE_SITE

 

 

EMP테이블에서 직속상관 MGR, 자기이름 ENAME, 자기급여 SAL 출력하여라 단, 같은 MGR별 직속부하들 중 최고 급여를 받는 사람들의 정보를 출력할 것

SELECT MGR, ENAME, SAL

FROM (SELECT MGR, ENAME, SAL,

                    MAX(SAL) OVER (PARTITION BY MGR) AS IV_MAX_SAL

           FROM EMP)

WHERE SAL = IV_MAX_SAL;

 

 

A_TB의 자료를 이용하여 매월의 A_OUT과 분기별 누계를 구하시오

SELECT A_MON, A_OUT,

          SUM(A_OUT) OVER (PARTITION BY TO_CHAR(TO_DATE(A_MON,'YYYYMM'),'Q')

                                      ORDER BY TO_DATE(A_MON,'YYYYMM')

                                      RANGE INTERVAL '2' MONTH PRECEDING) AS C1

FROM A_TB

 

 

TEST02의 20010901부터 20010911까지의 자료를 이용해 해당 일자 보다 작거나 같은 날의 환율을 순서대로 6개까기지 보여주는 QUERY를 만들어 보자

SELECT CDATE, AMT, CRATE

          , LAG(CRATE,1) OVER (ORDER BY CDATE) AS "-1 DAY"

          , LAG(CRATE,2) OVER (ORDER BY CDATE) AS "-2 DAY"

          , LAG(CRATE,3) OVER (ORDER BY CDATE) AS "-3 DAY"

          , LAG(CRATE,4) OVER (ORDER BY CDATE) AS "-4 DAY"

          , LAG(CRATE,5) OVER (ORDER BY CDATE) AS "-5 DAY"

          , LAG(CRATE,6) OVER (ORDER BY CDATE) AS "-6 DAY"

FROM TEST02

WHERE CDATE BETWEEN '20010901' AND '20010911’

 

 

TEST02의 20010901부터 20010911까지의 자료를 이용해 해당 일자 보다 크거나 같은 날의 환율을 순서대로 6개까기지 보여주는 QUERY를 만들어 보자

SELECT CDATE, AMT, CRATE

, LEAD(CRATE,1) OVER (ORDER BY CDATE) AS "+1 DAY"

, LEAD(CRATE,2) OVER (ORDER BY CDATE) AS "+2 DAY"

, LEAD(CRATE,3) OVER (ORDER BY CDATE) AS "+3 DAY"

, LEAD(CRATE,4) OVER (ORDER BY CDATE) AS "+4 DAY"

, LEAD(CRATE,5) OVER (ORDER BY CDATE) AS "+5 DAY"

, LEAD(CRATE,6) OVER (ORDER BY CDATE) AS "+6 DAY"

FROM TEST02

WHERE CDATE BETWEEN '20010901' AND '20010911'

 

 

TEST02 테이블을 이용해서 20010905부터 20010910까지 데이터를 출력하되 널값은 제외하고 출력하시오

현재일자의 금액에 전일 환율을 곱한 환산금액을 구하여 현재 일자, 현재 금액, 전일 환율, 환산금액을 출력하여라

SELECT C_DATE, C_AMT, 금일환율, 전일환율, 환산금액

FROM (SELECT CDATE C_DATE, AMT C_AMT, CRATE 금일환율

                    ,LAG(CRATE,1) OVER(ORDER BY CDATE) AS 전일환율

                    ,AMT*LAG(CRATE,1) OVER(ORDER BY CDATE) AS 환산금액

          FROM TEST02

          WHERE CDATE BETWEEN '20010904' AND '20010910'

          )

WHERE 환산금액 IS NOT NULL

 

 

TEMP의 EMP_ID를 이용해 자신의 사번과 자신보다 한 단계 빠른 사번을 가진 직원의 사번과 성명을 보여주는 QUERY를 만들어라 ( LAG(), MIN(), MAX() 함수 활용을 생각해보자 )

SELECT EMP_ID, EMP_NAME

          ,LAG(EMP_ID,1) OVER (ORDER BY EMP_ID) AS 이전사원사번

          ,LAG(EMP_NAME,1) OVER (ORDER BY EMP_ID) AS 이전사원이름

FROM TEMP

 

,MIN(EMP_ID) OVER (ORDER BY EMP_ID

                             ROWS BETWEEN 1 PRECEDING

                                       AND 1 PRECEDING) AS 전사원사번

 

,MAX(EMP_ID) OVER (ORDER BY EMP_ID

                              ROWS BETWEEN UNBOUNDED PRECEDING

                                        AND 1 PRECEDING) AS 이전사원사번

 

 

TEMP의 자료를 이용하여 자신의 사번, 성명, SALARY를 읽고, 자신을 포함한 이후 사번 중 SALARY가 가장 큰 금액을 함께 보여라

SELECT EMP_ID, EMP_NAME, SALARY

          ,MAX(SALARY) OVER (ORDER BY EMP_ID

                                       ROWS BETWEEN CURRENT ROW

                                                 AND UNBOUNDED FOLLOWING ) AS 이후최고금액

FROM TEMP

 

 

728x90
반응형
728x90
반응형

연습문제

 

11 오늘이 몇일인지 출력하시오

SELECT TO_CHAR(SYSDATE,‘DD’) FROM DUAL

 

 

12 오늘이 무슨요일인지 출력하시오

SELECT TO_CHAR(SYSDATE,‘DAY’) FROM DUAL

 

 

13 위의 11번과 12을 합쳐서 출력하시오 결과 예제 -> 03목요일

SELECT TO_CHAR(SYSDATE,‘DD’) || TO_CHAR(SYSDATE,‘DAY’) FROM DUAL

 

 

14 요일마다 LOGIN 암호를 자동으로 바꿔주는 프로그램을 만든다고 가정하자 월요일엔 해당일자에 01을 붙여서 4자리를 만들고 화요일엔 11, 수요일엔 21, 목요일엔 31, 금요일엔 41, 토요일엔 51, 일요일엔 61, 붙여서 만들고자한다

SELECT TO_CHAR(SYSDATE,'DD') || DECODE(TO_CHAR(SYSDATE,'DAY'), 'MONDAY', 11, 'TUESDAY', 12,

                              'WEDNESDAY', 13, 'THURSDAY', 14, 'FRIDAY', 15, 'SATURDAY', 16, 'SUNDAY', 17)

FROM DUAL

 

 

15 LECTURE 테이블에서 LEC_TIME과 LEC_POINT를 비교해보자 비교한 결과를 이용해 LEC_TIME이 크면 ’실험과목‘ LEC_POINT가 크면 ’기타과목‘, 둘이 같으면 ’일반과목’으로 값을 돌려받고자 한다.

SELECT DECODE(SIGN(LEC_TIME-LEC_POINT), 0, ‘일반과목’, 1, ‘실험과목’, -1, ‘기타과목’) FROM LECTURE

 

 

16 강의 시간과 학점이 같거나 강의시간이 학점보다 작으면 ‘일반과목’을 돌려받고 강의시간이 학점보다 큰경우만‘실험과목’을 돌려받고자 한다 두가지 방법 SIGN, LEAST를 사용하자

SELECT LEC_ID, DECODE(LEAST(LEC_TIME,LEC_POINT), LEC_TIME, '일반과목', '실험과목') FROM LECTURE

 

SELECT LEC_ID, DECODE(SIGN(LEC_TIME-LEC_POINT), 0, '일반과목', -1, '일반과목', 1, '실험과목') FROM LECTURE

 

 

17 LECTURE 테이블의 자료중 LEC_TIME과 LEC_POINT가 같다라는 조건을 만족하고 LEC_TIME=3 인 자료는 옆에 ‘중요과목’이라는 비고를 붙이고자한다

SELECT LEC_ID, DECODE(LEC_TIME, LEC_POINT, DECODE(LEC_TIME,3, '중요과목')) FROM LECTURE

 

 

18. TEMP의 행을 3개씩 묶어 하나의 번호를 부여하고, 해당 ROWNUM을 3으로 나눈 나머지가 1이면 사번 아니면 NULL, 또 한 번은 1이면 성명 아니면 NULL 이렇게 순차적으로 사번, 성명을 출력하고 이어서 나머지가 2, 0인 경우도 같은 조건으로 출력하여라

SELECT CEIL(ROWNUM/3) C0

,DECODE(MOD(ROWNUM,3), 1, EMP_ID, NULL) C1

,DECODE(MOD(ROWNUM,3), 1, EMP_ID, NULL) C2

,DECODE(MOD(ROWNUM,3), 2, EMP_ID, NULL) C3

,DECODE(MOD(ROWNUM,3), 2, EMP_ID, NULL) C4

,DECODE(MOD(ROWNUM,3), 0, EMP_ID, NULL) C5

,DECODE(MOD(ROWNUM,3), 0, EMP_ID, NULL) C6

FROM TEMP

 

 

19 위 예문의 결과를 이용하여 한 행에 사번 성명을 3명씩 보여주는 SQL을 작성하자 (열을 행으로 바꾸는 문제)

SELECT CEIL(ROWNUM/3) C0

, MAX(DECODE(MOD(ROWNUM,3), 1, EMP_ID, NULL)) C1

, MAX(DECODE(MOD(ROWNUM,3), 1, EMP_NAME, NULL)) C2

, MAX(DECODE(MOD(ROWNUM,3), 2, EMP_ID, NULL)) C3

, MAX(DECODE(MOD(ROWNUM,3), 2, EMP_NAME, NULL)) C4

, MAX(DECODE(MOD(ROWNUM,3), 0, EMP_ID, NULL)) C5

, MAX(DECODE(MOD(ROWNUM,3), 0, EMP_NAME, NULL)) C6

FROM TEMP

GROUP BY CEIL(ROWNUM/3)

 

 

20 사원 테이블에서 사원을 골라 해당 사원의 연봉이 동일한 직급을 가진 사원의 평균 연봉보다 많은 사원을 출력하라

SELECT A.SALARY, A.EMP_ID, A.EMP_NAME ,B.AVG_SAL

FROM TEMP A ,(SELECT LEV, AVG(SALARY) AVG_SAL

                                  FROM TEMP

                                  GROUP BY LEV ) B

WHERE A.LEV = B.LEV

AND A.SALARY > B.AVG_SAL

 

 

 

728x90
반응형
728x90
반응형

1. TEMP와 TCOM에 존재하는 사번의 교집합을 구한후, TEMP에서 TCOM에 존재하는 사번을 제외시킨 차집합을 구하고, 두결과의 합집합을 구해보자

(SELECT EMP_ID FROM TEMP INTERSECT SELECT EMP_ID FROM TCOM) UNION

(SELECT EMP_ID FROM TEMP MINUS SELECT EMP_ID FROM TCOM);

 

 

2. TEMP 테이블과 TCOM의 행의 개수를 카테시안 조인으로 구하여라

SELECT COUNT(*) FROM TEMP, TCOM

 

 

3. TEMP에서 사번, 성명, 부서코드, 부서명을 출력하시오

SELECT A.EMP_ID, A.EMP_NAME, A.DEPT_CODE, B.DEPT_NAME

FROM TEMP A, TDEPT B

WHERE B.DEPT_CODE = A.DEPT_CODE;

 

 

4. TEMP에 존재하는 직원들 중 과장직급을 가질만한 나이에 포함되는 사람이 누구인지 출력하시오

( EMP_LEVE, TEMP )

SELECT B.EMP_ID,B.BIRTH_DATE FROM EMP_LEVEL A, TEMP B

WHERE B.BIRTH_DATE BETWEEN ADD_MONTHS(SYSDATE,-1* TO_AGE*12)

                                                           AND ADD_MONTHS(SYSDATE,-1* FROM_AGE*12)

                                                           AND B.LEV='대리';

 

 

5. TEMP와 EMP_LEVE를 이용해 과장 직급의 연봉 상한/하한 범위내에 있는 직원 사번 성명 직급 SALARY를 출력

SELECT A.EMP_ID, A.EMP_NAME, A.LEV, A.SALARY

FROM TEMP A, EMP_LEVEL B

WHERE A.SALARY BETWEEN FROM_SAL AND TO_SAL

AND B.LEV='과장';

 

 

6. 사번, 이름 SALARY, 연봉 상한 금액을 보고자 한다. TEMP와 EMP_LEVEL을 조인하여 결과를 보여주되 연봉의 상하한이 등록되어있지않은 수습사원은 사번, 이름 SALARY까지만 출력하는 QUERY를 구성해보자

SELECT A.EMP_ID, A.EMP_NAME, A.SALARY, B.TO_SAL

FROM TEMP A, EMP_LEVEL B

WHERE B.LEV(+)=A.LEV;

 

 

7. TEMP의 자료를 이용해 NON-EQUI JOIN이면서 SELF JOIN이고 QUTER조인인 QUREY를 하나 만들어 보자

사번, 성명, 생일, 자신보다 생일이 빠른 사람의 수를 읽어와 자신보다 생일이 빠른 사람의 수로 정렬하여 출력

SELECT A.EMP_ID, A.EMP_NAME,A.BIRTH_DATE, COUNT(B.BIRTH_DATE)

FROM TEMP A, TEMP B

WHERE B.BIRTH_DATE(+)<A.BIRTH_DATE

GROUP BY A.EMP_ID, A.EMP_NAME, A.BIRTH_DATE

ORDER BY COUNT(B.BIRTH_DATE)

 

 

8. TEMP에 속한 수습사원만 순번을 붙여 출력하시오. 단 5번까지만 출력하시오

SELECT ROWNUM, EMP_ID, EMP_NAME

FROM TEMP

WHERE EMP_ID > 0 AND LEV=’수습‘ AND ROWNUM <= 5

 

 

9. TEMP 테이블의 자료를 이용하여 SELECT 결과를 3개행씩 묶어 하나의 번호를 부여하는 SQL을 만들어보자

SELECT ROWNUM, CEIL(ROWNUM/3), EMP_ID, EMP_NAME FROM TEMP WHERE EMP_ID>0

 

10. 강의 ID와 주당 강의 시간과 학점이 같으면 일반으로 표시하고 아니면 특별로 출력하시오

SELECT LEC_ID, DECODE(LEC_TIME, LEC_POINT,’일반‘,’특별’) FROM LECTURE

 

 

728x90
반응형
728x90
반응형

 

DECODE의 기본적인 기능은 프로그래밍 언어의 IF 문을 SQL 문장으로 사용하기 위하여 만들어진 오라클이다

FROM 절만 빼고 어디서나 사용할 수 있고 IF문의 전형적인 형태를 분류하여 DECODE문으로 나누어 사용하자

 

1.DECODE의 사용(등순 등호 비교)

IF A = B THEN

RETURN ‘T’;

END IF;

SELECT DECODE(A,B,‘T’,NULL) AS COL1

FROM TABLE이름

 

2 DECODE의 사용(등호비교 + ELSE)

IF A=B THEN

RETURN ‘T

ELSE

RETURN ‘F

END IF

SELECT DECODE(A,B,‘T’,‘F’) AS COL1

FROM TABLE이름

 

3 DECODE의 사용(ELSEIF 분기되는 등호비교)

 

IF A=B THEN

RETURN 1

ELSEIF A=C THEN

RETURN 2

ELSE

RETURN 3

END IF

SELECT DECODE(A, B, 1, C, 2, 3)

FROM TABLE이름

4 DECODE의 사용 (부등호 비교)

 

SIGN, LEAST, GREATEST를 사용하여 등호비교를 한다

SIGN 은 주어진 값이 음수인지, 양수인지 아니면 0인지를 나타낸다

LEAST 주어진 값들 중 최소값을 돌려준다

GREATEST는 주어진 값들중 최대값을 돌려준다

 

IF A>B = 0 THEN

RETURN ‘일반

ELSEIF 1 THEN

RETURN ’실험

END IF

SELECT DECODE(

          SIGN(A,B), 0, ‘일반‘,1, ’실험‘,-1, ’기타과목‘)AS TY

5 DECODE의 사용(OR 또는 IN 비교)

IF A IN (B, C, D) THEN

RETURN ‘T’

ELSE

RETURN ‘F’

END IF

 

IF A=B OR A=C THEN

RETURN ‘T’

ELSE

RETURN ‘F’

END IF

 

첫 번째 IF

DECODE(A, B,‘T’, C, ‘T’, D,‘T’,‘F’)

 

 

 

두 번째 IF

 

DECODE(A, B, ‘T’, C, ‘T’, ‘F’)

 

6 DECODE의 사용(AND 비교) - AND비교는 리턴 부분에 다시 IF(DECODE)이 사용된다는 것이 핵심

 

IF A=B AND A=C THEN

RETURN ‘T’

ELSE

RETURN ‘F’

END

 

DECODE(A, B, DECODE(A, C, ‘T’, ‘F’), ‘F’)

 

 

728x90
반응형

+ Recent posts

Powered by Tistory, Designed by wallel