07_21 오라클 sql문

create table student(
no number(4),
name varchar2(10) not null,
gender char(4),
addr varchar2(15),
jumin char(14) not null,

);
alter table student add  jumin char(14) not null;
desc student;
--학번에 프라이머리키 추가
alter table student add constraint temp_no_pk primary key(no);
--주민 낫널 삭제
alter table student modify jumin null ;
--주소 디폴트 경남마산
alter table student modify addr DEFAULT '경남 마산';
--성별 체크 남성 여성
alter table student add CONSTRAINT temp_gende_ck CHECK (gender IN('남자','여자'));

create TABLE library(
rent_id number(10) primary key,
book_id number(10) not null,
hakbun constraint st_no
references student (no)
);
desc library;

select constaint_name, constraint_type FROM user_constraints where table_name= 'STUDENT';
select constaint_name, constraint_type FROM user_constraints where table_name= 'LIBRARY';

--mvl
--INSERT INTO student(no, name, gender, addr, jumin ) VALUES('1111', '김태희', '여성', '서울시 강남구', '801212-1234567' );
INSERT INTO student(no, name, jumin ) VALUES('2222', '송혜교', '881212-1234567');
INSERT INTO student(no, name, jumin ) VALUES('1111', '강동원', '881212-1234567');
INSERT INTO student(no, name, gender, jumin ) VALUES('3333', '하정우', '남자', '830303-1234567');
INSERT INTO student(no, name,  addr, jumin ) VALUES('4444', '김새론', '서울시 서초구', '990909-1234567');
INSERT INTO library ( rent_id, book_id, hakbun ) VALUES( 1, 'bk001', '1111' );
INSERT INTO library ( rent_id, book_id, hakbun ) VALUES( 2, 'bk002', '2222' );
INSERT INTO library ( rent_id, book_id, hakbun ) VALUES( 3, 'bk003', '3333' );
INSERT INTO library ( rent_id, book_id, hakbun ) VALUES( 4, 'bk004', '4444' );
INSERT INTO library ( rent_id, book_id, hakbun ) VALUES( 5, 'bk005', '5555' );

insert into emp(empno, ename, job) values(8000,'이지수','PROGRAMER');

SELECT * from emp order by deptno asc;
SELECT * from emp order by deptno desc;

SELECT * from emp order by nvl(comm,0) desc;
desc emp;
 -- 사원명, 급여, 월급(급여/12)를 출력하되 월급은 십단위에서 반올림하여 출력
select ename, sal, sal/12 as "월급" from emp;
 -- 사원명, 급여, 세금(급여의 3.3%)를 원단위 절삭하고 출력
select ename, sal, trunc(sal*0.033,-1) as tax from emp;
-- scott의 정보를 사원번호, 성명, 담당업무(소문자) 출력
select empno, ename, lower(job) job from emp where ename = upper('scott');
select empno, ename, lower(job) job from emp where lower(ename) = 'scott';
-- 사원번호, 사원명(첫글자만 대문자), 담당업무(첫글자만 대문자)로 출력
select empno, INITCAP (ename), INITCAP (job) from emp;
-- 이름의 첫글자가 ‘K’보다 크고 ‘Y’보다 작은 사원의 정보( 사원번호, 이름, 업무, 급여, 부서번호)를 출력하되 이름순으로 정렬
Select empno, ename, comm, job, deptno From emp Where substr(ename,1,1)>'K' And substr(ename,1,1)<'Y' order by ename;
-- 이름이 5글자 이상인 사원들을 출력
SELECT EMPNO,ENAME,JOB FROM EMP WHERE LENGTH(ENAME) >=5;
-- 이름을 15자로 맞추고 글자는 왼쪽에 오른쪽에는 ‘*’로 채운다
select RPAD(ename, 15, '*') from emp;
-- 월급은 10자로 맞추고 숫자는 오른쪽에 왼쪽엔 ‘-‘로 채운다
select lPAD(sal, 10, '-') from emp;

insert into emp(empno, ename, sal)
values(8100,'홍',10000);
commit;
select * from emp where ename = '홍';
select * from emp where trim(ename) = '홍';

-- 월급을 숫자에서 ‘영일이삼사오육칠팔구’ 글자로 대체
select ename, sal, translate(sal,'01234566789','영일이삼사오육칠팔구') sal_str from emp;
-- 월급의 숫자에서 0을 ‘$’로 바꾸어 출력
select ename, sal ,replace(sal, 0, '$')sal_str from emp;

select sysdate as today from dual;

-- 현재까지 근무일 수가 많은 사람 순으로 출력
select * from emp order by sysdate - hiredate desc;
-- 현재까지 근무일 수가 몇 주 명 일인가를 출력
SELECT ename,hiredate,sysdate,
       TRUNC((sysdate - hiredate) / 7, 0) Weeks,
       ROUND(MOD((sysdate - hiredate), 7), 0) DAYS
 FROM emp;
-- 10번 부서의 사원의 현재까지의 근무 월수를 계산
 select ename,months_between(sysdate,hiredate) "개월수", deptno
 from emp
 where deptno=10;
-- 현재 날짜에서 3개월 후의 날짜 구하기
   select  add_months( sysdate, 3 ) as mydate from dual;
-- 현재 날짜에서 돌아오는 ‘월’요일의 날짜 구하기
  select  NEXT_DAY( sysdate, 2 ) as mydate from dual;
-- 현재 날짜에서 해당 월의 마지막 날짜 구하기
  select  LAST_DAY( sysdate ) as mydate from dual;
-- 입사일자에서 입사년도를 출력
  select ename, to_char(hiredate,'yyyy') hireyear from emp;     
-- 입사일자를 ‘1999년 1월 1일’ 형식으로 출력
select ename, to_char(hiredate,'yyyy"년" mm"월" dd"일"') hireyear from emp;     
-- 급여 앞에 $를 삽입하고 3자리 마다 ,를 출력
select ename,sal,to_char(sal,'$999,999,999')as sal_str from emp;

desc temp;
select * FROM temp;
insert into temp(name,jumin) values('이지수','900328-1234567');

-- 부서번호가 10이면 영업부, 20이면 관리부, 30이면 IT부 그 외는 기술부로 출력
select ename, case substr(deptno,1,2) when '10' then '영업부'when '20' then'관리부' when'30' then'IT부' else '기술부' end as "부서"from emp;
-- 업무(job)이 analyst이면 급여 증가가 10%이고 clerk이면 15%, manager이면 20%인 경우 사원번호, 사원명, 업무, 급여, 급여증가를 출력     
select empno, ename,job,sal,decode (job,'ANALYST',TRUNC(sal*0.1, 0),'CLERK',sal*0.15,'MANAGER',sal*0.2)upmoney
from emp;
-- 주민번호에서 성별 구하기
select decode(substr(jumin,8,1),1,'남자',3,'남자','여자')gender from temp;

select case substr(jumin,8,1) when '1' then'남자'
when '3' then'남자' else '여자' END as gender FROM temp;

--1.현재 급여에 15% 증가된 급여를 사원번호, 이름, 업무, 급여, 증가된 급여(New_Salary), 증가액(Increase)를 출력
select empno,ename,job,sal, (sal*0.15)+sal new_salary ,sal*0.15 increase from emp;
--2.이름, 입사일, 입사일로부터 6개월 후에 돌아오는 월요일을 출력
select ename,HIREDATE ,  next_day(add_months( HIREDATE, 6 ),2)  from emp;
--3.이름, 입사일, 입사일로부터 현재까지의 월수, 급여, 입사일로부터 현재까지의 급여의 총계를 출력
select ename,hiredate,trunc(months_between(sysdate,hiredate)) "월수", trunc(sal,sal*months_between(sysdate,hiredate)) from emp;
--4.이름, 업무, 일사일, 입사한 요일을 출력
select ename,job,hiredate,case TO_char(hiredate,'d')when '1' then'일요일' when '2' then'월요일' when '3' then'화요일' when '4' then'수요일' when '5' then'목요일' when '6' then'금요일' when '7' then'토요일'end "요일" FROM emp;
--5.이름, 업무, 급여(SAL), 보너스(COMM), 급여+보너스를 출력
select ename,job,sal,nvl(comm,0),sal+nvl(comm,0) bonussal from emp;

 

select rownum, job from emp where rownum <=5;--번호매겨서
select all job from emp; --모두
select distinct job from emp; --하나씩

select job, avg(sal),max(sal),min(sal),count(sal),sum(sal) from emp group by job having sum(sal)>5000; -- where job = 'SALESMAN';
desc emp;
select *from emp;
 -- 부서별로 인원수, 평균, 급여, 최저급여, 최고급여, 급여의 합을 구하기
 select job ,count(*) people,avg(sal),min(sal),max(sal),sum(sal) from emp group by job ;
 -- 부서별로 인원수, 평균, 급여, 최저급여, 최고급여, 급여의 합을 구하기 ( 부서별 급여의 합이 높은 순으로
 select job ,count(*) people,avg(sal),min(sal),max(sal),sum(sal) from emp group by job order by sum(sal) desc;
 -- 부서별 업무별 그룹하여 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하기
 SELECT deptno,job,count(*) people, avg(sal),sum(sal) from emp group by deptno,job ;
 -- 급여가 최대 2900이상인 부서에 대해 부서번호, 평균 급여, 급여의 합을 출력
  select deptno , avg(sal) as 평균급여, sum(sal) as 급여합계 from emp group by deptno having max(sal) >= 2900;
-- 업무별 급여의 평균이 3000이상인 업무에 대해 업무명, 평균 급여, 급여의 합을 출력
select job,avg(sal),sum(sal)from emp group by job having avg(sal)>=3000;
--전체 월급이 5000를 초과하는 각 업무에 대해서 업무와 급여 합계를 출력 단, SALESMAN은 제외하고 급여 합계가 높은 순으로 정렬
select job, sum(sal) as 급여합계 from emp group by job having sum(sal) > 5000 and job != 'SALESMAN' order by sum(sal) desc;