You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
42 lines
793 B
42 lines
793 B
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 |