Ch 8 Flashcards Preview

CPSC 5021 > Ch 8 > Flashcards

Flashcards in Ch 8 Deck (24)
Loading flashcards...
1
Q

Inner join

A

The inner join is the traditional join in which only rows that meet a given criterion are selected.

2
Q

Outer join

A

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.

3
Q

Cross join

A

A cross join performs a relational product (also known as the Cartesian product) of two tables.

4
Q

Correlated subquery

A

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.

5
Q

Set-oriented

A

SQL data manipulation commands are set-oriented, that is, they operate over entire sets of rows and columns (tables) at once.

6
Q

Union-compatible

A

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.

7
Q

View

A

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.

8
Q

Batch update routine

A

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.

9
Q

Updatable view

A

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.

10
Q

Persistent stored module (PSM)

A

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.

11
Q

Procedural Language SQL (PL/SQL)

A

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.

12
Q

Anonymous PL/SQL block

A

An anonymous PL/SQL block has not been given a specific name.

13
Q

Trigger

A

A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event.

14
Q

Statement-level trigger

A

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.

15
Q

Row-level trigger

A

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.)

16
Q

Stored procedure

A

A stored procedure is a named collection of procedural SQL statements. Just like database trigger, stored procedures are stored in the database.

17
Q

Cursor

A

A cursor is a special construct used in the procedural SQL to hold the data rows returned by a SQL query.

18
Q

Implicit cursor

A

An implicit cursor is automatically created in procedural SQL when the SQL statement returns only one value.

19
Q

Explicit cursor

A

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).

20
Q

Stored function

A

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.

21
Q

Embedded SQL

A

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.

22
Q

Host language

A

No matter what language you use, if it contains embedded SQL statements, it is called the host language.

23
Q

Static SQL

A

Static SQL means that the SQL statements will not change while the application is running.

24
Q

Dynamic SQL

A

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.