728x90
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | --๋ฌธ์ 1) EMPLOYEES ํ
์ด๋ธ์์ 20๋ฒ ๋ถ์์ ์ธ๋ถ ์ฌํญ์ ํฌํจํ๋ EMP_20 --VIEW๋ฅผ ์์ฑ ํ๋ผ CREATE VIEW EMP_20 AS SELECT * FROM employees WHERE department_id = 20; ------------------------------------------------------------------ SELECT * FROM EMP_20; -- ๊ฒฐ๊ณผ ํ์ธ์ฉ ------------------------------------------------------------------ --๋ฌธ์ 2) EMPLOYEES ํ
์ด๋ธ์์ 30๋ฒ ๋ถ์๋ง EMPLOYEE_ID ๋ฅผ emp_no ๋ก --LAST_NAME์ name์ผ๋ก SALARY๋ฅผ sal๋ก ๋ฐ๊พธ์ด EMP_30 VIEW๋ฅผ ์์ฑํ๋ผ. CREATE VIEW EMP_30 AS SELECT employee_id AS emp_no, last_name AS name, salary as sal FROM employees WHERE department_id=30; ------------------------------------------------------------------ SELECT * FROM EMP_30; -- ๊ฒฐ๊ณผ ํ์ธ์ฉ ------------------------------------------------------------------ --๋ฌธ์ 3) ๋ถ์๋ณ๋ก ๋ถ์๋ช
,์ต์ ๊ธ์ฌ,์ต๋ ๊ธ์ฌ,๋ถ์์ ํ๊ท ๊ธ์ฌ๋ฅผ ํฌํจํ๋ --DEPT_SUM VIEW์ ์์ฑํ์ฌ๋ผ. CREATE VIEW DEPT_SUM(dname, minsal, maxsal, avgsal) AS SELECT b.department_name, min(a.salary), max(a.salary), ROUND(avg(a.salary),1) FROM employees a, departments b WHERE a.department_id = b.department_id GROUP BY b.department_name; ------------------------------------------------------------------ SELECT * FROM DEPT_SUM; -- ๊ฒฐ๊ณผ ํ์ธ์ฉ ------------------------------------------------------------------ --๋ฌธ์ 4) ์์์ ์์ฑํ EMP_20,EMP_30 VIEW์ ์ญ์ ํ์ฌ๋ผ. DROP VIEW EMP_20 CASCADE CONSTRAINTS; DROP VIEW EMP_30 CASCADE CONSTRAINTS; | cs |
'๐ฆ ๋ฐ์ดํฐ ๋ฒ ์ด์ค (DB) ๐ฆ > ๐ พ ์ค๋ผํด DB & SQL' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
๐ TABLE(ํ ์ด๋ธ) ๋ฌธ์ - 1 (0) | 2021.02.17 |
---|---|
๐ ์จ๋ผ์ธ ๋ง์ผ(์ผํ๋ชฐ) ํ ์ด๋ธ ์์ (0) | 2021.02.17 |
โพ๏ธ์ด๋๋ถ(์ผ๊ตฌํ) ํ ์ด๋ธ ์์ (0) | 2021.02.17 |
INDEX (์ธ๋ฑ์ค) (0) | 2021.02.17 |
์ํ์ค (SEQUENCE) (0) | 2021.02.17 |
๋๊ธ