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.)
Stored procedure
A stored procedure is a named collection of procedural SQL statements. Just like database trigger, stored procedures are stored in the database.
Cursor
A cursor is a special construct used in the procedural SQL to hold the data rows returned by a SQL query.
Implicit cursor
An implicit cursor is automatically created in procedural SQL when the SQL statement returns only one value.
Explicit cursor
An explicit cursor is created to hold the output of a SQL statement that may return two or more rows (but could return zero rows or only one).
Stored function
A stored function is basically a named group of procedural and SQL statements that returns a value, as indicated by a RETURN statement in its program.
Embedded SQL
Embedded SQL is a term used to refer to SQL statements contained within an application programming language such as Visual Basic .NET, C#, COBOL or Java.
Host language
No matter what language you use, if it contains embedded SQL statements, it is called the host language.
Static SQL
Static SQL means that the SQL statements will not change while the application is running.
Dynamic SQL
Dynamic SQL is a term used to describe an environment in which the SQL statement is not known in advance; instead the SQL statement is generated at run time.
