Intro to Database (9) : Subset of SQL(SQL 하위 범주)

2019-07-09

 

1. DDL(Data definition language)

데이터 베이스의 구조를 정의하는 데 사용된다. 

CREATE, ALTER, RENAME, DROP, TRUNCATE(*내용만 없어짐) 등

2. DML(Data manipulation language)

데이터 정의, 갱신, 복구

SELECT, INSERT, UPDATE, DELETE, MERGE

3. SQL/PSM(Structured Query Language/Procedural programming capabilities)

순차적 프로그래밍 능력

 

4. TCL(Transaction control language) 

연산자 집합을 통제 COMMIT, ROLLBACK 

5. DCL(Data control language)

데이터베이스 권한을 부여하고 취소함 GRANT, REVOKE

 

실습(1) : DDL을 Oracle Developer 본인 계정에서 

--두개의 컬럼을 가지는 테스트1 테이블을 생성하시오
CREATE TABLE test1(
U_ID VARCHAR2(10),
U_DATE DATE);

--Stu_dept가 기계인 학생들로 t_student 테이블 생성
CREATE TABLE T_STUDENT AS SELECT * FROM STUDENT 
WHERE STU_DEPT = '기계'; 
ALTER TABLE T_STUDENT ADD (ARMY CHAR(1)); /*컬럼 추가*/
--열구조를 변경하시요(Army컬럼을 number로 변경)
alter table t_student modify(army number); /*컬럼 이름, 데이터 타입 순서대로*/
--열삭제(Army컬럼을 삭제)
alter table t_student drop (army);
--테이블 이름을 t_student에서 test_student로 변경하시요
rename t_student to test_student;
--Test_student의 레코드만 삭제한후 내용을 검색하시요
truncate table test_student;
select * from test_student;
--Test_student를 삭제한후 desc로 확인하시요
drop table test_student; 
desc test_student;

desc로 테이블 삭제 여부를 확인

기본키 제약조건을 추가하고 삭제하는 등의 다양한 작업도 할 수 있다.

(2)     

--사원테이블과 같은 구조의 t_tbl1(구조만 가져오기)
CREATE TABLE T_tbl1 AS SELECT * FROM EMP; /*구조를 가져와서 데이터 삭제*/
truncate table T_tbl1;

CREATE TABLE T_TBL1 AS SELECT * FROM EMP WHERE 1=2; /*'아무'조건을 줌으로서 빈 테이블만 가져오기*/
--구조확인
select * from T_tbl1; /*확인*/

--t_tbl1테이블에 부서번호가 20인 데이터를 3개 만들어서 확인
INSERT INTO T_TBL1 (DEPTNO) VALUES ('20'); /*3번 반복실행*/

--t_tbl1에 t_gender라는 컬럼을 삽입하고 확인. 데이터타입 char(1)
ALTER TABLE T_TBL1 ADD (T_GENDER CHAR(1));
DESC T_TBL1;

--t_gender의 구조변경하고 확인. 데이터타입 varchar2(10)
ALTER TABLE T_TBL1 MODIFY (T_GENDER VARCHAR2(10));
DESC T_TBL1;

--t_gender를 삭제
ALTER TABLE T_TBL1
DROP (T_GENDER);
DESC T_TBL1;

--t_tbl1의 모든 데이터를 삭제하고 확인
truncate table T_tbl1;
SELECT * FROM T_TBL1;

(3) 

--사원테이블과 구조가 같은 t_emp2를 생성
CREATE TABLE T_EMP2 AS SELECT * FROM EMP WHERE 1=2; /*구조를 가져와서 데이터 삭제*/
DESC T_EMP2;
SELECT * FROM T_EMP2;
--Deptno에 외래키 추가
alter table t_emp2 add constraint t_emp2_fk FOREIGN key (deptno) 
references dept(deptno);
--Constraint t_emp2_fk references dept(deptno)
--Sal에 NOT NULL 제약조건 추가
ALTER TABLE T_EMP2 MODIFY SAL NUMBER(8,2) NOT NULL; 
DESC T_EMP2;
--제약조건 검색
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T_EMP2';
--외래키조건 비활성화
ALTER TABLE T_EMP2  DISABLE CONSTRAINTS T_EMP2_FK;
--외래키조건 활성화
ALTER TABLE T_EMP2  ENABLE CONSTRAINTS T_EMP2_FK;
--외래키 삭제,확인
ALTER TABLE T_EMP2  DROP CONSTRAINTS T_EMP2_FK;
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T_EMP2';
--t_emp2 테이블 삭제, 확인
DROP TABLE T_EMP2;
DESC T_EMP2;

(4) 예제 풀이 

--1. 사원번호 7703, 사원이름 JOSH, 사원직무 SALESMAN, 상급자사원번호 7566, 급여 1400, 커미션 0, 부서번호 20인 사원이 오늘 입사
INSERT INTO EMP1 VALUES (7703, 'JOSH', 'SALESMAN', 7566, SYSDATE ,1400, 0, 20);
/*데이터가 다 있기 때문에 순서대로 넣으면 되고 숫자 문자만 신경씀 되고, 날짜는 SYSDATE으로*/
INSERT INTO EMP1 VALUES (7703, 'JOSH', 'SALESMAN', 7566,
TO_DATE('19/09/07','YY/MM/DD'),1400, 0, 20);

--2. 사원번호 7401, 사원이름 HOMER, 급여 1300, 부서번호 10인 사원이 입사
INSERT INTO EMP1 (EMPNO, ENAME, SAL, DEPTNO) VALUES (7401, 'HOMER', 1300, '10');

--3. 사원번호 7323, 사원이름 BRANDA, 사원번호 7499와 동일한 급여를 받는 사원입사
INSERT INTO EMP1 (EMPNO, ENAME, SAL) VALUES 
(7323, 'BRANDA', (SELECT SAL FROM EMP1 WHERE EMPNO = 7499));

--4. 사원테이블에서 부서번호가 10인 데이터를 emp1에 삽입
INSERT INTO EMP1 (SELECT * FROM EMP WHERE DEPTNO = 10);     

--5. 사원번호 7369의 사원직무를 ANALYST로 수정
UPDATE EMP1 SET JOB = 'ANALYST' WHERE EMPNO = 7369;

--6. 부서번호 20인 직원들의 급여를 10% 감소
UPDATE EMP1 SET SAL = (SAL/100)*90 WHERE DEPTNO = 20;

--7. 모든 사원의 급여를 100증가
UPDATE EMP1 SET SAL = SAL+100;

--8. 사원번호 7902의 상급자사원번호를 7654, 부서번호를 30으로 수정
UPDATE EMP1 SET MGR = 7654, DEPTNO = 30
WHERE EMPNO = 7902;

--9. 지역이 DALLAS인 사원들의 급여를 10 감소
ROLLBACK;
UPDATE EMP1 SET SAL = SAL-10 
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT1 WHERE LOC = 'DALLAS');

--10. 급여등급이 2인 사원들의 급여를 20 감소
UPDATE EMP1 SET SAL=SAL-20 
WHERE SAL > (SELECT LOSAL FROM SALGRADE WHERE GRADE = 2) 
AND SAL < (SELECT HISAL FROM SALGRADE WHERE GRADE = 2);

--11. 사원번호 7499 퇴사
DELETE FROM EMP1 WHERE EMPNO= '7499';

--12. 부서번호 50, 부서이름 ‘PLANNING’, 지역 ‘MIAMI’추가
INSERT INTO DEPT1 VALUES (50, 'PLANNING','MIAMI');

--13. 부서번호 40이 60으로 변경
UPDATE DEPT1 SET DEPTNO = 60 WHERE DEPTNO = 40;

--14. 부서번호 30이 폐쇄
DELETE FROM DEPT1 WHERE DEPTNO = '30';

--15. dept1 테이블에 없는 부서번호를 가지고 있는 사원들의 부서번호를 99로 변경
update emp1 set DEPTNO = 99 
where DEPTNO not in (select dept1.DEPTNO from dept1, emp1 where dept1.DEPTNO = emp1.DEPTNO

update emp1 set deptno = 99 where deptno = null;
--16. emp1에서 99번 부서번호를 삭제
delete emp1 where deptno=99;

--17. 상급자사원번호가 없는 사원의 급여를 100 증가
UPDATE EMP1 SET SAL = SAL + 100 WHERE MGR IS NULL;

--18. 사원 JONES, JOSH, CLARK이 30부서로 바뀜
UPDATE EMP1 SET DEPTNO = 30 
WHERE ENAME IN(SELECT ENAME FROM EMP1 WHERE ENAME IN ('JONES', 'JOSH', 'CLARK'));

UPDATE EMP1 SET DEPTNO = 30 
WHERE ENAME IN ('JONES', 'JOSH', 'CLARK');
--19. 커미션이 NULL인 데이터를 0으로 바꿈
UPDATE EMP1 SET COMM=0 WHERE COMM IS NULL;

--20. emp1 전체테이블 삭제
DROP TABLE EMP1;

(5) Big Mart Sales : 세일 분석하기

https://www.kaggle.com/brijbhushannanda1979/bigmart-sales-data 에서 데이터를 받아보실 수 있습니다.

빅마트체인은 여러 도시에10개의 매장을 가지고 있고 1559개의 상품을 취급하고 있습니다. 여러 매장에서 판매되는 상품의 세일을 분석하십시오. 

 

 여러 결과와 인사이트를 도출하기 위해 쿼리문을 짜고, 엑셀에 옮겨 시각화를 진행했다. 

UPDATE BIGMART 
SET ITEM_WEIGHT = ROUND((SELECT AVG(ITEM_WEIGHT) 
FROM BIGMART)) WHERE ITEM_WEIGHT IS NULL;

UPDATE BIGMART 
SET ITEM_VISIBILITY = ROUND((SELECT AVG(ITEM_VISIBILITY) 
FROM BIGMART)) WHERE ITEM_VISIBILITY IS NULL;

SELECT AVG(ITEM_WEIGHT) 
FROM BIGMART GROUP BY ITEM_FAT_CONTENT;

SELECT ITEM_TYPE, SUM(ITEM_OUTLET_SALES) FROM BIGMART GROUP BY ITEM_TYPE;

SELECT OUTLET_IDENTIFIER, SUM(ITEM_OUTLET_SALES) FROM BIGMART GROUP BY OUTLET_IDENTIFIER;

SELECT OUTLET_TYPE, SUM(ITEM_OUTLET_SALES) FROM BIGMART
GROUP BY OUTLET_TYPE;

UPDATE BIGMART SET OUTLET_SIZE = 'MEDIUM'
WHERE OUTLET_SIZE IS NULL;

SELECT OUTLET_ESTABLISHMENT_YEAR, SUM(ITEM_OUTLET_SALES) FROM BIGMART 
GROUP BY OUTLET_ESTABLISHMENT_YEAR ORDER BY OUTLET_ESTABLISHMENT_YEAR;

SELECT OUTLET_WEIGHT, ITEM_OUTLET_SALES FROM BIGMART;

SELECT ITEM_TYPE, SUM(ITEM_OUTLET_SALES) FROM BIGMART 
WHERE OUTLET_ESTABLISHMENT_YEAR = 1985 GROUP BY ITEM_TYPE ORDER BY 2 DESC;
SELECT ITEM_TYPE, SUM(ITEM_OUTLET_SALES) FROM BIGMART 
WHERE OUTLET_ESTABLISHMENT_YEAR = 1998 GROUP BY ITEM_TYPE ORDER BY 2 DESC;

SELECT OUTLET_LOCATION_TYPE, OUTLET_ESTABLISHMENT_YEAR FROM BIGMART 
GROUP BY Outlet_Location_Type, Outlet_Establishment_Year ORDER BY OUTLET_LOCATION_TYPE;

update bigmart set item_fat_content = 'Regular'
where item_fat_content = 'reg';
update bigmart set item_fat_content = 'Low Fat'
where item_fat_content = 'LF';
update bigmart set item_fat_content = 'Low Fat'
where item_fat_content = 'low fat';

select item_fat_content, avg(item_visibility) from bigmart group by item_fat_content;
select item_fat_content, avg(item_mrp) from bigmart group by item_fat_content;
select item_fat_content, avg(item_outlet_sales) from bigmart group by item_fat_content;

SELECT ITEM_TYPE, SUM(ITEM_VISIBILITY), SUM(ITEM_OUTLET_SALES) FROM BIGMART 
GROUP BY ITEM_TYPE ORDER BY 3 DESC;

위와 같은 시각화를 엑셀을 이용해 도출해 보았다.

 

코드가 실행이 잘 안되거나 질문 사항있으시면 댓글로 문의해주시길 바랍니다. 감사합니다!! :D