USE Study
GO

CREATE VIEW EMP30
AS (SELECT * FROM EMPLOYEE WHERE DNO = 30)

SELECT * FROM EMP30 WHERE SALARY >= 300

CREATE VIEW EMPAVGSAL
AS (SELECT AVG(SALARY) AS SALAVG FROM EMPLOYEE)

SELECT * FROM EMPAVGSAL

-- ÀζóÀÎ ºä
-- Á¶ÀÎ ¿¬»êÀ¸·Î Á¶°Ç¿¡ ¸Â´Â ¸ðµç Æ©ÇýÖÀÌ ¸ÕÀú »ý¼ºµÊ
SELECT DNAME, AVG(SALARY) AS AVG_SAL 
FROM EMPLOYEE AS E, DEPARTMENT AS D 
WHERE E.DNO = D.DNO 
GROUP BY DNAME
-- ¿øÇÏ´Â Æ©Çø¸ »ý¼º ÈÄ Á¶ÀÎÇÏ¿© ¿¬»ó È¿À²¼º Áõ°¡
SELECT DNAME, AVG_SAL
FROM (
	SELECT DNO, AVG(SALARY) AS AVG_SAL
	FROM EMPLOYEE
	GROUP BY DNO
	) AS S, DEPARTMENT D
WHERE S.DNO = D.DNO

GO
-- WITH¸¦ »ç¿ëÇÑ ÀζóÀκä
WITH S (DNO, AVG_SAL)
AS (
	SELECT DNO, AVG(SALARY)
	FROM EMPLOYEE
	GROUP BY DNO
	)
SELECT DNAME, AVG_SAL
FROM S, DEPARTMENT AS D
WHERE S.DNO = D.DNO

-- ºä Á¤ÀÇ º¸±â
SP_HELPTEXT EMP30