간단한 오라클 SQL 실습입니다. 한번 풀어 보세요
오라클자바커뮤니티에서
설립한 개발자실무교육6년차 오엔제이프로그래밍 실무교육센터
(신입사원채용무료교육, 오라클, SQL, 튜닝, 자바, 스프링, Ajax, jQuery, 안드로이드, 아이폰, 닷넷, C#,
ASP.Net) www.onjprogramming.co.kr
1. A로 시작하는 사원이름과 급여 출력하시오.
답) select ename, sal
from emp
where ename like 'A%';
2. 95년 01월 01일부터 오늘까지 몇달 차이가 나는지 출력하시오.
(소수 첫 째자리에서 반올림)
답) select round(months_between(sysdate, '95/01/01'), 0) from dual;
3. 급여(sal)가 사원 전체의 평균보다 높은 사원의 이름, sal, 부서명을 출력하시오.
답) select ename, sal, dname
from emp e, dept d
where e.deptno = d.deptno
and sal > (select avg(sal) from emp);
4. emp table에서 급여가 가장 높은 사람의 사번, 성명 부서명, 급여를 출력하시오.
답) select empno, ename, dname, sal
from emp e, dept d
where e.deptno = d.deptno
and sal = (select max(sal) from emp);
5. emp table에서 column명 addr을 address 로 바꾸시오.
답) alter table emp
rename column addr to address;
6. 최진실과 부서과 같고 직책이 같은 사원의 sal는?
sql> select sal from emp
where deptno=(select deptno from sawon
where ename='최진실')
and job=(select job from sawon
where ename='최진실');
7. 각 부서별로 급여 서열 2위 까지 부서코드, 이름을 출력?
sql> select deptno, ename from emp a
where 2 > (select count(*) from emp
where sal > a.sal
and deptno=a.deptno)
order by deptno,sal desc
8. 아이디가 test 비빌번호가 security인 사용자를 등록하시오.
(사용자의 default tablespace는 users이며 temporary tablespace는 temp임)
9. test사용자에세 기본적으로 DataBase에서 작업(테이블 생성등,,,) 을 할수 있는
role을 부여하시오.(grant 이용)
10. test 사용자의 비밀번호를 tatata로 변경 하시오.
11. 다음의 SQL을 실행하고 오류가 발생하면 조치하시오.
CREATE TABLE 사원
( 사원번호번호 NUMBER(6) PRIMARY KEY,
사원명 VARCHAR2(10) NOT NULL,
전화번호 VARCHAR2(15) DEFAULT "Un-known",
부서코드 NUMBER(5) REFERENCE dept(deptno) )
12. emp 테이블에 이름이 같은 홍길동이라는 사람을 2명 삽입 하고 이름 출력시 한명만 나오게 하시오
SQL>insert into emp(empno, ename) values ('1111','홍길동);
SQL>insert into emp(empno, ename) values ('1112','홍길동);
SQL>select distinct ename from emp;
13.사원번호와 이름, 주소를 갖고있는 sawon 테이블에 부서테이블의 부서번호를
참조하게(외래키)하서 만드는 코드를 작성하라.
SQL> create table sawon(
2 id varchar2(4) not null primary key,
3 name varchar2(10) not null,
4 deptno varchar2(2) null,
5 constraint fk_buseo_deptno foreign key (deptno) references buseo(deptno)
6 );
14. 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 구하여 출력 하여라.
(평균은 소수 둘때 다리에서 반올림 하시오.)
SQL>SELECT deptno,COUNT(ename) "인원수" ,
ROUND(AVG(sal),1) "급여평균",
SUM(sal) "급여합계"
FROM emp
GROUP BY deptno
15. SMITH와 CLARK의 부서와 같은 부서에서 일하는 사람들 중 급여가 가장 많은 사람의 부서명과 이름, sal을 출력하시오..
답) select ename, sal, dname
from emp e, dept d
where e.deptno=d.deptno
and sal=(select max(sal)
from emp
where deptno in( select deptno
from emp
where ename in('CLARK', 'SMITH')));
16. 'SMITH'와 같은 부서에서 일하는 사원의 이름과 부서명 출력하시오
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno
and e.deptno=(select deptno from emp where ename='SMITH');
17. 부서별 급여 평균을 출력하시오
select dname, sum(sal)
from emp, dept
where emp.deptno=dept.deptno
group by dname;
18. 오늘은 몇요일인가?
select decode(to_char(sysdate,'d'),
'1', '일요일',
'2', '월요일',
'3', '화요일',
'4', '수요일',
'5', '목요일',
'6', '금요일',
'7', '토요일') "요일" from dual;
19. emp 테이블에서 급여에 1200을 곱하여 만원단위로 사원이름과 급여를 출력하시오
select ename "성명",
sal*1200/10000 "만원"
from emp;
20. sal가 사원전체의 평균보다 큰사람의 이름과 sal출력
select ename,sal,dname
from emp e,dept d
where e.deptno = d.deptno
and sal > (select avg(sal)
from emp)
21. 부서별로 급여의 총합을 나타내고자 한다. 10번 부서보다 급여의 평균이 많은 부서만 출력하시오.
select dname,sum(sal)
from emp e,dept d //from 뒤에 테이블이 2개 이상나오면 조인꼭 해줘야한다.
where e.deptno = d.deptno //테이블이 두개면 조인1개 세개면 조인 2개 조인을 안하면 //emp*dept의 컬럼수가 나온다.
group by dname //부서별로 라고 했기때문에 dname명으로 그룹지었다.
having avg(sal) > (select avg(sal)
from emp
where deptno = 10);
22. emp테이블에서 job별로 급여의 평균을 출력하고자 한다. 'SALESMAN '이라는 job보다 급여의 평균이
큰 job만 출력하시오
select job,avg(sal) from emp
group by job
having avg(sal) > (select avg(sal) from emp
where job = 'SALESMAN');
23. 부서중에 sal합계가 가장큰 부서의 부서명과 sal합계를 구하시오.
select dname,sum(sal)
from emp e,dept d
where e.deptno = d.deptno
group by dname
having sum(sal) = (select max(sum(sal)) from emp
group by deptno)
24. emp테이블에서 부서코드가 10또는 20또는 30또는 40인 데이터의 급여의 grade를 출력하시오(사원ID,사원명,sal,grade)
select empno,ename,sal,grade
from emp,salgrade
where deptno in (10,20,30,40)
and sal between losal and hisal
25. 오늘 날짜 출력하기(2003.07.09 20:10:10 오후 형식으로 출력하시오)
select to_char(sysdate,'yyyy.mm.dd hh:mi:ss PM') from dual;
26. 현재 접속되어 있는 사용자의 계정을 확인할려면?
SQL> show user
27. c:\oracle\oradata\wink에 test01.dbf라는 데이터 파일을 test라는 테이블스페이스를
만들때 작성하고자 한다. TableSapce를 creation하는 Oracle Script를 기술하시오.
(TableSpace의 사이즈는 10m)
SQL> create tablespace test datafile 'd:\oracle\oradata\wink\test01.dbf' size 10m;
28. 현재 Oracle 시스템의 role을 확인하기 위해 Oracle에서 제공되는 View는?
user_roles
29. emp table의 idx_emp_ename 이라는 인덱스를 삭제후 다시 만드시오.
(ename, sal로 index를 사용하며INDX라는 테이블스페이스에 index를 만들려고 한다.)
30. 현재 사용자 계정에 만들어진 테이블들과 이들 테이블이 어느 테이블스페이스에 작성되었는지를
확인하시오.
SQL>select TABLE_NAME, TABLESPACE_NAME from user_tables;
31. 사원(EMP) 테이블에 sex(성별) 컬럼을 추가하시오. 길이는 varchar2(1) 이고 null을 허용하며
값이 안들어 올때는 'M'을 넣으시오.
32. check 제약조건을 이용하여 성별(sex)컬럼에 'M',또는 'F'만 입력이가능하도록 하시오.
33. Oracle의 sequence만드는 스크립트를 작성하시오.
34. sequence를 이용하여 data를 insert하는 SQL을 작성하시오.
35. 다음 SQL의 오류를 수정하시오.
create table sawon(
id varchar2(4),
name varchar2(10) not null,
deptno varchar2(2) null,
constraints pk_sawon_id primary key(id),
constraints fk_sawon_deptno foreign key (deptno) reference dept(deptno)
)
36. emp table의 현재 제약조건을 확인 하는 SQL을 작성하시오.
select constraint_name, column_name
from user_cons_columns
where table_name = 'EMP';
37. EMP Table에서 이름이 SMITH인 데이터와 같은 부서에 이쓴 사람들을 삭제하시오.
9삭제후 rollback하시오.)
38.seq_emp_id라는 sequence를 삭제하시어.
39. 현재 사용자의 계정에서 작성된 sequence를 확인하시오.
select sequence_name,
min_value,
max_value,
increment_by,
last_number
from user_sequences;
40. emp Table에서 empno, ename만 볼수 있는 View를 만드시오.
(뷰의 이름은 v_emp_empno_ename 이다.)
41. v_emp_empno_ename 뷰를 삭제 하시오.
42. EMP Table에서 이미 생성된 인덱스, 관련 컬럼명, Unique여부등을 출력 하시오.
select ic.index_name, ic.column_name, ic.column_position, ix.uniqueness
from user_indexes ix, user_ind_columns ic
where ic.index_name = ix.index_name
and ix.table_name = 'EMP'
43. sys계정으로 접속후 scott계정의 select any table 권한을 뺐으시오.
revoke scott from select any table
44. emp table에서 급여가 SMITH보다 큰 사람들의 급여에 10을 곱하시오.
45.emp 테이블에서 사원번호,사원명,봉급을 출력하시오 (단, 중복된 값들은 제외)
select distinct ename , sal
from emp;
46. 36.321을 소숫점 2째자리에서 반올림하세요
select round(36.321,1)
from dual;
47. 현시점에서 6개월후의 마지막날을 출력하세요
select last_day(add_months(sysdate,6))
from dual;
48. emp 테이블에 같은 이름이 있는 사원의 사원번호와 이름을 출력하세요
select distint e1.empno , e1.ename
from emp e1, emp e2
where e1.ename = e1.ename
and e1.rowid<>e2.rowid;
49. emp table의 primary key는 empno 이다. 키가 중복되는 레코드를 삭제하시오.
DELETE FROM EMP A
WHERE ROWID > ( SELECT MIN(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
답) select ename, sal
from emp
where ename like 'A%';
2. 95년 01월 01일부터 오늘까지 몇달 차이가 나는지 출력하시오.
(소수 첫 째자리에서 반올림)
답) select round(months_between(sysdate, '95/01/01'), 0) from dual;
3. 급여(sal)가 사원 전체의 평균보다 높은 사원의 이름, sal, 부서명을 출력하시오.
답) select ename, sal, dname
from emp e, dept d
where e.deptno = d.deptno
and sal > (select avg(sal) from emp);
4. emp table에서 급여가 가장 높은 사람의 사번, 성명 부서명, 급여를 출력하시오.
답) select empno, ename, dname, sal
from emp e, dept d
where e.deptno = d.deptno
and sal = (select max(sal) from emp);
5. emp table에서 column명 addr을 address 로 바꾸시오.
답) alter table emp
rename column addr to address;
6. 최진실과 부서과 같고 직책이 같은 사원의 sal는?
sql> select sal from emp
where deptno=(select deptno from sawon
where ename='최진실')
and job=(select job from sawon
where ename='최진실');
7. 각 부서별로 급여 서열 2위 까지 부서코드, 이름을 출력?
sql> select deptno, ename from emp a
where 2 > (select count(*) from emp
where sal > a.sal
and deptno=a.deptno)
order by deptno,sal desc
8. 아이디가 test 비빌번호가 security인 사용자를 등록하시오.
(사용자의 default tablespace는 users이며 temporary tablespace는 temp임)
9. test사용자에세 기본적으로 DataBase에서 작업(테이블 생성등,,,) 을 할수 있는
role을 부여하시오.(grant 이용)
10. test 사용자의 비밀번호를 tatata로 변경 하시오.
11. 다음의 SQL을 실행하고 오류가 발생하면 조치하시오.
CREATE TABLE 사원
( 사원번호번호 NUMBER(6) PRIMARY KEY,
사원명 VARCHAR2(10) NOT NULL,
전화번호 VARCHAR2(15) DEFAULT "Un-known",
부서코드 NUMBER(5) REFERENCE dept(deptno) )
12. emp 테이블에 이름이 같은 홍길동이라는 사람을 2명 삽입 하고 이름 출력시 한명만 나오게 하시오
SQL>insert into emp(empno, ename) values ('1111','홍길동);
SQL>insert into emp(empno, ename) values ('1112','홍길동);
SQL>select distinct ename from emp;
13.사원번호와 이름, 주소를 갖고있는 sawon 테이블에 부서테이블의 부서번호를
참조하게(외래키)하서 만드는 코드를 작성하라.
SQL> create table sawon(
2 id varchar2(4) not null primary key,
3 name varchar2(10) not null,
4 deptno varchar2(2) null,
5 constraint fk_buseo_deptno foreign key (deptno) references buseo(deptno)
6 );
14. 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 구하여 출력 하여라.
(평균은 소수 둘때 다리에서 반올림 하시오.)
SQL>SELECT deptno,COUNT(ename) "인원수" ,
ROUND(AVG(sal),1) "급여평균",
SUM(sal) "급여합계"
FROM emp
GROUP BY deptno
15. SMITH와 CLARK의 부서와 같은 부서에서 일하는 사람들 중 급여가 가장 많은 사람의 부서명과 이름, sal을 출력하시오..
답) select ename, sal, dname
from emp e, dept d
where e.deptno=d.deptno
and sal=(select max(sal)
from emp
where deptno in( select deptno
from emp
where ename in('CLARK', 'SMITH')));
16. 'SMITH'와 같은 부서에서 일하는 사원의 이름과 부서명 출력하시오
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno
and e.deptno=(select deptno from emp where ename='SMITH');
17. 부서별 급여 평균을 출력하시오
select dname, sum(sal)
from emp, dept
where emp.deptno=dept.deptno
group by dname;
18. 오늘은 몇요일인가?
select decode(to_char(sysdate,'d'),
'1', '일요일',
'2', '월요일',
'3', '화요일',
'4', '수요일',
'5', '목요일',
'6', '금요일',
'7', '토요일') "요일" from dual;
19. emp 테이블에서 급여에 1200을 곱하여 만원단위로 사원이름과 급여를 출력하시오
select ename "성명",
sal*1200/10000 "만원"
from emp;
20. sal가 사원전체의 평균보다 큰사람의 이름과 sal출력
select ename,sal,dname
from emp e,dept d
where e.deptno = d.deptno
and sal > (select avg(sal)
from emp)
21. 부서별로 급여의 총합을 나타내고자 한다. 10번 부서보다 급여의 평균이 많은 부서만 출력하시오.
select dname,sum(sal)
from emp e,dept d //from 뒤에 테이블이 2개 이상나오면 조인꼭 해줘야한다.
where e.deptno = d.deptno //테이블이 두개면 조인1개 세개면 조인 2개 조인을 안하면 //emp*dept의 컬럼수가 나온다.
group by dname //부서별로 라고 했기때문에 dname명으로 그룹지었다.
having avg(sal) > (select avg(sal)
from emp
where deptno = 10);
22. emp테이블에서 job별로 급여의 평균을 출력하고자 한다. 'SALESMAN '이라는 job보다 급여의 평균이
큰 job만 출력하시오
select job,avg(sal) from emp
group by job
having avg(sal) > (select avg(sal) from emp
where job = 'SALESMAN');
23. 부서중에 sal합계가 가장큰 부서의 부서명과 sal합계를 구하시오.
select dname,sum(sal)
from emp e,dept d
where e.deptno = d.deptno
group by dname
having sum(sal) = (select max(sum(sal)) from emp
group by deptno)
24. emp테이블에서 부서코드가 10또는 20또는 30또는 40인 데이터의 급여의 grade를 출력하시오(사원ID,사원명,sal,grade)
select empno,ename,sal,grade
from emp,salgrade
where deptno in (10,20,30,40)
and sal between losal and hisal
25. 오늘 날짜 출력하기(2003.07.09 20:10:10 오후 형식으로 출력하시오)
select to_char(sysdate,'yyyy.mm.dd hh:mi:ss PM') from dual;
26. 현재 접속되어 있는 사용자의 계정을 확인할려면?
SQL> show user
27. c:\oracle\oradata\wink에 test01.dbf라는 데이터 파일을 test라는 테이블스페이스를
만들때 작성하고자 한다. TableSapce를 creation하는 Oracle Script를 기술하시오.
(TableSpace의 사이즈는 10m)
SQL> create tablespace test datafile 'd:\oracle\oradata\wink\test01.dbf' size 10m;
28. 현재 Oracle 시스템의 role을 확인하기 위해 Oracle에서 제공되는 View는?
user_roles
29. emp table의 idx_emp_ename 이라는 인덱스를 삭제후 다시 만드시오.
(ename, sal로 index를 사용하며INDX라는 테이블스페이스에 index를 만들려고 한다.)
30. 현재 사용자 계정에 만들어진 테이블들과 이들 테이블이 어느 테이블스페이스에 작성되었는지를
확인하시오.
SQL>select TABLE_NAME, TABLESPACE_NAME from user_tables;
31. 사원(EMP) 테이블에 sex(성별) 컬럼을 추가하시오. 길이는 varchar2(1) 이고 null을 허용하며
값이 안들어 올때는 'M'을 넣으시오.
32. check 제약조건을 이용하여 성별(sex)컬럼에 'M',또는 'F'만 입력이가능하도록 하시오.
33. Oracle의 sequence만드는 스크립트를 작성하시오.
34. sequence를 이용하여 data를 insert하는 SQL을 작성하시오.
35. 다음 SQL의 오류를 수정하시오.
create table sawon(
id varchar2(4),
name varchar2(10) not null,
deptno varchar2(2) null,
constraints pk_sawon_id primary key(id),
constraints fk_sawon_deptno foreign key (deptno) reference dept(deptno)
)
36. emp table의 현재 제약조건을 확인 하는 SQL을 작성하시오.
select constraint_name, column_name
from user_cons_columns
where table_name = 'EMP';
37. EMP Table에서 이름이 SMITH인 데이터와 같은 부서에 이쓴 사람들을 삭제하시오.
9삭제후 rollback하시오.)
38.seq_emp_id라는 sequence를 삭제하시어.
39. 현재 사용자의 계정에서 작성된 sequence를 확인하시오.
select sequence_name,
min_value,
max_value,
increment_by,
last_number
from user_sequences;
40. emp Table에서 empno, ename만 볼수 있는 View를 만드시오.
(뷰의 이름은 v_emp_empno_ename 이다.)
41. v_emp_empno_ename 뷰를 삭제 하시오.
42. EMP Table에서 이미 생성된 인덱스, 관련 컬럼명, Unique여부등을 출력 하시오.
select ic.index_name, ic.column_name, ic.column_position, ix.uniqueness
from user_indexes ix, user_ind_columns ic
where ic.index_name = ix.index_name
and ix.table_name = 'EMP'
43. sys계정으로 접속후 scott계정의 select any table 권한을 뺐으시오.
revoke scott from select any table
44. emp table에서 급여가 SMITH보다 큰 사람들의 급여에 10을 곱하시오.
45.emp 테이블에서 사원번호,사원명,봉급을 출력하시오 (단, 중복된 값들은 제외)
select distinct ename , sal
from emp;
46. 36.321을 소숫점 2째자리에서 반올림하세요
select round(36.321,1)
from dual;
47. 현시점에서 6개월후의 마지막날을 출력하세요
select last_day(add_months(sysdate,6))
from dual;
48. emp 테이블에 같은 이름이 있는 사원의 사원번호와 이름을 출력하세요
select distint e1.empno , e1.ename
from emp e1, emp e2
where e1.ename = e1.ename
and e1.rowid<>e2.rowid;
49. emp table의 primary key는 empno 이다. 키가 중복되는 레코드를 삭제하시오.
DELETE FROM EMP A
WHERE ROWID > ( SELECT MIN(ROWID) FROM EMP B
WHERE A.EMPNO = B.EMPNO );
댓글 없음:
댓글 쓰기