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.