Script_20260408.sql

SELECT * FROM TAB;

SELECT title, credit, year, semester,division 
FROM course LEFT OUTER JOIN class
USING (course_id);

SELECT TITLE, CREDIT, YEAR, SEMESTER
FROM COURSE, CLASS 
WHERE COURSE.COURSE_ID = CLASS.COURSE_ID (+); 



SELECT TITLE, CREDIT, YEAR, SEMESTER 
FROM COURSE RIGHT OUTER JOIN CLASS 
USING (COURSE_ID);

SELECT TITLE, CREDIT, YEAR, SEMESTER 
FROM COURSE, CLASS 
WHERE COURSE.COURSE_ID (+) = CLASS.COURSE_ID;



SELECT TITLE, CREDIT, YEAR, SEMESTER 
FROM COURSE FULL OUTER JOIN CLASS 
USING (COURSE_ID);



SELECT * 
FROM STUDENT 
WHERE YEAR = 3;

SELECT COUNT(*) 
FROM STUDENT 
WHERE YEAR = 3;

SELECT * 
FROM EMP;

SELECT COUNT(*) 
FROM EMP;

SELECT COUNT(ENAME) 
FROM EMP;

SELECT COUNT(COMM) 	-- NULL은 개수를 세지 않는다!
FROM EMP;



SELECT COUNT(DEPT_ID) 
FROM STUDENT;

SELECT COUNT(DISTINCT DEPT_ID) 
FROM STUDENT;



SELECT * 
FROM STUDENT s, DEPARTMENT d 
WHERE S.DEPT_ID = D.DEPT_ID AND D.DEPT_NAME ='컴퓨터공학과';

SELECT COUNT(*) 
FROM STUDENT s, DEPARTMENT d 
WHERE S.DEPT_ID = D.DEPT_ID AND D.DEPT_NAME ='컴퓨터공학과';



SELECT *  
FROM PROFESSOR;

SELECT 2026 - YEAR_EMP 
FROM PROFESSOR;

SELECT SUM(2026 - YEAR_EMP) 
FROM PROFESSOR;

INSERT INTO PROFESSOR 
   VALUES('92302','750728*1102458','김태석',923,'교수',1999);

SELECT *  
FROM PROFESSOR;



SELECT * FROM EMP; 

SELECT * FROM DEPT;

SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
--------------------------------------------------
SELECT E.ENAME, D.DNAME
FROM EMP E
LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
--------------------------------------------------
SELECT E.EMPNO, E.ENAME, D.DNAME, D.LOC
FROM EMP E 
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
--------------------------------------------------(QUIZ-1)
SELECT * 
FROM EMP, DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO;
--------------------------------------------------(QUIZ-2)
SELECT ENAME, DNAME, LOC
FROM EMP, DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND ENAME = 'SMITH';
--------------------------------------------------



SELECT * 
FROM EMP;

SELECT SUM(SAL) 
FROM EMP;

SELECT * 
FROM EMP 
WHERE JOB = 'ANALYST'

SELECT SUM(SAL) 
FROM EMP 
WHERE JOB = 'ANALYST'
--------------------------------------------------------
SELECT * 
FROM EMP e, DEPT d 
WHERE E.DEPTNO = D.DEPTNO AND d.DNAME ='RESEARCH'

SELECT SUM(SAL) 
FROM EMP e, DEPT d 
WHERE E.DEPTNO = D.DEPTNO AND d.DNAME ='RESEARCH'
--------------------------------------------------------


SELECT AVG(2012 - YEAR_EMP) 
FROM PROFESSOR;

SELECT AVG(2026 - YEAR_EMP) 
FROM PROFESSOR;
-------------------------------------------------------
SELECT MAX(SAL) 
FROM EMP e , DEPT d 
WHERE E.DEPTNO = D.DEPTNO AND DNAME = 'ACCOUNTING';
-------------------------------------------------------
SELECT COUNT(SAL), MAX(SAL), MIN(SAL), AVG(SAL)  
FROM EMP e , DEPT d 
WHERE E.DEPTNO = D.DEPTNO 
AND DNAME = 'ACCOUNTING';
-------------------------------------------------------


########################################################
/* 프로그래머스 : SUM, MAX, MIN - "가장 비싼 상품 구하기"

SELECT MAX(PRICE) AS MAX_PRICE 
FROM PRODUCT;

**/
########################################################
/* 프로그래머스 : SELECT 조회 쿼리문 - "역순정렬하기"

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;

**/
########################################################


/*
SELECT ENAME, MAX(SAL) --> 오류 발생
FROM EMP;
*/
SELECT DEPT_ID, COUNT(*) 
FROM STUDENT 
GROUP BY DEPT_ID;	--> 이 경우는 오류 발생하지 않음

SELECT * 
FROM STUDENT;



SELECT DEPT_NAME AS 학과, COUNT(*) 학생수 
FROM STUDENT s, DEPARTMENT d 
WHERE S.DEPT_ID = D.DEPT_ID 
GROUP BY d.DEPT_NAME;	--> 이 경우는 오류 발생하지 않음

SELECT DNAME, COUNT(*), AVG(SAL), MAX(SAL), MIN(SAL) 
FROM EMP e , DEPT d 
WHERE E.DEPTNO = D.DEPTNO 
GROUP BY DNAME;	--> 이 경우는 오류 발생하지 않음


SELECT DEPT_NAME, COUNT(*), AVG(2012 - YEAR_EMP), MAX(2012 - YEAR_EMP) 
FROM PROFESSOR p, DEPARTMENT d 
WHERE P.DEPT_ID = D.DEPT_ID 
GROUP BY DEPT_NAME;


/*
SELECT DEPT_NAME, COUNT(*), AVG(2012 - YEAR_EMP), MAX(2012 - YEAR_EMP) 
FROM PROFESSOR p , DEPARTMENT d 
WHERE P.DEPT_ID = D.DEPT_ID AND AVG(2012 - YEAR_EMP) >= 10
GROUP BY DEPT_NAME;  --> 오류 발생
*/

SELECT DEPT_NAME, COUNT(*), AVG(2012 - YEAR_EMP), MAX(2012 - YEAR_EMP) 
FROM PROFESSOR p , DEPARTMENT d 
WHERE P.DEPT_ID = D.DEPT_ID 
GROUP BY DEPT_NAME
HAVING AVG(2012 - YEAR_EMP) >= 10;

SELECT DNAME, COUNT(*), AVG(SAL), MAX(SAL), MIN(SAL) 
FROM EMP e , DEPT d 
WHERE E.DEPTNO =D.DEPTNO 
GROUP BY DNAME 
HAVING COUNT(*) >= 5;


###############################################################
/* 프로그래머스 : GROUP BY문 - "성분으로 구분한 아이스크림 총 주문량"

SELECT I.INGREDIENT_TYPE, SUM(F.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF F
JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
GROUP BY I.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC;


SELECT I.INGREDIENT_TYPE, 
       SUM(F.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF F
JOIN ICECREAM_INFO I USING (FLAVOR)
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER;


SELECT I.INGREDIENT_TYPE,
       SUM(F.TOTAL_ORDER) AS TOTAL_ORDER
FROM ICECREAM_INFO I
INNER JOIN (
    SELECT FLAVOR, TOTAL_ORDER 
    FROM FIRST_HALF
) F ON I.FLAVOR = F.FLAVOR
GROUP BY I.INGREDIENT_TYPE
ORDER BY 2;


SELECT I.INGREDIENT_TYPE, 
       SUM(S.SUM_ORDER) AS TOTAL_ORDER
FROM (
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS SUM_ORDER
    FROM FIRST_HALF
    GROUP BY FLAVOR
) S
JOIN ICECREAM_INFO I ON S.FLAVOR = I.FLAVOR
GROUP BY I.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER;


SELECT INGREDIENT_TYPE, SUM(F.TOTAL_ORDER) AS TOTAL_ORDER 
FROM FIRST_HALF F, ICECREAM_INFO I 
WHERE F.FLAVOR = I.FLAVOR 
GROUP BY INGREDIENT_TYPE;

*/
###############################################################


SELECT * 
FROM EMP 
WHERE COMM IS NULL;

SELECT * 
FROM EMP 
WHERE COMM IS NOT NULL;



SELECT COUNT(COMM) 
FROM EMP; 

SELECT COUNT(*) 
FROM EMP; 

SELECT * 
FROM EMP 
WHERE COMM <> 500;



SELECT STU_ID 
FROM TAKES 
WHERE GRADE IS NULL;

SELECT STU_ID 
FROM TAKES 
WHERE GRADE <> 'A+';



###########################################################
/* 프로그래머스 : IS NULL - "이름이 없는 동물의 아이디"

SELECT ANIMAL_ID  
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID

**/
###########################################################



SELECT TITLE 
FROM COURSE 
WHERE COURSE_ID IN (
			SELECT DISTINCT COURSE_ID
			FROM CLASS
			WHERE CLASSROOM = '301호'
);


SELECT DISTINCT TITLE 
FROM COURSE c1 , CLASS c2 
WHERE C1.COURSE_ID = C2.COURSE_ID 
AND CLASSROOM = '301호';



SELECT TITLE 
FROM COURSE 
WHERE COURSE_ID NOT IN (
			SELECT DISTINCT COURSE_ID 
			FROM  CLASS
			WHERE YEAR = 2012 AND SEMESTER = 2
			);


SELECT TITLE 
FROM COURSE
WHERE COURSE_ID IN (
    SELECT COURSE_ID FROM COURSE
    MINUS
    SELECT COURSE_ID 
    FROM CLASS 
    WHERE YEAR = 2012 AND SEMESTER = 2
);



CREATE OR REPLACE VIEW V_TAKES AS 
					SELECT STU_ID, CLASS_ID 
					FROM TAKES;

SELECT * 
FROM V_TAKES;


CREATE OR REPLACE VIEW CS_STUDENT AS
	SELECT S.STU_ID, S.RESIDENT_ID, S.NAME, S.YEAR, S.ADDRESS, S.DEPT_ID 
	FROM STUDENT S, DEPARTMENT D 
	WHERE S.DEPT_ID = D.DEPT_ID AND 
	D.DEPT_NAME = '컴퓨터공학과';


SELECT * 
FROM CS_STUDENT;

INSERT INTO V_TAKES 
VALUES ('1292502', 'C101-01');

SELECT * 
FROM V_TAKES;

SELECT * FROM TAKES;


CREATE OR REPLACE VIEW V_TAKES AS 
	SELECT STU_ID, CLASS_ID 
	FROM TAKES 
	WITH READ ONLY;

SELECT * FROM V_TAKES; 

INSERT INTO V_TAKES VALUES ('1292503', 'C101-01');