수업

--19강 --저장프로시저(PROCEDURE)와 함수(FUNCTION) --학생테이블에서 학번과 학년을 입력으로하여 수정하는 저장프로시저를 작성하라. CREATE OR REPLACE PROCEDURE TEST2 (V_STU_NO IN STUDENT.STU_NO%TYPE, V_STU_GRADE IN STUDENT.STU_GRADE%TYPE) IS BEGIN UPDATE STUDENT SET STU_GRADE = V_STU_GRADE WHERE STU_NO = V_STU_NO; END TEST2; / SELECT * FROM STUDENT WHERE STU_NO = 20153075; EXECUTE TEST2(20153075,3); SELECT * FROM STUDENT WHERE STU_NO = 201530..

--15강 --DCL CREATE USER TST IDENTIFIED BY 1234; --16강 --PL/SQL (PROCEDURAL LANGUAGE EXTENSION TO SQL) --DECLARE(선언부) , BEGIN(실행부) , EXCEPTION(예외 처리부) SET SERVEROUTPUT ON; BEGIN DBMS_OUTPUT.PUT_LINE('WELCOME TO WORLD~!'); END; / --DECLARE/BEGIN/END DECLARE V_EMPNO NUMBER(4) :=7788; V_ENAME VARCHAR2(10); BEGIN V_ENAME := 'SCOTT'; DBMS_OUTPUT.PUT_LINE('V_EMPNO: ' || V_EMPNO); DBMS_OUTPUT.PUT_LINE('..

--1 --1) CREATE TABLE EMPIDX AS SELECT * FROM EMP; --2) CREATE INDEX IDX_EMPIDX_EMPNO ON EMPIDX (EMPNO); --3) SELECT * FROM EMPIDX; --2 CREATE OR REPLACE VIEW EMPIDX_OVER15K AS (SELECT EMPNO, ENAME, JOB, DEPTNO, SAL, NVL2(COMM, 'O', 'X') AS COMM FROM EMPIDX WHERE SAL > 1500); SELECT * FROM EMPIDX_OVER15K; --3 --1) CREATE TABLE DEPTSEQ AS SELECT * FROM DEPT; --2) CREATE SEQUENCE SEQ_DEPTSEQ INCRE..

--CONSTRAINT --NOT NULL / UNIQUE KEY / PRIMARY KEY(NOT NULL + UNIQUE KEY) / FOREIGN KEY / CHECk --NOT NULL create table t_student( stu_no char(9), stu_name varchar2(12), stu_dept varchar2(20) constraint n_stu_dept not null, stu_grade number(1), stu_class char(1), stu_gender char(1), stu_height number(5,2), stu_weight number(3,1) ); desc t_student; --UNIQUE KEY create table t_student( stu_no char..

--1번 CREATE TABLE EMP_HW( EMPNO NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2)); DESC EMP_HW; --2번 ALTER TABLE EMP_HW ADD ( BIGO VARCHAR2(20)); --3번 ALTER TABLE EMP_HW MODIFY (BIGO VARCHAR2(30)); ---4번 ALTER TABLE EMP_HW RENAME COLUMN BIGO TO REMARK; --5번 INSERT INTO EMP_HW(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DE..

--12강 --DDL, CREATE/DROP/TRUNCATE/ALTER CREATE TABLE TEST1 (U_ID VARCHAR2(10), U_DATE DATE); DESC TEST1; --NUMBER(N,M) --CHAR(N) --VARCHAR2(N) --DATE --LONG : 2GB 문자데이터에 대한 정의 --TIMESTAMP : 년 월 일 시 분 초 6자리소수부의 시간형태를 나타내는 자료형 타입. DESC STUDENT; CREATE TABLE T_STUDENT AS SELECT * FROM STUDENT WHERE STU_DEPT = '기계'; SELECT * FROM T_STUDENT; --열의 이름을 건드릴때는 ALTER()사용함. --새로운 열을 추가할때.. ALTER TABLE T_ST..

create table student1 as select * from student where stu_grade in (1,2); create table subject1 as select * from subject; create table enrol1 as select * from enrol; select * from student1; select * from subject1; select * from enrol1; /*no1. 학번 20101059, 이름 조병준, 학과 컴퓨터정보, 학년 1, 반 B, 키 164, 몸무게 70인 남학생이 추가되었다.*/ INSERT INTO STUDENT1 VALUES (20101059,'조병준','컴퓨터정보',1,'B','M',164,70); /*no2. 학번 2010..

--9강 연습문제 --1번 SELECT JOB,EMPNO,ENAME,SAL,DEPTNO,DNAME FROM EMP NATURAL JOIN DEPT WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN'); --2번 SELECT EMPNO,ENAME,DNAME,HIREDATE,LOC,SAL,GRADE FROM EMP NATURAL JOIN DEPT , SALGRADE WHERE SAL > (SELECT AVG(SAL) FROM EMP) AND SAL BETWEEN LOSAL AND HISAL ORDER BY SAL DESC,EMPNO; --3번 SELECT EMPNO,ENAME,JOB,DEPTNO,DNAME,LOC FROM EMP NATURAL JOIN DEPT ..

--8강 연습문제 --1번 SELECT A.DEPTNO,A.DNAME,B.EMPNO,B.ENAME,B.SAL FROM DEPT A,EMP B WHERE A.DEPTNO = B.DEPTNO AND SAL >= 2000 ORDER BY DEPTNO; --2번 SELECT A.DEPTNO,DNAME,TRUNC(AVG(SAL)) AS AVG_SAL,MAX(SAL) MAX_SAL,MIN(SAL) MIN_SAL,COUNT(*) AS CNT FROM DEPT A , EMP B WHERE A.DEPTNO=B.DEPTNO GROUP BY A.DEPTNO , DNAME; --3번 SELECT A.DEPTNO,DNAME,EMPNO,ENAME,JOB,SAL FROM DEPT A , EMP B WHERE A.DEPTNO = B..

CREATE TABLE CHAP10HW_EMP AS SELECT * FROM EMP; CREATE TABLE CHAP10HW_DEPT AS SELECT * FROM DEPT; CREATE TABLE CHAP10HW_SALGRADE AS SELECT * FROM SALGRADE; --1번 INSERT INTO CHAP10HW_DEPT(DEPTNO,DNAME,LOC) VALUES (50,'ORACLE','BUSAL'); INSERT INTO CHAP10HW_DEPT(DEPTNO,DNAME,LOC) VALUES (60,'SQL','LISAN'); INSERT INTO CHAP10HW_DEPT(DEPTNO,DNAME,LOC) VALUES (70,'SELECT','INCHEON'); INSERT INTO CHAP..