localhost_STUDENT_220225.sql

-- 학생테이블에서1학년학생ː講置臼㈖橘, 이름, 학과번호를t력하여라
SELECT * FROM STUDENT;
SELECT STUDNO, NAME, GRADE, DEPTNO FROM STUDENT;
SELECT STUDNO, NAME, DEPTNO FROM STUDENT
WHERE GRADE = '1';  
-- GRADE는 바차타입 1은 숫자인데 자동으로 형변환 해줌, 
-- 하지만 정확하게 하는게 좋음

DESC STUDENT;

-- 학생테이블에서몸무게가70kg 이하인학생ː講置臼㈖橘, 이름, 학년, 학과번호, 몸무게를t력하여라.
SELECT * FROM STUDENT;
SELECT STUDNO, NAME, GRADE, DEPTNO, WEIGHT FROM STUDENT
WHERE WEIGHT <= 70 ;

-- 학생테이블에서1학년이면서몸무게가70kg 이상인학생ː講置臼㈏見, 학년, 몸무게, 학과번호를t력하여라.
SELECT * FROM STUDENT;
SELECT NAME, GRADE, WEIGHT, DEPTNO FROM STUDENT;
SELECT NAME, GRADE, WEIGHT, DEPTNO FROM STUDENT 
WHERE GRADE = '1'
AND WEIGHT >= 70;

-- 학생테이블에서1학년이거나몸무게가70kg 이상인학생ː講置臼㈏見, 학년, 몸무게, 학과번호를t력하여라.
SELECT NAME, GRADE, WEIGHT, DEPTNO FROM STUDENT 
WHERE GRADE = '1'
OR WEIGHT >= 70;

-- 학생테이블에서학과번호가?101?이아ˆ‘학생의학번과이름과학과번호를t력해라
SELECT DEPTNO FROM STUDENT
WHERE DEPTNO != 101;

-- BETWEEN 연산자를사용하여몸무게가50kg에서70kg 사이인학생의학번, 이름, 몸무게를t력하여라.
SELECT * FROM STUDENT;
SELECT STUDNO, NAME, WEIGHT FROM STUDENT
WHERE WEIGHT BETWEEN 50 AND 70;

-- 학생테이블에서81년에서83년도에태어난학생의이름과생년월일을출력해라
SELECT * FROM STUDENT;
SELECT BIRTHDATE, NAME FROM STUDENT
WHERE TO_CHAR(BIRTHDATE, 'YY') IN (81, 82, 83);
-- WHERE TO_CHAR(BIRTHDATE, 'YY') BETWEEN 81 AND 83;
-- WHERE BIRTHDATE BETWEEN '81/01/01' AND '83/12/31';

-- IN 연산자를 사용하여 102번학과와 201번학과 학생의이름, 학년, 학과번호를 출력하여라
SELECT * FROM STUDENT;
SELECT NAME, GRADE, DEPTNO FROM STUDENT
WHERE DEPTNO NOT IN  ('102','201');

-- 학생테이블에서성이?김?씨인학생의이름, 학년, 학과번호를t력하여라.
SELECT * FROM STUDENT;
SELECT NAME, GRADE, DEPTNO FROM STUDENT
WHERE NAME LIKE '김%';

-- 이름 중에 '동'이 들어가는 학생의 학번, 이름 조회
SELECT * FROM STUDENT;
SELECT STUDNO, NAME FROM STUDENT
WHERE NAME LIKE '%동%';

INSERT INTO STUDENT(NAME) VALUES ('동서남');

SELECT * FROM PROFESSOR;
SELECT AVG(SAL) FROM PROFESSOR;
SELECT POSITION, AVG(SAL) FROM PROFESSOR GROUP BY POSITION;