#include <stdio.h>

int main()
{
/* double baseFee=660, extraFee=88.5, taX=0.09, usAge=0, sumFee=0, totalFee=0;
printf("전기 사용량을 입력하세요(kw) :");
scanf("%lf",&usAge);
sumFee=baseFee+(usAge*extraFee);
totalFee=sumFee*(1+taX);
printf("전기 사용요금은 %f원 입니다.",totalFee);

char backNum;
double hitRate;
int aGe;

printf("등번호를 입력하세요:");
scanf("%c",&backNum);

printf("타율을 입력하세요:");
scanf("%lf",&hitRate);

printf("나이를 입력하세요:");
scanf("%d",&aGe);

printf("%c번 선수의 타율은 %lf이고 나이는 %d살입니다.",backNum,hitRate,aGe);

char backNum, playerName[6], playerPostion[6];
double hitRate;
int aGe;

printf("등번호를 입력하세요:");
scanf("%c",&backNum);

printf("이름을 입력하세요:");
scanf(" %s",playerName); //배열에는 &를 사용치 않음,배열이므로 %s

printf("포지션을 입력하세요:");
scanf(" %s",playerPostion); //배열

printf("타율을 입력하세요:");
scanf("%lf",&hitRate);

printf("나이를 입력하세요:");
scanf("%d",&aGe);

printf("%c번-%s-%s, 타율은 %lf이고 나이는 %d살입니다.",backNum,playerName,playerPostion,hitRate,aGe);

*/

int hisTory,liteRate,aRt,sUm;
double aVg;
printf("역사, 문학, 예능 점수를 입력하세요 :");
scanf("%d%d%d",&hisTory,&liteRate,&aRt);
sUm=hisTory+liteRate+aRt;
aVg=sUm/3;

printf("총점은 %d이고 평균은 %lf입니다",sUm,aVg);
/*

int timeConstant, timeHour,timeMin,timeSec, temp;
timeConstant=32767;
timeHour=timeConstant/60/60;
timeMin=(timeConstant-timeHour*60*60)/60;
// timeSec=(timeConstant-timeHour*60*60-timeMin*60);
timeSec=timeConstant%60;
printf("%d초는 %d시간, %d분, %d초입니다.",timeConstant, timeHour,timeMin,timeSec);

double preciseDay=365.2422, temp;
long calDay,calHour,calMin,calSec;

//calDay=preciseDay;
//calHour=(preciseDay-calDay)*24;
//calMin=(preciseDay*24-calDay*24-calHour)*60;
//calSec=(preciseDay*24*60-calDay*24*60-calHour*60-calMin)*60;
//위에처럼 풀면 소수점 아래 7번째에서 반올림되는 부분때문에 오류가 남

calDay=preciseDay;
temp=preciseDay*24-calDay*24;

//printf("%f",temp);
calHour=temp;
temp=temp*60-calHour*60;

//printf("%f",temp);
calMin=temp;
temp=temp*60-calMin*60;

//printf("%f",temp);
calSec=temp;

printf("1년은 %d일, %d시간, %d분, %d초입니다.",calDay,calHour,calMin,calSec);
*/
return 0;
}

int a=9;
printf("내가 가장 좋아하는 숫자는 : %d",a);

printf("월드컵은\n2002년에\n개최되었습니다.");

int height, weight, cha;
printf("키:");
scanf("%d",&height);
printf("몸무게:");
scanf("%d",&weight);
cha=height-weight;
printf("키에서 몸무게를 뺀 값은 %d입니다.",cha);

printf("두 정수값을 입력하세요 :");
scanf("%d%d",&a, &b);
hap=a+b;
printf("두 정수의 합은 %d입니다.",hap);

http://blog.naver.com/anzu_k/60024762492

1000 8 8
1001 9 9
1010 a 10
1011 b 11
1100 c 12
1101 d 13
1110 e 14
1111 f 15


1010 a 10
1011 b 11 1
1110 e 14 4
1111 f 15 5

1000 8 8
1001 9 9 1
1100 c 12 4
1101 d 13 5

맥스터 하드가 또다시 뻗었다.
겨우 읽어들인상태에서 백업을 해놓고
하드디스크 테스트..

교육일정

오리엔테이션 6.19-6.23
컴퓨터학개론 6.26-6.29
C/C++ 프로그래밍 6.30-8.22
C 프로젝트 8.23-8.30
HTML & javascript 8.31-9.8

Basic C# 9.11-9.29
Advanced C# 10.9-10.23
C# Windows 10.24-11.7
채팅 프로젝트 11.8-11.20
XML.NET 11.21-12.5
오라클 12.6-1.4
ADO.NET 1.5-1.11
ASP.NET 1.12-2.15
XML 웹서비스 2.21-3.5

.NET 프로젝트 3.6-4.12

select * from emp group by job having (select avg(sal) from emp group by job);

select job, avg(sal) as asal from emp e, (select job, avg(sal) as asal from emp group by job) a where e.asal
select e.job, e.asal from (select job, avg(sal) as asal from emp group by job) e, (select min(avg(sal)) a_sal from emp group by job) a where e.asal=a.a_sal;

select job, avg(sal) from emp group by job having avg(sal)=(select min(avg(sal)) from emp group by job);
//직업별 평균급여중 최소값

select ename, job, dname, loc from (select ename, job, deptno, empno from emp where job='MANAGER') e join dept d on e.deptno=d.deptno;
//직업이 MANAGER인 사람의 이름 직업 부서명 지역명

select ename, e.job, sal from (select job, min(sal) msal from emp group by job) m, emp e where e.job=m.job and sal=msal;

select ename, e.job, sal from (select job, min(sal) msal from emp group by job) m join emp e on e.job=m.job and sal=msal;
//업무별 최소급여를 받는 사람

select ename, sal from emp e where sal>(select avg(sal) from emp where e.deptno=deptno)
//효율이 떨어짐.. 10000x100

select ename, hiredate from emp where deptno=(select deptno from emp where ename='BLAKE');

select ename, job, sal from emp where deptno=(select deptno from dept where loc='DALLAS');
//달라스에 근무하는 사람들

select ename, job, sal from emp e join dept d on e.deptno=d.deptno and loc='DALLAS';
//구지 조인할필요가 없음
//효율이 떨어짐

select ename, job, sal, mgr, empno from emp
where mgr in (select ename, job, sal, mgr, empno from emp
where mgr in (select empno from emp where ename='KING'));

select * from emp where (job, sal) IN(select job, sal from emp where ename='FORD') and ename !='FORD';
//포드와 월급과 직업이 같은 사람을 출력
//본인 제외
select ename, job, sal from emp where job=(select job from emp where ename='JONES') or sal>=(select sal from emp where ename='FORD') order by job, sal desc;
//존과 직업이 같거나 포드보다 월급이 많은사람

select ename, job, sal from emp where sal in (select sal from emp where ename in ('SCOTT','WARD')) and ename not in ('SCOTT','WARD');
//스캇과 워드와 같은 월급 받는 사람

select ename, job, deptno from emp where job in (select job from emp natural join dept where loc='CHICAGO');
//하나로 처리
//주의!!

select * from emp where mgr=(select mgr from emp where ename='BLAKE') and ename!='BLAKE';
//BLAKE와 같은 상사를 가진 사람

select * from emp e where 5>(select count(*) from emp where sal>e.sal) order by sal desc;
//급여가 많은 순서로 5명 출력


select rownum, empno, ename, sal from emp;
//rownum 숨겨진 컬럼명
//
select rownum, empno, ename, sal from emp order by sal;

select rownum, empno, ename, sal from emp where rownum<=5 order by sal desc;

//rownum은 PK를 따라다님

select * from(select rownum, empno, ename, sal from emp order by sal desc) where rownum<=5;
//따라서 월급에 따라 정렬한 뷰를 만들어서 rownum을 호출하면 정렬된 순서대로 가져올 수 있다.!!!!!!!



**PLSQL**


ed
RUN(/)

>ed a
select * from emp; //<--작정 a라는 SQL 문

>/ //이전에 실행했던 SQL문 실행
>@a //a라는 SQL문 실행

>ed b
select * from emp; //<--작성 b라는 SQL 문

>/ //이전에 실행했던 SQL문 실행
>@b //b라는 SQL문 실행

>ed //버퍼영역 재실행
select ename from emp; //버퍼영역수정
>/
>@b //b는 수정되지 않은 dept가 실행

>ed b //<--기본확장자 SQL
select ename, sal from emp; //b를 수정
>@b //b를 실행

>get a //a를 버퍼로 불러옴
>/ //버퍼에 있는 a내용 실행
//위의 두개는 @a

>select empno, ename, sal, sal*12 from emp;
>save c:\c //버퍼의 내용을 c:\c.sql로 저장

>ed w
set linesize 120
set pagesize 50
/
>@W /저장해서 쓰면 편함
//



>select empno, ename
2 from emp
3 where deptno=10;
>L(ist)
>3
>L

>C/10/20 //바꾸기 10을 20으로(deptno)
>L
>1
>L
>A ,sal,job //추가 ,sal,job을 추가
>L

>del 3 //3행을 없앰
>L
>3 where sal>2000
4 //3수정후 엔터치면 4로..
>L

>2
>A join dept //추가 join dept
>L

>2
>i //input
>3i on emp.deptno=dept.deptno //세번째줄로 추가됨(2다음)

select RPAD(RPAD(ename||' '||sal,15,' '),(sal/100)+15,'*') from emp;

select ename, job, dname, loc from emp e join dept d on e.deptno=d.deptno and ename like '%L%';

select ename, job, dname, loc from emp e natural join dept d where ename like '%L%';

select e.ename, sal, s.grade from emp e join salgrade s on sal between losal and hisal;



s1 (union all) s2 = a+b+b+c //중복해서 표시
s1 union s2 = a+b+c
s1 minus s2 = a
s1 intersect s2 = b

select ename, sal job from emp where ename like '%S%' //A
select ename sal, null(개수맞추기) from emp where ename like '%S%' //B

A minus B

20번 부서 이름 급여 부서 업무
2000이상 이름 급여 부서 업무


select e.empno, e.ename, e.mgr, m.ename from emp e join emp m on e.mgr=m.empno;

select e.empno, e.ename, e.mgr, m.ename from emp e left outer join emp m on e.mgr=m.empno;

select e.empno, e.ename, e.mgr, m.ename from emp e right outer join emp m on e.mgr=m.empno;

select e.empno, e.ename, e.mgr, m.ename from emp e full outer join emp m on e.mgr=m.empno;

select e.empno, e.ename, d.dname, d.loc, sal, grade from emp e join dept d on e.deptno=d.deptno join salgrade on sal between losal and hisal;
//세개 테이블 조인!!!!

select e.empno, e.ename, e.mgr, m.ename, d.dname from emp e full outer join emp m on e.mgr=m.empno join dept d on e.deptno=d.deptno;

select m.empno, m.ename, e.empno, e.ename from emp m join emp e on m.empno=e.mgr join dept d on d.deptno;
//
select e.empno, e.ename, m.empno, m.ename, d.dname from emp e full outer join emp m on e.mgr=m.empno join dept d on m.deptno=d.deptno;
//조건기술이 중요!!!!!
emp e right outer join emp m on e.mgr=m.empno left outer join dept d on e.deptno=d.deptno;

from emp e join dept d on e. deptno= d.deptno right outer join emp m on e. mgr=m.empno;
//조인 순서..가 다름!!!!!



select empno, ename, sal, job from emp where sal>3000;
select sal from emp where ename='SCOTT'; //3000
//합치면
select empno, ename, sal, job from emp where sal>(select sal from emp where ename='SCOTT');
//서브쿼리

select a.empno, a.ename, a.sal, a.job, b.ename, b.sal from emp a join emp b on a.sal > b.sal and lower(b.ename)=lower('scott');
//급여가 스캇의 급여보다 많이 받는 사번, 이름, 급여, 업무

select a.empno, a.ename, a.sal, a.job from emp a join emp b on a.job = b.job and lower(b.ename)=lower('scott');
select empno, ename, sal, job from emp where job=(select job from emp where ename='SCOTT');
//스캇의 업무와 같은 사람표시

select ename, job, sal from emp where job=(select job from emp where ename='SMITH') or sal>(select sal from emp where empno=7499);

select a.empno, a.ename, a.sal, a.job from emp a join emp b on (a.job = b.job and lower(b.ename)=lower('SMITH')) or (a.sal>b.sal and b.empno=7499);
//스미스와 업무가 같거나 7499의 급여보다 많이 받는 사람





select count(*), count(comm), avg(comm), count(distinct deptno) from emp;


select count(*), avg(sal), min(sal), max(sal), sum(sal) from emp group by deptno order by sum(sal) desc;

select deptno, avg(sal), max(sal), min(sal) from emp where sal>=2900 group by deptno;


select empno, ename, sal, job, deptno from emp where deptno=(select distinct deptno from emp where job='CLERK');
//두줄이상이 리턴되어 에러
select empno, ename, sal, job, deptno from emp where deptno in (select distinct deptno from emp where job='CLERK');
//IN을 사용해서 해결

단일행 연산자 비교시 >,=,<
복수행 연산자 비교시 in, any, all, exist


select empno, ename, sal, dname from emp natural join dept where sal>(select min(sal) from emp where deptno=10);
//10번 부서의 최소급여보다 많이 받는 사번 이름 급여 부서

//any (or)
//all (and) 연산자포함
//효율적이지 못함

select empno, ename, sal, dname from emp natural join dept where sal//10번 부서의 최대급여보다 적게 받는 사번 이름 급여 부서

select empno, ename, sal, dname from emp natural join dept where sal//10번 부서의 최소급여보다 적게 받는 사번 이름 급여 부서

select empno, ename, sal, dname from emp natural join dept where sal>any(select sal from emp where deptno=10);
//보다 효율적!!!!

select ename, job from emp where empno=any(select mgr from emp);
//관리자만
select ename, job from emp where empno=any(select distinct mgr from emp);
select ename, job, mgr from emp where empno not in(select distinct NVL(mgr,empno) from emp);
//말단사원만
//mgr중 NULL이 들어있으므로 not in 연산시 true와 NULL의 AND연산시 NULL이 되므로 값이 반환되지 않는다.
//표 참조
//NVL함수를 사용해서 실수값을 할당해야만 한다.!!!!
//
select ename, job, mgr from emp where empno e exists (select mgr from emp where e. empno=mgr);
//비효율적임

SELECT ename, sal, job, deptno from emp where job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH');
//SMITH 와 급여가 같으면서 부서가 동일 사람

SELECT ename, sal, job, deptno from emp where job||deptno=(select job||deptno from emp where ename='SMITH');
//꽁수
select ename, job, deptno from emp where (job, deptno) in (select job, deptno from emp where ename='SMITH');

select empno, ename, deptno, sal, comm from emp where (sal,NVL(comm,-1)) in (select sal, NVL(comm,-1) from emp where deptno=30);
//()로 묶는것이 포인트.. N:N비교


01
select empno, ename, job, hiredate, sal from emp where job=(select job from emp where empno=7521) and sal>(select sal from emp where empno=7934);
02
select empno, ename, job, sal, deptno from emp where sal<(select avg(sal) from emp);
03
select deptno, min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20);
04
select e.job, e.asal from (select job, avg(sal) as asal from emp group by job) e, (select min(avg(sal)) a_sal from emp group by job) a where e.asal=a.a_sal;

select job, avg(sal) from emp group by job having avg(sal)=(select min(avg(sal)) from emp group by job);
05
select empno, ename, job, hiredate, sal, deptno from emp where sal in (select min(sal) from emp group by job);
06
select empno, ename, job, hiredate, sal, deptno from emp where sal > any (select sal from emp where deptno=30) and deptno != 30;
//***
07
select empno, ename, job, hiredate, sal deptno from emp where sal>all(select sal from emp where deptno=30) and deptno!=30;
//***
08
select empno, ename, job, hiredate, sal, deptno from emp where empno in (select NVL(mgr,0) from emp);
select empno, ename, job, hiredate, sal, deptno from emp e where exists (select * from emp where e.empno=mgr);
//****

09
select ename, deptno, sal, comm from emp where (sal,comm) in (select sal, comm from emp where deptno=30);
//***
10
select empno, ename, job, sal, deptno from emp where sal in (select min(sal) from emp group by job) order by job;
11
select ename, deptno, sal, comm from emp where (sal, NVL(comm,0)) in (select sal, NVL(comm,0) from emp where deptno=30);
12
select empno, ename, job, sal, deptno from emp where (job, sal) in (select job, min(sal) from emp group by job ) order by job;
13
select e.ename, e.job, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno and e.job='MANAGER';
14
select ename, job, dname, loc from (select deptno, ename, job from emp where job='MANAGER') e , dept d where e.deptno=d.deptno;

select ename, job, dname, loc from (select ename, job, deptno from emp where job='MANAGER') e join dept d on e.deptno=d.deptno;

01
select ename, hiredate from emp where deptno=(select deptno from emp where ename='BLAKE');
02
select empno, ename from emp where sal>(select avg(sal) from emp) order by sal desc;
03
select empno, ename, sal, deptno from emp where deptno in (select deptno from emp where ename like '%T%');
04
select ename, job, sal from emp where deptno=(select deptno from dept where lower(loc)=lower('dallas'));
05
select ename, sal, mgr from emp where mgr=(select empno from emp where ename='KING');
06
select ename, job from emp where deptno=(select deptno from dept where dname='SALES');
07
select ename, deptno, sal from emp where sal>(select min(sal) from emp where deptno=30);
08
select * from emp where deptno=10 and job in (select job from emp where deptno=30);
09
select * from emp where (job, sal) in (select job, sal from emp where ename='FORD');
10
select * from emp where job=(select job from emp where ename='JONES') or sal>=(select sal from emp where ename='FORD') order by job, sal desc;
11
select ename, job, sal from emp where sal in (select sal from emp where ename in ('SCOTT','WARD'));
12
select ename, job from emp where job in (select job from emp where deptno=(select deptno from dept where loc='CHICAGO'));
13. EMP 테이블에서 부서별로 월급이 평균 월급보다 높은 사원을 부서번, 이름, 급여를 출력하는 SELECT 문을 작성하시오.
select e.deptno, e.ename, e.sal, e.job, a.a_sal "jobsalavg" from emp e, (select deptno, avg(sal) as a_sal from emp group by deptno) a where a.deptno=e.deptno and e.sal>a.a_sal;

14. EMP 테이블에서 업무별로 월급이 평균월급보다 낮은 사원을 부서번호, 이름, 급여를 출력하는 SELECT 문을 작성하시오.
select e.deptno, e.ename, e.sal, e.job, a.a_sal "jobsalavg" from emp e, (select job, avg(sal) as a_sal from emp group by job) a where a.job=e.job and e.sal>a.a_sal;
//핵심은 알리아스!!!!
15
select job, ename, empno, deptno from emp where empno in (select distinct NVL(mgr,0) from emp);

16
select empno, ename, job, deptno from emp where empno not in (select distinct NVL(mgr,0) from emp);

문제 14 해설
select
job,
sum(decode(deptno,10,sal,20,0,30,0)) "DEPT 10",
sum(decode(deptno,10,0,20,sal,30,0)) "DEPT 20",
sum(decode(deptno,10,0,20,0,30,sal)) "DEPT 30",
sum(sal) "TOTAL"
from emp group by job;

SELECT
select count(*), count(decode(to_char(hiredate,'yy'),'80',job)) "1980", count(decode(to_char(hiredate,'yy'),'81',job)) "1981", count(decode(to_char(hiredate,'yy'),'82',job)) "1982", count(decode(to_char(hiredate,'yy'),'87',job)) "1987" from emp;


equi-join 1:1 대응
outer-join 조인되지 않은 데이터도 내보냄
left-outer-join 좌측을 다 내보냄
right-outer-join 우측을 다 내보냄
non-equi-join 조인범위에 맞춰 데이터를 내보냄
//78 = 70-80
self-join


select * from emp a, dept b;
56행
select * from emp a, dept b where a.deptno=b.deptno;
14행
select ename, dname, emp.deptno from emp, dept where emp.deptno=dept.deptno;
//조인
select ename, dname, e.deptno from emp e, dept d where e.deptno=d.deptno;
//알리아스를 이용(떠올리기 쉽게)
select empno, ename, job, sal, dname, loc from emp e, dept d where e.deptno=d.deptno;
//사번 이름 업무 급여 부서명 위치

select empno, ename, job, sal, dname, loc from emp e, dept d where e.deptno=d.deptno;
//사번 이름 업무 급여 부서명 위치

select empno, ename, job, sal, d.deptno, dname, loc from emp e, dept d where e.deptno(+)=d.deptno;
//(+)의 반대쪽 테이블이 다나옴 /left-outer-join
select empno, ename, job, sal, d.deptno, dname, loc from emp e, dept d where e.deptno=d.deptno(+);
//(+)의 반대쪽 테이블이 다나옴/right-outer-join

select ename, sal, grade from emp e, salgrade s where sal between losal and hisal;
//non-equi-join

select e.job||' '||e.ename "시다바리", m.job||' '||m.ename "왕초", e.sal, m.sal, m.sal-e.sal "급여차" from emp e, emp m where e.mgr=m.empno(+);

select b.job||' '||b.ename, b.sal from emp a, emp b where b.sal

01
select empno, ename, job, deptno, dname, loc from emp natural join dept;
02
select e.empno, e.ename, e.job, e.deptno, d.deptno, d.dname, d.loc from emp e join dept d on e.deptno=d.deptno;
03
select empno, ename, sal, dname, loc from emp join dept on emp.deptno=dept.deptno and lower(job)=lower('SALESMAN');
04
05
select empno, ename, job, sal, grade, losal, hisal from emp e join salgrade s on sal between losal and hisal;
06
select empno, ename, job, e.deptno, d.deptno, dname, loc from emp e right outer join dept d on e.deptno= d.deptno;
07
select worker.ename||'의 관리자는 '||manager.ename from emp worker join emp manager on worker.mgr=manager.empno;


연습문제
1.
select ename, deptno, dname from emp natual join dept;
2
select ename, job, sal, dname from emp natural join dept where upper(loc)=upper('new york');
select ename, job, sal, dname from emp e join dept d on e.deptno=d.deptno and upper(loc)=upper('new york');
3
select ename, comm, dname, loc from emp e join dept d on e.deptno=d.deptno and comm is not NULL;
4
select ename, job, dname, loc from emp natural join dept where ename like '%L%';
select ename, job, dname, loc from emp e join dept d on e.deptno=d.deptno and ename like '%L%';
5
select e.ename, e.empno, m.ename, m. empno from emp e left outer join emp m on e.mgr=m.empno;
6
select e.ename, e.hiredate, m.ename, m.hiredate from emp e join emp m on e.mgr=m.empno and e.hiredate < m.hiredate;
7
select RPAD(RPAD(ename,10,' '),10+sal/100,'*') "Employee and their salary" from emp;