localhost_STUDENT_220228.sql

SELECT * FROM STUDENT;

SELECT * FROM PROFESSOR WHERE PROFNO = 9903;

-- '' 이게 문자의 널
-- 디비에서는 '' = NULL 은 같은 값

SELECT
    SYSDATE,
    TO_CHAR(SYSDATE, 'MM/DD') MD,  -- 두개의 인자가 있자만 하나의 값만 나오는게 스칼라
    TO_CHAR(NULL, 'MM/DD') NV,
    TO_CHAR(SYSDATE, NULL) NV2
FROM DUAL;

SELECT AVG(MYVAL)
FROM (
    SELECT 1000 MYVAL FROM DUAL
    UNION ALL
    SELECT NULL FROM DUAL
    UNION ALL
    SELECT NULL FROM DUAL
    UNION ALL
    SELECT NULL FROM DUAL
    UNION ALL
    SELECT 2000 FROM DUAL
);

-- NULL의 연산이라 NULL
SELECT (1000 + NULL + NULL + NULL + 2000) / 5 FROM DUAL; 

SELECT 2 / 0 FROM DUAL;

-- 교수테이블에서이름, 직급, 보직수당을t력하여라NULL
SELECT * FROM PROFESSOR;
SELECT NAME, POSITION, COMM 
FROM PROFESSOR;

-- 교수테이블에서 보직수당을 박는 교수의 이름, 직급, 보직수당을t력하여라NULL
SELECT * FROM PROFESSOR;
SELECT NAME, POSITION, COMM
FROM PROFESSOR
WHERE COMM IS NOT NULL; 
-- WHERE COMM != NULL로 할 경우 null은같음('=') 또는같지않음('!=')을 다름값과 비교하거나 다른null과 비교할수없다.


-- 교수테이블에서급여에보직수당을 더한값은sal_com이라는별명으로t력하여라.
SELECT * FROM PROFESSOR;
SELECT NAME, SAL, COMM, SAL+COMM sal_com FROM PROFESSOR;

-- 102번학과의학생중에서1학년또는4학년학생의이름, 학년, 학과번호를t력하여라.
SELECT * FROM STUDENT;
SELECT NAME, GRADE, DEPTNO FROM STUDENT;
SELECT NAME, GRADE, DEPTNO FROM STUDENT
WHERE DEPTNO = 102
AND (GRADE = 1
OR GRADE = 4);

-- 102번학과의학생중에서4학년학생이거나소속학과에상관없이1학년학생의이름, 학년, 학과번호를t력하여라.
SELECT NAME, GRADE, DEPTNO FROM STUDENT
WHERE DEPTNO = 102
AND GRADE = 4
OR GRADE = 1;

-- 1학년이면서몸무게가70kg 이상인학생의집합(stud_heavy)과
-- 1학년이면서101번학과에소속된학생(stud_101)으로구성된두개의테이블생성박동’,
CREATE TABLE STUD_HEAVY AS
SELECT * FROM STUDENT
WHERE GRADE = 1 AND WEIGHT >= 70;

CREATE TABLE STUD_101 AS
SELECT * FROM STUDENT
WHERE GRADE = 1 AND DEPTNO = 101;

SELECT * FROM STUD_HEAVY;
SELECT * FROM STUD_101;

SELECT STUDNO, NAME, DEPTNO, WEIGHT FROM STUD_HEAVY
UNION
SELECT STUDNO, NAME, DEPTNO, WEIGHT FROM STUD_101;

SELECT STUDNO, NAME, DEPTNO, WEIGHT FROM STUD_HEAVY
UNION ALL
SELECT STUDNO, NAME, DEPTNO, WEIGHT FROM STUD_101;

SELECT STUDNO, NAME, DEPTNO, WEIGHT FROM STUD_HEAVY
INTERSECT
SELECT STUDNO, NAME, DEPTNO, WEIGHT FROM STUD_101;

SELECT STUDNO, NAME, DEPTNO, WEIGHT FROM STUD_HEAVY
MINUS
SELECT STUDNO, NAME, DEPTNO, WEIGHT FROM STUD_101;

/*
1 2 3
4 5 6
7 8 9
*/
SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9 FROM DUAL;
SELECT 1, 2, 3 FROM DUAL
UNION 
SELECT 4, 5, 6 FROM DUAL
UNION 
SELECT 7, 8, 9 FROM DUAL;

SELECT (ROWNUM-1)*3+1 N1, (ROWNUM-1)*3+2 N2, (ROWNUM-1)*3+3 N3 
FROM STUDENT
WHERE ROWNUM <= 3;

-- 학생테이블에서이름을가나다순으로정또臼㈏見, 학년, †화번호를t력하여라
SELECT * FROM STUDENT;
SELECT NAME, GRADE, TEL FROM STUDENT;
SELECT NAME, GRADE, TEL FROM STUDENT
ORDER BY 1;

-- 학생테이블에서학년을내림차순으로정또臼㈏見, 학년, †화번호를t력하여라
SELECT NAME, GRADE, TEL FROM STUDENT
ORDER BY GRADE DESC;

-- 학생테이블에서학년을내림차순, 이름 내림차순으로정또臼㈏見, 학년, †화번호를t력하여라
SELECT NAME, GRADE, TEL FROM STUDENT
ORDER BY 2 DESC, 1 DESC;



-- 학생 테이블에서 학번, 이름, 탄생년도를 조회
SELECT * FROM STUDENT;
SELECT GRADE, NAME, 
TO_CHAR(BIRTHDATE, 'YYYY') "탄생년도" FROM STUDENT;
-- 이 둘의 차이는 단일행이냐 복수행이냐의 차이
-- 학생 테이블에서 평균키, 평균 몸무게 조회
SELECT * FROM STUDENT;
SELECT AVG(HEIGHT), AVG(WEIGHT) FROM STUDENT;

-- 학생테이블에서 "김영균? 학생의이름, 사용자아이디를 출력하여라.
-- 그리고 사용자 아이디의 첫문자를 대문자로 변환하여 출력하여라
SELECT * FROM STUDENT;
SELECT NAME, USERID, INITCAP(USERID) "아이디", LOWER(USERID), UPPER(USERID) FROM STUDENT
WHERE NAME = '김영균';

-- 부서테이블에서 부서이름의 길이를 문자 수와 바이트 수로 각각 출력하여라
SELECT * FROM DEPARTMENT;
SELECT DNAME, LENGTH(DNAME), LENGTHB(DNAME)
FROM DEPARTMENT;

-- 학생테이블에서 1학년학생의 주민등록번호에서 생년월일과 
-- 태어난달을 추출하여 이름, 주민번호, 생년월일, 태어난달을 출력하여라.
SELECT * FROM STUDENT;
SELECT NAME, IDNUM, BIRTHDATE FROM STUDENT;
SELECT 
    NAME, GRADE, IDNUM, SUBSTR(IDNUM, 1,6) "출생년도", SUBSTR(IDNUM,3,2) "출생 달"
FROM 
    STUDENT
WHERE 
    GRADE = 1;

SELECT 1, '12345678901234567890' FROM DUAL
UNION
SELECT 2, 'CORPORATE FLOOR' FROM DUAL
UNION
SELECT 3, TO_CHAR(INSTR('CORPORATE FLOOR', 'OR')) FROM DUAL
UNION
SELECT 4, TO_CHAR(INSTR('CORPORATE FLOOR', 'OR', 3)) FROM DUAL
UNION
SELECT 5, TO_CHAR(INSTR('CORPORATE FLOOR', 'OR', 3, 2)) FROM DUAL
UNION
SELECT 6, TO_CHAR(INSTR('CORPORATE FLOOR', 'OR', -3)) FROM DUAL
UNION
SELECT 7, TO_CHAR(INSTR('CORPORATE FLOOR', 'OR', -3, 2)) FROM DUAL;