[Oracle] SQL연습

기본 개념 ) 

DDL(Data Definition Language)  - 테이블 구조와 제약조건 생성, 삭제, 수정하는 명령문  - 인덱스를 생성하는 DDL 명령문     : 테이블 조회 시간을 단축하기 위해 인덱스를 사용  - 먼저 생성된 테이블들을 이용하여 새로운 테이블과 뷰를 생성  DML(Data Manipulation Language)  - 한 개의 테이블에 대해 데이터를 삽입, 수정, 삭제하고 행을 조회하는 명령문  - 조인, 서브 쿼리, 집합 연산자를 사용하여 두 개 이상의 테이블로부터 데이터 조회 시 사용  DCL(Data Control Language)  - 업무 단위인 트랜잭션의 완료와 취소를 위한 명령문  데이터 사전을 조회하는 명령문  : 생성된 테이블의 목록, 테이블 구조와 제약조건을 파악하기 위해 사용

 

alter나 create는 desc로 확인할 것 

1. 아래와 같은 테이블 student2 쿼리를 생성하는 문장

이름                                      널?      유형

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

 STU_NO                                   널 불허  / 고정 문자 9 / 기본키

 STU_NAME                                          가변문자 12   / 널 불허

 STU_DEPT                                           가변 문자 20

STU_GRADE                                         숫자 1  

 STU_GLASS                                         고정문자 1

 STU_GENDER                                        고정문자 1

 STU_HEIGHT                                        숫자 전체 5자라 소수점 2자리    / 널 허용

 STU_WEIGHT                                        숫자 전체 5자라 소수점 2자리

create table student2 (
STU_NO char(9),
STU_NAME varchar2(12) not null,
STU_DEPT varchar2(20),
STU_GRADE number(1),
STU_GLASS char(1),
STU_GENDER char(1),
STU_HEIGHT number(5,2) null,
STU_WEIGHT number(5,2),
 	constraint p_stu2_no primary key(STU_NO));

 

2. 위의 생성된 테이블에  컬럼 stu_address  / 고정 문자  / 크기 20 을 추가 하고 확인

alter table student2
	add (stu_address char(20));

 

3. 위의 생성 된 테이블에  컬럼 컬럼 stu_address  / 가변 문자  / 크기 50  으로 변경

alter table student2 
modify(stu_address varchar2(50));

 

4. 위의 생성 된 테이블에  STU_GLASS 컬럼명을  STU_CLASS 로 변경

alter table student2
rename column STU_GLASS to STU_CLASS ; 

 

5. 테이블의 이름을 student2에서  myStudent 로 변경

rename student2 to myStudent;

 

6. 위의 테이블에서 stu_address 컬럼을 삭제

alter table myStudent 
drop (stu_address);

 

7. 위의 테이블 mystudent 테이블을 삭제하고 테이블 목록을 확인

drop table myStudent;

 

8. 사원들의  사번, 이름, 직무를 검색 ( 계정 전환이 있음 ) 

select empno, ename, job
from emp ; 

 

9. 사원들의 입사일을 중복을 제거하여 출력

select distinct hiredate
from emp;

 

10. 사원들의 이름과 연봉을 출력

select ename, sal
from emp;

 

11. 사원들의 총 급여 (급여 + 커미션)를 출력 하되 커미션이 널인 경우 0으로 처리

select nvl2(comm, sal+comm, sal)
from emp;

 

12. 부서 테이블에서 목록을 출력하되  deptno 부서번호 , dname 부서명 , loc 는 지역으로 한글로 출력 

select deptno as 부서번호, dname as 부서명, loc as 지역
from dept;

 

13. 사원 테이블에서 10번 부서에서 근무하는 직원의 부서번호와 이름을 출력

select deptno, ename 
from emp
where deptno = 10 ;

 

14. 사원테이블에서 직책이 CLERK 인 직원의 사번과 이름을 검색 

select empno, ename
from emp
where job = 'CLERK';

 

15. 사원테이블에서 10번 부서에서 근무하고 직책이 manager 인 직언의 이름, 직책, 부서번호를 출력

select ename, job, deptno
from emp
where deptno = 10 and job = 'MANAGER';

 

16. 사원테이블에서 20번 부서가 아니면서 직책이 manager 인 직원의 이름 , 직책, 부서를 출력

select ename, job, deptno
from emp
where deptno <> 20 and job = 'MANAGER';

 

17. 사원테이블에서 급여가 1000에서 2000 사이의 직원의 사번 이름 급여를 출력

select empno, ename, sal
from emp
where sal between 1000 and 2000;

 

18. 사번이 75XX 인 직원을 검색하여 사번 이름 부서번호를 출력

select empno, ename, deptno
from emp
where empno like '75__';

 

19. 이름이 A로 시작하는 직원의 이름과 사번을 출력하되 이름이 빠른 직원부터 출력

select ename, empno
from emp
where ename like 'A%'
order by ename asc ;

 

20. 상급자 사원 번호가 null 이 아닌 사원의 사번 이름 메니져번호를 출력

select empno, ename, mgr
from emp 
where mgr is not null;

 

21. 사원번호,사원이름 ,부서이름을 검색

select empno, ename, dept.dname
from emp natural join dept;

 

22. 급여가 2000 이상인 사원들의 사원명과 지역을 검색

select ename, loc
from emp join dept using(deptno)
where sal >= 2000;

23. 23  FORD와 같은 부서에서 근무하는 직원의 이름을 출력

select ename 
from emp 
where deptno = (select deptno 
				from emp 
                where ename = 'FORD');

24. 사원번호, 사원이름을 부하직원수가 많은 순으로 검색

select b.empno, b.ename, count(*)
from emp a join emp b on a.mgr=b.empno
group by b.empno, b.ename, a.mgr
order by 3 desc;

25. enrol  학번이 2015 미만이 학생정보를 이용하여 a_enrol 테이블을 생성하고, 결과를 확인 (트랜잭션)

   모든 데이터를 삭제하고 , 복구

create table a_enrol
as select * 
from enrol
where stu_no < 20150000;

delete from a_enrol;

 

26. subject 테이블의 과목이름이 ‘시스템분석설계’인 과목에 해당하는 것을 a_enrol에서 ENR_GRADE를 10씩 증가시키는 쿼리를 작성하고 실해 하시오 (서브쿼리 수정문)

update a_enrol
set enr_grade = enr_grade + 10 
where sub_no = ( select sub_no 
					from subject 
                    where sub_name = '시스템분석설계');

27. 

student 테이블에서 체중-5 검색을 하는 인덱스 idx_stu_weight (이름이 중복되는 경우 바꿀 것 ) 를 생성하고 그 생성 정보를 확인 하시오 (인덱스)

create index idx_stu_weight on student(stu_weight-5);

select * from user_indexes 
where table_name = 'STUDENT';

select index_name, index_type, table_owner
from user_indexes
where table_name = 'STUDENT';

28.

29. STUDENT에서 ‘컴퓨터정보’ 학과를 검색하는 뷰를 생성

create view v_student_test
as select * from student where stu_dept = '컴퓨터정보';

29. ENROL과 SUBJECT 에서 과목이름, 과목번호, ENR등급을 조회하는 뷰를 생성

create view v_enrol_test 
as select sub_name, a.sub_no, enr_grade 
from enrol a, subject b 
where a.sub_no = b.sub_no ;

30. 부서 (부서코드,부서명)  ,사원 (사원코드,부서코드) 테이블 있다고 가정할 때 사원테이블의 부서코드가 부서테이블의 부서코드를 참조하는 외래키 작성코드를 기술하시오 . 제약이름은 임의로 정하시오.

create table dept_test(
deptno number(2)
 constraint pk_dept_test primary key,
dname varchar2(14));
 
 create table emp_test(
 empno number(2) 
  constraint pk_emp_test primary key,
 deptno number(2),
  constraint emp_test_deptno_fk1 foreign key(deptno) references dept_test(deptno));

// 아래의 코드를 실행해보았으나, 제약사항 검색을 어떻게 하는지 모르겠음 

SELECT constraint_name, status 
FROM    ALL_CONSTRAINTS
WHERE    TABLE_NAME = 'emp_test';

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'emp_test';

select * from information_schema.table_constraints where table_name = 'emp_test';