CREATE VIEW

CREATE VIEW

今儿上课讲CREATE VIEW

PostgreSQL 8.1 中文文档

Schema

employee(ssn, name, salary, s_ssn, deptNo)
dept(dno, dname, address)

Naive SQL

[code language="sql"]
SELECT d.dname, max(e.salary) AS sal
FROM employee e, dept d
WHERE e.deptNo = d.dno
GROUP BY d.dno, d.dname
[/code]

Materialized View

[code language="sql"]
CREATE TABLE V (dno, sal)
populate table offline
INSERT INTO V (dno, sal)
SELECT e.depNo as dno, max(e.salary) as sal
FROM employee e
GROUP BY e.deptNo
[/code]

Get deptname

[code language="sql"]
SELECT d.dname, v.sal
FROM V v, dept d
WHERE v.dno = d.dno
[/code]

When update or delete...

If new employee has more salary that max salary?

Need to look into the base sql. second highest salary?

How to do better?

[code language="sql"]
CREATE TABLE V’(dno, maxSal, mSalCnt, secondSal, sSalCnt)
[/code]

To be continue..