Cannot include aggregate functions sql1999 may involve

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: lors; ActiveSailors; 63 Updatable Views 64 Updatable Views in MySQL SQL-92: Must be defined on a single table using only SQLselection and projection and not using DISTINCT. Cannot include: • aggregate functions SQL:1999: May involve multiple tables in SQL:1999 if each view field is from exactly one underlying base table and that table’s PK is included in view; not table’ restricted to selection and project, but cannot insert into views that use union, intersection, or set difference. • DISTINCT • GROUP BY • HAVING • UNION • subquery in select list • certain joins Why have these restrictions? 65 66 Updatable Views (continued) Updatable Views (continued) In addition, to be insertable: insertable: Certain multiple-table views are updatable: multiple- • must contain no duplicate view column names • must user inner join (not outer join or union) • must contain all columns in base table with no default value • must result in update to a single table in view defn. (so SET clause must name only columns from one table in view) • must contain only simple column references, not derived columns May...
View Full Document

Ask a homework question - tutors are online