mssql_Study/view.sql

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

-- <EFBFBD>ζ<EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD>
-- <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD>ǿ<EFBFBD> <EFBFBD>´<EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> Ʃ<EFBFBD>ý<EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
SELECT DNAME, AVG(SALARY) AS AVG_SAL 
FROM EMPLOYEE AS E, DEPARTMENT AS D 
WHERE E.DNO = D.DNO 
GROUP BY DNAME
-- <EFBFBD><EFBFBD><EFBFBD>ϴ<EFBFBD> Ʃ<EFBFBD>ø<EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ͽ<EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> ȿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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<EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD>ζ<EFBFBD><EFBFBD>κ<EFBFBD>
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

-- <EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD>
SP_HELPTEXT EMP30