DB 실습-1

테이블 만들기

• 부서(부서번호, 부서명, 부서위치)
  dept(dno, dname, dloc)

create table dept(
    dno number primary key,
    dname varchar2(20),
    dloc varchar2(20)
);

• 사원(사원번호, 사원명, 직책, 입사일, 급여, 부서번호, 관리자번호)
  emp(eno, ename, job, hiredate, salary, dno, mgr)

create table emp(
	eno number primary key, 
    ename varchar2(20), 
    job varchar2(20), 
    hiredate date, 
    salary number, 
    dno number references dept(dno), 
    mgr number
);

 

행 추가

insert into dept values(10, '기획팀', '종각');
insert into dept values(20, '영업팀', '종각');
insert into dept values(30, '개발1팀', '판교');
insert into dept values(40, '개발2팀', '가산');
insert into dept values(50, '개발3팀', '가산');

 

• 아래와 같은 구조를 가질 때

팀	사장	과장	대리	사원	사원
기획팀	변시우	이동준	박성빈	임유나	홍석영
영업팀		최모래	유요한	유현진	박규희
개발1팀		박준수	김예은	이재원	조영흔
개발2팀		강동균	장동건	공희상	최가은

insert into emp values(1000, '변시우', '사장', '2010/01/03', 1000, 10, null);
insert into emp values (1001, '이동준', '과장', '2012/08/27', 600, 10, 1000);
insert into emp values ( 1002 , '박성빈' , '대리' , '2014/07/28' , 400 , 10 , 1001  ) ;
insert into emp values(1003, '임유나', '사원', '2023/10/10', 200, 10, 1002);
insert into emp values(1004, '홍석영', '사원', '2018/02/17', 200, 10, 1002);

insert into emp values(1005, '최모래', '과장', '2013/01/02', 500, 20, 1000);
insert into emp values(1006, '유요한', '대리', '2014/01/02', 400, 20, 1005);
insert into emp values (1007,'유현진','사원','2020/09/12',300,20,1006);
insert into emp values (1008,'박규희','사원','2020/09/12',300,20,1006);

 

INSERT INTO emp values(1009, '박준수', '과장', '2012/12/03', 500, 30, 1000);
insert into emp values(1010, '김예은', '대리', '2020/02/12', 600, 30, 1009);
insert into emp values(1011, '이재원', '사원', '2024/06/11', 100, 30, 1010);
insert into emp values(1012, '조영흔', '사원', '2020/03/05', 400, 30, 1010);

 

insert into emp values(1013, '강동균', '과장', '2014/10/20', 600, 40, 1000);
INSERT INTO emp VALUES(1014, '장동건', '대리', '2015/01/05', 500, 40, 1013);
INSERT INTO EMP VALUES (1015, '공희상', '사원', '2024/04/14', 400, 40, 1014);
INSERT INTO EMP VALUES (1016, '최가은', '사원', '2010/01/05', 700, 40, 1014);

ed emp;

column eno format 9999;
column ename format a10;
column job format a10;
column hiredate format a15;
column salary format 999,999;
column dono format 9999;
column mgr format 9999;

@emp;

• 조회해보기

select eno, e.dno, dname, job, hiredate, salary 
from dept d, emp e 
where d.dno = e.dno 
order by hiredate, eno;

select d.dno, dname, job, hiredate, salary from dept d, emp e where e.dno = d.dno and job in ('사원', '대리') order by salary desc, hiredate;