如果這個表被其他列引用，那麼，哪怕有一些row沒被引用，也無法删除beers表裡的内容。
可以用下面這種方法删除：
join
SELECT s.id, s.name, avg(e.mark) as avgMark
FROM Students s
JOIN Enrolments e on (s.id = e.student)
GROUP BY s.id, s.name
-- or --
SELECT s.id, s.name, avg(e.mark) as avgMark
FROM Students s, Enrolments e
WHERE s.id = e.student
GROUP BY s.id, s.name 一個join是和where相同的。
view
更新view
滿足上面的情況才能更新view:
- view中不能有派生屬性
- view中沒有的屬性，會是null
You can insert rows into a view only if the view is modifiable and contains no derived columns. The reason for the second restriction is that an inserted row must provide values for all columns, but the database server cannot tell how to distribute an inserted value through an expression. An attempt to insert into the response view, as the previous example shows, would fail.
When a modifiable view contains no derived columns, you can insert into it as if it were a table. The database server, however, uses NULL as the value for any column that is not exposed by the view. If such a column does not allow NULL values, an error occurs, and the insert fails.
Another mechanism for inserting rows (or performing UPDATE or DELETE operations) on IBM® Informix® views, including complex views, is to create INSTEAD OF triggers, as described in the IBM Informix Guide to SQL: Syntax.
插入之後，view會重新對表格查詢，擷取結果到view中。
Queries on Multiple Tables
exercise
prac
調試小知識：
psql weblog -f Hosts.sql > .errs 2>&1
grep 2>&1
File descriptor 1 is the standard output (stdout).
File descriptor 2 is the standard error (stderr). Here is one way to remember this construct (although it is not entirely accurate): at first, 2>1 may look like a good way to redirect stderr to stdout. However, it will actually be interpreted as “redirect stderr to a file named 1”. & indicates that what follows and precedes is a file descriptor and not a filename. So the construct becomes: 2>&1.
Consider >& as redirect merger operator.
通過grep可以找到所有帶error一行的錯誤。