localhost_STUDENT_220224.sql

SELECT * FROM STUDENT;

-- 학생 테이블에서 학번, 이름, 학과번호를 조회하시오
SELECT STUDNO, NAME , DEPTNO FROM STUDENT;

-- 중복을 카운트할때 행을 기준으로
SELECT 
    DEPTNO,
    NAME 
FROM
    STUDENT 
WHERE
    deptno != 101
    AND 1=1
ORDER BY 1 ;

-- 학생테이블에서 학과별 인원스를 조회
SELECT DEPTNO, COUNT(*)
FROM STUDENT
GROUP BY DEPTNO  -- 학과별 인원수가 나옴
HAVING COUNT(*) >= 5;

SELECT * FROM STUDENT;
--학생테이블에서중복되는학과번호(deptno)를제외하고t력하여라
SELECT DISTINCT DEPTNO FROM STUDENT;
--학생테이블에서중복되는학과번호, 학년제외하고t력하여라
SELECT DEPTNO, GRADE FROM STUDENT ORDER BY 1 DESC, 2;

-- 데이터베이스 문자열 리터릴 : '' 꼭 이거 써야함

-- HELLO WORLD를 출력하시오
SELECT 'HELLO WORLD' FROM STUDENT;

-- 학생의 이름과 '안녕'결합
SELECT NAME || ' 안녕' FROM STUDENT;

SELECT CONCAT(NAME, ' 안녕') AS HI FROM STUDENT ;  -- 컬럼에서는 AS는 필수가 아님

-- 학번에 "학번" 이름에는 "이름"을 부여한 별칭으로 학번,이름 조회
SELECT STUDNO "학번", NAME "이름" FROM STUDENT;

-- 부서테이블에서부서이름칼럼의별명은dept_name, 부서번호칼럼의별명은DN으로부여하여t력하여라
SELECT * FROM department;
SELECT DNAME, DEPTNO FROM department;
SELECT DNAME "dept_name", DEPTNO "DN" FROM department;

-- 학생테이블에서학번과이름칼럼을연결하여“Student”라는별명으로하나의칼럼처럼연결하여t력하여라
SELECT * FROM STUDENT;
SELECT STUDNO, NAME FROM student;
SELECT STUDNO || ' ' || NAME "STUDENT" FROM student;

-- 학생의몸무게를pound로Q산하고칼럼이름을?weight_pound?라는별명으로t력하여라. 1kg은2.2pound이다.
SELECT * FROM STUDENT;
SELECT STUDNO,NAME, WEIGHT FROM STUDENT;
SELECT NAME, WEIGHT, WEIGHT * 2.2 || 'POUND' "weight_pound"  FROM STUDENT;

/* 
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
이 순서 굉장히 중요함!!!!!!!!!!!!!!!!!!!!!
*/


SELECT DEPTNO, COUNT(*)
FROM STUDENT
WHERE WEIGHT IS NOT NULL
GROUP BY DEPTNO
HAVING COUNT(DEPTNO) >= 5
ORDER BY DEPTNO;

-- 오류 발생 순서 
-- FROM >> HAVING >> GROUP BY >> WHERE >> SELECT >> ORDER BY  이 순서는 다를 수 있음

DROP TABLE EX_TYPE;
CREATE TABLE EX_TYPE (
    C CHAR(10),
    V VARCHAR(10)
);

INSERT INTO EX_TYPE VALUES ('sql', 'sql');
SELECT * FROM EX_TYPE;


SELECT *
FROM EX_TYPE
WHERE C = 'sql';

SELECT *
FROM EX_TYPE
WHERE V = 'sql';

SELECT *
FROM EX_TYPE
WHERE C = 'sql      ';

SELECT *
FROM EX_TYPE
WHERE V = 'sql      ';


SELECT *
FROM EX_TYPE
WHERE C = V;

SELECT 1 
FROM DUAL
WHERE 1 = '1';

SELECT 3.14 + 1 FROM DUAL;

SELECT STUDNO, NAME, BIRTHDATE, SYSDATE, ROUND(SYSDATE - BIRTHDATE)
FROM STUDENT
WHERE STUDNO = 10101;

SELECT SYSDATE - TO_DATE('92/10/19') FROM DUAL;

SELECT 
    TO_CHAR(SYSDATE , 'YY/MM/DD HH24:MI:SS') NOW,
    TO_CHAR(SYSDATE + 1, 'YY/MM/DD HH24:MI:SS') "1DAY_AFTER",
    TO_CHAR(SYSDATE + 3 / 24, 'YY/MM/DD HH24:MI:SS') "3 HOURS AFTER",
    TO_CHAR(SYSDATE + 30 / 24/ 60, 'YY/MM/DD HH24:MI:SS') "30MINUTE AFTER",
    TO_CHAR(SYSDATE + 0.5, 'YY/MM/DD HH24:MI:SS') "12HOUR AFTER",
    TO_CHAR(SYSDATE + 50 / 24/ 60, 'YY/MM/DD HH24:MI:SS'),
    ADD_MONTHS(SYSDATE, -1)
FROM DUAL;

SELECT
    TO_CHAR(TO_DATE('92/10/19'),'YY/MM/DD HH24:MI:SS') 
FROM DUAL;

SELECT ROUND(SYSDATE-1/24, 'MM') FROM DUAL;

SELECT ROWID,ROWNUM, S.* FROM STUDENT S ORDER BY STUDNO DESC;