Ch 8 Flashcards
Inner join
The inner join is the traditional join in which only rows that meet a given criterion are selected.
Outer join
An outer join returns not only the matching rows but the rows with unmatched attribute values for one table or both tables to be joined.
Cross join
A cross join performs a relational product (also known as the Cartesian product) of two tables.
Correlated subquery
A correlated subquery is a subquery that executes once for each row in the outer query. The process is similar to the typical nested loop in a programming language.
Set-oriented
SQL data manipulation commands are set-oriented, that is, they operate over entire sets of rows and columns (tables) at once.
Union-compatible
UNION, INTERSECT and EXCEPT (MINUS) work properly only if relations are union-compatible, which means that the number of attributes must be the same and their corresponding data types must be alike.
View
A view is a virtual table based on a SELECT query. The query can contain columns, computed columns, aliases, and aggregate functions from one or more tables. The tables on which the view is based are called base tables.
Batch update routine
As the name implies, a batch update routine pools multiple transactions into a single batch to update a master table field in a single operation.
Updatable view
As its name suggests, an updatable view can be used to update attributes in any base table(s) used in the view. Not all views are updatable. Several restrictions govern updatable views, and some of them are vendor-specific.
Persistent stored module (PSM)
A persistent stored module (PSM) is a block of code containing standard SQL statements and procedural extensions that is stored and executed at the DBMS server.
Procedural Language SQL (PL/SQL)
Procedural Language SQL (PL/SQL) is a language that makes it possible to use and store procedural code and SQL statements within the database and to merge SQL and traditional programming constructs, such as variables, conditional processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops), and error trapping. The procedural code is executed as a unit by the DBMS when it is invoked (directly or indirectly) by the end user.
Anonymous PL/SQL block
An anonymous PL/SQL block has not been given a specific name.
Trigger
A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event.
Statement-level trigger
A statement-level trigger is assumed if you omit the FOR EACH ROW keywords. The type of trigger is executed once, before or after the triggering statement is completed. This is the default case.
Row-level trigger
A row-level trigger requires use of the FOR EACH ROW keywords. This type of trigger is executed once for each row affected by the triggering statement. (In other words, if you update 10 rows, the trigger executes 10 times.)
