Chapter 7

Procedural Language/SQL (PL/SQL)

An Oracle-supplied languagethat augments SQL with programming language structures such as while loops, if-then-else blocks, and other such constructs. PL/SQL is used to create stored procedures and triggers.

SQL ALTER VIEW statement

Modifies a previously created view. This includes an indexed view. ALTER VIEW does not affect dependent stored procedures or triggers and does not change permissions

Transact-SQL (T-SQL)

A Microsoft-supplied language that is part of SQL Server. It augments SQL with programming language structures such as while loops, if-then-else blocks, and other such constructs. Transact-SQL is used to create stored procedures and triggers.

Interrelation Constraint

A data constraint between two tables.

Intrarelation Constraint

A data constraint within one table.

Data Manipulation Language (DML)

A language used to describe the processing of a database. SQL DML is that portion of SQL that is used to query, insert, update, and modify data.

Data Definition Language (DDL)

A language used to describe the structure of a database. SQL DDL is that portion of SQL that is used to create, modify, and drop database structures.

Cursor

An indicator of the current position in a pseudofile for an SQL SELECT that has been embedded in a program; it shows the identity of the current row. a pointer

IDENTITY({StartValue}, {Increment}) property

[MSSQL] For SQL Server 2008, the attribute that is used to create a surrogate key.

SQL DELETE statement

deletes a row from a specific table.

SQL INSERT statement

used to add rows of data to a table.

Pseudofile

used to avoid the problem of too many values being returned.

SQL UPDATE statement

used to change values of existing rows.

SQL AS keyword

used to create table aliases as well as for naming output columns:

SQL DROP COLUMN clause

used to drop an existing column in the SQL ALTER TABLE statement.

SQL view

• Hides columns or rows • Displays results of computations • Hides complicated SQL syntax • Layers built-in functions • Provides a level of isolation between table data and users' view of data • Assigns different processing permissions to different views of the same table • Assigns different triggers to different views of the same table

Procedural Programming Language

A programming language where each step necessary to obtain a result must to specified. The language may have the ability to contain sets of steps in structures called procedures or subprocedures.

Casual Relationship

A relationship that is created without a foreign key constraint. This is useful if the tables are missing data values.

PRIMARY KEY constraint

In SQL, a constraint statement used to create a primary key for a table.

NOT NULL constraint

In SQL, a constraint that specifies that a column must contain a value in every row.

NULL constraint

In SQL, a constraint that specifies that a column may have empty cells in some or all rows.

UNIQUE constraint

In SQL, a constraint that specifies that the values in a column must be unique.

CHECK constraint

In SQL, a constraint that specifies what data values are allowed in a particular column.

FOREIGN KEY constraint

In SQL, the constraint used to create relationships and referential integrity between tables.

DEFAULT keyword

In SQL, the work used to specify a default value for an attribute.

Five types of constraints

PRIMARY KEY may not have null values UNIQUE may have null values NULL/NOT NULL FOREIGN KEY CHECK

SQL TRUNCATE TABLE statement

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

SQL DROP TABLE statement

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.

Inner Join

Synonym for join. Contrast with outer join.

SQL CREATE TABLE Statement

The SQL command used to create a database table.

SQL CREATE VIEW Statement

The SQL command used to create a database view.

SQL MERGE Statement

This SQL command is essentially a combination of the SQL INSERT and SQL UPDATE statements, where an INSERT or UPDATE is performed depending upon existing data.

Stored Procedure

a program that is stored within the database and is compiled when used. Stored procedures can receive input parameters and they can return results. Stored procedures can be called from programs written in standard languages, scripting languages, and SQL command prompts.

Trigger

a stored program that is executed by the DBMS whenever a specified event occurs on a specified table or view. The 3 types are BEFORE, INSTEAD OF, and AFTER.

SQL ADD Clause

adds a column to a specific table in the SQL ALTER TABLE statement.

SQL/Persistent Stored Modules (SQL/PSM)

an ANSI/ISO standard for embedding procedural programming functionality into SQL.

SQL JOIN ON syntax

an alternate format for joins of three or more tables.

SQL ALTER TABLE statement

changes table structure, properties, or constraints after it has been created. It can be used to add and drop a constraint when using that SQL RESTRAINT clause.

SQL MERGE statement

combines the SQL INSERT and SQL UPDATE statements into one statement that can either insert or update data depending upon whether or not some condition is meet.

SQL outer join

used to show all of the rows in a table.

SQL TOP {NumberOfRows} syntax

limits the number of rows that can be seen when returned.

SQL TRUNCATE TABLE Statement

removes all data from a database table while leaving the table structure in place.

SQL left outer join

returns rows from the left or first table.

SQL right outer join

returns rows from the right table or the second table.

SQL ON DELETE clause

specifies whether deletions in one table should cascade to another table.

SQL ON UPDATE clause

specifies whether updates should cascade form one table to the next.

SQL LEFT JOIN syntax

the syntax used to return all rows from the left table or first table, even if there are no matches in the right table

SQL RIGHT JOIN syntax

the syntax used to return all rows on the table on the right (or second table in the join statement) in the result.

SQL DROP CONSTRAINT clause

used in conjunction with the SQL ALTER TABLE to drop a constraints.

SQL ADD CONSTRAINT clause

used to add constraints.