DBMS MODULE 4

Subquery

Query inside another SQL query.

Nested Subquery

Subquery executed first, result used by outer query.

Single-row Subquery

Subquery returning exactly one value.

Multiple-row Subquery

Subquery returning multiple values.

Multiple-column Subquery

Subquery returning multiple columns.

IN operator

Used with multi-row subqueries to match any returned value.

ANY operator

Compares value with any returned by subquery.

ALL operator

Compares value with all values returned by subquery.

EXISTS operator

Checks if subquery returns at least one row.

NOT EXISTS operator

Checks if subquery returns zero rows.

Referential Integrity

Ensures FK values match PK values of referenced table.

Foreign Key

Attribute in referencing table pointing to primary key of another table.

Primary Key

Unique identifier for tuples in a table.

Inner Join

Returns rows where matching values exist in both tables.

Equi Join

Join using equality operator in join condition.

Non-Equi Join

Join using non-equality relational operators.

Left Outer Join

Returns matching rows plus all rows from left table.

Right Outer Join

Returns matching rows plus all rows from right table.

Full Outer Join

Returns all rows from both tables with matches where possible.

Self Join

Join where a table is joined with itself.

View

Virtual table based on result of SQL query.

CREATE VIEW

Creates a new view.

DROP VIEW

Removes an existing view.

Updatable View

View that allows DML operations on underlying tables.

Non-updatable View

View that includes aggregates, DISTINCT, GROUP BY, or joins.

Assertion

Database-wide constraint that must always be true.

CREATE ASSERTION

Creates an assertion with a CHECK condition.

Trigger

Procedure executed automatically on INSERT, UPDATE, or DELETE.

BEFORE Trigger

Trigger executed before DML event.

AFTER Trigger

Trigger executed after DML event.

ROW-level Trigger

Trigger executed once per row affected.

STATEMENT-level Trigger

Trigger executed once per SQL statement.

Database Security

Mechanisms to protect data from unauthorized access or misuse.

Authorization

Permissions given to users to access or modify data.

Read Authorization

Permission to read data from table.

Insert Authorization

Permission to insert tuples.

Update Authorization

Permission to update existing tuples.

Delete Authorization

Permission to delete tuples.

Resource Authorization

Permission to create tables, alter schema, and manage resources.

GRANT

Command to assign privileges to a user.

REVOKE

Command to remove privileges from a user.

WITH GRANT OPTION

Allows user to pass granted privileges to others.

Cascade Revoke

Automatic removal of privileges passed on by a user whose rights were revoked.