수업

--11강 --DEPT_TCL 테이블에 데이터를 입력 수정 삭제하기 (트렌섹션) CREATE TABLE DEPT_TCL AS SELECT * FROM DEPT; INSERT INTO DEPT_TCL VALUES (50,'DATABASE','SEOUL'); UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40; DELETE FROM DEPT_TCL WHERE DNAME='DATABASE'; SELECT * FROM DEPT_TCL; ROLLBACK; INSERT INTO DEPT_TCL VALUES (50,'NETWORK','SEOUL'); UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 20; DELETE FROM DEPT_T..

10강 --DML? INSERT/UPDATE/DELETE CREATE TABLE A_ENROL AS SELECT * FROM ENROL WHERE STU_NO

--9강 --SUB QUERY? --옥성우 학생보다 신장이 큰 학생들의 학번,이름,신장을 구하시오 SELECT STU_HEIGHT FROM STUDENT WHERE STU_NAME='옥성우'; SELECT STU_NO,STU_NAME,STU_HEIGHT FROM STUDENT WHERE STU_HEIGHT>172; SELECT STU_NO,STU_NAME,STU_HEIGHT FROM STUDENT WHERE STU_HEIGHT>= (SELECT STU_HEIGHT FROM STUDENT WHERE STU_NAME='옥성우') AND STU_NAME '옥성우'; --학생테이블에서 박희철 학생과 같은 몸무게를 가진 학생의 정보를 구하라 SELECT STU_WEIGHT FROM STUDENT WHERE STU..

8강 --조인(JOIN) --CROSS JOIN SELECT STUDENT.*,ENROL.* FROM STUDENT CROSS JOIN ENROL; SELECT * FROM STUDENT; SELECT * FROM ENROL; --EQUAL JOIN이란? --WHERE을 이용한 EQUAL JOIN SELECT STUDENT.STU_NO,STU_NAME,STU_DEPT,ENR_GRADE FROM STUDENT,ENROL WHERE STUDENT.STU_NO=ENROL.STU_NO; --NATURAL JOIN을 이용한 EQUAL JOIN SELECT STU_NO,STU_NAME,STU_DEPT,ENR_GRADE FROM STUDENT NATURAL JOIN ENROL; --JOIN USING을 이용한 EQU..

--7강 --1번 SELECT DEPTNO , TRUNC(AVG(SAL)) AS AVG_SAL , MAX(SAL) , MIN(SAL) , COUNT(*) AS CNT FROM EMP GROUP BY DEPTNO ; --2번 SELECT JOB , COUNT(*) FROM EMP GROUP BY JOB HAVING COUNT(*) >= 3; --3번 SELECT TO_CHAR(HIREDATE,'YYYY') AS HIRE_YEAR , DEPTNO , COUNT(*) AS CNT FROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY') , DEPTNO; --4번 SELECT NVL2(COMM, 'O' , 'X' ) , COUNT(*) AS CNT FROM EMP GROUP BY NVL2(C..

--6강 연습문제 --1번 SELECT EMPNO , RPAD(SUBSTR(EMPNO,1 ,2 ),LENGTH(EMPNO),'*') MASKING_EMPNO , ENAME , RPAD(SUBSTR (ENAME,1,1),LENGTH(ENAME),'*') MASKING_ENAME FROM EMP WHERE LENGTH(ENAME) >=5 AND LENGTH(ENAME)

7강 --그룹함수란? (단일행으로 구성되어있음) --COUNT/COUNT(*)/SUM/AVG/MAX/MIN/SUDDEV/VARIANCE... SELECT * FROM ENROL; SELECT MAX(ENR_GRADE),MIN(ENR_GRADE) FROM ENROL; SELECT MAX(STU_WEIGHT),MIN(STU_WEIGHT) FROM STUDENT; SELECT * FROM STUDENT; SELECT COUNT(*),COUNT(STU_HEIGHT) FROM STUDENT; SELECT SUM(STU_WEIGHT),TO_CHAR(AVG(STU_WEIGHT),'999.99') AS AVERGE FROM STUDENT; SELECT COUNT(*) AS 학생수,SUM(STU_HEIGHT) AS 신장..

6강 --함수 --1. 단일행함수 --1.1 숫자함수/문자함수/날짜함수/변환함수/일반함수 --숫자함수? ROUND(반올림) TRUNC(소수점 버리는 함수) CEIL(소수점 큰수에 붙음) FLOOR(작은수에 붙음) MOD(10,3)=1 ABS(그 값으로) SELECT ROUND(345.678),ROUND(345.678,0),ROUND(345.678,1), ROUND(345.678,-1) FROM DUAL; SELECT CEIL(3.01),CEIL(-3.01),FLOOR(3.01),FLOOR(-3.01) FROM DUAL; SELECT TRUNC(123.456),TRUNC(123.456,2) FROM DUAL; SELECT MOD(10,3) FROM DUAL; SELECT ABS(-100) FROM DUAL..

4~5강 select table_name from user_tables; --테이블의 구조를 보기위한 명령문? describe student; select * from student; --현재 테이블에서 각각의 테이블의 스키마를 살펴보고 검색하여 확인하시오? desc STUDENT; desc SUBJECT; desc enrol; --중복데이터를 제거하는 명령문인 distinct? select * from emp; select job,deptno from emp; select distinct job,deptno from emp; SELECT distinct job from emp; select * from student; select stu_dept from student; select distinct s..

오라클 SQL 1~3강 create table student( stu_no char(9), stu_name varchar2(12), stu_dept varchar2(20), stu_grade number(1), stu_class char(1), stu_gender char(1), stu_height number(5,2), stu_weight number(5,2), constraint p_stu_no primary key(stu_no)); create table subject( sub_no char(3), sub_name varchar2(30), sub_prof varchar2(12), sub_grade number(1), sub_dept varchar2(20), constraint p_sub_no pri..